case study by rocker12

Hotsos Symposium 2005 - The Basics of an Effective Test Environment SQL> @hstats t Name Null? ----------------------------------------- -------PKEY NOT NULL STATUS FLAG

1 / 72

Type ---------------------------NUMBER NUMBER CHAR(1)

========================================================================================== Table Statistics ========================================================================================== TABLE_NAME : T LAST_ANALYZED : 04-MAR-2005 11:53:17 DEGREE : 1 PARTITIONED : NO NUM_ROWS : 100000 CHAIN_CNT : 0 BLOCKS : 207 EMPTY_BLOCKS : 48 AVG_SPACE : 934 AVG_ROW_LEN : 13 MONITORING : NO SAMPLE_SIZE : 100000 ----------------========================================================================================== Column Statistics ========================================================================================== Name Analyzed Null? NDV Density # Nulls # Buckets Sample Avg Col Len ========================================================================================== PKEY 03/04/2005 NOT NULL 100000 .000010 0 1 100000 4 STATUS 03/04/2005 3 .333333 0 1 100000 2 FLAG 03/04/2005 2 .500000 0 1 100000 1 ========================================================================================== Index Information ========================================================================================== INDEX_NAME : SYS_C002345 INDEX_TYPE : NORM LAST_ANALYZED : 04-MAR-2005 11:53:17 DEGREE : 1 PARTITIONED : NO BLEVEL : 1 LEAF_BLOCKS : 187 DISTINCT_KEYS : 100000 AVG_LEAF_BLOCKS_PER_KEY : 1 AVG_DATA_BLOCKS_PER_KEY : 1 CLUSTERING_FACTOR : 204 BLOCKS_IN_TABLE : 207 ROWS_IN_TABLE : 100000 March 04, 2005

case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment

2 / 72

----------------INDEX_NAME : T_N1 INDEX_TYPE : NORM LAST_ANALYZED : 04-MAR-2005 11:53:17 DEGREE : 1 PARTITIONED : NO BLEVEL : 1 LEAF_BLOCKS : 238 DISTINCT_KEYS : 3 AVG_LEAF_BLOCKS_PER_KEY : 79 AVG_DATA_BLOCKS_PER_KEY : 68 CLUSTERING_FACTOR : 204 BLOCKS_IN_TABLE : 207 ROWS_IN_TABLE : 100000 ----------------INDEX_NAME : T_N2 INDEX_TYPE : NORM LAST_ANALYZED : 04-MAR-2005 11:53:17 DEGREE : 1 PARTITIONED : NO BLEVEL : 1 LEAF_BLOCKS : 293 DISTINCT_KEYS : 2 AVG_LEAF_BLOCKS_PER_KEY : 146 AVG_DATA_BLOCKS_PER_KEY : 204 CLUSTERING_FACTOR : 408 BLOCKS_IN_TABLE : 207 ROWS_IN_TABLE : 100000 ----------------INDEX_NAME : T_N3 INDEX_TYPE : NORM LAST_ANALYZED : 04-MAR-2005 11:53:17 DEGREE : 1 PARTITIONED : NO BLEVEL : 1 LEAF_BLOCKS : 355 DISTINCT_KEYS : 6 AVG_LEAF_BLOCKS_PER_KEY : 59 AVG_DATA_BLOCKS_PER_KEY : 68 CLUSTERING_FACTOR : 410 BLOCKS_IN_TABLE : 207 ROWS_IN_TABLE : 100000 ----------------========================================================================================== Index Columns Information ========================================================================================== Index Name Pos# Order Column Name Expression ========================================================================================== March 04, 2005

case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment SYS_C002345

1 ASC

PKEY

T_N1

1 ASC

STATUS

T_N2

1 ASC

FLAG

T_N3

1 ASC 2 ASC

STATUS FLAG

3 / 72

SQL> SQL> SQL> SQL> @hstats flags Name Null? Type ----------------------------------------- -------- ---------------------------FLAG NOT NULL CHAR(1) ========================================================================================== Table Statistics ========================================================================================== TABLE_NAME : FLAGS LAST_ANALYZED : 04-MAR-2005 10:58:41 DEGREE : 1 PARTITIONED : NO NUM_ROWS : 5 CHAIN_CNT : 0 BLOCKS : EMPTY_BLOCKS : AVG_SPACE : AVG_ROW_LEN : 4 MONITORING : NO SAMPLE_SIZE : 5 ----------------========================================================================================== Column Statistics ========================================================================================== Name Analyzed Null? NDV Density # Nulls # Buckets Sample Avg Col Len ========================================================================================== FLAG 03/04/2005 NOT NULL 5 .200000 0 1 5 1 ========================================================================================== Index Information ========================================================================================== INDEX_NAME : SYS_IOT_TOP_29235 INDEX_TYPE : IOT LAST_ANALYZED : 04-MAR-2005 10:58:41 DEGREE : 1 PARTITIONED : NO March 04, 2005

case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment

4 / 72

BLEVEL : 0 LEAF_BLOCKS : 1 DISTINCT_KEYS : 5 AVG_LEAF_BLOCKS_PER_KEY : 1 AVG_DATA_BLOCKS_PER_KEY : 1 CLUSTERING_FACTOR : 0 BLOCKS_IN_TABLE : ROWS_IN_TABLE : 5 ----------------========================================================================================== Index Columns Information ========================================================================================== Index Name Pos# Order Column Name Expression ========================================================================================== SYS_IOT_TOP_29235

1 ASC

FLAG

SQL> @hix t Index Flags Height Column Name ------------------------------ ------- ------ -----------------------------SYS_C002345 U 2 PKEY T_N1 2 STATUS T_N2 2 FLAG T_N3 2 STATUS . FLAG SQL> SQL> @hix flags Index Flags Height Column Name ------------------------------ ------- ------ -----------------------------SYS_IOT_TOP_29235 U 1 FLAG SQL> SQL> @hcons t Type Constraint # Column Constraint Rule Refers To ----------- ----------------------- -- ---------- ---------------------- --------------Primary Key T.SYS_C002345 1 PKEY SQL> @hcons flags Type Constraint # Column Constraint Rule Refers To ----------- ----------------------- -- ---------- ---------------------- --------------Primary Key FLAGS.SYS_IOT_TOP_29235 1 FLAG SQL> get test March 04, 2005

case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment

5 / 72

1 update t 2 set flag='B' 3* where status=2 SQL> SQL> SQL> @hds TableOwner[OP] : TableName : t ColumnList : flag, status WhereClause: PageSize[30] : Table blocks below hwm Table rows (B) (R) ---------------------- ---------------208 100,000 More: Block selectivity Block count Row selectivity Row count F STATUS (pb = b/B) (b) (pr = r/R) (r) - ---------- ----------------- -------------- ----------------- ---------------A 2 79.81% 166 1.60% 1,600 B 2 79.81% 166 78.40% 78,400 A 1 10.58% 22 0.20% 200 B 1 10.58% 22 9.80% 9,800 A 0 8.65% 18 0.20% 200 B 0 8.65% 18 9.80% 9,800 SQL> SQL> SQL> SQL> select count(*) from t where status = '2' ; COUNT(*) ---------80000 1 row selected. SQL> SQL> select count(*) from t where status = '2' and flag = 'B' ; COUNT(*) ---------78400 1 row selected. SQL> March 04, 2005

case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment SQL> SQL> @dodml Enter .sql file name (without extension): Enter the workspace name: Enter the scenario name: Display results of SQL (ON/OFF)?:

6 / 72

test update problem off

Capturing statistics information... ================================================================= Name of 10046 trace file: hotsos_ora_3364.trc ================================================================= Explain Plan for Scenario update:problem -------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost | -------------------------------------------------------------------| 0 | UPDATE STATEMENT | | 33333 | 99999 | 22 | | 1 | UPDATE | T | | | | |* 2 | TABLE ACCESS FULL | T | 33333 | 99999 | 22 | -------------------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------2 - filter("T"."STATUS"=2) Note: cpu costing is off ================================================================= Name of 10053 trace file: hotsos_ora_1988.trc ================================================================= Actual Plan for Scenario update:problem STAT #1 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE (cr=563 r=651 w=0 time=5998399 us)' STAT #1 id=2 cnt=80000 pid=1 pos=1 obj=29237 op='TABLE ACCESS FULL T (cr=211 r=200 w=0 time=480438 us)' SQL> SQL> SQL> @lsscstat List of values (Workspace) -----------------------------------------------------------UPDATE Workspace []: update

March 04, 2005

case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment

7 / 72

List of values (Scenario) -----------------------------------------------------------PROBLEM Scenario []: problem Statistic Name Statistic Type (S=stat, L=Latch) ---------------------------------------active checkpoint queue latch cache buffer handles channel operations parent latch checkpoint queue latch child cursor hash table Consistent RBA dml lock allocation FIB s.o chain latch FOB s.o list latch hash table column usage latch lgwr LWN SCN library cache pin allocation list of block allocation messages mostly latch-free SCN multiblock read objects post/wait queue redo writing session allocation session idle bit session timer sim partition latch simulator hash latch simulator lru latch SQL memory manager latch SQL memory manager workarea list latch transaction allocation undo global data active txn count during cleanout buffer is not pinned count bytes received via SQL*Net from client bytes sent via SQL*Net to client calls to get snapshot scn: kcmgss calls to kcmgas change write time cleanout - number of ktugct calls cluster key scan block gets March 04, 2005

Value - -------------L 10 L 54 L 4 L 6,935 L 15 L 66 L 7 L 4 L 4 L 4 L 66 L 90 L 7 L 258 L 66 L 48 L 38 L 216 L 2 L 17 L 2 L 20 L 5,867 L 206 L 2 L 134 L 1 L 693 S 2 S 244 S 1,123 S 709 S 340 S 542 S 65 S 150 S 4 case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment cluster key scans commit cleanouts commit cleanouts successfully completed commit txn count during cleanout consistent changes consistent gets - examination CPU used by this session CPU used when call started CR blocks created data blocks consistent reads - undo records applied

S S S S S S S S S S

2 452 452 150 2 155 175 175 2 2

db block changes deferred (CURRENT) block cleanout applications

S S

177,204 2

dirty buffers inspected enqueue releases enqueue requests free buffer inspected free buffer requested hot buffers moved to head of LRU immediate (CURRENT) block cleanout applications

S S S S S S S

2,309 213 215 2,819 2,339 139 450

index fetch by key index scans kdiixs1 leaf node splits messages sent no work - consistent read gets opened cursors cumulative pinned buffers inspected prefetched blocks prefetched blocks aged out before use recursive calls redo buffer allocation retries redo entries redo log space requests redo log space wait time redo ordering marks rollbacks only - consistent read gets session pga memory shared hash latch upgrades - no wait SQL*Net roundtrips to/from client switch current to new buffer table scans (long tables) user calls write clones created in foreground

S S S S S S S S S S S S S S S S S S S S S S S

2 11 150 63 231 18 60 176 57 121 2 88,288 2 41 132 2 16,336 11 5 150 1 8 107

March 04, 2005

8 / 72

case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment buffer pool cache buffers chains cache buffers lru chain enqueue hash chains enqueues library cache library cache pin redo allocation redo copy row cache enqueue latch row cache objects shared pool

L L L L L L L L L L L L

0 480,644 6,850 444 126 282 178 88,471 88,333 204 219 193

execute count parse count (hard) parse count (total) redo size table fetch by rowid table scan blocks gotten table scan rows gotten

S S S S S S S

18 2 18 22,887,828 11 207 100,000

buffer is pinned count consistent gets db block gets physical reads physical writes session logical reads sorts (disk) sorts (memory) sorts (rows)

S S S S S S S S S

0 563 96,896 651 0 97,459 0 0 0

elapsed time (centiseconds)

621

9 / 72

Actual Plan for Scenario UPDATE:PROBLEM STAT #1 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE (cr=563 r=651 w=0 time=5998399 us)' STAT #1 id=2 cnt=80000 pid=1 pos=1 obj=29237 op='TABLE ACCESS FULL T (cr=211 r=200 w=0 time=480438 us)' SQL> SQL> SQL> SQL> @hopttrace Enter the workspace name: Enter the scenario name:

March 04, 2005

update problem

case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment

10 / 72

