| 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 -> Re: Is it possible to tune this SQL statement?
The explain plan on oracle 9.2.0.6 gives:
17:39:26 SQL> explain plan for
17:39:35 2 select 'DATABASENAME: ORA_UNDO_SPACE: UNDO_AU:
'||round(f.undo_au
)||',
rom
17:39:42 5 (select nvl( sum(sumbytes), 0 ) undo_au from (select
status,
17:39:42 6 sum(bytes)/1024/1024 sumbytes, count(*) from
dba_undo_extents grou
p by
17:39:42 7 status ) sumundo
17:39:42 8 where status in ('ACTIVE','UNEXPIRED')) f,
17:39:42 9 (SELECT sum((VDF.bytes)/1024/1024) undo_size
17:39:42 10 FROM v$datafile VDF, v$tablespace VTS, dba_tablespaces
DTS
17:39:42 11 WHERE DTS.status = 'ONLINE' 17:39:42 12 AND DTS.contents = 'UNDO' 17:39:42 13 AND VTS.ts# = VDF.ts# 17:39:42 14 AND DTS.tablespace_name = VTS.name) g 17:39:42 15 where (f.undo_au/g.undo_size) > 0.7;
Explained.
17:39:42 SQL> 17:39:42 SQL> 17:39:42 SQL> select * from table(dbms_xplan.display
);
PLAN_TABLE_OUTPUT
| Id | Operation | Name | Rows
| Bytes
| Cost |
--------------------------------------------------------------------------------
---------
| 0 | SELECT STATEMENT | | 1
| 26
| 1522 |
| 1 | NESTED LOOPS | | 1
| 26
| 1522 |
| 2 | VIEW | | 1
| 13
| 1435 |
| 3 | SORT AGGREGATE | | 1
| 19
| |
| 4 | VIEW | | 1
| 19
| 1435 |
| 5 | SORT GROUP BY | | 1
| 182
| 1435 |
| 6 | NESTED LOOPS | | 1
| 182
| 1429 |
| 7 | NESTED LOOPS | | 1
| 156
| 1429 |
|* 8 | HASH JOIN | | 82
| 6396
| 35 |
|* 9 | TABLE ACCESS FULL | UNDO$ | 82
| 4264
| 17 |
| 10 | TABLE ACCESS FULL | TS$ | 8168
| 207K
| 17 |
|* 11 | FIXED TABLE FIXED INDEX | X$KTFBUE (ind:1) | 1
| 78
| 17 |
|* 12 | INDEX UNIQUE SCAN | I_FILE2 | 1
| 26
| |
|* 13 | VIEW | | 1
| 13
| 87 |
| 14 | SORT AGGREGATE | | 1
| 810
| |
| 15 | NESTED LOOPS | | 1
| 810
| 87 |
| 16 | NESTED LOOPS | | 1
| 513
| 70 |
| 17 | NESTED LOOPS | | 1
| 487
| 53 |
|* 18 | HASH JOIN | | 1
| 190
| 36 |
| 19 | NESTED LOOPS | | 1
| 99
| 18 |
|* 20 | FIXED TABLE FULL | X$KCCTS | 1
| 43
| 17 |
|* 21 | TABLE ACCESS BY INDEX ROWID| TS$ | 1
| 56
| 1 |
|* 22 | INDEX UNIQUE SCAN | I_TS1 | 1
| |
|
|* 23 | FIXED TABLE FULL | X$KCCFE | 1
| 91
| 17 |
|* 24 | FIXED TABLE FIXED INDEX | X$KCCFN (ind:1) | 1
| 297
| 17 |
|* 25 | FIXED TABLE FIXED INDEX | X$KCVFH (ind:1) | 1
| 26
| 17 |
|* 26 | FIXED TABLE FULL | X$KCCFN | 1
| 297
| 17 |
--------------------------------------------------------------------------------
---------
Predicate Information (identified by operation id):
8 - access("T"."TS#"="U"."TS#")
9 - filter("U"."SPARE1"=1)
11 - filter("E"."KTFBUESEGFNO"="U"."FILE#" AND
"E"."KTFBUESEGBNO"="U"."BLOCK#"
AND
"E"."KTFBUESEGTSN"="U"."TS#" AND
(DECODE("E"."KTFBUESTA",1,'ACTIVE
',2,'EXPIRED',3,'UNEXPI
RED','UNDEFINED')='ACTIVE' OR
DECODE("E"."KTFBUESTA",1,'ACTIVE',2,
'EXPIRED',3,'UNEXPIRED'
,'UNDEFINED')='UNEXPIRED'))
12 - access("U"."TS#"="F"."TS#" AND "E"."KTFBUEFNO"="F"."RELFILE#")
13 - filter("F"."UNDO_AU"/"G"."UNDO_SIZE">0.7)
18 - access("X$KCCTS"."TSTSN"="FE"."FETSN")
20 - filter("X$KCCTS"."INST_ID"=:B1 AND "X$KCCTS"."TSTSN"<>(-1))
21 - filter(DECODE("TS"."ONLINE$",1,'ONLINE',2,'OFFLINE',4,'READ
ONLY','UNDEFINED')='ONLINE' AND DECODE("TS"."CONTENTS$",0,DECODE(B
ITAND("TS"."FLAGS",16),
16,'UNDO','PERMANENT'),1,'TEMPORARY')='UNDO' AND
"TS"."ONLINE$"<>3
)
FNTYP"=4
AND "FN"."FNNAM" IS NOT NULL)
25 - filter("FN"."FNFNO"="FH"."HXFIL")
26 - filter("FE"."FEPAX"<>65535 AND "FE"."FEPAX"<>0 AND
"FE"."FEPAX"="FNAUX"."
FNNUM"
OR ("FE"."FEPAX"=0 OR "FE"."FEPAX"=65535) AND
"FE"."FENUM"="FNAUX"
."FNFNO" AND
"FNAUX"."FNTYP"=4 AND "FNAUX"."FNNAM" IS NOT NULL AND
"FE"."FEFNH"
="FNAUX"."FNNUM")
Note: cpu costing is off
59 rows selected.
17:39:49 SQL> Received on Wed Jan 11 2006 - 10:41:29 CST
 |
 |