SQL Tuning Fundamentals: Execution Plans - September 17 Webinar

Register now and join me for my next webinar entitled "SQL Tuning Fundamentals: Execution Plans".

Few tools are as critical to SQL optimization as the execution plan. The ability to read and understand an execution plan allows us to evaluate and optimize SQL performance. Unfortunately, complex plans often seem daunting and can be difficult to understand. During this webinar, I'll walk you through a set of guidelines for how to read an execution plan and how to make sense of the operations and statistics (both estimated and actual) the plan output provides.

Topics covered in this webinar include:

• How to read an execution plan, and how various plan operations work 
• How to use the plan to pinpoint performance problems 
• Tools to accelerate your analysis of execution plan data

My day with the Ohio Oracle Users Group

Thanks so much to everyone who attended the Ohio Oracle User's Group event with me on July 18. I really enjoyed the opportunity to be there. The group appears to be vibrant and growing and everyone involved made it a top-notch experience (for me at least!).

My topic for the day was "SQL Stuff You Should Know" and was a mash-up of lots of different bits and pieces of information about SQL Tuning and the Oracle Optimizer. Links to my presentation slides will be made available on the OOUG web site, but I thought I'd also provide them here.

Downloads:
Presentation slides
Scripts

User groups are a fantastic way to network and have easily accessible and inexpensive opportunities to learn. I'm always happy to be invited to participate in an event and even more happy when I see a thriving community of my Oracle peers! Thanks again OOUG!!

Follow-up: Using Optimizer Hints for Oracle Performance Tuning Webinar

Thanks to everyone who attended my webinar on using hints for Oracle testing and performance tuning. As usual, it was a great event and I appreciate the comments and questions.

Downloads:
Presentation PDF
Related scripts
Webinar recording


I'll be back in the saddle again in July so keep your eyes open for the announcement of that event. Thanks again and hope to see you then!

Using Optimizer Hints for Oracle Performance Tuning

My next Embarcadero sponsored webinar will be on May 14 and is entitled Using Optimizer Hints for Oracle Performance Tuning.

Register now!


Hints are excellent database performance tuning tools that direct the Oracle optimizer to utilize specific operations in SQL execution plans. We often use hints because the Oracle optimizer doesn’t always come up with the execution plan we want on its own. When used correctly, hints can help stabilize an execution plan to use the same operations over and over allowing the SQL to perform the way we desire.

In this webinar, I'll take a look at using hints specifically for testing. Hints are great, and often overlooked, testing tools.

Register for Optimizer Hints for Oracle Performance Tuning webinar to learn:

• The basics of using optimizer hints to choose desired plan operations 
• How to setup and compare tests using different optimizer hints for changes in response time and resource consumption 
• How to create and maintain an information repository of testing results to be used for problem analysis in the future 
• Tools for applying and testing a broad range of hints 

Thanks again to Embarcadero (@DBPowerStudio) for their sponsorship of these webinars.

I hope to see you there!

Webinar - Understanding Oracle Optimizer Statistics

Embarcadero is sponsoring another Oracle Community webinar on January 29  (rescheduled) February 5 conducted by yours truly. Register for Understanding Oracle Optimizer Statistics and join me as I cover

• Oracle optimizer statistics fundamentals for computing cardinality/selectivity on single and multi-column predicates.
• How statistics can help the optimizer understand data distribution patterns.
• Reasons why certain ways of writing SQL limit the optimizer's ability and how query transformations improve the odds of getting a better execution plan.

Hope to "see" you there!

I was wrong

Thanks to dear Maria Colgan for her intervention, I am publicly admitting the error of my ways and declaring to never use the drug of the optimizer_index_cost_adj parameter again. I publicly admit (again) that I have (on occasion) used an extremely high OICA setting to try and "force" Exadata to use full table scans over index scans. I was wrong and I am sorry.

Snicker...

I've had a great time at the Enkitec Extreme Exadata Expo (E4) this week in Dallas. I eagerly awaited the presentation of Maria Colgan ("The Optimizer Lady") where she promised to "show through the use of real-world examples what you can do to ensure the Optimizer fully understands the capabilities of the platform it is running on without having to mess with initialization parameters or Optimizer hints."

