Tuning - alfredokriegdba.com

Oracle Database Performance Diagnostics, Tuning and Validation with Oracle Enterprise Manager 13c Workshops

Last month we held 2 workshops where we went through some capabilities of Oracle Enterprise Manager 13c that help with performance diagnostics, tuning and validation for the Oracle Database.

If you were not able to attend and you want to learn about these capabilities, I recommend you take a look at the recording using the link below.

Session 1: August 4th 2021

Click here…

Session 2: August 25th 2021

click here…

Thanks,
Alfredo

Do You Trust SQL Tuning Advisor?

Today’s post is about SQL tuning. During my more than 14 years of experience as a Database Administrator I haven’t found a better tool than Enterprise Manager (EM) to monitor Oracle’s DB performance. The “Top Activity” graph has been a constant during all these years.

Note: The EM’s Top Activity page has been deprecated in EM 13.3 PG.

click here

So let’s start from the beginning. If I receive a call or ticket stating that the DB or a process in the DB is slow, the first thing I do is to jump to the “ASH Analytics” page in EM. This page is part of the “Diagnostics” DB pack.

Here I can spot right away SQL_ID 6kd5jj7kr8swv being the top CPU consumer followed by other 3 SQL_ID’s. I’m going to create a SQL Tuning Set (STS) containing all these SQL’s.

From the STS dashboard I’m going to to click on “Create” to launch the wizard.

Step 1 requires a name and owner for the STS.

Note: You can create an empty STS and populate it later.

The next step is to load the STS with SQL’s. I’m going to choose “Load SQL one time only” and select from “Cursor Cache“. This because I’m sure they are loaded in memory. Another possible option is to load them from the AWR repository.

Next is to filter the SQL’s we want top load to our STS. I’m using the SQL ID row along with the IN operator to achieve this.

The next screen will ask you if you want to run that immediately or you want to schedule this for a later time.

And finally the review page. If everything looks right, then click on “Submit“.

Once the STS is created you will be able to see those 4 SQL’s loaded into it. I’m going now to schedule a SQL Tuning Advisor (STA) task to find out if these SQL’s can be tuned for better performance. STA is part of the “Tuning” DB pack.

Let’s review the results of the STA task we just created couple of minutes back. STA examined 4 distinct SQL’s. It only found recommendations for 3 of them with an overall potential benefit of 55 seconds in DB time.

Let’s click on “Show all results” button to see detailed information about the recommendations. The table below shows all 4 SQL’s along with their recommendations. We can spot right away the benefits of tuning SQL ID 6kd5jj7kr8swv. A SQL Profile could be created with a benefit of 57% or an Index with a benefit of 72%.

What do you do here? Do you hit “Implement All SQL Profiles” and cross your fingers hoping the new execution plan are going to be faster?

You don’t know how many times I saw sub-optimal plans generated by SQL Profiles.

That’s actually me fingers crossed hoping everything works!!!

There’s actually a better way to handle this. Real Application Testing (RAT) DB pack has a tool named SQL Performance Analyzer (SPA). This tool allows you to play the SQL before and after the changes (SQL Profile) and gives you a detailed report with the results.

In order to do that you’ll have to click on the “Validate All Profiles with SPA” button.

What is going to happen is that SPA will gather data before and after implementing the SQL Profiles and will produce a report.

Note: SPA will actually implement the SQL Profiles. Keep this in mind in a running Production system. RAT has also the capabilities to replay this workload in a Non-Production system. You can implement the SQL Profiles there without harming the Production performance.

Ooops! The SQL Profiles actually have a regression impact of -824%. Look at the Net Impact for the 1st SQL. It has a -830% impact, this means that the SQL Profile produced a sub-optimal plan. After this I will go and disable the recently created SQL Profiles before they cause a bigger impact.

My next step will be to propose the Index creation. This may have different implications depending on the application you are running, the business needs and operating procedures. I decided to implement the index to measure the performance benefit.

Here you can see how the performance was before the SQL Profile (maroon), with the SQL Profile implemented (turquoise) and with the index created (pink).

In summary, SPA is a great tool that helps you validate that the changes made to the DB system are working as expected. SPA not only validates SQL Profiles, it also validates statistics, DB parameter changes, DB upgrades and also you can verify the performance of your DB system with an Exadata storage server simulation.

Thanks,
Alfredo

Why you should attend to IOUG Collaborate 2015?

