Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Ramesh .

Asked: May 02, 2000 - 1:21 pm UTC

Last updated: July 28, 2021 - 2:40 am UTC

Version:

Viewed 10K+ times! This question is

You Asked

How should I use the EXPLAIN Plan for tuning of my SQL
Statements .Kindly advice me on the sequence of steps to be
followed . Some live examples could also be very helpful.

With Regards.
Ramesh.S



and Tom said...



If you are looking for the syntax of explain plan -- i will simply refer you to the SQL Language Manual, it is documented there.

I myself find it much easier to use autotrace in sqlplus. Consider this example:

scott@dev8i> set autotrace on
scott@dev8i> select count(*) from emp, dept where emp.deptno = dept.deptno;

COUNT(*)
----------
14


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=1 Bytes=26)
1 0 SORT (AGGREGATE)
2 1 MERGE JOIN (Cost=6 Card=11 Bytes=286)
3 2 SORT (JOIN) (Cost=3 Card=14 Bytes=182)
4 3 TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=14 Bytes=182)
5 2 SORT (JOIN) (Cost=3 Card=82 Bytes=1066)
6 5 TABLE ACCESS (FULL) OF 'DEPT' (Cost=1 Card=82 Bytes=1066)


Statistics
----------------------------------------------------------
475 recursive calls
27 db block gets
81 consistent gets
8 physical reads
0 redo size
1083 bytes sent via SQL*Net to client
697 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
9 sorts (memory)
0 sorts (disk)
1 rows processed


(to interpret the explain plan output -- see the server tuning manual). the above shows me that we did 2 full scans and merged the emp/dept tables together, then did an aggregate (the count) and finished. It took 81 logical reads (consistent gets) and hit the disk for 8 blocks and so on.

to get a quick start on setting up autotrace, see </code> http://asktom.oracle.com/~tkyte/article1/autotrace.html <code>


TKPROF and SQL_TRACE is another great tool. Consider:

scott@dev8i> alter session set sql_trace=true;
Session altered.

scott@dev8i> alter system set timed_statistics=true;
System altered.

scott@dev8i> select count(*) from dept,emp where dept.deptno = emp.deptno;

COUNT(*)
----------
14


$ ls -t $ORACLE_HOME/admin/$ORACLE_SID/udump/*_ora_*.trc | head -1
/export/home/oracle8i/admin/ora8idev/udump/ora8idev_ora_13400.trc

$ tkprof /export/home/oracle8i/admin/ora8idev/udump/ora8idev_ora_13400.trc tmp.txt explain=scott/tiger

TKPROF: Release 8.1.5.0.0 - Production on Sun Feb 27 09:19:20 2000

(c) Copyright 1999 Oracle Corporation. All rights reserved.

$ vi tmp.txt


and in the report we'll find:


...
select count(*)
from
dept,emp where dept.deptno = emp.deptno


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 2 24 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.01 0 2 24 1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 34 (SCOTT)

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
14 MERGE JOIN
15 SORT JOIN
14 TABLE ACCESS FULL EMP
14 SORT JOIN
4 TABLE ACCESS FULL DEPT
....

which is even more informative then the autotrace. it not only shows the plan but execution times, number of rows flowing through each step of the plan and so on.


see the server tuning manual for more info on sql_trace and timed_statistics....



Rating

  (154 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

autotrace

mo, December 05, 2002 - 10:59 am UTC

Tom:

when i do this in GUI sqlplus I get:

SQL> set autotrace on;
SP2-0613: Unable to verify PLAN_TABLE format or existence
SP2-0611: Error enabling EXPLAIN report

do i have to install anything in database 

Tom Kyte
December 05, 2002 - 11:38 am UTC

to get a quick start on setting up autotrace, see

</code> http://asktom.oracle.com/~tkyte/article1/autotrace.html <code>


that tells you what you need to do.

You set sys=no ?

Robert, December 05, 2002 - 1:13 pm UTC

$ tkprof /export/home/oracle8i/admin/ora8idev/udump/ora8idev_ora_13400.trc tmp.txt explain=scott/tiger


Tom, do you set sys=no or do you find the sys stuff any useful ?

Tom Kyte
December 05, 2002 - 3:06 pm UTC

depends, it can be useful -- sometimes it is just in the way.

Consider this recent issue that might have been missed with sys=no:

...

select c.name, u.name 
from
 con$ c, cdef$ cd, user$ u  where c.con# = cd.con# and cd.enabled = :1 and 
  c.owner# = u.user#


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse   275635    128.33     356.95          0          0          0           0
Execute 275635    130.44     300.40          0          0          0           0
Fetch   275635    128.62     600.91          0    2756350          0      275635
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   826905    387.39    1258.26          0    2756350          0      275635


does this means that 275635 soft parses??? and can you tell me y this is 
happening?

tnx and more power.

Cheers. 


Followup:

This is recursive SQL -- sql that is done on behalf of your SQL.

In this case, it looks like the recursive SQL we use to retrieve a constraint 
name upon unique key violation (ora-00001).  Consider:


ops$tkyte@ORA920.US.ORACLE.COM> create table t ( x int primary key );
Table created.

ops$tkyte@ORA920.US.ORACLE.COM> insert into t values ( 1 );
1 row created.

ops$tkyte@ORA920.US.ORACLE.COM> alter session set sql_trace=true;
Session altered.

ops$tkyte@ORA920.US.ORACLE.COM> insert into t values ( 1 );
insert into t values ( 1 )
*
ERROR at line 1:
ORA-00001: unique constraint (OPS$TKYTE.SYS_C004571) violated


ops$tkyte@ORA920.US.ORACLE.COM> insert into t values ( 1 );
insert into t values ( 1 )
*
ERROR at line 1:
ORA-00001: unique constraint (OPS$TKYTE.SYS_C004571) violated


and the tkprof has:

select c.name, u.name
from
 con$ c, cdef$ cd, user$ u  where c.con# = cd.con# and cd.enabled = :1 and
  c.owner# = u.user#


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0         16          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6      0.00       0.00          0         16          0           2


So, one approach to decreasing could be changing the logic.  Suppose you are 
doing a "merge" -- you have logic like this:


   for x in ( set of records )
   loop
        begin
              insert into table 
        exception
              when dup_val_on_index then update ...
        end
   end loop;

turn it into:

   for x in ( set of records )
   loop
       update ...
       if ( sql%rowcount = 0 )
       then
            insert ....
       end if;
   end loop


or avoid the constraint by doing something like:


   insert into t 
   select :pk, :bv1, :bv2, :bv3 from dual
    where not exists (select 1 from T where pk = :pk );
  
   if ( sql%rowcount = 0 )
   then
      duplication record logic here or just raise dup_val_on_index
   end if;


In short -- it is the result of 275,635 unique key violations in one program 
execution. 
 

Error enabling Explain Plan

Aamir Abbas, May 13, 2003 - 4:14 am UTC

I've installed 9i Enterprise in Typical mode -- and I assume that Typical will install almost complete package).

I selected to create General Purpose database with pre-defined set of objects....

When I use the AUTOTRACE options for query optimization, I get the following errors.

SQL> set autotrace on explain
SP2-0613: Unable to verify PLAN_TABLE format or existence
SP2-0611: Error enabling EXPLAIN report


SQL> set autotrace traceonly
SP2-0613: Unable to verify PLAN_TABLE format or existence
SP2-0611: Error enabling EXPLAIN report
SP2-0618: Cannot find the Session Identifier.  Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report


How can I enable this TRACE, EXPLAIN PLAN, STATISTICS etc in my newly built database?

Also kindly guide.. if I want to rebuild my database with predefined set of objects.... Lets say if I get my database inconsistent or corruprt.. or for any other reason... I want to rebuilt the GENERAL PURPOSE database with predefined set of objects.. How can I do so?

Thanks for your help.
 

Tom Kyte
May 13, 2003 - 7:22 am UTC


see

</code> http://asktom.oracle.com/~tkyte/article1/autotrace.html <code>

trace and statistics are always "enabled". explain plan needs the plan_table, ?/rdbms/admin/utlxplan.sql




you would delete and create a new database -- use DBCA, it'll do that for you

Explain Plan + DBCA

Aamir Abbas, May 14, 2003 - 1:43 pm UTC

Your link worked. Thanks. But sorry for being still a bit asky. :)

<quote>
trace and statistics are always "enabled".  explain plan needs the plan_table, 
?/rdbms/admin/utlxplan.sql
</quote>

I've just installed 9i, and it's not "enabled by default". Also I've 8.1.6 on other server, and it also says the same thing:

SQL> set autotrace on
SP2-0613: Unable to verify PLAN_TABLE format or existence
SP2-0611: Error enabling EXPLAIN report
....

DBCA
====
<quote>
you would delete and create a new database -- use DBCA, it'll do that for you 
</quote>

I found no files with this name on my disk. See...

D:\>dbca
'dbca' is not recognized as an internal or external command,
operable program or batch file.

D:\>path
PATH=d:\progra~1\jdk13\bin;D:\Oracle\OraDev6i\bin;D:\Oracle\Ora816\bin;D:\Progra
m Files\Oracle\jre\1.1.7\bin;D:\WINNT\system32;D:\WINNT;D:\WINNT\System32\Wbem;D
:\Program Files\Common Files\Network Associates\VirusScan Engine\4.0.xx\;E:\Orac
le\Ora816\orb\bin;E:\Oracle\OraDev\BIN;D:\Oracle\OraDev\BIN;D:\Oracle\Ora816\orb
\bin;D:\Oracle\OraDev6i\jdk\bin;D:\Program Files\Rational\common;D:\Program File
s\Rational\jintegra\bin;D:\Program Files\Rational\XDE\Addins\CompareMerge;

D:\>dir dbca*.* /a/s/w
 Volume in drive D has no label.
 Volume Serial Number is 0CAD-CA0B

 Directory of D:\Oracle\Ora816\assistants

[dbca]
               0 File(s)              0 bytes

 Directory of D:\Oracle\Ora816\classes\oracle\sysman\ocmhelp

dbcapturewiz32x32.gif
               1 File(s)            391 bytes

     Total Files Listed:
               1 File(s)            391 bytes
               1 Dir(s)     297,930,752 bytes free


What's DBCA? Is it an executable or a .sql? Where to find it? Can you briefly hint the procedure of creating new database with predefined options/schemas?

Thanks a lot for your patience. :) 

location of tkprof

sgs_ocp, May 27, 2003 - 2:08 am UTC

Hello Tom,
I could not find the tkprof executable in our installation

win2000 / oracle 9.0.1

Do i have to select something while installation?
As we have gone for default installation.
Please guide.




Tom Kyte
May 27, 2003 - 7:44 am UTC

goto metalink and get the patch. They forgot tkprof.exe on windoze early releases.


got sp2 error when set autotrace on in scott account

David, May 27, 2003 - 2:38 pm UTC

Tom,

In your original example you used scott account. I tried to use scott as well but I got the error as showing below. 

PLUSTRACE role is granted to Scott and PLAN_TABLE is created by Scott via utlxplan.sql. 

By the way, set autotrace on in my dba account worked.

Enter user-name: scott
Enter password: 

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.4.0 - 64bit Production

SQL> drop table plan_table;

Table dropped.

SQL> @/app/oracle/product/817-64/rdbms/admin/utlxplan.sql

Table created.

SQL> set autotrace on
SP2-0618: Cannot find the Session Identifier.  Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report
 

Tom Kyte
May 27, 2003 - 5:39 pm UTC

don't think the plustrace role is correct then.


It found the plan table.
It did not find the v$ tables that plustrace gives you access to.

so plan table was OK.
v$ not

something wrong with your plustrace role. consider this example:


ops$tkyte@ORA817DEV> drop user a cascade;
User dropped.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> grant connect, plustrace to a identified by a;

Grant succeeded.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> select table_name, privilege from dba_tab_privs where grantee='PUBLIC'
  2  and table_name in ( 'V_$SESSION', 'V_$SESSTAT', 'V_$STATNAME' );

no rows selected

<b>shows the v$ stuff we need access to isn't granted to public on my system or anything..</b>

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> select table_name, privilege from dba_tab_privs where grantee='PLUSTRACE' and table_name like 'V%';

TABLE_NAME                     PRIVILEGE
------------------------------ ----------------------------------------
V_$SESSION                     SELECT
V_$SESSTAT                     SELECT
V_$STATNAME                    SELECT

<b>that is what plustrace gives access to</b>

ops$tkyte@ORA817DEV> select * from dba_sys_privs where grantee='PUBLIC';

no rows selected

<b>and no funky "select any table" privs on public or anything like that</b>

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> @connect a/a
ops$tkyte@ORA817DEV> set termout off
a@ORA817DEV> REM GET afiedt.buf NOLIST
a@ORA817DEV> set termout on
a@ORA817DEV> select * from session_roles;

ROLE
------------------------------
PLUSTRACE
CONNECT

<b>only those two roles are on...</b>

a@ORA817DEV> set autotrace on
a@ORA817DEV> select * from dual;

D
-
X


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'DUAL'




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

a@ORA817DEV> set autotrace off

<b>and that is what it takes to get autotrace working</b>
 

AUTOTRACE ON EXPLAIN

A reader, September 10, 2003 - 12:40 pm UTC

If set autotrace on explain and tried running a query using USER_ data dictionary tables. I kept getting the error

Execution Plan
----------------------------------------------------------
ERROR:
ORA-01031: insufficient privileges


I get an explain plan if I query any of the application tables. So, is there a restriction on generating an explain plan when dealing with data dictionary tables?




Tom Kyte
September 10, 2003 - 8:07 pm UTC



you need to be able to see the base tables upon which the view is constructed to explain it

you don't have that access, so you cannot see the plans.

UTLXPLAN.SQL

Kunjan Mehta, October 27, 2003 - 4:27 am UTC

Hi, I tried to locate the UTLXPLAN.SQL file . as per documentation you suggested, (it is located under $ORACLE_HOME/rdbms/admin on Unix.) The OS for me is windows and not Unix. Still, i checked in $ORACLE_HOME/rdbms/admin but did not find the file.
(Q 1) would it be under some other name in windows?

I searced for UTLXPLAN.sql in entire ORACLE_HOME (which is directory orahome1 in our root directory) but didnot find such file.

i also tried to check the distribution media (2 CDs) from which we had installed Oracle9iDS (we have installed entire 9iDS) but search didn't yield any positive results here also. Probably it might be compressed in any of the jar file.
(Q 2) Can you please guide me as to where can i find the script of UTLXPLAN.SQL?
(Q 3) or if we missed some component while installing 9iDS, what component should be install which will install such utility SQL script files?

Tom Kyte
October 27, 2003 - 7:46 am UTC

utlxplan.sql is in rdbms/admin on windows and on all ports.


show us a cut and paste of you being in the oracle_home/rdbms/admin directory and typing:

dir utlx*.*




dir utlx*.*

Kunjan Mehta, October 28, 2003 - 4:06 am UTC

giving this command gives following result.

---------------------

C:\ORAHOME1\RDBMS\ADMIN>dir utlx*.*
Volume in drive C has no label.
Volume Serial Number is 78A5-ED9A

Directory of C:\ORAHOME1\RDBMS\ADMIN

File Not Found

C:\ORAHOME1\RDBMS\ADMIN>

------------------
As i had written in my query, i have already searched C:\orahome1\rdbms\admin. (also checked the DBS, BIN etc .directory before giving a full scan of directory c:\orahome1 for file utlxplan.sql, u*.sql and *.sql, but i did not find utlxplan.sql in entire directory tree orahome1.

If I get any URL from where i can download this script, that also would do good.

Rgds...

Tom Kyte
October 28, 2003 - 8:00 am UTC

you do not have a database install there

there would be lots of utlx*.* files in there.


so, you have none of the utl scripts. find a database install.

Here is a plan table of 9iR2

create table PLAN_TABLE (
statement_id varchar2(30),
timestamp date,
remarks varchar2(80),
operation varchar2(30),
options varchar2(255),
object_node varchar2(128),
object_owner varchar2(30),
object_name varchar2(30),
object_instance numeric,
object_type varchar2(30),
optimizer varchar2(255),
search_columns number,
id numeric,
parent_id numeric,
position numeric,
cost numeric,
cardinality numeric,
bytes numeric,
other_tag varchar2(255),
partition_start varchar2(255),
partition_stop varchar2(255),
partition_id numeric,
other long,
distribution varchar2(30),
cpu_cost numeric,
io_cost numeric,
temp_space numeric,
access_predicates varchar2(4000),
filter_predicates varchar2(4000));


Kunjan try this:

David, October 28, 2003 - 12:54 pm UTC

You are not interpreting oracle home correctly. You want to enter dir %ORACLE_HOME%\rdbms\admin\utlx*.* on a windows system. If you don't find anything, you don't have oracle installed!

Hate it when I post without testing

David, October 28, 2003 - 1:00 pm UTC

For %oracle_home% to translate properly, you need to be in sqlplus. To run utlxplan.sql on a windows sytem simply enter @%oracle_home%\rdbms\admin\utlxplan at the sqlplus prompt (make sure you are logged in with an account with correct privileges).

utlxplan.sql

Kunjan Mehta, October 30, 2003 - 12:05 am UTC

Hi Tom,

Thank you very much for the script. 

----
you do not have a database install there 
----
looking to your answer..... I was looking at a wrong place. I was trying to locate the script on the client machine, in rdbms/admin directory with intention to run it from client machine through SQL Plus - SYS account. That's my mistake. I checked on the server and found UTLXPLAN.sql and all other scripts. 

I am using Oracle 9i Enterprise Release 9.0.1. Is it same as 9iR1? also i run script given by you (from my machine through sys as sysdba) and it worked well. I also checked by explaining plan for a couple of scripts. But the script you gave (9iR2) is slightly different than the script i found on our database server (your script have 2 more fields - access_predicates varchar2(4000) & filter_predicates varchar2(4000)). should i rerun the script i found on the server (i.e. for the version it's intended for) or i can continue using script given by you. Any advantage/disadvantage/mismatch?

Thanks a lot once again.

Regards

----------------------------
Hi David, 
If you read my query, you would be knowing that i'm not interpreting oracle home incorrectly. on my machine the oracle home is 'C:\Orahome1'. Still... I tried running the command given by you.
---
SQL*Plus: Release 9.0.1.3.0 - Production on Wed Oct 29 09:20:51 2003

(c) Copyright 2001 Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.0.1.2.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production

SQL> connect sys as sysdba
Enter password: *****************
Connected.
SQL> @%oracle_home%\rdbms\admin\utlxplan
SP2-0310: unable to open file "C:\OraHome1\rdbms\admin\utlxplan.sql"
SQL> 
---
The problem was not in the oracle home. it was the machine, on which i was locating the script.
anyway, thanks for the reply. 

Tom Kyte
October 30, 2003 - 7:08 am UTC

i gave you 9iR2's plan table.

you are running 9iR1 -- different plan table. use the one from your install

exec plan generation hangs

j., November 15, 2003 - 11:08 am UTC

hi tom,

i've to analyze a performance issue within an existing application developed some years ago by some consultants.

there are several views, building up different layers, one on top of another. these views provide a client interface and are therefore forced by hints to get first rows fast. furthermore nested loops and ordered are enforced many times and outer joins are frequently used. so in general these views are at least fast enough as long as they are used the *exact* way they are designed for, using certain predicates ...

but since we have now the additional need to use such views in "all rows mode" (or to join them) we are facing really bad performance in most cases.

i generated an execution plan for one of those uppermost (top level) views using the predicates it was designed for. the plan contained nearly 100 nested loops (almost only index unique scans). in order to measure the execution time for one of my all rows use cases without having a chance to get trace files from server side i've "wrapped" the view statement like this:

select count(*) from (select RowNum, a.* from <slow view> a where <usual view parameters>)

the intention of rownum was to "materialize" the result set (all rows hint didn't kick in), and count(*) should minimize the generated data to be transferred to the client side to zero out network overhead.

unfortunately this last statement didn't terminate at all, and in addition i can't get an execution plan for it anymore although i set optimizer max permutations to 5 (!).

so, i've now three questions at a time: is my plan to measure "net" execution time appropriate at all, and what could have caused the statement to hang after applying rownum and count? how can one find out the reason in such cases?

can you please help?

Tom Kyte
November 15, 2003 - 11:40 am UTC

well, the query probably did not "hang", it was just taking a long time. if you have 9i, the plan for the executing query will be in v$sql_plan, you can query it from there.


set autotrace traceonly explain -- does that work with this?

getting the TRACE FILE is going to be very very relevant.

tuning a query with 100+ nested loops joins is going to be a "tad difficult"



traceonly was *THE* answer

j, November 15, 2003 - 2:39 pm UTC

the "only" problem was to get statistics generated. the execution plan looks very simlar to the previous (although i haven't inspected each and every of those 100 nested loops so far) - except the costs computed.

what could be the impact of count and rownum? it takes some time to loop through the result set of <slow view> in pl/sql, but at least this loop reaches its end. do you have any ideas why pure sql doesn't work as well?

Tom Kyte
November 16, 2003 - 10:33 am UTC

you are waiting for the very very very last row to be returned.

you have queries that have been hinted to death to get the first row returned.

meaning, the last row could be days or weeks in coming. See

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:6749454952894#6760861174154 <code>

for a classic case.....

1 hour using nested loops
10 seconds using hash joins

your views are not at all appropriate for what you are trying to use them for. Suggestion:

a) search for getaview on this site
b) suck their views out of the database
c) rename them all (put xx_ on the front)
d) delete the hints
e) query using the xx_ views with ALL_ROWS optimization and see what happens then

9i R2 explain plan

A reader, January 03, 2004 - 3:38 pm UTC

Hi

I am using dbms_xplan to see the execution plans. There is something which make someone wonder, sometimes it shows me COST %CPU in COST column but most of time not. How so? I dont gather system statistics at all

Tom Kyte
January 03, 2004 - 4:54 pm UTC

optimizer_index_cost_adj can "accidently" turn it on

ops$tkyte@ORA9IR2> create table t ( x int primary key );
Table created.
 
ops$tkyte@ORA9IR2> insert into t select rownum from all_users;
44 rows created.
 
ops$tkyte@ORA9IR2> analyze table t compute statistics;
Table analyzed.
 
ops$tkyte@ORA9IR2> delete from plan_table;
2 rows deleted.
 
ops$tkyte@ORA9IR2> explain plan for select * from t where x = 55;
Explained.
 
ops$tkyte@ORA9IR2> select * from table( dbms_xplan.display );
 
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
 
---------------------------------------------------------------------
| Id  | Operation            |  Name        | Rows  | Bytes | Cost  |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT     |              |     1 |     2 |     1 |
|*  1 |  INDEX UNIQUE SCAN   | SYS_C004027  |     1 |     2 |       |
---------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("T"."X"=55)
 
Note: cpu costing is off
 
14 rows selected.
 
ops$tkyte@ORA9IR2> alter session set optimizer_index_cost_adj = 50;
Session altered.
 
ops$tkyte@ORA9IR2> delete from plan_table;
2 rows deleted.
 
ops$tkyte@ORA9IR2> explain plan for select * from t where x = 55;
Explained.
 
ops$tkyte@ORA9IR2> select * from table( dbms_xplan.display );
 
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
 
--------------------------------------------------------------------------
| Id  | Operation            |  Name        | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |              |     1 |     2 |     2  (50)|
|*  1 |  INDEX UNIQUE SCAN   | SYS_C004027  |     1 |     2 |            |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("T"."X"=55)
 
12 rows selected.
 

oh I see

A reader, January 03, 2004 - 5:32 pm UTC

No wonder, I was playing with these optimizer parameters... Btw what is a good value for optimizer_index_cost_adj and optimizer_index_caching for OLTP? 20/80, 30/70?

Tom Kyte
January 03, 2004 - 6:27 pm UTC

yes, those are both "valid", one it just "stronger than the other"

A quick one

OracleO, February 04, 2004 - 1:59 am UTC

Hi Tom,

I guess this might be out of context but should be a quick one for you to answer.

The output after setting autotrace on gets wraaped to next line. is there anyway for it not to wrap.

7 6 INDEX (RANGE SCAN) OF 'INDX_FK_PERMISSION_PART <== wraps and goes to next line
Y' (NON-UNIQUE) (Cost=3 Card=1)

8 3 TABLE ACCESS (BY INDEX ROWID) OF 'PROCESS' (Cost=1 C
ard=1064 Bytes=45752)


Thanks,

Tom Kyte
February 04, 2004 - 7:22 am UTC

column plan_plus_exp format a100


(if you have my book Effective Oracle by Design -- chapter 2 has all of the settings you can set for autotrace)

Explain Plan on Stored procedure

Wor, February 23, 2004 - 4:07 pm UTC

Hi Tom,
I have oracle 8.1.7 windows 2000.
How can I get the explain plan of stored procedure.
I know to generate the explain plan for SQL's but I am not able to generate the Plan for stored procedures.

The reason:
If I extract the SQL from procedure with harcoded IN Clause
I am able to generate the explain plan.
but in stored procedure if I have 2 where conditions with syntax e.g. select ..where ..IN (SELECT * FROM THE (SELECT CAST(..) FROM DUAL))) and ...IN (SELECT * FROM THE (SELECT CAST(..) FROM DUAL)))

The stored procedure executes for couple of minutes and then gives the same results if I harcode the IN clause.

My guess is when I use 2 SELECT CAST syntax to handle IN clause the procedure ignores all the indexes, thats why I want to generate the explain plan for the stored procedure and not extract the SQL from procedure to generate explain plan.

Thanks in advance
Wor

Tom Kyte
February 23, 2004 - 5:09 pm UTC

alter session set sql_trace=true; and use tkprof.

different explain plans for same query using two different tools!!!??

P, April 09, 2004 - 11:07 am UTC

hi tom,
I am little baffled here and need your expert explanation 
I executed explain plan for this query using sqlplus and sql navigator both these tools give different plans and I couldn’t understand why 

Here is the query using sqlplus 
10:44:58 SQL> explain plan for SELECT
10:45:29   2    D_CUST_MBN_BTN_DETAIL.MBN,
10:45:29   3    A_TO_CUST_PROD.CUST_PROD_START_DATE,
10:45:29   4    decode(A_TO_CUST_PROD.CUST_PROD_END_DATE,to_date('12-31-9999','mm/dd/yyyy'),to_Date(
NULL),A_TO_CUST_PROD.CUST_PROD_END_DATE)
10:45:29   5  FROM
10:45:29   6    D_CUST_MBN_BTN_DETAIL,
10:45:29   7    D_CUST_PROD  A_TO_CUST_PROD
10:45:29   8  WHERE
10:45:29   9    ( A_TO_CUST_PROD.CUST_MBN_BTN_DETAIL_KEY=D_CUST_MBN_BTN_DETAIL.CUST_MBN_BTN_DETAIL_K
EY  )
10:45:29  10    AND  (
10:45:29  11    A_TO_CUST_PROD.CUST_PROD_DESC  =  '1+'
10:45:29  12    )
10:45:30  13  ;

Explained.

10:45:32 SQL> select * from table (dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------
| Id  | Operation            |  Name                  | Rows  | Bytes |TempSpc| Cost  |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                        |  3092K|   147M|       | 20713 |
|*  1 |  HASH JOIN           |                        |  3092K|   147M|   123M| 20713 |
|*  2 |   TABLE ACCESS FULL  | D_CUST_PROD            |  3092K|    88M|       |  4967 |
|   3 |   TABLE ACCESS FULL  | D_CUST_MBN_BTN_DETAIL  |  7551K|   144M|       | 11987 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
---------------------------------------------------

   1 - access("A_TO_CUST_PROD"."CUST_MBN_BTN_DETAIL_KEY"="D_CUST_MBN_BTN_DETAIL"."CUST_
              MBN_BTN_DETAIL_KEY")
   2 - filter("A_TO_CUST_PROD"."CUST_PROD_DESC"='1+')

Note: cpu costing is off

18 rows selected.


And now using sql navigator
  SQL Statement from editor:
   
   
  SELECT
    D_CUST_MBN_BTN_DETAIL.MBN,
    A_TO_CUST_PROD.CUST_PROD_START_DATE,
    decode(A_TO_CUST_PROD.CUST_PROD_END_DATE,to_date('12-31-9999','mm/dd/yyyy'),to_Date(NULL),A_TO_CUST_PROD.CUST_PROD_END_DATE)
  FROM
    D_CUST_MBN_BTN_DETAIL,
    D_CUST_PROD  A_TO_CUST_PROD
  WHERE
    ( A_TO_CUST_PROD.CUST_MBN_BTN_DETAIL_KEY=D_CUST_MBN_BTN_DETAIL.CUST_MBN_BTN_DETAIL_KEY  )
    AND  (
    A_TO_CUST_PROD.CUST_PROD_DESC  =  '1+'
    )
  ------------------------------------------------------------
    
  Statement Id=0   Type=
  Cost=2.64023809101798E-308  TimeStamp=09-04-04::11::03:01
  
    SELECT STATEMENT  RULE
    NESTED LOOPS
           (2)  TABLE ACCESS FULL DM_DIM.D_CUST_PROD  [Analyzed] 
           (2)   Blocks: 20,731 Est. Rows: ?? of 9,277,540 
                Tablespace: DATA02
           (4)  TABLE ACCESS BY INDEX ROWID DM_DIM.D_CUST_MBN_BTN_DETAIL  [Analyzed] 
           (4)   Blocks: 50,038 Est. Rows: ?? of 7,551,480 
                Tablespace: DATA01
               (3)  UNIQUE INDEX UNIQUE SCAN DM_DIM.PK_D_CUST_MBN_BTN_DETAIL  [Analyzed]
 

Tom Kyte
April 09, 2004 - 1:20 pm UTC

one use the brillant plan from the CBO, sql navigator seems to be turning on the RBO explicitly.

one use CBO, the other RBO -- you need to figure out how to get sql navigator to stop doing that.

different explain plans for same query using two different tools!!!??

P, April 09, 2004 - 1:56 pm UTC

Thanks tom
I didn’t see it my bad...you know its Friday :)...some times you just need fresh pair of eyes


Everything has been great so far.

Mr. Ryan, April 09, 2004 - 3:16 pm UTC

I am a pretty experienced developer, with a few years on App Dev under my belt.

Recently I have become a DBA, about six months ago.

I have been to one of your seminars and your knowledge is impressive.

I read that you are not certified at all, and that caught me by surprise.

I would like to know more about your career, and when you started with Oracle and especially in what capacity you were hired.

If there is a link to this already I failed to find it.

Re: Tom's Start

Jeff, April 09, 2004 - 3:51 pm UTC

A quick search for "mentor" turned this up. I remembered the question that you were speaking of.

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:13253348444918 <code>

how does it work ?

A reader, June 03, 2004 - 1:36 pm UTC

I have tables,

institution (inst_code (pk),inst_name )
inst_role (instr_code (pk),instr_name )
inst2instr ( (inst_code,instr_code)(pk),map_is_live)


i ran the query

select i.inst_code
from institution i, inst_role ir,inst2instr i2ir
where i2ir.inst_code = i.inst_code
and i2ir.instr_code = ir.instr_code;

-- no order by clause

as i.inst_code,and ir.instr_code is pk in their respecitive tables
also in inst2instr table (inst_code,instr_code) is a composit pk.

the expain plan showed me that

1.) if I select i.inst_code only then there is no full tablescane

2.) but if i select i.inst_code,i.inst_name it shows me the
institution table is full scanned.

why is that case 2 doing full tablescan. as you already have the inst_Code(pk id) to can't oracle directly go that block and get the row why is it doing full table search ?
please explain

Tom Kyte
June 03, 2004 - 2:39 pm UTC

(why is it so hard to supply test scripts like i do???  simple create tables so I can just cut and paste and get right at it......)

ops$tkyte@ORA9IR2> drop table institution;
 
Table dropped.
 
ops$tkyte@ORA9IR2> create table institution (inst_code int primary key,inst_name int );
 
Table created.
 
ops$tkyte@ORA9IR2> drop table inst_role;
 
Table dropped.
 
ops$tkyte@ORA9IR2> create table inst_role        (instr_code int primary key,instr_name int );
 
Table created.
 
ops$tkyte@ORA9IR2> drop table inst2instr;
 
Table dropped.
 
ops$tkyte@ORA9IR2> create table inst2instr ( inst_code int,instr_code int,map_is_live int, primary
  2  key(inst_code,instr_code));
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> set autotrace traceonly explain
ops$tkyte@ORA9IR2> select i.inst_code
  2  from institution i, inst_role ir,inst2instr i2ir
  3  where i2ir.inst_code = i.inst_code
  4  and   i2ir.instr_code = ir.instr_code;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   NESTED LOOPS
   2    1     NESTED LOOPS
   3    2       TABLE ACCESS (FULL) OF 'INST2INSTR'
   4    2       INDEX (UNIQUE SCAN) OF 'SYS_C007152' (UNIQUE)
   5    1     INDEX (UNIQUE SCAN) OF 'SYS_C007151' (UNIQUE)
 
 
 
ops$tkyte@ORA9IR2> select i.inst_code, i.inst_name
  2  from institution i, inst_role ir,inst2instr i2ir
  3  where i2ir.inst_code = i.inst_code
  4  and   i2ir.instr_code = ir.instr_code;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   NESTED LOOPS
   2    1     NESTED LOOPS
   3    2       TABLE ACCESS (FULL) OF 'INST2INSTR'
   4    2       INDEX (UNIQUE SCAN) OF 'SYS_C007152' (UNIQUE)
   5    1     TABLE ACCESS (BY INDEX ROWID) OF 'INSTITUTION'
   6    5       INDEX (UNIQUE SCAN) OF 'SYS_C007151' (UNIQUE)
 

So, I guess you'll need to clarify or at least show us how to reproduce. 

Explain plan

A reader, July 06, 2004 - 1:42 pm UTC

Hi

I am using a select statement to query data from a remote database using dblinks. When I do an explain plan on the select statement it gives me the following results in which the object name is null for each entry. The select statement contains an inline view. But if I take out the inline view and do an explain plan it displays all the object names. I don't know what to make out from the following results. Can you please let me know when the object name is null in an explain plan. The interesting thing is that the query is fast considering the number of rows it returns.

Thanks in advance.

SQL> @c:\utlxpls.sql

Plan Table
--------------------------------------------------------------------------------
| Operation                 |  Name    |  Rows | Bytes|  Cost  | Pstart| Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT          |          |    22K|   12M|   7845 |       |       |
|  WINDOW SORT              |          |    22K|   12M|   7845 |       |       |
|   HASH JOIN OUTER         |          |    22K|   12M|   1709 |       |       |
|    HASH JOIN OUTER        |          |    22K|   10M|   1424 |       |       |
|     HASH JOIN             |          |    22K|    9M|   1164 |       |       |
|      VIEW                 |          |     3K|   64K|     32 |       |       |
|       SORT UNIQUE         |          |     3K|  105K|     32 |       |       |
|        REMOTE             |          |     4K|  137K|      3 |       |       |
|      HASH JOIN            |          |    35K|   14M|   1047 |       |       |
|       REMOTE              |          |    98 |    1K|      2 |       |       |
|       HASH JOIN           |          |    58K|   22M|   1042 |       |       |
|        REMOTE             |          |    95 |    1K|      2 |       |       |
|        HASH JOIN OUTER    |          |    98K|   36M|   1036 |       |       |
|         HASH JOIN         |          |    98K|   30M|    222 |       |       |
|          HASH JOIN OUTER  |          |    46 |    9K|      6 |       |       |
|           HASH JOIN OUTER |          |    46 |    6K|      4 |       |       |
|            REMOTE         |          |    46 |    3K|      2 |       |       |
|            REMOTE         |          |     4 |  272 |      2 |       |       |
|           REMOTE          |          |     4 |  272 |      2 |       |       |
|          REMOTE           |          |    85K|    9M|    213 |       |       |
|         REMOTE            |          |     4 |  272 |      2 |       |       |
|     REMOTE                |          |   115 |    6K|      8 |       |       |
|    REMOTE                 |          |     4 |  272 |      2 |       |       |
-------------------------------------------------------------------------------- 

Tom Kyte
July 06, 2004 - 2:27 pm UTC

what does autotrace traceonly explain do for you?

Look in the OTHER column

Gary, July 06, 2004 - 8:11 pm UTC

"I am using a select statement to query data from a remote database using dblinks.
Can you please let me know when the object name is
null in an explain plan."

I find that, where the main query is broken down to some bits performed locally and other bits performed on the remote db, the query sent over the DB_LINK comes out in the OTHER column of the explain plan table, and the object column is null.
If the local db sends the whole thing over to the remote db, you get the explain plan from the remote db which will have the object name.

The moral of this is, when you are dealing with database links ALWAYS include the OTHER column when showing the explain plan.


Explain Plan - Object Name

A reader, July 07, 2004 - 5:46 am UTC

Hi Tom

I have tried to emulate my query with the example below.
But I will have to correct the previous statement I had made. It is the DISTINCT operator used within the inline view that causes object names to be null in the explain plan. If I take out the distinct operator, the explain plan gives me all the object names along with the access paths. Since I need to use DISTINCT, in absence of access paths, it is not possible for me to find out whether the query is using all the expected indexes in the source database. In light of Gary's comments, I am assuming that this behaviour is due to some local processing of data. Can you please tell me what needs to be done in such a situation.

Thanks

Thanks Gary for your comments. The OTHER column does give me the object name.



SQL> select a.empno,
  2         a.ename
  3    from emp@bsl.world a,
  4         ( select empno from emp@bsl.world ) b
  5   where a.empno = b.empno;

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS

     EMPNO ENAME
---------- ----------
      7900 JAMES
      7902 FORD
      7934 MILLER

14 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT (REMOTE) Optimizer=CHOOSE
   1    0   MERGE JOIN
   2    1     SORT (JOIN)
   3    2       TABLE ACCESS (FULL) OF 'EMP'                           BSLD.WOR
                                                                       LD

   4    1     SORT (JOIN)
   5    4       TABLE ACCESS (FULL) OF 'EMP'                           BSLD.WOR
                                                                       LD





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

SQL> select a.empno,
  2         a.ename
  3    from emp@bsl.world a,
  4         ( select distinct empno from emp@bsl.world ) b
  5   where a.empno = b.empno;

     EMPNO ENAME
---------- ------------------------------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS

     EMPNO ENAME
---------- ------------------------------
      7900 JAMES
      7902 FORD
      7934 MILLER

14 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   REMOTE*                                                    BSL.WORL
                                                                       D



   1 SERIAL_FROM_REMOTE            SELECT "A2"."EMPNO","A2"."ENAME" FROM "EMP"
                                   "A2", (SELECT /*+ */ DISTINCT "A3"."



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

