Test Process: Create Table Test As SELECT * FROM DBA_OBJECTS; Insert INTO TEST SELECT * FORM DBA_OBJECTS; ... COMMIT;
Create Table Sort_Test As SELECT ROWNUM RN, T. * from test t order by Object_id;
SQL> SELECT Count (*) from sort_test;
COUNT (*) ---------- 842752
create or replace procedure sp_test is num1 number; num2 number; begin runstats_pgk.rs_start; for i in 1..100 loop select count (*) into num1 from (select * from (select rownum countnum, t * from (select * from. sort_test order by object_id, rn) t where rownum <= 50) countnum where> 0); end loop; runstats_pgk.rs_middle; for i in 1..100 loop select count (*) into num2 from (select * from (select row_number () OVER (ORDER BY Object_ID, RN) Countnum, T. * from sort_test t) Where countnum> 0 and countnum <= 50); end loop; runstats_pgk.rs_stop; end sp_test;
The following is the result of the test: SQL> EXEC SP_TEST; Run1 Ran in 21286 HSECSRUN2 RAN in 16932 HSECSRUN1 RAN IN 125.71% IF THE TIME
Name Run1 Run2 Difflatch.Simulator Lru Latch 1 0-1stat ... Change Write Time 1 0 -1stat ... Free Buff_queue_processes Para 4 2 -2Stat ... Cleanout - Number Of KT 3 5 2Stat. ..constent gets - Exami 3 5 2Stat ... Consistent Gets 1,093,003 1,093,005 2Stat ... Active TXN Count During 3 5 2stat ... Calls to Kcmgcs 3 5 2Latch.Row Cache Objects 149 152 3lanch.row Cache Enqueue Latch 144 148 4stat ... Consistent Changes 509 515 6Stat ... DB Block Gets 519 525 6stat ... DB Block Changes 1,018 1,025 7stat.
..session logical reads 1,093,522 1,093,530 8LATCH.dml lock allocation 5 19 14LATCH.hash table column usage 0 14 14LATCH.lgwr LWN SCN 70 56 -14LATCH.mostly latch-free SCN 70 56 -14LATCH.session allocation 52 38 -14LATCH.redo allocation 580 594 14LATCH.active checkpoint queue 70 55 -15LATCH.session timer 71 56 -15LATCH.enqueue hash chains 254 236 -18LATCH.undo global data 23 44 21LATCH.channel operations paren 137 111 -26LATCH.redo writing 220 180 -40LATCH. Enqueues 276 223 -53latch.session IDLE BIT 170 115 -55STAT ... Redo Size 64, 880 64,940 60latch.library Cache Pin Alloca 112 178 66LATCH.cache buffers chains 2,189,080 2,189,156 76LATCH.shared pool 282 361 79LATCH.library cache pin 537 627 90LATCH.messages 511 407 -104LATCH.simulator hash latch 69,249 69,120 -129LATCH.library cache 655 800 145LATCH.SQL memory manager worka 4,556 3,885 - 671LATCH.checkpoint queue latch 6,656 5,280 -1,376STAT ... recursive cpu usage 21,291 16,933 -4,358STAT ... session pga memory max 7,916 0 -7,916run1 latchs total versus runs - defference and pctRun1 Run2 Diff Pct2,273,958 2,271,937 -2,021 100%
The PL / SQL process has been successfully completed. Select Count (Select Rownum Countnum, T. * From (Select * from sort_test order by object_id, rn) T where rownum <= 50) Where countnum> 0)
Call Count CPU Elapsed Disk Query Current Rows -------------------------------------------------------------------------------------------------------------- ---------------------------- Parse 1 0.00 0.00 0 0 0 0 0 0 0 0Fetch 100 212.87 207.84 0 1093000 0 100 ------------ -------------------------------------------------------- - - ---------- ---------- Total 201 212.87 207.85 0 1093000 0 100
Select Count (Select Row_Number () Over (Order By Object_ID, RN) Countnum, T. * From Sort_Test T) Where countnum> 0 and countnum <= 50)
Call Count CPU Elapsed Disk Query Current Rows -------------------------------------------------------------------------------------------------------------- ---------------------------- Parse 1 0.00 0.00 0 0 0 0Execute 100 0.01 0.00 0 0 0 0 0Fetch 100 169.29 165.32 0 1093000 0 100 ------------ -------------------------------------------------------- - - ---------- ---------- Total 201 169.30 165.33 0 1093000 0 100
