Performance tuning

Uwe Hesse's picture

Create a SQL Profile to let the Optimizer ignore hints in #Oracle

Something I presented recently during an Oracle Database 12c Performance Management and Tuning class. Hints are a double-edged sword; they may do more harm than good. What if  hinted SQL comes from an application that you as the DBA in charge can’t modify? You can tell the Optimizer to ignore that nasty hint.

One method is to use alter session set “_optimizer_ignore_hints”=true; This will make the optimizer ignore all hints during that session  – also the useful ones, so maybe that is not desirable. The method I show here works on the statement level. The playground:

rshamsud's picture

CPU profiling using perf utility in Linux

After reading my blog entry about a performance issue due to excessive HCC decompression ( Accessing HCC compressed objects using index access path, a reader asked me about the CPU profiling method I mentioned in that blog entry. I started responding to that comment, and realized that the response was too big for a comment. So, in this blog entry, I will cover basics of the CPU profiling in Linux. Other platform provides similar utilities, for example, Solaris provides an utility dtrace.

Tool Box

rshamsud's picture

Accessing HCC compressed objects using an index

Problem

I came across another strange SQL performance issue: Problem was that a SQL statement was running for about 3+ hours in an User Acceptance (UA) database, compared to 1 hour in a development database. I ruled out usual culprits such as statistics, degree of parallelism etc. Reviewing the SQL Monitor output posted below, you can see that the SQL statement has already done 6 Billion buffer gets and steps 21 through 27 were executed 3 Billion times so far.

Statistics and execution plan

rshamsud's picture

library cache lock on BUILD$ object

I was testing an application performance in 12c, and one job was constantly running slower than 11g. This post is to detail the steps. I hope the steps would be useful if you encounter similar issue.

Problem

In an one hour period, over 90% of the DB time spent on waiting for library cache lock waits. Upon investigation, one statement was suffering from excessive waits for ‘library cache lock’ event. We recreated the problem and investigated it further to understand the issue.

Following is the output of wait_details_rac.sql script (that I will upload here) and there are many PX query servers are waiting for ‘library cache lock’ wait event.

rshamsud's picture

OTNYathra2016

Over the last many years, some of you have invited me to attend conferences in India, and talk about Oracle RAC and performance. I have not had an opportunity to make it to conferences in India, until now </p />
</p></div>
    <div class=»

Uwe Hesse's picture

Full Database Caching in #Oracle 12c

If your Oracle Database fits into the buffer cache, we will put all segments into it now upon access – no more direct reads:

SQL> connect / as sysdba
Connected.
SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE	12.1.0.2.0	Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

SQL> select sum(bytes)/1024/1024 as mb from v$datafile;

	MB
----------
      1424

SQL> select current_size from v$buffer_pool;

CURRENT_SIZE
------------
	1872

The requirements are met here, I don’t need to force anything:

rshamsud's picture

OOUG RAC day presentation files and scripts

Thanks for coming to my presentations in RAC day at Dublin, Ohio. Please find the presentation files below. Hopefully, I will get video files and upload that here too.

OOUG presentation files and scripts

md5 checksum of the zip file is:

$md5sum ooug_2015_pdf.zip
df8bdcbc02926e5bbd721514b473bf16  ooug_2015_pdf.zip

rshamsud's picture

RAC day with Ohio Oracle User Group

I will be talking about RAC and performance in-depth, with lots of demos, in a RAC day training with Ohio Oracle User group on Nov 16,2015 Monday. Venue for the presentation is Dublin, Ohio.

Agenda for the day:

08:00a – 09:00: Registration / Breakfast

09:00a – 09:15: Announcements -Introduction of the speaker

09:15a – 10:30: Underpinning for Oracle RAC and Clusterware

10:30a – 10:45: Break

10:45a – 11:45: RAC cache fusion internals

11:45a – 01:00: Lunch

01:00p – 02:00: RAC Performance tuning Part 1 – Wait events and object tuning

02:00p – 02:15: Break

02:15p – 03:30: RAC performance tuning Part 2 – locks, library cache locks etc.

03:30p – 03:45: Member Announcements, Gift Drawings

Please RSVP to the co-ordinators so that you will have a seat </p />
</p></div>
    <div class=»

Uwe Hesse's picture

Less Performance Impact with Unified Auditing in #Oracle 12c

There is a new auditing architecture in place with Oracle Database 12c, called Unified Auditing. Why would you want to use it? Because it has significantly less performance impact than the old approach. We buffer now audit records in the SGA and write them asynchronously to disk, that’s the trick.

Other benefits of the new approach are that we have now one centralized way (and one syntax also) to deal with all the various auditing features that have been introduced over time, like Fine Grained Auditing etc. But the key improvement in my opinion is the reduced performance impact, because that was often hurting customers in the past. Let’s see it in action! First, I will record a baseline without any auditing:

 

Uwe Hesse's picture

Multiple invisible indexes on the same column in #Oracle 12c

After invisible indexes got introduced in 11g, they have now been enhanced in 12c: You can have multiple indexes on the same set of columns with that feature. Why would you want to use that? Actually, this is always the first question I ask when I see a new feature – sometimes it’s really hard to answer :-)

Here, a plausible use case could be that you expect a new index on the same column to be an improvement over the existing old index, but you are not 100% sure. So instead of just dropping the old index, you make it invisible first to see the outcome:

 

Syndicate content