| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Why is this cursor running so long?
Here's the output from SQL Trace and TKProf:
First, the cursor using the hard-coded values:
TKPROF: Release 7.3.2.3.2 - Production on Tue Nov 2 15:32:53 1999
Copyright (c) Oracle Corporation 1979, 1994. All rights reserved.
Trace file: DEVCEA_07:[ORACLE7.CEA2D.PFILE.DB_CEA2D.TRACE]DEVCEA_CEA2D_FG_SRV2_0 Sort options: default
count = number of times OCI procedure was executed cpu = cpu time in seconds executing elapsed = elapsed time in seconds executing disk = number of physical reads of buffers from disk query = number of buffers gotten for consistent read current = number of buffers gotten in current mode (usually for update) rows = number of rows processed by the fetch or execute call ********************************************************************************
alter session set sql_trace true
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0 Execute 1 0.02 0.18 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.02 0.18 0 0 0 0
Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer goal: CHOOSE
Parsing user id: 305 (S010792)
select parameter, value
from
v$nls_parameters where (upper(parameter) in ('NLS_SORT','NLS_CURRENCY',
'NLS_ISO_CURRENCY', 'NLS_DATE_LANGUAGE',
'NLS_NUMERIC_CHARACTERS', 'NLS_LANGUAGE','NLS_TERRITORY'))
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 0 0 7
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 0 0 7
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 305 (S010792)
error during parse of EXPLAIN PLAN statement
ORA-01039: insufficient privileges on underlying objects of the view
parse error offset: 105
select value
from
v$nls_parameters where (upper(parameter) = 'NLS_DATE_FORMAT')
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.00 0 0 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 0 0 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 305 (S010792)
error during parse of EXPLAIN PLAN statement
ORA-01039: insufficient privileges on underlying objects of the view
parse error offset: 98
begin DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); end;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0 Execute 2 0.00 0.00 0 0 0 2 Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 0 0 2
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 305 (S010792)
declare
p_start_date VARCHAR2(20);
p_stop_date VARCHAR2(20);
cursor c_getit is
select sum(a.net_energy) mlr_load,
trunc(a.hour_ending - (1/24), 'DDD') day_ending,
b.id report_comp_id,
a.comp_name_abbrv comp_name_abbrv
FROM mlr_load_view a,
customer_hist_view b
and a.hour_ending <= b.stop_date) and a.comp_name_abbrv = b.ems_name and b.aep_comp_flag = 'Y'
p_start_date := '31-AUG-1999';
p_stop_date := '01-SEP-1999';
for c_rec in c_getit
loop
dbms_output.put_line('mlr load = '||c_rec.mlr_load);
end loop;
end;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.04 0 0 0 0 Execute 1 0.00 0.02 0 0 0 1 Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.03 0.06 0 0 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 305 (S010792)
select text
from
view$ where obj#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 27 0.00 0.04 0 0 0 0 Execute 27 0.00 0.00 0 0 0 0 Fetch 27 0.01 0.08 8 108 0 27
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 81 0.01 0.12 8 108 0 27
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 2)
select value$
from
props$ where name = 'GLOBAL_DB_NAME'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.00 0 2 2 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 2 2 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 2)
SELECT SUM(A.NET_ENERGY) MLR_LOAD,TRUNC(A.HOUR_ENDING - (1 / 24 ) ,'DDD') DAY_ENDING,B.ID REPORT_COMP_ID,A.COMP_NAME_ABBRV COMP_NAME_ABBRV FROM
MLR_LOAD_VIEW A,CUSTOMER_HIST_VIEW B WHERE (A.HOUR_ENDING >
TO_DATE('31-AUG-1999','DD-MON-YYYY') AND A.HOUR_ENDING <=
TO_DATE('01-SEP-1999','DD-MON-YYYY') ) AND (A.HOUR_ENDING > B.START_DATE
AND A.HOUR_ENDING <= B.STOP_DATE ) AND A.COMP_NAME_ABBRV = B.EMS_NAME AND
B.AEP_COMP_FLAG = 'Y' GROUP BY TRUNC(HOUR_ENDING - (1 / 24 ) ,'DDD'),B.ID,
A.COMP_NAME_ABBRV
call count cpu elapsed disk query current rows
Parse 1 0.54 2.66 0 0 2 0 Execute 1 0.01 0.04 0 0 0 0 Fetch 7 2.60 4.04 45 91184 14 6
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 9 3.15 6.74 45 91184 16 6
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 305 (S010792) (recursive depth: 1)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
144 SORT (GROUP BY)
440 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'CUSTOMER_HIST'
144 VIEW OF 'MLR_LOAD_VIEW'
168 SORT (GROUP BY)
168 VIEW OF 'CALC_MLR_LOAD_VIEW'
168 UNION-ALL
144 VIEW OF 'INTERNAL_LOAD_VIEW'
456 SORT (GROUP BY)
456 VIEW OF 'CALC_INTERNAL_LOAD_VIEW'
456 UNION-ALL
264 VIEW OF 'TIE_INTER_INTRA_VIEW'
264 UNION-ALL
5928 SORT (GROUP BY)
5928 NESTED LOOPS
5928 NESTED LOOPS
11760 HASH JOIN
248 HASH JOIN
266 TABLE ACCESS GOAL: ANALYZED
(FULL) OF 'TIE_LINE_DEF_HIST'
440 TABLE ACCESS GOAL: ANALYZED
(FULL) OF 'CUSTOMER_HIST'
6001 INDEX GOAL: ANALYZED (RANGE
SCAN) OF 'HOUR_TIE_NAME_ENERGY' (NON-UNIQUE)
5928 TABLE ACCESS GOAL: ANALYZED (BY
ROWID) OF 'CUSTOMER_HIST'
11856 INDEX GOAL: ANALYZED (RANGE
SCAN) OF 'CUSTOMER_HIST_ID' (NON-UNIQUE)
5952 TABLE ACCESS GOAL: ANALYZED (BY
ROWID) OF 'CUSTOMER_HIST'
11880 INDEX GOAL: ANALYZED (RANGE SCAN)
OF 'CUSTOMER_HIST_ID' (NON-UNIQUE)
2064 SORT (GROUP BY)
2064 NESTED LOOPS
2064 NESTED LOOPS
2064 NESTED LOOPS
3312 HASH JOIN
266 TABLE ACCESS GOAL: ANALYZED
(FULL) OF 'TIE_LINE_DEF_HIST'
6001 INDEX GOAL: ANALYZED (RANGE
SCAN) OF 'HOUR_TIE_NAME_ENERGY' (NON-UNIQUE)
2064 TABLE ACCESS GOAL: ANALYZED
(BY ROWID) OF 'CUSTOMER_HIST'
4128 INDEX GOAL: ANALYZED (RANGE
SCAN) OF 'CUSTOMER_HIST_ID' (NON-UNIQUE)
2064 TABLE ACCESS GOAL: ANALYZED (BY
ROWID) OF 'CUSTOMER_HIST'
4128 INDEX GOAL: ANALYZED (RANGE
SCAN) OF 'CUSTOMER_HIST_ID' (NON-UNIQUE)
2064 TABLE ACCESS GOAL: ANALYZED (BY
ROWID) OF 'CUSTOMER_HIST'
4128 INDEX GOAL: ANALYZED (RANGE SCAN)
OF 'CUSTOMER_HIST_ID' (NON-UNIQUE)
144 VIEW OF 'NET_OUTPUT_GEN_VIEW'
2088 SORT (GROUP BY)
2088 NESTED LOOPS
3240 HASH JOIN
88 INDEX GOAL: ANALYZED (RANGE SCAN)
OF 'REPORT_USE_UNITS' (NON-UNIQUE)
2593 INDEX GOAL: ANALYZED (RANGE SCAN)
OF 'HOUR_UNIT_NAME_NET_GEN' (NON-UNIQUE)
2088 TABLE ACCESS GOAL: ANALYZED (BY
ROWID) OF 'CUSTOMER_HIST'
4176 INDEX GOAL: ANALYZED (RANGE SCAN)
OF 'CUSTOMER_HIST_ID' (NON-UNIQUE)
24 VIEW OF 'PUMPED_GEN_VIEW'
72 SORT (GROUP BY)
72 NESTED LOOPS
72 HASH JOIN
114 TABLE ACCESS GOAL: ANALYZED (FULL)
OF 'UNIT_DEF_HIST'
2593 INDEX GOAL: ANALYZED (RANGE SCAN)
OF 'HOUR_UNIT_NAME_NET_GEN' (NON-UNIQUE)
72 TABLE ACCESS GOAL: ANALYZED (BY
ROWID) OF 'CUSTOMER_HIST'
144 INDEX GOAL: ANALYZED (RANGE SCAN)
OF 'CUSTOMER_HIST_ID' (NON-UNIQUE)
24 HASH JOIN
24 NESTED LOOPS
25 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'CUST_LOAD_DEF_HIST'
24 TABLE ACCESS GOAL: ANALYZED (BY ROWID)
OF 'HOURLY_MISC_LOAD'
598 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'HRMSC_LOAD_HOUR_ENDING_CUST' (UNIQUE)
440 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'CUSTOMER_HIST'
0 REMOTE [DEV_CEASPH1_LINK.WORLD]
SELECT "MWH","HOUR_ENDING","COMP_NAME_ABBRV" FROM
"CEAS"."AUXPB_SCHEDULE_VIEW" AUXPB_SCHEDULE_VIEW
WHERE "HOUR_ENDING"<=:1 AND "HOUR_ENDING">:2
24 REMOTE [DEV_CEASPH1_LINK.WORLD]
SELECT "MWH","HOUR_ENDING","COMP_NAME_ABBRV" FROM
"CEAS"."FIRM_SCHEDULE_VIEW" FIRM_SCHEDULE_VIEW WHERE
"HOUR_ENDING"<=:1 AND "HOUR_ENDING">:2
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 5 0.03 0.04 0 0 0 0 Execute 6 0.02 0.20 0 0 0 3 Fetch 2 0.00 0.00 0 0 0 8
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 13 0.05 0.24 0 0 0 11
Misses in library cache during parse: 1 Misses in library cache during execute: 1
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 29 0.54 2.70 0 0 2 0 Execute 29 0.01 0.04 0 0 0 0 Fetch 36 2.61 4.12 53 91294 16 34
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 94 3.16 6.86 53 91294 18 34
Misses in library cache during parse: 1
7 user SQL statements in session.
28 internal SQL statements in session.
35 SQL statements in session.
1 statement EXPLAINed in this session.
0 session in tracefile.
7 user SQL statements in trace file.
28 internal SQL statements in trace file.
35 SQL statements in trace file.
8 unique SQL statements in trace file.
1 SQL statements EXPLAINed using schema:
S010792.prof$plan_table
Default table was used.
Table was created.
Table was dropped.
443 lines in trace file.
Here's the tkprof output for the query using variables:
TKPROF: Release 7.3.2.3.2 - Production on Tue Nov 2 15:37:31 1999
Copyright (c) Oracle Corporation 1979, 1994. All rights reserved.
Trace file: DEVCEA_07:[ORACLE7.CEA2D.PFILE.DB_CEA2D.TRACE]DEVCEA_CEA2D_FG_SRV2_0 Sort options: default
count = number of times OCI procedure was executed cpu = cpu time in seconds executing elapsed = elapsed time in seconds executing disk = number of physical reads of buffers from disk query = number of buffers gotten for consistent read current = number of buffers gotten in current mode (usually for update) rows = number of rows processed by the fetch or execute call ********************************************************************************
alter session set sql_trace true
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0 Execute 1 0.01 0.16 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.01 0.16 0 0 0 0
Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer goal: CHOOSE
Parsing user id: 305 (S010792)
select parameter, value
from
v$nls_parameters where (upper(parameter) in ('NLS_SORT','NLS_CURRENCY',
'NLS_ISO_CURRENCY', 'NLS_DATE_LANGUAGE',
'NLS_NUMERIC_CHARACTERS', 'NLS_LANGUAGE','NLS_TERRITORY'))
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 0 0 7
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 0 0 7
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 305 (S010792)
error during parse of EXPLAIN PLAN statement
ORA-01039: insufficient privileges on underlying objects of the view
parse error offset: 105
select value
from
v$nls_parameters where (upper(parameter) = 'NLS_DATE_FORMAT')
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.00 0 0 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 0 0 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 305 (S010792)
error during parse of EXPLAIN PLAN statement
ORA-01039: insufficient privileges on underlying objects of the view
parse error offset: 98
begin DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); end;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0 Execute 2 0.01 0.00 0 0 0 2 Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.00 0 0 0 2
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 305 (S010792)
declare
p_start_date VARCHAR2(20);
p_stop_date VARCHAR2(20);
cursor c_getit is
select sum(a.net_energy) mlr_load,
trunc(a.hour_ending - (1/24), 'DDD') day_ending,
b.id report_comp_id,
a.comp_name_abbrv comp_name_abbrv
FROM mlr_load_view a,
customer_hist_view b
and a.hour_ending <= b.stop_date) and a.comp_name_abbrv = b.ems_name and b.aep_comp_flag = 'Y'
p_start_date := '31-AUG-1999';
p_stop_date := '01-SEP-1999';
for c_rec in c_getit
loop
dbms_output.put_line('mlr load = '||c_rec.mlr_load);
end loop;
end;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 1 Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 0.00 0 0 0 1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 305 (S010792)
SELECT SUM(A.NET_ENERGY) MLR_LOAD,TRUNC(A.HOUR_ENDING - (1 / 24 ) ,'DDD')
DAY_ENDING,B.ID REPORT_COMP_ID,A.COMP_NAME_ABBRV COMP_NAME_ABBRV
FROM
MLR_LOAD_VIEW A,CUSTOMER_HIST_VIEW B WHERE (A.HOUR_ENDING > TO_DATE(:b1,
'DD-MON-YYYY') AND A.HOUR_ENDING <= TO_DATE(:b2,'DD-MON-YYYY') ) AND
(A.HOUR_ENDING > B.START_DATE AND A.HOUR_ENDING <= B.STOP_DATE ) AND
A.COMP_NAME_ABBRV = B.EMS_NAME AND B.AEP_COMP_FLAG = 'Y' GROUP BY
TRUNC(HOUR_ENDING - (1 / 24 ) ,'DDD'),B.ID,A.COMP_NAME_ABBRV
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 7 111.22 178.08 8393 44514 10 6
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 9 111.22 178.08 8393 44514 10 6
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 305 (S010792) (recursive depth: 1)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
144 SORT (GROUP BY)
440 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'CUSTOMER_HIST'
1008 VIEW OF 'MLR_LOAD_VIEW'
168 SORT (GROUP BY)
168 VIEW OF 'CALC_MLR_LOAD_VIEW'
168 UNION-ALL
144 VIEW OF 'INTERNAL_LOAD_VIEW'
456 SORT (GROUP BY)
456 VIEW OF 'CALC_INTERNAL_LOAD_VIEW'
456 UNION-ALL
264 VIEW OF 'TIE_INTER_INTRA_VIEW'
264 UNION-ALL
5928 SORT (GROUP BY)
5928 FILTER
5928 NESTED LOOPS
248 NESTED LOOPS
248 NESTED LOOPS
248 NESTED LOOPS
266 TABLE ACCESS GOAL:
ANALYZED (FULL) OF 'TIE_LINE_DEF_HIST'
248 TABLE ACCESS GOAL:
ANALYZED (BY ROWID) OF 'CUSTOMER_HIST'
496 INDEX GOAL: ANALYZED
(RANGE SCAN) OF 'CUSTOMER_HIST_ID'
(NON-UNIQUE)
248 TABLE ACCESS GOAL: ANALYZED
(BY ROWID) OF 'CUSTOMER_HIST'
496 INDEX GOAL: ANALYZED
(RANGE SCAN) OF 'CUSTOMER_HIST_ID'
(NON-UNIQUE)
249 TABLE ACCESS GOAL: ANALYZED
(BY ROWID) OF 'CUSTOMER_HIST'
497 INDEX GOAL: ANALYZED (RANGE
SCAN) OF 'CUSTOMER_HIST_ID' (NON-UNIQUE)
5928 TABLE ACCESS GOAL: ANALYZED (BY
ROWID) OF 'HOURLY_TIE'
1740703 INDEX GOAL: ANALYZED (RANGE
SCAN) OF 'HOURTIE_PK' (UNIQUE)
2064 SORT (GROUP BY)
2064 FILTER
2064 NESTED LOOPS
86 NESTED LOOPS
86 NESTED LOOPS
86 NESTED LOOPS
266 TABLE ACCESS GOAL:
ANALYZED (FULL) OF 'TIE_LINE_DEF_HIST'
86 TABLE ACCESS GOAL:
ANALYZED (BY ROWID) OF 'CUSTOMER_HIST'
172 INDEX GOAL: ANALYZED
(RANGE SCAN) OF 'CUSTOMER_HIST_ID'
(NON-UNIQUE)
86 TABLE ACCESS GOAL: ANALYZED
(BY ROWID) OF 'CUSTOMER_HIST'
172 INDEX GOAL: ANALYZED
(RANGE SCAN) OF 'CUSTOMER_HIST_ID'
(NON-UNIQUE)
86 TABLE ACCESS GOAL: ANALYZED
(BY ROWID) OF 'CUSTOMER_HIST'
172 INDEX GOAL: ANALYZED (RANGE
SCAN) OF 'CUSTOMER_HIST_ID' (NON-UNIQUE)
2064 TABLE ACCESS GOAL: ANALYZED (BY
ROWID) OF 'HOURLY_TIE'
622323 INDEX GOAL: ANALYZED (RANGE
SCAN) OF 'HOURTIE_PK' (UNIQUE)
144 VIEW OF 'NET_OUTPUT_GEN_VIEW'
2088 SORT (GROUP BY)
2088 FILTER
2088 NESTED LOOPS
2088 NESTED LOOPS
88 INDEX GOAL: ANALYZED (RANGE SCAN)
OF 'REPORT_USE_UNITS' (NON-UNIQUE)
2088 TABLE ACCESS GOAL: ANALYZED (BY
ROWID) OF 'HOURLY_UNIT_GEN'
626068 INDEX GOAL: ANALYZED (RANGE
SCAN) OF 'HOURUTGEN_PK' (UNIQUE)
2088 TABLE ACCESS GOAL: ANALYZED (BY
ROWID) OF 'CUSTOMER_HIST'
4176 INDEX GOAL: ANALYZED (RANGE SCAN)
OF 'CUSTOMER_HIST_ID' (NON-UNIQUE)
24 VIEW OF 'PUMPED_GEN_VIEW'
72 SORT (GROUP BY)
72 FILTER
72 NESTED LOOPS
72 NESTED LOOPS
114 TABLE ACCESS GOAL: ANALYZED
(FULL) OF 'UNIT_DEF_HIST'
72 TABLE ACCESS GOAL: ANALYZED (BY
ROWID) OF 'HOURLY_UNIT_GEN'
21627 INDEX GOAL: ANALYZED (RANGE
SCAN) OF 'HOURUTGEN_PK' (UNIQUE)
72 TABLE ACCESS GOAL: ANALYZED (BY
ROWID) OF 'CUSTOMER_HIST'
144 INDEX GOAL: ANALYZED (RANGE SCAN)
OF 'CUSTOMER_HIST_ID' (NON-UNIQUE)
24 FILTER
24 NESTED LOOPS
1 NESTED LOOPS
25 TABLE ACCESS GOAL: ANALYZED (FULL)
OF 'CUST_LOAD_DEF_HIST'
1 TABLE ACCESS GOAL: ANALYZED (BY
ROWID) OF 'CUSTOMER_HIST'
2 INDEX GOAL: ANALYZED (RANGE SCAN)
OF 'CUSTOMER_HIST_ID' (NON-UNIQUE)
24 TABLE ACCESS GOAL: ANALYZED (BY ROWID)
OF 'HOURLY_MISC_LOAD'
25 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'HRMISCLOAD_PK' (UNIQUE)
0 FILTER
0 REMOTE [DEV_CEASPH1_LINK.WORLD]
SELECT "MWH","HOUR_ENDING","COMP_NAME_ABBRV" FROM
"CEAS"."AUXPB_SCHEDULE_VIEW" AUXPB_SCHEDULE_VIEW
WHERE "HOUR_ENDING"<=TO_DATE(:1,'DD-MON-YYYY') AND
"HOUR_ENDING">TO_DATE(:2,'DD-MON-YYYY')
24 FILTER
24 REMOTE [DEV_CEASPH1_LINK.WORLD]
SELECT "MWH","HOUR_ENDING","COMP_NAME_ABBRV" FROM
"CEAS"."FIRM_SCHEDULE_VIEW" FIRM_SCHEDULE_VIEW WHERE
"HOUR_ENDING"<=TO_DATE(:1,'DD-MON-YYYY') AND
"HOUR_ENDING">TO_DATE(:2,'DD-MON-YYYY')
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 5 0.01 0.00 0 0 0 0 Execute 6 0.02 0.16 0 0 0 3 Fetch 2 0.00 0.00 0 0 0 8
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 13 0.03 0.16 0 0 0 11
Misses in library cache during parse: 0 Misses in library cache during execute: 1
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 7 111.22 178.08 8393 44514 10 6
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 9 111.22 178.08 8393 44514 10 6
Misses in library cache during parse: 0
7 user SQL statements in session.
0 internal SQL statements in session.
7 SQL statements in session.
1 statement EXPLAINed in this session.
0 session in tracefile.
7 user SQL statements in trace file.
0 internal SQL statements in trace file.
7 SQL statements in trace file.
6 unique SQL statements in trace file.
1 SQL statements EXPLAINed using schema:
S010792.prof$plan_table
Default table was used.
Table was created.
Table was dropped.
205 lines in trace file.
--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Ivan Samuelson * Staff Support Coordinator and * isamuels_at_columbus.rr.com Information Systems Consultant * Metro Information Services * http://home.columbus.rr.com/isamuels http://www.MetroIS.com *
 |
 |