Dump file e:\oracle\admin\hotsos\udump\hotsos_ora_1988.trc Fri Mar 04 11:01:50 2005 ORACLE V9.2.0.6.0 - Production vsnsta=0 vsnsql=12 vsnxtr=3 Windows 2000 Version 5.1 Service Pack 2, CPU type 586 Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.6.0 - Production Windows 2000 Version 5.1 Service Pack 2, CPU type 586 Instance name: hotsos Redo thread mounted by this instance: 1 Oracle process number: 13 Windows thread id: 1988, image: ORACLE.EXE *** SESSION ID:(7.214) 2005-03-04 11:01:50.148 *************************************** PARAMETERS USED BY THE OPTIMIZER ******************************** OPTIMIZER_FEATURES_ENABLE = 9.2.0 OPTIMIZER_MODE/GOAL = Choose _OPTIMIZER_PERCENT_PARALLEL = 101 HASH_AREA_SIZE = 4194304 HASH_JOIN_ENABLED = TRUE HASH_MULTIBLOCK_IO_COUNT = 0 SORT_AREA_SIZE = 2097152 OPTIMIZER_SEARCH_LIMIT = 5 PARTITION_VIEW_ENABLED = FALSE _ALWAYS_STAR_TRANSFORMATION = FALSE _B_TREE_BITMAP_PLANS = TRUE STAR_TRANSFORMATION_ENABLED = FALSE _COMPLEX_VIEW_MERGING = TRUE _PUSH_JOIN_PREDICATE = TRUE PARALLEL_BROADCAST_ENABLED = TRUE OPTIMIZER_MAX_PERMUTATIONS = 2000 OPTIMIZER_INDEX_CACHING = 50 _SYSTEM_INDEX_CACHING = 0 OPTIMIZER_INDEX_COST_ADJ = 50 OPTIMIZER_DYNAMIC_SAMPLING = 1 _OPTIMIZER_DYN_SMP_BLKS = 32 QUERY_REWRITE_ENABLED = TRUE QUERY_REWRITE_INTEGRITY = ENFORCED _INDEX_JOIN_ENABLED = TRUE _SORT_ELIMINATION_COST_RATIO = 0 _OR_EXPAND_NVL_PREDICATE = TRUE March 04, 2005

case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment

11 / 72

_NEW_INITIAL_JOIN_ORDERS = TRUE ALWAYS_ANTI_JOIN = CHOOSE ALWAYS_SEMI_JOIN = CHOOSE _OPTIMIZER_MODE_FORCE = TRUE _OPTIMIZER_UNDO_CHANGES = FALSE _UNNEST_SUBQUERY = TRUE _PUSH_JOIN_UNION_VIEW = TRUE _FAST_FULL_SCAN_ENABLED = TRUE _OPTIM_ENHANCE_NNULL_DETECTION = TRUE _ORDERED_NESTED_LOOP = TRUE _NESTED_LOOP_FUDGE = 100 _NO_OR_EXPANSION = FALSE _QUERY_COST_REWRITE = TRUE QUERY_REWRITE_EXPRESSION = TRUE _IMPROVED_ROW_LENGTH_ENABLED = TRUE _USE_NOSEGMENT_INDEXES = FALSE _ENABLE_TYPE_DEP_SELECTIVITY = TRUE _IMPROVED_OUTERJOIN_CARD = TRUE _OPTIMIZER_ADJUST_FOR_NULLS = TRUE _OPTIMIZER_CHOOSE_PERMUTATION = 0 _USE_COLUMN_STATS_FOR_FUNCTION = TRUE _SUBQUERY_PRUNING_ENABLED = TRUE _SUBQUERY_PRUNING_REDUCTION_FACTOR = 50 _SUBQUERY_PRUNING_COST_FACTOR = 20 _LIKE_WITH_BIND_AS_EQUALITY = FALSE _TABLE_SCAN_COST_PLUS_ONE = TRUE _SORTMERGE_INEQUALITY_JOIN_OFF = FALSE _DEFAULT_NON_EQUALITY_SEL_CHECK = TRUE _ONESIDE_COLSTAT_FOR_EQUIJOINS = TRUE _OPTIMIZER_COST_MODEL = CHOOSE _GSETS_ALWAYS_USE_TEMPTABLES = FALSE DB_FILE_MULTIBLOCK_READ_COUNT = 16 _NEW_SORT_COST_ESTIMATE = TRUE _GS_ANTI_SEMI_JOIN_ALLOWED = TRUE _CPU_TO_IO = 0 _PRED_MOVE_AROUND = TRUE *************************************** BASE STATISTICAL INFORMATION *********************** Table stats Table: T Alias: T TOTAL :: CDN: 100000 NBLKS: 207 AVG_ROW_LEN: 13 -- Index stats INDEX NAME: SYS_C002345 COL#: 1 TOTAL :: LVLS: 1 #LB: 187 #DK: 100000 LB/K: 1 DB/K: 1 CLUF: 204 INDEX NAME: T_N1 COL#: 2 TOTAL :: LVLS: 1 #LB: 238 #DK: 3 LB/K: 79 DB/K: 68 CLUF: 204 INDEX NAME: T_N2 COL#: 3 TOTAL :: LVLS: 1 #LB: 225 #DK: 2 LB/K: 112 DB/K: 204 CLUF: 408 March 04, 2005

case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment

12 / 72

INDEX NAME: T_N3 COL#: 2 3 TOTAL :: LVLS: 1 #LB: 273 #DK: 6 LB/K: 45 DB/K: 68 CLUF: 410 _OPTIMIZER_PERCENT_PARALLEL = 0 *************************************** SINGLE TABLE ACCESS PATH Column: STATUS Col#: 2 Table: T Alias: T NDV: 3 NULLS: 0 DENS: 3.3333e-001 LO: 0 HI: 2 NO HISTOGRAM: #BKT: 1 #VAL: 2 TABLE: T ORIG CDN: 100000 ROUNDED CDN: 33333 CMPTD CDN: 33333 Access path: tsc Resc: 22 Resp: 22 Access path: index (equal) Index: T_N1 TABLE: T RSC_CPU: 0 RSC_IO: 148 IX_SEL: 0.0000e+000 TB_SEL: 3.3333e-001 Skip scan: ss-sel 0 andv 2 ss cost 92 index io scan cost 0 Access path: index (index-only) Index: T_N3 TABLE: T RSC_CPU: 0 RSC_IO: 92 IX_SEL: 3.3333e-001 TB_SEL: 3.3333e-001 BEST_CST: 22.00 PATH: 2 Degree: 1 *************************************** OPTIMIZER STATISTICS AND COMPUTATIONS *************************************** GENERAL PLANS *********************** Join order[1]: T[T]#0 Best so far: TABLE#: 0 CST: 22 CDN: 33333 BYTES: 99999 Final - All Rows Plan: JOIN ORDER: 1 CST: 22 CDN: 33333 RSC: 22 RSP: 22 BYTES: 99999 IO-RSC: 22 IO-RSP: 22 CPU-RSC: 0 CPU-RSP: 0 QUERY explain plan set statement_id='update:problem' for update t set flag='B' where status=2 PLAN Cost of plan: 22 Operation...........Object name.....Options.........Id...Pid.. UPDATE STATEMENT 0 UPDATE T 1 TABLE ACCESS T FULL 2 1 QUERY alter session set events '10053 trace name context off' March 04, 2005

case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment

13 / 72

SQL> SQL> SQL> SQL> SQL> SQL> @horatrace Enter the workspace name: update Enter the scenario name: problem Review tkprof output? (Y/N): Y Your trace file (hotsos_ora_3364.trc) has been spooled to your default directory. Tkprof output will be displayed first (if requested). hotsos_ora_3364.trc will be displayed in your editor after any tkprof output. Press Enter to continue. . . TKPROF: Release 9.2.0.6.0 - Production on Fri Mar 4 11:29:18 2005 Copyright (c) 1982, 2002, Oracle Corporation.

Trace file: hotsos_ora_3364.trc Sort options: default . . . . . . update t set flag='B' where status=2 call count ------- -----Parse 1 Execute 1 Fetch 0 ------- -----total 2

cpu elapsed disk query current -------- ---------- ---------- ---------- ---------0.00 0.00 0 0 0 1.73 6.00 651 563 96896 0.00 0.00 0 0 0 -------- ---------- ---------- ---------- ---------1.73 6.00 651 563 96896

rows ---------0 80000 0 ---------80000

Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 41 Rows ------0 80000 . . . . . .

Row Source Operation --------------------------------------------------UPDATE (cr=563 r=651 w=0 time=5998399 us) TABLE ACCESS FULL T (cr=211 r=200 w=0 time=480438 us)

March 04, 2005

case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment

14 / 72

Dump file e:\oracle\admin\hotsos\udump\hotsos_ora_3364.trc . . . . . . PARSING IN CURSOR #1 len=40 dep=0 uid=41 oct=6 lid=41 tim=90089340242 hv=1483413068 ad='2bf70aac' update t set flag='B' where status=2 END OF STMT PARSE #1:c=0,e=1509,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=90089340234 . . . . . . EXEC #1:c=1734375,e=6000464,p=651,cr=563,cu=96896,mis=0,r=80000,dep=0,og=4,tim=90095354755 . . . . . .

SQL> SQL> SQL> SQL> SQL> get test_ans1 1 update t 2 set flag='B' 3 where status=2 4* and flag != 'B' SQL> SQL> SQL> @dodml Enter .sql file name (without extension): Enter the workspace name: Enter the scenario name: Display results of SQL (ON/OFF)?:

test_ans1 update answer1 off

Capturing statistics information... ================================================================= Name of 10046 trace file: hotsos_ora_1256.trc ================================================================= Explain Plan for Scenario update:answer1 -------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost | -------------------------------------------------------------------| 0 | UPDATE STATEMENT | | 16667 | 50001 | 22 | March 04, 2005

case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment

15 / 72

| 1 | UPDATE | T | | | | |* 2 | TABLE ACCESS FULL | T | 16667 | 50001 | 22 | -------------------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------2 - filter("T"."STATUS"=2 AND "T"."FLAG"<>'B') Note: cpu costing is off ================================================================= Name of 10053 trace file: hotsos_ora_1292.trc ================================================================= Actual Plan for Scenario update:answer1 STAT #1 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE (cr=215 r=717 w=0 time=2217354 us)' STAT #1 id=2 cnt=1600 pid=1 pos=1 obj=29237 op='TABLE ACCESS FULL T (cr=211 r=205 w=0 time=277281 us)' SQL> SQL> SQL> @lsscstat List of values (Workspace) -----------------------------------------------------------UPDATE Workspace [UPDATE]: List of values (Scenario) -----------------------------------------------------------ANSWER1 PROBLEM Scenario [PROBLEM]: answer1 Statistic Name Statistic Type (S=stat, L=Latch) ---------------------------------------active checkpoint queue latch channel operations parent latch checkpoint queue latch child cursor hash table Consistent RBA dml lock allocation hash table column usage latch March 04, 2005

Value - -------------L 4 L 2 L 2,357 L 15 L 5 L 3 L 6 case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment lgwr LWN SCN library cache pin allocation list of block allocation messages mostly latch-free SCN multiblock read objects post/wait queue redo writing session allocation session idle bit sim partition latch simulator hash latch simulator lru latch SQL memory manager latch SQL memory manager workarea list latch undo global data buffer is not pinned count bytes received via SQL*Net from client bytes sent via SQL*Net to client calls to get snapshot scn: kcmgss calls to kcmgas CPU used by this session CPU used when call started db block changes dirty buffers inspected enqueue releases enqueue requests free buffer inspected free buffer requested hot buffers moved to head of LRU index scans kdiixs1 messages sent no work - consistent read gets opened cursors cumulative pinned buffers inspected prefetched blocks prefetched blocks aged out before use recursive calls redo entries session pga memory shared hash latch upgrades - no wait SQL*Net roundtrips to/from client switch current to new buffer table scans (long tables) user calls write clones created in foreground

L L L L L L L L L L L L L L L L S S S S S S S S S S S S S S S S S S S S S S S S S S S S S S

5 42 6 31 5 48 4 21 2 17 2 464 91 1 67 10 210 1,142 709 9 178 33 33 16,111 835 4 6 898 981 365 1 6 209 6 36 181 25 8 8,011 189,544 1 5 166 1 8 12

buffer pool

March 04, 2005

16 / 72

case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment cache buffers chains cache buffers lru chain enqueue hash chains enqueues library cache library cache pin redo allocation redo copy row cache enqueue latch row cache objects shared pool

L L L L L L L L L L L

65,004 2,507 16 11 150 82 8,038 8,028 58 61 136

execute count parse count (hard) parse count (total) redo size table fetch by rowid table scan blocks gotten table scan rows gotten

S S S S S S S

6 2 6 1,633,416 1 207 100,000

