Re: Views versus Embedded SQL

sims wrote:
> 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%

someplace in your post I missed the DDL, DML, and version number.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org