During her presentation today, she did offer a couple of ways (made available in Exadata patch bundles...only one of which is currently available) to help clue the optimizer in on a couple of Exadata features so that it can make adjustments to cost computations. The patch that is available allows you to collect system stats in "Exadata" mode. By doing so, the MBRC system statistic is actually recorded and made available to the optimizer and results in a reduction of the cost of a full scan. This ultimately results in full scans being selected more frequently (versus an index scan operation) which opens up the possibility of more smart scans being able to kick in. As Maria told us, the MBRC value is currently not retained in the system stats values and the optimizer uses a "default" of 8 (and has since Oracle 8 days). The patch allows a system stats collection done using the Exadata mode to collect and retain an actual (more realistic) MBRC value that can provide more accuracy.

I'm still holding out for options to affect specific individual SQL statements on a case-by-case basis versus a more broad brush approach that can cause all SQL to be affected, but this looks like a good start. In the meantime, I promise to do my very best to refrain from resorting to using OICA.

Disclaimer: I'm not promising to *not* use other parameters or hints...yet.  :)

Thanks to everyone who attended E4 and I hope you shared my opinion that it was a fantastic event. I'm already looking forward to doing it again next year!

"Dynamic" WHERE clauses that aren't

What does the optimizer do with "dynamic" WHERE clauses? What I'm talking about is when you have a SELECT statement that has a "simple" WHERE clause like this:

WHERE column1 = NVL(:B1, column1) 
  AND column2 = NVL(:B2, column2)

The intention of this WHERE clause is to allow 4 different scenarios to be covered.
1) If :B1 is null and :B2 is null, then retrieve all rows.
2) If :B1 is null and :B2 is not null, then retrieve only rows where column2 = :B2.
3) If :B2 is null and :B1 is not null, then retrieve only rows where column1 = :B1.
4) If :B1 is not null and :B2 is not null, then retrieve only rows where column1 = :B1 and column2 = :B2.

The logic is that since the two conditions are AND'ed, that both have to evaluate to true in order to produce any rows. If, however, you wish to be flexible and allow for all 4 cases to be possible with a single query, then you have to find a way to ensure that both sides of the AND evaluate TRUE regardless of what inputs you receive. By using the NVL, you can accomplish that. You either pass in a null value or a not null value and the NVL check will make sure that the condition always evaluates to true. You get to either qualify which rows are returned by passing in a not null bind variable or you can leave the selection of rows unqualified by passing in a null value. Simple, huh?

Well, it may be simple in terms of the way the code looks, but what it does to the optimizer isn't that simple. The optimizer has to come up with a plan that can best serve all 4 possibilities equally. It can't/won't come up with 4 different plans.

Let me show you a few tests. I'll be using a test table I had in place from some previous tests for another post. It's a simple 2500 row table with 2 indexes (T2_I3 on the hair column and T2_I6 on the work column). Take a look at what happens when I execute my query 4 times using the 4 different scenarios I noted above.

SQL>set serveroutput off
SQL>variable b1 number
SQL>variable b2 number
SQL>exec :b1 := null;

PL/SQL procedure successfully completed.

SQL>exec :b2 := null ;

PL/SQL procedure successfully completed.

SQL>
SQL>select /*+ gather_plan_statistics */ count(*)
  2    from bitmap_t2
  3  where hair = NVL(:b1, hair)
  4    and work = NVL(:b2, work) ;

       COUNT(*)
---------------
           2500

1 row selected.

SQL>
SQL>@allstats_last

PLAN_TABLE_OUTPUT
------------------
SQL_ID  16jagac5xq0r4, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*)   from bitmap_t2 where hair = NVL(:b1, hair)   and work
= NVL(:b2, work)