buffer is pinned count consistent gets db block gets physical reads physical writes session logical reads sorts (disk) sorts (memory) sorts (rows)

S S S S S S S S S

0 215 14,585 717 0 14,800 0 0 0

elapsed time (centiseconds)

234

17 / 72

Actual Plan for Scenario UPDATE:ANSWER1 STAT #1 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE (cr=215 r=717 w=0 time=2217354 us)' STAT #1 id=2 cnt=1600 pid=1 pos=1 obj=29237 op='TABLE ACCESS FULL T (cr=211 r=205 w=0 time=277281 us)' SQL> SQL> SQL> SQL> @diff List of values (First workspace) -----------------------------------------------------------UPDATE

March 04, 2005

case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment

18 / 72

First workspace []: update List of values (First scenario) -----------------------------------------------------------ANSWER1 PROBLEM First scenario []: problem List of values (Second workspace) -----------------------------------------------------------UPDATE Second workspace []: update List of values (Second scenario) -----------------------------------------------------------ANSWER1 PROBLEM Second scenario []: answer1 Statistic Name Statistic Type (S=stat, L=Latch) ---------------------------------------active checkpoint queue latch cache buffer handles channel operations parent latch checkpoint queue latch Consistent RBA dml lock allocation FIB s.o chain latch FOB s.o list latch hash table column usage latch lgwr LWN SCN library cache pin allocation list of block allocation messages mostly latch-free SCN post/wait queue redo writing session timer sim partition latch simulator hash latch March 04, 2005

L L L L L L L L L L L L L L L L L L L

UPDATE UPDATE PROBLEM ANSWER1 Difference -------------- -------------- -------------10 4 -6 54 0 -54 4 2 -2 6,935 2,357 -4,578 66 5 -61 7 3 -4 4 0 -4 4 0 -4 4 6 2 66 5 -61 90 42 -48 7 6 -1 258 31 -227 66 5 -61 38 4 -34 216 21 -195 2 0 -2 20 2 -18 5,867 464 -5,403 case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment

19 / 72

simulator lru latch SQL memory manager latch SQL memory manager workarea list latch transaction allocation undo global data active txn count during cleanout buffer is not pinned count bytes received via SQL*Net from client calls to get snapshot scn: kcmgss calls to kcmgas change write time cleanout - number of ktugct calls cluster key scan block gets cluster key scans commit cleanouts commit cleanouts successfully completed commit txn count during cleanout consistent changes consistent gets - examination CPU used by this session CPU used when call started CR blocks created data blocks consistent reads - undo records applied

L L L L L S S S S S S S S S S S S S S S S S S

206 2 134 1 693 2 244 1,123 340 542 65 150 4 2 452 452 150 2 155 175 175 2 2

91 1 67 0 10 0 210 1,142 9 178 0 0 0 0 0 0 0 0 0 33 33 0 0

-115 -1 -67 -1 -683 -2 -34 19 -331 -364 -65 -150 -4 -2 -452 -452 -150 -2 -155 -142 -142 -2 -2

db block changes deferred (CURRENT) block cleanout applications

S S

177,204 2

16,111 0

-161,093 -2

dirty buffers inspected enqueue releases enqueue requests free buffer inspected free buffer requested hot buffers moved to head of LRU immediate (CURRENT) block cleanout applications

S S S S S S S

2,309 213 215 2,819 2,339 139 450

835 4 6 898 981 365 0

-1,474 -209 -209 -1,921 -1,358 226 -450

S S S S S S S S S S

2 11 150 63 231 18 60 176 57 121

0 1 0 6 209 6 36 181 25 8

-2 -10 -150 -57 -22 -12 -24 5 -32 -113

March 04, 2005

case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment

20 / 72

redo buffer allocation retries redo entries redo log space requests redo log space wait time redo ordering marks rollbacks only - consistent read gets session pga memory shared hash latch upgrades - no wait switch current to new buffer write clones created in foreground

S S S S S S S S S S

2 88,288 2 41 132 2 16,336 11 150 107

0 8,011 0 0 0 0 189,544 1 166 12

-2 -80,277 -2 -41 -132 -2 173,208 -10 16 -95

buffer pool cache buffers chains cache buffers lru chain enqueue hash chains enqueues library cache library cache pin redo allocation redo copy row cache enqueue latch row cache objects shared pool

L L L L L L L L L L L L

0 480,644 6,850 444 126 282 178 88,471 88,333 204 219 193

0 65,004 2,507 16 11 150 82 8,038 8,028 58 61 136

0 -415,640 -4,343 -428 -115 -132 -96 -80,433 -80,305 -146 -158 -57

execute count parse count (hard) parse count (total) redo size table fetch by rowid table scan blocks gotten table scan rows gotten

S S S S S S S

18 2 18 22,887,828 11 207 100,000

6 2 6 1,633,416 1 207 100,000

-12 0 -12 -21,254,412 -10 0 0

buffer is pinned count consistent gets db block gets physical reads physical writes session logical reads sorts (disk) sorts (memory) sorts (rows)

S S S S S S S S S

0 563 96,896 651 0 97,459 0 0 0

0 215 14,585 717 0 14,800 0 0 0

0 -348 -82,311 66 0 -82,659 0 0 0

elapsed time

621

234

-387

March 04, 2005

case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment

elapsed time (in seconds)

6.21

2.34

21 / 72

-3.87

List of values (Show actual 10046 STAT lines) -----------------------------------------------------------YES NO Show actual 10046 STAT lines [YES]: Actual Plan for Scenario UPDATE:PROBLEM STAT #1 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE (cr=563 r=651 w=0 time=5998399 us)' STAT #1 id=2 cnt=80000 pid=1 pos=1 obj=29237 op='TABLE ACCESS FULL T (cr=211 r=200 w=0 time=480438 us)' Actual Plan for Scenario UPDATE:ANSWER1 STAT #1 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE (cr=215 r=717 w=0 time=2217354 us)' STAT #1 id=2 cnt=1600 pid=1 pos=1 obj=29237 op='TABLE ACCESS FULL T (cr=211 r=205 w=0 time=277281 us)' List of values (Show EXPLAIN PLAN output) -----------------------------------------------------------YES NO Show EXPLAIN PLAN output [YES]: ========================================================================== UPDATE:PROBLEM -------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost | -------------------------------------------------------------------| 0 | UPDATE STATEMENT | | 33333 | 99999 | 22 | | 1 | UPDATE | T | | | | |* 2 | TABLE ACCESS FULL | T | 33333 | 99999 | 22 | -------------------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------2 - filter("T"."STATUS"=2) Note: cpu costing is off

March 04, 2005

case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment

22 / 72

========================================================================== UPDATE:ANSWER1 -------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost | -------------------------------------------------------------------| 0 | UPDATE STATEMENT | | 16667 | 50001 | 22 | | 1 | UPDATE | T | | | | |* 2 | TABLE ACCESS FULL | T | 16667 | 50001 | 22 | -------------------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------2 - filter("T"."STATUS"=2 AND "T"."FLAG"<>'B') Note: cpu costing is off ========================================================================== SQL> SQL> get test_ans2 1 update t 2 set flag='B' 3 where status=2 4* and flag in (select flag from flags minus select 'B' from dual ) SQL> SQL> SQL> @dodml Enter .sql file name (without extension): test_ans2 Enter the workspace name: update Enter the scenario name: answer2 Display results of SQL (ON/OFF)?: off Capturing statistics information... ================================================================= Name of 10046 trace file: hotsos_ora_3532.trc ================================================================= Explain Plan for Scenario update:answer2 ---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost | ---------------------------------------------------------------------------| 0 | UPDATE STATEMENT | | 83333 | 488K| 26 | | 1 | UPDATE | T | | | | | 2 | NESTED LOOPS | | 83333 | 488K| 26 | | 3 | VIEW | VW_NSO_1 | 5 | 15 | 25 | March 04, 2005

case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment

23 / 72

| 4 | MINUS | | | | | | 5 | SORT UNIQUE | | 5 | 5 | | | 6 | INDEX FULL SCAN | SYS_IOT_TOP_29235 | 5 | 5 | 1 | | 7 | TABLE ACCESS FULL| DUAL | 8168 | | 11 | |* 8 | INDEX RANGE SCAN | T_N3 | 16667 | 50001 | | ---------------------------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------8 - access("T"."STATUS"=2 AND "T"."FLAG"="VW_NSO_1"."$nso_col_1") Note: cpu costing is off ================================================================= Name of 10053 trace file: hotsos_ora_2552.trc ================================================================= Actual Plan for Scenario update:answer2 STAT STAT STAT STAT STAT STAT STAT STAT SQL> SQL> SQL>

#1 #1 #1 #1 #1 #1 #1 #1

id=1 id=2 id=3 id=4 id=5 id=6 id=7 id=8

cnt=0 pid=0 pos=1 obj=0 op='UPDATE (cr=15 r=494 w=0 time=1203620 us)' cnt=1600 pid=1 pos=1 obj=0 op='NESTED LOOPS (cr=15 r=3 w=0 time=40684 us)' cnt=4 pid=2 pos=1 obj=0 op='VIEW (cr=4 r=1 w=0 time=15222 us)' cnt=4 pid=3 pos=1 obj=0 op='MINUS (cr=4 r=1 w=0 time=15205 us)' cnt=5 pid=4 pos=1 obj=0 op='SORT UNIQUE (cr=1 r=1 w=0 time=15077 us)' cnt=5 pid=5 pos=1 obj=29236 op='INDEX FULL SCAN SYS_IOT_TOP_29235 (cr=1 r=1 w=0 time=14973 us)' cnt=1 pid=4 pos=2 obj=222 op='TABLE ACCESS FULL DUAL (cr=3 r=0 w=0 time=84 us)' cnt=1600 pid=2 pos=2 obj=29241 op='INDEX RANGE SCAN T_N3 (cr=11 r=2 w=0 time=18340 us)'

@lsscstat

March 04, 2005

case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment Statistic Name Statistic Type (S=stat, L=Latch) ---------------------------------------active checkpoint queue latch channel operations parent latch checkpoint queue latch child cursor hash table Consistent RBA dml lock allocation hash table column usage latch lgwr LWN SCN library cache load lock library cache pin allocation list of block allocation messages mostly latch-free SCN post/wait queue redo writing session allocation session idle bit session timer sim partition latch simulator hash latch simulator lru latch SQL memory manager latch SQL memory manager workarea list latch undo global data buffer is not pinned count bytes received via SQL*Net from client bytes sent via SQL*Net to client calls to get snapshot scn: kcmgss calls to kcmgas consistent gets - examination CPU used by this session CPU used when call started cursor authentications db block changes dirty buffers inspected enqueue releases enqueue requests free buffer inspected free buffer requested hot buffers moved to head of LRU index scans kdiixs1 messages sent no work - consistent read gets opened cursors cumulative parse time cpu March 04, 2005

24 / 72

Value - -------------L 4 L 2 L 1,641 L 15 L 4 L 3 L 10 L 4 L 2 L 58 L 6 L 25 L 4 L 2 L 18 L 2 L 17 L 1 L 1 L 258 L 62 L 1 L 71 L 6 S 2 S 1,191 S 709 S 8 S 4 S 1 S 11 S 11 S 1 S 4,421 S 538 S 2 S 4 S 541 S 537 S 222 S 9 S 4 S 7 S 7 S 2 case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment parse time elapsed pinned buffers inspected recursive calls recursive cpu usage redo entries shared hash latch upgrades - no wait SQL*Net roundtrips to/from client table scans (short tables) user calls workarea executions - optimal write clones created in foreground

S S S S S S S S S S S

13 3 5 2 2,189 10 5 1 8 5 4

buffer pool cache buffers chains cache buffers lru chain enqueue hash chains enqueues library cache library cache pin redo allocation redo copy row cache enqueue latch row cache objects shared pool

L L L L L L L L L L L L

0 16,101 1,434 12 7 208 100 2,217 2,209 120 123 180

execute count parse count (hard) parse count (total) redo size table fetch by rowid table scan blocks gotten table scan rows gotten

S S S S S S S

7 2 7 638,912 0 1 1

buffer is pinned count consistent gets db block gets physical reads physical writes session logical reads sorts (disk) sorts (memory) sorts (rows)

S S S S S S S S S

2 20 2,840 498 0 2,860 0 5 6,405

elapsed time (centiseconds)

151

25 / 72

Actual Plan for Scenario UPDATE:ANSWER2 March 04, 2005

case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment STAT STAT STAT STAT STAT STAT STAT STAT

#1 #1 #1 #1 #1 #1 #1 #1

