ExaGridDba

Database Administration

10053 diff: where status = ‘PENDING’

Posted on February 12, 2018 by Brian Fitzgerald

[oracle@stormking db12201 12.2]$ diff run1/db12201_ora_20225_STATUS.trc run2/db12201_ora_20607_STATUS.trc

1c1
< Trace file /u01/app/oracle/diag/rdbms/db12201/db12201/trace/db12201_ora_20225_STATUS.trc
---
> Trace file /u01/app/oracle/diag/rdbms/db12201/db12201/trace/db12201_ora_20607_STATUS.trc
12,13c12,13
< Oracle process number: 41
< Unix process pid: 20225, image: oracle@stormking (TNS V1-V3)
---
> Oracle process number: 40
> Unix process pid: 20607, image: oracle@stormking (TNS V1-V3)
16,22c16,22
< *** 2018-02-11T14:23:22.737391-05:00
< *** SESSION ID:(84.19304) 2018-02-11T14:23:22.737391-05:00
< *** CLIENT ID:() 2018-02-11T14:23:22.737391-05:00
< *** SERVICE NAME:(SYS$USERS) 2018-02-11T14:23:22.737391-05:00
< *** MODULE NAME:(SQL*Plus) 2018-02-11T14:23:22.737391-05:00
< *** ACTION NAME:() 2018-02-11T14:23:22.737391-05:00
< *** CLIENT DRIVER:(SQL*PLUS) 2018-02-11T14:23:22.737391-05:00
---
> *** 2018-02-11T14:25:26.553076-05:00
> *** SESSION ID:(84.15782) 2018-02-11T14:25:26.553076-05:00
> *** CLIENT ID:() 2018-02-11T14:25:26.553076-05:00
> *** SERVICE NAME:(SYS$USERS) 2018-02-11T14:25:26.553076-05:00
> *** MODULE NAME:(SQL*Plus) 2018-02-11T14:25:26.553076-05:00
> *** ACTION NAME:() 2018-02-11T14:25:26.553076-05:00
> *** CLIENT DRIVER:(SQL*PLUS) 2018-02-11T14:25:26.553076-05:00
25c25
< *** TRACE CONTINUED FROM FILE /u01/app/oracle/diag/rdbms/db12201/db12201/trace/db12201_ora_20225_STATS.trc ***
---
> *** TRACE CONTINUED FROM FILE /u01/app/oracle/diag/rdbms/db12201/db12201/trace/db12201_ora_20607_STATS.trc ***
27c27
< Registered qb: SEL$1 0xf972ef68 (PARSER)
---
> Registered qb: SEL$1 0x6bfd6f68 (PARSER)
816c816
< kkoqbc-subheap (create addr=0x7fddf9729810)
---
> kkoqbc-subheap (create addr=0x7f286bfd1810)
873a874,875
> Column (#3):
> NewDensity:0.000050, OldDensity:0.000050 BktCnt:10000.000000, PopBktCnt:9999.000000, PopValCnt:1, NDV:2
875,876c877,879
< AvgLen: 9 NDV: 2 Nulls: 0 Density: 0.500000
< Estimated selectivity: 0.500000 , col: #3
---
> AvgLen: 9 NDV: 2 Nulls: 0 Density: 0.000050
> Histogram: Freq #Bkts: 2 UncompBkts: 10000 EndPtVals: 2 ActualVal: no
> Estimated selectivity: 1.0000e-04 , endpoint value predicate, col: #3
878c881
< Card: Original: 10000.000000 Rounded: 5000 Computed: 5000.000000 Non Adjusted: 5000.000000
---
> Card: Original: 10000.000000 Rounded: 1 Computed: 1.000000 Non Adjusted: 1.000000
882c885
< io = NOCOST, cpu = 50.000000, sel = 0.500000 flag = 2048 ("ORDS"."STATUS"='PENDING')
---
> io = NOCOST, cpu = 50.000000, sel = 0.000100 flag = 2048 ("ORDS"."STATUS"='PENDING')
896c899
< Estimated selectivity: 0.500000 , col: #3
---
> Estimated selectivity: 1.0000e-04 , endpoint value predicate, col: #3
899,901c902,904
< resc_io: 30.000000 resc_cpu: 2164493
< ix_sel: 0.500000 ix_sel_with_filters: 0.500000
< Cost: 30.057486 Resp: 30.057486 Degree: 1
---
> resc_io: 2.000000 resc_cpu: 15483
> ix_sel: 1.0000e-04 ix_sel_with_filters: 1.0000e-04
> Cost: 2.000411 Resp: 2.000411 Degree: 1
904c907
< Estimated selectivity: 0.500000 , col: #3
---
> Estimated selectivity: 1.0000e-04 , endpoint value predicate, col: #3
908c911
< Estimated selectivity: 0.500000 , col: #3
---
> Estimated selectivity: 1.0000e-04 , endpoint value predicate, col: #3
911,913c914,916
< resc_io: 14.000000 resc_cpu: 1100550
< ix_sel: 0.500000 ix_sel_with_filters: 0.500000
< Cost: 14.029229 Resp: 14.029229 Degree: 0
---
> resc_io: 1.000000 resc_cpu: 8171
> ix_sel: 1.0000e-04 ix_sel_with_filters: 1.0000e-04
> Cost: 1.000217 Resp: 1.000217 Degree: 0
916c919
< Cost = 14.047156, sel = 0.500000
---
> Cost = 1.000221, sel = 1.0000e-04
931c934
< resc: 14.029229 card 5000.000000 bytes: deg: 1 resp: 14.029229
---
> resc: 1.000217 card 1.000000 bytes: deg: 1 resp: 1.000217
935,937c938,940
< Cost per ptn: 0.062413 #ptns: 1
< hash_area: 256 (max=41035) buildfrag: 19 probefrag: 32 ppasses: 1
< Hash join: Resc: 35.148731 Resp: 35.148731 [multiMatchCost=0.000000]
---
> Cost per ptn: 0.042498 #ptns: 1
> hash_area: 256 (max=41035) buildfrag: 1 probefrag: 32 ppasses: 1
> Hash join: Resc: 22.099804 Resp: 22.099804 [multiMatchCost=0.000000]
941c944
< Cost: 35.148731
---
> Cost: 22.099804
943,944c946,948
< Best:: AccessPath: TableScan
< Cost: 11.070360 Degree: 1 Resp: 11.070360 Card: 5000.000000 Bytes: 0.000000
---
> Best:: AccessPath: IndexRange
> Index: STATUS_IDX
> Cost: 2.000411 Degree: 1 Resp: 2.000411 Card: 1.000000 Bytes: 0.000000
958c962
< Best so far: Table#: 0 cost: 11.070360 card: 5000.000000 bytes: 65000.000000
---
> Best so far: Table#: 0 cost: 2.000411 card: 1.000000 bytes: 13.000000
970,971c974,975
< AutoDOP: Consider caching for ORDS[ORDS](obj#93045)
< cost:11.070360 blkSize:8192 objSize:35.00 marObjSize:33.25 bufSize:469047.00 affPercent:80 smallTab:YES affinitized:NO
---
> AutoDOP: Consider caching for ORDS[ORDS](obj#-1)
> cost:2.000411 blkSize:8192 objSize:35.00 marObjSize:33.25 bufSize:469047.00 affPercent:80 smallTab:YES affinitized:NO
974c978,979
< id=0 frofand predicate="ORDS"."STATUS"='PENDING'
---
> id=0 frofkks[i] (index start key) predicate="ORDS"."STATUS"='PENDING'
> id=0 frofkke[i] (index stop key) predicate="ORDS"."STATUS"='PENDING'
978,981c983,986
< Cost: 11.070360 Degree: 1 Card: 5000.000000 Bytes: 65000.000000
< Resc: 11.070360 Resc_io: 11.000000 Resc_cpu: 2649250
< Resp: 11.070360 Resp_io: 11.000000 Resc_cpu: 2649250
< kkoqbc-subheap (delete addr=0x7fddf9729810, in-use=41016, alloc=49272)
---
> Cost: 2.000411 Degree: 1 Card: 1.000000 Bytes: 13.000000
> Resc: 2.000411 Resc_io: 2.000000 Resc_cpu: 15483
> Resp: 2.000411 Resp_io: 2.000000 Resc_cpu: 15483
> kkoqbc-subheap (delete addr=0x7f286bfd1810, in-use=41112, alloc=49272)
984c989
< call(in-use=9096, alloc=65760), compile(in-use=86264, alloc=89040), execution(in-use=2936, alloc=4032)
---
> call(in-use=8888, alloc=65760), compile(in-use=88344, alloc=89040), execution(in-use=2936, alloc=4032)
990c995
< call(in-use=9096, alloc=65760), compile(in-use=87272, alloc=89040), execution(in-use=2936, alloc=4032)
---
> call(in-use=8888, alloc=65760), compile(in-use=89352, alloc=93184), execution(in-use=2936, alloc=4032)
992a998,1004
> CBRID: ORDS @ SEL$1 TableLookup allocation - Failure - bug-fix control
> kkeCostToTime: using io calibrate stats maxpmbps=200(MB/s)
> block_size=8192 mb_io_count=1 mb_io_size=8192 (bytes)
> tot_io_size=0(MB) time=0(ms)
> kkeCostToTime: using io calibrate stats maxpmbps=200(MB/s)
> block_size=8192 mb_io_count=1 mb_io_size=8192 (bytes)
> tot_io_size=0(MB) time=0(ms)
1002c1014
< sql_id=03y2yj25g7d27 plan_hash_value=1252245826 problem_type=3
---
> sql_id=03y2yj25g7d27 plan_hash_value=1807741764 problem_type=3
1016,1021c1028,1034
< -------------------------------------+-----------------------------------+
< | Id | Operation | Name | Rows | Bytes | Cost | Time |
< -------------------------------------+-----------------------------------+
< | 0 | SELECT STATEMENT | | | | 11 | |
< | 1 | TABLE ACCESS FULL | ORDS | 5000 | 63K | 11 | 00:00:01 |
< -------------------------------------+-----------------------------------+
---
> ---------------------------------------------------------+-----------------------------------+
> | Id | Operation | Name | Rows | Bytes | Cost | Time |
> ---------------------------------------------------------+-----------------------------------+
> | 0 | SELECT STATEMENT | | | | 2 | |
> | 1 | TABLE ACCESS BY INDEX ROWID BATCHED | ORDS | 1 | 13 | 2 | 00:00:01 |
> | 2 | INDEX RANGE SCAN | STATUS_IDX| 1 | | 1 | 00:00:01 |
> ---------------------------------------------------------+-----------------------------------+
1024a1038
> 2 - SEL$1 / ORDS@SEL$1
1028c1042
< 1 - filter("STATUS"='PENDING')
---
> 2 - access("STATUS"='PENDING')
1034,1036c1048,1050
< plan_hash_full : 2095704957
< plan_hash : 1252245826
< plan_hash_2 : 2095704957
---
> plan_hash_full : 2670720227
> plan_hash : 1807741764
> plan_hash_2 : 2670720227
1045c1059,1060
< FULL(@"SEL$1" "ORDS"@"SEL$1")
---
> INDEX_RS_ASC(@"SEL$1" "ORDS"@"SEL$1" ("ORDS"."STATUS"))
> BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "ORDS"@"SEL$1")
2906c2921
< SEL$1 0xf972ef68 (PARSER) [FINAL]
---
> SEL$1 0x6bfd6f68 (PARSER) [FINAL]
2909c2924
< call(in-use=16696, alloc=65760), compile(in-use=108432, alloc=168664), execution(in-use=6504, alloc=8088)
---
> call(in-use=13888, alloc=65760), compile(in-use=113288, alloc=175064), execution(in-use=9136, alloc=12144)
2916c2931,2933
< *** TRACE CONTINUES IN FILE /u01/app/oracle/diag/rdbms/db12201/db12201/trace/db12201_ora_20225_CAT.trc ***
---
> *** 2018-02-11T14:25:26.880036-05:00
>
> *** TRACE CONTINUES IN FILE /u01/app/oracle/diag/rdbms/db12201/db12201/trace/db12201_ora_20607_CAT.trc ***

Leave a ReplyCancel reply