Tom Kyte
July 07, 2004 - 8:35 am UTC

the addition of the distinct had Oracle send the query to the remote site for optimization -- the entire query was shipped over there. the query plan wasn't fully known over here.



Explain plan

A reader, July 07, 2004 - 12:54 pm UTC

Hi Tom

Can I execute the query directly on the remote database
to check to see if it uses the indexes and rely on it?

Thanks

Tom Kyte
July 07, 2004 - 1:07 pm UTC

you could enable sql_trace on the remote system

o touch the database link
o on the other database -- find your session (sid,serial#)
o use dbms_system to set sql_trace on in that session




A reader, July 08, 2004 - 3:51 am UTC

Thanks Tom

Global plan_table

A reader, July 17, 2004 - 9:45 pm UTC

What do you think about creating a global temporary plan_table for everyone to use?

create global temporary table plan_table ....
create public synonym plan_table for plan_table;
grant all on plan_table to public;
create view plan as select * from table(dbms_xplan.display);
create public synonym plan for plan;
grant select on plan to public;

This way, any database user can just do

explain plan for ...
select * from plan;

Your opinion?

Tom Kyte
July 18, 2004 - 12:17 pm UTC

you'd want to use the "on commit preserve rows" option.

but yes, I generally use a single table.

Global plan_table

A reader, July 23, 2004 - 2:04 pm UTC

explain plan for select * from t1;
explain plan for select * from t2;

select * from table(dbms_xplan.display);

1. This shows the plan for the last explain plan. This is good, but is this documented behaviour that can be relied upon?

2. If I use a GTT as a plan_table for the entire database and multiple users 'explain plan' into it, is each user guaranteed to see their own last executed explain plan?

Thanks


Tom Kyte
July 23, 2004 - 4:53 pm UTC

1) documentation says......

</code> http://docs.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_xplan3.htm <code>

..By default, if several plans in the plan table match the statement_id parameter passed to the display table function (default value is NULL), only the plan corresponding to the last EXPLAIN PLAN command is displayed. ....

2) since a GTT only shows data to the session that populated it, it would be impossible for any session to see another sessions data. as far as the gtt is concerned ONLY YOUR data is in that table.


Global plan_table

A reader, July 23, 2004 - 5:12 pm UTC

"..By default, if several plans in the plan table match the statement_id parameter passed to the display table function (default value is NULL), only the plan corresponding to the last EXPLAIN PLAN command is displayed"

So I guess internally the dbms_xplan.display() does a where timestamp=(select max(timestamp) ...).

Not really a big deal, but plan_table only has a timestamp column with precision down to 1 second. So if I issue multiple explain plans (from a script or something) that complete in the same second, the dbms_xplan.display output gets all jumbled up!

Thanks

Tom Kyte
July 23, 2004 - 5:34 pm UTC

that is why i always:

delete from plan_table;
explain plan.

A reader, August 05, 2004 - 4:08 pm UTC


Predicate information

A reader, August 23, 2004 - 9:31 am UTC

How can we make use of the 'Predicate information' provided at the end of dbms_xplan.display() output? Can this be used to tune the query or understand the plan better? How?

Thanks

Tom Kyte
August 23, 2004 - 10:22 am UTC

absolutely -- it shows for the first time clearly WHEN a predicate is being applied. that definitely leads to better "understanding" of when filters are being done.

that just seems intuitively tho? not really sure what else to say here.

Predicate information

A reader, August 23, 2004 - 11:45 am UTC

Thanks, yes, it is very intuitive, but...

What is the difference between a 'access' and a 'filter'?

Some of my comparisons to literals in my WHERE clause appear as ACCESS and others appear as FILTER. Why?

How exactly can I make use of this information? To maybe create indexes on some of the most commonly used FILTER columns?

Thanks



Tom Kyte
August 23, 2004 - 11:51 am UTC

ops$tkyte@ORA9IR2> create table t ( x int, y int );
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> delete from plan_table;
 
2 rows deleted.
 
ops$tkyte@ORA9IR2> explain plan for select * from t where x = 5;
 
Explained.
 
ops$tkyte@ORA9IR2> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
 
--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |       |       |       |
|*  1 |  TABLE ACCESS FULL   | T           |       |       |       |
--------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("T"."X"=5)
 
Note: rule based optimization
 
14 rows selected.

<b>here we have a "filter", a predicate in the pure sense if you will.  The where clause was not used to influence the access path really here -- we had to full scan and then "filter" the rows</b>


ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create index t_idx on t(x,y);
 
Index created.

<b>add an index..</b>
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> delete from plan_table;
 
2 rows deleted.
 
ops$tkyte@ORA9IR2> explain plan for select * from t where x = 5;
 
Explained.
 
ops$tkyte@ORA9IR2> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
 
--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |       |       |       |
|*  1 |  INDEX RANGE SCAN    | T_IDX       |       |       |       |
--------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("T"."X"=5)
 
Note: rule based optimization
 
14 rows selected.
 
<b>and the filter becomes an "access" -- meaning the predicate was used to influence the choice of an access path -- the index specifically in this case</b>

 

Predicate information

A reader, August 23, 2004 - 12:11 pm UTC

Thats what I thought, thanks for the example.

But I really wanted to know if I could use this as (one of the ) tools on some of my 'high buffer gets/execute' queries to reduce their LIOs by possibly creating a index on some of the FILTER predicates?

Yes, I know that if used improperly, index could increase LIOs, but even if I create the index which is not selective or doesnt reduce query cost, CBO will just ignore it, right?

Thanks

Tom Kyte
August 23, 2004 - 12:32 pm UTC

sure, that would be an approach -- consider:


ops$tkyte@ORA9IR2> create table t ( x int, y int );
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> delete from plan_table;
 
2 rows deleted.
 
ops$tkyte@ORA9IR2> explain plan for select * from t where x = 5 and y = 10;
 
Explained.
 
ops$tkyte@ORA9IR2> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
 
--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |       |       |       |
|*  1 |  TABLE ACCESS FULL   | T           |       |       |       |
--------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("T"."Y"=10 AND "T"."X"=5)
 
Note: rule based optimization
 
14 rows selected.
 
<b>all filter, no access...</b>

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create index t_idx on t(x);
 
Index created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> delete from plan_table;
 
2 rows deleted.
 
ops$tkyte@ORA9IR2> explain plan for select * from t where x = 5 and y = 10;
 
Explained.
 
ops$tkyte@ORA9IR2> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
 
---------------------------------------------------------------------------
| Id  | Operation                   |  Name       | Rows  | Bytes | Cost  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |       |       |       |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T           |       |       |       |
|*  2 |   INDEX RANGE SCAN          | T_IDX       |       |       |       |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("T"."Y"=10)
   2 - access("T"."X"=5)
 
Note: rule based optimization
 
16 rows selected.

<b>because the index is on X, we have part access and part filter after the fact, we can use that knowledge to:</b>

 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> drop index t_idx;
 
Index dropped.
 
ops$tkyte@ORA9IR2> create index t_idx on t(x,y);
 
Index created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> delete from plan_table;
 
3 rows deleted.
 
ops$tkyte@ORA9IR2> explain plan for select * from t where x = 5 and y = 10;
 
Explained.
 
ops$tkyte@ORA9IR2> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
 
--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |       |       |       |
|*  1 |  INDEX RANGE SCAN    | T_IDX       |       |       |       |
--------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("T"."X"=5 AND "T"."Y"=10)
 
Note: rule based optimization
 
14 rows selected.

<b>and go "all access"</b>
 

Thanks!

A reader, August 23, 2004 - 1:08 pm UTC

Thanks a lot, that was very useful. Appreciate it.

Explain concepts

parag jayant patankar, October 15, 2004 - 11:24 am UTC

Hi Tom

After going thru Perfomance & tunning ref manaual in Oracle 9i I have not understood some basic concepts. So Will you pl expalin following concepts for accessing paths and joings of tables for CBO in simple language ?

1. Cluster Scans
2. Hash Scans

Joins :

1. Hash Joins
2. Sort Merge Joins
3. Nested loop joins

if possible pl explain with examples.

regards & thanks
pjp

Tom Kyte
October 15, 2004 - 12:12 pm UTC

if you have my book "Effective Oracle by Design", I cover the joins in some detail (somethings don't fit in a paragraph)

their names are really conceptually what they do. to help you visualize what a "hash join" would be:

a) hash table1 based on its key, so if you were given a key value -- you can go right there, just like a hash table in a computer program. if you don't know what "hashing" is, you'll need to study that CS concept

b) full scan the other table, taking the join key, hashing it, looking up the related row(s) and joining them -- output result....


or sort merge:

a) sort table1 by join key
b) sort table2 by join key
c) merge them by join key, they are both sorted by the key now


or nested loops

for x in ( select * from table1 )
loop
for y in ( select * from table2 WHERE TABLE2.KEY = TABLE1.KEY )
loop
output record from X and Y
end loop
end loop





If you do not understand the cluster scan and hash scans -- you really need to understand CLUSTERS first. For that, either check out the concepts guide or I have a really good write up (IMO) about how they work in "Expert One on One Oracle".

Scalar Subqueries in Explain Plan

A reader, October 15, 2004 - 12:48 pm UTC

Hi Tom. Do you know of a work around (other than using TKPROF) in 8i to show up the plan of scalar subqueries within a select statement ? I know this can be done in 9iR2, but It would not in 8i (and that's the version we have were at work).

Thank you!

Tom Kyte
October 15, 2004 - 3:36 pm UTC

what you'll need to do is just take the scalar subquery and replace the correlation variables with "binds" and explain it.

that is really what is happening under the covers.

so,

select d.*, (select count(*) from emp where deptno = d.deptno) cnt
from dept d;


you would explain

select d.* from dept d;

and

select count(*) from emp where deptno = to_number(:n);




Set autotrace wrong???

Hector Gabriel Ulloa Ligarius, October 21, 2004 - 9:50 am UTC

Hi Tom...

  Two questions...

1.- Why you appear at the end of each magazine "Oracle Magazine"?  I think that you would have to be in the cover, things of Oracle ;)

2.- Why happens this?

   a) 

SYSDATE
---------
21-OCT-04

 real: 31
10:18:19 SQL> set autotrace on --Actived
10:18:27 SQL> select sysdate from dual;

SYSDATE
---------
21-OCT-04

 real: 532

Execution Plan
----------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'DUAL'




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

10:18:34 SQL> set autotrace off --To deactivate
10:18:43 SQL> select sysdate from dual;

The result was correct and there was no problem

But , If we took the code to a file SQL, example

Code of test.sql

set autotrace trace exp stat

spool c:\lista.txt

  select sysdate from dual;
spool off

set autot off

This process becomes disconnected and throws an error by the SET AUTOTRACE OFF

execute the test.sql

10:22:55 SQL> @test.sql

 real: 468

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'DUAL'




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

ORA-00022: invalid session id; access denied
Error accessing package DBMS_APPLICATION_INFO
ERROR:
ORA-01012: not logged on


10:23:02 SQL> 


The sentence SET AUTOTRACE OFF causes the error then if the coat of the code and I execute the SQL, works perfectly 

work with

10:28:28 SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
PL/SQL Release 9.2.0.5.0 -                      Production
CORE    9.2.0.6.0                               Production
TNS for Linux: Version 9.2.0.5.0 -              Production
NLSRTL Version 9.2.0.5.0 -                      Production

 real: 63
10:28:35 SQL> 

What happens?  

thank you very much Tom Kyte



 

Tom Kyte
October 21, 2004 - 2:28 pm UTC

1) that is actually a "coveted" position in a magazine :)

2) please contact support, that is something that should not happen.

What is this?

A reader, October 26, 2004 - 5:13 pm UTC

Hi Tom,
While getting the execution plan of a query,
I found:
Execution Plan
----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=619615 Card=5890989
Bytes=1095723954)

1 3 RECURSIVE EXECUTION OF 'SYS_LE_3_0'
2 3 RECURSIVE EXECUTION OF 'SYS_LE_3_1'
3 0 TEMP TABLE TRANSFORMATION
4 3 SORT (GROUP BY) (Cost=619615 Card=5890989 Bytes=10957239
54)

5 4 MERGE JOIN (CARTESIAN) (Cost=260 Card=5890989 Bytes=10
95723954)
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=619615 Card=5890989
Bytes=1095723954)

1 3 RECURSIVE EXECUTION OF 'SYS_LE_3_0'
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=619615 Card=5890989
Bytes=1095723954)

1 3 RECURSIVE EXECUTION OF 'SYS_LE_3_0'
2 3 RECURSIVE EXECUTION OF 'SYS_LE_3_1'
3 0 TEMP TABLE TRANSFORMATION
4 3 SORT (GROUP BY) (Cost=619615 Card=5890989 Bytes=10957239
54)

5 4 MERGE JOIN (CARTESIAN) (Cost=260 Card=5890989 Bytes=10
95723954)

6 5 NESTED LOOPS (Cost=32 Card=3 Bytes=543)
7 6 NESTED LOOPS (Cost=26 Card=3 Bytes=519)
8 7 NESTED LOOPS (Cost=20 Card=3 Bytes=414)
9 8 HASH JOIN (Cost=20 Card=3 Bytes=390)
10 9 HASH JOIN (Cost=17 Card=8 Bytes=736)
11 10 HASH JOIN (Cost=14 Card=39 Bytes=2652)
12 11 TABLE ACCESS (FULL) OF 'TB_XXXX_REGION'
(Cost=2 Card=12 Bytes=276)

13 11 HASH JOIN (Cost=11 Card=643 Bytes=28935)
14 13 TABLE ACCESS (FULL) OF 'TB_XXXX_BUSINE
SS_CHANNEL' (Cost=2 Card=2 Bytes=20)

15 13 TABLE ACCESS (FULL) OF 'TB_XXXX_SITE_1
' (Cost=8 Card=4822 Bytes=168770)

16 10 TABLE ACCESS (FULL) OF 'TB_XXXX_STATE' (Co
st=2 Card=9 Bytes=216)

17 9 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6715_5
55D859' (Cost=2 Card=1369 Bytes=52022)

18 8 INDEX (UNIQUE SCAN) OF 'IN_XXXX_EMPLOYEE_0' (U
NIQUE)

19 7 TABLE ACCESS (BY INDEX ROWID) OF 'TB_XXXXX_CONT
ACT_DETAIL' (Cost=2 Card=1 Bytes=35)

20 19 INDEX (UNIQUE SCAN) OF 'IN_XXXXX_CONTACT_DETA
IL_0' (UNIQUE) (Cost=1 Card=1)

21 6 TABLE ACCESS (BY INDEX ROWID) OF 'TB_XXXXX_CONTAC
T_ADDRESS' (Cost=2 Card=1 Bytes=8)

22 21 INDEX (UNIQUE SCAN) OF 'IN_XXXXX_CONTACT_ADDRES
S_0' (UNIQUE) (Cost=1 Card=1)

23 5 BUFFER (SORT) (Cost=619613 Card=1815508 Bytes=907754
0)

24 23 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6716_555D859
' (Cost=76 Card=1815508 Bytes=9077540)
__________________________
I never saw 'SYS_TEMP....' along with 'RECURSIVE EXECUTION OF 'SYS_LE_3_0.....' before. The query is taking huge amount of time.
Any help will be highly appreciated. The DB is 9.2.
With Regards,






Tom Kyte
October 26, 2004 - 5:41 pm UTC

what, you cannot bear to share the query with us? it'll make it lots easier to talk about.

A reader, November 17, 2004 - 9:25 am UTC

Tom,

This table "tablename" has only only one index. It is on its primary key (XPKtablename). Could you please tell if the execution plans are ok?. plan_table was cleared between the two explain plans. Version: 8.1.7. Thanks in advance.


==============

SQL> explain plan for SELECT  id1, col1, id2,col2
FROM tablename WHERE pk IN (SELECT
MIN(pk) FROM tablename )  2    3  
  4  /
 
Explained.
 
SQL> @?/rdbms/admin/utlxpls
 
Plan Table
--------------------------------------------------------------------------------
| Operation                 |  Name    |  Rows | Bytes|  Cost  | Pstart| Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT          |          |     1 |   20 |      2 |       |       |
|  TABLE ACCESS BY INDEX ROW|tablename |     1 |   20 |      2 |       |       |
|   INDEX UNIQUE SCAN       |XPKtable  |     1 |      |      1 |       |       |
|    SORT AGGREGATE         |          |     1 |   13 |        |       |       |
|     INDEX FULL SCAN (MIN/M|XPKtablen |    28K|  358K|      2 |       |       |
--------------------------------------------------------------------------------
 
8 rows selected.
 
SQL> set timing on
SQL> set autotrace on   

SQL> SELECT  id1, col1, id2,col2
FROM tablename WHERE pk IN (SELECT
MIN(pk) FROM tablename)  2    3  
  4  /
 
Elapsed: 00:00:00.94
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=20)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'tablename' (Cost=2 Car
          d=1 Bytes=20)
 
   2    1     INDEX (UNIQUE SCAN) OF 'XPKtablename' (UNIQUE) (Cost=1
           Card=1)
 
   3    2       SORT (AGGREGATE)
   4    3         INDEX (FULL SCAN (MIN/MAX)) OF 'XPKtablename' (UNI
          QUE) (Cost=2 Card=28272 Bytes=367536)
 
 
 ======================

SQL> EXPLAIN PLAN FOR SELECT  id1, col1, id2,col2
FROM tablename WHERE pk ='000072713'
  2  /
 
Explained.
 
SQL> @?/rdbms/admin/utlxpls
 
Plan Table
--------------------------------------------------------------------------------
| Operation                 |  Name    |  Rows | Bytes|  Cost  | Pstart| Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT          |          |     1 |   20 |      2 |       |       |
|  TABLE ACCESS BY INDEX ROW|tablenam  |     1 |   20 |      2 |       |       |
|   INDEX UNIQUE SCAN       |XPKtablen |     1 |      |      1 |       |       |
--------------------------------------------------------------------------------
 
6 rows selected.
 
/
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=247 Bytes=42)
   1    0   UNION-ALL
   2    1     TABLE ACCESS (FULL) OF 'DUAL' (Cost=1 Card=82)
   3    1     TABLE ACCESS (FULL) OF 'DUAL' (Cost=1 Card=82)
   4    1     VIEW (Cost=1 Card=1 Bytes=42)
   5    4       CONNECT BY
   6    5         SORT (ORDER BY) (Cost=3 Card=1 Bytes=35)
   7    6           TABLE ACCESS (FULL) OF 'PLAN_TABLE' (Cost=1 Card=1
           Bytes=35)
 
   8    7             SORT (AGGREGATE)
   9    8               TABLE ACCESS (FULL) OF 'PLAN_TABLE' (Cost=1 Ca
          rd=1 Bytes=22)
 
  10    5         TABLE ACCESS (BY USER ROWID) OF 'PLAN_TABLE'
  11    5         TABLE ACCESS (FULL) OF 'PLAN_TABLE' (Cost=1 Card=1 B
          ytes=400)
 
  12    1     TABLE ACCESS (FULL) OF 'DUAL' (Cost=1 Card=82)
 
 
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
         32  db block gets
         10  consistent gets
          0  physical reads
          0  redo size
       1032  bytes sent via SQL*Net to client
       1147  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          6  rows processed
 
SQL> 
select id1, col1, id2,col2
FROM tablename WHERE pk='000072713'SQL> 
  2  /
 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=20)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'tablename' (Cost=2 Car
          d=1 Bytes=20)
 
   2    1     INDEX (UNIQUE SCAN) OF 'XPKtablename' (UNIQUE) (Cost=1
           Card=1)
 
 
 
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          1  physical reads
          0  redo size
        542  bytes sent via SQL*Net to client
        426  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
SQL> 


  

Tom Kyte
November 17, 2004 - 1:57 pm UTC

plans look right -- not sure what you are after tho?

recursive execution problem ....

Sravan Tailam, December 01, 2004 - 2:02 pm UTC

Sir,
I have a similar issue found earlier in the string :

I have 2 queries almost simlar, one working fine and other failing due to filling up of TEMP tablespace. The 2nd plan shows that its using RECURSIVE EXECUTION of SYS_LE_3_0? I know what a recursive execution is but in here, what is it trying to do ?


The 2 queries have the same logic, the difference being the 2nd query hitting table B, which is a copy of table A, which query 1 uses. Table A and B are identical in structure but the # rows are slightly different(15%).

Query which Works Fine:
=======================
SELECT t1_1.name,
t1_1.sort_sequence,
t2_1.name,
t2_1.sort_sequence,
t3_1.name,
t3_1.sort_sequence,
t4.unique_id,
t0.object_id,
t4.unique_id,
t4.unique_id,
t4.unique_id
FROM sample_510546895 t0,
group_entry t1_1,
group_entry t2_1,
group_entry t3_1,
encounter_178508316 t4, group_detail_90090848 t1_2, group_detail_391274910 t2_2, group_detail_61832031 t3_2
WHERE t1_2.object_id = t4.unique_id
AND t1_2.lookup_cd (+) = 59
AND t1_2.group_definition_id = to_number(90090848.1)
AND t1_1.unique_id = t1_2.group_entry_id
AND t2_2.object_id = t4.unique_id
AND t2_2.lookup_cd (+) = 59
AND t2_2.group_definition_id = to_number(391274910.1)
AND t2_1.unique_id = t2_2.group_entry_id
AND t3_2.object_id = t4.unique_id
AND t3_2.lookup_cd (+) = 59
AND t3_2.group_definition_id = to_number(61832031.1)
AND t3_1.unique_id = t3_2.group_entry_id
AND (t0.object_id = t4.unique_id AND t0.data_set_id=178508316.1)

Execution Plan :
================
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=11001 Card=108385 By
tes=19834455)

1 0 HASH JOIN (Cost=11001 Card=108385 Bytes=19834455)
2 1 TABLE ACCESS (FULL) OF 'GROUP_ENTRY' (Cost=16 Card=1285
Bytes=35980)

3 1 HASH JOIN (Cost=10969 Card=108385 Bytes=16799675)
4 3 TABLE ACCESS (FULL) OF 'GROUP_ENTRY' (Cost=16 Card=128
5 Bytes=35980)

5 3 HASH JOIN (Cost=10939 Card=108385 Bytes=13764895)
6 5 TABLE ACCESS (FULL) OF 'GROUP_ENTRY' (Cost=16 Card=1
285 Bytes=35980)

7 5 HASH JOIN (Cost=10912 Card=108385 Bytes=10730115)
8 7 TABLE ACCESS (BY INDEX ROWID) OF 'GROUP_DETAIL_391
274910' (Cost=1225 Card=128538 Bytes=3470526)

9 8 INDEX (RANGE SCAN) OF 'GROUP_DETAIL_1X_391274910
' (NON-UNIQUE) (Cost=398 Card=2)

10 7 HASH JOIN (Cost=9478 Card=103353 Bytes=7441416)
11 10 TABLE ACCESS (BY INDEX ROWID) OF 'GROUP_DETAIL_6
1832031' (Cost=1048 Card=115570 Bytes=2773680)

12 11 INDEX (RANGE SCAN) OF 'GROUP_DETAIL_1X_6183203
1' (NON-UNIQUE) (Cost=344 Card=1)

13 10 HASH JOIN (Cost=8266 Card=109613 Bytes=5261424)
14 13 TABLE ACCESS (FULL) OF 'SAMPLE_510546895' (Cos
t=82 Card=122823 Bytes=1965168)

15 13 NESTED LOOPS (Cost=8058 Card=109613 Bytes=3507
616)

16 15 TABLE ACCESS (FULL) OF 'GROUP_DETAIL_9009084
8' (Cost=8058 Card=109613 Bytes=2740325)

17 15 INDEX (UNIQUE SCAN) OF 'ENCOUNTER_1X_1785083
16' (UNIQUE)

===========================================
THE BELOW QUERY IS NOT WORKING FINE, RUNNING OUT OF TEMP SPACE W/ RECURSIVE EXECUTION
=================================================