id=1 id=2 id=3 id=4 id=5 id=6 id=7 id=8

26 / 72

SQL> SQL> SQL> SQL> @hrank update Scenario ranking for workspace update by elapsed time Elapsed Time (secs) --------------------1.510000 2.340000 6.210000

Scenario Name -------------------------------------------------------------------------------ANSWER2 ANSWER1 PROBLEM

SQL> SQL> @diff List of values (First workspace) -----------------------------------------------------------UPDATE First workspace [UPDATE]: List of values (First scenario) -----------------------------------------------------------ANSWER1 ANSWER2 PROBLEM First scenario [PROBLEM]: List of values (Second workspace) -----------------------------------------------------------UPDATE Second workspace [UPDATE]:

March 04, 2005

case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment

27 / 72

List of values (Second scenario) -----------------------------------------------------------ANSWER1 ANSWER2 PROBLEM Second scenario [ANSWER1]: answer2 Statistic Name Statistic Type (S=stat, L=Latch) ---------------------------------------active checkpoint queue latch cache buffer handles channel operations parent latch checkpoint queue latch Consistent RBA dml lock allocation FIB s.o chain latch FOB s.o list latch hash table column usage latch lgwr LWN SCN library cache load lock library cache pin allocation list of block allocation messages mostly latch-free SCN multiblock read objects post/wait queue redo writing session timer sim partition latch simulator hash latch simulator lru latch SQL memory manager latch SQL memory manager workarea list latch transaction allocation undo global data active txn count during cleanout buffer is not pinned count bytes received via SQL*Net from client calls to get snapshot scn: kcmgss calls to kcmgas change write time cleanout - number of ktugct calls cluster key scan block gets cluster key scans commit cleanouts March 04, 2005

L L L L L L L L L L L L L L L L L L L L L L L L L L S S S S S S S S S S

UPDATE UPDATE PROBLEM ANSWER2 Difference -------------- -------------- -------------10 4 -6 54 0 -54 4 2 -2 6,935 1,641 -5,294 66 4 -62 7 3 -4 4 0 -4 4 0 -4 4 10 6 66 4 -62 0 2 2 90 58 -32 7 6 -1 258 25 -233 66 4 -62 48 0 -48 38 2 -36 216 18 -198 2 1 -1 20 1 -19 5,867 258 -5,609 206 62 -144 2 1 -1 134 71 -63 1 0 -1 693 6 -687 2 0 -2 244 2 -242 1,123 1,191 68 340 8 -332 542 4 -538 65 0 -65 150 0 -150 4 0 -4 2 0 -2 452 0 -452 case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment

28 / 72

commit cleanouts successfully completed commit txn count during cleanout consistent changes consistent gets - examination CPU used by this session CPU used when call started CR blocks created cursor authentications data blocks consistent reads - undo records applied

S S S S S S S S S

452 150 2 155 175 175 2 0 2

0 0 0 1 11 11 0 1 0

-452 -150 -2 -154 -164 -164 -2 1 -2

db block changes deferred (CURRENT) block cleanout applications

S S

177,204 2

4,421 0

-172,783 -2

dirty buffers inspected enqueue releases enqueue requests free buffer inspected free buffer requested hot buffers moved to head of LRU immediate (CURRENT) block cleanout applications

S S S S S S S

2,309 213 215 2,819 2,339 139 450

538 2 4 541 537 222 0

-1,771 -211 -211 -2,278 -1,802 83 -450

index fetch by key index scans kdiixs1 leaf node splits messages sent no work - consistent read gets opened cursors cumulative parse time cpu parse time elapsed pinned buffers inspected prefetched blocks prefetched blocks aged out before use recursive calls recursive cpu usage redo buffer allocation retries redo entries redo log space requests redo log space wait time redo ordering marks rollbacks only - consistent read gets session pga memory shared hash latch upgrades - no wait switch current to new buffer table scans (long tables) table scans (short tables)

S S S S S S S S S S S S S S S S S S S S S S S S

2 11 150 63 231 18 0 0 60 176 57 121 0 2 88,288 2 41 132 2 16,336 11 150 1 0

0 9 0 4 7 7 2 13 3 0 0 5 2 0 2,189 0 0 0 0 0 10 0 0 1

-2 -2 -150 -59 -224 -11 2 13 -57 -176 -57 -116 2 -2 -86,099 -2 -41 -132 -2 -16,336 -1 -150 -1 1

March 04, 2005

case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment

29 / 72

workarea executions - optimal write clones created in foreground

S S

0 107

5 4

5 -103

buffer pool cache buffers chains cache buffers lru chain enqueue hash chains enqueues library cache library cache pin redo allocation redo copy row cache enqueue latch row cache objects shared pool

L L L L L L L L L L L L

0 480,644 6,850 444 126 282 178 88,471 88,333 204 219 193

0 16,101 1,434 12 7 208 100 2,217 2,209 120 123 180

0 -464,543 -5,416 -432 -119 -74 -78 -86,254 -86,124 -84 -96 -13

execute count parse count (hard) parse count (total) redo size table fetch by rowid table scan blocks gotten table scan rows gotten

S S S S S S S

18 2 18 22,887,828 11 207 100,000

7 2 7 638,912 0 1 1

-11 0 -11 -22,248,916 -11 -206 -99,999

buffer is pinned count consistent gets db block gets physical reads physical writes session logical reads sorts (disk) sorts (memory) sorts (rows)

S S S S S S S S S

0 563 96,896 651 0 97,459 0 0 0

2 20 2,840 498 0 2,860 0 5 6,405

2 -543 -94,056 -153 0 -94,599 0 5 6,405

elapsed time

621

151

-470

elapsed time (in seconds)

6.21

1.51

-4.70

List of values (Show actual 10046 STAT lines) -----------------------------------------------------------YES NO March 04, 2005

case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment

30 / 72

Show actual 10046 STAT lines [YES]:

Actual Plan for Scenario UPDATE:PROBLEM STAT #1 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE (cr=563 r=651 w=0 time=5998399 us)' STAT #1 id=2 cnt=80000 pid=1 pos=1 obj=29237 op='TABLE ACCESS FULL T (cr=211 r=200 w=0 time=480438 us)' Actual Plan for Scenario UPDATE:ANSWER2 STAT STAT STAT STAT STAT STAT STAT STAT

#1 #1 #1 #1 #1 #1 #1 #1

id=1 id=2 id=3 id=4 id=5 id=6 id=7 id=8

List of values (Show EXPLAIN PLAN output) -----------------------------------------------------------YES NO Show EXPLAIN PLAN output [YES]: ========================================================================== UPDATE:PROBLEM -------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost | -------------------------------------------------------------------| 0 | UPDATE STATEMENT | | 33333 | 99999 | 22 | | 1 | UPDATE | T | | | | |* 2 | TABLE ACCESS FULL | T | 33333 | 99999 | 22 | -------------------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------2 - filter("T"."STATUS"=2) Note: cpu costing is off

March 04, 2005

case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment

31 / 72

========================================================================== UPDATE:ANSWER2 ---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost | ---------------------------------------------------------------------------| 0 | UPDATE STATEMENT | | 83333 | 488K| 26 | | 1 | UPDATE | T | | | | | 2 | NESTED LOOPS | | 83333 | 488K| 26 | | 3 | VIEW | VW_NSO_1 | 5 | 15 | 25 | | 4 | MINUS | | | | | | 5 | SORT UNIQUE | | 5 | 5 | | | 6 | INDEX FULL SCAN | SYS_IOT_TOP_29235 | 5 | 5 | 1 | | 7 | TABLE ACCESS FULL| DUAL | 8168 | | 11 | |* 8 | INDEX RANGE SCAN | T_N3 | 16667 | 50001 | | ---------------------------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------8 - access("T"."STATUS"=2 AND "T"."FLAG"="VW_NSO_1"."$nso_col_1") Note: cpu costing is off ========================================================================== SQL> SQL> @diff List of values (First workspace) -----------------------------------------------------------UPDATE First workspace [UPDATE]: List of values (First scenario) -----------------------------------------------------------ANSWER1 ANSWER2 PROBLEM First scenario [PROBLEM]: answer1 List of values (Second workspace) -----------------------------------------------------------UPDATE

March 04, 2005

case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment

32 / 72

Second workspace [UPDATE]: List of values (Second scenario) -----------------------------------------------------------ANSWER1 ANSWER2 PROBLEM Second scenario [ANSWER2]: Statistic Name Statistic Type (S=stat, L=Latch) ---------------------------------------checkpoint queue latch Consistent RBA hash table column usage latch lgwr LWN SCN library cache load lock library cache pin allocation messages mostly latch-free SCN multiblock read objects post/wait queue redo writing session timer sim partition latch simulator hash latch simulator lru latch SQL memory manager workarea list latch undo global data buffer is not pinned count bytes received via SQL*Net from client calls to get snapshot scn: kcmgss calls to kcmgas consistent gets - examination CPU used by this session CPU used when call started cursor authentications db block changes dirty buffers inspected enqueue releases enqueue requests free buffer inspected free buffer requested hot buffers moved to head of LRU index scans kdiixs1 March 04, 2005

L L L L L L L L L L L L L L L L L S S S S S S S S S S S S S S S S

UPDATE UPDATE ANSWER1 ANSWER2 Difference -------------- -------------- -------------2,357 1,641 -716 5 4 -1 6 10 4 5 4 -1 0 2 2 42 58 16 31 25 -6 5 4 -1 48 0 -48 4 2 -2 21 18 -3 0 1 1 2 1 -1 464 258 -206 91 62 -29 67 71 4 10 6 -4 210 2 -208 1,142 1,191 49 9 8 -1 178 4 -174 0 1 1 33 11 -22 33 11 -22 0 1 1 16,111 4,421 -11,690 835 538 -297 4 2 -2 6 4 -2 898 541 -357 981 537 -444 365 222 -143 1 9 8 case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment

33 / 72

messages sent no work - consistent read gets opened cursors cumulative parse time cpu parse time elapsed pinned buffers inspected prefetched blocks prefetched blocks aged out before use recursive calls recursive cpu usage redo entries session pga memory shared hash latch upgrades - no wait switch current to new buffer table scans (long tables) table scans (short tables) workarea executions - optimal write clones created in foreground

S S S S S S S S S S S S S S S S S S

6 209 6 0 0 36 181 25 8 0 8,011 189,544 1 166 1 0 0 12

4 7 7 2 13 3 0 0 5 2 2,189 0 10 0 0 1 5 4

-2 -202 1 2 13 -33 -181 -25 -3 2 -5,822 -189,544 9 -166 -1 1 5 -8

buffer pool cache buffers chains cache buffers lru chain enqueue hash chains enqueues library cache library cache pin redo allocation redo copy row cache enqueue latch row cache objects shared pool

L L L L L L L L L L L L

0 65,004 2,507 16 11 150 82 8,038 8,028 58 61 136

0 16,101 1,434 12 7 208 100 2,217 2,209 120 123 180

0 -48,903 -1,073 -4 -4 58 18 -5,821 -5,819 62 62 44

execute count parse count (hard) parse count (total) redo size table fetch by rowid table scan blocks gotten table scan rows gotten

S S S S S S S

6 2 6 1,633,416 1 207 100,000

7 2 7 638,912 0 1 1

1 0 1 -994,504 -1 -206 -99,999

buffer is pinned count consistent gets db block gets physical reads physical writes

S S S S S

0 215 14,585 717 0

2 20 2,840 498 0

2 -195 -11,745 -219 0

March 04, 2005

case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment

34 / 72

session logical reads sorts (disk) sorts (memory) sorts (rows)

S S S S

14,800 0 0 0

2,860 0 5 6,405

-11,940 0 5 6,405

elapsed time

234

151

-83

elapsed time (in seconds)

2.34

1.51

-0.83

List of values (Show actual 10046 STAT lines) -----------------------------------------------------------YES NO Show actual 10046 STAT lines [YES]:

#1 #1 #1 #1 #1 #1 #1 #1

id=1 id=2 id=3 id=4 id=5 id=6 id=7 id=8

case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment

35 / 72

