RE: 10046 tracing Scalar Subquery
- From: "Igor Neyman" <ineyman@xxxxxxxxxxxxxx>
- To: <egorst@xxxxxxxxx>
- Date: Thu, 14 Apr 2005 10:18:09 -0400
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
-----
- dynamic sampling used for this statement
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@xxxxxxxxxxxxxx
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Egor Starostin
Sent: Thursday, April 14, 2005 5:24 AM
To: ineyman@xxxxxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