SELECT t1_1.name,
t1_1.sort_sequence,
t2_1.name,
t2_1.sort_sequence,
t3_1.name,
t3_1.sort_sequence,
t4.unique_id,
t0.object_id,
t4.unique_id,
t4.unique_id,
t4.unique_id
FROM sample_510546946 t0,
group_entry t1_1,
group_entry t2_1,
group_entry t3_1,
encounter_375157784 t4, group_detail_90090848 t1_2, group_detail_511605829 t2_2, group_detail_61832031 t3_2
WHERE t1_2.object_id = t4.unique_id
AND t1_2.lookup_cd (+) = 80
AND t1_2.group_definition_id = to_number(90090848.1)
AND t1_1.unique_id = t1_2.group_entry_id
AND t2_2.object_id = t4.unique_id
AND t2_2.lookup_cd (+) = 80
AND t2_2.group_definition_id = to_number(511605829.1)
AND t2_1.unique_id = t2_2.group_entry_id
AND t3_2.object_id = t4.unique_id
AND t3_2.lookup_cd (+) = 80
AND t3_2.group_definition_id = to_number(61832031.1)
AND t3_1.unique_id = t3_2.group_entry_id
AND (t0.object_id = t4.unique_id AND t0.data_set_id=375157784.1)

Execution Plan :
================
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=10230 Card=63246 Byt
es=11574018)

1 3 RECURSIVE EXECUTION OF 'SYS_LE_3_0'
2 3 RECURSIVE EXECUTION OF 'SYS_LE_3_1'
3 0 TEMP TABLE TRANSFORMATION
4 3 HASH JOIN (Cost=10230 Card=63246 Bytes=11574018)
5 4 TABLE ACCESS (FULL) OF 'GROUP_ENTRY' (Cost=16 Card=128
5 Bytes=35980)

6 4 HASH JOIN (Cost=10204 Card=63246 Bytes=9803130)
7 6 TABLE ACCESS (FULL) OF 'GROUP_ENTRY' (Cost=16 Card=1
285 Bytes=35980)

8 6 HASH JOIN (Cost=10179 Card=63246 Bytes=8032242)
9 8 TABLE ACCESS (FULL) OF 'GROUP_ENTRY' (Cost=16 Card
=1285 Bytes=35980)

10 8 HASH JOIN (Cost=10156 Card=63246 Bytes=6261354)
11 10 TABLE ACCESS (FULL) OF 'SAMPLE_510546946' (Cost=
100 Card=151197 Bytes=2419152)

12 10 HASH JOIN (Cost=9898 Card=64499 Bytes=5353417)
13 12 TABLE ACCESS (FULL) OF 'GROUP_DETAIL_511605829
' (Cost=498 Card=121054 Bytes=3268458)

14 12 HASH JOIN (Cost=9243 Card=82156 Bytes=4600736)
15 14 TABLE ACCESS (BY INDEX ROWID) OF 'GROUP_DETA
IL_61832031' (Cost=1048 Card=115570 Bytes=16064230)

16 15 INDEX (RANGE SCAN) OF 'GROUP_DETAIL_1X_618
32031' (NON-UNIQUE) (Cost=344 Card=1)

17 14 NESTED LOOPS (Cost=8058 Card=109613 Bytes=35
07616)

18 17 TABLE ACCESS (FULL) OF 'GROUP_DETAIL_90090
848' (Cost=8058 Card=109613 Bytes=2740325)

19 17 INDEX (UNIQUE SCAN) OF 'ENCOUNTER_1X_37515
7784' (UNIQUE)


=======================
The indexes are same on the differing table and are all analyzed.
Can you please help me understand what/why it was doing a recursive execution of SYS_LE_3_0 in the 2nd Plan ? What is temp_table transformation ?

Thanks
-Sravan

Tom Kyte
December 01, 2004 - 3:27 pm UTC

so, how much temp do you have -- using temp is "not a bad thing(tm)"


you have two tables with two TOTALLY DIFFERENT sets of data -- it would be surprising for the same plan to result in all cases.

thanks

Sravan Tailam, December 01, 2004 - 3:44 pm UTC

Thanks Tom.
I have about 9GB for temp. I do understand that temp is not a bad thing :)

I was trying to understand the "recursive execution of ..." step, which I have never seen before. I know what a "Recursive Execution" in general terms mean. Can you point me to a resource, as I did not find a site which explains this step nicely. Also what is TEMP Table transformation?

Thanks much for your assistance.

Tom Kyte
December 01, 2004 - 7:47 pm UTC

use

delete from plan_table;
explain plan for your_query;
select * from table(dbms_xplan.display);

it is usually more informative.

it created "temporary tables" in order to process your request -- sql_trace will show you these things also.

cant set autotrace

Sanjay Talati, December 06, 2004 - 5:01 am UTC

SQL> set autotrace on
SP2-0618: Cannot find the Session Identifier.  Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report

am having this error... what can the possible reasons be. 

Tom Kyte
December 06, 2004 - 11:43 am UTC

did you read the error message?

cpu cost

Eugene, December 14, 2004 - 7:38 pm UTC

Tom,
I am ran select * from table(dbms_xplan.display) on a sql statement and saw a note at the bottom about cpu costing being off.
Then I ran dbms_stats.gather_schema_stats(ownname=>'TEST_USER, cascade=>TRUE);
It is still off. What do I need to do to see it?

Thanks
Eugene


Tom Kyte
December 15, 2004 - 1:12 pm UTC

use 10g....


ops$tkyte@ORA9IR2> create table t as select * from all_users;
 
Table created.
 
ops$tkyte@ORA9IR2> exec dbms_stats.gather_table_stats( user, 'T' );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> delete from plan_table;
 
3 rows deleted.
 
ops$tkyte@ORA9IR2> explain plan for select * from t;
 
Explained.
 
ops$tkyte@ORA9IR2> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------
 
--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |    22 |   374 |     2 |
|   1 |  TABLE ACCESS FULL   | T           |    22 |   374 |     2 |
--------------------------------------------------------------------
 
Note: cpu costing is off
 
9 rows selected.



ops$tkyte@ORA10G> create table t as select * from all_users;
 
Table created.
 
ops$tkyte@ORA10G> exec dbms_stats.gather_table_stats( user, 'T' );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G> delete from plan_table;
 
0 rows deleted.
 
ops$tkyte@ORA10G> explain plan for select * from t;
 
Explained.
 
ops$tkyte@ORA10G> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------
Plan hash value: 749696591
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1028 | 20560 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T    |  1028 | 20560 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
 
8 rows selected.
 
<b>cpu costing is off in 9i, on in 10g -- if it turns on in 9i, it is generally "a product issue", it is off by default</b>
 

Interpreting statistics in the explain plan

A reader, December 16, 2004 - 12:34 am UTC

Tom,
In the statistics part of the explain plan -
Q1) What is the difference between db block gets and physical reads?
Q2) How should the information in the statistics section be used to fine tune a SQL. What in particular should we be looking at.

Thanks for you valuable time.


Statistics
----------------------------------------------------------
475 recursive calls
27 db block gets
81 consistent gets
8 physical reads
0 redo size
1083 bytes sent via SQL*Net to client
697 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
9 sorts (memory)
0 sorts (disk)
1 rows processed


Tom Kyte
December 16, 2004 - 8:10 am UTC

do you have Effective Oracle by Design? I go into details there.

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:4783147586098 <code>


Plan for a Table Changing

Ashok K Rathi, December 29, 2004 - 10:58 am UTC

Tom ,
Following Two query has a differnt plan for a table

select count(1) from table_name where date_column >= sysdate -34 ;

select count(1) from table_name where date_column >= sysdate -34 -another_date_column ;

First query utilizes index on table date_column but not the second query .

If i try to force the particular index cost goes very high .

Is there any way to utilize the index on date_column in the second query with the Minimum cost

Thanking you in anticipation
Ashok

Tom Kyte
December 29, 2004 - 7:02 pm UTC

think about it.


select ... from t where COLUMN1 >= constant - COLUMN2;


what index could you possibly create that could be used?

every row must be inspected -- for you have to subtract from CONSTANT every rows value of COLUMN2 to get a "date" to be compared to column1......

your predicate naturally has to visit each and every single row in that entire table, there is no avoiding it.  Not even with a function based index -- due to the use of SYSDATE there (1 second after you run this query, the results would be different because sysdate is different and you have to do the (sysdate-another_date_column) math to get a number to subtract 34 from.

but the entire thing doesn't compute to me.


ops$tkyte@ORA9IR2> select * from t where dt > sysdate-34-dt2;
select * from t where dt > sysdate-34-dt2
                         *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected DATE got NUMBER


soooo, what was the real problem. 

Sorry

Ashok K Rathi, December 29, 2004 - 11:00 am UTC

the column in the query is another_number_column not the another_day_column


Thanks
Ashok

Tom Kyte
December 29, 2004 - 7:04 pm UTC

then you can use:

where dt+n >= sysdate-34;

and create an index on (dt+n)



ops$tkyte@ORA9IR2> create table t ( dt date, n number );
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t select sysdate-1, rownum from all_users;
 
22 rows created.
 
ops$tkyte@ORA9IR2> create index t_idx on t(dt+n);
 
Index created.
 
ops$tkyte@ORA9IR2> set autotrace traceonly explain
ops$tkyte@ORA9IR2> select /*+ first_rows */ * from t where dt+n >= sysdate-34;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=1 Card=20 Bytes=440)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=1 Card=20 Bytes=440)
   2    1     INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=2 Card=4)
 
 
 
ops$tkyte@ORA9IR2> set autotrace off
 

Explain plan at runtime

A reader, January 05, 2005 - 10:06 am UTC

I looked for an answer to this question but could not find it.

I am running an INSERT that is taking a long time to complete. Is there a way to look at the execution PLAN while the insert is running?

Thanks.

Tom Kyte
January 05, 2005 - 11:05 am UTC

v$sql_plan will have it in 9i and above.

irish, January 07, 2005 - 12:50 pm UTC

----

Row Count is zero

abhay, January 17, 2005 - 6:23 pm UTC

I ran TKPROF, but why are all Row counts Zero ?

Rows Row Source Operation
------- ---------------------------------------------------
0 FILTER
0 HASH JOIN
0 TABLE ACCESS FULL CUSTOMERS
0 PARTITION RANGE ITERATOR PARTITION: 3 KEY
0 TABLE ACCESS BY LOCAL INDEX ROWID SALES PARTITION: 3 KEY
0 BITMAP CONVERSION TO ROWIDS
..........




Tom Kyte
January 17, 2005 - 6:40 pm UTC

you did not close the cursor (eg: in sqlplus, exit before tkprof'ing)

Query Performance

Rory, February 02, 2005 - 2:14 am UTC

Hi Tom.

I've got a query that takes too long. There is one table, NX_TICKETS that still makes a full table scan. Though I know you said that not all full table scans are evil, I was wondering why it wont use the index.

ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE;
ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED;
ALTER SESSION SET optimizer_index_caching=90;
ALTER SESSION SET optimizer_index_cost_adj=35;

I then created a function based index.

CREATE INDEX TICK_CANCEL ON NX_TICKETS(TO_CHAR(CANCEL_DATE,'MON'),TO_CHAR(CANCEL_DATE,'RRRR'));

I analyzed the table after the creation of the index.

SET TIMING ON AUTOTRACE ON;

SELECT
NVL(t.ticket_no,0) ticket_no,
r.rese_no,
r.reit_no,
r.rev_rese_no,
r.rev_reit_no,
r.paid_at paid_at,
r.paid_to paid_to,
TRUNC(r. paid_date) paid_date,
t.issued_at issued_at,
t.issued_by issued_by,
TRUNC(t.issue_date) issue_date,
t.cancelled_at cancelled_at,
t.cancelled_by cancelled_by,
TRUNC(t.cancel_date) cancel_date,
r.vess_code vess_code,
r.voyage_no voyno,
r.origin orig,
r.destination dest,
r.acco_code acco_code,
r.created_at created_at,
r.created_by created_by,
r.create_date create_date,
NVL(r.total_amt,0) total_amt,
NVL(r.adj,0) adj,
NVL(r.other_charges,0) othcharge,
DECODE(t.stat,'RD',NVL(t.surcharge,0),0) surcharge,
DECODE(t.stat,'RF',NVL(t.rf_surcharge,0),0) rf_surcharge,
NVL(r.comm_amt,0) -
DECODE(NVL(r.scon_wga_netrate,0),0,0,
r.comm_amt*(1-ROUND(NVL(r.scon_wga_netrate,0)/(NVL(r.scon_wga_netrate,0)+NVL(r.scon_other_owne_netrate,0)),4))) comm,
NVL(r.insurance_amt,0) ins,
NVL(r.terminal_fee,0) tfee,
NVL(r.meal_allowance,0) meal,
NVL(r.transpo_allowance,0) transpo,
NVL(r.scon_flag,'N'),
r.scon_other_owne_code,
NVL(r.scon_wga_rate,0) swga_rate,
NVL(r.scon_other_owne_rate,0) sother_rate,
NVL(r.scon_wga_netrate,0) swga_netrate,
NVL(r.scon_other_owne_netrate,0) sother_netrate,
DECODE(NVL(r.scon_wga_netrate,0),0,0,
r.total_amt*ROUND(NVL(r.scon_wga_netrate,0)/(NVL(r.scon_wga_netrate,0)+NVL(r.scon_other_owne_netrate,0)),4)) swga_share,
DECODE(NVL(r.scon_wga_netrate,0),0,0,
r.total_amt*(1-ROUND(NVL(r.scon_wga_netrate,0)/(NVL(r.scon_wga_netrate,0)+NVL(r.scon_other_owne_netrate,0)),4))) sother_share,
DECODE(NVL(r.scon_wga_netrate,0),0,0,
r.comm_amt*(1-ROUND(NVL(r.scon_wga_netrate,0)/(NVL(r.scon_wga_netrate,0)+NVL(r.scon_other_owne_netrate,0)),4))) sother_comm,
DECODE(NVL(r.scon_wga_netrate,0),0,0,
NVL(r.scon_wga_rate,0) -
(r.total_amt*ROUND(NVL(r.scon_wga_netrate,0)/(NVL(r.scon_wga_netrate,0)+NVL(r.scon_other_owne_netrate,0)),4))) swga_adj,
NVL(r.meal_rate,0) meal_rate,
NVL(r.meal_adj,0) meal_adj,
NVL(r.total_meal_rate,0) total_meal_rate,
NVL(MEAL_CHARGED_TO,'PAX') meal_charged_to,
r.tgs_dipr_code,
r.tgs_disc_apof_code,
NVL(r.tgs_adj,0) tgs_adj,
r.paymode paymode,
r.dipr_code,
d.disc_type,
t.replaced_ticket_no old_ticket,
r.stat stat,
DECODE(r.stat,'PP','Y', NVL(t.pre_paid_flag,'N')) prepaid_flag
FROM nx_reservation_items r,
nx_tickets t,
nx_discounts_promos d
WHERE t.rese_no (+)= r.rese_no AND t.reit_no (+)= r.reit_no AND
d.dipr_code (+)= NVL(r.dipr_code,'*') AND
((TO_CHAR(r.paid_date,'MON') = 'JAN' AND TO_CHAR(r.paid_date,'RRRR')= '2006') OR
(TO_CHAR(t.cancel_date,'MON') = 'JAN' AND TO_CHAR(t.cancel_date,'RRRR')= '2006')) AND
r.paid_date IS NOT NULL AND
t.stat (+)!= 'CN' AND r.stat !='RL' AND r.stat !='LR' AND
r.extract_flag = 'N'

WAITS for the session above are "db file sequential read" and "db file scattered read"

no rows selected


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=140091 Card=3024814 Bytes=541441706)
1 0 HASH JOIN (OUTER) (Cost=140091 Card=3024814 Bytes=541441706)
2 1 FILTER
3 2 HASH JOIN (OUTER)
4 3 TABLE ACCESS (BY INDEX ROWID) OF NX_RESERVATION_ITEMS' (Cost=145 Card=3024814 Bytes=369027308)
5 4 INDEX (RANGE SCAN) OF 'EXTRACT_INDX' (NON-UNIQUE)
(Cost=13 Card=3821436)
6 3 TABLE ACCESS (FULL) OF 'NX_TICKETS' (Cost=16681 Card=6421564 Bytes=308235072)
7 1 TABLE ACCESS (FULL) OF 'NX_DISCOUNTS_PROMOS' (Cost=13 Card=4241 Bytes=38169)

Statistics
----------------------------------------------------------
441 recursive calls
0 db block gets
329846 consistent gets
329234 physical reads
0 redo size
3328 bytes sent via SQL*Net to client
2186 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

Does it have aything to do with the "OR"?
Thanks TOm

Tom Kyte
February 02, 2005 - 5:07 am UTC

why would you do this:

((TO_CHAR(r.paid_date,'MON') = 'JAN' AND TO_CHAR(r.paid_date,'RRRR')=
'2006') OR
(TO_CHAR(t.cancel_date,'MON') = 'JAN' AND
TO_CHAR(t.cancel_date,'RRRR')= '2006'))

when this


r.paid_date between to_date('01-jan-2006')
and (to_date('01-feb-2006')-1/24/60/60)
or
r.cancel_date between to_date('01-jan-2006')
and (to_date('01-feb-2006')-1/24/60/60)



but that is the cause of "index not used"

where ( c1 = this OR c2 = that )
and lots of other stuff

won't likely use an index on c1 or c2.


You could try "unioning" the two queries together.

A reader, February 14, 2005 - 1:59 pm UTC

Hi Tom,
We are using oracle 9i with rule based optimizer.I am running merge statement (the one you helped me last week) and looks like the statement is invoking cost based optimizer. Any idea why this is happening? There are no stats on the tables.

SQL> sho parameter opt

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
filesystemio_options                 string      asynch
object_cache_optimal_size            integer     102400
optimizer_dynamic_sampling           integer     0
optimizer_features_enable            string      8.1.7
optimizer_index_caching              integer     0
optimizer_index_cost_adj             integer     100
optimizer_max_permutations           integer     2000
optimizer_mode                       string      RULE
SQL> MERGE INTO siebel.eim_opty t
  2      USING ( SELECT rowid rid, ntile(10) over (ORDER BY row_id) nt
  3              FROM siebel.eim_opty WHERE if_row_batch_num = 3000 ) t2
  4      ON ( t.rowid = t2.rid )
  5      WHEN matched THEN UPDATE SET if_row_batch_num = 2999 + t2.nt
  6      WHEN NOT matched THEN INSERT (if_row_batch_num) VALUES (null);

0 rows merged.


Execution Plan
----------------------------------------------------------
   0      MERGE STATEMENT Optimizer=RULE (Cost=24 Card=20 Bytes=223800
          )

   1    0   MERGE OF 'EIM_OPTY'
   2    1     VIEW
   3    2       NESTED LOOPS (OUTER) (Cost=24 Card=20 Bytes=224080)
   4    3         VIEW (Cost=4 Card=20 Bytes=400)
   5    4           WINDOW (BUFFER) (Cost=4 Card=20 Bytes=580)
   6    5             INDEX (RANGE SCAN) OF 'EIM_OPTY_U1' (UNIQUE) (Co
          st=2 Card=20 Bytes=580)

   7    3         TABLE ACCESS (BY USER ROWID) OF 'EIM_OPTY' (Cost=1 C
          ard=1 Bytes=11184)





Statistics
----------------------------------------------------------
       8781  recursive calls
          0  db block gets
       1149  consistent gets
         47  physical reads
          0  redo size
        367  bytes sent via SQL*Net to client
        665  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
         36  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL> select count(*) from all_tables where owner = 'SIEBEL' and last_analyzed is not null;

  COUNT(*)
----------
         0

 

Tom Kyte
February 14, 2005 - 5:41 pm UTC

because many new features -- some ansi joins, queries on iots, certain connect by constructs, the use of function based indexes, tables with non-default degrees of parallelism, etc etc etc etc are only available with the CBO.


looks like "hints are us" for you - hint it till you get to the CBO.

Very strange plan - cannot understand temp table transformation at all

Andrey, March 23, 2005 - 3:36 am UTC

The query:

SELECT /*+ ordered use_nl (c ap ac co d) index(c table2$table3_N) index(ap table3$N) index(ac table4$N) index(co table5$N) index(d table6$UP) */
31248
, 1
, NVL(co.typ,-1) con_typ
, NVL(sf.r_table3,-1) table3_r_table3
, NVL(ac.acc_method,-1) acc_acc_method
, NVL(ac.Bill$Group,-1) acc_billgroup
, NVL(ac.Status,-1) acc_status
, NVL(ap.r$tarif$srv,-1) trf_n
, NVL(ap.typ,-1) table3_typ
, NVL(c.city,-1) city
, DECODE( GROUPING(DECODE(GREATEST(BITAND(NVL(sf.status,0),32),BITAND(NVL(sf.status,0),8),BITAND(NVL(sf.status,0),2),BITAND(NVL(sf.status,0),1))
, 0,-1,GREATEST(BITAND(NVL(sf.status,0),32),BITAND(NVL(sf.status,0),8),BITAND(NVL(sf.status,0),2),BITAND(NVL(sf.status,0),1))))
, 0
, DECODE(GREATEST(BITAND(NVL(sf.status,0),32),BITAND(NVL(sf.status,0),8),BITAND(NVL(sf.status,0),2),BITAND(NVL(sf.status,0),1))
, 0,-1,GREATEST(BITAND(NVL(sf.status,0),32),BITAND(NVL(sf.status,0),8),BITAND(NVL(sf.status,0),2),BITAND(NVL(sf.status,0),1)))
, 1
, -2
) srv_status
, DECODE(GROUPING(NVL(sf.table1,-1)), 0, NVL(sf.table1,-1), 1, -2) srv_table1
, DECODE(GROUPING(NVL(D.r$table6$code,9999)), 0, NVL(D.r$table6$code,9999), 1, -2 ) srv_typ

-- , count( DISTINCT DECODE(sign(bitand(bl.status,12)),0,sf.up,to_number(NULL)) ) table3act
, COUNT( DISTINCT sf.up ) table3act
FROM table1 sf
, table2 c
-- , blocking bl
, table3 ap
, table4 ac
, table5 co
, table6 D
WHERE TO_DATE('31012003235959','DDMMYYYYHH24MISS') BETWEEN co.fd AND co.td
AND co.n=ac.up

AND TO_DATE('31012003235959','DDMMYYYYHH24MISS') BETWEEN ac.fd AND ac.td
AND ac.n=ap.up

AND TO_DATE('31012003235959','DDMMYYYYHH24MISS') BETWEEN ap.fd AND ap.td
-- and ap.n=bl.up

AND sf.td>=TO_DATE('01012003000000','DDMMYYYYHH24MISS')
AND sf.fd<=TO_DATE('31012003235959','DDMMYYYYHH24MISS')
/* and (sf.up between 1*10000+1 and (1+1)*10000
OR 1 IS NULL)
*/
-- AND bl.up = c.table3_n
-- and to_date('31012003235959','DDMMYYYYHH24MISS') BETWEEN bl.fd AND bl.td
-- AND bl.typ=1
AND ap.n = c.table3_n /*@#$*/

AND c.table3_n = sf.up
AND TO_DATE('31012003235959','DDMMYYYYHH24MISS') BETWEEN c.fd AND c.td

AND D.up = 9
AND TO_DATE('31012003235959','DDMMYYYYHH24MISS') BETWEEN D.fd AND D.td
AND D.code = sf.table1
AND D.lang = 1

GROUP BY
NVL(co.typ,-1)
, NVL(sf.r_table3,-1)
, NVL(ac.acc_method,-1)
, NVL(ac.Bill$Group,-1)
, NVL(ac.Status,-1)
, NVL(ap.r$tarif$srv,-1)
, NVL(ap.typ,-1)
, NVL(c.city,-1)
, CUBE( DECODE(GREATEST(BITAND(NVL(sf.status,0),32),BITAND(NVL(sf.status,0),8),BITAND(NVL(sf.status,0),2),BITAND(NVL(sf.status,0),1))
, 0,-1,GREATEST(BITAND(NVL(sf.status,0),32),BITAND(NVL(sf.status,0),8),BITAND(NVL(sf.status,0),2),BITAND(NVL(sf.status,0),1)))
, NVL(sf.table1,-1)
, NVL(D.r$table6$code,9999)
)
HAVING (GROUPING(NVL(D.r$table6$code,9999)) = 0)
OR (GROUPING(NVL(sf.table1,-1)) = 1)

Gives the plan:

SELECT STATEMENT Optimizer MODE=RULE 1 2
RECURSIVE EXECUTION .SYS_LE_5_0
RECURSIVE EXECUTION .SYS_LE_5_1
RECURSIVE EXECUTION .SYS_LE_5_2
RECURSIVE EXECUTION .SYS_LE_5_3
TEMP TABLE TRANSFORMATION
VIEW 40 7 K 2
TABLE ACCESS FULL SYS.SYS_TEMP_0FD9D6654_B6E74E3 40 6 K 2

What happened at all and how can i optimize this query - i cannot see the "real" plan.
Trace file shows me a couple of inserts into sys temp tables. Can i control execution of this query?

Tom Kyte
March 23, 2005 - 8:48 am UTC

to tune, the first step would be removal of all hints.

then ask "why the heck am an NVL'ing" what did I do to myself there.




NVL is not a point here...

A reader, March 23, 2005 - 11:45 am UTC

Thank you Tom...
But removal of all hints would not generate different plan.
Developers of this query used hints for plan stability(they do so with all queries).
Question was: how can i get "normal plan from this query" without all that "recursive bla-bla-bla". I'm not asking you to optimize it for me, just clarify a little this plan...

Tom Kyte
March 23, 2005 - 6:11 pm UTC

sorry -- get rid of the hints. developers are doing the wrongest thing ON THE PLANET.

you cannot get rid of that which is mandatory. nothing wrong with recursive blah blah blah (if there were, well, hey -- we wouldn't have added the capability)

Thank you once more

Andrey, March 24, 2005 - 3:31 am UTC

Dear Tom!
Can you kindly redirect me to discussion of disadvantages of hints? I thought that using hints and stored outlines are nearly the same thing. Developers used this to ensure that the plan on the customers database would be the same. How they had to do this by another way? Export-import statistics?
And where in the docs i can read about "recursive execution" and "temp table transformation", I'm interesting
when this steps appear and why?

Thank you for your great work!

Tom Kyte
March 24, 2005 - 8:47 am UTC

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:36496737148112 <code>

see my short write up on Dominic's presentation there.

READER

A reader, January 20, 2006 - 2:25 pm UTC

may i know how to read the explain plan output..i have your book "expert oracle by design" but i could not understand the algo in there ..could you please tell me that "in language" like do we have to start reading the most indented statement first and then statement next to it..how exactly it goes..like that

thanks in advance

Reader

Sachin, January 23, 2006 - 12:13 am UTC

hi tom

I have read all that was written on this site and any other I could find by searching google. I have some doubts.

1. For reading the Explain plan you have to start with the most indented statement. It worked fine for all the queries. I have read so far except this below why its explain plan starts from table access full of customers and not from most indented one.

SELECT customer_name
FROM customers a
WHERE EXISTS
(
SELECT 1
FROM invoices_view b
WHERE b.customer_id = a.customer_id
AND number_of_lines > 100
)
ORDER BY customer_name;

ID PARENT OPERATION OBJECT_NAME
---- ------ ----------------------------------- ------------------------------
0 SELECT STATEMENT
1 0 SORT ORDER BY
2 1 FILTER
3 2 TABLE ACCESS FULL CUSTOMERS
4 2 VIEW INVOICES_VIEW
5 4 FILTER
6 5 SORT GROUP BY
7 6 NESTED LOOPS
8 7 TABLE ACCESS BY INDEX ROWID INVOICES
9 8 INDEX RANGE SCAN INVOICES_CUSTOMER_ID
10 7 INDEX RANGE SCAN INVOICE_ITEMS_PK


2. When you have made a tree for the explain plan output. Could I say that " that tree is only and only possible if you know both id and parent id of the statement from the plan table. With knowing only id that is not possible.

Tom Kyte
January 23, 2006 - 9:50 am UTC

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:231814117467#7344298017927 <code>


you can use the parent and id to build a graph of what feeds into what.

but here you have an exists working as a filter - like in a loop.

reader

A reader, January 23, 2006 - 11:04 am UTC

above the 2) part is totally different from the 1)..and i am sorry to say you have not answered both my questions.....i know that link already i have read that 10 times....

thanks

Tom Kyte
January 23, 2006 - 11:49 am UTC

sorry - this is not for new questions here - that is rather "stated", I pointed to link I thought you would find useful. I said "see that filter, you are filtering"

and of course, without having child and parent ids, walking a tree is not quite "possible"?


ID PARENT OPERATION OBJECT_NAME
---- ------ ----------------------------------- ------------------------------
0 SELECT STATEMENT
1 0 SORT ORDER BY
2 1 FILTER
3 2 TABLE ACCESS FULL CUSTOMERS
4 2 VIEW INVOICES_VIEW
5 4 FILTER
6 5 SORT GROUP BY
7 6 NESTED LOOPS
8 7 TABLE ACCESS BY INDEX ROWID INVOICES
9 8 INDEX RANGE SCAN INVOICES_CUSTOMER_ID
10 7 INDEX RANGE SCAN INVOICE_ITEMS_PK

your filter operation is being applied against the table access full. It is something that is happening to the rows as they come out of the full scan. Then there is the plan for what is happening to them there.

Question on your response from above on Dec 14, 2004

Bernice Lam, February 14, 2006 - 9:46 pm UTC

Hi Tom,

I have a question. In the above thread on Dec 14, 2004, your answer to the user's question on why he sees "cpu costing is off" message in his 9i database is :

"cpu costing is off in 9i, on in 10g -- if it turns on in 9i, it is generally "a product issue", it is off by default"

What do you mean by "a product issue"?
The reason for my question - I have a 9i database (as a backend to a third-party app). I tracked down a problem sql and want to see its execution plan. I followed your instructions in your Effective Oracle by Design book to grab the execution plan from v$sql_plan and throw it into a plan table. When I review the plan for various sql (tested using other sql), I noticed some have the message "cpu costing is off" and majority have the cpu cost in there. Based on your response in this thread, in 9i, cpu costing is off by default. Then, I am confused why some of the execution plan shows the cpu cost. Is there a parameter that controls this?

thanks,
Bernice

Tom Kyte
February 15, 2006 - 8:38 am UTC

a product issue is my euphemism for "bug"

Thanks.

Bernice Lam, February 15, 2006 - 12:57 pm UTC

Ahhh. Gotcha.

If they do happen to show up, are these numbers reliable? Or I should just ignore it?

thanks,
B

Tom Kyte
February 15, 2006 - 1:21 pm UTC

cpu costing was to be "not the default", but there were cases where it would come on. It should not affect the answer (that would be a serious "product issue"), but it could affect the generated plan.

so it would be an issue if you had poorly performing queries and this was on.

Explain plan for selects in the select clause

A reader, February 24, 2006 - 6:27 am UTC

Hi Tom,
  I was trying to compare the following two selects (simplified for this example).  In the first case, the plan does not even show the read from the ts_staff table.  COuld you please explain why that is so?  Am I missing something? Thanks.

SQL> select
  2    (select first_name || ' ' || last_name from ts_staff
  3     where usercode = tst.usercode), usercode
  4  from ts_staff_tasks tst
  5  where project_code = :b1;

no rows selected


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TS_STAFF_TASKS'
   2    1     INDEX (RANGE SCAN) OF 'TS_STAFF_TASKS_PROJ_IND' (NON-UNI
          QUE)





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



SQL> select first_name || ' ' || last_name, tst.usercode
  2  from ts_staff_tasks tst, ts_staff s 
  3  where tst.usercode = s.usercode 
  4  and tst.project_code = :b1;

