| 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
I'm running 10.1.0.3, which didn't reproduce your plans. But setting optimizer_features_enable to 10.1.0.2, I was able to get your first plan with the tablescan on T1 and indexed access nested loop into the partition view.
I think this is a bug. The 10053 trace showed that Oracle had given the indexed access path into the partition view a virtually zero cost, when it really should have given it a cost of 2 (or more)
Strangely, 10.1.0.3 did not notice the option to use an index at this point, and only considered the cost of a nested loop with "full tablescan" on the view - with a total cost of around 4,000,000.
In either version, a nested loop should be a bad idea at this point, because the next step would have to be performance 1,000,000 times. My default 10.1.0.3 actually switched order, and used a hash join.
I still haven't managed to emulate your second plan with its index (full) on T3 - so far my plan does do a table scan on T3. Since the difference in cost would be marginal in this case, it's probably something to do with a minor variation in other init.ora parameters.
-- Regards Jonathan Lewis http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/seminar.html Public Appearances - schedule updated Jan 21st 2005 "Anon" <anon_at_anon.com> wrote in message news:1108809689.31229.0_at_doris.uk.clara.net...Received on Sat Feb 19 2005 - 10:28:44 CST
> 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>
>
>
>
 |
 |