Views versus Embedded SQL

From: sims <sims_at_mailinator.com>
Date: Fri, 1 Jun 2007 16:45:36 -0400
Message-ID: <13611b7qfr53u12@corp.supernews.com>

10gR2
So I take a big long procedure and "refactored" it by replacing all the big SELECTs (like in INSERT INTO....SELECT.....) with Views and replace variables with SYS_CONTEXT (less than 10) - that's all
so ZERO changes to SELECTS, and only added calls to define CONTEXT values.

I don't get it --- new smaller code ALWAYS runs a bit slower and and this is the result via Tom Kyte's "runstat tool" what's with all that LATCHing ????
and please tell me what else is terriblly wrong that you see. thanks

Run1 = New, smaller procedure
Run2 = Old long procedure

Run1 ran in 2735 hsecs
Run2 ran in 2287 hsecs
run 1 ran in 119.59% of the time

Name Run1 Run2 Diff

STAT...physical read total mul 1 0 -1
STAT...pinned buffers inspecte 0 1 1
STAT...physical reads cache pr 1 0 -1
STAT...shared hash latch upgra 181 180 -1
STAT...cleanouts only - consis 0 1 1
STAT...immediate (CR) block cl 0 1 1
STAT...leaf node splits 0 1 1
STAT...leaf node 90-10 splits 0 1 1
STAT...sorts (memory) 4,603 4,604 1
LATCH.session switching 0 1 1
LATCH.KMG MMAN ready and start 9 8 -1
LATCH.archive control 1 0 -1
LATCH.FAL subheap alocation 1 0 -1
LATCH.FAL request queue 1 0 -1
LATCH.transaction branch alloc 0 1 1
LATCH.KTF sga latch 1 0 -1
LATCH.kks stats 0 1 1
LATCH.global KZLD latch for me 6 5 -1
LATCH.resmgr:actses change gro 8 7 -1
LATCH.Shared B-Tree 1 0 -1
LATCH.session timer 9 10 1
LATCH.ncodef allocation latch 0 1 1
LATCH.kwqbsn:qsga 1 0 -1
LATCH.threshold alerts latch 0 1 1
LATCH.PL/SQL warning settings 18 17 -1
STAT...commit cleanout failure 0 2 2
STAT...commit cleanouts 0 2 2
STAT...immediate (CURRENT) blo 0 2 2
STAT...cursor authentications 0 2 2
LATCH.process allocation 10 12 2
LATCH.process group creation 10 12 2
LATCH.OS process: request allo 10 12 2
LATCH.mostly latch-free SCN 18 20 2
LATCH.lgwr LWN SCN 18 20 2
LATCH.Consistent RBA 18 20 2
LATCH.multiblock read objects 2 0 -2
LATCH.archive process latch 10 8 -2
LATCH.parameter list 20 18 -2
LATCH.job_queue_processes para 3 1 -2
LATCH.ASM db client latch 18 16 -2
LATCH.JS slv state obj latch 0 2 2
STAT...commit txn count during 0 3 3
STAT...parse count (hard) 0 3 3
LATCH.event group latch 5 8 3
LATCH.active checkpoint queue 11 14 3
LATCH.flashback mapping 10 7 -3
LATCH.transaction allocation 5 8 3
LATCH.commit callback allocati 1 4 3
LATCH.sequence cache 21 18 -3
LATCH.KWQMN job cache list lat 3 0 -3
STAT...change write time 5 1 -4
STAT...calls to kcmgas 246 242 -4
STAT...index crx upgrade (posi 175 179 4
LATCH.redo allocation 56 60 4
LATCH.list of block allocation 4 8 4
LATCH.user lock 26 22 -4
LATCH.constraint object alloca 0 4 4
STAT...parse time cpu 0 5 5
LATCH.resmgr group change latc 13 8 -5
LATCH.cache buffers lru chain 275 270 -5
STAT...opened cursors current 16 22 6
STAT...physical write total mu 149 155 6
LATCH.active service list 80 86 6
LATCH.qmn task queue latch 6 0 -6
LATCH.post/wait queue 11 18 7
LATCH.redo writing 84 91 7
STAT...concurrency wait time 8 0 -8
STAT...parse time elapsed 0 8 8
LATCH.messages 241 233 -8
LATCH.session idle bit 403 394 -9
LATCH.parameter table allocati 7 16 9
LATCH.OS process allocation 19 28 9
LATCH.OS process 15 24 9
STAT...heap block compress 52 62 10
LATCH.session state list latch 16 26 10
LATCH.library cache load lock 0 12 12
STAT...table fetch continued r 1,672 1,685 13
LATCH.client/application info 53 40 -13
STAT...session cursor cache hi 0 15 15
LATCH.flashback allocation 154 171 17
STAT...physical reads for flas 116 135 19
STAT...redo ordering marks 116 135 19
STAT...redo subscn max counts 116 135 19
STAT...calls to kcmgcs 44 64 20
LATCH.library cache lock alloc 58 79 21
STAT...opened cursors cumulati 17 41 24
LATCH.library cache pin alloca 13 37 24
STAT...active txn count during 38 63 25
STAT...cleanout - number of kt 38 64 26
LATCH.ktm global data 32 0 -32
STAT...physical writes direct 1,092 1,059 -33
STAT...physical writes non che 1,092 1,059 -33
STAT...physical writes direct 1,092 1,059 -33
STAT...db block gets direct 1,092 1,059 -33
STAT...parse count (total) 15 48 33
STAT...physical writes 1,092 1,059 -33
LATCH.FOB s.o list latch 35 1 -34
STAT...switch current to new b 65 105 40
STAT...physical write IO reque 201 160 -41
STAT...physical write total IO 201 160 -41
LATCH.compile environment latc 106 65 -41
STAT...rows fetched via callba 4,785 4,827 42
LATCH.SQL memory manager worka 1,751 1,708 -43
STAT...workarea memory allocat -25 33 58
LATCH.checkpoint queue latch 444 502 58
LATCH.channel handle pool latc 74 12 -62
LATCH.message pool operations 64 0 -64
STAT...hot buffers moved to he 88 24 -64
STAT...workarea executions - o 4,615 4,551 -64
LATCH.loader state object free 194 128 -66
LATCH.object stats modificatio 69 1 -68
LATCH.JS queue state obj latch 216 144 -72
LATCH.object queue header heap 74 2 -72
LATCH.cache buffer handles 1,850 1,928 78
STAT...recursive cpu usage 2,090 2,171 81
STAT...CPU used by this sessio 2,091 2,177 86
LATCH.library cache lock 2,523 2,621 98
LATCH.enqueue hash chains 1,241 1,139 -102
LATCH.undo global data 233 343 110
STAT...sorts (rows) 184,374 184,487 113
STAT...index fetch by key 4,968 4,827 -141
STAT...db block gets 6,536 6,701 165
LATCH.shared pool simulator 578 756 178
STAT...enqueue releases 265 72 -193
LATCH.dml lock allocation 155 352 197
STAT...db block gets from cach 5,444 5,642 198
STAT...enqueue requests 272 74 -198
STAT...redo entries 4,105 4,318 213
STAT...table scans (short tabl 4,530 4,750 220
STAT...consistent changes 4,411 4,639 228
LATCH.sort extent pool 252 13 -239
STAT...execute count 445 740 295
LATCH.channel operations paren 462 165 -297
STAT...user I/O wait time 469 110 -359
STAT...consistent gets - exami 12,890 13,261 371
LATCH.enqueues 1,076 703 -373
LATCH.session allocation 291 703 412
STAT...Elapsed Time 2,761 2,320 -441
STAT...index scans kdiixs1 83,464 83,917 453
STAT...db block changes 8,632 9,093 461
STAT...physical read IO reques 651 168 -483
STAT...free buffer requested 773 290 -483
STAT...physical reads cache 652 168 -484
STAT...physical reads 652 168 -484
STAT...recursive calls 548 1,035 487
STAT...free buffer inspected 712 184 -528
LATCH.library cache pin 17,863 17,332 -531
STAT...table fetch by rowid 92,718 93,280 562
STAT...physical read total IO 811 168 -643
STAT...buffer is pinned count 249,360 250,094 734
STAT...calls to get snapshot s 10,082 10,833 751
STAT...buffer is not pinned co 28,357 29,359 1,002
LATCH.object queue header oper 2,297 844 -1,453
LATCH.shared pool 17,224 18,724 1,500
LATCH.simulator lru latch 27,727 24,623 -3,104
LATCH.simulator hash latch 28,903 25,720 -3,183
LATCH.row cache objects 77,423 99,749 22,326
LATCH.cache buffers chains 641,937 665,350 23,413
STAT...table scan blocks gotte 35,449 67,934 32,485
STAT...no work - consistent re 132,788 166,109 33,321
STAT...consistent gets 155,083 189,260 34,177
STAT...consistent gets from ca 155,083 189,260 34,177
STAT...session logical reads 161,619 195,961 34,342
LATCH.library cache 73,614 28,922 -44,692
STAT...undo change vector size 926,128 1,061,488 135,360
STAT...redo size 1,218,852 1,364,768 145,916
STAT...physical write total by 8,945,664 8,675,328 -270,336
STAT...physical write bytes 8,945,664 8,675,328 -270,336
STAT...physical read bytes 5,341,184 1,376,256 -3,964,928
STAT...session uga memory 4,531,872 168,256 -4,363,616
STAT...session uga memory max 6,313,472 478,840 -5,834,632
STAT...physical read total byt 7,962,624 1,376,256 -6,586,368
STAT...session pga memory max 10,747,904 3,997,696 -6,750,208

STAT...session pga memory 10,223,616 262,144 -9,961,472 STAT...table scan rows gotten 8,480,732 24,003,818 15,523,086

Run1 latches total versus runs -- difference and pct Run1 Run2 Diff Pct
900,542 894,480 -6,062 100.68% Received on Fri Jun 01 2007 - 15:45:36 CDT