no rows selected


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   NESTED LOOPS
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'TS_STAFF_TASKS'
   3    2       INDEX (RANGE SCAN) OF 'TS_STAFF_TASKS_PROJ_IND' (NON-U
          NIQUE)

   4    1     TABLE ACCESS (BY INDEX ROWID) OF 'TS_STAFF'
   5    4       INDEX (UNIQUE SCAN) OF 'PK_STAFF' (UNIQUE)




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


Chandini
 

Tom Kyte
February 24, 2006 - 8:36 am UTC

because you are using an old version of Oracle which did not show scalar subqueries in the explain plan.

Please clarify

Suresh, March 10, 2006 - 2:33 am UTC

Hello Tom,
Can execution plans be different for the
same set of data retrieved??

I think as "yes" since a query can be put in a variety of ways
to retrieve the same set of data.

If that's the case,can that statement be shared
across different user sessions??

Does Oracle choose best of execution plans and put that in the
shared pool??

How useful that can be??

Tom Kyte
March 10, 2006 - 12:12 pm UTC

this doesn't really make sense.

execution plans are coupled with queries.
execution plans are NOT coupled with "sets of data".




Different Explain Plans

CT.VELU, March 23, 2006 - 11:07 am UTC

Hi Tom
I have a query which is selecting from a local table ,two remote tables from two different databases.When I run this query in sqlplus(fom TOAD), it takes a second.when I run the same query thru website it takes two minutes 17 seconds.Explain plan looks different.I traced the website session, when I tried to tkprof I am getting an error message.Looking in to the trace file I found lot of dblink waits.so I couldn't post the tkprof result here.
Why we are seeing two different explain plans for the same query.what is the logic behind this?
Thank you very much for your valuable time
with best
CT

Tom Kyte
March 23, 2006 - 11:21 am UTC

first, did you retrieve all of the rows from toad? toad doesn't typically get them all.

alter system ...

Rahul, March 23, 2006 - 1:26 pm UTC

<snip...>
scott@dev8i> alter system set timed_statistics=true;
System altered.
<...snip>

Would alter session suffice?

Tom Kyte
March 23, 2006 - 1:57 pm UTC

yes, for tkprof it would

Alexander the ok, March 23, 2006 - 1:30 pm UTC

CT.VELU,

I use TOAD for some things but have had difficulties in the past trying to tune using it. TOAD has it's own plan_table, and I believe it's called the same as the Oracle version so you may be missing one of them and cause an error.

If you execute your query using the "Execute as script" button, to the far left of the script editor, it will fetch all the rows because what Tom said is correct. Using the "Execute Statement" will not always return everything depending on how many rows are returned.

My advice would be to use the command line version of sqlplus and use

set timing on
set autotrace traceonly

and see what happens.

Different Explain plan(Yes I got all the 55 rows)

CT.Velu, March 23, 2006 - 1:58 pm UTC

Hi Tom
Thank you for the response.Yes I got all the rows in toad.I executed it in sql plus command line version.The query takes 1.13 seconds.when I run the query in a test java program it takes 2 second.(Explain plans are same for the above two scenario).The problem arises when I run the query in my website code.
Version 10.1.0.3.0
with best
CT

Tom Kyte
March 23, 2006 - 2:02 pm UTC

1.13 verses 2 seconds - that is not measurable by any standard.

tkprof it. show us the tkprofs here.

Explain Plan different(tkprof)

CT.Velu, March 23, 2006 - 2:54 pm UTC

Hi Tom
Following is the query and the tkprof for that when I am running in sqlplus command line version.In the website now the other team has changed the code, so I couldn't get the explain plan or tkprof for that.I know it is difficult for you to analyse, I would appreciate if you can tell me why we are getting two different explain plans( one in sqlplus and one while executing from website thru JDBC) or methods of accessing when we use remote databases(this is not the case when we use local tables).
Please enlighten me on this.
select
to_char(s.vp_dst_nbr) cd,
d.name dscr,
sum
(
case
when
yr = 2005
then posted_cases
else 0
end
) posted_cases,
sum
(
case
when
yr = 2005
then ext_net_amt
else 0
end
) ext_net_amt,
sum
(
case
when
yr = (2005-1)
then posted_cases
else 0
end
) py_posted_cases,
sum
(
case
when
yr = (2005-1)
then ext_net_amt
else 0
end
) py_ext_net_amt
from
p.websync_sales_ru_view s,
p.ct_vp_dst_na_view d
where
s.vp_dst_nbr > 0 and
s.vp_dst_nbr=d.vp_dst_nbr and
s.yr in (2005,2005-1) and
(
('NAT' = 'NAT') or
('NAT' = 'REG' and s.mfg_lvl_1=0) or
('NAT' = 'BRK_HQ' and s.brk_hq_nbr=0) or
('NAT' = 'BRK_BR' and s.vp_brk_nbr=0)
) and
(
('YR'='MTH' and s.mth=0 ) or
('YR'='QTR' and s.per=0 ) or
'YR'='YR'
) and
(0=0 or 0=0 or 0=0)
group by
s.vp_dst_nbr,
d.name
order by
ext_net_amt desc

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.02 0 8 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 56 1.23 1.30 0 1418 0 55
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 58 1.25 1.34 0 1426 0 55

Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 69

Rows Row Source Operation
------- ---------------------------------------------------
55 SORT ORDER BY (cr=1418 pr=0 pw=0 time=1312768 us)
55 SORT GROUP BY (cr=1418 pr=0 pw=0 time=1312768 us)
244984 HASH JOIN (cr=1418 pr=0 pw=0 time=784101 us)
60 NESTED LOOPS (cr=2 pr=0 pw=0 time=5120 us)
60 NESTED LOOPS (cr=0 pr=0 pw=0 time=5120 us)
60 REMOTE (cr=0 pr=0 pw=0 time=4096 us)
60 REMOTE (cr=0 pr=0 pw=0 time=40960 us)
60 INDEX UNIQUE SCAN VP_DST_LOCAL_PKEY (cr=2 pr=0 pw=0 time=2048 us)(object id 52860)
244984 TABLE ACCESS FULL WEBSYNC_SALES_RU (cr=1416 pr=0 pw=0 time=244983 us)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 58 0.00 0.00
SQL*Net message from client 58 0.00 0.02
SQL*Net message to dblink 122 0.00 0.00
SQL*Net message from dblink 122 0.00 0.03
********************************************************************************



Tom Kyte
March 23, 2006 - 3:05 pm UTC

it is not difficult.

it is quite simply "not possible".

I don't know what query you actually run in the "website" (it is likely different in some fashion), and they have changed it (so not seeing the point even since the query no longer exists?)


I could guess (but won't).

Timing issues

CT.Velu, March 23, 2006 - 3:00 pm UTC

Hi tom
The timing issue
1.sqlplus 1.3 second
2.stand alone java program 2 second(I am using a timer in java to find this so this ia rounded to seconds)
3.website 2 minutes and 17 seconds.
Thank you very much
CT

Tom Kyte
March 23, 2006 - 3:05 pm UTC

need tkprofs.

Confusing AUTOTRACE output

Carl Bruneau, March 24, 2006 - 4:30 pm UTC

Hi Thomas,

When I run a query I see that it does not use the index I was hoping it would use.

So I use AUTOTRACE to see what is happening.

The query is very simple:

select *
from hastus_dblkrte
WHERE workday_date = TO_DATE(:workday_date, 'YYYYMMDD') AND
division_id = :division_id;

When I use the AUTOTRACE I have this output:

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=407 Card=21 Bytes=840)
1 0 TABLE ACCESS (FULL) OF 'HASTUS_DBLKRTE' (Cost=407 Card=21 Bytes=840)

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

But if I use an hint on that query here is the output from AUTOTRACE:

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=593 Card=21 Bytes=840)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'HASTUS_DBLKRTE' (Cost=593 Card=21 Bytes=840)
2 1 INDEX (RANGE SCAN) OF 'HASTUS_DBLKRTE_IDX1' (NON-UNIQUE) (Cost=11 Card=2062)

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

What I really don't understand in those 2 outputs is:

Why the cost is evaluated so hight when consistent gets is 2 and why the cost is lower when the consistent get is 2677? What could explain that?

Maybe I missed something but it think that the index should be used since it incurs a consistent gets of 2 compare to a consistent get of 2677 for a full table scan.

Best regards,

Carl




Tom Kyte
March 24, 2006 - 5:45 pm UTC

the optimizer believes it will get 2062 rows from the index, goto the table using single block IO 2062 times to find 21 rows that match.

It prefered full scanning to doing that 2062 times (single block IO)

since the table is about 2000 blocks (little more) and it thinks 2000 rows resulting in 2000 single block accesses - it is saying "full scan"

I don't know what column(s) the index is on so cannot comment further.

Confusing AUTOTRACE output

Carl Bruneau, March 27, 2006 - 9:05 am UTC

Hello Thomas,

Ok the Optimizer believe it will get 2062 because we have 217 distinct workday_date on a total of 447514 rows in the table.

Here is the definition of the index (function-based index unsing NLSSORT function):

CREATE INDEX HASTUS_DBLKRTE_IDX1 ON HASTUS_DBLKRTE
(WORKDAY_DATE, NLSSORT(ROUTE_ID,'nls_sort=PUNCTUATION'))

Since the Optimizer assume we have a uniform distribution for the values of this column the number of rows it will get each time represent only 0.46% of the total rows of the table ((2062/447514)*100). For me it seem very selective. Isn't it?

Can we say that the Optimizer does not make the best choice?

The other thing that I really don'tunderstand is why the AUTOTRACE give two section (Execution plan and statistics) that contradict each other. Is it because one is theorical and the other is real?

Best regards,

Carl

Tom Kyte
March 27, 2006 - 10:09 am UTC

I did talk about the clustering above - Look at the full scan:

2677 consistent gets


that table is about 2600 blocks. the optimizer thinks 2000 rows. I'd guess the clustering_factor of this index is near the number of ROWS in the table, not the number of blocks (eg: the data in the table is not sorted by this date field)

did I guess right?


autotrace shows the explain plan - the guess as to what will happen.
autotrace shows statistics, observed values after running the query.

Confusing AUTOTRACE output

Carl Bruneau, March 27, 2006 - 11:16 am UTC

Thomas,

Here is the clustering factor for this index: 126248

SQL> SELECT DISTINCT_KEYS "DISTINCT", LEAF_BLOCKS, CLUSTERING_FACTOR "CF", BLEVEL "LEVEL", AVG_LEAF_BLOCKS_PER_KEY "ALFBPKEY"
  2  FROM USER_INDEXES
  3  WHERE INDEX_NAME = 'HASTUS_DBLKRTE_IDX1';

 DISTINCT LEAF_BLOCKS         CF      LEVEL   ALFBPKEY
---------- ----------- ---------- ---------- ----------
     33776        2020     126248          2          1

Not sure what it mean. I will read about it.

Regards,

Carl 

Tom Kyte
March 27, 2006 - 3:11 pm UTC

the clustering factor is a measure of how sorted the table data is in regards to the index.

if the clustering factor is near the number of blocks in the table - then the table and index are sorted in the same way (eg: an index on a column populated by a sequence typically has a really 'good' clustering factor).

If the clustering factor is near the number of rows int eh table - then the table and index are not sorted at all the same.


The clustering factor says how many logical IO's it would take to read every row out of the table via the index one row at a time. Your clustering factor is much nearer the number of rows than the number of blocks in the table - therefore if we had to read 2600 rows out of the table via the index - we would hit EVERY BLOCK in the table - so why not full scan and just get it over with.

Confusing AUTOTRACE output

Carl Bruneau, March 27, 2006 - 12:26 pm UTC

Hello Thomas,

I saw the impact of clustering factor in other example you gave. Its the first time I hear about that. Very very usefull. Thanks.

But in this particular example, is it that having the CF to 126248 and the number of rows in the table at 447 000 suffisant to explain the Optimizer choice?

Maybe now we have to test, test and re-test modifications on values for OPTIMIZER_INDEX_CACHING and/or OPTIMIZER_INDEX_COST_ADJ since we are using default values and have many queries issues like this one.

Do you think its where we should work (for that case)?

Regards,

Carl


Tom Kyte
March 27, 2006 - 3:18 pm UTC

absolutely - it thinks will have to read almost every block anyway.

the problem is - for this particular query - there were 2 rows. Use index.

However, you must therefore have some executions of this query that get 4,000 rows - use full scan.

since you are using binds - histograms would not be called for, unless the inputs you provide ALWAYS lead to the plan you would like.

so - sometimes index and sometimes not. If you would always like index - i'd rather use a first rows oriented opimization rather than play with init.ora parameters.

Confusing AUTOTRACE output

Carl Bruneau, March 27, 2006 - 3:49 pm UTC

Hello Thomas,

4000 rows over 447 000 rows is less than 4%. Based on what can you confirm that a TS (table scan) would be better than the use of the index?

Also just read in you excellent book (congratulation!) Effective Oracle By Design, that, for oltp system, OPTIMIZER_INDEX_CACHING sould be considered to be set at 90 and OPTIMIZER_INDEX_COST_ADJ should be considered to be set at 25. So I think that we should, at least reconsider the default value for those 2 parameters.

I understand the concept of clustering factor for the indexes but I realy don't understand the huge impact on the optimizer choice. Could you please illustrate?

Best regards,

Carl

Tom Kyte
March 27, 2006 - 8:18 pm UTC

forget rows.

think BLOCKS.

the table is small (the index is about the same size), the full scan read:

2677 consistent gets


and given the clustering factor of the index - it believes it will have to read about that many blocks PLUS the index. So it said "forget the index, what a waste".

I would not set the init.ora parameters as my first step - not a change. better to be "standard" than to be "different from the rest".




Confusing AUTOTRACE output

Carl Bruneau, March 28, 2006 - 9:28 am UTC

Hello Thomas,

Thanks for your patience.

I made some more tests.

With the worst case (workday_date with 5940 rows highest occurences), AUTOTRACE statistics gives me:

1902 consistent gets (using idx1)

Its better than:

2677 consistent gets (for the table scan)

Given that result, I don't understand why the Optimizer scan the table.

Here is the result of my test:

SQL> begin
  2    :workday_date := '20051221'
  3  ;
  4  end;
  5  /

PL/SQL procedure successfully completed.

SQL> set autot traceonly
SQL> select /*+ INDEX(HASTUS_DBLKRTE HASTUS_DBLKRTE_IDX1) */ * 
  2   -- test2
  3  from  hastus_dblkrte
  4  WHERE workday_date = TO_DATE
(:workday_date, 'YYYYMMDD') AND
  5         division_id = :division_id;

no rows selected

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=616 Card=21 Bytes=840)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'HASTUS_DBLKRTE' (Cost=616 Card=21 Bytes=840)
   2    1     INDEX (RANGE SCAN) OF 'HASTUS_DBLKRTE_IDX1' (NON-UNIQUE)(Cost=11 Card=2060)

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

 

Tom Kyte
March 28, 2006 - 3:55 pm UTC

set arraysize 2 and rerun, then you'll see "why"

the optimizer works with numbers - it has to make assumptions. it sees the clustering factor and says "table not sorted by index", it sees the number of estimated rows (lots of them, near the number of blocks), it says every fetch this person does will cause us to read another block

so it full scans.

Confusing AUTOTRACE output

Carl Bruneau, March 29, 2006 - 9:31 am UTC

Hello Thomas,

The more I learn from you, the more the title of this tread should be "Bad AUTOTRACE interpretation".

Here is the result having set arraysize to 2 (with an without the index).

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


Consistent Gets have fall down to 0. Not sur to understand why?

Also how could we explain this:

I have put timing on in sqlplus.

With the table scan:

Elapsed: 00:00:02.01

With the index (idx1):

Elapsed: 00:00:00.01

Best regards,

Carl

Tom Kyte
March 29, 2006 - 11:08 am UTC

you did that as sys didn't you.

don't do that (or anything really) as sys.

Confusing AUTOTRACE output

Carl Bruneau, March 29, 2006 - 12:11 pm UTC

Tomas,

Yesterday afternoon I gave this user the dba role to do some tests.

I just rekoked the dba role from this user but still have 0 consistent get. Don't know why?

The only role this user have now is the PLUSTRACE role.

?

Regards,

Carl

Tom Kyte
March 29, 2006 - 12:41 pm UTC

"prove it"

cut and paste the show user output and the query and the autotrace output.

Confusing AUTOTRACE output

Carl Bruneau, March 29, 2006 - 12:59 pm UTC

Hello Thomas,

I just closed sqlplus and reopen it and it solve this issue (having 0 in the AUTOTRACE statistics).

Here is the output:

SQL> show user
USER is "TAR"
SQL> set arraysize 2
SQL> 
SQL> select  * 
  2  -- test11
  3  from  hastus_dblkrte
  4  WHERE workday_date = TO_DATE
:workday_date, 'YYYYMMDD') AND
  5        division_id = :division_id;

no rows selected

Elapsed: 00:00:01.02

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=407 Card=21 Bytes=1008)
   1    0   TABLE ACCESS (FULL) OF 'HASTUS_DBLKRTE' (Cost=407 Card=21 Bytes=1008)

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

SQL> 
SQL> select /*+ INDEX(HASTUS_DBLKRTE HASTUS_DBLKRTE_IDX1) */ * 
  2  -- test12
  3  from  hastus_dblkrte
  4  WHERE workday_date = TO_DATE(:workday_date, 'YYYYMMDD') AND
  5        division_id = :division_id;

no rows selected

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=593 Card=21 Bytes=1008)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'HASTUS_DBLKRTE' (Cost=593 Card=21 Bytes=1008)
   2    1     INDEX (RANGE SCAN) OF 'HASTUS_DBLKRTE_IDX1' (NON-UNIQUE)(Cost=11 Card=2062)

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

Not the result I was expecting...

Best regards,

Carl 

Tom Kyte
March 29, 2006 - 2:21 pm UTC

why not?

Confusing AUTOTRACE output

Carl Bruneau, March 29, 2006 - 2:43 pm UTC

Hello Thomas,

I was expecting the consistent get on the table scan to be cut in two.

As I read in Effective Oracle By Design p.108, 109.

The timing is still higher for the table scan:

Elapsed: 00:00:01.03 vs Elapsed: 00:00:00.01

Now for the table scan it goes constantly on disk: 2590 physical reads.

Weird... I am all messed up.

Regards,

Carl

Tom Kyte
March 29, 2006 - 2:49 pm UTC

why - you did not actually *fetch* any data whatsoever.

there were never any block to be revisited across fetches, you only did a single fetch!

Confusing AUTOTRACE output

Carl Bruneau, March 29, 2006 - 3:13 pm UTC

Thomas,

lets recap...

Based on what on the latest statistics output I should have been able to answer to my question "why the optimizer actually scan the table" (after having set arraysize to 2)?

Sorry for my confusion...

Regards,

Carl





Tom Kyte
March 29, 2006 - 7:07 pm UTC

"the answer is the same" regardless of the array size - I don't care WHAT the arraysize is, neither does the optimizer.

optimizer sees 2000+ rows likely

optimizer sees clustering factor much closer to number of rows than blocks, index not very good as far as retrieving data that is clumped together

optimizer sees 2000+ blocks in the table

optimizer says "better to scan"

Confusing AUTOTRACE output

Carl Bruneau, March 30, 2006 - 8:42 am UTC

Hello Thomas,

On the theory I have learn a lot reading your answers and your book. You are doing a very great job on this site.

Oracle should offer a 2 or 3 days formation on how to anaylyze optimizer behavior with specific queries using all the tools documented in your book. Are they?

But on the practical side I still don't understand why, when I set TIMING ON, it takes a lot more times to have the output of the AUTOTRACE when it scans the table compare to when it takes index IDX1.

Is it normal? Is it something I doesn't have to care off?

Best regards,

Carl



Tom Kyte
March 31, 2006 - 11:26 am UTC

I don't understand the last bit about "on the pratical side". what do you mean?

Confusing AUTOTRACE ouput

Carl Bruneau, March 31, 2006 - 1:08 pm UTC

Hello Thomas,

What I mean is:

Why setting TIMING on in sqlplus,

The elapsed time is higher for the table scan:

Elapsed: 00:00:01.03 vs Elapsed: 00:00:00.01 (index used).

Is it something normal? Something that I should be aware of? Something I should ignore in a case like this?

Hope it is clearer.

Regards,

Carl




Tom Kyte
March 31, 2006 - 1:23 pm UTC

one of them did physical IO, one did not.

had nothing to do with setting timing on or off - it had to do with the fact that one did physical reads.

Confusing AUTOTRACE output

Carl Bruneau, March 31, 2006 - 4:41 pm UTC

Hello Thomas,

Regardless of the order I execute those two queries (Table Scan vs Index used with an HINT), it is always the TS that make physical I/O the other , not a single one... I suppose you will tell me it is normal. Why?

Regards,

Carl

Tom Kyte
March 31, 2006 - 4:47 pm UTC

because that is the way full scans of large tables work - they push the blocks out of the cache rapidly. so they are not there when you go to full scan again.

if you used the "cache" hint that would change the way the blocks were cached for the full scan

Confusing AUTOTRACE output

Carl Bruneau, April 03, 2006 - 8:32 am UTC

Hello Thomas,

With the "CACHE" hint, the physical reads have now fallen to 0.

SQL> SELECT /*+ CACHE(HASTUS_LINKDT) */ *
  2  FROM HASTUS_LINKDT 
  3  WHERE dt_ver = :dt_ver AND 
  4        vehgroup_id IS NULL 
  5  ORDER BY dt_ver ASC, vehgroup_id ASC, start_place ASC, end_place ASC;

no rows selected


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3246 Card=1453 Bytes=117693)
   1    0   SORT (ORDER BY) (Cost=3246 Card=1453 Bytes=117693)
   2    1     TABLE ACCESS (FULL) OF 'HASTUS_LINKDT' (Cost=3223 Card=1453 Bytes=117693)

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

Thank's for your help.

Best regards,

Carl 

Confusing AUTOTRACE output

Carl Bruneau, April 03, 2006 - 8:37 am UTC

Ouppss... working on too many queries at the same time. Here is the correct output:

SQL> select /*+ CACHE(HASTUS_DBLKRTE) */ *
  2  from  hastus_dblkrte
  3  WHERE workday_date = TO_DATE(:workday_date, 'YYYYMMDD') AND
  4         division_id = :division_id;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=407 Card=21 Bytes=1008)
   1    0   TABLE ACCESS (FULL) OF 'HASTUS_DBLKRTE' (Cost=407 Card=21 Bytes=1008)

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

Confusing AUTOTRACE output

Carl Bruneau, April 04, 2006 - 2:06 pm UTC

Hello Thomas,

I know I am not supposed to ask another question on this thread but since almost two year, every time me an my colleague tried to post a new question on your site it was not possible.

Can I? It is related to function-based indexes (using NLSSORT) and histograms. It seems to me that histograms are not used when we have function-based index.

If not, ignore this post and I will open an SR on Metalink.

Best regards,

Carl

please clarify

sd, April 07, 2006 - 12:03 pm UTC

execution plans are coupled with queries.
execution plans are NOT coupled with "sets of data".


In response to the above, I think the guy here saying that if the number of rows get changed in different system, then do explain plan will change for this.

Suppose I have a query, at the design level I just took explain plan (without data offcourse) of this query for documentation purpose, now at production with millions of records will this explain plan change or it'll be same, (All other conditions like index etc. are same)...




Tom Kyte
April 08, 2006 - 9:10 am UTC

execution plans (assuming cost based optimizer here) are a function of

the query

and the statistics on the underlying referenced data

and the optimizer settings in place on the server

and the physical structures that are available

in addition to other bits of data.


If you change any of them (statistics for example) then you would expect the plans to be subject to change - absolutely.

me again

sd, April 10, 2006 - 7:21 am UTC

Thanks for your response..
In my case everything else is same onboth the environment just the data is different,
in test env its 1000 rows and in dev its millions of rows, so will it make any difference in explain plan, again everything else is same...

Tom Kyte
April 10, 2006 - 7:54 am UTC


yes, the volume of data will have a profound effect on the plans.
^^^^^^^^


I would hope most of them would be different in test than in production.

Difference in Cardinality

max, April 10, 2006 - 10:10 am UTC

i 've observed hugh differences in cardinality (for an unique index scan) within execution plans generated for the very same statement on different instances A and B (B has been created from A):

instance A:

13 12 NESTED LOOPS (Cost=6 Card=219 Bytes=15768)
14 13 NESTED LOOPS (Cost=5 Card=1 Bytes=65)
15 14 TABLE ACCESS (BY INDEX ROWID) OF 'TAB_A' (Cost=4 Card=1 Bytes=33)
16 15 INDEX (RANGE SCAN) OF 'TAB_A_IDX2' (NON-UNIQUE) (Cost=3 Card=1)
17 14 TABLE ACCESS (BY INDEX ROWID) OF 'TAB_B' (Cost=1 Card=1 Bytes=32)
18 17 INDEX (UNIQUE SCAN) OF 'TAB_B_IDX' (UNIQUE)
19 13 INDEX (UNIQUE SCAN) OF 'TAB_C_IDX' (UNIQUE) (Cost=1 Card=219 Bytes=1533)

instance B:

10 9 NESTED LOOPS (Cost=6 Card=2 Bytes=100)
11 10 NESTED LOOPS (Cost=5 Card=1 Bytes=45)
12 11 TABLE ACCESS (BY INDEX ROWID) OF 'TAB_A' (Cost=4 Card=1 Bytes=26)
13 12 INDEX (RANGE SCAN) OF 'TAB_A_IDX1' (NON-UNIQUE) (Cost=3 Card=1)
14 11 TABLE ACCESS (BY INDEX ROWID) OF 'TAB_B' (Cost=1 Card=1 Bytes=19)
15 14 INDEX (UNIQUE SCAN) OF 'TAB_B_IDX' (UNIQUE)
16 10 INDEX (UNIQUE SCAN) OF 'TAB_C_IDX' (UNIQUE) (Cost=1 Card=2 Bytes=10)

although the row counts of the base table of that certain index are (still very much) the same (only 500 rows difference for 200.000 rows overall) in both A and B the cardinality reported for A is round about 100 times the cardinality for B -- leading to impacts for subsequent steps ...

what could have caused such a difference?

Tom Kyte
April 11, 2006 - 10:16 am UTC

compare the statistics (user_tables, user_indexes, user_histograms) for the two instances.

We are talking the same versions right.

do we get to see the query or must we guess?

Privileges required to generate execution Plan

A reader, August 28, 2006 - 3:34 pm UTC

Tom,

I have "select any table" privilege system privilege granted to me. I am able to select from other user schema using schemaname.tablename, but when I try to generate a execution plan for the same select statement using plan_table, I get the error:
ORA-01031: insufficient privileges

Do these select privileges on the tables have to be granted directly to me and not via a System Privilege/Role?

Thanks

Tom Kyte
August 28, 2006 - 4:46 pm UTC

get rid of that privilege, geez.  

likely, you are not allowed to insert perhaps into this plan table, all you need is select on the underlying object:

ops$tkyte%ORA10GR2> create user a identified by a quota unlimited on users default tablespace users;

User created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> grant create session, create table to a;

Grant succeeded.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table t ( x int );

Table created.

ops$tkyte%ORA10GR2> grant select on t to a;

Grant succeeded.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> connect a/a
Connected.
a%ORA10GR2> @?/rdbms/admin/utlxplan

a%ORA10GR2> create table PLAN_TABLE (
  2          statement_id       varchar2(30),
  3          plan_id            number,
  4          timestamp          date,
  5          remarks            varchar2(4000),
  6          operation          varchar2(30),
  7          options            varchar2(255),
  8          object_node        varchar2(128),
  9          object_owner       varchar2(30),
 10          object_name        varchar2(30),
 11          object_alias       varchar2(65),
 12          object_instance    numeric,
 13          object_type        varchar2(30),
 14          optimizer          varchar2(255),
 15          search_columns     number,
 16          id                 numeric,
 17          parent_id          numeric,
 18          depth              numeric,
 19          position           numeric,
 20          cost               numeric,
 21          cardinality        numeric,
 22          bytes              numeric,
 23          other_tag          varchar2(255),
 24          partition_start    varchar2(255),
 25          partition_stop     varchar2(255),
 26          partition_id       numeric,
 27          other              long,
 28          distribution       varchar2(30),
 29          cpu_cost           numeric,
 30          io_cost            numeric,
 31          temp_space         numeric,
 32          access_predicates  varchar2(4000),
 33          filter_predicates  varchar2(4000),
 34          projection         varchar2(4000),
 35          time               numeric,
 36          qblock_name        varchar2(30),
 37          other_xml          clob
 38  );

Table created.

a%ORA10GR2> explain plan for select * from ops$tkyte.t;

Explained.
 

explain plan parent id and such to interpret execution plans

A reader, October 29, 2006 - 5:30 pm UTC

Hi

When use set autotrace on we see execution plan as follows

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=1 Bytes=26)
1 0 SORT (AGGREGATE)
2 1 MERGE JOIN (Cost=6 Card=11 Bytes=286)
3 2 SORT (JOIN) (Cost=3 Card=14 Bytes=182)
4 3 TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=14 Bytes=182)
5 2 SORT (JOIN) (Cost=3 Card=82 Bytes=1066)
6 5 TABLE ACCESS (FULL) OF 'DEPT' (Cost=1 Card=82 Bytes=1066)


We get the parent id and child id, however using dbms_xplan.display we dont get any of those, what is the new way to interpret executions plans?

Cheers

Tom Kyte
October 29, 2006 - 5:35 pm UTC

you don't need them, you have the indentation - that is all that is needed.

plan pseudo code

A reader, November 23, 2006 - 1:22 am UTC

I read the query and explain plan below from your books Effective
Oracle by Design on chapter Effective SQL :

alter session set optimizer_goal=first_rows;

select ename, dname
from emp, dept
where emp.deptno(+) = dept.deptno

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=43 Card=1909 Bytes=55361)
1 0 NESTED LOOPS (OUTER) (Cost=43 Card=1909 Bytes=55361)
2 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=409 Bytes=8998)
3 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=2 Card=5 Bytes=35)
4 3 INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (NON-UNIQUE)


Regarding on the output plan you write the pseudo code :

For x in ( select * from DEPT )
Loop
Found_record = FALSE
For y in ( select * from EMP where EMP.deptno = X.deptno )
Loop
Found_record = TRUE;
Output joined record
End loop
If (NOT found_record)
Then
OUTPUT a record anyway, with NULL values for EMP columns
End if
End loop


My question is what the pseudo code for the query plan below?

select /*+ RULE */ t1.object_name, t2.object_name
from t t1, t t2
where t1.object_id = t2.object_id
and t1.owner = 'WMSYS'

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T'
2 1 NESTED LOOPS
3 2 TABLE ACCESS (FULL) OF 'T'
4 2 INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE)

Thanks.

Tom Kyte
November 24, 2006 - 6:14 pm UTC

it is virtually the same, but might be more like this:


for x in (select * from T)
loop
probe t_idx and find all matches, record their rowids and the output from T
in an array
end loop

sort array by rowids
loop over array
retrieve data from T by rowid (sorted rowids to get as many rows from
a single block at a time)

output
end


it is getting some rowids from the index, hoping to hit the same block over and over - to optimize access to the blocks in T again.


basically, same concept.


Plan on the fly

Marcio, November 28, 2006 - 8:03 pm UTC

I fall on a problem today that would be necessary to see the plan on the fly, I mean, we catch up the pid on the OS then I got the sid so I could see the query however I didn't know how to come up with the plan. Just in case, do you have a script for that? I can't use the OEM, because it is not allow over there.

