Forum Stats

  • 3,734,267 Users
  • 2,246,934 Discussions
  • 7,857,216 Comments

Discussions

tuning histogram query

BartO
BartO Member Posts: 22
edited Sep 14, 2016 8:17AM in General Database Discussions

Hello,

The performance of a query is quite low due to a mismatch in E-rows and A-rows. After some investigations I still don't know why there is a mismatch. Can you help me with this?

---The query:SELECT   COUNT(DISTINCT t.identityid)FROM metadata_linkedpair_tbl t33121956 ,  metadata_node_tbl t33121958 ,  metadata_node_tbl t33121961 ,  archiveobject_tbl tWHERE t.status           =0AND (lower(t.ISRESERVED) = lower('false'))AND (NVL(t.NextV, '#nvl#') LIKE NVL('', '#nvl#') ESCAPE '\')AND (NVL(t.producttype, '#nvl#') LIKE NVL('PROBAV\_L3\_S1\_TOA\_333M', '#nvl#') ESCAPE '\')AND t33121956.semanticid=2101AND (NVL(t33121956.value, '#nvl#') NOT LIKE NVL('%V101', '#nvl#') ESCAPE '\')AND t33121958.semanticid=9AND (NVL(t33121958.value, '#nvl#') LIKE NVL('file://%', '#nvl#') ESCAPE '\')AND t33121961.semanticid=13AND (NVL(t33121961.value, '#nvl#') LIKE NVL('shared', '#nvl#') ESCAPE '\')AND (t.stopdate       < CURRENT_DATE + -30)AND t33121956.objectid=t.identityidAND t33121958.objectid=t.identityidAND t33121961.objectid=t.identityid---report:-------------------------------------------------------------------------------------------------------------------------------------------------------------                                                                                                                                                | Id  | Operation                         | Name                      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |                                                                                                                                                -------------------------------------------------------------------------------------------------------------------------------------------------------------                                                                                                                                                |   0 | SELECT STATEMENT                  |                           |      1 |        |      1 |00:00:25.91 |     513K|    306K|                                                                   |   1 |  SORT AGGREGATE                   |                           |      1 |      1 |      1 |00:00:25.91 |     513K|    306K|       |                                              |   2 |   VIEW                            | VW_DAG_0                  |      1 |   1549 |   1035 |00:00:25.91 |     513K|    306K|       |       |          |   3 |    HASH GROUP BY                  |                           |      1 |   1549 |   1035 |00:00:25.91 |     513K|    306K|  1349K|  1349K| 1344K(0)| |*  4 |     HASH JOIN                     |                           |      1 |   1587 |   1426 |00:00:25.91 |     513K|    306K|  1348K|  1348K| 1353K (0)| |*  5 |      HASH JOIN                    |                           |      1 |   1572 |   1773 |00:00:25.79 |     512K|    306K|  1348K|  1348K| 1331K (0)| |*  6 |       HASH JOIN                   |                           |      1 |   1559 |   1209 |00:00:13.86 |     155K|    144K|  1348K|  1348K| 1356K (0)||*  7 |        INDEX RANGE SCAN           | ARCHIVEOBJECT_IDX1        |      1 |   1549 |   1209 |00:00:00.01 |      53 |      0 |       |       |          | |   8 |        TABLE ACCESS BY INDEX ROWID| METADATA_NODE_TBL         |      1 |   5578 |    322K|00:00:13.69 |     155K|    144K|       |       |          | |*  9 |         INDEX RANGE SCAN          | METADATA_NODE_TBL_SEM_VAL |      1 |   5578 |    322K|00:00:00.13 |     954 |    940 |       |       |          | |  10 |       TABLE ACCESS BY INDEX ROWID | METADATA_NODE_TBL         |      1 |   6972 |    356K|00:00:11.76 |     357K|    162K|       |       |          ||* 11 |        INDEX RANGE SCAN           | METADATA_NODE_TBL_SEM_VAL |      1 |   6973 |    356K|00:00:00.51 |    5154 |   5135 |       |       |          ||* 12 |      INDEX RANGE SCAN             | IDX_BAO2                  |      1 |   8128 |    119K|00:00:00.06 |     607 |     10 |       |       |          | -------------------------------------------------------------------------------------------------------------------------------------------------------------  

Table stats(updated 100% yesterday):

  select * from user_tab_col_Statisticswhere table_name='METADATA_NODE_TBL'---result:METADATA_NODE_TBL    SYS_NC00010$    7034549    236E766C23    74727565    0.000170542957042891    0    254    13-SEP-16    19591617    YES    NO    29    HEIGHT BALANCEDMETADATA_NODE_TBL    IDENTITYID    19591617    3B5F49232F66    C50203523350    0.00000005104223913728    0    1    13-SEP-16    19591617    YES    NO    7    NONEMETADATA_NODE_TBL    NAME    67    2F434F4E46494755524154494F4E    2F466C65787379732F6169704964    0.0000000255208030273797    0    45    13-SEP-16    19591617    YES    NO    42    FREQUENCYMETADATA_NODE_TBL    OBJECTID    398540    C102    C50203522E40    0.00000250915842826316    3    1    13-SEP-16    19591857    YES    NO    7    NONEMETADATA_NODE_TBL    PARENTID    7233287    C3025B07    C50203524714    0.000000138249733489076    398535    1    13-SEP-16    19193420    YES    NO    6    NONEMETADATA_NODE_TBL    STATUS    1    C102    C102    1    0    1    13-SEP-16    19591617    YES    NO    3    NONEMETADATA_NODE_TBL    PROCESSID    833016    80    C50203522D2B    0.00000120045713407666    0    1    13-SEP-16    19591617    YES    NO    7    NONEMETADATA_NODE_TBL    CLASSE    4    C102    C10B    0.25    0    1    13-SEP-16    19591617    YES    NO    3    NONEMETADATA_NODE_TBL    SEMANTICID    69    C102    C3021216    0.0000000255206623448956    0    69    13-SEP-16    19591968    YES    NO    4    FREQUENCYMETADATA_NODE_TBL    VALUE    7034548    2A    74727565    0.000200399801771744    6871855    254    13-SEP-16    12719762    YES    NO    27    HEIGHT BALANCED

There is a height balanced histogram  on the function based index METADATA_NODE_TBL_SEM_VAL =>

select * from user_histogramswhere table_name='METADATA_NODE_TBL'and column_name='SYS_NC00010$'------==>METADATA_NODE_TBL    SYS_NC00010$    89    183970837494991000000000000000000000    #nvl#METADATA_NODE_TBL    SYS_NC00010$    93    218076468058463000000000000000000000    *METADATA_NODE_TBL    SYS_NC00010$    94    234651410097795000000000000000000000    -15.5 -61.5METADATA_NODE_TBL    SYS_NC00010$    95    234732618958327000000000000000000000    -56.0 -180.0 -56.0 -120.00148 -5METADATA_NODE_TBL    SYS_NC00010$    100    249230249209672000000000000000000000    0METADATA_NODE_TBL    SYS_NC00010$    105    254422546068207000000000000000000000    1METADATA_NODE_TBL    SYS_NC00010$    106    255399762383401000000000000000000000    10.463 -86.872 9.933 -86.997 10.METADATA_NODE_TBL    SYS_NC00010$    107    255400078669838000000000000000000000    102205593METADATA_NODE_TBL    SYS_NC00010$    108    255400317293656000000000000000000000    1055941METADATA_NODE_TBL    SYS_NC00010$    109    255400635134756000000000000000000000    109893639METADATA_NODE_TBL    SYS_NC00010$    110    255420519544238000000000000000000000    11427757METADATA_NODE_TBL    SYS_NC00010$    111    255420835837895000000000000000000000    1180722426METADATA_NODE_TBL    SYS_NC00010$    112    255440566429710000000000000000000000    12192812METADATA_NODE_TBL    SYS_NC00010$    113    255441039019355000000000000000000000    1270769METADATA_NODE_TBL    SYS_NC00010$    114    255460926518823000000000000000000000    13241424METADATA_NODE_TBL    SYS_NC00010$    115    255461400960543000000000000000000000    13812299METADATA_NODE_TBL    SYS_NC00010$    116    255481289089899000000000000000000000    14375882METADATA_NODE_TBL    SYS_NC00010$    117    255481764764732000000000000000000000    14982858METADATA_NODE_TBL    SYS_NC00010$    118    255501729341655000000000000000000000    [email protected]_ARCH1METADATA_NODE_TBL    SYS_NC00010$    119    255521694838642000000000000000000000    16159745METADATA_NODE_TBL    SYS_NC00010$    120    255522249122649000000000000000000000    1684639METADATA_NODE_TBL    SYS_NC00010$    121    255542216163159000000000000000000000    17493METADATA_NODE_TBL    SYS_NC00010$    122    255562337338563000000000000000000000    182092585METADATA_NODE_TBL    SYS_NC00010$    123    255562892864170000000000000000000000    18939514METADATA_NODE_TBL    SYS_NC00010$    124    255582937277397000000000000000000000    196278089METADATA_NODE_TBL    SYS_NC00010$    125    260592296606045000000000000000000000    2013-06-24T15:56:14ZMETADATA_NODE_TBL    SYS_NC00010$    126    260592296606045000000000000000000000    2013-06-25T18:00:00ZMETADATA_NODE_TBL    SYS_NC00010$    127    260592296606045000000000000000000000    2013-06-27T07:56:56ZMETADATA_NODE_TBL    SYS_NC00010$    128    260592296606045000000000000000000000    2013-07-03T09:08:17ZMETADATA_NODE_TBL    SYS_NC00010$    129    260592296606045000000000000000000000    2013-07-08T12:14:01ZMETADATA_NODE_TBL    SYS_NC00010$    130    260592296606045000000000000000000000    2013-07-18T10:37:39ZMETADATA_NODE_TBL    SYS_NC00010$    131    260592296606045000000000000000000000    2013-07-22T18:00:00ZMETADATA_NODE_TBL    SYS_NC00010$    132    260592296606045000000000000000000000    2013-07-25T18:00:00ZMETADATA_NODE_TBL    SYS_NC00010$    133    260592296606045000000000000000000000    2013-08-06T08:09:12ZMETADATA_NODE_TBL    SYS_NC00010$    134    260592296606049000000000000000000000    2013-11-28T16:07:03ZMETADATA_NODE_TBL    SYS_NC00010$    135    260592296915530000000000000000000000    2014-08-01T20:21:50ZMETADATA_NODE_TBL    SYS_NC00010$    136    260592297225015000000000000000000000    2015-08-11T17:06:43ZMETADATA_NODE_TBL    SYS_NC00010$    137    260592297534500000000000000000000000    2016-04-29T20:43:25ZMETADATA_NODE_TBL    SYS_NC00010$    138    260592297534500000000000000000000000    2016-08-14T18:00:00ZMETADATA_NODE_TBL    SYS_NC00010$    139    260592297534500000000000000000000000    2016-08-30T18:00:00ZMETADATA_NODE_TBL    SYS_NC00010$    140    260592297843985000000000000000000000    2017-03-12T18:00:00ZMETADATA_NODE_TBL    SYS_NC00010$    141    260592376762662000000000000000000000    2026-08-15T18:00:00ZMETADATA_NODE_TBL    SYS_NC00010$    142    260592692746857000000000000000000000    2063-06-12T18:00:00ZMETADATA_NODE_TBL    SYS_NC00010$    143    260592692746857000000000000000000000    2063-06-21T18:00:00ZMETADATA_NODE_TBL    SYS_NC00010$    144    260592692746857000000000000000000000    2063-07-10T18:00:00ZMETADATA_NODE_TBL    SYS_NC00010$    145    260592693675312000000000000000000000    2066-02-18T18:00:00ZMETADATA_NODE_TBL    SYS_NC00010$    146    260612815777730000000000000000000000    2140219METADATA_NODE_TBL    SYS_NC00010$    147    260632782510211000000000000000000000    2204026METADATA_NODE_TBL    SYS_NC00010$    148    260653142911245000000000000000000000    231012METADATA_NODE_TBL    SYS_NC00010$    149    260653778902704000000000000000000000    23971METADATA_NODE_TBL    SYS_NC00010$    150    260673981778543000000000000000000000    24864652METADATA_NODE_TBL    SYS_NC00010$    151    260694105733011000000000000000000000    25665265METADATA_NODE_TBL    SYS_NC00010$    152    260714307674104000000000000000000000    26523497METADATA_NODE_TBL    SYS_NC00010$    153    260734433180871000000000000000000000    27378859METADATA_NODE_TBL    SYS_NC00010$    154    260754636358672000000000000000000000    28275592METADATA_NODE_TBL    SYS_NC00010$    155    260774759385913000000000000000000000    2904752METADATA_NODE_TBL    SYS_NC00010$    156    260775236298667000000000000000000000    29694167METADATA_NODE_TBL    SYS_NC00010$    157    265784751623518000000000000000000000    [email protected]_ARCH1METADATA_NODE_TBL    SYS_NC00010$    158    265804877741978000000000000000000000    31196142_0METADATA_NODE_TBL    SYS_NC00010$    159    265825079991375000000000000000000000    320639840METADATA_NODE_TBL    SYS_NC00010$    160    265845360541623000000000000000000000    33001331METADATA_NODE_TBL    SYS_NC00010$    161    265846075455636000000000000000000000    33964646METADATA_NODE_TBL    SYS_NC00010$    162    265866279258451000000000000000000000    3488639METADATA_NODE_TBL    SYS_NC00010$    163    265886559194602000000000000000000000    35808544METADATA_NODE_TBL    SYS_NC00010$    164    265906842527815000000000000000000000    368343METADATA_NODE_TBL    SYS_NC00010$    165    265927126490875000000000000000000000    3788902.8METADATA_NODE_TBL    SYS_NC00010$    166    265947486888311000000000000000000000    389476248METADATA_NODE_TBL    SYS_NC00010$    167    270936721966332000000000000000000000    4.64786656E8METADATA_NODE_TBL    SYS_NC00010$    168    270977445234601000000000000000000000    40841643METADATA_NODE_TBL    SYS_NC00010$    169    270997728265602000000000000000000000    41863853METADATA_NODE_TBL    SYS_NC00010$    170    271018089289210000000000000000000000    42947314METADATA_NODE_TBL    SYS_NC00010$    171    271057942294094000000000000000000000    44088176METADATA_NODE_TBL    SYS_NC00010$    172    271078381607800000000000000000000000    45234968METADATA_NODE_TBL    SYS_NC00010$    173    271098742936053000000000000000000000    463243442METADATA_NODE_TBL    SYS_NC00010$    174    271119104265562000000000000000000000    47415758METADATA_NODE_TBL    SYS_NC00010$    175    271139466207987000000000000000000000    48521995METADATA_NODE_TBL    SYS_NC00010$    176    271159828777821000000000000000000000    49654614METADATA_NODE_TBL    SYS_NC00010$    177    276169582708327000000000000000000000    50611094METADATA_NODE_TBL    SYS_NC00010$    178    276190023576702000000000000000000000    5181366METADATA_NODE_TBL    SYS_NC00010$    179    276230034423768000000000000000000000    53138287METADATA_NODE_TBL    SYS_NC00010$    180    276250556365121000000000000000000000    54490793METADATA_NODE_TBL    SYS_NC00010$    181    276271233374152000000000000000000000    5594565METADATA_NODE_TBL    SYS_NC00010$    182    276311321898357000000000000000000000    57317840METADATA_NODE_TBL    SYS_NC00010$    183    276331921840789000000000000000000000    58738857METADATA_NODE_TBL    SYS_NC00010$    184    276352204868131000000000000000000000    5975736METADATA_NODE_TBL    SYS_NC00010$    185    281381687232524000000000000000000000    61036455METADATA_NODE_TBL    SYS_NC00010$    186    281402287786697000000000000000000000    6247192METADATA_NODE_TBL    SYS_NC00010$    187    281422966033649000000000000000000000    63966456METADATA_NODE_TBL    SYS_NC00010$    188    281463213943857000000000000000000000    6556995METADATA_NODE_TBL    SYS_NC00010$    189    281503382303076000000000000000000000    6705159METADATA_NODE_TBL    SYS_NC00010$    190    281524139770947000000000000000000000    68640225METADATA_NODE_TBL    SYS_NC00010$    191    286553703534748000000000000000000000    7009366METADATA_NODE_TBL    SYS_NC00010$    192    286574538682166000000000000000000000    7173179METADATA_NODE_TBL    SYS_NC00010$    193    286594584028637000000000000000000000    72453089METADATA_NODE_TBL    SYS_NC00010$    194    286634989154807000000000000000000000    74214448METADATA_NODE_TBL    SYS_NC00010$    195    286675474437562000000000000000000000    7610512METADATA_NODE_TBL    SYS_NC00010$    196    286716040181597000000000000000000000    78132118METADATA_NODE_TBL    SYS_NC00010$    197    291746156685640000000000000000000000    8022585METADATA_NODE_TBL    SYS_NC00010$    198    291746238015859000000000000000000000    8039METADATA_NODE_TBL    SYS_NC00010$    199    291746315387112000000000000000000000    8043METADATA_NODE_TBL    SYS_NC00010$    200    291746633291313000000000000000000000    8086410METADATA_NODE_TBL    SYS_NC00010$    201    291806926232477000000000000000000000    [email protected]_ARCH1METADATA_NODE_TBL    SYS_NC00010$    202    291847728732535000000000000000000000    8547103METADATA_NODE_TBL    SYS_NC00010$    203    291888529380533000000000000000000000    877121METADATA_NODE_TBL    SYS_NC00010$    204    296938375249270000000000000000000000    90159107_1METADATA_NODE_TBL    SYS_NC00010$    205    296978861455682000000000000000000000    92076667_1METADATA_NODE_TBL    SYS_NC00010$    206    296999381855621000000000000000000000    933834METADATA_NODE_TBL    SYS_NC00010$    207    297040102970808000000000000000000000    955185METADATA_NODE_TBL    SYS_NC00010$    208    297080511182124000000000000000000000    97371265_1METADATA_NODE_TBL    SYS_NC00010$    209    297121153682059000000000000000000000    994210METADATA_NODE_TBL    SYS_NC00010$    212    365069608624213000000000000000000000    FOREVERMETADATA_NODE_TBL    SYS_NC00010$    213    380625508363292000000000000000000000    INITIALMETADATA_NODE_TBL    SYS_NC00010$    214    401213024081965000000000000000000000    METOP_AVHRR_S10METADATA_NODE_TBL    SYS_NC00010$    216    417053185800123000000000000000000000    PROBAV_L1CMETADATA_NODE_TBL    SYS_NC00010$    217    417053185800123000000000000000000000    PROBAV_L2A_333MMETADATA_NODE_TBL    SYS_NC00010$    218    427745190092595000000000000000000000    RawSegment_PROBAV#2_201406101006METADATA_NODE_TBL    SYS_NC00010$    221    427825536133089000000000000000000000    RemoteSystemObjectManagerMETADATA_NODE_TBL    SYS_NC00010$    224    432589517152557000000000000000000000    SPAEGETATION_L2A_FREEPMETADATA_NODE_TBL    SYS_NC00010$    225    433017356985588000000000000000000000    Segment_PROBAV#1_20140311134941_METADATA_NODE_TBL    SYS_NC00010$    231    438290465950943000000000000000000000    TicketMETADATA_NODE_TBL    SYS_NC00010$    232    447537335489209000000000000000000000    V1KRNP____20120207F034_V1METADATA_NODE_TBL    SYS_NC00010$    233    447557617898813000000000000000000000    V2KRNP____20090219F135_V1METADATA_NODE_TBL    SYS_NC00010$    234    516419571699074000000000000000000000    cus_tifMETADATA_NODE_TBL    SYS_NC00010$    237    531752520548814000000000000000000000    file://localhost/data/MTDA/SpotVMETADATA_NODE_TBL    SYS_NC00010$    239    531752520548814000000000000000000000    file://pdfwork.xxx.xxx.be/data/METADATA_NODE_TBL    SYS_NC00010$    244    542360858001159000000000000000000000    http://www.xxxx-xxxxx.be/quicklMETADATA_NODE_TBL    SYS_NC00010$    249    542360858001159000000000000000000000    http://www.xxxx-xxxx.be/thumbnMETADATA_NODE_TBL    SYS_NC00010$    253    599231229865914000000000000000000000    sharedMETADATA_NODE_TBL    SYS_NC00010$    254    604627931237857000000000000000000000    true

other info:

---the index definitionCREATE INDEX "PROBAV_0100"."METADATA_NODE_TBL_SEM_VAL" ON "PROBAV_0100"."METADATA_NODE_TBL" ("SEMANTICID", NVL("VALUE",'#nvl#'))----database versionOracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionPL/SQL Release 11.2.0.3.0 - Production"CORE    11.2.0.3.0    Production"TNS for Linux: Version 11.2.0.3.0 - ProductionNLSRTL Version 11.2.0.3.0 - Production

So when looking to the height balanced histogram, the 'file://%' part can/will be in 5 buckets (239-234).

Doing some maths with a total rows of 20M and 254 buckets, means that each bucket contains 77K values=> 77K x 5buckets => 385K values

Why does the optimizer does not now this? What to do to help it?

Regards

Tagged:

Answers

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited Sep 14, 2016 8:17AM

    Why aren't you using different tables with appropriate columns to store each type of data? type, key , values is a recipe for awful performance.

    Additionally

    You haven't included the predicates section of your plan - this is ESSENTIAL.

    Whenever you paste output from a query, it's incredibly helpful to include the column headers and ensure you use a fixed width font so we can understand it easily.

    You've also got a frequency histogram on SEMANTICID, why not share this?

    Is your SQL generated?

    LIKE NVL('file://%', '#nvl#') ESCAPE '\') 

    Looks difficult for the optimizer to use your histogram for without evaluating a function - something it typically won't do.

    LIKE 'file://%'

    Is more reasonable.

    But really, fix your data model.

This discussion has been closed.