Plan hash value: 2153464131
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE                |           |      1 |      1 |      1 |00:00:00.08 |    2504 |    342 |
|   2 |   CONCATENATION                |           |      1 |        |   2500 |00:00:00.07 |    2504 |    342 |
|*  3 |    FILTER                      |           |      1 |        |   2500 |00:00:00.06 |    2504 |    342 |
|*  4 |     TABLE ACCESS FULL          | BITMAP_T2 |      1 |    357 |   2500 |00:00:00.03 |    2504 |    342 |
|*  5 |    FILTER                      |           |      1 |        |      0 |00:00:00.01 |       0 |      0 |
|*  6 |     TABLE ACCESS BY INDEX ROWID| BITMAP_T2 |      0 |      5 |      0 |00:00:00.01 |       0 |      0 |
|*  7 |      INDEX RANGE SCAN          | T2_I6     |      0 |     32 |      0 |00:00:00.01 |       0 |      0 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(:B2 IS NULL)
   4 - filter(("HAIR"=NVL(:B1,"HAIR") AND "WORK" IS NOT NULL))
   5 - filter(:B2 IS NOT NULL)
   6 - filter("HAIR"=NVL(:B1,"HAIR"))
   7 - access("WORK"=:B2)

SQL>
SQL>exec :b2 := 45 ;

PL/SQL procedure successfully completed.

SQL>
SQL>select /*+ gather_plan_statistics */ count(*)
  2    from bitmap_t2
  3  where hair = NVL(:b1, hair)
  4    and work = NVL(:b2, work) ;

       COUNT(*)
---------------
             32

1 row selected.

SQL>
SQL>@allstats_last

PLAN_TABLE_OUTPUT
------------------
SQL_ID  16jagac5xq0r4, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*)   from bitmap_t2 where hair = NVL(:b1, hair)   and work
= NVL(:b2, work)

Plan hash value: 2153464131
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE                |           |      1 |      1 |      1 |00:00:00.01 |      67 |      4 |
|   2 |   CONCATENATION                |           |      1 |        |     32 |00:00:00.01 |      67 |      4 |
|*  3 |    FILTER                      |           |      1 |        |      0 |00:00:00.01 |       0 |      0 |
|*  4 |     TABLE ACCESS FULL          | BITMAP_T2 |      0 |    357 |      0 |00:00:00.01 |       0 |      0 |
|*  5 |    FILTER                      |           |      1 |        |     32 |00:00:00.01 |      67 |      4 |
|*  6 |     TABLE ACCESS BY INDEX ROWID| BITMAP_T2 |      1 |      5 |     32 |00:00:00.01 |      67 |      4 |
|*  7 |      INDEX RANGE SCAN          | T2_I6     |      1 |     32 |     32 |00:00:00.01 |      35 |      0 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(:B2 IS NULL)
   4 - filter(("HAIR"=NVL(:B1,"HAIR") AND "WORK" IS NOT NULL))
   5 - filter(:B2 IS NOT NULL)
   6 - filter("HAIR"=NVL(:B1,"HAIR"))
   7 - access("WORK"=:B2)

SQL>
SQL>exec :b1 := 5;

PL/SQL procedure successfully completed.

SQL>exec :b2 := null ;

PL/SQL procedure successfully completed.

SQL>
SQL>select /*+ gather_plan_statistics */ count(*)
  2    from bitmap_t2
  3  where hair = NVL(:b1, hair)
  4    and work = NVL(:b2, work) ;

       COUNT(*)
---------------
            357

1 row selected.

SQL>
SQL>@allstats_last

PLAN_TABLE_OUTPUT
------------------
SQL_ID  16jagac5xq0r4, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*)   from bitmap_t2 where hair = NVL(:b1, hair)   and work
= NVL(:b2, work)

Plan hash value: 2153464131
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE                |           |      1 |      1 |      1 |00:00:00.04 |    2504 |    342 |
|   2 |   CONCATENATION                |           |      1 |        |    357 |00:00:00.04 |    2504 |    342 |
|*  3 |    FILTER                      |           |      1 |        |    357 |00:00:00.03 |    2504 |    342 |
|*  4 |     TABLE ACCESS FULL          | BITMAP_T2 |      1 |    357 |    357 |00:00:00.03 |    2504 |    342 |
|*  5 |    FILTER                      |           |      1 |        |      0 |00:00:00.01 |       0 |      0 |
|*  6 |     TABLE ACCESS BY INDEX ROWID| BITMAP_T2 |      0 |      5 |      0 |00:00:00.01 |       0 |      0 |
|*  7 |      INDEX RANGE SCAN          | T2_I6     |      0 |     32 |      0 |00:00:00.01 |       0 |      0 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(:B2 IS NULL)
   4 - filter(("HAIR"=NVL(:B1,"HAIR") AND "WORK" IS NOT NULL))
   5 - filter(:B2 IS NOT NULL)
   6 - filter("HAIR"=NVL(:B1,"HAIR"))
   7 - access("WORK"=:B2)