Thanks and regards,

Tom Kyte
November 28, 2006 - 8:11 pm UTC

v$sql_plan?

explain plan is too slow

A reader, December 14, 2006 - 5:32 pm UTC


For some SQL, it is taking a long time to get the explain plan - version 8174 - any possible reasons ?

thanks
Venki

explain plan

A reader, December 16, 2006 - 12:10 am UTC

I am really confused in reading explain plan, even I was finish reading your book.
I am not get the point in reading the plan, here is the example of my confusion

Query (taken from your book) :

select t1.username, sum(t2.object_id)
from t1, t2
where t1.username = t2.owner (+)
group by t1.username;


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
(Cost=158 Card=10000000 Bytes=470000000)
1 0 SORT (GROUP BY NOSORT) (Cost=158 Card=10000000 Bytes=470000000)
2 1 MERGE JOIN (OUTER) (Cost=158 Card=10000000 Bytes=470000000)
3 2 INDEX (FULL SCAN) OF 'T1_USERNAME_IDX' (NON-UNIQUE)
(Cost=26 Card=10000000 Bytes=170000000)
4 2 SORT (JOIN) (Cost=155 Card=10000 Bytes=300000)
5 4 TABLE ACCESS (FULL) OF 'T2' (Cost=98 Card=10000 Bytes=300000)


At the step 3 index full scanning of t1 WITHOUT (as I see from the plan - absence of TABLE ACCESS BY ROWID and the likes) accessing the table to get the data and then merge with data from t2. How this can really happen joining rowid and data..

Please clear my confusion Tom and give me the point I really need.

Tom Kyte
December 16, 2006 - 5:58 pm UTC

you only need username from table T1.

the index T1_USERNAME_IDX has everything we need. We don't need to go to the table since every column we requested is right there in the index.

Many thanks

A reader, December 17, 2006 - 2:57 am UTC


A reader, February 27, 2007 - 6:02 am UTC

Tom,

I have the following example

create table foo1 (a number, b number);

insert into foo1 values(1,2);

insert into foo1 values (2,2);


create table foo2 (a number, b number);

insert into foo2 values (10,1);

insert into foo2 values(11,2);

select (select a from foo2 where foo2.b = foo1.a) x from foo1

But the TKPROF says:

Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: RULE
2 TABLE ACCESS (FULL) OF 'FOO2'
2 TABLE ACCESS (FULL) OF 'FOO1'

Now that is NOT consistent with Explain Plan notes from Oracle:

More about Plans and the order of execution
===========================================

To understand plans and the order of execution, it is necessary to
understand the PARENT -- CHILD relationships involved:

PARENT
FIRST CHILD
SECOND CHILD

In this example, the FIRST CHILD is executed first followed by the
SECOND CHILD, then the PARENT collates the output in some way.

A more complex case is:

PARENT1
FIRST CHILD
FIRST GRANDCHILD
SECOND CHILD

Here the same principles apply, the FIRST GRANDCHILD is the initial operation
then the FIRST CHILD followed by the SECOND CHILD and finally the PARENT
collates the output.


1) I think SQL is executing F001 first and then executing FOO2, which breaks the Parent/Child relationship above. I guess this only happens for Scalar Subquery plans, but is this not wrong?


Our version of Oracle is

"Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production"

Thanks

Ravi

Confusing: Sequence In the EXPLAIN PLAN O/P

A reader, March 23, 2007 - 3:33 pm UTC

Tom,

This is the point where I always get confused, while looking at the EXPLAIN PLAN OUTPUT. In the following output, what would be the sequence of events. Which step, in terms of id nos., will take place first id #4 or id #7 ??

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 146 | 15547 | | |
| 1 | SORT AGGREGATE | | 1 | 146 | | | |
| 2 | NESTED LOOPS | | 1 | 146 | 15547 | | |
| 3 | SORT UNIQUE | | | | | | |
|* 4 | TABLE ACCESS FULL | EXCEPTION_TABLE | 1014 | 76050 | 13512 | | |
| 5 | PARTITION RANGE ITERATOR | | | | | KEY | KEY |
| 6 | PARTITION HASH ITERATOR | | | | | KEY | KEY |
|* 7 | TABLE ACCESS BY LOCAL INDEX ROWID| BASE_LAYER | 1 | 71 | 2 | | |
|* 8 | INDEX UNIQUE SCAN | PK_BASE_LAYER | 1 | | 1 | | |
----------------------------------------------------------------------------------------------------------

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

4 - filter("ET"."OWNER_LAST_NAME" IS NOT NULL AND "ET"."TRANS_POSTING_STATUS"<>'P' AND
"ET"."TRANS_POSTING_STATUS"<>'R' AND "ET"."TRANS_POSTING_STATUS"<>'T' AND "ET"."POSSIBLE_BASEREC_1"<>0
AND "ET"."POSSIBLE_BASEREC_2"=0)
7 - filter("BASE_LAYER"."OWNER_LAST_NAME"="ET"."OWNER_LAST_NAME")
8 - access("BASE_LAYER"."FIPS_STATE_COUNTY"="ET"."FIPS_STATE_COUNTY" AND
"BASE_LAYER"."FIPS_TOWNSHIP"="ET"."FIPS_TOWNSHIP" AND "BASE_LAYER"."APN_SOURCE"="ET"."APN_SOURCE")

Note: cpu costing is off

26 rows selected.

Thanks

ORA-1039

A reader, May 18, 2007 - 11:09 am UTC

I have a readonly data-access id in Oracle for reporting purposes. It has been granted SELECT on a bunch of views.

When this id tries to do a EXPLAIN PLAN FOR select * from some_view; I get a
01039, 00000, "insufficient privileges on underlying objects of the view"
// *Cause: Attempting to explain plan on other people's view without
//         the necessary privileges on the underlying objects of the view.
// *Action: Get necessary privileges or do not perform the offending operation.


I really don't want to grant direct SELECT privs on all the underlying tables to this id, that is the whole reason we built views so that the id has a logical data access layer and is insulated from all the physical table design.

Any other options, thoughts?

Thanks
Tom Kyte
May 18, 2007 - 4:21 pm UTC

don't run explain plan?

Not sure what to say, read your last paragraph there "that is the whole reason..."

well, if you built views to insulate a logical data access layer from a physical, why are you looking to expose it via explain plan? you'd be showing them a BUNCH OF STUFF they could not possibly use or understand - since it is by your definition - hidden from them.


ORA-1039

A reader, May 18, 2007 - 9:49 pm UTC

why are you looking to expose it via explain plan?

I don't! I said as much. But the developers using that id are trying to tune their queries and need to run explain plans, but they aren't able to!

How is this sort of thing typically handled?
Tom Kyte
May 21, 2007 - 10:12 am UTC

you did not say so, you said quite the opposite. You are asking to run explain plan, that would expose the physical schema.

What is typically done? The physical schema is exposed - if you want to tune.


same query different explain plan

Sakthivel Govindasamy, May 21, 2007 - 7:25 am UTC

Hi Tom

I have the sql but gives different explain plan on the same sql session. The database is 8.1.7 and running on windows.

SQL> set autot trace exp
SQL> select ems_exptx.exptx_num,ems_exptx.status from emsmgr.ems_exptx where
2 ems_exptx.container_flg='SENT_TO_GO' and (ems_exptx.status in ('O
3 PEN', 'HOLD', 'CANCEL') and ems_exptx.exptx_type = 'EXPTX_ORDER')
4 order by ems_exptx.exptx_num
5 /

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=116 Card=2245 Bytes=
78575)

1 0 SORT (ORDER BY) (Cost=116 Card=2245 Bytes=78575)
2 1 INLIST ITERATOR
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'EMS_EXPTX' (Cost=94
Card=2245 Bytes=78575)

4 3 INDEX (RANGE SCAN) OF 'DELL_EMS_EXPTX4' (NON-UNIQUE)
(Cost=7 Card=2245)




SQL> select ems_exptx.exptx_num,ems_exptx.status from emsmgr.ems_exptx
2 where ems_exptx.container_flg='SENT_TO_GO' and (ems_exptx.status in
3 ('OPEN', 'HOLD', 'CANCEL') and ems_exptx.exptx_type = 'EXPTX_ORDER')
4 order by ems_exptx.exptx_num
5
SQL> /

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=228126 Card=14483760
Bytes=506931600)

1 0 SORT (ORDER BY) (Cost=228126 Card=14483760 Bytes=506931600
)

2 1 TABLE ACCESS (FULL) OF 'EMS_EXPTX' (Cost=84202 Card=1448
3760 Bytes=506931600)



Tom Kyte
May 21, 2007 - 11:06 am UTC

sorry, those are two very different queries.

'O
PEN'

and 'OPEN'

are very very different. I will assume that

a) there are lots of records with 'OPEN' status
b) there are few with 'O
PEN' status

hence, the different card=values and the radically different approaches for retrieving the data.


ops$tkyte%ORA10GR2> create table t as
  2  select 'OPEN' the_status, a.* from all_objects a;

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T', method_opt =>'for columns the_status size 254' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> set autotrace traceonly explain
ops$tkyte%ORA10GR2> select * from t where the_status = 'OPEN';

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 49882 |  4773K|   164   (4)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T    | 49882 |  4773K|   164   (4)| 00:00:02 |
--------------------------------------------------------------------------

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

   1 - filter("THE_STATUS"='OPEN')

ops$tkyte%ORA10GR2> select * from t where the_status = 'O
  2  PEN';

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    98 |   163   (3)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |    98 |   163   (3)| 00:00:02 |
--------------------------------------------------------------------------

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

   1 - filter("THE_STATUS"='O PEN')

ops$tkyte%ORA10GR2> set autotrace off

Explain Plan v/s v$sql_plan

Suraj Sharma, June 12, 2007 - 8:12 am UTC

Tom,

Please correct me if I am wrong.

We Use the EXPLAIN PLAN statement to determine the execution plan Oracle Database follows to execute a specified SQL statement. This statement inserts a row describing each step of the execution plan into a specified table. We use v$sql_plan view to see the execution plan Oracle Database HAS followed to execute a specified SQL statement. What is the significance of using EXPLAIN PLAN over not using v$sql_plan view? As most of of the time I saw people use EXPLAIN PLAN, even many experienced people do the same and recommend the same.
If I am correct above, then isn¿t is better to have realistic information (which plan Oracle database has followed) than going for more theoretical approach using EXPLAIN PLAN?
Tom Kyte
June 12, 2007 - 10:26 am UTC

explain plan shows what the optimizer GUESSED for cardinality. that is vital in understanding why a wrong plan is being chosen.


use reality (v$sql_plan - row source operation in tkprofs)

and compare to the guess (explain plan)

when you are trying to find out why a plan has gone awry - once we know what card=value is "bad", we can usually find a way to correct it (without rewriting query, without hinting and so on)...

and if we fix one bad card=value, we likely fix others...

Suraj Sharma, June 13, 2007 - 12:45 am UTC

Tom,

I am sorry for not able to understand your comment above. Could you please explain me the same?
Tom Kyte
June 13, 2007 - 8:05 am UTC

explain plan - that shows the estimated cardinality the optimizer GUESSED. the cardinality guessed is one of the single biggest influences on a plan. If it guesses WRONG, we get the wrong plan.

tkprof reports with row source operation steps - that shows what really happened, reality. It shows the REAL row counts (cardinalities) observed at run time.

when we have a bad plan being generated - I was the explain plan (the guess) and a tkprof (reality) so we can compare them, find where the optimizer went wrong in its guessing - and try to fix that so it doesn't.



Top Sessions-Please Help

A reader, June 29, 2007 - 3:29 pm UTC

Hi Tom,
I wish you are doing fine.
I am trying to find out the top 20 active sessions in my database (version 10.1.0.5) using the a query , but it takes over 20 minutes to complete but i can't figure out why that long.
Could you please help to show me why it tooks that long?
is it possible to reduce that time ?
Thanks in advance
the details of query and execution plan are listed below:

============== the query steps========================

alter session set events '10046 trace name context forever, level  12 ' ;
set timing on ;
set autotrace traceonly ;
select * from
(select b.sid sid,
     decode (b.username,null,e.name,b.username) user_name,
     d.spid os_id,
     b.machine machine_name,
     to_char(logon_time,'dd-mon-yy hh:mi:ss pm') logon_time,
    (sum(decode(c.name,'physical reads  ',value,0)) +
     sum(decode(c.name,'physical writes',value,0)) +
     sum(decode(c.name,'physical writes direct',value,0)) +
     sum(decode(c.name,'physical writes direct (lob)',value,0))+
     sum(decode(c.name,'physical reads  direct (lob)',value,0)) +
     sum(decode(c.name,'physical reads   direct',value,0)))
     total_physical_io,
    (sum(decode(c.name,'db block gets',value,0)) +
     sum(decode(c.name,'db block changes',value,0)) +
     sum(decode(c.name,'consistent changes',value,0)) +
     sum(decode(c.name,'consistent gets ',value,0)) ) 
     total_logical_io,
    (sum(decode(c.name,'session pga memory',value,0))+
     sum(decode(c.name,'session uga memory',value,0)) )
     total_memory_usage,
     sum(decode(c.name,'parse count (total)',value,0)) parses,
     sum(decode(c.name,'cpu used by this session',value,0)) 
     total_cpu,
     sum(decode(c.name,'parse time cpu',value,0)) parse_cpu,
     sum(decode(c.name,'recursive cpu usage',value,0)) 
       recursive_cpu,
     sum(decode(c.name,'cpu used by this session',value,0)) -
     sum(decode(c.name,'parse time cpu',value,0)) -
     sum(decode(c.name,'recursive cpu usage',value,0)) 
       other_cpu,
     sum(decode(c.name,'sorts (disk)',value,0)) disk_sorts,
     sum(decode(c.name,'sorts (memory)',value,0)) memory_sorts,
     sum(decode(c.name,'sorts (rows)',value,0)) rows_sorted,
     sum(decode(c.name,'user commits',value,0)) commits,
     sum(decode(c.name,'user rollbacks',value,0)) rollbacks,
     sum(decode(c.name,'execute count',value,0)) executions
from sys.v_$sesstat  a,
     sys.v_$session b,
     sys.v_$statname c,
     sys.v_$process d,
     sys.v_$bgprocess e
where a.statistic#=c.statistic# and
      c.NAME in ('physical reads  ',
                 'physical writes',
                 'physical writes direct',
                 'physical reads   direct',
                 'physical writes direct (lob)',
                 'physical reads   direct (lob)',
                 'db block gets',
                 'db block changes',
                 'consistent changes',
                 'consistent gets ',
                 'session pga memory',
                 'session uga memory',
                 'parse count (total)',
                 'CPU used by this session',
                 'parse time cpu',
                 'recursive cpu usage',
                 'sorts (disk)',
                 'sorts (memory)',
                 'sorts (rows)',
                 'user commits',
                 'user rollbacks',
                 'execute count'
)
group by b.sid,
         d.spid,
         decode (b.username,null,e.name,b.username),
         b.machine,
         to_char(logon_time,'dd-mon-yy hh:mi:ss pm')
order by 4 desc)
where rownum < 21;


=================== OUTPUT ================
20 inregistrari selectate.

Scurs: 00:17:04.69

Plan de executie
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=174 Card=1 Bytes=2
71)

1 0 COUNT (STOPKEY)
2 1 VIEW (Cost=174 Card=1 Bytes=271)
3 2 SORT (ORDER BY STOPKEY) (Cost=174 Card=1 Bytes=340)
4 3 SORT (GROUP BY) (Cost=174 Card=1 Bytes=340)
5 4 NESTED LOOPS (Cost=170 Card=1 Bytes=340)
6 5 MERGE JOIN (CARTESIAN) (Cost=145 Card=1 Bytes=27
5)

7 6 NESTED LOOPS (Cost=121 Card=1 Bytes=215)
8 7 MERGE JOIN (CARTESIAN) (Cost=97 Card=1 Bytes
=202)

9 8 MERGE JOIN (CARTESIAN) (Cost=73 Card=1 Byt
es=77)

10 9 NESTED LOOPS (Cost=48 Card=1 Bytes=43)
11 10 FIXED TABLE (FULL) OF 'X$KSBDP' (TABLE
(FIXED)) (Cost=24 Card=1 Bytes=30)

12 10 FIXED TABLE (FIXED INDEX) OF 'X$KSBDD
(ind:2)' (TABLE (FIXED)) (Cost=24 Card=1 Bytes=13)

13 9 BUFFER (SORT) (Cost=48 Card=1 Bytes=34)
14 13 FIXED TABLE (FULL) OF 'X$KSUPR' (TABLE
(FIXED)) (Cost=24 Card=1 Bytes=34)

15 8 BUFFER (SORT) (Cost=73 Card=1 Bytes=125)
16 15 FIXED TABLE (FULL) OF 'X$KSUSE' (TABLE (
FIXED)) (Cost=24 Card=1 Bytes=125)

17 7 FIXED TABLE (FIXED INDEX) OF 'X$KSLED (ind:2
)' (TABLE (FIXED)) (Cost=24 Card=1 Bytes=13)

18 6 BUFFER (SORT) (Cost=121 Card=1 Bytes=60)
19 18 FIXED TABLE (FULL) OF 'X$KSUSD' (TABLE (FIXE
D)) (Cost=24 Card=1 Bytes=60)

20 5 FIXED TABLE (FIXED INDEX) OF 'X$KSUSESTA (ind:2)
' (TABLE (FIXED)) (Cost=24 Card=1 Bytes=65)

21 20 FIXED TABLE (FULL) OF 'X$KSUSGIF' (TABLE (FIXE
D)) (Cost=2 Card=1 Bytes=13)





Statistici
----------------------------------------------------------
36 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
2291 bytes sent via SQL*Net to client
1715 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
20 rows processed
=======================Trace File=======================
order by 4 desc)
where rownum < 21
END OF STMT
PARSE #2:c=200000,e=190902,p=0,cr=10,cu=0,mis=1,r=0,dep=0,og=1,tim=1155412781327351
BINDS #2:
EXEC #2:c=0,e=338,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1155412781328315
WAIT #2: nam='SQL*Net message to client' ela= 4 p1=1650815232 p2=1 p3=0
*** 2007-06-29 19:01:52.633
FETCH #2:c=1024420000,e=1000539923,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,tim=1155413781868441
WAIT #2: nam='SQL*Net message from client' ela= 2848 p1=1650815232 p2=1 p3=0
WAIT #2: nam='SQL*Net message to client' ela= 5 p1=1650815232 p2=1 p3=0
FETCH #2:c=0,e=274,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=1,tim=1155413781873522
WAIT #2: nam='SQL*Net message from client' ela= 695 p1=1650815232 p2=1 p3=0
WAIT #2: nam='SQL*Net message to client' ela= 2 p1=1650815232 p2=1 p3=0
FETCH #2:c=0,e=145,p=0,cr=0,cu=0,mis=0,r=4,dep=0,og=1,tim=1155413781874606
WAIT #2: nam='SQL*Net message from client' ela= 2099 p1=1650815232 p2=1 p3=0
*** SESSION ID:(424.416) 2007-06-29 19:01:52.653
STAT #2 id=1 cnt=20 pid=0 pos=1 obj=0 op='COUNT STOPKEY (cr=0 pr=0 pw=0 time=1000540080 us)'
STAT #2 id=2 cnt=20 pid=1 pos=1 obj=0 op='VIEW (cr=0 pr=0 pw=0 time=1000540057 us)'
STAT #2 id=3 cnt=20 pid=2 pos=1 obj=0 op='SORT ORDER BY STOPKEY (cr=0 pr=0 pw=0 time=1000540038 us)'
STAT #2 id=4 cnt=27829 pid=3 pos=1 obj=0 op='SORT GROUP BY (cr=0 pr=0 pw=0 time=1000155938 us)'
STAT #2 id=5 cnt=9616099 pid=4 pos=1 obj=0 op='NESTED LOOPS (cr=0 pr=0 pw=0 time=644283108 us)'
STAT #2 id=6 cnt=567018 pid=5 pos=1 obj=0 op='MERGE JOIN CARTESIAN (cr=0 pr=0 pw=0 time=5106649 us)'
STAT #2 id=7 cnt=31501 pid=6 pos=1 obj=0 op='NESTED LOOPS (cr=0 pr=0 pw=0 time=695690 us)'
STAT #2 id=8 cnt=31501 pid=7 pos=1 obj=0 op='MERGE JOIN CARTESIAN (cr=0 pr=0 pw=0 time=191674 us)'
STAT #2 id=9 cnt=1853 pid=8 pos=1 obj=0 op='MERGE JOIN CARTESIAN (cr=0 pr=0 pw=0 time=10185 us)'
STAT #2 id=10 cnt=109 pid=9 pos=1 obj=0 op='NESTED LOOPS (cr=0 pr=0 pw=0 time=4813 us)'
STAT #2 id=11 cnt=109 pid=10 pos=1 obj=85 op='FIXED TABLE FULL X$KSBDP (cr=0 pr=0 pw=0 time=2309 us)'
STAT #2 id=12 cnt=109 pid=10 pos=2 obj=86 op='FIXED TABLE FIXED INDEX X$KSBDD (ind:2) (cr=0 pr=0 pw=0 time=1410 us)'
STAT #2 id=13 cnt=1853 pid=9 pos=2 obj=0 op='BUFFER SORT (cr=0 pr=0 pw=0 time=4898 us)'
STAT #2 id=14 cnt=17 pid=13 pos=1 obj=25 op='FIXED TABLE FULL X$KSUPR (cr=0 pr=0 pw=0 time=116 us)'
STAT #2 id=15 cnt=31501 pid=8 pos=2 obj=0 op='BUFFER SORT (cr=0 pr=0 pw=0 time=100039 us)'
STAT #2 id=16 cnt=17 pid=15 pos=1 obj=23 op='FIXED TABLE FULL X$KSUSE (cr=0 pr=0 pw=0 time=1849 us)'
STAT #2 id=17 cnt=31501 pid=7 pos=2 obj=10 op='FIXED TABLE FIXED INDEX X$KSLED (ind:2) (cr=0 pr=0 pw=0 time=321389 us)'
STAT #2 id=18 cnt=567018 pid=6 pos=2 obj=0 op='BUFFER SORT (cr=0 pr=0 pw=0 time=1761658 us)'
STAT #2 id=19 cnt=18 pid=18 pos=1 obj=28 op='FIXED TABLE FULL X$KSUSD (cr=0 pr=0 pw=0 time=150 us)'
STAT #2 id=20 cnt=9616099 pid=5 pos=2 obj=32 op='FIXED TABLE FIXED INDEX X$KSUSESTA (ind:2) (cr=0 pr=0 pw=0 time=694947499 us)'

Tom Kyte
July 03, 2007 - 8:50 am UTC

.... I am trying to find out the top 20 active sessions in my database (version 10.1.0.5) using the a query , but it takes over 20 minutes to complete but i can't figure out why that long. ...

well, at least you know the TOP "most active" session then don't you :) YOURS!!!


from sys.v_$sesstat  a,
     sys.v_$session b,
     sys.v_$statname c,
     sys.v_$process d,
     sys.v_$bgprocess e
where a.statistic#=c.statistic# and
      c.NAME in (... list chopped out...)


umm, that would be what we call a massive cartesian join.


you do not seem to be joining any of the tables to each other, so if there say 350 rows per sid in sesstat and say 200 sessions in session and 200 processes in process and say 150 rows in v$bgprocess - that in itself would be

350*200*200*150
---------------
     2100000000
 


then multiply that by the rows in statname again and - well, you get a ton of rows don't you.


and why would you order by machine name ???!?!?

this query has not be thought out very well.

explain plan

A reader, July 19, 2007 - 1:26 pm UTC

What does CPU (%COST) mean? Does it always mean the lower number the better performance?

----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 138 | 8 (0)| 00:00:01 | | |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID | U11_FT | 1 | 75 | 4 (0)| 00:00:01 | ROWID | ROWID |
| 2 | NESTED LOOPS | | 1 | 138 | 8 (0)| 00:00:01 | | |
| 3 | PARTITION RANGE SINGLE | | 1 | 63 | 4 (0)| 00:00:01 | 53 | 53 |
|* 4 | TABLE ACCESS BY LOCAL INDEX ROWID| U11_FE | 1 | 63 | 4 (0)| 00:00:01 | 53 | 53 |
|* 5 | INDEX RANGE SCAN | IDX_U11_FE_05 | 1 | | 3 (0)| 00:00:01 | 53 | 53 |
|* 6 | INDEX RANGE SCAN | PK_U11_FT_TMP | 1 | | 3 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------------

Thanks!
Tom Kyte
July 19, 2007 - 2:44 pm UTC

in a perfect world, it would.

the optimizer is a mathematical model. It takes statistics on the table, information about your system (IO times and such), the query itself and starts assigning numbers to things.

If the optimizer gets everything "perfect" - all of the inputs were perfect, all of the assumptions were perfect - then the output would be a perfect.

But the optimizer has to guess sometimes, the rows estimated are not exactly precise in every case.

It is best to consider the cost an internal number that Oracle uses to find what it believes to be the best possible plan. It is great for diagnosing issues with the optimizer but for you and I - day to day - it is not so useful.

oem explain plan

A reader, August 10, 2007 - 9:52 am UTC

I use oem (10g) to monitor top activities and to find which sql consumes more cpu. But the thing I noticed is the explain plan is different between oem and regular way you get explain plan (set autotrace traceonly explain plan). Do you know why is that?

Thanks!
Tom Kyte
August 14, 2007 - 2:58 pm UTC

OEM shows reality - gets it from v$sql_plan.

autotrace uses explain plan...

http://asktom.oracle.com/Misc/when-explanation-doesn-sound-quite.html

Sam Mousleh, June 18, 2008 - 9:11 am UTC

I do have two databases production and copy of production on different machine.
So, I ran the same statement on both database and I got different explain plan, on production it's doing full table scan on both tables "SAOPTDEM and GEPARAM_PROD".
and on the other database which is a copy of production with the same struccture and index and so on...(exact a copy o production) it's using the index on the table SAOPTDEM. and which run so mush faster:

I don't understand, would you please take a quick look at the following:

This is on production
SELECT /*+ INDEX(SAOPTDEM SAOPTDEM_I2)*/ S.NDEMNUM
, S.NCNTNUM
, S.CCNTSCS
, S.CDEMPROD
, DECODE (S.CDEMTYPACT,'S', 'A', 'R', 'S')
, G.GEPR_ECHEANCE
, S.CDEMMED
, S.CDEMEMET
FROM SAOPTDEM S
, GEPARAM_PROD G
WHERE ROWNUM = 1
AND S.CDEMSTAT = 'O'
AND S.CDEMTYPACT IN ('S', 'R')
AND G.GEPR_MODE = 'TP'
AND S.CDEMPROD = G.GEPR_CODE_PROD;

no rows selected

Execution Plan
----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4251 Card=1 Bytes=46)


 

    1    0   COUNT (STOPKEY)

 

    2    1     HASH JOIN (Cost=4251 Card=55319 Bytes=2544674)

 

    3    2       TABLE ACCESS (FULL) OF 'GEPARAM_PROD' (Cost=7 Card=164Bytes=2296)

 

    4    2       TABLE ACCESS (FULL) OF 'SAOPTDEM' (Cost=4243 Card=102518 Bytes=3280576)

Statistics
----------------------------------------------------------
         30  recursive calls
          0  db block gets
      44146  consistent gets
      18657  physical reads
          0  redo size
        753  bytes sent via SQL*Net to client
        457  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed



This is on the copy of production with good result:
SELECT /*+ INDEX(SAOPTDEM SAOPTDEM_I2)*/ S.NDEMNUM
, S.NCNTNUM
, S.CCNTSCS
, S.CDEMPROD
, DECODE (S.CDEMTYPACT,'S', 'A', 'R', 'S')
, G.GEPR_ECHEANCE
, S.CDEMMED
, S.CDEMEMET
FROM SAOPTDEM S
, GEPARAM_PROD G
WHERE ROWNUM = 1
AND S.CDEMSTAT = 'O'
AND S.CDEMTYPACT IN ('S', 'R')
AND G.GEPR_MODE = 'TP'
AND S.CDEMPROD = G.GEPR_CODE_PROD;


NDEMNUM NCNTNU CC CDEM D GE CD CDEME
---------- ------ -- ---- - -- -- -----
8698648 PFL690 01 S72R S JJ AU GP


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=95 Card=1 Bytes=47)

   1    0   COUNT (STOPKEY)

   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'SAOPTDEM' (Cost=2 Card=1 Bytes=36)
 
   3    2       NESTED LOOPS (Cost=95 Card=46 Bytes=2162)

   4    3         TABLE ACCESS (FULL) OF 'GEPARAM_PROD' (Cost=3 Card=46 Bytes=506)
 
   5    3         INDEX (RANGE SCAN) OF 'SAOPTDEM_I2' (NON-UNIQUE) (Cost=1 Card=10115)
 
 
Statistics
----------------------------------------------------------
          7  recursive calls
          0  db block gets
       5048  consistent gets
       5056  physical reads
          0  redo size
        997  bytes sent via SQL*Net to client
        655  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed



Tom Kyte
June 18, 2008 - 2:44 pm UTC

I seriously doubt the index exists in production.

but I know the statistics are radically different - look at the estimated cardinality values there.

Sam Mousleh, June 19, 2008 - 4:58 am UTC

Sorry, but the index exist on production this is the first thing I checked. We imported the production database into the new machine with a dump file.
and we did not change any thing on the new database still as is.

Anyway do you think rebuilding the index on production or may be droping it then create a new index will change anything?

Thank you...


Tom Kyte
June 19, 2008 - 10:13 am UTC

prove it, total cut and paste from production.

select index name, status and other intersting columns from user_indexes
select column-name, position and other interesting columns from user_ind_cols

explain the query using explain plan, dbms_xplan, autotrace -whatever you use.

Sam Mousleh, June 20, 2008 - 7:38 am UTC

This is the result from production db...

 1  SELECT owner, index_name, index_type, table_name, table_type, status, initial_extent, next_extent, max_extents

  2  FROM dba_indexes

  3  WHERE index_name LIKE 'SAOPTDEM_%'

  4* ORDER BY index_name

SQL> /

 

OWNER        INDEX_NAME           INDEX_TYPE TABLE_NAME           TABLE_TYPE  STATUS   INITIAL_EXTENT NEXT_EXTENT MAX_EXTENTS

------------ -------------------- ---------- -------------------- ----------- -------- -------------- ----------- -----------

OPS$SABXFER  SAOPTDEM_I1          NORMAL     SAOPTDEM             TABLE       VALID           3145728     3145728  2147483645

OPS$SABXFER  SAOPTDEM_I2          NORMAL     SAOPTDEM             TABLE       VALID            131072     3145728  2147483645

OPS$SABXFER  SAOPTDEM_PK          NORMAL     SAOPTDEM             TABLE       VALID           3145728     3145728  2147483645

 

SQL> SELECT index_name, index_type, table_name, table_type, status, initial_extent, next_extent, max_extents

FROM user_indexes

WHERE index_name like 'SAOPTDEM_%'  2    3

  4  /

 

no rows selected


SQL> SET AUTOTRACE ON

 

SELECT /*+ INDEX(SAOPTDEM SAOPTDEM_I2)*/ s.NDEMNUM, s.NCNTNUM, s.CCNTSCS, s.CDEMPROD,

      DECODE (s.CDEMTYPACT,'S', 'A', 'R', 'S'), G.GEPR_ECHEANCE,   s.CDEMMED,  s.CDEMEMET

  FROM SAOPTDEM S, GEPARAM_PROD G

 WHERE ROWNUM = 1

      AND S.CDEMSTAT = 'O'

      AND S.CDEMTYPACT IN ('S', 'R')

      AND G.GEPR_MODE = 'TP'

      AND s.CDEMPROD = G.GEPR_CODE_PROD;

 

