| 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 -> sql query taking too long
I run this sql query pretty often. Usually it only takes ~1 minute.
Now it's taking several minutes. Below is the TKPROF output. Thanks
for any help you can give.
TKPROF: Release 8.1.7.4.0 - Production on Tue May 11 18:36:02 2004
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Trace file: ora_25211_prod.trc
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.00 0.01 0 0 0
0
Fetch 0 0.00 0.00 0 0 0
0
total 1 0.00 0.01 0 0 0
0
Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer goal: CHOOSE
Parsing user id: SYS
select text
from
view$ where rowid=:1
call count cpu elapsed disk query current
rows
Parse 2 0.00 0.01 0 0 0
0
Execute 2 0.00 0.00 0 0 0
0
Fetch 2 0.00 0.00 0 4 0
2
total 6 0.00 0.01 0 4 0
2
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY USER ROWID VIEW$
********************************************************************************
select tablespace_name,
sum_alloc_mb,
count_bytes,
max_mb,
sum_free_mb,
100*sum_free_mb/sum_alloc_mb as pct_free
from (select tablespace_name,
round(sum(bytes)/(1024*1024)) sum_alloc_mb
from dba_data_files
group by tablespace_name),
(select tablespace_name fs_ts_name,
round(max(bytes)/(1024*1024)) as max_mb,
count(bytes) as count_bytes,
round(sum(bytes)/(1024*1024)) as sum_free_mb
from dba_free_space
group by tablespace_name)
where tablespace_name = fs_ts_name
order by pct_free
call count cpu elapsed disk query current
rows
Parse 1 0.02 0.04 0 0 0
0
Execute 1 0.01 0.01 0 0 0
0
Fetch 2 1314.62 1374.80 4 160599312 15
5
total 4 1314.65 1374.85 4 160599312 15
5
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
5 SORT ORDER BY
5 HASH JOIN
5 VIEW
5 SORT GROUP BY
15 VIEW DBA_DATA_FILES
15 UNION-ALL
15 NESTED LOOPS
16 NESTED LOOPS
16 FIXED TABLE FULL X$KCCFN
30 TABLE ACCESS BY INDEX ROWID FILE$
30 INDEX UNIQUE SCAN (object id 38)
15 TABLE ACCESS CLUSTER TS$
30 INDEX UNIQUE SCAN (object id 7)
0 NESTED LOOPS
1 NESTED LOOPS
1 NESTED LOOPS
16 FIXED TABLE FULL X$KCCFN
15 FIXED TABLE FIXED INDEX #1 X$KTFBHC
0 TABLE ACCESS BY INDEX ROWID FILE$
0 INDEX UNIQUE SCAN (object id 38)
0 TABLE ACCESS CLUSTER TS$
0 INDEX UNIQUE SCAN (object id 7)
5 VIEW
5 SORT GROUP BY
186168 VIEW DBA_FREE_SPACE
186168 UNION-ALL
186168 NESTED LOOPS
186169 NESTED LOOPS
16 INDEX FULL SCAN (object id 39)
186183 TABLE ACCESS CLUSTER FET$
30 INDEX UNIQUE SCAN (object id 7)
186168 TABLE ACCESS CLUSTER TS$
0 NESTED LOOPS
1 NESTED LOOPS
1 TABLE ACCESS FULL TS$
0 FIXED TABLE FIXED INDEX #1 X$KTFBFE
0 INDEX UNIQUE SCAN (object id 39)
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current
rows
Parse 1 0.02 0.04 0 0 0
0
Execute 2 0.01 0.02 0 0 0
0
Fetch 2 1314.62 1374.80 4 160599312 15
5
total 5 1314.65 1374.86 4 160599312 15
5
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 2 0.00 0.01 0 0 0
0
Execute 2 0.00 0.00 0 0 0
0
Fetch 2 0.00 0.00 0 4 0
2
total 6 0.00 0.01 0 4 0
2
Misses in library cache during parse: 1
2 user SQL statements in session.
2 internal SQL statements in session.
4 SQL statements in session.
1 session in tracefile.
2 user SQL statements in trace file.
2 internal SQL statements in trace file.
4 SQL statements in trace file.
3 unique SQL statements in trace file.
102 lines in trace file.
(prod):/oracle/product/admin/prod/udump> Received on Tue May 11 2004 - 18:00:17 CDT
 |
 |