SQL>
SQL>exec :b1 := 5;

PL/SQL procedure successfully completed.

SQL>exec :b2 := 45 ;

PL/SQL procedure successfully completed.

SQL>
SQL>select /*+ gather_plan_statistics */ count(*)
  2    from bitmap_t2
  3  where hair = NVL(:b1, hair)
  4    and work = NVL(:b2, work) ;

       COUNT(*)
---------------
              5

1 row selected.

SQL>
SQL>@allstats_last

PLAN_TABLE_OUTPUT
------------------
SQL_ID  16jagac5xq0r4, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*)   from bitmap_t2 where hair = NVL(:b1, hair)   and work
= NVL(:b2, work)

Plan hash value: 2153464131
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE                |           |      1 |      1 |      1 |00:00:00.02 |      67 |      5 |
|   2 |   CONCATENATION                |           |      1 |        |      5 |00:00:00.02 |      67 |      5 |
|*  3 |    FILTER                      |           |      1 |        |      0 |00:00:00.01 |       0 |      0 |
|*  4 |     TABLE ACCESS FULL          | BITMAP_T2 |      0 |    357 |      0 |00:00:00.01 |       0 |      0 |
|*  5 |    FILTER                      |           |      1 |        |      5 |00:00:00.02 |      67 |      5 |
|*  6 |     TABLE ACCESS BY INDEX ROWID| BITMAP_T2 |      1 |      5 |      5 |00:00:00.02 |      67 |      5 |
|*  7 |      INDEX RANGE SCAN          | T2_I6     |      1 |     32 |     32 |00:00:00.01 |      35 |      0 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(:B2 IS NULL)
   4 - filter(("HAIR"=NVL(:B1,"HAIR") AND "WORK" IS NOT NULL))
   5 - filter(:B2 IS NOT NULL)
   6 - filter("HAIR"=NVL(:B1,"HAIR"))
   7 - access("WORK"=:B2)

Note how with each different set of bind variable values, the query produces a different result. But the plan remains the same (check the SQL_ID, child_number and plan hash values...they all match). You can also see how one set of the plan operations doesn't execute (note the 0's in the A-Rows, Buffers and Reads columns).

What this tells you is that the optimizer created a plan that would work dynamically. Well, sorta. Basically, the checks (step 3 and step 5) are made to see if :B2 IS NULL. If it is null, then step 4 executes. If it is not null, then steps 6-7 execute. This works fine for when you want the whole table (both bind values are null) or when you want to retrieve rows only where the work column equals some value. But, when you want to retrieve rows where you need to qualify both the work and hair columns or when you want to access rows for a particular hair value, the plan is sub-optimal.

So, OK...you're batting .500. Not bad I suppose. ;) But what if your SLA says you have to bat .900 or better? This plan just won't do. You need each different scenario to produce as optimal a plan as possible. How can you do that?

One way is to write your code to build the WHERE clause on the fly and use EXECUTE IMMEDIATE. The pseudo-code to do this might go something like this:

full_string := 'SELECT (rest of stuff).....';
if :b1 is null and :b2 is null then
   where_string := null;
elsif :b1 is not null and :b2 is not null then
   where_string := ' WHERE hair = :B1 AND work = :B2';
elsif :b1 is null and :b2 is not null then
   where_string := ' WHERE work = :B2';
elsif :b1 is not null and :b2 is null then
   where_string := ' WHERE hair = :B1';

full_string := full_string || where_string;
EXECUTE IMMEDIATE...

This way, you have 4 different statements and therefore 4 different plans. Each one (hopefully) optimal.

Another option would be to change your indexing strategy. Perhaps you could create a multi-column index using both columns. But, even with this option, you're not going to get "perfect" results. Depending on which column you decide to lead the index with, one of the index scans would likely have to do a skip scan or a full scan which wouldn't be as effective.