NDEMNUM NCNTNU CC CDEM D GE CD CDEME

---------- ------ -- ---- - -- -- -----

   9262598 H93988 04 P588 A NA SM GP

 

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4247 Card=1 Bytes=43   )

   1    0   COUNT (STOPKEY)

   2    1     HASH JOIN (Cost=4247 Card=20465 Bytes=879995)

   3    2       TABLE ACCESS (FULL) OF 'GEPARAM_PROD' (Cost=3 Card=57  Bytes=627)

   4    2       TABLE ACCESS (FULL) OF 'SAOPTDEM' (Cost=4243 Card=1035 95 Bytes=3315040)

 

Statistics

----------------------------------------------------------

         91  recursive calls

          0  db block gets

       3689  consistent gets

        560  physical reads

          0  redo size

        997  bytes sent via SQL*Net to client

        652  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed



You asked me to find the indexes from user_indexes, but there is no data found. however from dba_indexes you can see the three indexes on the table (SAOPTDEM). I am not sure what does that mean?
Also I do have same results from the prod copy db, nothing in user_indexes. but 3 rows on the dba_indexes...
From the explain plan you can see that on Prod the indexes are not used, however on the copy of prod they are used and geting good cost.
This is what most important from the explain plan the "COST"
right?

Thank you
Sam



Tom Kyte
June 20, 2008 - 10:34 am UTC

... You asked me to find the indexes from user_indexes, but there is no data found. however from dba_indexes you can see the three indexes on the table (SAOPTDEM). I am not sure what does that mean? ....

it could very well mean there is another table SAOPTDEM out there owned by someone else that has indexes on it.


SELECT owner, index_name, table_owner, table_name, status
FROM dba_indexes
WHERE index_name LIKE 'SAOPTDEM_%'
ORDER BY index_name


run that instead.

and instead of:


FROM SAOPTDEM S, GEPARAM_PROD G


use

FROM "put the owner here".SAOPTDEM S, "put the owner here".GEPARAM_PROD G

so it is entirely unambiguous what segments we are dealing with.

Index used then also index skip scan

sunil shah, June 20, 2008 - 10:56 pm UTC

I have a problem while running the query
Which uses 3 table wh5110 wh1500 and gn1000
I have indexes on them
But the index is used and goes skip scan of any other index
So I would like to have a better solution than thes
I have almost 35,00,000 records in wh1500 and 2000000 in wh5110
Please convert my explain plan index scan to unique scan or range scan with low cardinality
And cost


SELECT 'WH',3,SYSDATE,A.NO_CMP,a.no_loc,a.vc_grinspno,A.NO_LN,'WH5110','WH1500','Quantity Mismatch BETWEEN WH5110 AND WH1500'
FROM nealv.WH5110 a ,nealv.WH1500 b ,nealv.GN1000 c
WHERE a.vc_grinspno = b.vc_objno
AND a.no_wh = b.no_wh
AND a.vc_partno = b.vc_partno
AND a.no_ln = b.no_ln
AND a.no_accqty != b.no_qty
AND a.vc_uom = c.vc_uom
AND a.no_loc = b.no_loc
AND a.no_cmp = b.no_cmp
AND a.vc_partno = c.vc_partno
AND b.vc_partno = c.vc_partno
AND b.dt_bk >= '01-apr-2007'
AND b.ch_canccd = 'N'
AND NOT EXISTS (SELECT /*+ ORDERED INDEX (wh1500 pk_wh1500) */ e.no_refbk
FROM nealv.WH1500 e
WHERE e.no_refbk = b.no_bk
AND e.no_loc = b.no_loc
AND e.no_cmp = b.no_cmp
AND e.vc_objno = b.vc_objno
AND e.no_ln = b.no_ln
AND e.no_wh = b.no_wh
AND e.vc_partno = b.vc_partno
and e.dt_bk = b.dt_bk )
ORDER BY a.no_cmp,a.no_loc,a.vc_grinspno;

Tom Kyte
June 20, 2008 - 11:35 pm UTC

hah

Sam Mousleh, June 23, 2008 - 8:01 am UTC

Tom, I got the same results. and as you can see the same owner for both indexes and table.

OWNER        INDEX_NAME           TABLE_OWNER                    TABLE_NAME                     STATUS

------------ -------------------- ------------------------------ ------------------------------ --------

OPS$SABXFER  SAOPTDEM_I1          OPS$SABXFER                    SAOPTDEM                       VALID

OPS$SABXFER  SAOPTDEM_I2          OPS$SABXFER                    SAOPTDEM                       VALID

OPS$SABXFER  SAOPTDEM_PK          OPS$SABXFER                    SAOPTDEM                       VALID



Set autotrace on

 

SELECT /*+ INDEX(SAOPTDEM SAOPTDEM_I2)*/ s.NDEMNUM, s.NCNTNUM, s.CCNTSCS, s.CDEMPROD,
            DECODE (s.CDEMTYPACT,'S', 'A', 'R', 'S'), G.GEPR_ECHEANCE,   s.CDEMMED,  s.CDEMEMET
  FROM OPS$SABXFER.SAOPTDEM S, OPS$SABXFER.GEPARAM_PROD G
 WHERE ROWNUM = 1
      AND S.CDEMSTAT = 'O'
      AND S.CDEMTYPACT IN ('S', 'R')
      AND G.GEPR_MODE = 'TP'
      AND s.CDEMPROD = G.GEPR_CODE_PROD;


set autotrace off

no rows selected

 

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4247 Card=1 Bytes=43 )

   1    0   COUNT (STOPKEY)

   2    1     HASH JOIN (Cost=4247 Card=20465 Bytes=879995)

   3    2       TABLE ACCESS (FULL) OF 'GEPARAM_PROD' (Cost=3 Card=57 Bytes=627)

   4    2       TABLE ACCESS (FULL) OF 'SAOPTDEM' (Cost=4243 Card=103595 Bytes=3315040)

 

Statistics

----------------------------------------------------------

         30  recursive calls

          0  db block gets

      44154  consistent gets

      22684  physical reads

          0  redo size

        753  bytes sent via SQL*Net to client

        456  bytes received via SQL*Net from client

          1  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          0  rows processed


Sam
Tom Kyte
June 23, 2008 - 8:18 am UTC

your hint is entirely wrong. hints use correlation names. you have S and G


and it would be interesting to get a 10053 trace from both systems and YOU (stress YOU) compare them to see what is fundamentally different.

Sam Mousleh, June 23, 2008 - 10:43 am UTC

Thank you Tom for your respond...
I have changed the hint as you said and replaced the table name with alias "S". It is using the index now on the table SAOPTDEM but it cost more as total nested loops:

NESTED LOOPS (Cost=86016 Card=20465 Bytes=879995)

We have 3 indexes on that table:
SAOPTDEM_I1 (non unique) column: ncntnum, ccntscs
SAOPTDEM_I2 (non unique) column: cdemprod
SAOPTDEM_PK (unique) column: ndemnum
Can we force more then one index at the same time.

SELECT /*+ INDEX(S SAOPTDEM_I2)*/ s.NDEMNUM, s.NCNTNUM, s.CCNTSCS, s.CDEMPROD,
      DECODE (s.CDEMTYPACT,'S', 'A', 'R', 'S'), G.GEPR_ECHEANCE,   s.CDEMMED,  s.CDEMEMET
  FROM OPS$SABXFER.SAOPTDEM S

           , OPS$SABXFER.GEPARAM_PROD G
 WHERE ROWNUM = 1
      AND S.CDEMSTAT = 'O'
      AND S.CDEMTYPACT IN ('S', 'R')
      AND G.GEPR_MODE = 'TP'
      AND s.CDEMPROD = G.GEPR_CODE_PROD;

 

no rows selected

 

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=86016 Card=1 Bytes=43)

   1    0   COUNT (STOPKEY)

   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'SAOPTDEM' (Cost=1509 Card=362 Bytes=11584)

   3    2       NESTED LOOPS (Cost=86016 Card=20465 Bytes=879995)

   4    3         TABLE ACCESS (FULL) OF 'GEPARAM_PROD' (Cost=3 Card=57 Bytes=627)

   5    3         INDEX (RANGE SCAN) OF 'SAOPTDEM_I2' (NON-UNIQUE) (Cost=10 Card=4347)

Statistics

----------------------------------------------------------

         51  recursive calls

          0  db block gets

      66967  consistent gets

       7151  physical reads

          0  redo size

        753  bytes sent via SQL*Net to client

        457  bytes received via SQL*Net from client

          1  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          0  rows processed


Tom Kyte
June 23, 2008 - 1:06 pm UTC

so?

you got what you asked for.

Sam Mousleh, June 23, 2008 - 1:42 pm UTC

But Tom, the overall cost is higher then before. So, I have added a new index on the table on a column that exist in the where clause "CDEMSTAT= 'O'". I ran a new explain plan on the copy of prod database and got very good results.
The overall cost was 95 and now it when down to 6.
So, I will add the new index on production and run new explain plan and we will find out the difference.

This is the result on prod COPY:
SELECT /*+ INDEX(S SAOPTDEM_I3)*/ s.NDEMNUM, s.NCNTNUM, s.CCNTSCS, s.CDEMPROD,
      DECODE (s.CDEMTYPACT,'S', 'A', 'R', 'S'), G.GEPR_ECHEANCE,   s.CDEMMED,  s.CDEMEMET
  FROM OPS$SABXFER.SAOPTDEM S, OPS$SABXFER.GEPARAM_PROD G
 WHERE ROWNUM = 1
      AND S.CDEMSTAT = 'O'
      AND S.CDEMTYPACT IN ('S', 'R')
      AND G.GEPR_MODE = 'TP'
      AND s.CDEMPROD = G.GEPR_CODE_PROD;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=1 Bytes=47)
   1    0   COUNT (STOPKEY)
   2    1     HASH JOIN (Cost=6 Card=46 Bytes=2162)
   3    2       TABLE ACCESS (FULL) OF 'GEPARAM_PROD' (Cost=3 Card=46 Bytes=506)
   4    2       TABLE ACCESS (BY INDEX ROWID) OF 'SAOPTDEM' (Cost=2 Card=136 Bytes=4896)
   5    4         INDEX (RANGE SCAN) OF 'SAOPTDEM_I3' (NON-UNIQUE) (Cost=1 Card=5422)

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


And the number of consistent gets decreased enormously compare to 75598.

Thank you again...



Tom Kyte
June 23, 2008 - 1:58 pm UTC

well, good?

Sam - you wanted that index to be used.

You were having a hard time getting it used.

It wasn't being used because it would be less efficient to have used it.

I did what you seem to want, to get the index to be used.

That you (using YOUR knowledge of YOUR data) indexed it differently and it works better is "great", but that wasn't really what you were asking here.

remember, this is whence we started:

....
I do have two databases production and copy of production on different machine.
So, I ran the same statement on both database and I got different explain plan, on production it's doing full table scan on both tables "SAOPTDEM and GEPARAM_PROD".
and on the other database which is a copy of production with the same struccture and index and so on...(exact a copy o production) it's using the index on the table SAOPTDEM. and which run so mush faster:
..............


I don't know your tables
I don't know their data
We weren't looking for an alternate scheme
you were convinced it should use that index
we were trying to figure out "why it wasn't" (and now we know...)


Sam Mousleh, June 24, 2008 - 6:22 am UTC

Well, Tom I still don't understand how the optimizer get this, NESTED LOOPS (Cost=86016 Card=20465 Bytes=879995) Since the cost for both table was so low (cost=3 and cost:10) after successfully using the index 'SAOPTDEM_I2'

Could you explain briefly to me if you could, this should help me understand how to read the explain plan for other statements that I am working on:

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=86016 Card=1 Bytes=43)
   1    0   COUNT (STOPKEY)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'SAOPTDEM' (Cost=1509 Card=362 Bytes=11584)
   3    2       NESTED LOOPS (Cost=86016 Card=20465 Bytes=879995)
   4    3         TABLE ACCESS (FULL) OF 'GEPARAM_PROD' (Cost=3 Card=57 Bytes=627)
   5    3         INDEX (RANGE SCAN) OF 'SAOPTDEM_I2' (NON-UNIQUE) (Cost=10 Card=4347)


What I know that I needed to have an index on different column that exist in the where clause since the existing index(es) were unefficient; correct me if I am wrong but having index(es) on the table without having the column(s) that reference the index in the where clause will not use the index.

Thank you again for your time, your comments is very helpful and useful.

Sam


Tom Kyte
June 24, 2008 - 6:54 am UTC

see the estimated cardinality. Nested loops = a loop, iteration.

for x in (select * from geparam_prod)
loop
   index range scan about 4300 things
end loop


Sam Mousleh, June 24, 2008 - 9:09 am UTC

Ok thank you Tom,

One more think, in general should we analyze all tables more often, I mean every week or day...
Will that help the performance? or there is no need to do so.

Sam
Tom Kyte
June 24, 2008 - 10:42 am UTC

it depends.

do the tables change that rapidly? In most/many cases - using gather stale with dbms_stats (only get segments that have changed 'significantly') works well - there are exceptions of course, but in many cases...

Sam Mousleh, June 25, 2008 - 12:53 pm UTC

When the optimizer choose to do a full table scan instead of using the index. Is it because the index cost more than a table full scan.
In that case do we need to rebuild the index?

This to me is very strange as results:

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=462 Card=1 Bytes=35)
   1    0   COUNT (STOPKEY)
   2    1     HASH JOIN (Cost=462 Card=12840 Bytes=449400)
   3    2       TABLE ACCESS (FULL) OF 'GEPARAM_PROD' (Cost=3 Card=46
          Bytes=506)

   4    2       TABLE ACCESS (FULL) OF 'SAOPTDEM' (Cost=408 Card=10216
          1 Bytes=2451864)



Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=59711 Card=1 Bytes=35)
   1    0   COUNT (STOPKEY)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'SAOPTDEM' (Cost=1298 Card=281 Bytes=6744)
   3    2       NESTED LOOPS (Cost=59711 Card=12840 Bytes=449400)
   4    3         TABLE ACCESS (FULL) OF 'GEPARAM_PROD' (Cost=3 Card=46 Bytes=506)
   5    3         INDEX (RANGE SCAN) OF 'SAOPTDEM_I2' (NON-UNIQUE) (Cost=12 Card=3368)


Plus, if we look at the cardinality it is way more in the full table scan... "Card=102161" compare to "Card=3368" using index. But the cost is mush less using full table scan then using the index.
The table SAOPTDEM have about 1,200,000 records.

Sam
Tom Kyte
June 25, 2008 - 3:28 pm UTC

indexes are not all goodness
full scans are not all evil

in this case, given your data, using that index was more costly than full scanning


that in no way shape or form implies "index needs to be rebuilt", just that the index you HAD was not efficient for accessing the data you WANTED. You created a new index, on entirely different columns in different order - that index made sense and we used it.

Sam Mousleh, June 25, 2008 - 1:25 pm UTC

Continue...

analyze index SAOPTDEM_I2 validate structure;

Index analyzed.

SQL> SELECT name
, lf_rows 2
3 , del_lf_rows
, (del_lf_rows * 100) / lf_rows
FROM index_stats 4 5 ;

NAME                     LF_ROWS DEL_LF_ROWS (DEL_LF_ROWS*100)/LF_ROWS
------------------------ ------- ----------- -------------------------
SAOPTDEM_I2              1225934           0                         0


There is no delete or update on the table...

Thank you for the help...
Sam

yoav ben moha, April 30, 2009 - 8:27 am UTC

Hi Tom,

Could you please explain why the operator UPDATE shows the value 0 and not the value 14 under the column (A-Rows) ?

sql> set serveroutput off
sql> update /*+ gather_plan_statistics */ emp e1
set sal = (select avg(sal) from emp e2 where e2.deptno=e1.deptno),
comm = (select avg(comm) from emp e3);

---------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
---------------------------------------------------------------
| 1 | UPDATE | EMP | 1 | | 0 |
| 2 | TABLE ACCESS FULL | EMP | 1 | 14 | 14 |
| 3 | SORT AGGREGATE | | 3 | 1 | 3 |
|* 4 | TABLE ACCESS FULL| EMP | 3 | 1 | 14 |
| 5 | SORT AGGREGATE | | 1 | 1 | 1 |
| 6 | TABLE ACCESS FULL| EMP | 1 | 14 | 14 |
---------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("E2"."DEPTNO"=:B1)

Thank You


Tom Kyte
April 30, 2009 - 11:02 am UTC

it should probably show null as the E-Rows does. The update is just sort of a 'tag' here - the rows on line id=2 were the target of this 'tag', this operation.

update and delete - tag exists, shows 0, should show null.

insert - tag isn't even there in 10g, is there in 11g - with zero.

merge - tag is there but....number is wrong, it is not zero nor is it null, it should be null - but it is "2"

In any case, they should be ignored, the answer is there - on the final 'selection' component of the plan.


EXPLAIN PLAN with PARALLEL hint.

Rajeshwaran, Jeyabal, January 18, 2010 - 11:00 am UTC

scott@IRADSDB> select /*+ parallel(e,4) parallel(d,4) */ dname,ename,sal
  2  from emp e,dept d
  3  where e.deptno = d.deptno;

Execution Plan
----------------------------------------------------------
Plan hash value: 1036174697

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |    14 |   364 |     2   (0)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR                |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)          | :TQ10000 |    14 |   364 |     2   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    NESTED LOOPS                |          |    14 |   364 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|   4 |     PX BLOCK ITERATOR          |          |       |       |            |          |  Q1,00 | PCWC |            |
|   5 |      TABLE ACCESS FULL         | EMP      |    14 |   182 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|   6 |     TABLE ACCESS BY INDEX ROWID| DEPT     |     1 |    13 |     1   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|*  7 |      INDEX UNIQUE SCAN         | PK_DEPT  |     1 |       |     0   (0)| 00:00:01 |  Q1,00 | PCWP |            |
------------------------------------------------------------------------------------------------------------------------

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

   7 - access("E"."DEPTNO"="D"."DEPTNO")


Tom,
From your book. I learnt from you (and your book) how to read an Execution plan.
Book : Effective Oracle by Design
Chapter : 2 - Your Performance Toolkit
Page no : 10 (How to Read a Query Plan)

When working with PARALLEL Queries i have some problems with reading execution plans.
1) What does TQ, IN-OUT & PQ Distrib in Execution Plan denotes?
2) What does this means ( PX COORDINATOR & PX BLOCK ITERATOR & PX SEND QC (RANDOM) ) in Execution Plan ?
3) what does P->S, PCWP & PCWC Convey in Execution Plan ?

I referred Oracle documentation http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/ex_plan.htm#i26005
but i couldnot understand clearly. can you please explain me with a clear example?

Reading parallel Execution Plan.

Rajeshwaran, Jeyabal, January 26, 2010 - 7:12 am UTC

scott@IRADSDB> show parameter parallel;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback         string      LOW
parallel_adaptive_multi_user         boolean     TRUE
parallel_automatic_tuning            boolean     FALSE
parallel_execution_message_size      integer     2148
parallel_instance_group              string
parallel_max_servers                 integer     80
parallel_min_percent                 integer     0
parallel_min_servers                 integer     0
parallel_server                      boolean     FALSE
parallel_server_instances            integer     1
parallel_threads_per_cpu             integer     2
recovery_parallelism                 integer     0

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |    14 |   364 |     2   (0)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR                |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)          | :TQ10000 |    14 |   364 |     2   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    NESTED LOOPS                |          |    14 |   364 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|   4 |     PX BLOCK ITERATOR          |          |       |       |            |          |  Q1,00 | PCWC |            |
|   5 |      TABLE ACCESS FULL         | EMP      |    14 |   182 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|   6 |     TABLE ACCESS BY INDEX ROWID| DEPT     |     1 |    13 |     1   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|*  7 |      INDEX UNIQUE SCAN         | PK_DEPT  |     1 |       |     0   (0)| 00:00:01 |  Q1,00 | PCWP |            |
------------------------------------------------------------------------------------------------------------------------


Tom,

1) Can you please help me how to read this Parallel Execution plan?
(a) looking at the step 5 in plan TABLE ACCESS FULL is done by Paralle combined with Parent (PCWP) is that parent for this operation is Step 3 in plan?
(b) Looking at TQ column in plan that only one Slave process (Q1,00) is allocated for this Execution?

Please correct me if i am wrong.

Thanks,
Rajesh.

Getting Just the Rows I Want

Karthikeyan Baskaran, February 11, 2010 - 11:42 am UTC

Hi tom,

I have seen the “Getting Just the Rows I Want” article from the below link.

https://asktom.oracle.com/Misc/oramag/getting-just-the-rows-i-want.html

Really this very helpful. Thanks a lot tom!

I have tried doing the same in my location box. But Query1 and Query2 both are going for the full table scan and it is reading 29118 records.

You have given below results and it is going for the index.

< src : https://asktom.oracle.com/Misc/oramag/getting-just-the-rows-i-want.html >

ops$tkyte@ORA8I.WORLD> select empno, ename

2 from ( select empno, ename from emp
order by empno )
3 where rownum < 500;

499 rows selected.
SQL_TRACE/TKPROF reports
Rows Row Source Operation
---- --------------------

499 COUNT STOPKEY
499 VIEW
499 TABLE ACCESS BY INDEX ROWID EMP
499 INDEX FULL SCAN (object id 27418)

</src>


Please guide me and I have tried below script.


SQL> drop table emp;

Table dropped.

SQL> create table emp as
2 select object_id empno, object_name
3 ename from all_objects;

Table created.

SQL> select count(*) from emp;

COUNT(*)
----------
29118


SQL> create index emp_idx
2 on emp(empno);

Index created.

SQL> analyze table emp compute statistics;

Table analyzed.

SQL> analyze index emp_idx compute statistics;

Index analyzed.

SQL> set autotrace traceonly

SQL> select empno, ename
2 from ( select empno, ename from emp order by empno )
3 where rownum < 500;

499 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=194 Card=499 Bytes=8
73540)

1 0 COUNT (STOPKEY)
2 1 VIEW (Cost=194 Card=29118 Bytes=873540)
3 2 SORT (ORDER BY STOPKEY) (Cost=194 Card=29118 Bytes=786
186)

4 3 TABLE ACCESS (FULL) OF 'EMP' (Cost=15 Card=29118 Byt
es=786186)





Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
139 consistent gets
0 physical reads
0 redo size
12887 bytes sent via SQL*Net to client
866 bytes received via SQL*Net from client
35 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
499 rows processed

SQL> select *
2 from ( select ename, count(*) from emp
3 group by ename )
4 where rownum < 500;

499 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=151 Card=499 Bytes=5
25690)

1 0 COUNT (STOPKEY)
2 1 VIEW (Cost=151 Card=17523 Bytes=525690)
3 2 SORT (GROUP BY STOPKEY) (Cost=151 Card=17523 Bytes=403
029)

4 3 TABLE ACCESS (FULL) OF 'EMP' (Cost=15 Card=29118 Byt
es=669714)





Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
139 consistent gets
0 physical reads
0 redo size
21264 bytes sent via SQL*Net to client
866 bytes received via SQL*Net from client
35 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
499 rows processed

Regards,
Karthi
Tom Kyte
February 16, 2010 - 10:57 am UTC

because the optimizer gets smarter with each release. You are using 9i - 5 releases old - and it did not push the estimate cardinality down into the index part. We can see that:

ops$tkyte%ORA9IR2> set autotrace traceonly explain
ops$tkyte%ORA9IR2> select empno, ename from ( select empno, ename from emp order by empno ) where rownum < 500;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=192 Card=499 Bytes=14970)
   1    0   COUNT (STOPKEY)
   2    1     VIEW (Cost=192 Card=27972 Bytes=839160)
   3    2       SORT (ORDER BY STOPKEY) (Cost=192 Card=27972 Bytes=783216)
   4    3         TABLE ACCESS (FULL) OF 'EMP' (Cost=15 Card=27972 Bytes=783216)



ops$tkyte%ORA9IR2> select empno, ename from ( select /*+ first_rows */ empno, ename from emp order by empno ) where rownum < 500;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=23817 Card=499 Bytes=14970)
   1    0   COUNT (STOPKEY)
   2    1     VIEW (Cost=23817 Card=27972 Bytes=839160)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=23817 Card=27972 Bytes=783216)
   4    3         INDEX (FULL SCAN) OF 'EMP_IDX' (NON-UNIQUE) (Cost=62 Card=27972)






So, the index full scan was costed at 62 (lots of single block IO's) whereas the full scan was costed at 15 - full scan won - but only because the estimated cardinality on the index was 27972 - not 499.

Update to more current software:

ops$tkyte%ORA10GR2> set autotrace traceonly explain
ops$tkyte%ORA10GR2> select empno, ename from ( select empno, ename from emp order by empno ) where rownum < 500;

Execution Plan
----------------------------------------------------------
Plan hash value: 2549860052

-----------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |   499 | 14970 |     8   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY                |         |       |       |            |          |
|   2 |   VIEW                        |         |   499 | 14970 |     8   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| EMP     | 50009 |  1367K|     8   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN           | EMP_IDX |   499 |       |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<500)




and you see the estimated cardinality is more in line.


Is COST Cumulative

V.Hariharaputhran, August 10, 2010 - 8:29 am UTC

Mr.Oracle

How is the cost in a plan shouldl read,

-----------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |   499 | 14970 |     8   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY                |         |       |       |            |          |
|   2 |   VIEW                        |         |   499 | 14970 |     8   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| EMP     | 50009 |  1367K|     8   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN           | EMP_IDX |   499 |       |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------


Cost : I understand that it is the cost consumed for each step, but how should it be read when it comes to total cost of the query, off course the top one indicates 8 in the above case, but the summation is not comming to 8. Please help to understand better.

Regards
V.Hari
Tom Kyte
August 18, 2010 - 10:59 pm UTC

the cost of using the index was 3

the cost of accessing the table after using the index was 5, resulting in a total cost of 8 so far.

The view step is just a step, no real work done there, just more of a placeholder - an indicator - a telling message to you and me - so cost still 8

the count stopkey is much like the view step here, it is telling us "there must have been a rownum operation there" - that the query would prematurely 'stop' - and hence it has no effect on the cost either. cost is still 8.

Therefore, the cost is 8


The cost is not cumulative, it is an additive thing - steps that "add work" add to the cost.

Reading Cost

V.Hariharaputhran, August 19, 2010 - 5:20 am UTC

Mr.Oracle
Excellent,Simple,informative
Thanks for your time, effort and reply.

Now in this case

---------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                       | Rows |Bytes| Cost(%CPU)|
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                            |  1809 |653K| 23387 (100)|
|   1 |  UNION-ALL                          |                            |       |    |           |
|   2 |   NESTED LOOPS                      |                            |     1 |2311|    39  (0)|
|   3 |    NESTED LOOPS                     |                            |     1 |2177|    37  (0)|
|   4 |     NESTED LOOPS                    |                            |     1 |2155|    35  (0)|
|   5 |      NESTED LOOPS                   |                            |     1 |2093|    33  (0)|
|   6 |       NESTED LOOPS                  |                            |     1 |2084|    31  (0)|
|*  7 |        TABLE ACCESS FULL            | TBLMSGEXT_CTM              |     1 |2038|    30  (0)|
|*  8 |        TABLE ACCESS BY INDEX ROWID  | TBLMSG                     |     1 |  46|     1  (0)|
|*  9 |         INDEX UNIQUE SCAN           | IDX_TBLMSG_ID              |     1 |    |     1  (0)|
|* 10 |       TABLE ACCESS BY INDEX ROWID   | TBLMSGEXT_SETTLEMENT       |     1 |   9|     2  (0)|
|* 11 |        INDEX UNIQUE SCAN            | PKIDX_TBLMSGEXT_SETTLEMENT |     1 |    |     1  (0)|
|  12 |      TABLE ACCESS BY INDEX ROWID    | TBLMSGEXT_TRADE            |     1 |  62|     2  (0)|
|* 13 |       INDEX UNIQUE SCAN             | PKIDX_TBLMSGEXT_TRADE      |     1 |    |     1  (0)|
|  14 |     TABLE ACCESS BY INDEX ROWID     | TBLGROUP                   |     1 |  22|     2  (0)|
|* 15 |      INDEX UNIQUE SCAN              | PKIDX_TBLGROUP             |     1 |    |     1  (0)|
|  16 |    TABLE ACCESS BY INDEX ROWID      | TBLGROUPEXT_TRADE          |     1 | 134|     2  (0)|
|* 17 |     INDEX UNIQUE SCAN               | PKIDX_TBLGROUPEXT_TRADE    |     1 |    |     1  (0)|
|  18 |   NESTED LOOPS                      |                            |  1808 |651K| 23348  (1)|
|  19 |    NESTED LOOPS                     |                            |  1808 |542K| 20930  (1)|
|  20 |     NESTED LOOPS ANTI               |                            |  1808 |517K| 18637  (1)|
|  21 |      NESTED LOOPS                   |                            |  1838 |488K| 16737  (1)|
|  22 |       NESTED LOOPS                  |                            |  2160 |291K| 12737  (1)|
|  23 |        NESTED LOOPS                 |                            |  2194 |248K|  8347  (1)|
|  24 |         NESTED LOOPS                |                            |  2466 |257K|  5445  (1)|
|* 25 |          TABLE ACCESS BY INDEX ROWID| TBLMSGEXT_LOC              |  2465 |146K|   131  (0)|
|* 26 |           INDEX RANGE SCAN          | IDX_TBLMSGEXT_LOC          |  2699 |    |    19  (0)|
|* 27 |          TABLE ACCESS BY INDEX ROWID| TBLMSG                     |     1 |  46|     2  (0)|
|* 28 |           INDEX UNIQUE SCAN         | IDX_TBLMSG_ID              |     1 |    |     1  (0)|
|* 29 |         TABLE ACCESS BY INDEX ROWID | TBLMSGEXT_SETTLEMENT       |     1 |   9|     2  (0)|
|* 30 |          INDEX UNIQUE SCAN          | PKIDX_TBLMSGEXT_SETTLEMENT |     1 |    |     1  (0)|
|  31 |        TABLE ACCESS BY INDEX ROWID  | TBLGROUP                   |     1 |  22|     2  (0)|
|* 32 |         INDEX UNIQUE SCAN           | PKIDX_TBLGROUP             |     1 |    |     1  (0)|
|  33 |       TABLE ACCESS BY INDEX ROWID   | TBLGROUPEXT_TRADE          |     1 | 134|     2  (0)|
|* 34 |        INDEX UNIQUE SCAN            | PKIDX_TBLGROUPEXT_TRADE    |     1 |    |     1  (0)|
|* 35 |      TABLE ACCESS BY INDEX ROWID    | TBLTASKAUTHORISE           |    41 | 861|     2  (0)|
|* 36 |       INDEX RANGE SCAN              | MELIDX_TBLTASKAUTHORISE_01 |     1 |    |     1  (0)|
|  37 |     TABLE ACCESS BY INDEX ROWID     | TBLMATCH                   |     1 |  14|     2  (0)|
|* 38 |      INDEX UNIQUE SCAN              | PKIDX_TBLMATCH             |     1 |    |     1  (0)|
|  39 |    TABLE ACCESS BY INDEX ROWID      | TBLMSGEXT_TRADE            |     1 |  62|     2  (0)|
|* 40 |     INDEX UNIQUE SCAN               | PKIDX_TBLMSGEXT_TRADE      |     1 |    |     1  (0)|
---------------------------------------------------------------------------------------------------


