| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Questions on PLAN_TABLE
Anon wrote:
> Hi, owning up I have been using Oracle for over 3 years as a
middleware
> developer regularly using an Oracle database and I have never really
> understood the Oracle Plan table. Despite much effort over the past
few
> days, I am still not sure I do and would like to ask the experts in
this
> newsgroup to clear something up for me.
>
> I have created a dummy script below to try to rationalise things to
myself.
> It produces two PLANs. For the purposes of discussion here I will
call them
> 1 and 2.
>
> In PLAN "1" there is the line " TABLE ACCESS (FULL) T1" for
the
> table (T1) that has the largest number of rows. To me this is the
bottleneck
> in processing the query because it has to process everything in T1
start to
> finish. T1 is by far the biggest table.
>
> In PLAN "2", a seemingly identical query but where the tables being
queried
> have been swapped, there is no full table scan and an index is being
used.
> Here the line reads "INDEX (FULL SCAN) IDXT3".
>
> Wouldn't query that produces PLAN "1" be more efficient if it used
the
> index? And if this is the case, why isn't it? And why is the query
that
> produces PLAN2 using an index when to me this would be a candidate
where a
> full table scan might be quicker than an index given there are only a
> handful or rows.
>
> Your comments would be most appreciated.
>
> Thank you
> Pete
>
> C:\SQLPLUS SCOTT/TIGER_at_TEST10G
>
> SQL*Plus: Release 9.0.1.4.0 - Production on Sat Feb 19 10:21:34 2005
>
> (c) Copyright 2001 Oracle Corporation. All rights reserved.
>
>
> Connected to:
> Personal Oracle Database 10g Release 10.1.0.2.0 - Production
> With the Partitioning, OLAP and Data Mining options
>
> SQL>
> SQL> CREATE TABLE t1(a NUMBER);
>
> Table created.
>
> SQL> CREATE TABLE t2(b NUMBER);
>
> Table created.
>
> SQL> CREATE TABLE t3(c NUMBER);
>
> Table created.
>
> SQL>
> SQL> DECLARE
> 2 BEGIN
> 3 DBMS_RANDOM.INITIALIZE(TO_NUMBER(TO_CHAR(SYSDATE,'ss')));
> 4
> 5 INSERT INTO t1
> 6 SELECT DBMS_RANDOM.RANDOM()
> 7 FROM DUAL
> 8 CONNECT BY ROWNUM<=1000000;
> 9
> 10 INSERT INTO t2
> 11 SELECT DBMS_RANDOM.RANDOM()
> 12 FROM DUAL
> 13 CONNECT BY ROWNUM<=100;
> 14
> 15 INSERT INTO t3
> 16 SELECT a
> 17 FROM t1
> 18 WHERE ROWNUM<=10;
> 19
> 20 INSERT INTO t3
> 21 SELECT b
> 22 FROM t2
> 23 WHERE ROWNUM<=10;
> 24
> 25
> 26 DBMS_RANDOM.TERMINATE;
> 27 END;
> 28 /
>
> PL/SQL procedure successfully completed.
>
> SQL>
> SQL> CREATE INDEX idxT1 ON t1(a);
>
> Index created.
>
> SQL> CREATE INDEX idxT2 ON t2(b);
>
> Index created.
>
> SQL> CREATE INDEX idxT3 ON t3(c);
>
> Index created.
>
> SQL>
> SQL>
> SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT');
>
> PL/SQL procedure successfully completed.
>
> SQL>
> SQL>
> SQL> TRUNCATE TABLE plan_table;
>
> Table truncated.
>
> SQL>
> SQL> EXPLAIN PLAN FOR
> 2 SELECT COUNT(*) FROM t1
> 3 WHERE a IN (SELECT b z FROM t2 UNION SELECT c z FROM t3);
>
> Explained.
>
> SQL>
> SQL> COLUMN "Operation" FORMAT A30
> SQL> COLUMN "Object" FORMAT A30
> SQL> SET LINESIZE 80
> SQL>
> SQL> SELECT SUBSTR(LPAD(' ',LEVEL-1) || operation || ' (' || options
||
> ')',1,30 ) "Operation",
> 2 object_name "Object"
> 3 FROM plan_table
> 4 START WITH id=0
> 5 CONNECT BY PRIOR id=parent_id;
>
> Operation Object
> ------------------------------ ------------------------------
> SELECT STATEMENT ()
> SORT (AGGREGATE)
> NESTED LOOPS ()
> TABLE ACCESS (FULL) T1
> VIEW () VW_NSO_1
> SORT (UNIQUE)
> UNION-ALL (PARTITION)
> INDEX (RANGE SCAN) IDXT2
> INDEX (RANGE SCAN) IDXT3
>
> 9 rows selected.
>
> SQL>
> SQL> TRUNCATE TABLE plan_table;
>
> Table truncated.
>
> SQL>
> SQL> EXPLAIN PLAN FOR
> 2 SELECT COUNT(*) FROM t3
> 3 WHERE c IN (SELECT a z FROM t1 UNION SELECT b z FROM t2);
>
> Explained.
>
> SQL>
> SQL> SELECT SUBSTR(LPAD(' ',LEVEL-1) || operation || ' (' || options
||
> ')',1,30 ) "Operation",
> 2 object_name "Object"
> 3 FROM plan_table
> 4 START WITH id=0
> 5 CONNECT BY PRIOR id=parent_id;
>
> Operation Object
> ------------------------------ ------------------------------
> SELECT STATEMENT ()
> SORT (AGGREGATE)
> NESTED LOOPS ()
> INDEX (FULL SCAN) IDXT3
> VIEW () VW_NSO_1
> SORT (UNIQUE)
> UNION-ALL (PARTITION)
> INDEX (RANGE SCAN) IDXT1
> INDEX (RANGE SCAN) IDXT2
>
> 9 rows selected.
>
> SQL>
> SQL>
Your plan_table query leaves a wealth of information behind. Rumnning
your example and using dbms_xplan.display to retrieve results from
PLAN_TABLE shows a different, more infornmed picture:
SQL> CREATE TABLE t1(a NUMBER);
Table created.
SQL> CREATE TABLE t2(b NUMBER);
Table created.
SQL> CREATE TABLE t3(c NUMBER);
Table created.
SQL> DECLARE
2 BEGIN
3 DBMS_RANDOM.INITIALIZE(TO_NUMBER(TO_CHAR(SYSDATE,'ss')));
4 INSERT INTO t1
5 SELECT DBMS_RANDOM.RANDOM()
6 FROM DUAL
7 CONNECT BY ROWNUM<=1000000;
8 INSERT INTO t2
9 SELECT DBMS_RANDOM.RANDOM()
10 FROM DUAL 11 CONNECT BY ROWNUM<=100;
14 FROM t1 15 WHERE ROWNUM<=10;
19 FROM t2 20 WHERE ROWNUM<=10;
PL/SQL procedure successfully completed.
SQL>
SQL> CREATE INDEX idxT1 ON t1(a) tablespace ets12_index;
Index created.
SQL> CREATE INDEX idxT2 ON t2(b) tablespace ets12_index;
Index created.
SQL> CREATE INDEX idxT3 ON t3(c) tablespace ets12_index;
Index created.
SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT'); PL/SQL procedure successfully completed.
SQL>
SQL> truncate table plan_table;
Table truncated.
SQL>
SQL> EXPLAIN PLAN FOR
2 SELECT COUNT(*) FROM t1
3 WHERE a IN (SELECT b z FROM t2 UNION SELECT c z FROM t3);
Explained.
SQL> SQL> SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 1 | 20 | 24 | | 1 | SORT AGGREGATE | | 1 | 20 | | |* 2 | HASH JOIN | | 120 | 2400 | 24 |
| 3 | VIEW | VW_NSO_1 | 120 | 1560 | 18 | | 4 | SORT UNIQUE | | 120 | 840 | 18 | | 5 | UNION-ALL | | | | | | 6 | TABLE ACCESS FULL| T2 | 100 | 700 | 3 |
PLAN_TABLE_OUTPUT
Predicate Information (identified by operation id):
2 - access("T1"."A"="VW_NSO_1"."Z")
Note: cpu costing is off
21 rows selected.
SQL>
SQL> truncate table plan_table;
Table truncated.
SQL>
SQL> EXPLAIN PLAN FOR
2 SELECT COUNT(*) FROM t3
3 WHERE c IN (SELECT a z FROM t1 UNION SELECT b z FROM t2);
Explained.
SQL>
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
| 0 | SELECT STATEMENT | | 1 | 20 | | 5010 |
| 1 | SORT AGGREGATE | | 1 | 20 | |
|
|* 2 | HASH JOIN | | 1000K| 19M| |
5010 |
| 3 | TABLE ACCESS FULL | T3 | 20 | 140 | |
3 |
| 4 | VIEW | VW_NSO_1 | 1000K| 12M| |
5005 |
| 5 | SORT UNIQUE | | 1000K| 6836K| 30M|
5005 |
| 6 | UNION-ALL | | | | |
|
PLAN_TABLE_OUTPUT
Predicate Information (identified by operation id):
2 - access("T3"."C"="VW_NSO_1"."Z")
Note: cpu costing is off
21 rows selected.
For some reason I have a different plan that you did, as my plan is using an index for t1 in the first query, where yours did not. Also, note the difference in the output; far more information is available using dbms_xplan.display, including the row counts and cost.
Altering the statistics slightly I ran the same explain plan statements again:
SQL>
SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'SCOTT',
method_opt=>'FOR ALL INDEXED COLUMNS', cascade=>TRUE);
PL/SQL procedure successfully completed.
SQL>
SQL> truncate table plan_table;
Table truncated.
SQL>
SQL> EXPLAIN PLAN FOR
2 SELECT COUNT(*) FROM t1
3 WHERE a IN (SELECT b z FROM t2 UNION SELECT c z FROM t3);
Explained.
SQL> SQL> SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
| Id | Operation | Name | Rows | Bytes | Cost |
| 0 | SELECT STATEMENT | | 1 | 20 | 258 | | 1 | SORT AGGREGATE | | 1 | 20 | | | 2 | NESTED LOOPS | | 120 | 2400 | 258 |
| 3 | VIEW | VW_NSO_1 | 120 | 1560 | 18 | | 4 | SORT UNIQUE | | 120 | 840 | 18 | | 5 | UNION-ALL | | | | | | 6 | TABLE ACCESS FULL| T2 | 100 | 700 | 3 |
PLAN_TABLE_OUTPUT
Predicate Information (identified by operation id):
8 - access("T1"."A"="VW_NSO_1"."Z")
Note: cpu costing is off
21 rows selected.
SQL>
SQL> truncate table plan_table;
Table truncated.
SQL>
SQL> EXPLAIN PLAN FOR
2 SELECT COUNT(*) FROM t3
3 WHERE c IN (SELECT a z FROM t1 UNION SELECT b z FROM t2);
Explained.
SQL>
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
| 0 | SELECT STATEMENT | | 1 | 20 | | 5005 |
| 1 | SORT AGGREGATE | | 1 | 20 | |
|
| 2 | NESTED LOOPS | | 1000K| 19M| |
5005 |
| 3 | VIEW | VW_NSO_1 | 1000K| 12M| |
5005 |
| 4 | SORT UNIQUE | | 1000K| 6836K| 30M|
5005 |
| 5 | UNION-ALL | | | | |
|
| 6 | TABLE ACCESS FULL| T1 | 1000K| 6835K| |
340 |
PLAN_TABLE_OUTPUT
|* 8 | INDEX RANGE SCAN | IDXT3 | 1 | 7 | |
|
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
8 - access("T3"."C"="VW_NSO_1"."Z")
Note: cpu costing is off
21 rows selected.
SQL>
SQL> spool off
Note the INDEX FAST FULL SCAN became an INDEX RANGE SCAN in the first query when statistics are also calculated on the indexes, not done by default with dbms_stats.gather_schema_stats, as CASCADE is set to FALSE. Note also there are no index scans in the second query, as should be expected based upon the subquery selecting all values from both tables.
I reallly can't answer why my plans use an index on table t1 in the first query and yours do not; possibly this is the difference between 9.2.0.6 and 10.1.0.2, as other than the recomputation of the statistics and using a separate index tablespace (which shouldn't have an affect on the plans) the example is exactly as you wrote it.
I hope this helps you understand the plan_table better.
David Fitzjarrell Received on Sat Feb 19 2005 - 09:43:22 CST
 |
 |