ExaGridDba

Database Administration

10053 diff: where categoryid and status

Posted on February 12, 2018 by Brian Fitzgerald

[oracle@stormking db12201 12.2]$ diff run1/db12201_ora_20225_CAT.trc run2/db12201_ora_20607_CAT.trc
1c1
< Trace file /u01/app/oracle/diag/rdbms/db12201/db12201/trace/db12201_ora_20225_CAT.trc
---
> Trace file /u01/app/oracle/diag/rdbms/db12201/db12201/trace/db12201_ora_20607_CAT.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:23.119343-05:00
< *** SESSION ID:(84.19304) 2018-02-11T14:23:23.119343-05:00
< *** CLIENT ID:() 2018-02-11T14:23:23.119343-05:00
< *** SERVICE NAME:(SYS$USERS) 2018-02-11T14:23:23.119343-05:00
< *** MODULE NAME:(SQL*Plus) 2018-02-11T14:23:23.119343-05:00
< *** ACTION NAME:() 2018-02-11T14:23:23.119343-05:00
< *** CLIENT DRIVER:(SQL*PLUS) 2018-02-11T14:23:23.119343-05:00
---
> *** 2018-02-11T14:25:26.881036-05:00
> *** SESSION ID:(84.15782) 2018-02-11T14:25:26.881036-05:00
> *** CLIENT ID:() 2018-02-11T14:25:26.881036-05:00
> *** SERVICE NAME:(SYS$USERS) 2018-02-11T14:25:26.881036-05:00
> *** MODULE NAME:(SQL*Plus) 2018-02-11T14:25:26.881036-05:00
> *** ACTION NAME:() 2018-02-11T14:25:26.881036-05:00
> *** CLIENT DRIVER:(SQL*PLUS) 2018-02-11T14:25:26.881036-05:00
25c25
< *** TRACE CONTINUED FROM FILE /u01/app/oracle/diag/rdbms/db12201/db12201/trace/db12201_ora_20225_STATUS.trc ***
---
> *** TRACE CONTINUED FROM FILE /u01/app/oracle/diag/rdbms/db12201/db12201/trace/db12201_ora_20607_STATUS.trc ***
27c27
< Registered qb: SEL$1 0xf972ef68 (PARSER)
---
> Registered qb: SEL$1 0x6bfd6f68 (PARSER)
817c817
< kkoqbc-subheap (create addr=0x7fddf9729810)
---
> kkoqbc-subheap (create addr=0x7f286bfd1810)
876a877,878
> Column (#3):
> NewDensity:0.000050, OldDensity:0.000050 BktCnt:10000.000000, PopBktCnt:9999.000000, PopValCnt:1, NDV:2
878,879c880,882
< 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: 0.999900 , endpoint value predicate, col: #3
886c889
< Estimated selectivity: 0.500000 , col: #3
---
> Estimated selectivity: 0.999900 , endpoint value predicate, col: #3
888c891
< Card: Original: 10000.000000 Rounded: 5 Computed: 5.000000 Non Adjusted: 5.000000
---
> Card: Original: 10000.000000 Rounded: 10 Computed: 9.999000 Non Adjusted: 9.999000
893c896
< io = NOCOST, cpu = 50.000000, sel = 0.500000 flag = 2048 ("ORDS"."STATUS"='COMPLETE')
---
> io = NOCOST, cpu = 50.000000, sel = 0.999900 flag = 2048 ("ORDS"."STATUS"='COMPLETE')
916c919
< Estimated selectivity: 0.500000 , col: #3
---
> Estimated selectivity: 0.999900 , endpoint value predicate, col: #3
919,921c922,924
< resc_io: 30.000000 resc_cpu: 2414493
< ix_sel: 0.500000 ix_sel_with_filters: 0.500000
< Cost: 30.064126 Resp: 30.064126 Degree: 1
---
> resc_io: 60.000000 resc_cpu: 4827696
> ix_sel: 0.999900 ix_sel_with_filters: 0.999900
> Cost: 60.128217 Resp: 60.128217 Degree: 1
933c936
< Estimated selectivity: 0.500000 , col: #3
---
> Estimated selectivity: 0.999900 , endpoint value predicate, col: #3
937c940
< Estimated selectivity: 0.500000 , col: #3
---
> Estimated selectivity: 0.999900 , endpoint value predicate, col: #3
940,942c943,945
< 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: 28.000000 resc_cpu: 2200050
> ix_sel: 0.999900 ix_sel_with_filters: 0.999900
> Cost: 28.058430 Resp: 28.058430 Degree: 0
946,950c949,950
< Used STATUS_IDX
< Cost = 14.047156, sel = 0.500000
< Access path: Bitmap index - accepted
< Cost: 16.017750 Cost_io: 15.970067 Cost_cpu: 1795404.871128 Sel: 5.0000e-04
< Not Believed to be index-only
---
> Not used STATUS_IDX
> Cost = 28.094281, sel = 0.999900
968c968
< resc: 14.029229 card: 5000.000000 bytes: deg: 1 resp: 14.029229
---
> resc: 28.058430 card: 9999.000000 bytes: deg: 1 resp: 28.058430
970,972c970,972
< Cost per ptn: 0.029254 #ptns: 1
< hash_area: 256 (max=41035) buildfrag: 1 probefrag: 19 ppasses: 1
< Hash join: Resc: 15.058748 Resp: 15.058748 [multiMatchCost=0.000000]
---
> Cost per ptn: 0.042531 #ptns: 1
> hash_area: 256 (max=41035) buildfrag: 1 probefrag: 38 ppasses: 1
> Hash join: Resc: 29.101226 Resp: 29.101226 [multiMatchCost=0.000000]
976c976
< resc: 1.000265 card 5.000000 bytes: deg: 1 resp: 1.000265
---
> resc: 1.000265 card 9.999000 bytes: deg: 1 resp: 1.000265
980c980
< Cost per ptn: 0.042514 #ptns: 1
---
> Cost per ptn: 0.042534 #ptns: 1
982c982
< Hash join: Resc: 22.099868 Resp: 22.099868 [multiMatchCost=0.000000]
---
> Hash join: Resc: 22.099888 Resp: 22.099888 [multiMatchCost=0.000000]
986c986
< Cost: 37.158616
---
> Cost: 51.201114
990c990
< Cost: 11.002220 Degree: 1 Resp: 11.002220 Card: 5.000000 Bytes: 0.000000
---
> Cost: 11.002220 Degree: 1 Resp: 11.002220 Card: 9.999000 Bytes: 0.000000
1004c1004
< Best so far: Table#: 0 cost: 11.002220 card: 5.000000 bytes: 85.000000
---
> Best so far: Table#: 0 cost: 11.002220 card: 9.999000 bytes: 170.000000
1026c1026
< Cost: 11.002220 Degree: 1 Card: 5.000000 Bytes: 85.000000
---
> Cost: 11.002220 Degree: 1 Card: 10.000000 Bytes: 170.000000
1029c1029
< kkoqbc-subheap (delete addr=0x7fddf9729810, in-use=42200, alloc=49272)
---
> kkoqbc-subheap (delete addr=0x7f286bfd1810, in-use=42200, alloc=49272)
1032c1032
< call(in-use=10064, alloc=65760), compile(in-use=93904, alloc=97328), execution(in-use=2936, alloc=4032)
---
> call(in-use=9856, alloc=65760), compile(in-use=95696, alloc=99072), execution(in-use=2936, alloc=4032)
1038c1038
< call(in-use=10064, alloc=65760), compile(in-use=94912, alloc=97328), execution(in-use=2936, alloc=4032)
---
> call(in-use=9856, alloc=65760), compile(in-use=96704, alloc=99072), execution(in-use=2936, alloc=4032)
1043c1043
< SPD: Inserted felem, fid=11333809974545767572, ftype = 1, freason = 1, dtype = 0, dstate = 0, dflag = 0, ver = NO, keep = NO
---
> SPD: Modified felem, fid=11333809974545767572, ftype = 1, freason = 1, dtype = 0, dstate = 0, dflag = 0, ver = NO, keep = NO
1047c1047
< SPD: Inserted felem, fid=2757520453804995358, ftype = 1, freason = 1, dtype = 0, dstate = 0, dflag = 0, ver = NO, keep = NO
---
> SPD: Modified felem, fid=2757520453804995358, ftype = 1, freason = 1, dtype = 0, dstate = 0, dflag = 0, ver = NO, keep = NO
1083c1083
< | 1 | TABLE ACCESS BY INDEX ROWID BATCHED | ORDS | 5 | 85 | 11 | 00:00:01 |
---
> | 1 | TABLE ACCESS BY INDEX ROWID BATCHED | ORDS | 10 | 170 | 11 | 00:00:01 |
2973c2973
< SEL$1 0xf972ef68 (PARSER) [FINAL]
---
> SEL$1 0x6bfd6f68 (PARSER) [FINAL]
2976c2976
< call(in-use=17648, alloc=65760), compile(in-use=123424, alloc=183352), execution(in-use=10112, alloc=12144)
---
> call(in-use=17440, alloc=65760), compile(in-use=125208, alloc=187496), execution(in-use=10112, alloc=12144)
2983c2983
< *** TRACE CONTINUES IN FILE /u01/app/oracle/diag/rdbms/db12201/db12201/trace/db12201_ora_20225_GBY.trc ***
---
> *** TRACE CONTINUES IN FILE /u01/app/oracle/diag/rdbms/db12201/db12201/trace/db12201_ora_20607_GBY.trc ***

Leave a ReplyCancel reply