-------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost | -------------------------------------------------------------------| 0 | UPDATE STATEMENT | | 16667 | 50001 | 22 | | 1 | UPDATE | T | | | | |* 2 | TABLE ACCESS FULL | T | 16667 | 50001 | 22 | -------------------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------2 - filter("T"."STATUS"=2 AND "T"."FLAG"<>'B') Note: cpu costing is off ========================================================================== UPDATE:ANSWER2 ---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost | ---------------------------------------------------------------------------| 0 | UPDATE STATEMENT | | 83333 | 488K| 26 | | 1 | UPDATE | T | | | | | 2 | NESTED LOOPS | | 83333 | 488K| 26 | | 3 | VIEW | VW_NSO_1 | 5 | 15 | 25 | | 4 | MINUS | | | | | | 5 | SORT UNIQUE | | 5 | 5 | | | 6 | INDEX FULL SCAN | SYS_IOT_TOP_29235 | 5 | 5 | 1 | | 7 | TABLE ACCESS FULL| DUAL | 8168 | | 11 | |* 8 | INDEX RANGE SCAN | T_N3 | 16667 | 50001 | | ---------------------------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------8 - access("T"."STATUS"=2 AND "T"."FLAG"="VW_NSO_1"."$nso_col_1") Note: cpu costing is off ========================================================================== SQL> SQL> SQL> get test_ans3 1 update t 2 set flag='B' 3 where status=2 4* and flag in (select flag from flags where flag != 'B') March 04, 2005

case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment SQL> SQL> SQL> @dodml Enter .sql file name (without extension): Enter the workspace name: Enter the scenario name: Display results of SQL (ON/OFF)?:

36 / 72

test_ans3 update answer3 off

Capturing statistics information... ================================================================= Name of 10046 trace file: hotsos_ora_2480.trc ================================================================= Explain Plan for Scenario update:answer3 --------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost | --------------------------------------------------------------------------| 0 | UPDATE STATEMENT | | 33333 | 130K| 2 | | 1 | UPDATE | T | | | | | 2 | NESTED LOOPS | | 33333 | 130K| 2 | |* 3 | INDEX FULL SCAN | SYS_IOT_TOP_29235 | 4 | 4 | 1 | |* 4 | INDEX RANGE SCAN | T_N3 | 8333 | 24999 | | --------------------------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------3 - filter("FLAGS"."FLAG"<>'B') 4 - access("T"."STATUS"=2 AND "T"."FLAG"="FLAGS"."FLAG") Note: cpu costing is off ================================================================= Name of 10053 trace file: hotsos_ora_1200.trc ================================================================= Actual Plan for Scenario update:answer3 STAT STAT STAT STAT SQL> SQL> SQL>

#1 #1 #1 #1

id=1 id=2 id=3 id=4

cnt=0 pid=0 pos=1 obj=0 op='UPDATE (cr=12 r=212 w=0 time=666891 us)' cnt=1600 pid=1 pos=1 obj=0 op='NESTED LOOPS (cr=12 r=2 w=0 time=29875 us)' cnt=4 pid=2 pos=1 obj=29236 op='INDEX FULL SCAN SYS_IOT_TOP_29235 (cr=1 r=1 w=0 time=13743 us)' cnt=1600 pid=2 pos=2 obj=29241 op='INDEX RANGE SCAN T_N3 (cr=11 r=1 w=0 time=11135 us)'

@lsscstat

March 04, 2005

case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment

37 / 72

List of values (Workspace) -----------------------------------------------------------UPDATE Workspace [UPDATE]: List of values (Scenario) -----------------------------------------------------------ANSWER1 ANSWER2 ANSWER3 PROBLEM Scenario [ANSWER2]: answer3 Statistic Name Statistic Type (S=stat, L=Latch) ---------------------------------------active checkpoint queue latch checkpoint queue latch child cursor hash table Consistent RBA dml lock allocation hash table column usage latch lgwr LWN SCN library cache pin allocation list of block allocation messages mostly latch-free SCN redo writing session idle bit simulator hash latch simulator lru latch SQL memory manager workarea list latch undo global data buffer is not pinned count bytes received via SQL*Net from client bytes sent via SQL*Net to client calls to get snapshot scn: kcmgss calls to kcmgas consistent gets - examination CPU used by this session CPU used when call started db block changes dirty buffers inspected enqueue releases March 04, 2005

Value - -------------L 4 L 910 L 14 L 3 L 3 L 15 L 3 L 42 L 6 L 22 L 3 L 13 L 16 L 203 L 29 L 4 L 6 S 1 S 1,181 S 709 S 3 S 1 S 1 S 12 S 12 S 4,421 S 2 S 2 case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment enqueue requests free buffer inspected free buffer requested hot buffers moved to head of LRU index scans kdiixs1 messages sent no work - consistent read gets opened cursors cumulative redo entries shared hash latch upgrades - no wait SQL*Net roundtrips to/from client user calls workarea executions - optimal write clones created in foreground

S S S S S S S S S S S S S S

4 2 247 303 5 3 6 5 2,189 6 5 8 4 1

buffer pool cache buffers chains cache buffers lru chain enqueue hash chains enqueues library cache library cache pin redo allocation redo copy row cache enqueue latch row cache objects shared pool

L L L L L L L L L L L L

0 13,914 504 10 4 156 78 2,212 2,206 74 77 152

execute count parse count (hard) parse count (total) redo size table fetch by rowid table scan blocks gotten table scan rows gotten

S S S S S S S

5 2 5 638,912 0 0 0

buffer is pinned count consistent gets db block gets physical reads physical writes session logical reads sorts (disk) sorts (memory) sorts (rows)

S S S S S S S S S

2 12 2,840 212 0 2,852 0 2 6,400

elapsed time (centiseconds)

102

March 04, 2005

38 / 72

case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment

39 / 72

Actual Plan for Scenario UPDATE:ANSWER3 STAT STAT STAT STAT

#1 #1 #1 #1

id=1 id=2 id=3 id=4

SQL> SQL> SQL> SQL> @hrank update Scenario ranking for workspace update by elapsed time Elapsed Time (secs) --------------------1.020000 1.510000 2.340000 6.210000

Scenario Name -------------------------------------------------------------------------------ANSWER3 ANSWER2 ANSWER1 PROBLEM

SQL> @diff List of values (First workspace) -----------------------------------------------------------UPDATE First workspace [UPDATE]: List of values (First scenario) -----------------------------------------------------------ANSWER1 ANSWER2 ANSWER3 PROBLEM First scenario [ANSWER1]: problem List of values (Second workspace) -----------------------------------------------------------UPDATE Second workspace [UPDATE]:

March 04, 2005

case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment

40 / 72

List of values (Second scenario) -----------------------------------------------------------ANSWER1 ANSWER2 ANSWER3 PROBLEM Second scenario [ANSWER2]: answer3 Statistic Name Statistic Type (S=stat, L=Latch) ---------------------------------------active checkpoint queue latch cache buffer handles channel operations parent latch checkpoint queue latch child cursor hash table Consistent RBA dml lock allocation FIB s.o chain latch FOB s.o list latch hash table column usage latch lgwr LWN SCN library cache pin allocation list of block allocation messages mostly latch-free SCN multiblock read objects post/wait queue redo writing session allocation session idle bit session timer sim partition latch simulator hash latch simulator lru latch SQL memory manager latch SQL memory manager workarea list latch transaction allocation undo global data active txn count during cleanout buffer is not pinned count bytes received via SQL*Net from client calls to get snapshot scn: kcmgss calls to kcmgas change write time March 04, 2005

L L L L L L L L L L L L L L L L L L L L L L L L L L L L S S S S S S

UPDATE UPDATE PROBLEM ANSWER3 Difference -------------- -------------- -------------10 4 -6 54 0 -54 4 0 -4 6,935 910 -6,025 15 14 -1 66 3 -63 7 3 -4 4 0 -4 4 0 -4 4 15 11 66 3 -63 90 42 -48 7 6 -1 258 22 -236 66 3 -63 48 0 -48 38 0 -38 216 13 -203 2 0 -2 17 16 -1 2 0 -2 20 0 -20 5,867 203 -5,664 206 29 -177 2 0 -2 134 4 -130 1 0 -1 693 6 -687 2 0 -2 244 1 -243 1,123 1,181 58 340 3 -337 542 1 -541 65 0 -65 case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment

41 / 72

cleanout - number of ktugct calls cluster key scan block gets cluster key scans commit cleanouts commit cleanouts successfully completed commit txn count during cleanout consistent changes consistent gets - examination CPU used by this session CPU used when call started CR blocks created data blocks consistent reads - undo records applied

S S S S S S S S S S S S

150 4 2 452 452 150 2 155 175 175 2 2

0 0 0 0 0 0 0 1 12 12 0 0

-150 -4 -2 -452 -452 -150 -2 -154 -163 -163 -2 -2

db block changes deferred (CURRENT) block cleanout applications

S S

177,204 2

4,421 0

-172,783 -2

dirty buffers inspected enqueue releases enqueue requests free buffer inspected free buffer requested hot buffers moved to head of LRU immediate (CURRENT) block cleanout applications

S S S S S S S

2,309 213 215 2,819 2,339 139 450

2 2 4 2 247 303 0

-2,307 -211 -211 -2,817 -2,092 164 -450

index fetch by key index scans kdiixs1 leaf node splits messages sent no work - consistent read gets opened cursors cumulative pinned buffers inspected prefetched blocks prefetched blocks aged out before use recursive calls redo buffer allocation retries redo entries redo log space requests redo log space wait time redo ordering marks rollbacks only - consistent read gets session pga memory shared hash latch upgrades - no wait switch current to new buffer table scans (long tables) workarea executions - optimal

S S S S S S S S S S S S S S S S S S S S S

2 11 150 63 231 18 60 176 57 121 2 88,288 2 41 132 2 16,336 11 150 1 0

0 5 0 3 6 5 0 0 0 0 0 2,189 0 0 0 0 0 6 0 0 4

-2 -6 -150 -60 -225 -13 -60 -176 -57 -121 -2 -86,099 -2 -41 -132 -2 -16,336 -5 -150 -1 4

March 04, 2005

case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment

42 / 72

write clones created in foreground

107

-106

buffer pool cache buffers chains cache buffers lru chain enqueue hash chains enqueues library cache library cache pin redo allocation redo copy row cache enqueue latch row cache objects shared pool

L L L L L L L L L L L L

0 480,644 6,850 444 126 282 178 88,471 88,333 204 219 193

0 13,914 504 10 4 156 78 2,212 2,206 74 77 152

0 -466,730 -6,346 -434 -122 -126 -100 -86,259 -86,127 -130 -142 -41

execute count parse count (hard) parse count (total) redo size table fetch by rowid table scan blocks gotten table scan rows gotten

S S S S S S S

18 2 18 22,887,828 11 207 100,000

5 2 5 638,912 0 0 0

-13 0 -13 -22,248,916 -11 -207 -100,000

buffer is pinned count consistent gets db block gets physical reads physical writes session logical reads sorts (disk) sorts (memory) sorts (rows)

S S S S S S S S S

0 563 96,896 651 0 97,459 0 0 0

2 12 2,840 212 0 2,852 0 2 6,400

2 -551 -94,056 -439 0 -94,607 0 2 6,400

elapsed time

621

102

-519

elapsed time (in seconds)

6.21

1.02

-5.19

List of values (Show actual 10046 STAT lines) -----------------------------------------------------------YES NO

March 04, 2005

case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment

43 / 72

Show actual 10046 STAT lines [YES]:

Actual Plan for Scenario UPDATE:PROBLEM STAT #1 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE (cr=563 r=651 w=0 time=5998399 us)' STAT #1 id=2 cnt=80000 pid=1 pos=1 obj=29237 op='TABLE ACCESS FULL T (cr=211 r=200 w=0 time=480438 us)' Actual Plan for Scenario UPDATE:ANSWER3 STAT STAT STAT STAT

#1 #1 #1 #1

id=1 id=2 id=3 id=4

case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment

44 / 72

--------------------------------------------------------------------------| 0 | UPDATE STATEMENT | | 33333 | 130K| 2 | | 1 | UPDATE | T | | | | | 2 | NESTED LOOPS | | 33333 | 130K| 2 | |* 3 | INDEX FULL SCAN | SYS_IOT_TOP_29235 | 4 | 4 | 1 | |* 4 | INDEX RANGE SCAN | T_N3 | 8333 | 24999 | | --------------------------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------3 - filter("FLAGS"."FLAG"<>'B') 4 - access("T"."STATUS"=2 AND "T"."FLAG"="FLAGS"."FLAG") Note: cpu costing is off ========================================================================== SQL> SQL> SQL> @diff List of values (First workspace) -----------------------------------------------------------UPDATE First workspace [UPDATE]: List of values (First scenario) -----------------------------------------------------------ANSWER1 ANSWER2 ANSWER3 PROBLEM First scenario [PROBLEM]: answer2 List of values (Second workspace) -----------------------------------------------------------UPDATE Second workspace [UPDATE]: List of values (Second scenario) -----------------------------------------------------------ANSWER1 March 04, 2005