So, while the idea to use a single statement with conditional logic (in this case, NVL) and binds looks appealing, just remember that the plan you end up with may not provide the performance you want/need for all possible scenarios. If you use this approach, be prepared to create indexes properly so as to get the best possible coverage for all scenarios. I think the bottom line is that if you truly want dynamic SQL, then really use dynamic SQL. The attempt to simplify or to have a single statement that covers many different possible needs can cause performance to suffer for a some percentage (perhaps a significant %) of the scenarios.

MIN/MAX and Index Full Scans

We got a question at work today from a blogger in regard to his blog post entitled MIN and MAX Functions in a Single Query are Disastrous. If you check out his post, you'll see the details of his tests. Basically, he was wondering why the optimizer would choose to do a full table scan instead of using an INDEX FULL SCAN (MIN/MAX) operation for a query that contained both a MIN and a MAX function call on the same indexed column. I thought my answer to his question to us, about why the optimizer makes such a "bad" decision about the execution plan, would be a good topic for my post today.

Normally, if you write a query that uses only one call, either MIN or MAX, the optimizer will use the INDEX FULL SCAN (MIN/MAX) optimization to read only through to the first leaf block (for MIN) and to the last leaf block (for MAX). This is a great optimization that can save hundreds/thousands of block accesses over either a full table scan or even an INDEX FAST FULL SCAN. But, as Momen's tests show, if you execute a query that has both MIN and MAX in it, the optimizer refuses to use this optimization although it would seem to us (the feeble human brain) that the optimizer should know that it could do the operation twice, once to get the first block and once to get the last block, right?

But, the optimizer can't do this. The INDEX FULL SCAN (MIN/MAX) operation will do either an index scan for the first leaf block or an index scan for the last leaf block. It can not do both at the same time. Think about it... The code to handle the operation would have to have some fairly specific logic in it to handle the dual calls.

The question to then ask is why wouldn't Oracle choose to add that logic? Well, since there's actually an easy way to get the result you want using the current operation's code path, I'd say Oracle didn't want to invest development time into writing specific case code when the simple branch of MIN or MAX can work properly as long as the developer knows how to write their SQL to invoke it.

Here's what has to happen.
This query:

select min(empno) min_empno, max(empno) max_empno
from emp;

---------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |     1 |     5 |   442   (5)| 00:00:06 |
|   1 |  SORT AGGREGATE       |         |     1 |     5 |            |          |
|   2 |   INDEX FAST FULL SCAN| PK_EMP  |   917K|  4480K|   442   (5)| 00:00:06 |
---------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
      196  recursive calls
        0  db block gets
     1959  consistent gets
        1  physical reads
        0  redo size
      476  bytes sent via SQL*Net to client
      381  bytes received via SQL*Net from client
        2  SQL*Net roundtrips to/from client
        6  sorts (memory)
        0  sorts (disk)
        1  rows processed

becomes this:

select a.min_empno, b.max_empno
from
  (select min(empno) min_empno from emp) a,
  (select max(empno) max_empno from emp) b;

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     1 |    26 |     6   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |         |     1 |    26 |     6   (0)| 00:00:01 |
|   2 |   VIEW                       |         |     1 |    13 |     3   (0)| 00:00:01 |
|   3 |    SORT AGGREGATE            |         |     1 |     5 |            |          |
|   4 |     INDEX FULL SCAN (MIN/MAX)| PK_EMP  |   917K|  4480K|     3   (0)| 00:00:01 |
|   5 |   VIEW                       |         |     1 |    13 |     3   (0)| 00:00:01 |
|   6 |    SORT AGGREGATE            |         |     1 |     5 |            |          |
|   7 |     INDEX FULL SCAN (MIN/MAX)| PK_EMP  |   917K|  4480K|     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
        0  recursive calls
        0  db block gets
        6  consistent gets
        0  physical reads
        0  redo size
      476  bytes sent via SQL*Net to client
      381  bytes received via SQL*Net from client
        2  SQL*Net roundtrips to/from client
        0  sorts (memory)
        0  sorts (disk)
        1  rows processed

The same results could also be achieved by doing a UNION of the two separate statements. Either way, what is required is that the developer must understand how the optimizer utilizes the INDEX FULL SCAN (MIN/MAX) optimization and write their SQL to take full advantage of it.