Reading Cost
Id values from above are indicated as "steps" below for understanding.

Cost from Steps 7 8 9 leads to step 6 at a Cost = 31
Steps 11 --> 10 cost = 2.
Hence Cost at step 5 = 2(From step 10) + 31(At step 6)= 33
Steps 13 --> 12 cost = 2.
Hence Cost at step 4 = 2(From step 12) + 33(At step 5)= 35
Steps 15 --> 14 cost = 2.
Hence Cost at step 3 = 2(From step 14) + 35(At step 4)= 37
Steps 17 --> 16 cost = 2.
Hence Cost at step 2 = 2(From step 16) + 37(At step 3)= 39

Like wise finally we get
Step 0 = Step 18 + Step 2 = 23348 + 39 = 23387

Guru is my understanding correct.


Regards
V.Hari
Tom Kyte
August 19, 2010 - 2:44 pm UTC

they tend to roll up additively or multiplicativly - yes.

Reading COST

V.Hariharaputhran, August 19, 2010 - 11:54 pm UTC

Mr.Oracle

Thanks for your time.

Regards
V.Hari

A reader, September 17, 2010 - 7:10 am UTC

Sir,

Plan 1
FILTER
TABLE ACCESS FULL table1
TABLE ACCESS FULL table2

Plan 2
NESTED LOOPS
TABLE ACCESS FULL table1
TABLE ACCESS FULL table2



What is the difference between the above two plans ?
Will the optimizer execute them using the SAME alogirithm internally ?

thanks for your time

Tom Kyte
September 17, 2010 - 7:14 am UTC

give full plan with everything including indentation and the queries so we have a bit of context to look at here.

A reader, September 17, 2010 - 10:28 am UTC

thanks Tom,

this is On 9iR2-

the plan is really simple as shown below:

SQL:

select * from A where
UPDATED < TO_DATE('14/10/2007 00:00:00', 'dd/mm/yyyy HH24:MI:SS')
and not exists (select null from B WHERE B.PTT_ACCNO=A.EIRCOM_ACCOUNT_NO);

Plan:
------------------
SELECT STATEMENT CHOOSE
€FILTER
|TABLE ACCESS FULL A
|TABLE ACCESS FULL B



there is an index on both eircom_account_no in the table A and ptt_accno in the table B - we have gathered statistics also - but not sure why the plan is like that, two full scans (I know full scans are not eveil but I belive in this context they are evil :)


250k rows in table B
Filter ratio of tale A= 550K/1.1 million rows =0.5


here how FILTER works ?

Tom Kyte
September 17, 2010 - 1:00 pm UTC

why would you not want a full scan?? You really want to fire off that query in the not exists and do an index range scan for every row in the outer query?

Show me BOTH plans plese - using DBMS_XPLAN.DISPLAY

A reader, September 17, 2010 - 11:27 am UTC

On 11g - it is very fast
It used the smart techniques 'SEMI/ANTI JOIN'
but I think On 9i - i am not sure whether they wil work or not ???


2 | HASH JOIN ANTI | | 11568 | 451K| 8910 (1)| 00:0
7 |

3 | TABLE ACCESS FULL| T1_PHONE_ORDER | 11570 | 350K| 7620 (1)| 00:0
2 |

4 | TABLE ACCESS FULL| T3_LR_MASTER | 250K| 2202K| 1289 (1)| 00:0
6 |

explain plans are different,

A reader, October 10, 2010 - 9:32 am UTC

Hello,

I have a exact same SQL statement that gives two different explain plan from two different user IDs. Logged in as user "A", I get appropriate explain plan and when I do explain plan for the exact same SQL statement as user "B", I get a different one. As user "B" the SQL statement takes very long hours to complete the execution but when I execute the same as user "A", it runs fast.

I checked the object ID and it referes the exactly same ones irrespective of user ids.

Why would this happen and how I can fix it? This is on 11gR1 on HPUX.

Thanks
Tom Kyte
October 11, 2010 - 12:02 pm UTC

no example, no comment.

I don't see a query
I don't see differing plans
I don't know anything about the schema, what features are in use or not.
I don't know much about this case at all - makes it really hard to comment.

explan plans are different....cont

A reader, October 10, 2010 - 9:34 am UTC

Hello,

I forgot to put the explain plan in my earlier post.

Explain Plan for user id 142.
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 26M| 6419M| 890K (2)| 03:27:54 |
| 1 | TABLE ACCESS FULL| BACKLOG_DELV | 26M| 6419M| 890K (2)| 03:27:54 |
----------------------------------------------------------------------------------

Explain plan for User id 366

------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 26M| 6419M| 246K (2)| 00:57:37 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 26M| 6419M| 246K (2)| 00:57:37 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 26M| 6419M| 246K (2)| 00:57:37 | Q1,00 | PCWC | |
| 4 | TABLE ACCESS FULL| BACKLOG_DELV | 26M| 6419M| 246K (2)| 00:57:37 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------

Tom Kyte
October 11, 2010 - 12:04 pm UTC

one person is using parallel query and one is not.

show us the query. show us the parallel settings on the table. show us the create table if possible.

are you sure on that time difference? This is a simple full scan - the client retrieving this would typically be the big bottleneck - nothing parallel query would typically "fix"

follow up,

A reader, October 11, 2010 - 1:42 pm UTC

Hello,

The query for the above explain plans is below:

select /*+ FULL(backlog_delv) PARALLEL(backlog_delv,4) */
                   legacy_order_no, shipment_group, delivery_group,
               status,                      block_oe_cr, block_oe_cfg,
block_oe_doc, block_oe_info, block_oe_oth, block_ff_cfg, block_ff_doc, block_ff_mat, block_ff_can,
   block_ff_chg, block_ff_uns, block_ff_oth,
block_ship, block_bill,                    block_oe_pro,
       block_oe_pri,                       block_ff_cons,
       block_ship_tra,                     block_bill_inv,
       delivery_prio,                      block_oe_exp,
       block_oe_stax,                      block_oe_acc,
       block_ship_mer,                     block_fo_sap,
       asap,                               stt, sat, ftt,
       block_fo_ois,                       to_char(compl_landed_date,
'YYYY-MM-DD HH24:MI:SS'),                    block_can,
         block_bill_part,                    block_ff_alli,
         block_ff_alloc,                     block_ff_cred,
         block_ff_esc1,                      block_ff_esc2,
         block_ff_esc3,                      block_ff_fact,
         block_ff_exp,                       block_ff_npi,
         block_ff_obs,                       block_ff_plant,
         block_ff_prod,                      block_ff_shipm,
         block_ff_tba,                       block_oe_bitf,
         block_oe_cont,                      block_oe_cust,
         block_oe_cins,                      block_oe_cmov,
         block_oe_crp,                       block_oe_data,
         block_oe_deal,                      block_oe_dpri,
         block_oe_dupo,                      block_oe_eord,
         block_oe_fulcr,                     block_oe_leas,
         block_oe_lic,                       block_oe_man,
         block_oe_ois,                       block_oe_oee,
         block_oe_pay,                       block_oe_res,
         block_oe_so,                        block_oe_serv,
         block_oe_ship,                      block_oe_shto,
         block_oe_skel,                      block_oe_supp,
         block_oe_vdps,                      block_ship_fo,
         block_ship_sch,                     block_st_awcf,
         block_st_awcr,                      block_st_awrp,
         block_st_ffw,                       block_st_oew,
         block_st_shw               from backlog_delv


Some more information from dba_tables:

SQL> select owner,degree,last_analyzed,num_rows,partitioned from dba_tables where table_name='BACKLOG_DELV';

OWNER                          DEGREE                                   LAST_ANALYZED         NUM_ROWS PAR
------------------------------ ---------------------------------------- ------------------- ---------- ---
OSSDBA                                  1                               2010-10-10 13:28:22   26476274 NO

I don't have DDL for this table.  This table has around 15 indexes as well.

I haven't run this query but the DBA and his other team members have run and found that when the query not run in parallel elpased more than 12 hours when the query got parallel degrees elapsed less than an hour.


Thanks,


Tom Kyte
October 11, 2010 - 2:20 pm UTC

tell me, who owns backlog_delv, is backlog_delv a view, a synonym (it must be one of those since you do not have a schema name)

are you sure the two different users use the same query - if backlog_delv is NOT a synonym - they are not using the same query (it could not compile "as is")

follow up,

A reader, October 11, 2010 - 2:24 pm UTC

BACKLOG_DELV is owned by OSSDBA.  I have that information in my query result against dba_tables.  It has public synonym as well.

SQL> l
  1* select object_name,object_type,owner,created,last_ddl_time from dba_objects where object_name='BACKLOG_DELV'
SQL> /

OBJECT_NAME          OBJECT_TYPE         OWNER                          CREATED             LAST_DDL_TIME
-------------------- ------------------- ------------------------------ ------------------- -------------------
BACKLOG_DELV         SYNONYM             PUBLIC                         2008-02-18 09:06:34 2009-08-22 07:16:09
BACKLOG_DELV         TABLE               OSSDBA                         2008-02-18 06:48:07 2010-10-10 00:53:23

By the way, does the value of optimizer_secure_view_merging parmeter makes a difference?  It is set to TRUE in our case.  

Thanks,



Tom Kyte
October 11, 2010 - 2:33 pm UTC

how are you getting the plans. optimizer secure view merging shouldn't have any impact on such a simple query, no.

Let me see the plan get generated - cut and paste a connect as each user, issuing a set autotrace traceonly explain - followed by the query and then the plan being displayed.


explain plan differ - follow up,

A reader, October 11, 2010 - 4:36 pm UTC

Hello,

I am working with the team to get login credentials of that particular USERNAME and send you the details you have requested for.

Thanks for your help.


explain plan differ - follow up,

A reader, October 11, 2010 - 6:28 pm UTC

Hello,

I got the information you wanted.  Here is the information below.  It's the same SQL statement.  The explain plan for USERNAME ossevent is the one that doesn't leverage parallelism.

Thanks,


SQL> conn ossevent/xxxxxxx

SQL> set autotrace traceonly explain
SQL> l
  1  select /*+ FULL(backlog_delv) PARALLEL(backlog_delv,4) */
  2                     legacy_order_no, shipment_group, delivery_group,
  3                 status,                      block_oe_cr, block_oe_cfg,
  4  block_oe_doc, block_oe_info, block_oe_oth, block_ff_cfg, block_ff_doc, block_ff_mat, block_ff_can,
  5     block_ff_chg, block_ff_uns, block_ff_oth,
  6  block_ship, block_bill,                    block_oe_pro,
  7         block_oe_pri,                       block_ff_cons,
  8         block_ship_tra,                     block_bill_inv,
  9         delivery_prio,                      block_oe_exp,
 10         block_oe_stax,                      block_oe_acc,
 11         block_ship_mer,                     block_fo_sap,
 12         asap,                               stt, sat, ftt,
 13         block_fo_ois,                       to_char(compl_landed_date,
 14  'YYYY-MM-DD HH24:MI:SS'),                    block_can,
 15           block_bill_part,                    block_ff_alli,
 16           block_ff_alloc,                     block_ff_cred,
 17           block_ff_esc1,                      block_ff_esc2,
 18           block_ff_esc3,                      block_ff_fact,
 19           block_ff_exp,                       block_ff_npi,
 20           block_ff_obs,                       block_ff_plant,
 21           block_ff_prod,                      block_ff_shipm,
 22           block_ff_tba,                       block_oe_bitf,
 23           block_oe_cont,                      block_oe_cust,
 24           block_oe_cins,                      block_oe_cmov,
 25           block_oe_crp,                       block_oe_data,
 26           block_oe_deal,                      block_oe_dpri,
 27           block_oe_dupo,                      block_oe_eord,
 28           block_oe_fulcr,                     block_oe_leas,
 29           block_oe_lic,                       block_oe_man,
 30           block_oe_ois,                       block_oe_oee,
 31           block_oe_pay,                       block_oe_res,
 32           block_oe_so,                        block_oe_serv,
 33      block_oe_ship,                      block_oe_shto,
 34      block_oe_skel,                      block_oe_supp,
 35      block_oe_vdps,                      block_ship_fo,
 36      block_ship_sch,                     block_st_awcf,
 37      block_st_awcr,                      block_st_awrp,
 38      block_st_ffw,                       block_st_oew,
 39*     block_st_shw               from backlog_delv
SQL> /

Execution Plan
----------------------------------------------------------
Plan hash value: 1123316539

----------------------------------------------------------------------------------
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |              |    26M|  6438M|   891K  (2)| 03:27:55 |
|   1 |  TABLE ACCESS FULL| BACKLOG_DELV |    26M|  6438M|   891K  (2)| 03:27:55 |
----------------------------------------------------------------------------------

SQL>
SQL> conn osswww/xxxxxxx
Connected.
SQL> set autotrace traceonly explain
SQL> l
  1  select /*+ FULL(backlog_delv) PARALLEL(backlog_delv,4) */
  2                     legacy_order_no, shipment_group, delivery_group,
  3                 status,                      block_oe_cr, block_oe_cfg,
  4  block_oe_doc, block_oe_info, block_oe_oth, block_ff_cfg, block_ff_doc, block_ff_mat, block_ff_can,
  5     block_ff_chg, block_ff_uns, block_ff_oth,
  6  block_ship, block_bill,                    block_oe_pro,
  7         block_oe_pri,                       block_ff_cons,
  8         block_ship_tra,                     block_bill_inv,
  9         delivery_prio,                      block_oe_exp,
 10         block_oe_stax,                      block_oe_acc,
 11         block_ship_mer,                     block_fo_sap,
 12         asap,                               stt, sat, ftt,
 13         block_fo_ois,                       to_char(compl_landed_date,
 14  'YYYY-MM-DD HH24:MI:SS'),                    block_can,
 15           block_bill_part,                    block_ff_alli,
 16           block_ff_alloc,                     block_ff_cred,
 17           block_ff_esc1,                      block_ff_esc2,
 18           block_ff_esc3,                      block_ff_fact,
 19           block_ff_exp,                       block_ff_npi,
 20           block_ff_obs,                       block_ff_plant,
 21           block_ff_prod,                      block_ff_shipm,
 22           block_ff_tba,                       block_oe_bitf,
 23           block_oe_cont,                      block_oe_cust,
 24           block_oe_cins,                      block_oe_cmov,
 25           block_oe_crp,                       block_oe_data,
 26           block_oe_deal,                      block_oe_dpri,
 27           block_oe_dupo,                      block_oe_eord,
 28           block_oe_fulcr,                     block_oe_leas,
 29           block_oe_lic,                       block_oe_man,
 30           block_oe_ois,                       block_oe_oee,
 31           block_oe_pay,                       block_oe_res,
 32           block_oe_so,                        block_oe_serv,
 33      block_oe_ship,                      block_oe_shto,
 34      block_oe_skel,                      block_oe_supp,
 35      block_oe_vdps,                      block_ship_fo,
 36      block_ship_sch,                     block_st_awcf,
 37      block_st_awcr,                      block_st_awrp,
 38      block_st_ffw,                       block_st_oew,
 39*     block_st_shw               from backlog_delv
SQL> /

Execution Plan
----------------------------------------------------------
Plan hash value: 3400378765

------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name         | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |              |    26M|  6438M|   246K  (2)| 00:57:37 |        |      |            |
|   1 |  PX COORDINATOR      |              |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000     |    26M|  6438M|   246K  (2)| 00:57:37 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR |              |    26M|  6438M|   246K  (2)| 00:57:37 |  Q1,00 | PCWC |            |
|   4 |     TABLE ACCESS FULL| BACKLOG_DELV |    26M|  6438M|   246K  (2)| 00:57:37 |  Q1,00 | PCWP |            |
------------------------------------------------------------------------------------------------------------------


Tom Kyte
October 12, 2010 - 7:10 am UTC

Ok, verify that

o there are no logon triggers that do things like disable parallel query for a group of users

o there are no resource plans that limit the use of parallel query for a group of users

and post the output of:


select name || ' = ' || value from v$parameter where isdefault <> 'TRUE';


for the instance this is affecting.

explain plans differ - follow up,

A reader, October 12, 2010 - 9:17 am UTC

Hello,

I am giving the information you have requested for.  There is a LOGON trigger owned by OSSEVENT user.  

SQL> select trigger_name,owner,trigger_type from dba_triggers where triggering_event like 'LOGON%';

TRIGGER_NAME                   OWNER                          TRIGGER_TYPE
------------------------------ ------------------------------ ----------------
LOGINCHECK                     OSSEVENT                       AFTER EVENT

The logon trigger is owned by OSSEVENT.  This is the user which is not leveraging parallelism.

TRIGGER_BODY
--------------------------------------------------------------------------------

DECLARE
   v_inst_name VARCHAR2(10);
   sqlstr1 VARCHAR2(10) := 'OSSP1';
   sqlstr2 VARCHAR2(10) :='OSSP2';
BEGIN
   select instance_name
  INTO   v_inst_name
  from   sys.v_$instance;
if (v_inst_name=sqlstr1)
THEN
      execute immediate 'alter session set PARALLEL_INSTANCE_GROUP=OSSP1';
else
execute immediate 'alter session set PARALLEL_INSTANCE_GROUP=OSSP2';
   END IF;
END ;


On Instance 1(OSSP1):

SQL> l
  1* select name || ' = ' || value from v$parameter where isdefault <> 'TRUE'

processes = 800
sessions = 885
timed_statistics = TRUE
sga_max_size = 49660559360
shared_pool_size = 10737418240
large_pool_size = 1073741824
spfile = /u01/app/oracle/admin/OSSP/pfile/spfileOSSP.ora
nls_date_format = YYYY-MM-DD HH24:Mi:SS
filesystemio_options = SETALL
disk_asynch_io = FALSE
backup_tape_io_slaves = TRUE
resource_manager_plan =
sga_target = 0
control_files = /u02/origlog/OSSP/cntl1OSSP.ctl, /u03/mirrlog/OSSP/cntl2OSSP.ctl, /u05/oradata/OSSP/cntl3OSSP.ctl
db_file_name_convert = /u11/oradata/OSSP, /u10/oradata/OSSP
log_file_name_convert = /u02/origlog/OSSS, /u02/origlog/OSSP, /u03/mirrlog/OSSS, /u03/mirrlog/OSSP
control_file_record_keep_time = 45
db_ultra_safe = DATA_ONLY
db_block_size = 16384
db_cache_size = 32212254720
db_32k_cache_size = 4294967296
db_recycle_cache_size = 0
db_writer_processes = 16
compatible = 10.2.0.2.0
log_archive_config = DG_CONFIG=(OSSP,OSSS)
log_archive_dest_1 = LOCATION=/u01/app/oracle/arch/OSSP
log_archive_dest_2 = SERVICE=OSSS DELAY=360 LGWR ASYNC valid_for=(online_logfiles,primary_role) DB_UNIQUE_NAME=OSSS
log_archive_dest_3 =
log_archive_dest_state_2 = ENABLE
log_archive_dest_state_3 = ENABLE
log_archive_format = OSSParch%r_%t_%s.dbf
fal_client = OSSP2
fal_server = OSSS
log_buffer = 31457280
archive_lag_target = 900
db_files = 1022
db_file_multiblock_read_count = 32
cluster_database = TRUE
cluster_database_instances = 4
db_recovery_file_dest = /u01/app/oracle/arch/OSSP/flash_recovery
db_recovery_file_dest_size = 322122547200
standby_file_management = AUTO
_gc_integrity_checks = 0
thread = 1
recovery_parallelism = 8
db_flashback_retention_target = 1440
dml_locks = 1000
replication_dependency_tracking = TRUE
undo_management = AUTO
undo_tablespace = UNDO_01
instance_number = 1
_kks_use_mutex_pin = FALSE
remote_login_passwordfile = EXCLUSIVE
db_domain =
global_names = TRUE
instance_name = OSSP1
service_names = OSSPC22, OSSPC3, OSSPC, OSSP, OSSPC1
max_dispatchers = 15
local_listener = LISTENER_OSSP1
remote_listener = LISTENER_OSSP
session_cached_cursors = 200
job_queue_processes = 10
cursor_sharing = EXACT
result_cache_mode = MANUAL
parallel_min_servers = 4
parallel_max_servers = 100
_px_execution_services_enabled = FALSE
parallel_execution_message_size = 8200
result_cache_max_size = 5242880
core_dump_dest = /u01/app/oracle/admin/OSSP/cdump
audit_file_dest = /u01/app/oracle/admin/OSSP/adump
db_name = OSSP
db_unique_name = OSSP
open_cursors = 500
star_transformation_enabled = false
_optim_peek_user_binds = TRUE
pga_aggregate_target = 10737418240
workarea_size_policy = auto
_bloom_filter_enabled = FALSE
diagnostic_dest = /u01/app/oracle/admin
max_dump_file_size = 1024M

On Instance 2 (OSSP2):

SQL> select name || ' = ' || value from v$parameter where isdefault <> 'TRUE';

NAME||'='||VALUE
--------------------------------------------------------------------------------
processes = 800
sessions = 885
timed_statistics = TRUE
sga_max_size = 49660559360
shared_pool_size = 10737418240
large_pool_size = 1073741824
spfile = /u01/app/oracle/admin/OSSP/pfile/spfileOSSP.ora
nls_date_format = YYYY-MM-DD HH24:Mi:SS
filesystemio_options = SETALL
disk_asynch_io = FALSE
backup_tape_io_slaves = TRUE
resource_manager_plan =
sga_target = 0
control_files = /u02/origlog/OSSP/cntl1OSSP.ctl, /u03/mirrlog/OSSP/cntl2OSSP.ctl
, /u05/oradata/OSSP/cntl3OSSP.ctl

db_file_name_convert = /u11/oradata/OSSP, /u10/oradata/OSSP
log_file_name_convert = /u02/origlog/OSSS, /u02/origlog/OSSP, /u03/mirrlog/OSSS,
 /u03/mirrlog/OSSP

control_file_record_keep_time = 45
db_ultra_safe = DATA_ONLY
db_block_size = 16384
db_cache_size = 32212254720
db_32k_cache_size = 4294967296
db_recycle_cache_size = 0
db_writer_processes = 16
compatible = 10.2.0.2.0
log_archive_config = DG_CONFIG=(OSSP,OSSS)
log_archive_dest_1 = LOCATION=/u01/app/oracle/arch/OSSP
log_archive_dest_2 = SERVICE=OSSS DELAY=360 LGWR ASYNC valid_for=(online_logfile
s,primary_role) DB_UNIQUE_NAME=OSSS

log_archive_dest_3 =
log_archive_dest_state_2 = ENABLE
log_archive_dest_state_3 = ENABLE
log_archive_format = OSSParch%r_%t_%s.dbf
fal_client = OSSP2
fal_server = OSSS
log_buffer = 31457280
archive_lag_target = 900
db_files = 1022
db_file_multiblock_read_count = 32
cluster_database = TRUE
cluster_database_instances = 4
db_recovery_file_dest = /u01/app/oracle/arch/OSSP/flash_recovery
db_recovery_file_dest_size = 322122547200
standby_file_management = AUTO
_gc_integrity_checks = 0
thread = 2
recovery_parallelism = 8
db_flashback_retention_target = 1440
dml_locks = 1000
replication_dependency_tracking = TRUE
undo_management = AUTO
undo_tablespace = UNDO_02
instance_number = 2
_kks_use_mutex_pin = FALSE
remote_login_passwordfile = EXCLUSIVE
db_domain =
global_names = TRUE
instance_name = OSSP2
service_names = OSSP,OSSPC3,OSSPC22,OSSPC
max_dispatchers = 15
local_listener = LISTENER_OSSP2
remote_listener = LISTENER_OSSP
session_cached_cursors = 200
job_queue_processes = 10
cursor_sharing = EXACT
result_cache_mode = MANUAL
parallel_min_servers = 4
parallel_max_servers = 100
_px_execution_services_enabled = FALSE
parallel_execution_message_size = 8200
result_cache_max_size = 5242880
core_dump_dest = /u01/app/oracle/admin/OSSP/cdump
audit_file_dest = /u01/app/oracle/admin/OSSP/adump
db_name = OSSP
db_unique_name = OSSP
open_cursors = 500
star_transformation_enabled = false
_optim_peek_user_binds = TRUE
pga_aggregate_target = 10737418240
workarea_size_policy = auto
_bloom_filter_enable

SQL> conn ossevent/xxxxxxx
Connected.
SQL> select resource_name,limit from user_resource_limits;

RESOURCE_NAME                    LIMIT
-------------------------------- ----------------------------------------
COMPOSITE_LIMIT                  UNLIMITED
SESSIONS_PER_USER                UNLIMITED
CPU_PER_SESSION                  UNLIMITED
CPU_PER_CALL                     UNLIMITED
LOGICAL_READS_PER_SESSION        UNLIMITED
LOGICAL_READS_PER_CALL           UNLIMITED
IDLE_TIME                        UNLIMITED
CONNECT_TIME                     UNLIMITED
PRIVATE_SGA                      UNLIMITED
u01/app/oracle/admin
max_dump_file_size = 1024M

Thanks,

Tom Kyte
October 12, 2010 - 10:50 am UTC

ouch, that is pretty ugly isn't it - why so many settings.

what is
_px_execution_services_enabled
being set for?

if you apply this logic:

if (v_inst_name=sqlstr1)
THEN
execute immediate 'alter session set PARALLEL_INSTANCE_GROUP=OSSP1';
else
execute immediate 'alter session set PARALLEL_INSTANCE_GROUP=OSSP2';
END IF;

in your session - what happens to the plan then? Make everything "equal" here.

explain plan differ - follow up,

A reader, October 12, 2010 - 9:31 am UTC

Hello,

In addition to the above details (in my previous post), I like to provide some more information about init.ora parameters instance_group and parallel_instance_gruop.

On Instance 2, the parallel instance group is not set.  Not sure whether this is one of the problems.


On instance 1 (OSSP1)
SQL> show parameter group

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
asm_diskgroups                       string
asm_preferred_read_failure_groups    string
instance_groups                      string
parallel_instance_group              string      OSSP1


On Instance 2 (OSSP2)
SQL> show parameter group

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
asm_diskgroups                       string
asm_preferred_read_failure_groups    string
instance_groups                      string
parallel_instance_group              string

Thanks,

Tom Kyte
October 12, 2010 - 10:51 am UTC

see above, make everything "equal"

explain plan differ - follow up,

A reader, October 12, 2010 - 11:08 am UTC

Hello,

SQL> show parameter _px

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_px_execution_services_enabled       boolean     FALSE


I didn't understand what to "make equal".  You mean set parallel_instance_group to same instance in both "if"
 and "else" condition?

Thanks,

Tom Kyte
October 12, 2010 - 11:12 am UTC

set the instance group in the session of the one that isn't getting parallel - make it EQUAL as in the same - remove all differences between the two operating environments (the schema that works is one operating environment, the schema that does not is the other)

Apply the logic found in the trigger to the session not getting parallel - manually issue the alter session command.


Why is _px_execution_services_enabled set to a none default value was my question - who did it, why did they do it, what was the goal there.

explain plan differ - follow up,

A reader, October 12, 2010 - 11:31 am UTC

Hello,

The issue is on both the instances. It is not getting parallel in either one of them.

I am not sure about _px_execution_services_enabled parameter is. I believe it is new since 11.1. Is it recommended to set it to TRUE?

I will work on your advice on instance_groups and let you know.

Thanks,


Tom Kyte
October 12, 2010 - 11:46 am UTC

it is recommended to NOT set parameters unless and until you have a completely valid reason to. If you don't know why something is set - you should be questioning it.


If you rewrite the query (just to rule out the synonym) to reference the table directly by owner.table - what happens then, something like:

SQL> l
  1  select<b> /*+ FULL(bd) PARALLEL(bd,4) */</b>
  2                     legacy_order_no, shipment_group, delivery_group,
...
 38      block_st_ffw,                       block_st_oew,
 39*     block_st_shw               from OWNER.backlog_delv <b>BD</b>
SQL> /
 

explain plan differ - follow up,

A reader, October 12, 2010 - 12:21 pm UTC

Hello,

I believe I tried all the following:

1. on both OSPP1 & OSSP2 instances, I tried setting PARALLEL_INSTANCE_GRUOP to OSSP1 and also OSPP2.  No change in plan

2. I tried by prefexing owner name to the table (and also used alias).  No change in plan


SQL> conn ossevent/xxxxxxxxx
Connected.
SQL> alter session set parallel_instance_group=OSSP2
  2  ;

Session altered.
SQL> set autotrace traceonly explain
SQL> select /*+ FULL(bd) PARALLEL(bd,4) */
  2                     legacy_order_no, shipment_group, delivery_group,
  3                 status,                      block_oe_cr, block_oe_cfg,
  4  block_oe_doc, block_oe_info, block_oe_oth, block_ff_cfg, block_ff_doc, block_ff_mat, block_ff_can,
  5     block_ff_chg, block_ff_uns, block_ff_oth,
  6  block_ship, block_bill,                    block_oe_pro,
  7         block_oe_pri,                       block_ff_cons,
  8         block_ship_tra,                     block_bill_inv,
  9         delivery_prio,                      block_oe_exp,
 10         block_oe_stax,                      block_oe_acc,
 11         block_ship_mer,                     block_fo_sap,
 12         asap,                               stt, sat, ftt,
 13         block_fo_ois,                       to_char(compl_landed_date,
 14  'YYYY-MM-DD HH24:MI:SS'),                    block_can,
 15           block_bill_part,                    block_ff_alli,
 16           block_ff_alloc,                     block_ff_cred,
 17           block_ff_esc1,                      block_ff_esc2,
 18           block_ff_esc3,                      block_ff_fact,
 19           block_ff_exp,                       block_ff_npi,
 20           block_ff_obs,                       block_ff_plant,
 21           block_ff_prod,                      block_ff_shipm,
 22           block_ff_tba,                       block_oe_bitf,
 23           block_oe_cont,                      block_oe_cust,
 24           block_oe_cins,                      block_oe_cmov,
 25           block_oe_crp,                       block_oe_data,
 26           block_oe_deal,                      block_oe_dpri,
 27           block_oe_dupo,                      block_oe_eord,
 28           block_oe_fulcr,                     block_oe_leas,
 29           block_oe_lic,                       block_oe_man,
 30           block_oe_ois,                       block_oe_oee,
 31           block_oe_pay,                       block_oe_res,
 32           block_oe_so,                        block_oe_serv,
 33           block_oe_ship,                      block_oe_shto,
 34           block_oe_skel,                      block_oe_supp,
 35           block_oe_vdps,                      block_ship_fo,
 36           block_ship_sch,                     block_st_awcf,
 37           block_st_awcr,                      block_st_awrp,
 38           block_st_ffw,                       block_st_oew,
 39           block_st_shw               from ossdba.backlog_delv bd;

Execution Plan
----------------------------------------------------------
Plan hash value: 1123316539

----------------------------------------------------------------------------------
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |              |    26M|  6438M|   891K  (2)| 03:27:55 |
|   1 |  TABLE ACCESS FULL| BACKLOG_DELV |    26M|  6438M|   891K  (2)| 03:27:55 |
----------------------------------------------------------------------------------


On Instance 1:

SQL> alter session set PARALLEL_INSTANCE_GROUP=OSSP1;

Session altered.