case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment

45 / 72

ANSWER2 ANSWER3 PROBLEM Second scenario [ANSWER3]: Statistic Name Statistic Type (S=stat, L=Latch) ---------------------------------------channel operations parent latch checkpoint queue latch child cursor hash table Consistent RBA hash table column usage latch lgwr LWN SCN library cache load lock library cache pin allocation messages mostly latch-free SCN post/wait queue redo writing session allocation session idle bit session timer sim partition latch simulator hash latch simulator lru latch SQL memory manager latch SQL memory manager workarea list latch buffer is not pinned count bytes received via SQL*Net from client calls to get snapshot scn: kcmgss calls to kcmgas CPU used by this session CPU used when call started cursor authentications dirty buffers inspected free buffer inspected free buffer requested hot buffers moved to head of LRU index scans kdiixs1 messages sent no work - consistent read gets opened cursors cumulative parse time cpu parse time elapsed pinned buffers inspected March 04, 2005

L L L L L L L L L L L L L L L L L L L L S S S S S S S S S S S S S S S S S S

UPDATE UPDATE ANSWER2 ANSWER3 Difference -------------- -------------- -------------2 0 -2 1,641 910 -731 15 14 -1 4 3 -1 10 15 5 4 3 -1 2 0 -2 58 42 -16 25 22 -3 4 3 -1 2 0 -2 18 13 -5 2 0 -2 17 16 -1 1 0 -1 1 0 -1 258 203 -55 62 29 -33 1 0 -1 71 4 -67 2 1 -1 1,191 1,181 -10 8 3 -5 4 1 -3 11 12 1 11 12 1 1 0 -1 538 2 -536 541 2 -539 537 247 -290 222 303 81 9 5 -4 4 3 -1 7 6 -1 7 5 -2 2 0 -2 13 0 -13 3 0 -3 case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment

46 / 72

recursive calls recursive cpu usage shared hash latch upgrades - no wait table scans (short tables) workarea executions - optimal write clones created in foreground

S S S S S S

5 2 10 1 5 4

0 0 6 0 4 1

-5 -2 -4 -1 -1 -3

buffer pool cache buffers chains cache buffers lru chain enqueue hash chains enqueues library cache library cache pin redo allocation redo copy row cache enqueue latch row cache objects shared pool

L L L L L L L L L L L L

0 16,101 1,434 12 7 208 100 2,217 2,209 120 123 180

0 13,914 504 10 4 156 78 2,212 2,206 74 77 152

0 -2,187 -930 -2 -3 -52 -22 -5 -3 -46 -46 -28

execute count parse count (hard) parse count (total) redo size table fetch by rowid table scan blocks gotten table scan rows gotten

S S S S S S S

7 2 7 638,912 0 1 1

5 2 5 638,912 0 0 0

-2 0 -2 0 0 -1 -1

buffer is pinned count consistent gets db block gets physical reads physical writes session logical reads sorts (disk) sorts (memory) sorts (rows)

S S S S S S S S S

2 20 2,840 498 0 2,860 0 5 6,405

2 12 2,840 212 0 2,852 0 2 6,400

0 -8 0 -286 0 -8 0 -3 -5

elapsed time

151

102

-49

elapsed time (in seconds)

March 04, 2005

1.51

1.02

-0.49

case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment

47 / 72

List of values (Show actual 10046 STAT lines) -----------------------------------------------------------YES NO Show actual 10046 STAT lines [YES]:

Actual Plan for Scenario UPDATE:ANSWER2 STAT STAT STAT STAT STAT STAT STAT STAT

#1 #1 #1 #1 #1 #1 #1 #1

id=1 id=2 id=3 id=4 id=5 id=6 id=7 id=8

Actual Plan for Scenario UPDATE:ANSWER3 STAT STAT STAT STAT

#1 #1 #1 #1

id=1 id=2 id=3 id=4

case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment

48 / 72

| 6 | INDEX FULL SCAN | SYS_IOT_TOP_29235 | 5 | 5 | 1 | | 7 | TABLE ACCESS FULL| DUAL | 8168 | | 11 | |* 8 | INDEX RANGE SCAN | T_N3 | 16667 | 50001 | | ---------------------------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------8 - access("T"."STATUS"=2 AND "T"."FLAG"="VW_NSO_1"."$nso_col_1") Note: cpu costing is off ========================================================================== UPDATE:ANSWER3 --------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost | --------------------------------------------------------------------------| 0 | UPDATE STATEMENT | | 33333 | 130K| 2 | | 1 | UPDATE | T | | | | | 2 | NESTED LOOPS | | 33333 | 130K| 2 | |* 3 | INDEX FULL SCAN | SYS_IOT_TOP_29235 | 4 | 4 | 1 | |* 4 | INDEX RANGE SCAN | T_N3 | 8333 | 24999 | | --------------------------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------3 - filter("FLAGS"."FLAG"<>'B') 4 - access("T"."STATUS"=2 AND "T"."FLAG"="FLAGS"."FLAG") Note: cpu costing is off ========================================================================== SQL> SQL> SQL> analyze table t compute statistics for columns status ; Table analyzed.

SQL> SQL> SQL> get test_ans1 1 update t 2 set flag='B' 3 where status=2 March 04, 2005

case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment 4* and flag != 'B' SQL> SQL> @dodml Enter .sql file name (without extension): Enter the workspace name: Enter the scenario name: Display results of SQL (ON/OFF)?:

49 / 72

test_ans1 update answer1_hist off

Capturing statistics information... ================================================================= Name of 10046 trace file: hotsos_ora_2004.trc ================================================================= Explain Plan for Scenario update:answer1_hist -------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost | -------------------------------------------------------------------| 0 | UPDATE STATEMENT | | 40000 | 117K| 22 | | 1 | UPDATE | T | | | | |* 2 | TABLE ACCESS FULL | T | 40000 | 117K| 22 | -------------------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------2 - filter("T"."STATUS"=2 AND "T"."FLAG"<>'B') Note: cpu costing is off ================================================================= Name of 10053 trace file: hotsos_ora_1088.trc ================================================================= Actual Plan for Scenario update:answer1_hist STAT #1 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE (cr=215 r=364 w=0 time=1577867 us)' STAT #1 id=2 cnt=1600 pid=1 pos=1 obj=29237 op='TABLE ACCESS FULL T (cr=211 r=4 w=0 time=72003 us)' SQL> SQL> SQL> @lsscstat List of values (Workspace) -----------------------------------------------------------UPDATE Workspace [UPDATE]: March 04, 2005

case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment

50 / 72

List of values (Scenario) -----------------------------------------------------------ANSWER1 ANSWER1_HIST ANSWER2 ANSWER3 PROBLEM Scenario [ANSWER3]: answer1_hist Statistic Name Statistic Type (S=stat, L=Latch) ---------------------------------------active checkpoint queue latch channel operations parent latch checkpoint queue latch child cursor hash table Consistent RBA dml lock allocation hash table column usage latch lgwr LWN SCN library cache pin allocation list of block allocation messages mostly latch-free SCN multiblock read objects post/wait queue redo writing session allocation session idle bit session timer sim partition latch simulator hash latch simulator lru latch SQL memory manager latch SQL memory manager workarea list latch undo global data buffer is not pinned count bytes received via SQL*Net from client bytes sent via SQL*Net to client calls to get snapshot scn: kcmgss calls to kcmgas change write time CPU used by this session CPU used when call started March 04, 2005

Value - -------------L 4 L 2 L 2,101 L 15 L 7 L 3 L 6 L 7 L 42 L 5 L 39 L 7 L 2 L 4 L 28 L 2 L 17 L 1 L 1 L 356 L 58 L 1 L 67 L 11 S 210 S 1,142 S 709 S 9 S 209 S 5 S 23 S 23 case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment db block changes dirty buffers inspected enqueue releases enqueue requests free buffer inspected free buffer requested hot buffers moved to head of LRU index scans kdiixs1 messages sent no work - consistent read gets opened cursors cumulative parse time cpu parse time elapsed pinned buffers inspected prefetched blocks recursive calls redo entries shared hash latch upgrades - no wait SQL*Net roundtrips to/from client switch current to new buffer table scans (long tables) user calls write clones created in foreground

S S S S S S S S S S S S S S S S S S S S S S S

16,112 824 4 6 985 658 65 1 5 209 6 1 1 9 2 8 8,011 1 5 153 1 8 56

buffer pool cache buffers chains cache buffers lru chain enqueue hash chains enqueues library cache library cache pin redo allocation redo copy row cache enqueue latch row cache objects shared pool

L L L L L L L L L L L L

0 64,161 1,957 20 15 150 82 8,043 8,029 58 62 130

execute count parse count (hard) parse count (total) redo size table fetch by rowid table scan blocks gotten table scan rows gotten

S S S S S S S

6 2 6 1,633,476 1 207 100,000

buffer is pinned count consistent gets db block gets

S S S

0 215 14,588

March 04, 2005

51 / 72

case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment physical reads physical writes session logical reads sorts (disk) sorts (memory) sorts (rows)

S S S S S S

364 0 14,803 0 0 0

elapsed time (centiseconds)

169

52 / 72

Actual Plan for Scenario UPDATE:ANSWER1_HIST STAT #1 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE (cr=215 r=364 w=0 time=1577867 us)' STAT #1 id=2 cnt=1600 pid=1 pos=1 obj=29237 op='TABLE ACCESS FULL T (cr=211 r=4 w=0 time=72003 us)' SQL> SQL> SQL> SQL> get test_ans2 1 update t 2 set flag='B' 3 where status=2 4* and flag in (select flag from flags minus select 'B' from dual ) SQL> SQL> @dodml Enter .sql file name (without extension): test_ans2 Enter the workspace name: update Enter the scenario name: answer2_hist Display results of SQL (ON/OFF)?: off Capturing statistics information... ================================================================= Name of 10046 trace file: hotsos_ora_812.trc ================================================================= Explain Plan for Scenario update:answer2_hist ---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost | ---------------------------------------------------------------------------| 0 | UPDATE STATEMENT | | 200K| 1171K| 26 | | 1 | UPDATE | T | | | | | 2 | NESTED LOOPS | | 200K| 1171K| 26 | | 3 | VIEW | VW_NSO_1 | 5 | 15 | 25 | | 4 | MINUS | | | | | | 5 | SORT UNIQUE | | 5 | 5 | | March 04, 2005

case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment

53 / 72

| 6 | INDEX FULL SCAN | SYS_IOT_TOP_29235 | 5 | 5 | 1 | | 7 | TABLE ACCESS FULL| DUAL | 8168 | | 11 | |* 8 | INDEX RANGE SCAN | T_N3 | 40000 | 117K| | ---------------------------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------8 - access("T"."STATUS"=2 AND "T"."FLAG"="VW_NSO_1"."$nso_col_1") Note: cpu costing is off ================================================================= Name of 10053 trace file: hotsos_ora_2760.trc ================================================================= Actual Plan for Scenario update:answer2_hist STAT STAT STAT STAT STAT STAT STAT STAT SQL> SQL> SQL>

#1 #1 #1 #1 #1 #1 #1 #1

id=1 id=2 id=3 id=4 id=5 id=6 id=7 id=8

cnt=0 pid=0 pos=1 obj=0 op='UPDATE (cr=15 r=411 w=0 time=1185803 us)' cnt=1600 pid=1 pos=1 obj=0 op='NESTED LOOPS (cr=15 r=2 w=0 time=30175 us)' cnt=4 pid=2 pos=1 obj=0 op='VIEW (cr=4 r=1 w=0 time=13335 us)' cnt=4 pid=3 pos=1 obj=0 op='MINUS (cr=4 r=1 w=0 time=13318 us)' cnt=5 pid=4 pos=1 obj=0 op='SORT UNIQUE (cr=1 r=1 w=0 time=13199 us)' cnt=5 pid=5 pos=1 obj=29236 op='INDEX FULL SCAN SYS_IOT_TOP_29235 (cr=1 r=1 w=0 time=13100 us)' cnt=1 pid=4 pos=2 obj=222 op='TABLE ACCESS FULL DUAL (cr=3 r=0 w=0 time=76 us)' cnt=1600 pid=2 pos=2 obj=29241 op='INDEX RANGE SCAN T_N3 (cr=11 r=1 w=0 time=11444 us)'

@lsscstat

