Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: 10046 tracing Scalar Subquery

RE: 10046 tracing Scalar Subquery

From: Igor Neyman <ineyman_at_perceptron.com>
Date: Thu, 14 Apr 2005 10:18:09 -0400
Message-ID: <000201c540fc$c919e1a0$2004a8c0@development.perceptron.com> 





Thanks.


Ok, "explain plan" seems to be working (explaining scalar subquery) in
"simple" cases:



create table my_users as select * from all_users;
create table my_objects as select * from all_objects;



I create my own tables, because "explain plan" for original views
(all_users, all_objects) is too big/complicated.
And now: 



Explain plan for


SELECT username, to_number(substr(data,1,10)) CNT,


         to_number(substr(data,11)) AVG


    FROM (


       SELECT A.username, (SELECT to_char(count(*), 'fm0000000009') ||
                                    avg(object_id)
                              FROM my_objects B
                              WHERE B.owner = A.username) data
          FROM my_users A);





gives me:



select * from table(dbms_xplan.display);



PLAN_TABLE_OUTPUT









Plan hash value: 4202433434







| Id  | Operation             | Name       | Rows  | Bytes | Cost

(%CPU)| Time     |






|   0 | SELECT STATEMENT      |            |    12 |   204 |     3


(0)| 00:00:01 |


|   1 |  SORT AGGREGATE       |            |     1 |    30 |


|          |



|*  2 |   TABLE ACCESS FULL   | MY_OBJECTS |   436 | 13080 |   215

(1)| 00:00:03 |


|   3 |    SORT AGGREGATE     |            |     1 |    30 |


|          |



|*  4 |     TABLE ACCESS FULL | MY_OBJECTS |   436 | 13080 |   215

(1)| 00:00:03 |


|   5 |      TABLE ACCESS FULL| MY_USERS   |    12 |   204 |     3


(0)| 00:00:01 |







Predicate Information (identified by operation id):





   2 - filter("B"."OWNER"=:B1)


   4 - filter("B"."OWNER"=:B1)



Note






22 rows selected.



Which is fine.


Unfortunately, when I get to my real query, tables involved in scalar
subquery are not showing up in plan_table.



Here it is. I'm quering the following view:



create or replace view T2_STATS  (filter_id, part_type_id,

part_type_name, insp_point_id, insp_point_type_code_id, insp_point_name,
ch_id, ch_display_name, ch_sort_order, ch_number_type,
reporting_option_type, minimum, maximum, average, std_deviation,


total_count) as


SELECT filter_id, part_type_id, part_type_name,

       insp_point_id, insp_point_type_code_id, insp_point_name,
       ch_id, ch_display_name, ch_sort_order,
       ch_number_type, reporting_option_type,
       to_number(substr(AGR_DATA,1,11))  m_min,
       to_number(substr(AGR_DATA,12,11)) m_max,
       to_number(substr(AGR_DATA,23,11)) m_avg,
       to_number(substr(AGR_DATA,34,11)) m_std,
       to_number(substr(AGR_DATA,45,11)) m_cnt
   FROM	(SELECT F.filter_id, F.part_type_id, SC.part_type_name,
F.cell_id,
			SC.insp_point_id, SC.insp_point_name,
SC.insp_point_type_code_id,
			SC.ch_id, SC.ch_display_name, 
                        SC.ch_sort_order,
SC.ch_number_type,SC.reporting_option_type,
		        (SELECT to_char(MIN(M.measurement),'999999D999')
||
                                to_char(MAX(M.measurement),'999999D999')
||
		                to_char(AVG(M.measurement),'999999D999')


|| 
 


to_char(STDDEV(M.measurement),'999999D999') ||
        


to_char(COUNT(M.cycle_date_time),'999999D999')

		           FROM T_FILTER_RESULTS FR, GP_MEASUREMENT M
		           WHERE M.cell_id = F.cell_id
			     AND M.error_code_id = 0
			     AND M.cycle_date_time = FR.cycle_date_time
			     AND F.filter_id = FR.filter_id
			     AND F.part_type_id = FR.part_type_id
			     AND M.ch_id = SC.ch_id) AGR_DATA
                     FROM T_FILTER F, GPV_PART_TYPE_CH SC
		     WHERE F.cell_id = SC.cell_id
		       AND F.part_type_id = SC.part_type_id
		       AND SC.active_flag=1)




/