SQL> set autotrace traceonly explain
SQL> select /*+ FULL(bd) PARALLEL(bd,4) */
  2                     legacy_order_no, shipment_group, delivery_group,
  3                 status,                      block_oe_cr, block_oe_cfg,
  4  block_oe_doc, block_oe_info, block_oe_oth, block_ff_cfg, block_ff_doc, block_ff_mat, block_ff_can,
  5     block_ff_chg, block_ff_uns, block_ff_oth,
  6  block_ship, block_bill,                    block_oe_pro,
  7         block_oe_pri,                       block_ff_cons,
  8         block_ship_tra,                     block_bill_inv,
  9         delivery_prio,                      block_oe_exp,
 10         block_oe_stax,                      block_oe_acc,
 11         block_ship_mer,                     block_fo_sap,
 12         asap,                               stt, sat, ftt,
 13         block_fo_ois,                       to_char(compl_landed_date,
 14  'YYYY-MM-DD HH24:MI:SS'),                    block_can,
 15           block_bill_part,                    block_ff_alli,
 16           block_ff_alloc,                     block_ff_cred,
 17           block_ff_esc1,                      block_ff_esc2,
 18           block_ff_esc3,                      block_ff_fact,
 19           block_ff_exp,                       block_ff_npi,
 20           block_ff_obs,                       block_ff_plant,
 21           block_ff_prod,                      block_ff_shipm,
 22           block_ff_tba,                       block_oe_bitf,
 23           block_oe_cont,                      block_oe_cust,
 24           block_oe_cins,                      block_oe_cmov,
 25           block_oe_crp,                       block_oe_data,
 26           block_oe_deal,                      block_oe_dpri,
 27           block_oe_dupo,                      block_oe_eord,
 28           block_oe_fulcr,                     block_oe_leas,
 29           block_oe_lic,                       block_oe_man,
 30           block_oe_ois,                       block_oe_oee,
 31           block_oe_pay,                       block_oe_res,
 32           block_oe_so,                        block_oe_serv,
         block_oe_ship,                      block_oe_shto,
 33   34           block_oe_skel,                      block_oe_supp,
 35           block_oe_vdps,                      block_ship_fo,
         block_ship_sch,                     block_st_awcf,
 36   37           block_st_awcr,                      block_st_awrp,
 38           block_st_ffw,                       block_st_oew,
 39           block_st_shw               from ossdba.backlog_delv bd;

Execution Plan
----------------------------------------------------------
Plan hash value: 1123316539

----------------------------------------------------------------------------------
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |              |    26M|  6438M|   891K  (2)| 03:27:55 |
|   1 |  TABLE ACCESS FULL| BACKLOG_DELV |    26M|  6438M|   891K  (2)| 03:27:55 |
----------------------------------------------------------------------------------


Thanks,

Tom Kyte
October 12, 2010 - 12:51 pm UTC

if you are willing, can you:


alter session set events '10053 trace name context forever, level 1';
explain plan for <your query>;
exit;

and send me the trace file (thomas.kyte@oracle.com) - one trace file for the "working" one and one trace file for the "not working" one.


explain plan differ - follow up,

A reader, October 12, 2010 - 1:30 pm UTC

Hello,

I am going to send you the zip files shortly.

Thanks a lot for your help.


Tom Kyte
October 12, 2010 - 1:48 pm UTC

easy, you have

parallel_execution_enabled = false


in the not_working trace and you don't in the working trace.

Somewhere, somehow, you have disabled parallel query execution in the schema that is not working. It is a session setting.


issue: alter session enable parallel query;

and what happens then.

explain plan differ - follow up,

A reader, October 12, 2010 - 2:17 pm UTC

Hello,

Thanks for reviewing my trace files.  I just did alter session and apparently the explain plan is still same.

SQL> alter session enable parallel query;

Session altered.

SQL> set autotrace traceonly explain
SQL> set lines 200
SQL> set pages 200
SQL> select /*+ FULL(backlog_delv) PARALLEL(backlog_delv,4) */
  2                     legacy_order_no, shipment_group, delivery_group,
  3                 status,                      block_oe_cr, block_oe_cfg,
  4  block_oe_doc, block_oe_info, block_oe_oth, block_ff_cfg, block_ff_doc, block_ff_mat, block_ff_can,
  5     block_ff_chg, block_ff_uns, block_ff_oth,
  6  block_ship, block_bill,                    block_oe_pro,
  7         block_oe_pri,                       block_ff_cons,
  8         block_ship_tra,                     block_bill_inv,
  9         delivery_prio,                      block_oe_exp,
 10         block_oe_stax,                      block_oe_acc,
 11         block_ship_mer,                     block_fo_sap,
 12         asap,                               stt, sat, ftt,
 13         block_fo_ois,                       to_char(compl_landed_date,
'YYYY-MM-DD HH24:MI:SS'),                    block_can,
 14   15           block_bill_part,                    block_ff_alli,
 16           block_ff_alloc,                     block_ff_cred,
 17           block_ff_esc1,                      block_ff_esc2,
 18           block_ff_esc3,                      block_ff_fact,
 19           block_ff_exp,                       block_ff_npi,
 20           block_ff_obs,                       block_ff_plant,
 21           block_ff_prod,                      block_ff_shipm,
 22           block_ff_tba,                       block_oe_bitf,
 23           block_oe_cont,                      block_oe_cust,
 24           block_oe_cins,                      block_oe_cmov,
 25           block_oe_crp,                       block_oe_data,
 26           block_oe_deal,                      block_oe_dpri,
         block_oe_dupo,                      block_oe_eord,
 27   28           block_oe_fulcr,                     block_oe_leas,
 29           block_oe_lic,                       block_oe_man,
 30           block_oe_ois,                       block_oe_oee,
 31           block_oe_pay,                       block_oe_res,
 32           block_oe_so,                        block_oe_serv,
 33           block_oe_ship,                      block_oe_shto,
 34           block_oe_skel,                      block_oe_supp,
 35           block_oe_vdps,                      block_ship_fo,
 36           block_ship_sch,                     block_st_awcf,
 37           block_st_awcr,                      block_st_awrp,
 38           block_st_ffw,                       block_st_oew,
 39           block_st_shw               from backlog_delv;

Execution Plan
----------------------------------------------------------
Plan hash value: 1123316539

----------------------------------------------------------------------------------
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |              |    26M|  6438M|   891K  (2)| 03:27:55 |
|   1 |  TABLE ACCESS FULL| BACKLOG_DELV |    26M|  6438M|   891K  (2)| 03:27:55 |
----------------------------------------------------------------------------------

SQL> show user
USER is "OSSEVENT"
SQL>

Thanks,

Tom Kyte
October 12, 2010 - 2:21 pm UTC

look in the 10053 trace, what setting do you see in there for it in the tracefile.

I also noticed that active_instance_count was different in each one as well

explain plan differ - follow up,

A reader, October 12, 2010 - 3:54 pm UTC

Hello,

I asked the DBA to perform a check by implementing a similar AFTER LOGON trigger on the other user as well. Later, the same query did not use parallelism on the other user as well. It appears the trigger is the root cause for this problem. Also on OSSEVENT session, I asked the DBA to check whether any other query runs with parallel slaves and apparently none of them did.

Last month the DBA had unset the instance_groups and parallel_instance_group parameter values (not sure what the earlier values was) and since then any query from OSSEVENT session is not running in parallel.

Now we know the root cause of the problem and how to fix it isn't decided yet. I appreciate a lot for your help.

Thanks,



Explain Doubt

V.Hariharaputhran, December 19, 2010 - 1:13 pm UTC

Mr.Oracle,

In the below plan, The feeding step for STEP 5 is not present, rather i should say as it is from STEP4.

EMP - Have PK_EMP index(primary Key).
EMP_1 - No Primary Key/Index.

"SQL_ID  5tm5s0xuantcn, child number 1"
"-------------------------------------"
"select aa.empno,aa.empname from    emp aa,emp_1 bb where 
"aa.EMPNO=bb.empno"
" "
"Plan hash value: 800080349"
" "
"---------------------------------------------------------------------------------------"
"| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |"
"---------------------------------------------------------------------------------------"
"|   0 | SELECT STATEMENT             |        |       |       |    16 (100)|          |"
"|   1 |  NESTED LOOPS                |        |       |       |            |          |"
"|   2 |   NESTED LOOPS               |        |     6 |   126 |    16   (0)| 00:00:01 |"
"|   3 |    TABLE ACCESS FULL         | EMP_1  |     6 |    24 |     4   (0)| 00:00:01 |"
"|*  4 |    INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     1   (0)| 00:00:01 |"
"|   5 |   TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    17 |     2   (0)| 00:00:01 |"
"---------------------------------------------------------------------------------------"
" "
"Query Block Name / Object Alias (identified by operation id):"
"-------------------------------------------------------------"
" "
"   1 - SEL$1"
"   3 - SEL$1 / BB@SEL$1"
"   4 - SEL$1 / AA@SEL$1"
"   5 - SEL$1 / AA@SEL$1"
" "
"Predicate Information (identified by operation id):"
"---------------------------------------------------"
" "
"   4 - access("AA"."EMPNO"="BB"."EMPNO")"
" "
"Column Projection Information (identified by operation id):"
"-----------------------------------------------------------"
" "
"   1 - "AA"."EMPNO"[NUMBER,22], "AA"."EMPNAME"[VARCHAR2,50]"
"   2 - "AA".ROWID[ROWID,10], "AA"."EMPNO"[NUMBER,22]"
"   3 - "BB"."EMPNO"[NUMBER,22]"
"   4 - "AA".ROWID[ROWID,10], "AA"."EMPNO"[NUMBER,22]"
"   5 - "AA"."EMPNAME"[VARCHAR2,50]"


The doubt is from the explain plan alignment I understand that, Step 2 & 5 occur similtaneously, so step 5 comes before performing step 4--> if this is the case how does it performs the table access with index rowid, when the step 4 is yet to perform.

Thanks for your time guru.

Regards
V.Hari


Tom Kyte
December 20, 2010 - 7:43 am UTC

nothing happens "simultaneously" in a serial plan...


this plan will be like this:

for x in (select * from emp_1)
loop
   lookup in pk_emp and find a rowid (or not) for EMP 
   if found rowid
       then save X + emp.rowid
   end if
   if we've saved "enough" rowids up
   then
       sort em quickly - put rowids together by blocks
       retrieve the emp data by rowid
       output them
   end if
end loop
if we've saved any more rowids up
then
   sort em quickly - put rowids together by blocks
   retrieve the emp data by rowid
   output them
end if


read explain plan

V.Hariharaputhran, December 20, 2010 - 1:33 pm UTC

Thanks for your valuable time guru. I should have not used the word simultaneously, but the reason is as below.

Test Case
Db Version : 10.2.0.3.0

create table emp as select rownum as EMPNO, rownum||'HI' AS EMPNAME,trunc(log(10,rownum)) as DEPTNO, rownum+1000 as SAL from dual connect by level < 1000000;
Alter table emp add constraint pk_emp primary key (empno);
create table emp_1 as select * from emp where empno in(999,19999,999999);

Declare
begin
dbms_stats.gather_table_stats(ownname => user, tabname => 'EMP' ,estimate_percent => 100, method_opt => 'for all indexed columns size auto');
dbms_stats.gather_table_stats(ownname => user, tabname => 'EMP_1' ,estimate_percent => 100, method_opt => 'for all indexed columns size auto');
End;
/

select aa.empno,aa.empname from emp aa, emp_1 bb where aa.empno=bb.empno;

"---------------------------------------------------------------------------------------"
"| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |"
"---------------------------------------------------------------------------------------"
"|   0 | SELECT STATEMENT             |        |       |       |     5 (100)|          |"
"|   1 |  NESTED LOOPS                |        |     3 |    48 |     5   (0)| 00:00:01 |"
"|   2 |   TABLE ACCESS FULL          | EMP_1  |     3 |    12 |     2   (0)| 00:00:01 |"
"|   3 |   TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    12 |     1   (0)| 00:00:01 |"
"|*  4 |    INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)|          |"
"---------------------------------------------------------------------------------------"

This plan is the usual one have seen, Which looks like 

For X in (select * from EMP_1)
Loop
  select * from EMP where EMP.empno = X.empno

  output respective records for the require columns 
End Loop

(Hope the above loop is correct for the plan above) 

 But the same test case in 11.2.0.1.0 gave the below plan

"---------------------------------------------------------------------------------------"
"| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |"
"---------------------------------------------------------------------------------------"
"|   0 | SELECT STATEMENT             |        |       |       |     9 (100)|          |"
"|   1 |  NESTED LOOPS                |        |       |       |            |          |"
"|   2 |   NESTED LOOPS               |        |     3 |   105 |     9   (0)| 00:00:01 |"
"|   3 |    TABLE ACCESS FULL         | EMP_1  |     3 |    39 |     3   (0)| 00:00:01 |"
"|*  4 |    INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     1   (0)| 00:00:01 |"
"|   5 |   TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    22 |     2   (0)| 00:00:01 |"
"---------------------------------------------------------------------------------------"

The Step one Nested Loop details are missing in the above plan, More info from trace below,

STAT #3 id=1 cnt=3 pid=0 pos=1 obj=0 op='NESTED LOOPS  (cr=15 pr=0 pw=0 time=30 us)'
STAT #3 id=2 cnt=3 pid=1 pos=1 obj=0 op='NESTED LOOPS  (cr=12 pr=0 pw=0 time=20 us cost=9 size=105 card=3)'
STAT #3 id=3 cnt=3 pid=2 pos=1 obj=74786 op='TABLE ACCESS FULL EMP_1 (cr=4 pr=0 pw=0 time=5 us cost=3 size=39 card=3)'
STAT #3 id=4 cnt=3 pid=2 pos=2 obj=74785 op='INDEX UNIQUE SCAN PK_EMP (cr=8 pr=0 pw=0 time=0 us cost=1 size=0 card=1)'
STAT #3 id=5 cnt=3 pid=1 pos=2 obj=74784 op='TABLE ACCESS BY INDEX ROWID EMP (cr=3 pr=0 pw=0 time=0 us cost=2 size=22 card=1)'


The above Plan's cost varies also the byte too in comparison with 10g.

Does this save X + rowid step which you have specified in the just above follow up, exists prior 11g ? Is this part of oracle 11g optimizer changes.

Or

During Nested Loops it would be common to save the rowid's plus the outer table data.

Appreciate your help and thanks for your efforts, time and explanation guru.

Regards
V.Hari
Tom Kyte
December 20, 2010 - 2:40 pm UTC

the deferral of the table access by index rowid till after some rowids were fetched in bulk was available in 10g.


Difference in cost when using USERENV var

Satyendra Singh, January 05, 2011 - 9:46 pm UTC

Hi Tom,
How these two queries explained so much differently.
I am very much confused here...
--1
SELECT
  line_id,
  line_number
FROM
  OE_ORDER_LINES_ALL OL,
  OE_ORDER_HEADERS_ALL OH
WHERE
  OH.HEADER_ID=OL.HEADER_ID
AND NVL(OL.ORG_ID,USERENV('CLIENT_INFO'))=104
AND NVL(OH.ORG_ID,USERENV('CLIENT_INFO'))=104;

--2
SELECT
  line_id,
  line_number
FROM
  OE_ORDER_LINES_ALL OL,
  OE_ORDER_HEADERS_ALL OH
WHERE
  OH.HEADER_ID=OL.HEADER_ID
AND NVL(OL.ORG_ID,104)=104
AND NVL(OH.ORG_ID,104)=104;

--From SQl*plus
SQL> exec dbms_application_info.set_client_info(104);

PL/SQL procedure successfully completed.

SQL> explain plan for SELECT
  2    line_id,
  3    line_number
  4  FROM
  5    OE_ORDER_LINES_ALL OL,
  6    OE_ORDER_HEADERS_ALL OH
  7  WHERE
  8    OH.HEADER_ID=OL.HEADER_ID
  9  AND NVL(OL.ORG_ID,USERENV('CLIENT_INFO'))=104
 10  AND NVL(OH.ORG_ID,USERENV('CLIENT_INFO'))=104;

Explained.

SQL> @?/rdbms/admin/utlxpls

Plan Table
----------------------------------------------------------------------------------------------------
| Operation                 |  Name              |  Rows | Bytes|  Cost  | Pstart| Pstop |
------------------------------------------------------------------------------------------
| SELECT STATEMENT          |                    |   593 |   16K|   4667 |       |       |
|  NESTED LOOPS             |                    |       |      |        |       |       |
|   NESTED LOOPS            |                    |   593 |   16K|   4667 |       |       |
|    TABLE ACCESS FULL      |OE_ORDER_HEADERS_AL |   502 |    4K|    775 |       |       |
|    INDEX RANGE SCAN       |OE_ORDER_LINES_N1   |    35 |      |      2 |       |       |
|   TABLE ACCESS BY INDEX RO|OE_ORDER_LINES_ALL  |     1 |   19 |      9 |       |       |
------------------------------------------------------------------------------------------

9 rows selected.

SQL> explain plan for SELECT
  2    line_id,
  3    line_number
  4  FROM
  5    OE_ORDER_LINES_ALL OL,
  6    OE_ORDER_HEADERS_ALL OH
  7  WHERE
  8    OH.HEADER_ID=OL.HEADER_ID
  9  AND NVL(OL.ORG_ID,104)=104
 10  AND NVL(OH.ORG_ID,104)=104;

Explained.

SQL> @?/rdbms/admin/utlxpls

Plan Table
----------------------------------------------------------------------------------------------------
| Operation                 |  Name              |  Rows | Bytes|  Cost  | Pstart| Pstop |
------------------------------------------------------------------------------------------
| SELECT STATEMENT          |                    |     1M|   38M|  63540 |       |       |
|  HASH JOIN                |                    |     1M|   38M|  63540 |       |       |
|   TABLE ACCESS FULL       |OE_ORDER_HEADERS_AL |    50K|  441K|    773 |       |       |
|   TABLE ACCESS FULL       |OE_ORDER_LINES_ALL  |     1M|   26M|  62759 |       |       |
------------------------------------------------------------------------------------------

7 rows selected.

Tom Kyte
January 06, 2011 - 7:21 am UTC

think about it, in the first case, the optimizer sees:

AND NVL(OL.ORG_ID,USERENV('CLIENT_INFO'))=104
AND NVL(OH.ORG_ID,USERENV('CLIENT_INFO'))=104;

which is basically the same as:

and nvl(ol.org_id,magic) = 104
and nvl(oh.org_id,magic) = 104

whereas in the second case, the optimizer sees the 104 in the NVL clearly - and can use that number to estimate cardinalities. Different estimated cardinalities = different plans.

awais

awais, January 05, 2012 - 1:15 am UTC

How access and filter predicates work togther within a same id from plan?

From document it says

ACCESS_PREDICATES
Predicates used to locate rows in an access structure. For example, start or stop predicates for an index range scan.

FILTER_PREDICATES
Predicates used to filter rows before producing them.

------------------------------------------------------------------------------
| Id  | Operation                             | Name                          |
------------------------------------------------------------------------------
|  10 |           NESTED LOOPS                |                               |
|  11 |            TABLE ACCESS BY INDEX ROWID| XXCS_DEPOT_SDS                |
|* 12 |             INDEX RANGE SCAN          | XX_DPT_SDS_ORGID              |
|* 13 |            TABLE ACCESS BY INDEX ROWID| MTL_SYSTEM_ITEMS_B            |
|* 14 |             INDEX RANGE SCAN          | MTL_SYSTEM_ITEMS_B_N1         |

12-access("ORG"."ORG_ID"=122)
13-filter("MTLB"."DESCRIPTION" NOT LIKE '%Sales Tax%')  
14-access("ORG"."ORGANIZATION_ID"="MTLB"."ORGANIZATION_ID")          filter(SUBSTR("MTLB"."SEGMENT1",1,2)=NVL(NULL,SUBSTR("MTLB"."SEGMENT1",1,2)))                                 

Step 1
-------
12 - access("ORG"."ORG_ID"=122)
At Id 12 it access the xxcs_depot_sds (table alias ORG) using an index XX_DPT_SDS_ORGID on column org_id with predicate orgid =122 and feed to id 11.id 11 is accessing the data of org_id=122 from table xxcs_depot_sds which is pass-thru operation.

Step 2
-------

14-access(ORG.ORGANIZATION_ID=MTLB.ORGANIZATION_ID)
filter(SUBSTR(MTLB.SEGMENT1,1,2)=NVL(NULL,SUBSTR(MTLB.SEGMENT1,1,2)))

At Id 14 it access the MTL_SYSTEM_ITEMS_B (table alias MTLB) using index MTL_SYSTEM_ITEMS_B_N1 and joined (using NL) {ORG.ORGANIZATION_ID=MTLB.ORGANIZATION_ID}

After joining it filters MTL_SYSTEM_ITEMS_B (table alias MTLB) by SUBSTR(MTLB.SEGMENT1,1,2)=NVL(NULL,SUBSTR(MTLB.SEGMENT1,1,2)) and then access the row from MTL_SYSTEM_ITEMS_B

Step 3
-------

At id 13 now it filters the rows %Sales Tax% which fetched from id 14.

Please correct my above understanding.


Tom Kyte
January 05, 2012 - 9:36 am UTC

at step 12, it reads the index XX_DPT_SDS_ORGID

this feeds into step 11 which will access the table (hence the name, access) XXCS_DEPOT_SDS

then we use the output of step 11 to read the index MTL_SYSTEM_ITEMS_B_N1 on step 14. We filter any of the index entries we find using the predicate on step 13.

we use the output of that to access table MTL_SYSTEM_ITEMS_B and once we've done that filter on the filter stuff from step 13


awais

Awais, January 05, 2012 - 12:09 pm UTC

but what about filter at step 14 SUBSTR(MTLB.SEGMENT1,1,2)=NVL(NULL,SUBSTR(MTLB.SEGMENT1,1,2)),you did not discuss about it.
Tom Kyte
January 05, 2012 - 1:03 pm UTC

then we use the output of step 11 to read the index MTL_SYSTEM_ITEMS_B_N1 on step 14. We filter any of the index entries we find using the predicate on step 13.

all of step 14 is done on the index. We 'access' the key of the index, we filter on the other columns we find in the index, then we'll go to the table.

Awais

Awais, January 07, 2012 - 1:45 pm UTC

Thanks a lot tom , you are an Oracle legend.

Awais

Awais, January 07, 2012 - 2:32 pm UTC

Please rephrase me in my own word if i understand you correctly , if not then please point out my misunderstanding.

At step 12 it access the xxcs_depot_sds (table alias ORG) using an index XX_DPT_SDS_ORGID on column org_id with predicate orgid =122 and feed to step 11.step 11 is accessing the data of org_id=122 from table xxcs_depot_sds which is pass-thru operation.

After that the output of step 11 is used to read the index MTL_SYSTEM_ITEMS_B_N1 at step 14 , it access the key of index then filter on that columns SEGMENT1.After that it feed its output to step 13 which in turn access the row data from table mtl_system_items_b , after fetching then it is filtered at step 13 by predicate MTLB.DESCRIPTION NOT LIKE '%Sales Tax%'.

In above case if the compound index is created on the columns {substr(segment1,1,2),description not like 'Sales Tax%} (regardless from here that like clause skip index) then the both filter occurs once at step 14.

Please correct me.
Tom Kyte
January 10, 2012 - 9:49 pm UTC

at step 12 it is using an index XX_DPT_SDS_ORGID to get rowids to ACCESS table XXCS_DEPOT_SDS, but it is not yet 'accessing it', it just has the rowids to access it.


step 11 is accessing the table, step 12 was getting the data used to access the table.


As for your 2nd to last paragraph, if the index has enough information to do the the filtering - then yes - we would filter at step 14 BEFORE going to the table, possibly avoiding going to the table.

you would not need a function based index for the filter at step 14, you would just need the columns to be present.

awais

Awais, January 11, 2012 - 12:18 am UTC

you would not need a function based index for the filter at step 14, you would just need the columns to be present.

why not FBI as FBI for substr(segment1,1,2) is being used in predicate, if i do not create FBI as substr(segment1,1,2) i.e create non FBI (segment1) then how come using predicate substr(segment1,1,2) will use index.
Tom Kyte
January 11, 2012 - 9:21 am UTC

I said you would not NEED, need is different from cannot have.


however, look at the predicate:

filter("MTLB"."DESCRIPTION" NOT LIKE '%Sales Tax%')  
14-access("ORG"."ORGANIZATION_ID"="MTLB"."ORGANIZATION_ID")          
filter(SUBSTR("MTLB"."SEGMENT1",1,2)=NVL(NULL,SUBSTR("MTLB"."SEGMENT1",1,2)))   



What function would you put in place to make

SUBSTR("MTLB"."SEGMENT1",1,2)=NVL(NULL,SUBSTR("MTLB"."SEGMENT1",1,2))

become an access? You have a column on both sides of the equality. It isn't going to work.

It does beg the question, why didn't you just code "and segment1 is not null"? that predicate is really:

SUBSTR("MTLB"."SEGMENT1",1,2)=SUBSTR("MTLB"."SEGMENT1",1,2)

which will be true of segment1 is not null... You would need to change the predicate to something like

where decode(segment1,null,1,0) = 0

and index the decode.

also, if you wanted

"MTLB"."DESCRIPTION" NOT LIKE '%Sales Tax%'

to be used as an access, not a filter, you'd have to do something like:

where instr( description, 'Sales Tax') > 0

and index the instr...

awais

awais, January 13, 2012 - 6:20 am UTC

thanks tom , you are great.

One more confusion tom.

<code>SQL> create index qp_pricing_attributes_mv_n1 on qp_pricing_attributes_mv
  2  (pricing_attribute_context,0,pricing_attribute,0)
  3  tablespace pzapex_index
  4  /

Index created. 

Now run the query again.

Select /*+ gather_plan_statistics mv_10.txt */  Rowid
  From Qp_Pricing_Attributes_mv
 Where Pricing_Attribute_Context Is Null
   And Pricing_Attribute Is Null

.
.
.
.

2228 rows selected in 0.75 seconds

SQL> select * from table(dbms_xplan.display_cursor('c7mv19pf6ttnq',NULL,'ALLSTATS LAST'))
  2  /

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------
SQL_ID  c7mv19pf6ttnq, child number 0
-------------------------------------
Select /*+ gather_plan_statistics mv_10.txt */  Rowid   From
Qp_Pricing_Attributes_mv  Where --Qppr.List_Line_Id = List_Line_Id
/*And*/ Pricing_Attribute_Context Is Null    And Pricing_Attribute Is
Null

Plan hash value: 2928018230

-----------------------------------------------------------------------------------
| Id  | Operation        | Name                        | Starts | E-Rows | A-Rows |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                             |      1 |        |   2228 |
|*  1 |  INDEX RANGE SCAN| QP_PRICING_ATTRIBUTES_MV_N1 |      1 |    362 |   2228 |
---------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("PRICING_ATTRIBUTE_CONTEXT" IS NULL AND "PRICING_ATTRIBUTE" IS NULL)
       filter("PRICING_ATTRIBUTE" IS NULL)

22 rows selected.

Now you can see that access and filter is occuring at only with in one step , my confusion is here when once accessed is occured at step 1 for fetching the row using predicate PRICING_ATTRIBUTE_CONTEXT IS NULL AND PRICING_ATTRIBUTE IS NULL) , then why filter is occuring PRICING_ATTRIBUTE IS NULL which has been already accessed with the same predicate.</code>

Not getting parent id

Ashish Kumar, April 10, 2012 - 2:10 am UTC

Hello,

When i execute below methods to get explain plan, I do not get parent id in second column.

set AUTOTRACE TRACEONLY explain
or
select * from table(dbms_xplan.display)

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 1632 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| FOLD_T | 12 | 1632 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------
I want parent id column also. can you help me with it.

Regards,
AShish

Tom Kyte
April 10, 2012 - 7:43 am UTC

here is the documentation for dbms_xplan and all of the options.

http://docs.oracle.com/cd/E14072_01/appdev.112/e10577/d_xplan.htm


If you want truly customized output to your specification, you can easily query v$sql_plan yourself and get whatever you want.

the indentation sort of shows the parent, I've never really seen the need for it.

how to read explain plan

A reader, April 10, 2012 - 9:59 am UTC

Hello,

I have seen the most common issue is the misinterpretation of the explain plan. Lot of people (particularly in application development team) are not aware of how to read a explain plan of their SQL statement and make some decisions.

I know a bit but I like to know in full.

Would you please explain or link me to a document that is already there?

Thanks,

Rajeshwaran Jeyabal, July 21, 2021 - 10:48 am UTC

Team,

could you help us to understand why "INTERNAL_FUNCTION" is getting applied on the IN-LIST predicates, while pulling the plan from cursor_cache but not during the explain plan command.

INTERNAL_FUNCTION - we see during "implicit" conversion right? so what kind of implicit conversion was going on here? dont see any.

demo@XEPDB1> set serveroutput off
demo@XEPDB1> select count(*)
  2  from emp
  3  where ename in ('KING','TOM','SCOTT')
  4  or sal > 1000;

  COUNT(*)
----------
        12

demo@XEPDB1> select * from table( dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
SQL_ID  ftnrc8jfbsjp3, child number 0
-------------------------------------
select count(*) from emp where ename in ('KING','TOM','SCOTT') or sal >
1000

Plan hash value: 2083865914

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     4 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    10 |            |          |
|*  2 |   TABLE ACCESS FULL| EMP  |    14 |   140 |     4   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter(("SAL">1000 OR INTERNAL_FUNCTION("ENAME")))


20 rows selected.

demo@XEPDB1> set autotrace traceonly exp
demo@XEPDB1> select count(*)
  2  from emp
  3  where ename in ('KING','TOM','SCOTT')
  4  or sal > 1000;

Execution Plan
----------------------------------------------------------
Plan hash value: 2083865914

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    10 |     4   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |    10 |            |          |
|*  2 |   TABLE ACCESS FULL| EMP  |    14 |   140 |     4   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("SAL">1000 OR "ENAME"='KING' OR "ENAME"='SCOTT' OR
              "ENAME"='TOM')

demo@XEPDB1> set autotrace off
demo@XEPDB1>

Connor McDonald
July 28, 2021 - 2:40 am UTC

There is a human-readable execution plan and a internal execution, ie, the list of machine operations the database will do to run your query.

When you EXPLAIN PLAN (or autotrace) you can think of the steps as being:

- present the query to the optimizer
- get the human readable plan

which eventually will then become the machine version of the plan ready to be executed.

When you do DISPLAY_CURSOR we are doing the opposite. We see the machine version of the plan, and we are trying to convert that *back* into a human readable version.

But this is not necessarily a 1-to-1 completely reversible process. The following examples are fiction, but should give you the idea.

Lets your predicate is: DATE_COL = trunc(sysdate)

That might map to some machine functions of:

field_check(DATE_COL,fnc_truncate(fnc_current_date_time))

So when we run DISPLAY_CURSOR we see "fnc_truncate(fnc_current_date_time)" and work backwards to say "That must have been trunc(sysdate)"

But (perhaps) with an INLIST the predicate:

ENAME in ('A','B','C') becomes

string_checker(ENAME,'A:B:C')

When we come to convert that back to human readable form, we can't be 100% positive that this was an IN-LIST predicate anymore.

Hope that makes sense.








INTERNAL_FUNCTION

Rajeshwaran Jeyabal, July 28, 2021 - 5:24 am UTC

thanks for the clarification of "INTERNAL_FUNCTION" during inlist, that helps.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.