List of values (Workspace) -----------------------------------------------------------UPDATE Workspace [UPDATE]: List of values (Scenario) -----------------------------------------------------------ANSWER1 ANSWER1_HIST ANSWER2 ANSWER2_HIST ANSWER3 PROBLEM Scenario [ANSWER1_HIST]: answer2_hist

March 04, 2005

case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment Statistic Name Statistic Type (S=stat, L=Latch) ---------------------------------------active checkpoint queue latch checkpoint queue latch child cursor hash table Consistent RBA dml lock allocation hash table column usage latch lgwr LWN SCN library cache pin allocation list of block allocation messages mostly latch-free SCN redo writing session idle bit simulator hash latch simulator lru latch SQL memory manager workarea list latch undo global data buffer is not pinned count bytes received via SQL*Net from client bytes sent via SQL*Net to client calls to get snapshot scn: kcmgss calls to kcmgas consistent gets - examination CPU used by this session CPU used when call started db block changes dirty buffers inspected enqueue releases enqueue requests free buffer inspected free buffer requested hot buffers moved to head of LRU index scans kdiixs1 messages sent no work - consistent read gets opened cursors cumulative parse time cpu parse time elapsed pinned buffers inspected redo entries session pga memory max shared hash latch upgrades - no wait SQL*Net roundtrips to/from client table scans (short tables) March 04, 2005

54 / 72

Value - -------------L 2 L 1,113 L 15 L 4 L 3 L 10 L 4 L 46 L 6 L 17 L 4 L 14 L 16 L 206 L 54 L 4 L 7 S 2 S 1,191 S 709 S 6 S 1 S 1 S 20 S 20 S 4,421 S 292 S 2 S 4 S 296 S 446 S 219 S 5 S 2 S 7 S 5 S 2 S 2 S 3 S 2,189 S 131,072 S 6 S 5 S 1 case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment user calls workarea executions - optimal write clones created in foreground

S S S

8 5 1

buffer pool cache buffers chains cache buffers lru chain enqueue hash chains enqueues library cache library cache pin redo allocation redo copy row cache enqueue latch row cache objects shared pool

L L L L L L L L L L L L

0 14,833 972 10 4 173 82 2,215 2,207 100 104 152

execute count parse count (hard) parse count (total) redo size table fetch by rowid table scan blocks gotten table scan rows gotten

S S S S S S S

5 2 5 638,912 0 1 1

buffer is pinned count consistent gets db block gets physical reads physical writes session logical reads sorts (disk) sorts (memory) sorts (rows)

S S S S S S S S S

2 16 2,839 411 0 2,855 0 3 6,405

elapsed time (centiseconds)

141

55 / 72

Actual Plan for Scenario UPDATE:ANSWER2_HIST STAT STAT STAT STAT STAT STAT STAT

#1 #1 #1 #1 #1 #1 #1

id=1 id=2 id=3 id=4 id=5 id=6 id=7

March 04, 2005

case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment

56 / 72

STAT #1 id=8 cnt=1600 pid=2 pos=2 obj=29241 op='INDEX RANGE SCAN T_N3 (cr=11 r=1 w=0 time=11444 us)' SQL> SQL> SQL> SQL> get test_ans3 1 update t 2 set flag='B' 3 where status=2 4* and flag in (select flag from flags where flag != 'B') SQL> SQL> @dodml Enter .sql file name (without extension): test_ans3 Enter the workspace name: update Enter the scenario name: answer3_hist Display results of SQL (ON/OFF)?: off Capturing statistics information... ================================================================= Name of 10046 trace file: hotsos_ora_3228.trc ================================================================= Explain Plan for Scenario update:answer3_hist --------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost | --------------------------------------------------------------------------| 0 | UPDATE STATEMENT | | 80000 | 312K| 2 | | 1 | UPDATE | T | | | | | 2 | NESTED LOOPS | | 80000 | 312K| 2 | |* 3 | INDEX FULL SCAN | SYS_IOT_TOP_29235 | 4 | 4 | 1 | |* 4 | INDEX RANGE SCAN | T_N3 | 20000 | 60000 | | --------------------------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------3 - filter("FLAGS"."FLAG"<>'B') 4 - access("T"."STATUS"=2 AND "T"."FLAG"="FLAGS"."FLAG") Note: cpu costing is off ================================================================= Name of 10053 trace file: hotsos_ora_2200.trc ================================================================= Actual Plan for Scenario update:answer3_hist March 04, 2005

case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment STAT STAT STAT STAT SQL> SQL> SQL>

#1 #1 #1 #1

id=1 id=2 id=3 id=4

57 / 72

cnt=0 pid=0 pos=1 obj=0 op='UPDATE (cr=12 r=174 w=0 time=565098 us)' cnt=1600 pid=1 pos=1 obj=0 op='NESTED LOOPS (cr=12 r=2 w=0 time=25717 us)' cnt=4 pid=2 pos=1 obj=29236 op='INDEX FULL SCAN SYS_IOT_TOP_29235 (cr=1 r=1 w=0 time=9258 us)' cnt=1600 pid=2 pos=2 obj=29241 op='INDEX RANGE SCAN T_N3 (cr=11 r=1 w=0 time=11507 us)'

@lsscstat

List of values (Workspace) -----------------------------------------------------------UPDATE Workspace [UPDATE]: List of values (Scenario) -----------------------------------------------------------ANSWER1 ANSWER1_HIST ANSWER2 ANSWER2_HIST ANSWER3 ANSWER3_HIST PROBLEM Scenario [ANSWER2_HIST]: answer3_hist Statistic Name Statistic Type (S=stat, L=Latch) ---------------------------------------active checkpoint queue latch checkpoint queue latch child cursor hash table Consistent RBA dml lock allocation hash table column usage latch lgwr LWN SCN library cache pin allocation list of block allocation messages mostly latch-free SCN redo writing session idle bit simulator hash latch simulator lru latch SQL memory manager workarea list latch March 04, 2005

Value - -------------L 2 L 694 L 15 L 4 L 3 L 15 L 4 L 40 L 8 L 19 L 4 L 14 L 16 L 177 L 24 L 4 case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment transaction allocation undo global data buffer is not pinned count bytes received via SQL*Net from client bytes sent via SQL*Net to client calls to get snapshot scn: kcmgss change write time consistent gets - examination CPU used by this session CPU used when call started db block changes dirty buffers inspected enqueue releases enqueue requests free buffer inspected free buffer requested hot buffers moved to head of LRU index scans kdiixs1 messages sent no work - consistent read gets opened cursors cumulative parse time cpu parse time elapsed pinned buffers inspected redo entries session pga memory max shared hash latch upgrades - no wait SQL*Net roundtrips to/from client user calls workarea executions - optimal

L L S S S S S S S S S S S S S S S S S S S S S S S S S S S S

2 17 1 1,181 709 3 1 1 14 14 4,421 212 2 4 216 208 441 5 2 6 5 1 2 4 2,189 65,536 6 5 8 4

buffer pool cache buffers chains cache buffers lru chain enqueue hash chains enqueues library cache library cache pin redo allocation redo copy row cache enqueue latch row cache objects shared pool

L L L L L L L L L L L L

0 14,332 636 16 4 151 76 2,238 2,230 74 78 140

execute count parse count (hard) parse count (total) redo size

S S S S

5 2 5 638,912

March 04, 2005

58 / 72

case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment table fetch by rowid table scan blocks gotten table scan rows gotten

S S S

0 0 0

buffer is pinned count consistent gets db block gets physical reads physical writes session logical reads sorts (disk) sorts (memory) sorts (rows)

S S S S S S S S S

2 12 2,839 174 0 2,851 0 2 6,400

elapsed time (centiseconds)

59 / 72

Actual Plan for Scenario UPDATE:ANSWER3_HIST STAT STAT STAT STAT

#1 #1 #1 #1

id=1 id=2 id=3 id=4

SQL> SQL> SQL> SQL> @hrank update Scenario ranking for workspace update by elapsed time Elapsed Time (secs) --------------------0.680000 1.020000 1.410000 1.510000 1.690000 2.340000 6.210000

Scenario Name -------------------------------------------------------------------------------ANSWER3_HIST ANSWER3 ANSWER2_HIST ANSWER2 ANSWER1_HIST ANSWER1 PROBLEM

SQL> SQL> SQL> @diff List of values (First workspace) -----------------------------------------------------------March 04, 2005

case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment

60 / 72

UPDATE First workspace [UPDATE]: List of values (First scenario) -----------------------------------------------------------ANSWER1 ANSWER1_HIST ANSWER2 ANSWER2_HIST ANSWER3 ANSWER3_HIST PROBLEM First scenario [ANSWER2]: answer1 List of values (Second workspace) -----------------------------------------------------------UPDATE Second workspace [UPDATE]: List of values (Second scenario) -----------------------------------------------------------ANSWER1 ANSWER1_HIST ANSWER2 ANSWER2_HIST ANSWER3 ANSWER3_HIST PROBLEM Second scenario [ANSWER3]: answer1_hist Statistic Name Statistic Type (S=stat, L=Latch) ---------------------------------------checkpoint queue latch Consistent RBA lgwr LWN SCN list of block allocation messages mostly latch-free SCN multiblock read objects March 04, 2005

L L L L L L L

UPDATE UPDATE ANSWER1 ANSWER1_HIST Difference -------------- -------------- -------------2,357 2,101 -256 5 7 2 5 7 2 6 5 -1 31 39 8 5 7 2 48 2 -46 case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment

61 / 72

redo writing session timer sim partition latch simulator hash latch simulator lru latch undo global data calls to kcmgas change write time CPU used by this session CPU used when call started db block changes dirty buffers inspected free buffer inspected free buffer requested hot buffers moved to head of LRU messages sent parse time cpu parse time elapsed pinned buffers inspected prefetched blocks prefetched blocks aged out before use session pga memory switch current to new buffer write clones created in foreground

L L L L L L S S S S S S S S S S S S S S S S S S

21 0 2 464 91 10 178 0 33 33 16,111 835 898 981 365 6 0 0 36 181 25 189,544 166 12

28 1 1 356 58 11 209 5 23 23 16,112 824 985 658 65 5 1 1 9 2 0 0 153 56

7 1 -1 -108 -33 1 31 5 -10 -10 1 -11 87 -323 -300 -1 1 1 -27 -179 -25 -189,544 -13 44

buffer pool cache buffers chains cache buffers lru chain enqueue hash chains enqueues library cache library cache pin redo allocation redo copy row cache enqueue latch row cache objects shared pool

L L L L L L L L L L L L

0 65,004 2,507 16 11 150 82 8,038 8,028 58 61 136

0 64,161 1,957 20 15 150 82 8,043 8,029 58 62 130

0 -843 -550 4 4 0 0 5 1 0 1 -6

execute count parse count (hard) parse count (total) redo size table fetch by rowid table scan blocks gotten table scan rows gotten

S S S S S S S

6 2 6 1,633,416 1 207 100,000

6 2 6 1,633,476 1 207 100,000

0 0 0 60 0 0 0

March 04, 2005

case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment

62 / 72

buffer is pinned count consistent gets db block gets physical reads physical writes session logical reads sorts (disk) sorts (memory) sorts (rows)

S S S S S S S S S

0 215 14,585 717 0 14,800 0 0 0

0 215 14,588 364 0 14,803 0 0 0

0 0 3 -353 0 3 0 0 0

elapsed time

234

169

-65

elapsed time (in seconds)

2.34

1.69

-0.65

List of values (Show actual 10046 STAT lines) -----------------------------------------------------------YES NO Show actual 10046 STAT lines [YES]:

Actual Plan for Scenario UPDATE:ANSWER1 STAT #1 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE (cr=215 r=717 w=0 time=2217354 us)' STAT #1 id=2 cnt=1600 pid=1 pos=1 obj=29237 op='TABLE ACCESS FULL T (cr=211 r=205 w=0 time=277281 us)' Actual Plan for Scenario UPDATE:ANSWER1_HIST STAT #1 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE (cr=215 r=364 w=0 time=1577867 us)' STAT #1 id=2 cnt=1600 pid=1 pos=1 obj=29237 op='TABLE ACCESS FULL T (cr=211 r=4 w=0 time=72003 us)' List of values (Show EXPLAIN PLAN output) -----------------------------------------------------------YES NO Show EXPLAIN PLAN output [YES]: ========================================================================== UPDATE:ANSWER1 March 04, 2005

case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment

63 / 72