Perhaps at some point (I haven't tested this in 11g yet to see if the optimizer behaves any differently) the optimizer will have code path to handle both calls simultaneously, but for now, the developer must provide the optimizer with syntax to allow the optimizer to formulate the optimal overall execution plan.

To me, this is just one more really good example why the database should not be "just a black box" to developers. Writing high performance SQL requires understanding how the Oracle optimizer works, not just an ability to put together a statement that gets the correct answer.

Thanks to Momen for his post and his question to Method R on this. It was good food for thought and nice blog fodder! :)

Addendum - July 11, 2008, 8:57am
Thanks to my colleague Ron who did the 11g test on this and he confirmed that the optimizer behavior has not changed. So, even in 11g, the SQL would need to be written to submit the MIN and MAX calls separately in order to get the optimal execution plan.

Optimizer NOWORKLOAD statistics

I am often asked about statistics collection. People want to know the magic formula for collecting them. When? How much? But, the truth is there isn't one formula, magic or otherwise, that works for everyone. Think about it... If there were, Oracle would have already implemented it, don't you think? But hope is still alive that someday the mystery about it all will be revealed.

I think Oracle is getting better and better in its attempts to automate the process of statistics collection. But there continues to be alot of questions and discussion on how to "do it right" in the meantime.

So, I thought I'd start trying to put together some of the questions I've received and do a little research and testing to see if I can't answer some of them here in the blog. Today, I wanted to start with a few words about system statistics, particularly about noworkload statistics. Starting with 10g, noworkload statistics are always available. Even if you delete them, if the database restarts, then they are automatically regathered.

The noworkload statistics are basically a few generic benchmarks used as performance measures for your system. The stats that are maintained are:

CPUSPEEDNW
The number of operations per second (in millions) that a CPU can process.
IOSEEKTIM
Average number of milliseconds needed to find data on the disk (default 10).
IOTFRSPEED
Average number of bytes per millisecond that can be transferred from the disk (default 4,096).

I've never touched the noworkload statistics on my laptop test database so I started by checking them to see what they were (they should be the defaults from when my database was first started). I used dbms_stats.get_system_stats to retrieve the values.

SQL> @getsysstats
iotfrspeed: 4096
ioseektm  : 10
cpuspeednw: 773.806

I then executed a single SQL statement and grabbed the execution plan. The statement was a 4 part UNION.


Then, I gathered noworkload statistics to see what they would be now. I started the collection and then executed several queries in another session while they were being collected.

SQL> exec dbms_stats.gather_system_statistics (gathering_mode => 'noworkload');

Using the gathering_mode parameter set to 'noworkload' will collect the values in about a minute. I'm not totally positive, but I believe the CPU speed is likely measured by executing some sort of calibration operation in a loop and the I/O is measured by doing different sized reads on several different datafiles.

These were my new values.

SQL> @getsysstats
iotfrspeed: 29126.045
ioseektm  : 14.856
cpuspeednw: 917.075

So, I ran the same SQL and checked the plan and you can see the difference the new noworkload statistics made.



I notice two key things: 1) the cost for this plan went down, but the estimated %CPU went up. This makes sense if you look at the new statistics. The iotfrspeed was significantly larger than my original value. This would mean that the optimizer now thinks it is much less expensive to transfer data from the disk than it was previously and reduce the cost of those operations.

In my example, the optimizer still chose the same plan it originally chose, but now, in comparison to any other plan choices, this plan is even more likely to be selected due to the much lower cost.

Several people have commented to me that they've chosen not to use system stats in their 10 databases because they had trouble initially when upgrading from 9i. But, what they often don't realize is that they are using system statistics no matter what. They are simply using the noworkload variety is they disable/delete the others. And, as you can see in this simple example, noworkload stats can change and cause the optimizer's calculations to change as as result. This could mean plans change for apparently no reason. This is one of those cases when someone swears "nothing has changed" but then they tell me that the database had just been restarted for some reason. When that happened, the noworkload stats could've been recomputed. Even if that were the only thing that changed, it might be enough to cause some plans to break.

So, as I've mentioned before in a previous post: instead of insisting nothing has changed, evaluate what might have changed that might not be on your radar. Something is bound to have changed somewhere.

More stats stuff to come in later posts.