Today I want to share a personal story about IOUG’s Collaborate, a great conference that supports products from Engineered Systems, Oracle Databases to Fusion Middleware and Siebel. Last year was my first time as a speaker and was one of kind experience as I was able to share my knowledge in Oracle Database performance tuning with professionals that work on a daily basis with Oracle technologies.

This is what makes Collaborate great, professionals sharing their experiences to other professionals. You can be sure that the guys who are speaking at Collaborate really know what they are talking about, that’s a guarantee. Apart of education, networking is also great at Collaborate, so you can meet IT professionals, Oracle Product Managers and technology gurus (you may already seen their blogs).

This year Collaborate will be greater than ever, just imagine to learn from Tom Kyte, Craig Shallahamer and Kellyn Pot’Vin-Gorman, you don’t want to miss the opportunity!

If you want to be part of this big IT event, want to learn great stuff that will help you with your daily work and want to network with great people, you should register right away!

More information available at http://collaborate.ioug.org/

BTW. If you attend the Collaborate 2015, be sure to stop by my sessions about Oracle DB Performance and Oracle Enterprise Manager

http://coll15.mapyourshow.com/6_0/sessions/session-details.cfm?ScheduleID=3431

http://coll15.mapyourshow.com/6_0/sessions/session-details.cfm?ScheduleID=4503

Oracle Enterprise Manager at Collaborate 2015 https://lnkd.in/e3G6ZzK

Thanks,

Alfredo

Why the execution plan changed? Case study

Overview

At some point in time the execution plan of the SQL_ID etp65bryqtd2d changed. The new execution plan drops on performance from an Avg. of 0.01 seconds per execution to an Avg. of 1.5 seconds per execution.

This increase on elapsed time per execution provoke that the application can’t process work orders fast enough, the work queue started to drastically grow leading the application team to escalate a severity 1 issue.

Analysis

Execution plan changes

The execution plan changes only when a parse is performed, this because the _optim_bind_peeking parameter is on default value “TRUE”. A hard parse is required due to cursor invalidation in the shared pool; a cursor can be invalidated for some reasons but the most happening is, object change (Index rebuild, DBMS_STATS, etc.).

Rolling Cursor Invalidation

Starting with Oracle10g cursors are marked for rolling invalidation instead of marked INVALID immediately. On the next execution of the query the Oracle server will generate a random number between 0 and the value of the _optimizer_invalidation_period parameter, which has a default value of 18000, and the cursor will remain valid for this number of seconds. Upon every following execution Oracle will check if this random selected timeout has expired. If that is the case then the cursor will be hard parsed again.

Things will change without notice! Cursor invalidation has changed in the past and the reason for this change is that previously all dependent cursors were invalidated immediately after gathering new object statistics. This massive invalidation might cause a serious degradation in performance right after statistics gathering due to a high number of hard parses. Rolling cursor invalidation spreads the invalidation of cursors out over a longer period of time thereby avoiding the performance degradation caused by hard parsing the invalidated cursors.

Reference: Metalink note:557661.1

Bind variable peeking

The query optimizer peeks at the values of user-defined bind variables on the first invocation of a cursor. This feature lets the optimizer determine the selectivity of any WHERE clause condition, as well as if literals have been used instead of bind variables. On subsequent invocations of the cursor, no peeking takes place, and the cursor is shared, based on the standard cursor-sharing criteria, even if subsequent invocations use different bind values.

In this case we can clearly see that the elapsed time per execution is not changing because of the bind peeking; when Oracle peeks a bad execution plan, all elapsed times are bad.

CBO trace event 10053

Behind the scenes, what is the CBO doing when it comes to how it comes up with an execution plan? This is where the 10053 trace event comes to play. Other tools or settings show us WHAT the CBO comes up with; the 10053 setting tells us HOW the CBO came to its decision (the final execution plan).

conn / as sysdba

oradebug setmypid

oradebug unlimit

oradebug event 10053 trace name context forever, level 1

…your statement here…

oradebug event 10053 trace name context off

oradebug tracefile_name

We have performed this CBO trace when the query was performing bad and when it was performing well. With this method we have 2 trace files to compare and find out what is making the CBO to take such bad plan.

Notice that this method will produce a trace file only in the hard parse stage. So, in order to force the CBO to hard parse the SQL we have run it with a comment hint.

variable N1 VARCHAR2(128)

exec :N1 :=’125′;

select /* comment*/…

After comparing both trace files we can clearly see when the CBO chose the good plan:

***********************

Best so far: Table#: 1 cost: 7.3522 card: 3973.0000 bytes: 218515

Table#: 7 cost: 16.2850 card: 9.0178 bytes: 612

Table#: 2 cost: 21.6884 card: 33.7888 bytes: 3094

Table#: 5 cost: 48.9215 card: 3565.0298 bytes: 406410

Table#: 4 cost: 1475.7155 card: 3565.4846 bytes: 556140

Table#: 0 cost: 1483.2886 card: 3559.8976 bytes: 644360

Table#: 6 cost: 2195.7103 card: 3599.4022 bytes: 770186

Table#: 3 cost: 2197.9000 card: 3599.4022 bytes: 820572

***********************

In the other hand, the trace of the bad plan is having a huge cost:

***********************

Best so far: Table#: 1 cost: 7.3510 card: 3928.0000 bytes: 216040

Table#: 7 cost: 16.2836 card: 8.9798 bytes: 612

Table#: 2 cost: 21.6870 card: 32.6678 bytes: 3003

Table#: 5 cost: 153.8561 card: 20168.2755 bytes: 2299152

Table#: 4 cost: 8225.5481 card: 20170.8485 bytes: 3146676

Table#: 0 cost: 8233.2391 card: 20139.2414 bytes: 3645159

Table#: 6 cost: 12263.4248 card: 20362.7290 bytes: 4357682

Table#: 3 cost: 12266.6610 card: 20362.7290 bytes: 4642764

***********************

In the trace files we can clearly see that cost of table#5 messed up our total cost. Why is that?

***************

Now joining: TABLE5[TL2]#5

***************

NL Join

Outer table: Card: 32.67 Cost: 21.69 Resp: 21.69 Degree: 1 Bytes: 91

Inner table: TABLE5 Alias: TL2

Access Path: TableScan

Index: TABLE5_IDX3

resc_io: 20.00 resc_cpu: 360839

ix_sel: 3.9216e-04 ix_sel_with_filters: 3.9216e-04

NL Join: Cost: 153.86 Resp: 153.86 Degree: 1

Cost_io: 152.55 Cost_cpu: 18392646

Resp_io: 152.55 Resp_cpu: 18392646

****** finished trying bitmap/domain indexes ******

Best NL cost: 153.86

resc: 153.86 resc_io: 152.55 resc_cpu: 18392646

resp: 153.86 resp_io: 152.55 resp_cpu: 18392646

Join Card: 20168.28 = outer (32.67) * inner (1574304.00) * sel (3.9216e-04)

Join Card – Rounded: 20168 Computed: 20168.28

Index selectivity on TABLE5_IDX3 is very low (3.9216e-04) compared with the good plan (6.7020e-05).

Index selectivity

After looking into the Index selectivity, the question arises; why the Index selectivity is low?

B*TREE Indexes improve the performance of queries that select a small percentage of rows from a table. As a general guideline, we should create indexes on tables that are often queried for less than 15% of the table’s rows. This value may be higher in situations where all data can be retrieved from an index, or where the indexed columns can be used for joining to other tables.

The ratio of the number of distinct values in the indexed column / columns to the number of records in the table represents the selectivity of an index. The ideal selectivity is 1. Such selectivity can be reached only by unique indexes on NOT NULL columns.

Selectivity = Distinct Values / Total Number Rows

With this measure we can correlate the importance of the statistics in the objects. If we gather statistics only on the table, the total number of rows will change; leading to drastically change the Index selectivity. In the same manner, if we only gather statistics on the Index, the number of distinct values will change and the selectivity will dramatically change.

Apart of that, the accuracy of the statistics will also play an important role. The accurate the statistics, the accurate the Index selectivity.

Suggestions – How to fix this issue?

The suggestions came from the percept that Index selectivity is playing a bad game on this particular SQL_ID.

• As table and its indexes have a close relationship on how the CBO calculates the cost, we must gather statistics on the table and its indexes in the same time. This will avoid the Index selectivity to drastically change.

• When an index is rebuilt, we must gather statistics of the table too.

• Review the accuracy of the statistics. In some cases AUTO_SAMPLE_SIZE is not giving a good sample.

Some test on this SQL_ID have proven that gathering statistics to the TABLE5 table and its indexes with an estimate percent of 50%, plus invalidating the cursor immediately solved the issue.

The CBO was able to pick the good execution plan because the index selectivity was good enough.

Thanks,

Alfred