-------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost | -------------------------------------------------------------------| 0 | UPDATE STATEMENT | | 16667 | 50001 | 22 | | 1 | UPDATE | T | | | | |* 2 | TABLE ACCESS FULL | T | 16667 | 50001 | 22 | -------------------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------2 - filter("T"."STATUS"=2 AND "T"."FLAG"<>'B') Note: cpu costing is off ========================================================================== UPDATE:ANSWER1_HIST -------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost | -------------------------------------------------------------------| 0 | UPDATE STATEMENT | | 40000 | 117K| 22 | | 1 | UPDATE | T | | | | |* 2 | TABLE ACCESS FULL | T | 40000 | 117K| 22 | -------------------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------2 - filter("T"."STATUS"=2 AND "T"."FLAG"<>'B') Note: cpu costing is off ========================================================================== SQL> SQL> SQL> @diff List of values (First workspace) -----------------------------------------------------------UPDATE First workspace [UPDATE]: List of values (First scenario) -----------------------------------------------------------March 04, 2005

case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment

64 / 72

ANSWER1 ANSWER1_HIST ANSWER2 ANSWER2_HIST ANSWER3 ANSWER3_HIST PROBLEM First scenario [ANSWER1]: answer2 List of values (Second workspace) -----------------------------------------------------------UPDATE Second workspace [UPDATE]: List of values (Second scenario) -----------------------------------------------------------ANSWER1 ANSWER1_HIST ANSWER2 ANSWER2_HIST ANSWER3 ANSWER3_HIST PROBLEM Second scenario [ANSWER1_HIST]: answer2_hist Statistic Name Statistic Type (S=stat, L=Latch) ---------------------------------------active checkpoint queue latch channel operations parent latch checkpoint queue latch library cache load lock library cache pin allocation messages post/wait queue redo writing session allocation session idle bit session timer sim partition latch simulator hash latch simulator lru latch March 04, 2005

L L L L L L L L L L L L L L

UPDATE UPDATE ANSWER2 ANSWER2_HIST Difference -------------- -------------- -------------4 2 -2 2 0 -2 1,641 1,113 -528 2 0 -2 58 46 -12 25 17 -8 2 0 -2 18 14 -4 2 0 -2 17 16 -1 1 0 -1 1 0 -1 258 206 -52 62 54 -8 case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment

65 / 72

SQL memory manager latch SQL memory manager workarea list latch undo global data calls to get snapshot scn: kcmgss calls to kcmgas CPU used by this session CPU used when call started cursor authentications dirty buffers inspected free buffer inspected free buffer requested hot buffers moved to head of LRU index scans kdiixs1 messages sent opened cursors cumulative parse time elapsed recursive calls recursive cpu usage session pga memory max shared hash latch upgrades - no wait write clones created in foreground

L L L S S S S S S S S S S S S S S S S S S

1 71 6 8 4 11 11 1 538 541 537 222 9 4 7 13 5 2 0 10 4

0 4 7 6 1 20 20 0 292 296 446 219 5 2 5 2 0 0 131,072 6 1

-1 -67 1 -2 -3 9 9 -1 -246 -245 -91 -3 -4 -2 -2 -11 -5 -2 131,072 -4 -3

buffer pool cache buffers chains cache buffers lru chain enqueue hash chains enqueues library cache library cache pin redo allocation redo copy row cache enqueue latch row cache objects shared pool

L L L L L L L L L L L L

0 16,101 1,434 12 7 208 100 2,217 2,209 120 123 180

0 14,833 972 10 4 173 82 2,215 2,207 100 104 152

0 -1,268 -462 -2 -3 -35 -18 -2 -2 -20 -19 -28

execute count parse count (hard) parse count (total) redo size table fetch by rowid table scan blocks gotten table scan rows gotten

S S S S S S S

7 2 7 638,912 0 1 1

5 2 5 638,912 0 1 1

-2 0 -2 0 0 0 0

buffer is pinned count consistent gets

S S

2 20

2 16

0 -4

March 04, 2005

case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment

66 / 72

db block gets physical reads physical writes session logical reads sorts (disk) sorts (memory) sorts (rows)

S S S S S S S

2,840 498 0 2,860 0 5 6,405

2,839 411 0 2,855 0 3 6,405

-1 -87 0 -5 0 -2 0

elapsed time

151

141

-10

elapsed time (in seconds)

1.51

1.41

-0.10

List of values (Show actual 10046 STAT lines) -----------------------------------------------------------YES NO Show actual 10046 STAT lines [YES]:

Actual Plan for Scenario UPDATE:ANSWER2 STAT STAT STAT STAT STAT STAT STAT STAT

#1 #1 #1 #1 #1 #1 #1 #1

id=1 id=2 id=3 id=4 id=5 id=6 id=7 id=8

Actual Plan for Scenario UPDATE:ANSWER2_HIST STAT STAT STAT STAT STAT STAT STAT STAT

#1 #1 #1 #1 #1 #1 #1 #1

id=1 id=2 id=3 id=4 id=5 id=6 id=7 id=8

List of values (Show EXPLAIN PLAN output) March 04, 2005

case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment

67 / 72

-----------------------------------------------------------YES NO Show EXPLAIN PLAN output [YES]: ========================================================================== UPDATE:ANSWER2 ---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost | ---------------------------------------------------------------------------| 0 | UPDATE STATEMENT | | 83333 | 488K| 26 | | 1 | UPDATE | T | | | | | 2 | NESTED LOOPS | | 83333 | 488K| 26 | | 3 | VIEW | VW_NSO_1 | 5 | 15 | 25 | | 4 | MINUS | | | | | | 5 | SORT UNIQUE | | 5 | 5 | | | 6 | INDEX FULL SCAN | SYS_IOT_TOP_29235 | 5 | 5 | 1 | | 7 | TABLE ACCESS FULL| DUAL | 8168 | | 11 | |* 8 | INDEX RANGE SCAN | T_N3 | 16667 | 50001 | | ---------------------------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------8 - access("T"."STATUS"=2 AND "T"."FLAG"="VW_NSO_1"."$nso_col_1") Note: cpu costing is off ========================================================================== UPDATE:ANSWER2_HIST ---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost | ---------------------------------------------------------------------------| 0 | UPDATE STATEMENT | | 200K| 1171K| 26 | | 1 | UPDATE | T | | | | | 2 | NESTED LOOPS | | 200K| 1171K| 26 | | 3 | VIEW | VW_NSO_1 | 5 | 15 | 25 | | 4 | MINUS | | | | | | 5 | SORT UNIQUE | | 5 | 5 | | | 6 | INDEX FULL SCAN | SYS_IOT_TOP_29235 | 5 | 5 | 1 | | 7 | TABLE ACCESS FULL| DUAL | 8168 | | 11 | |* 8 | INDEX RANGE SCAN | T_N3 | 40000 | 117K| | ----------------------------------------------------------------------------

March 04, 2005

case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment

68 / 72

Predicate Information (identified by operation id): --------------------------------------------------8 - access("T"."STATUS"=2 AND "T"."FLAG"="VW_NSO_1"."$nso_col_1") Note: cpu costing is off ========================================================================== SQL> SQL> @diff List of values (First workspace) -----------------------------------------------------------UPDATE First workspace [UPDATE]: List of values (First scenario) -----------------------------------------------------------ANSWER1 ANSWER1_HIST ANSWER2 ANSWER2_HIST ANSWER3 ANSWER3_HIST PROBLEM First scenario [ANSWER2]: answer3 List of values (Second workspace) -----------------------------------------------------------UPDATE Second workspace [UPDATE]: List of values (Second scenario) -----------------------------------------------------------ANSWER1 ANSWER1_HIST ANSWER2 ANSWER2_HIST ANSWER3 ANSWER3_HIST PROBLEM

March 04, 2005

case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment

69 / 72

Second scenario [ANSWER2_HIST]: answer3_hist Statistic Name Statistic Type (S=stat, L=Latch) ---------------------------------------active checkpoint queue latch checkpoint queue latch child cursor hash table Consistent RBA lgwr LWN SCN library cache pin allocation list of block allocation messages mostly latch-free SCN redo writing simulator hash latch simulator lru latch transaction allocation undo global data calls to kcmgas change write time CPU used by this session CPU used when call started dirty buffers inspected free buffer inspected free buffer requested hot buffers moved to head of LRU messages sent parse time cpu parse time elapsed pinned buffers inspected session pga memory max write clones created in foreground

L L L L L L L L L L L L L L S S S S S S S S S S S S S S

buffer pool cache buffers chains cache buffers lru chain enqueue hash chains enqueues library cache library cache pin redo allocation redo copy row cache enqueue latch row cache objects shared pool

L L L L L L L L L L L L

March 04, 2005

UPDATE UPDATE ANSWER3 ANSWER3_HIST Difference -------------- -------------- -------------4 2 -2 910 694 -216 14 15 1 3 4 1 3 4 1 42 40 -2 6 8 2 22 19 -3 3 4 1 13 14 1 203 177 -26 29 24 -5 0 2 2 6 17 11 1 0 -1 0 1 1 12 14 2 12 14 2 2 212 210 2 216 214 247 208 -39 303 441 138 3 2 -1 0 1 1 0 2 2 0 4 4 0 65,536 65,536 1 0 -1 0 13,914 504 10 4 156 78 2,212 2,206 74 77 152

0 14,332 636 16 4 151 76 2,238 2,230 74 78 140

0 418 132 6 0 -5 -2 26 24 0 1 -12 case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment

70 / 72

execute count parse count (hard) parse count (total) redo size table fetch by rowid table scan blocks gotten table scan rows gotten

S S S S S S S

5 2 5 638,912 0 0 0

0 0 0 0 0 0 0

buffer is pinned count consistent gets db block gets physical reads physical writes session logical reads sorts (disk) sorts (memory) sorts (rows)

S S S S S S S S S

2 12 2,840 212 0 2,852 0 2 6,400

2 12 2,839 174 0 2,851 0 2 6,400

0 0 -1 -38 0 -1 0 0 0

elapsed time

102

-34

elapsed time (in seconds)

1.02

0.68

-0.34

List of values (Show actual 10046 STAT lines) -----------------------------------------------------------YES NO Show actual 10046 STAT lines [YES]:

Actual Plan for Scenario UPDATE:ANSWER3 STAT STAT STAT STAT

#1 #1 #1 #1

id=1 id=2 id=3 id=4

Actual Plan for Scenario UPDATE:ANSWER3_HIST STAT #1 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE (cr=12 r=174 w=0 time=565098 us)' STAT #1 id=2 cnt=1600 pid=1 pos=1 obj=0 op='NESTED LOOPS (cr=12 r=2 w=0 time=25717 us)' March 04, 2005

case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment

71 / 72

STAT #1 id=3 cnt=4 pid=2 pos=1 obj=29236 op='INDEX FULL SCAN SYS_IOT_TOP_29235 (cr=1 r=1 w=0 time=9258 us)' STAT #1 id=4 cnt=1600 pid=2 pos=2 obj=29241 op='INDEX RANGE SCAN T_N3 (cr=11 r=1 w=0 time=11507 us)' List of values (Show EXPLAIN PLAN output) -----------------------------------------------------------YES NO Show EXPLAIN PLAN output [YES]: ========================================================================== UPDATE:ANSWER3 --------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost | --------------------------------------------------------------------------| 0 | UPDATE STATEMENT | | 33333 | 130K| 2 | | 1 | UPDATE | T | | | | | 2 | NESTED LOOPS | | 33333 | 130K| 2 | |* 3 | INDEX FULL SCAN | SYS_IOT_TOP_29235 | 4 | 4 | 1 | |* 4 | INDEX RANGE SCAN | T_N3 | 8333 | 24999 | | --------------------------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------3 - filter("FLAGS"."FLAG"<>'B') 4 - access("T"."STATUS"=2 AND "T"."FLAG"="FLAGS"."FLAG") Note: cpu costing is off ========================================================================== UPDATE:ANSWER3_HIST --------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost | --------------------------------------------------------------------------| 0 | UPDATE STATEMENT | | 80000 | 312K| 2 | | 1 | UPDATE | T | | | | | 2 | NESTED LOOPS | | 80000 | 312K| 2 | |* 3 | INDEX FULL SCAN | SYS_IOT_TOP_29235 | 4 | 4 | 1 | |* 4 | INDEX RANGE SCAN | T_N3 | 20000 | 60000 | | --------------------------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------March 04, 2005

case study.txt

Hotsos Symposium 2005 - The Basics of an Effective Test Environment

72 / 72

3 - filter("FLAGS"."FLAG"<>'B') 4 - access("T"."STATUS"=2 AND "T"."FLAG"="FLAGS"."FLAG") Note: cpu costing is off ========================================================================== SQL>

March 04, 2005

case study.txt