OraFAQ Forum: Performance Tuning » Why are these explain plans missing some info and messed up like they've been simultaneously written

Home » RDBMS Server » Performance Tuning » Why are these explain plans missing some info and messed up like they've been simultaneously written (Windows 7 Pro, x64bit)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Why are these explain plans missing some info and messed up like they've been simultaneously written [message #501415]

Tue, 29 March 2011 00:01

ilharco
Messages: 8
Registered: March 2011
Location: Portugal

Junior Member

Hi everyone,

I'm doing a TPCH-H academic benchmark and I'm now runiing explain plans for my queries before doing dbms_stats.
The problem is that, no matter how I get the explain plans, they are always (but not all queries) lacking some information about CPU cost, time , etc.

Here is the ouput for the 12 queries I have:

EXPLAIN PLAN FOR SELECT l_orderkey, l_linenumber, l_extendedprice FROM lineitem WHERE l_extendedprice < 1000; 

Explained.


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2037675383

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

| Id  | Operation		    | Name		    | Rows  | Bytes | Co
st (%CPU)| Time     |

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


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |			    | 22837 |	334K| 21
725   (1)| 00:04:21 |

|   1 |  TABLE ACCESS BY INDEX ROWID| LINEITEM		    | 22837 |	334K| 21
725   (1)| 00:04:21 |

|*  2 |   INDEX RANGE SCAN	    | L_EXTENDEDPRICE_INDEX | 22837 |	    |
 53   (0)| 00:00:01 |

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

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


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

   2 - access("L_EXTENDEDPRICE"<1000)

14 rows selected.

-------------------------------------------------------------------------------- 
EXPLAIN PLAN FOR SELECT l_orderkey, l_linenumber, l_extendedprice FROM lineitem WHERE l_extendedprice < 100000; 

Explained.


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 98068815

------------------------------------------------------------------------------
| Id  | Operation	  | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	     |	  22M|	 326M|	 120K  (1)| 00:24:07 |
|*  1 |  TABLE ACCESS FULL| LINEITEM |	  22M|	 326M|	 120K  (1)| 00:24:07 |
------------------------------------------------------------------------------

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

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

   1 - filter("L_EXTENDEDPRICE"<100000)

13 rows selected.

-------------------------------------------------------------------------------- 
EXPLAIN PLAN FOR select l_returnflag, l_linestatus, sum(l_quantity) as sum_qty, sum(l_extendedprice) as sum_base_price, sum(l_extendedprice * (1 - l_discount)) as sum_disc_price, sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge, avg(l_quantity) as avg_qty, avg(l_extendedprice) as avg_price, avg(l_discount) as avg_disc, count(*) as count_order from lineitem where l_shipdate <= date '1998-12-01' - interval '90' day group by l_returnflag, l_linestatus order by l_returnflag, l_linestatus; 

Explained.


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 119192358

-------------------------------------------------------------------------------
| Id  | Operation	   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	      |     5 |   135 |   122K	(3)| 00:24:30 |
|   1 |  SORT GROUP BY	   |	      |     5 |   135 |   122K	(3)| 00:24:30 |
|*  2 |   TABLE ACCESS FULL| LINEITEM |    23M|   596M|   120K	(1)| 00:24:09 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

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

   2 - filter("L_SHIPDATE"<=TO_DATE(' 1998-09-02 00:00:00',
	      'syyyy-mm-dd hh24:mi:ss'))

15 rows selected.

-------------------------------------------------------------------------------- 
EXPLAIN PLAN FOR select s_acctbal, s_name, n_name, p_partkey, p_mfgr, s_address, s_phone, s_comment from part, supplier, partsupp, nation, region where rownum < 101 and p_partkey = ps_partkey and s_suppkey = ps_suppkey and p_size = 15 and p_type like '%BRASS' and s_nationkey = n_nationkey and n_regionkey = r_regionkey and r_name = 'EUROPE' and ps_supplycost = (select min(ps_supplycost) from partsupp, supplier, nation, region where p_partkey = ps_partkey and s_suppkey = ps_suppkey and s_nationkey = n_nationkey and n_regionkey = r_regionkey and r_name = 'EUROPE') order by s_acctbal desc, n_name, s_name, p_partkey; 

Explained.


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2365986010

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

| Id  | Operation			    | Name	   | Rows  | Bytes | Cos
t (%CPU)| Time	   |

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


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		    |		   |   100 | 21000 |  67
93   (1)| 00:01:22 |

|   1 |  SORT ORDER BY			    |		   |   100 | 21000 |  67
93   (1)| 00:01:22 |

|*  2 |   COUNT STOPKEY 		    |		   |	   |	   |
	|	   |

|*  3 |    VIEW 			    | VW_WIF_1	   |   633 |   129K|  67
92   (1)| 00:01:22 |

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

|   4 |     WINDOW SORT 		    |		   |   633 |   178K|  67
92   (1)| 00:01:22 |

|*  5 |      HASH JOIN			    |		   |   633 |   178K|  67
91   (1)| 00:01:22 |

|*  6 |       TABLE ACCESS FULL 	    | REGION	   |	 1 |	29 |
 3   (0)| 00:00:01 |

|*  7 |       HASH JOIN 		    |		   |  3163 |   803K|  67

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
88   (1)| 00:01:22 |

|   8 |        TABLE ACCESS FULL	    | NATION	   |	25 |   800 |
 3   (0)| 00:00:01 |

|*  9 |        HASH JOIN		    |		   |  3163 |   704K|  67
84   (1)| 00:01:22 |

|  10 | 	NESTED LOOPS		    |		   |	   |	   |
	|	   |


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|  11 | 	 NESTED LOOPS		    |		   |  3190 |   258K|  65
44   (1)| 00:01:19 |

|* 12 | 	  TABLE ACCESS FULL	    | PART	   |   800 | 44800 |  41
43   (1)| 00:00:50 |

|* 13 | 	  INDEX RANGE SCAN	    | SYS_C0013827 |	 4 |	   |
 2   (0)| 00:00:01 |

|  14 | 	 TABLE ACCESS BY INDEX ROWID| PARTSUPP	   |	 4 |   108 |
 3   (0)| 00:00:01 |

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

|  15 | 	TABLE ACCESS FULL	    | SUPPLIER	   | 40000 |  5664K|   2
39   (1)| 00:00:03 |

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


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


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   2 - filter(ROWNUM<101)
   3 - filter("VW_COL_9" IS NOT NULL)
   5 - access("N_REGIONKEY"="R_REGIONKEY")
   6 - filter("R_NAME"='EUROPE')
   7 - access("S_NATIONKEY"="N_NATIONKEY")
   9 - access("S_SUPPKEY"="PS_SUPPKEY")
  12 - filter("P_SIZE"=15 AND "P_TYPE" LIKE '%BRASS')
  13 - access("P_PARTKEY"="PS_PARTKEY")

34 rows selected.

-------------------------------------------------------------------------------- 
EXPLAIN PLAN FOR select l_orderkey, sum(l_extendedprice * (1 - l_discount)) as revenue, o_orderdate, o_shippriority from customer, orders, lineitem where rownum < 11 and c_mktsegment = 'BUILDING' and c_custkey = o_custkey and l_orderkey = o_orderkey and o_orderdate < date '1995-03-15' and l_shipdate > date '1995-03-15' group by l_orderkey, o_orderdate, o_shippriority order by revenue desc, o_orderdate; 

Explained.


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1509253376

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

| Id  | Operation		    | Name		 | Rows  | Bytes |TempSp
c| Cost (%CPU)| Time	 |

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


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |			 |    10 |   600 |
 |   233K  (1)| 00:46:43 |

|   1 |  SORT ORDER BY		    |			 |    10 |   600 |   132
M|   233K  (1)| 00:46:43 |

|   2 |   HASH GROUP BY 	    |			 |    10 |   600 |   132
M|   233K  (1)| 00:46:43 |

|*  3 |    COUNT STOPKEY	    |			 |	 |	 |
 |	      | 	 |

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

|*  4 |     HASH JOIN		    |			 |  1921K|   109M|    40
M|   178K  (1)| 00:35:37 |

|*  5 |      HASH JOIN		    |			 |   865K|    30M|  3288
K| 33820   (1)| 00:06:46 |

|*  6 |       VIEW		    | index$_join$_001	 |   120K|  1875K|
 |  2601   (1)| 00:00:32 |

|*  7 |        HASH JOIN	    |			 |	 |	 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
 |	      | 	 |

|*  8 | 	INDEX RANGE SCAN    | C_MKTSEGMENT_INDEX |   120K|  1875K|
 |   374   (1)| 00:00:05 |

|   9 | 	INDEX FAST FULL SCAN| SYS_C0013824	 |   120K|  1875K|
 |  1579   (1)| 00:00:19 |

|* 10 |       TABLE ACCESS FULL     | ORDERS		 |  2916K|    58M|
 | 26465   (1)| 00:05:18 |


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 11 |      TABLE ACCESS FULL	    | LINEITEM		 |    12M|   282M|
 |   120K  (1)| 00:24:09 |

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


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

   3 - filter(ROWNUM<11)

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   4 - access("L_ORDERKEY"="O_ORDERKEY")
   5 - access("C_CUSTKEY"="O_CUSTKEY")
   6 - filter("C_MKTSEGMENT"='BUILDING')
   7 - access(ROWID=ROWID)
   8 - access("C_MKTSEGMENT"='BUILDING')
  10 - filter("O_ORDERDATE"<TO_DATE(' 1995-03-15 00:00:00', 'syyyy-mm-dd hh24:mi
:ss'))

  11 - filter("L_SHIPDATE">TO_DATE(' 1995-03-15 00:00:00', 'syyyy-mm-dd hh24:mi:
ss'))


30 rows selected.

-------------------------------------------------------------------------------- 
EXPLAIN PLAN FOR select o_orderpriority, count(*) as order_count from orders where o_orderdate >= date '1993-07-01' and o_orderdate < date '1993-07-01' + interval '3' month and exists (select * from lineitem where l_orderkey = o_orderkey and l_commitdate < l_receiptdate) group by o_orderpriority order by o_orderpriority; 

Explained.


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 168290725

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

| Id  | Operation	    | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Ti
me     |

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


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |	       |     5 |   260 |       |   167K  (1)| 00
:33:30 |

|   1 |  SORT GROUP BY	    |	       |     5 |   260 |       |   167K  (1)| 00
:33:30 |

|*  2 |   HASH JOIN SEMI    |	       |   232K|    11M|  9520K|   167K  (1)| 00
:33:30 |

|*  3 |    TABLE ACCESS FULL| ORDERS   |   232K|  6797K|       | 26459	 (1)| 00
:05:18 |

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

|*  4 |    TABLE ACCESS FULL| LINEITEM |    12M|   255M|       |   120K  (1)| 00
:24:10 |

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


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


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   2 - access("L_ORDERKEY"="O_ORDERKEY")
   3 - filter("O_ORDERDATE"<TO_DATE(' 1993-10-01 00:00:00', 'syyyy-mm-dd
	      hh24:mi:ss') AND "O_ORDERDATE">=TO_DATE(' 1993-07-01 00:00:00', 's
yyyy-mm-dd

	      hh24:mi:ss'))
   4 - filter("L_COMMITDATE"<"L_RECEIPTDATE")

20 rows selected.

-------------------------------------------------------------------------------- 
EXPLAIN PLAN FOR select n_name, sum(l_extendedprice * (1 - l_discount)) as revenue from customer, orders, lineitem, supplier, nation, region where c_custkey = o_custkey and l_orderkey = o_orderkey and l_suppkey = s_suppkey and c_nationkey = s_nationkey and s_nationkey = n_nationkey and n_regionkey = r_regionkey and r_name = 'ASIA' and o_orderdate >= date '1994-01-01' and o_orderdate < date '1994-01-01' + interval '1' year group by n_name order by revenue desc; 

Explained.


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3444527134

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

| Id  | Operation			   | Name	       | Rows  | Bytes |
TempSpc| Cost (%CPU)| Time     |

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


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		   |		       |    25 |  2900 |
       |   179K  (1)| 00:35:59 |

|   1 |  SORT ORDER BY			   |		       |    25 |  2900 |
       |   179K  (1)| 00:35:59 |

|   2 |   HASH GROUP BY 		   |		       |    25 |  2900 |
       |   179K  (1)| 00:35:59 |

|*  3 |    HASH JOIN			   |		       | 28496 |  3228K|
    11M|   179K  (1)| 00:35:59 |

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

|   4 |     TABLE ACCESS FULL		   | CUSTOMER	       |   600K|  4687K|
       |  3857	 (1)| 00:00:47 |

|*  5 |     HASH JOIN			   |		       |   712K|    73M|
    27M|   171K  (1)| 00:34:18 |

|*  6 |      TABLE ACCESS FULL		   | ORDERS	       |   913K|    16M|
       | 26463	 (1)| 00:05:18 |

|*  7 |      HASH JOIN			   |		       |  4757K|   403M|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
       |   120K  (1)| 00:24:10 |

|*  8 |       HASH JOIN 		   |		       |  8000 |   539K|
       |   170	 (2)| 00:00:03 |

|   9 |        NESTED LOOPS		   |		       |       |       |
       |	    |	       |

|  10 | 	NESTED LOOPS		   |		       |     5 |   305 |
       |     4	 (0)| 00:00:01 |


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 11 | 	 TABLE ACCESS FULL	   | REGION	       |     1 |    29 |
       |     3	 (0)| 00:00:01 |

|* 12 | 	 INDEX RANGE SCAN	   | N_REGIONKEY_INDEX |     5 |       |
       |     0	 (0)| 00:00:01 |

|  13 | 	TABLE ACCESS BY INDEX ROWID| NATION	       |     5 |   160 |
       |     1	 (0)| 00:00:01 |

|  14 |        VIEW			   | index$_join$_004  | 40000 |   312K|
       |   165	 (2)| 00:00:02 |

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

|* 15 | 	HASH JOIN		   |		       |       |       |
       |	    |	       |

|  16 | 	 INDEX FAST FULL SCAN	   | S_NATIONKEY_INDEX | 40000 |   312K|
       |   100	 (1)| 00:00:02 |

|  17 | 	 INDEX FAST FULL SCAN	   | SYS_C0013825      | 40000 |   312K|
       |   106	 (1)| 00:00:02 |

|  18 |       TABLE ACCESS FULL 	   | LINEITEM	       |    23M|   457M|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
       |   120K  (1)| 00:24:06 |

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


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

   3 - access("C_CUSTKEY"="O_CUSTKEY" AND "C_NATIONKEY"="S_NATIONKEY")
   5 - access("L_ORDERKEY"="O_ORDERKEY")

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   6 - filter("O_ORDERDATE"<TO_DATE(' 1995-01-01 00:00:00', 'syyyy-mm-dd hh24:mi
:ss') AND

	      "O_ORDERDATE">=TO_DATE(' 1994-01-01 00:00:00', 'syyyy-mm-dd hh24:m
i:ss'))

   7 - access("L_SUPPKEY"="S_SUPPKEY")
   8 - access("S_NATIONKEY"="N_NATIONKEY")
  11 - filter("R_NAME"='ASIA')
  12 - access("N_REGIONKEY"="R_REGIONKEY")
  15 - access(ROWID=ROWID)

38 rows selected.

-------------------------------------------------------------------------------- 
EXPLAIN PLAN FOR select sum(l_extendedprice * l_discount) as revenue from lineitem where l_shipdate >= date '1994-01-01' and l_shipdate < date '1994-01-01' + interval '1' year and l_discount between .06 - 0.01 and .06 + 0.01 and l_quantity < 24; 

Explained.


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2287326370

-------------------------------------------------------------------------------
| Id  | Operation	   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	      |     1 |    20 |   120K	(1)| 00:24:10 |
|   1 |  SORT AGGREGATE    |	      |     1 |    20 | 	   |	      |
|*  2 |   TABLE ACCESS FULL| LINEITEM |   623K|    11M|   120K	(1)| 00:24:10 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

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

   2 - filter("L_SHIPDATE"<TO_DATE(' 1995-01-01 00:00:00', 'syyyy-mm-dd
	      hh24:mi:ss') AND "L_QUANTITY"<24 AND "L_DISCOUNT">=.05 AND
	      "L_SHIPDATE">=TO_DATE(' 1994-01-01 00:00:00', 'syyyy-mm-dd hh24:mi
:ss')

	      AND "L_DISCOUNT"<=.07)

17 rows selected.

-------------------------------------------------------------------------------- 
EXPLAIN PLAN FOR select supp_nation, cust_nation, l_year, sum(volume) as revenue from (select n1.n_name as supp_nation, n2.n_name as cust_nation, extract(year from l_shipdate) as l_year, l_extendedprice * (1 - l_discount) as volume from supplier, lineitem, orders, customer, nation n1, nation n2 where s_suppkey = l_suppkey and o_orderkey = l_orderkey and c_custkey = o_custkey and s_nationkey = n1.n_nationkey and c_nationkey = n2.n_nationkey and ((n1.n_name = 'FRANCE' and n2.n_name = 'GERMANY') or (n1.n_name = 'GERMANY' and n2.n_name = 'FRANCE')) and l_shipdate between date '1995-01-01' and date '1996-12-31') shipping group by supp_nation, cust_nation, l_year order by supp_nation, cust_nation, l_year; 

Explained.


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 33325609

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

| Id  | Operation		    | Name		| Rows	| Bytes |TempSpc
| Cost (%CPU)| Time	|

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


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |			|  1465 |   161K|
|   164K  (1)| 00:32:52 |

|   1 |  SORT GROUP BY		    |			|  1465 |   161K|
|   164K  (1)| 00:32:52 |

|*  2 |   HASH JOIN		    |			| 22052 |  2433K|    11M
|   164K  (1)| 00:32:51 |

|   3 |    TABLE ACCESS FULL	    | CUSTOMER		|   600K|  4687K|
|  3857   (1)| 00:00:47 |

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

|*  4 |    HASH JOIN		    |			|   551K|    55M|    55M
|   156K  (1)| 00:31:22 |

|*  5 |     HASH JOIN		    |			|   551K|    49M|
|   120K  (1)| 00:24:12 |

|*  6 |      HASH JOIN		    |			|  3197 |   206K|
|   173   (2)| 00:00:03 |

|   7 |       NESTED LOOPS	    |			|     2 |   116 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|     7   (0)| 00:00:01 |

|*  8 |        TABLE ACCESS FULL    | NATION		|     2 |    58 |
|     3   (0)| 00:00:01 |

|*  9 |        TABLE ACCESS FULL    | NATION		|     1 |    29 |
|     2   (0)| 00:00:01 |

|  10 |       VIEW		    | index$_join$_002	| 40000 |   312K|
|   165   (2)| 00:00:02 |


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 11 |        HASH JOIN	    |			|	|	|
|	     |		|

|  12 | 	INDEX FAST FULL SCAN| S_NATIONKEY_INDEX | 40000 |   312K|
|   100   (1)| 00:00:02 |

|  13 | 	INDEX FAST FULL SCAN| SYS_C0013825	| 40000 |   312K|
|   106   (1)| 00:00:02 |

|* 14 |      TABLE ACCESS FULL	    | LINEITEM		|  6956K|   185M|
|   120K  (1)| 00:24:09 |

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

|  15 |     TABLE ACCESS FULL	    | ORDERS		|  6000K|    62M|
| 26406   (1)| 00:05:17 |

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


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


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   2 - access("C_CUSTKEY"="O_CUSTKEY" AND "C_NATIONKEY"="N2"."N_NATIONKEY")
   4 - access("O_ORDERKEY"="L_ORDERKEY")
   5 - access("S_SUPPKEY"="L_SUPPKEY")
   6 - access("S_NATIONKEY"="N1"."N_NATIONKEY")
   8 - filter("N1"."N_NAME"='FRANCE' OR "N1"."N_NAME"='GERMANY')
   9 - filter(("N2"."N_NAME"='FRANCE' OR "N2"."N_NAME"='GERMANY') AND ("N1"."N_N
AME"='FRANCE'

	      AND "N2"."N_NAME"='GERMANY' OR "N1"."N_NAME"='GERMANY' AND "N2"."N
_NAME"='FRANCE'))


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
  11 - access(ROWID=ROWID)
  14 - filter("L_SHIPDATE">=TO_DATE(' 1995-01-01 00:00:00', 'syyyy-mm-dd hh24:mi
:ss') AND

	      "L_SHIPDATE"<=TO_DATE(' 1996-12-31 00:00:00', 'syyyy-mm-dd hh24:mi
:ss'))


36 rows selected.

-------------------------------------------------------------------------------- 
EXPLAIN PLAN FOR select o_year, sum(case when nation = 'BRAZIL' then volume else 0 end) / sum(volume) as mkt_share from (select extract(year from o_orderdate) as o_year, l_extendedprice * (1 - l_discount) as volume, n2.n_name as nation from part, supplier, lineitem, orders, customer, nation n1, nation n2, region where p_partkey = l_partkey and s_suppkey = l_suppkey and l_orderkey = o_orderkey and o_custkey = c_custkey and c_nationkey = n1.n_nationkey and n1.n_regionkey = r_regionkey and r_name = 'AMERICA' and s_nationkey = n2.n_nationkey and o_orderdate between date '1995-01-01' and date '1996-12-31' and p_type = 'ECONOMY ANODIZED STEEL') all_nations group by o_year order by o_year; 

Explained.


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 548146875

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

| Id  | Operation		       | Name		   | Rows  | Bytes |Temp
Spc| Cost (%CPU)| Time	   |

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


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	       |		   |   732 |   107K|
   |   156K  (1)| 00:31:22 |

|   1 |  SORT GROUP BY		       |		   |   732 |   107K|
   |   156K  (1)| 00:31:22 |

|*  2 |   HASH JOIN		       |		   |  9469 |  1396K|
   |   156K  (1)| 00:31:22 |

|   3 |    TABLE ACCESS FULL	       | NATION 	   |	25 |   725 |
   |	 3   (0)| 00:00:01 |

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

|*  4 |    HASH JOIN		       |		   |  9469 |  1128K|
   |   156K  (1)| 00:31:22 |

|   5 |     VIEW		       | index$_join$_003  | 40000 |   312K|
   |   165   (2)| 00:00:02 |

|*  6 |      HASH JOIN		       |		   |	   |	   |
   |		|	   |

|   7 |       INDEX FAST FULL SCAN     | S_NATIONKEY_INDEX | 40000 |   312K|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   |   100   (1)| 00:00:02 |

|   8 |       INDEX FAST FULL SCAN     | SYS_C0013825	   | 40000 |   312K|
   |   106   (1)| 00:00:02 |

|*  9 |     HASH JOIN		       |		   |  9551 |  1063K|
   |   156K  (1)| 00:31:20 |

|* 10 |      TABLE ACCESS FULL	       | REGION 	   |	 1 |	29 |
   |	 3   (0)| 00:00:01 |


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 11 |      HASH JOIN		       |		   | 47757 |  3964K|
   |   156K  (1)| 00:31:20 |

|  12 |       VIEW		       | index$_join$_007  |	25 |   150 |
   |	 3  (34)| 00:00:01 |

|* 13 |        HASH JOIN	       |		   |	   |	   |
   |		|	   |

|  14 | 	INDEX FAST FULL SCAN   | N_REGIONKEY_INDEX |	25 |   150 |
   |	 1   (0)| 00:00:01 |

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

|  15 | 	INDEX FAST FULL SCAN   | SYS_C0013823	   |	25 |   150 |
   |	 1   (0)| 00:00:01 |

|* 16 |       HASH JOIN 	       |		   | 47757 |  3684K|  38
72K|   156K  (1)| 00:31:20 |

|* 17 |        HASH JOIN	       |		   | 47757 |  3311K|  99
68K|   152K  (1)| 00:30:25 |

|* 18 | 	HASH JOIN	       |		   |   159K|  8098K|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   |   122K  (1)| 00:24:29 |

|* 19 | 	 VIEW		       | index$_join$_002  |  5333 |   140K|
   |  1716   (2)| 00:00:21 |

|* 20 | 	  HASH JOIN	       |		   |	   |	   |
   |		|	   |

|* 21 | 	   INDEX RANGE SCAN    | P_TYPE_INDEX	   |  5333 |   140K|
   |	27   (0)| 00:00:01 |


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|  22 | 	   INDEX FAST FULL SCAN| SYS_C0013826	   |  5333 |   140K|
   |  2104   (1)| 00:00:26 |

|  23 | 	 TABLE ACCESS FULL     | LINEITEM	   |	23M|   572M|
   |   120K  (1)| 00:24:06 |

|* 24 | 	TABLE ACCESS FULL      | ORDERS 	   |  1826K|	33M|
   | 26464   (1)| 00:05:18 |

|  25 |        TABLE ACCESS FULL       | CUSTOMER	   |   600K|  4687K|
   |  3857   (1)| 00:00:47 |

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

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


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

   2 - access("S_NATIONKEY"="N2"."N_NATIONKEY")
   4 - access("S_SUPPKEY"="L_SUPPKEY")
   6 - access(ROWID=ROWID)

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   9 - access("N1"."N_REGIONKEY"="R_REGIONKEY")
  10 - filter("R_NAME"='AMERICA')
  11 - access("C_NATIONKEY"="N1"."N_NATIONKEY")
  13 - access(ROWID=ROWID)
  16 - access("O_CUSTKEY"="C_CUSTKEY")
  17 - access("L_ORDERKEY"="O_ORDERKEY")
  18 - access("P_PARTKEY"="L_PARTKEY")
  19 - filter("P_TYPE"='ECONOMY ANODIZED STEEL')
  20 - access(ROWID=ROWID)
  21 - access("P_TYPE"='ECONOMY ANODIZED STEEL')
  24 - filter("O_ORDERDATE">=TO_DATE(' 1995-01-01 00:00:00', 'syyyy-mm-dd hh24:m

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
i:ss') AND

	      "O_ORDERDATE"<=TO_DATE(' 1996-12-31 00:00:00', 'syyyy-mm-dd hh24:m
i:ss'))


51 rows selected.

-------------------------------------------------------------------------------- 
EXPLAIN PLAN FOR select nation, o_year, sum(amount) as sum_profit from (select n_name as nation, extract(year from o_orderdate) as o_year, l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount from part, supplier, lineitem, partsupp, orders, nation where s_suppkey = l_suppkey and ps_suppkey = l_suppkey and ps_partkey = l_partkey and p_partkey = l_partkey and o_orderkey = l_orderkey and s_nationkey = n_nationkey and p_name like '%green%') profit group by nation, o_year order by nation, o_year desc; 

Explained.


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 596691277

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

| Id  | Operation		  | Name	      | Rows  | Bytes |TempSpc|
Cost (%CPU)| Time     |

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


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	  |		      | 42533 |  5565K|       |
  249K	(1)| 00:49:57 |

|   1 |  SORT GROUP BY		  |		      | 42533 |  5565K|   163M|
  249K	(1)| 00:49:57 |

|*  2 |   HASH JOIN		  |		      |  1168K|   149M|       |
  236K	(1)| 00:47:19 |

|   3 |    TABLE ACCESS FULL	  | NATION	      |    25 |   725 |       |
    3	(0)| 00:00:01 |

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

|*  4 |    HASH JOIN		  |		      |  1168K|   117M|       |
  236K	(1)| 00:47:19 |

|   5 |     VIEW		  | index$_join$_003  | 40000 |   312K|       |
  165	(2)| 00:00:02 |

|*  6 |      HASH JOIN		  |		      |       |       |       |
	   |	      |

|   7 |       INDEX FAST FULL SCAN| S_NATIONKEY_INDEX | 40000 |   312K|       |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
  100	(1)| 00:00:02 |

|   8 |       INDEX FAST FULL SCAN| SYS_C0013825      | 40000 |   312K|       |
  106	(1)| 00:00:02 |

|*  9 |     HASH JOIN		  |		      |  1178K|   109M|    82M|
  236K	(1)| 00:47:17 |

|  10 |      TABLE ACCESS FULL	  | PARTSUPP	      |  3200K|    45M|       |
18114	(1)| 00:03:38 |


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 11 |      HASH JOIN		  |		      |  1176K|    92M|    91M|
  208K	(1)| 00:41:47 |

|* 12 |       HASH JOIN 	  |		      |  1196K|    77M|  2032K|
  170K	(1)| 00:34:06 |

|* 13 |        TABLE ACCESS FULL  | PART	      | 40000 |  1562K|       |
 4141	(1)| 00:00:50 |

|  14 |        TABLE ACCESS FULL  | LINEITEM	      |    23M|   640M|       |
  120K	(1)| 00:24:06 |

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

|  15 |       TABLE ACCESS FULL   | ORDERS	      |  6000K|    80M|       |
26432	(1)| 00:05:18 |

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


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


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   2 - access("S_NATIONKEY"="N_NATIONKEY")
   4 - access("S_SUPPKEY"="L_SUPPKEY")
   6 - access(ROWID=ROWID)
   9 - access("PS_SUPPKEY"="L_SUPPKEY" AND "PS_PARTKEY"="L_PARTKEY")
  11 - access("O_ORDERKEY"="L_ORDERKEY")
  12 - access("P_PARTKEY"="L_PARTKEY")
  13 - filter("P_NAME" LIKE '%green%')

33 rows selected.

-------------------------------------------------------------------------------- 
EXPLAIN PLAN FOR select c_custkey, c_name, sum(l_extendedprice * (1 - l_discount)) as revenue, c_acctbal, n_name, c_address, c_phone, c_comment from customer, orders, lineitem, nation where rownum < 21 and c_custkey = o_custkey and l_orderkey = o_orderkey and o_orderdate >= date '1993-10-01' and o_orderdate < date '1993-10-01' + interval '3' month and l_returnflag = 'R' and c_nationkey = n_nationkey group by c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment order by revenue desc; 

Explained.


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 682210806

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

| Id  | Operation		| Name	   | Rows  | Bytes |TempSpc| Cost (%CPU)
| Time	   |

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


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	|	   |	20 |  4300 |	   |   203K  (1)
| 00:40:47 |

|   1 |  SORT ORDER BY		|	   |	20 |  4300 |	82M|   203K  (1)
| 00:40:47 |

|   2 |   HASH GROUP BY 	|	   |	20 |  4300 |	82M|   203K  (1)
| 00:40:47 |

|*  3 |    COUNT STOPKEY	|	   |	   |	   |	   |
|	   |

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

|*  4 |     HASH JOIN		|	   |   381K|	78M|	   |   168K  (1)
| 00:33:39 |

|   5 |      TABLE ACCESS FULL	| NATION   |	25 |   725 |	   |	 3   (0)
| 00:00:01 |

|*  6 |      HASH JOIN		|	   |   381K|	67M|	17M|   168K  (1)
| 00:33:39 |

|*  7 |       HASH JOIN 	|	   |   381K|	13M|  7024K|   158K  (1)

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 00:31:47 |

|*  8 |        TABLE ACCESS FULL| ORDERS   |   232K|  4304K|	   | 26459   (1)
| 00:05:18 |

|*  9 |        TABLE ACCESS FULL| LINEITEM |  7998K|   129M|	   |   120K  (1)
| 00:24:12 |

|  10 |       TABLE ACCESS FULL | CUSTOMER |   600K|	85M|	   |  3860   (1)
| 00:00:47 |


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


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

   3 - filter(ROWNUM<21)
   4 - access("C_NATIONKEY"="N_NATIONKEY")
   6 - access("C_CUSTKEY"="O_CUSTKEY")
   7 - access("L_ORDERKEY"="O_ORDERKEY")

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   8 - filter("O_ORDERDATE"<TO_DATE(' 1994-01-01 00:00:00', 'syyyy-mm-dd
	      hh24:mi:ss') AND "O_ORDERDATE">=TO_DATE(' 1993-10-01 00:00:00', 's
yyyy-mm-dd

	      hh24:mi:ss'))
   9 - filter("L_RETURNFLAG"='R')

29 rows selected.

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

So, the info

| Id  | Operation		    | Name		    | Rows  | Bytes | Co
st (%CPU)| Time     |

is missing in some plans.

Is this normal? If not, what's the problem and how can I handle it?

Please, I'm freaking out!

Report message to a moderator

Re: Why are these explain plans missing some info and messed up like they've been simultaneously wri [message #501419 is a reply to message #501415]

Tue, 29 March 2011 01:06

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

I don't know you but me I am unable to read lines that I have to horizontally scroll 8 times
and I am unable to read plans with fold lines and break into 4 lines groups.

Repost in a readable way. Remove what is useless.
I don't understand that someone is able to post such a crap.

Regards
Michel

[Updated on: Tue, 29 March 2011 01:07]

Report message to a moderator

Re: Why are these explain plans missing some info and messed up like they've been simultaneously wri [message #501427 is a reply to message #501415]

Tue, 29 March 2011 02:12

flyboy
Messages: 1903
Registered: November 2006

Senior Member

ilharco wrote on Tue, 29 March 2011 07:01

So, the info
| Id  | Operation		    | Name		    | Rows  | Bytes | Co
st (%CPU)| Time     |
is missing in some plans.

How did you find this? In fact, there are 12 EXPLAIN PLAN statements followed by 12 sections starting with

| Id  |

. Sometimes, 'Operation' column length differs, sometimes 'TempSpc' column is present.
Just curious, which tool did you use to get this?

Report message to a moderator

Re: Why are these explain plans missing some info and messed up like they've been simultaneously wri [message #501502 is a reply to message #501415]

Tue, 29 March 2011 08:26

LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL

Senior Member

Perhaps if you change the following sqlplus settings before generating the PLAN_TABLE report, it will help:

set lin 130 pages 999 trims on

[Updated on: Tue, 29 March 2011 10:31] by Moderator

Report message to a moderator

Re: Why are these explain plans missing some info and messed up like they've been simultaneously wri [message #501526 is a reply to message #501502]

Tue, 29 March 2011 12:47

ilharco
Messages: 8
Registered: March 2011
Location: Portugal

Junior Member

Those settings do solve my problem if I do it on sqlplus console.

But if I redirect the ouput of SQLPLUS, it gives me those same errors I've reported:

As you can see, in console it shows me this : (sorry, but I can't post links yet)

------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)
| Time |
--------------------------------------------------------------------------------
------------
| 0 | SELECT STATEMENT | | 20 | 4300 | | 203K (1)
| 00:40:47 |
| 1 | SORT ORDER BY | | 20 | 4300 | 82M| 203K (1)
| 00:40:47 |
| 2 | HASH GROUP BY | | 20 | 4300 | 82M| 203K (1)
| 00:40:47 |
|* 3 | COUNT STOPKEY | | | | |
| |
|* 4 | HASH JOIN | | 381K| 78M| | 168K (1)
| 00:33:39 |
| 5 | TABLE ACCESS FULL | NATION | 25 | 725 | | 3 (0)
| 00:00:01 |
|* 6 | HASH JOIN | | 381K| 67M| 17M| 168K (1)
| 00:33:39 |
|* 7 | HASH JOIN | | 381K| 13M| 7024K| 158K (1)
| 00:31:47 |
|* 8 | TABLE ACCESS FULL| ORDERS | 232K| 4304K| | 26459 (1)
| 00:05:18 |
|* 9 | TABLE ACCESS FULL| LINEITEM | 7998K| 129M| | 120K (1)
| 00:24:12 |
| 10 | TABLE ACCESS FULL | CUSTOMER | 600K| 85M| | 3860 (1)
| 00:00:47 |
--------------------------------------------------------------------------------
------------

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

3 - filter(ROWNUM<21)
4 - access("C_NATIONKEY"="N_NATIONKEY")
6 - access("C_CUSTKEY"="O_CUSTKEY")
7 - access("L_ORDERKEY"="O_ORDERKEY")
8 - filter("O_ORDERDATE"<TO_DATE(' 1994-01-01 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "O_ORDERDATE">=TO_DATE(' 1993-10-01 00:00:00', 's
yyyy-mm-dd
hh24:mi:ss'))
9 - filter("L_RETURNFLAG"='R')

29 rows selected.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64
bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

D:\tpch\oracle>

but doing redirect to an output file, it gets messy. It seems something is happening at the same time.

Any idea? I've been trying many possibilities.

Thank you for your answer, at least I can get some good results looking to sqpllus comand line.

[Updated on: Tue, 29 March 2011 12:55]

Report message to a moderator

Re: Why are these explain plans missing some info and messed up like they've been simultaneously wri [message #501528 is a reply to message #501526]

Tue, 29 March 2011 13:16

ilharco
Messages: 8
Registered: March 2011
Location: Portugal

Junior Member

I don't know if it was because of this privileges, but after doing this:

CONN / AS SYSDBA
GRANT SELECT ON v_$session TO my_schema;
GRANT SELECT ON v_$sql TO my_schema;
GRANT SELECT ON v_$sql_plan TO my_schema;
GRANT SELECT ON v_$sql_plan_statistics_all TO my_schema;

and then running sqpplus @explain_plans.sql >>out.txt

(explain plans.sql has something like this:

set lin 130 pages 999 trims on;
-- Queries C e D
EXPLAIN PLAN FOR SELECT l_orderkey, l_linenumber, l_extendedprice FROM lineitem WHERE l_extendedprice < 1000;
select * from table(dbms_xplan.display);
EXPLAIN PLAN FOR SELECT l_orderkey, l_linenumber, l_extendedprice FROM lineitem WHERE l_extendedprice < 100000;
select * from table(dbms_xplan.display);
-- .... and so on

it does write the plans in the correct way.

Report message to a moderator

Re: Why are these explain plans missing some info and messed up like they've been simultaneously wri [message #501529 is a reply to message #501526]

Tue, 29 March 2011 13:18

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Post via COPY & PASTE complete results of
SELECT * from v$version;

Report message to a moderator

SELECT * from v$version output [message #501530 is a reply to message #501529]

Tue, 29 March 2011 13:27

ilharco
Messages: 8
Registered: March 2011
Location: Portugal

Junior Member

Thank you for the advice. I'll try to be more careful from now on.

Here is the requested output:

SELECT * from v$version;

BANNER
--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

Report message to a moderator

Re: SELECT * from v$version output [message #501549 is a reply to message #501530]

Tue, 29 March 2011 17:06

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

http://download.oracle.com/docs/cd/E11882_01/server.112/e16638/ex_plan.htm#PFGRF009

Report message to a moderator

Previous Topic:	nested select / instead-of-trigger and views=no index used
Next Topic:	Can I use Oracle Hint to use index in a query which uses UNION

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Apr 29 03:31:24 CDT 2024