Gather_Plan_Statistics Does Does Not Generate Basic Plan Statistics

Difference on explain plan and gather_plan_statistics

Yes, it looks confusing but is quite simple:

• Explain plan looks at a query and creates an theoretical execution plan.
• Gather_plan_statistics instructs the optimizer to keep notes during the actual execution, to be able to compare the theory with reality.

See this question for details.

A-Rows in Explain Plan

A-Rows is the total number of rows produced by all starts of that operation. But E-Rows is an estimate of the number of rows produced by a single start of an operation. Your previous comment is correct - you probably want to divide A-Rows by Starts to determine if the cardinality estimate is good.

In your example, Oracle estimated 1017 rows per operation run. But the actual rows per operation run was 31M (A-Rows) / 29568 (Starts) = 1048 (actual rows per start). Those numbers are very close.

(Although I don't understand why they are so different than the actual number of rows in the table, 10169. There is no * next to the Id in the execution plan so there is no obvious filtering on that table. But maybe some other operation is limiting results. Either way, this is enough information to imply that Oracle is estimating this operation well so maybe the problem is with another step.)

Test Schema

Create a table with 10 rows, then run a query that does a count on the table 10 times.

drop table test1 purge;
create table test1(a number);
insert into test1 select level from dual connect by level <= 10;
begin
    dbms_stats.gather_table_stats(user, 'TEST1');
end;
/

select /*+ gather_plan_statistics */ (select count(*) from test1 where a > b)
from (select - level b from dual connect by level <= 10);

select * from table(dbms_xplan.display_cursor(format => 'allstats last'));

Results:

The A-Rows in Id 2 is 100. This is 10 rows per full table scan multiplied by 10 runs.

(The plan below was slightly modified to fit the screen.)

Plan hash value: 2073232735

---------------------------------------------------------------------------------------
| Id  | Operation                     | Name  | Starts | E-Rows | A-Rows |   A-Time   |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |      1 |        |     10 |00:00:00.01 |
|   1 |  SORT AGGREGATE               |       |     10 |      1 |     10 |00:00:00.01 |
|*  2 |   TABLE ACCESS FULL           | TEST1 |     10 |      1 |    100 |00:00:00.01 |
|   3 |  VIEW                         |       |      1 |      1 |     10 |00:00:00.01 |
|   4 |   CONNECT BY WITHOUT FILTERING|       |      1 |        |     10 |00:00:00.01 |
|   5 |    FAST DUAL                  |       |      1 |      1 |      1 |00:00:00.01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("A">:B1)

Execution plan cost estimation

GLOBAL OR SESSION parameters
Oracle is setup with a set of initialisation parameters. Those will be used by default if nothing is specified to override them. They can be overriden by using an ALTER SESSION (just affects a single user) or ALTER SYSTEM (affects all users until Oracle is restarted) commands to change things at the session or system level or by using optimiser hints in the code. These can have an effect on the plans you see.

In relation to explain plan, a different Oracle database may have different initialisation parameters or have some session/system parameters set which could mean the SAME code behaves differently (by getting a different execution plan on one Oracle database compared to another Oracle database).

Also, as the execution plan is affected by the data chosen, it's possible that a query or package that runs fine in TEST never finishes in PRODUCTION where the volume of data is much larger. This is a common issue when code is not tested with accurate volumes of data (or at least with the table statistics imported from a production database if test cannot hold a full volume of production like data).

TRACING
The suggestions so far tell you how to trace an individual statement assuming you know which statement has a problem but you mention you have a shell script with several SQL statements.

If you are using a here document with a single call to SQL plus containin several SQL statements like this ...

 #!/bin/ksh
 sqlplus -S user/pass <<EOF
 set heading off
 BEGIN
      -- DO YOUR FIRST SQL HERE
      -- DO YOUR SECOND SQL HERE
 END;
 /

 EOF

... you can create a single oracle trace file like this

 #!/bin/ksh
 sqlplus -S user/pass <<EOF
 set heading off
 BEGIN
      ALTER SESSION SET EVENTS '10046 trace name context forever, level 8'
      -- DO YOUR FIRST SQL HERE
      -- DO YOUR SECOND SQL HERE
 END;
 /

 EOF

• Note the level 8 is for tracing with WAITS. You can do level 4 (bind variables), and level 12 (binds and waits) but i've always found the problem just with level 8. Level 12 can also take a lot longer to execute and in full size environments.

Now run the shell script to do a single execution, then check where your trace file is created in SQL PLUS using

  SQL> show parameter user_dump_dest
  /app/oracle/admin/rms/udump

Go to that directory and if no other tracing has been enabled, there will be a .trc file that contains the trace of the entire run of the SQL in your script.

You need to convert this to readable format with the unix tkprof command like this

  unix> tkprof your.trc ~/your.prf sys=no sort=fchela,exeela

Now change to your home directory and there will be a .prf file with the SQL statements listed in order of those that take the most execution time or fetch time to execute along with the explain plans. This set of parameters to tkprof allow you to focus on fixing the statements that take the longest and therefore have the biggest return for tuning.

Note that if your shell script runs several sqlplus commands, each one will create a separate session and therefore adding an ALTER SESSION statement to each one will create separate trace files.

Also, dont forget that it's easy to get lost in the detail, sometimes tuning is about looking at the overall picture and doing the same thing another way rather than starting by working on a single SQL that may gain a few seconds but in the overall scheme doesnt help to reduce the overall run time.

If you can, try to minimise the number of update statements as if you have one big table, it will be more efficient if you can do the updates in one pass of the table (and have indexes supporting the updates) rather than doing lots of small updates.

Maybe you can post the relevant parts of your script, the overall time it takes to run and the explain plan if you need more assistance.

---

Related Topics