GPV_PART_TYPE_CH is a view, all others are tables.
So, 


explain plan for 


select * from T2_STATS where filter_id = 364 and part_type_id = 12055;



gives me:



PLAN_TABLE_OUTPUT





----Plan hash value: 65649395







| Id  | Operation                         | Name                   |

Rows  | Bytes | Cost (%CPU)| Time     |






|   0 | SELECT STATEMENT                  |                        |


28 |  8092 |    14   (0)| 00:00:01 |


|   1 |  VIEW                             |                        |


28 |  8092 |    14   (0)| 00:00:01 |


|   2 |   NESTED LOOPS                    |                        |


28 |  3304 |    14   (0)| 00:00:01 |


|   3 |    NESTED LOOPS                   |                        |


28 |  2492 |    13   (0)| 00:00:01 |


|   4 |     NESTED LOOPS                  |                        |


28 |  2240 |    12   (0)| 00:00:01 |


|   5 |      NESTED LOOPS                 |                        |



28 |  1624 |     6   (0)| 00:00:01 |


|   6 |       NESTED LOOPS                |                        |

1 |    40 |     2   (0)| 00:00:01 |


|   7 |        TABLE ACCESS BY INDEX ROWID| GP_PART_TYPE           |

1 |    26 |     1   (0)| 00:00:01 |
|*  8 |         INDEX UNIQUE SCAN         | PK_PART_TYPE           |
1 |       |     0   (0)| 00:00:01 |
|*  9 |        TABLE ACCESS BY INDEX ROWID| T_FILTER               |
1 |    14 |     1   (0)| 00:00:01 |
|* 10 |         INDEX UNIQUE SCAN         | PK_T_FILTER            |
1 |       |     0   (0)| 00:00:01 |
|* 11 |       TABLE ACCESS FULL           | GP_CHARACTERISTIC      |
28 |   504 |     4   (0)| 00:00:01 |


|  12 |      TABLE ACCESS BY INDEX ROWID  | GP_INSPECTION_POINT    |

1 |    22 |     1   (0)| 00:00:01 |
|* 13 |       INDEX UNIQUE SCAN           | PK_INSPECTION_POINT    |
1 |       |     0   (0)| 00:00:01 |
|* 14 |     INDEX UNIQUE SCAN             | PK_PARTTYPE_INSPPOINT  |
1 |     9 |     0   (0)| 00:00:01 |


|  15 |    TABLE ACCESS BY INDEX ROWID    | GP_CHARACTERISTIC_TYPE |

1 |    29 |     1   (0)| 00:00:01 |
|* 16 |     INDEX UNIQUE SCAN             | PK_CHARACTERISTIC_TYPE |
1 |       |     0   (0)| 00:00:01 |
------------------------------------------------------------------------






Predicate Information (identified by operation id):




   8 - access("PT"."PART_TYPE_ID"=12055)
   9 - filter("F"."CELL_ID"="PT"."CELL_ID")
  10 - access("F"."FILTER_ID"=364 AND "F"."PART_TYPE_ID"=12055)
  11 - filter("CH"."ACTIVE_FLAG"=1)
  13 - access("CH"."INSP_POINT_ID"="IP"."INSP_POINT_ID")
  14 - access("PTIP"."PART_TYPE_ID"=12055 AND


"PTIP"."INSP_POINT_ID"="IP"."INSP_POINT_ID")

  16 - access("CH"."CH_TYPE_CODE_ID"="CHT"."CH_TYPE_CODE_ID")



34 rows selected.



As you can see, no references to scalar subquery tables
(T_FILTER_RESULTS or GP_MEASUREMENT). The only tables showing in explain
plan are T_FILTER and tables, which GPV_PART_TYPE_CH view is based on.



Igor Neyman, OCP DBA


ineyman_at_perceptron.com





-----Original Message-----


From: oracle-l-bounce_at_freelists.org


[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Egor Starostin
Sent: Thursday, April 14, 2005 5:24 AM


To: ineyman_at_perceptron.com


Cc: oracle-l_at_freelists.org


Subject: Re: 10046 tracing Scalar Subquery



> So, the question is there any way to get "complete" execution plan for
> SELECT that includes scalar subquery?


Old and good (most of the time) 'explain plan', I think.




--=20


Egor


http://www.oracledba.ru/orasrp/


Free Oracle Session Resource Profiler

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l


Received on Thu Apr 14 2005 - 10:23:11 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US