To set autotrace option run the file @D:\Oracle\Ora81\RDBMS\ADMIN\UTLXPLAN.SQL.
This sql creates the required table for setting up auto trace.
update scott.up_ml set cl3=null
where rowid in ( select rid
from ( select b.rowid rid,
row_number() over
(partition by cl1,cl2,cl3 ORDER BY cl1,cl2,cl3) rn
from scott.up_ml b
)
where rn <> 1 )
update scott.up_ml a set cl3 = null where rowid > (select min(rowid) from scott.up_ml b where a.cl1=b.cl1 and b.cl2=b.cl2);
Auto trace outputs
Execution Plan
----------------------------------------------------------
0 UPDATE STATEMENT Optimizer=CHOOSE
1 0 UPDATE OF 'UP_ML'
2 1 NESTED LOOPS
3 2 VIEW OF 'VW_NSO_1'
4 3 SORT (UNIQUE)
5 4 VIEW
6 5 WINDOW (SORT)
7 6 TABLE ACCESS (FULL) OF 'UP_ML'
8 2 TABLE ACCESS (BY USER ROWID) OF 'UP_ML'
Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
8 consistent gets
0 physical reads
860 redo size
619 bytes sent via SQL*Net to client
689 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
3 rows processed
*************************************************************************************
Execution Plan
----------------------------------------------------------
0 UPDATE STATEMENT Optimizer=CHOOSE
1 0 UPDATE OF 'UP_ML'
2 1 FILTER
3 2 TABLE ACCESS (FULL) OF 'UP_ML'
4 2 SORT (AGGREGATE)
5 4 TABLE ACCESS (FULL) OF 'UP_ML'
Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
12 consistent gets
0 physical reads
808 redo size
621 bytes sent via SQL*Net to client
618 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
3 rows processed
No comments:
Post a Comment