[Oracle/SQL] Waarde partitie-key niet expliciet beschikbaar

[Knakker]

Ik ben bezig met het optimaliseren van een rapportageproces, en loop er tegenaan dat één van de Oracle-queries waanzinnig slecht presteert. Hoop dat één van jullie mij in de juiste richting kan duwen.

Alle tabellen in de query zijn gepartitioneerd op het veld MODEL_ID. Dit is tevens de primary key van de MODEL tabel.

Deze query presteert prima:

SQL:

SELECT SQ.* FROM (...) SQ INNER JOIN MODEL M ON SQ.MODEL_ID = M.MODEL_ID 
WHERE M.MODEL_ID = 1000

Helaas heeft de applicatie die de query aanroept niet de beschikking over het juiste MODEL_ID (noch kunnen we dit aanpassen), alleen de juiste waarde voor het NAME veld in de MODEL tabel (deze is overigens uniek, maar niet afgedwongen in het schema).

Echter zodra ik de MODEL_ID expliciet uit de where-clause laat, vindt Oracle het nodig om een full-table scan te doen op de onderliggende tabellen (hetgeen een factor 10 langer duurt):

SQL:

SELECT SQ.* FROM (...) SQ INNER JOIN MODEL M ON SQ.MODEL_ID = M.MODEL_ID 
WHERE M.NAME = 'Voorbeeld'

Vanuit mijn semi-leken-begrip had ik gehoopt dat het expliciet maken van de relatie met MODEL_ID middels een subquery tot verbetering zou leiden, maar helaas

SQL:

SELECT SQ.* FROM (...) SQ INNER JOIN MODEL M ON SQ.MODEL_ID = M.MODEL_ID 
WHERE M.MODEL_ID = (SELECT MODEL_ID FROM MODEL WHERE NAME = 'Voorbeeld')

Wat kan ik eraan doen om in het bovenstaande scenario de full-table scan te vermijden?

Dank

[ Voor 3% gewijzigd door Knakker op 11-06-2014 13:59 ]

[Mike78]

Je zou een index op naam kunnen toevoegen? Wellicht dat dat al helpt.

[Knakker]

Helaas kan ik ook het schema niet aanpassen, aangezien het vendor-software betreft die de berekeningen uitvoert.

Mijn enige sturingsmiddel in deze is de query, die staat namelijk in een configuratiebestandje van de (rapportage)applicatie.

[ Voor 10% gewijzigd door Knakker op 11-06-2014 14:45 ]

[Mike78]

Dan wordt het lastig, indien er alleen gezocht wordt op naam zal er vrees ik een tablescan plaats moeten vinden.

[P_de_B]

Kun je een materialized view maken waarop de query de selects doet?

[Knakker]

Ik heb geprobeerd de subquery die de MODEL_ID opzoekt te 'materializen', i.e.

SQL:

WITH M AS (SELECT /*+ MATERIALIZE */ MODEL_ID FROM MODEL WHERE NAME = 'Voorbeeld')
SELECT SQ.* FROM (...) SQ INNER JOIN M ON SQ.MODEL_ID = M.MODEL_ID  

... maar dit geeft geen vebetering, helaas. Maar goed mogelijk dat ik het 'materialized' concept niet begrijp - welk gedeelte van de query zou ik moeten materializen? De subquery SQ?

[poehee]

Het ruikt naar statistics issues. Worden er table statistics bijgehouden op de bron database? En hoe worden ze berekend??

Met Oracle hints kun je soms ook een en ander sturen. Meestal kom ik er wel uit (maar liefst pak ik het bij de bron aan, en dat betreft meestal statistics) , maar jouw voorbeeld is zo triviaal dat het aan statistics (of mogelijk sommige Oracle Optimizer parameter -- je kunt Oracle in een Oracle 8 of 9 mode zetten bijvoorbeeld -- ik heb daar bijzondere ervaringen mee...) ligt.


Er werd niet gedoeld op de materialized hint, maar op het zelf aanmaken van een materialized view (of tabel gebaseerd op je query). Dan kun je daarna los gaan met indexen e.d. Maar we hebben geen idee of dat mogelijk is (datasizing, rechten, vrije ruimte, klagende dba's --terecht vaak hoor :-) --)

[ Voor 28% gewijzigd door poehee op 11-06-2014 16:26 ]

[Knakker]

Heb even gekeken, de statistics zijn voor het laatst afgelopen vrijdag bijgewerkt. De data die ik in de query bekijk is een maandje ouder - dus dat zou ok moeten zijn.

Ik denk niet dat ik op onze productiedatabase zelf een table aan kan maken

Wat voor andere hints zou ik kunnen geruiken?

[ Voor 29% gewijzigd door Knakker op 11-06-2014 16:48 ]

[CyBeRSPiN]

SQL:

SELECT SQ.* FROM (...) SQ INNER JOIN MODEL M ON SQ.MODEL_ID = M.MODEL_ID 
WHERE M.MODEL_ID = (SELECT MODEL_ID FROM MODEL WHERE NAME = 'Voorbeeld')

Waarom niet

SQL:

SELECT SQ.* FROM (...) SQ 
WHERE SQ.MODEL_ID = (SELECT MODEL_ID FROM MODEL WHERE NAME = 'Voorbeeld')

en waarom SQ.* en niet de kolommen explicieit benoemen? Of worden ze echt allemaal gebruikt?

Kun je een explain plan laten zien?

Trouwens:

(deze is overigens uniek, maar niet afgedwongen in het schema).

Dat is een bug die je bij de Vendor moet aankaarten. Dit nekt je performance sowieso..

[ Voor 17% gewijzigd door CyBeRSPiN op 11-06-2014 16:56 ]

[Knakker]

Beide is het geval in de oorspronkelijke query (kolommen expliciet benoemd, en subquery op de MODEL_ID van de oorspronkelijke tabellen), sorry voor de verwarring.

Onderstaand het plan. Zodra ik dus een expliciete waarde voor MODEL_ID invul wordt de eerste PARTITION LIST ALL van elke serie van twee vervangen door PARTITION LIST SINGLE en runt de query 10x sneller (de tweede PARTITION LIST ALL is correct en in dit geval noodzakelijk).

De 'cost' daalt in dat geval naar 89K, maar ik weet eigenlijk nooit zo goed of dat iets betekent of niet.

code:

-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                              | Name                           | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                       |                                |     1 |  4607 |       |  4989K  (2)| 16:37:58 |       |       |
|   1 |  NESTED LOOPS                                          |                                |     1 |  4607 |       |  4989K  (2)| 16:37:58 |       |       |
|   2 |   NESTED LOOPS                                         |                                |    33 |   148K|       |  4989K  (2)| 16:37:58 |       |       |
|   3 |    NESTED LOOPS                                        |                                |     1 |  4580 |       |  4989K  (2)| 16:37:58 |       |       |
|   4 |     NESTED LOOPS                                       |                                |    39 |   174K|       |  4989K  (2)| 16:37:58 |       |       |
|   5 |      NESTED LOOPS                                      |                                |     1 |  4553 |       |  4989K  (2)| 16:37:58 |       |       |
|   6 |       NESTED LOOPS                                     |                                |     1 |  4540 |       |  4989K  (2)| 16:37:58 |       |       |
|   7 |        NESTED LOOPS                                    |                                |     1 |  4527 |       |  4989K  (2)| 16:37:58 |       |       |
|   8 |         NESTED LOOPS                                   |                                |     1 |    68 |       |     2   (0)| 00:00:01 |       |       |
|   9 |          TABLE ACCESS BY INDEX ROWID                   | MODEL                          |     1 |    34 |       |     1   (0)| 00:00:01 |       |       |
|* 10 |           INDEX UNIQUE SCAN                            | MODEL_AK                       |     1 |       |       |     1   (0)| 00:00:01 |       |       |
|  11 |          TABLE ACCESS BY INDEX ROWID                   | MODEL                          |     1 |    34 |       |     1   (0)| 00:00:01 |       |       |
|* 12 |           INDEX UNIQUE SCAN                            | MODEL_AK                       |     1 |       |       |     1   (0)| 00:00:01 |       |       |
|  13 |         VIEW                                           |                                |     1 |  4459 |       |  4989K  (2)| 16:37:58 |       |       |
|  14 |          SORT GROUP BY                                 |                                |     1 |   697 |       |  4989K  (2)| 16:37:58 |       |       |
|  15 |           NESTED LOOPS                                 |                                |     1 |   697 |       |  4989K  (2)| 16:37:58 |       |       |
|  16 |            NESTED LOOPS                                |                                |     1 |   657 |       |  4989K  (2)| 16:37:58 |       |       |
|  17 |             NESTED LOOPS OUTER                         |                                |     1 |   617 |       |  4989K  (2)| 16:37:58 |       |       |
|  18 |              NESTED LOOPS OUTER                        |                                |     1 |   611 |       |  4989K  (2)| 16:37:58 |       |       |
|  19 |               NESTED LOOPS                             |                                |     1 |   607 |       |  4989K  (2)| 16:37:58 |       |       |
|  20 |                NESTED LOOPS OUTER                      |                                |     1 |   580 |       |  4989K  (2)| 16:37:58 |       |       |
|  21 |                 NESTED LOOPS                           |                                |     1 |   553 |       |  4989K  (2)| 16:37:58 |       |       |
|* 22 |                  HASH JOIN                             |                                |    40 | 19800 |       |  4989K  (2)| 16:37:58 |       |       |
|  23 |                   VIEW                                 | index$_join$_030               |    63 |   504 |       |     2   (0)| 00:00:01 |       |       |
|* 24 |                    HASH JOIN                           |                                |       |       |       |            |          |       |       |
|  25 |                     INDEX FAST FULL SCAN               | ACC_STRUCT_MODEL_AK            |    63 |   504 |       |     1   (0)| 00:00:01 |       |       |
|  26 |                     INDEX FAST FULL SCAN               | PK_ACC_STRUCT_MODEL            |    63 |   504 |       |     1   (0)| 00:00:01 |       |       |
|  27 |                   NESTED LOOPS                         |                                |    40 | 19480 |       |  4989K  (2)| 16:37:57 |       |       |
|  28 |                    NESTED LOOPS                        |                                |    40 | 19480 |       |  4989K  (2)| 16:37:57 |       |       |
|  29 |                     NESTED LOOPS                       |                                |    40 | 18720 |       |  4989K  (2)| 16:37:57 |       |       |
|* 30 |                      HASH JOIN                         |                                |    40 | 11240 |       |  4989K  (2)| 16:37:57 |       |       |
|  31 |                       VIEW                             |                                |   761 | 23591 |       |   439   (1)| 00:00:06 |       |       |
|  32 |                        UNION-ALL                       |                                |       |       |       |            |          |       |       |
|  33 |                         PARTITION LIST ALL             |                                |   401 | 12431 |       |   155   (0)| 00:00:02 |     1 |    58 |
|  34 |                          PARTITION LIST ALL            |                                |   401 | 12431 |       |   155   (0)| 00:00:02 |     1 |  LAST |
|  35 |                           TABLE ACCESS FULL            | RES_DIM_STATIC_SCENARIO        |   401 | 12431 |       |   155   (0)| 00:00:02 |     1 |   175 |
|  36 |                         PARTITION LIST ALL             |                                |   360 | 11160 |       |   284   (1)| 00:00:04 |     1 |    61 |
|  37 |                          PARTITION LIST ALL            |                                |   360 | 11160 |       |   284   (1)| 00:00:04 |     1 |  LAST |
|  38 |                           TABLE ACCESS FULL            | RES_DIM_DYN_WHATIF             |   360 | 11160 |       |   284   (1)| 00:00:04 |     1 |   281 |
|* 39 |                       HASH JOIN                        |                                | 76516 |    18M|       |  4989K  (2)| 16:37:52 |       |       |
|  40 |                        PARTITION LIST ALL              |                                |   321 |  4494 |       |   437   (1)| 00:00:06 |     1 |    61 |
|  41 |                         PARTITION LIST ALL             |                                |   321 |  4494 |       |   437   (1)| 00:00:06 |     1 |  LAST |
|  42 |                          TABLE ACCESS FULL             | RES_SOLVE                      |   321 |  4494 |       |   437   (1)| 00:00:06 |     1 |   398 |
|* 43 |                        HASH JOIN                       |                                |   183K|    41M|       |  4988K  (2)| 16:37:47 |       |       |
|  44 |                         VIEW                           | index$_join$_004               |    61 |  2074 |       |     2   (0)| 00:00:01 |       |       |
|* 45 |                          HASH JOIN                     |                                |       |       |       |            |          |       |       |
|  46 |                           INDEX FAST FULL SCAN         | MODEL_AK                       |    61 |  2074 |       |     1   (0)| 00:00:01 |       |       |
|  47 |                           INDEX FAST FULL SCAN         | PK_MODEL                       |    61 |  2074 |       |     1   (0)| 00:00:01 |       |       |
|* 48 |                         HASH JOIN                      |                                |   183K|    35M|       |  4988K  (2)| 16:37:47 |       |       |
|  49 |                          VIEW                          | index$_join$_005               |   820 | 27060 |       |     4   (0)| 00:00:01 |       |       |
|* 50 |                           HASH JOIN                    |                                |       |       |       |            |          |       |       |
|  51 |                            INDEX FAST FULL SCAN        | PK_SLV_JOB                     |   820 | 27060 |       |     1   (0)| 00:00:01 |       |       |
|  52 |                            INDEX FAST FULL SCAN        | SLV_JOB_AK                     |   820 | 27060 |       |     4   (0)| 00:00:01 |       |       |
|  53 |                          NESTED LOOPS                  |                                |  2289K|   368M|       |  4988K  (2)| 16:37:46 |       |       |
|  54 |                           TABLE ACCESS BY INDEX ROWID  | MODEL                          |     1 |    34 |       |     1   (0)| 00:00:01 |       |       |
|* 55 |                            INDEX UNIQUE SCAN           | MODEL_AK                       |     1 |       |       |     1   (0)| 00:00:01 |       |       |
|* 56 |                           VIEW                         |                                |  2289K|   294M|       |  4988K  (2)| 16:37:46 |       |       |
|  57 |                            UNION-ALL                   |                                |       |       |       |            |          |       |       |
|  58 |                             SORT GROUP BY              |                                |    27M|  2896M|  3851M|  1044K  (2)| 03:28:50 |       |       |
|  59 |                              PARTITION LIST ALL        |                                |    27M|  2896M|       |   357K  (2)| 01:11:29 |     1 |    58 |
|  60 |                               PARTITION LIST ALL       |                                |    27M|  2896M|       |   357K  (2)| 01:11:29 |     1 |  LAST |
|  61 |                                TABLE ACCESS FULL       | RES_FACT_NPV                   |    27M|  2896M|       |   357K  (2)| 01:11:29 |     1 |   175 |
|  62 |                             SORT GROUP BY              |                                |   102M|    10G|    14G|  3944K  (2)| 13:08:57 |       |       |
|  63 |                              PARTITION LIST ALL        |                                |   102M|    10G|       |  1346K  (2)| 04:29:14 |     1 |    61 |
|  64 |                               PARTITION LIST ALL       |                                |   102M|    10G|       |  1346K  (2)| 04:29:14 |     1 |  LAST |
|  65 |                                TABLE ACCESS FULL       | RES_FACT_DYN_NPV               |   102M|    10G|       |  1346K  (2)| 04:29:14 |     1 |   253 |
|* 66 |                      TABLE ACCESS BY GLOBAL INDEX ROWID| CONTRACT                       |     1 |   187 |       |     1   (0)| 00:00:01 | ROWID | ROWID |
|* 67 |                       INDEX UNIQUE SCAN                | PK_CONTRACT                    |     1 |       |       |     1   (0)| 00:00:01 |       |       |
|  68 |                     PARTITION LIST ITERATOR            |                                |     1 |       |       |     1   (0)| 00:00:01 |   KEY |   KEY |
|  69 |                      PARTITION LIST ITERATOR           |                                |     1 |       |       |     1   (0)| 00:00:01 |   KEY |   KEY |
|* 70 |                       INDEX RANGE SCAN                 | CONTRACT_ACCOUNT_MAPPING_CO_FK |     1 |       |       |     1   (0)| 00:00:01 |   KEY |   KEY |
|* 71 |                    TABLE ACCESS BY LOCAL INDEX ROWID   | CONTRACT_ACCOUNT_MAPPING       |     1 |    19 |       |     1   (0)| 00:00:01 |     1 |     1 |
|* 72 |                  TABLE ACCESS BY INDEX ROWID           | ACCOUNT                        |     1 |    58 |       |     1   (0)| 00:00:01 |       |       |
|* 73 |                   INDEX UNIQUE SCAN                    | PK_ACCOUNT                     |     1 |       |       |     1   (0)| 00:00:01 |       |       |
|  74 |                 PARTITION LIST ITERATOR                |                                |     1 |    27 |       |     5   (0)| 00:00:01 |   KEY |   KEY |
|  75 |                  PARTITION LIST ITERATOR               |                                |     1 |    27 |       |     5   (0)| 00:00:01 |   KEY |   KEY |
|* 76 |                   TABLE ACCESS FULL                    | RES_DIM_TBS                    |     1 |    27 |       |     5   (0)| 00:00:01 |   KEY |   KEY |
|* 77 |                TABLE ACCESS BY INDEX ROWID             | ACCOUNT                        |     1 |    27 |       |     1   (0)| 00:00:01 |       |       |
|* 78 |                 INDEX UNIQUE SCAN                      | PK_ACCOUNT                     |     1 |       |       |     1   (0)| 00:00:01 |       |       |
|  79 |               VIEW PUSHED PREDICATE                    | VW_DATA_FX_OBS                 |     1 |     4 |       |    10   (0)| 00:00:01 |       |       |
|  80 |                NESTED LOOPS                            |                                |     1 |    32 |       |    10   (0)| 00:00:01 |       |       |
|  81 |                 NESTED LOOPS                           |                                |    41 |    32 |       |    10   (0)| 00:00:01 |       |       |
|  82 |                  NESTED LOOPS                          |                                |    41 |   902 |       |     2   (0)| 00:00:01 |       |       |
|* 83 |                   TABLE ACCESS BY INDEX ROWID          | RISK_FACTOR_CONFIGURATION      |     1 |    13 |       |     1   (0)| 00:00:01 |       |       |
|* 84 |                    INDEX RANGE SCAN                    | RISK_FACTOR_CONF_MODEL_FK      |     1 |       |       |     1   (0)| 00:00:01 |       |       |
|* 85 |                   INDEX RANGE SCAN                     | OBS_FX_RATE_AK                 |    41 |   369 |       |     1   (0)| 00:00:01 |       |       |
|* 86 |                  INDEX UNIQUE SCAN                     | PK_CURRENCY_DEF                |     1 |       |       |     1   (0)| 00:00:01 |       |       |
|* 87 |                 TABLE ACCESS BY INDEX ROWID            | CURRENCY_DEF                   |     1 |    10 |       |     1   (0)| 00:00:01 |       |       |
|  88 |              VIEW PUSHED PREDICATE                     |                                |     1 |     6 |       |     1   (0)| 00:00:01 |       |       |
|  89 |               SORT GROUP BY                            |                                |     1 |    32 |       |     1   (0)| 00:00:01 |       |       |
|  90 |                PARTITION LIST SINGLE                   |                                |     1 |    32 |       |     1   (0)| 00:00:01 |   KEY |   KEY |
|  91 |                 PARTITION LIST SINGLE                  |                                |     1 |    32 |       |     1   (0)| 00:00:01 |       |       |
|* 92 |                  TABLE ACCESS BY LOCAL INDEX ROWID     | CONTRACT_CASHFLOW_DATA         |     1 |    32 |       |     1   (0)| 00:00:01 |   KEY |   KEY |
|* 93 |                   INDEX RANGE SCAN                     | CT_CASHFLOW_DATA_CONTRACT_FK   |     1 |       |       |     1   (0)| 00:00:01 |   KEY |   KEY |
|  94 |             TABLE ACCESS BY INDEX ROWID                | ACCOUNT                        |     1 |    40 |       |     1   (0)| 00:00:01 |       |       |
|* 95 |              INDEX UNIQUE SCAN                         | ACCOUNT_AK                     |     1 |       |       |     1   (0)| 00:00:01 |       |       |
|  96 |            TABLE ACCESS BY INDEX ROWID                 | ACCOUNT                        |     1 |    40 |       |     1   (0)| 00:00:01 |       |       |
|* 97 |             INDEX UNIQUE SCAN                          | ACCOUNT_AK                     |     1 |       |       |     1   (0)| 00:00:01 |       |       |
|* 98 |        TABLE ACCESS BY INDEX ROWID                     | RISK_FACTOR_CONFIGURATION      |     1 |    13 |       |     1   (0)| 00:00:01 |       |       |
|* 99 |         INDEX RANGE SCAN                               | RISK_FACTOR_CONF_MODEL_FK      |     1 |       |       |     1   (0)| 00:00:01 |       |       |
|*100 |       TABLE ACCESS BY INDEX ROWID                      | RISK_FACTOR_CONFIGURATION      |     1 |    13 |       |     1   (0)| 00:00:01 |       |       |
|*101 |        INDEX RANGE SCAN                                | RISK_FACTOR_CONF_MODEL_FK      |     1 |       |       |     1   (0)| 00:00:01 |       |       |
| 102 |      TABLE ACCESS BY INDEX ROWID                       | OBS_FX_RATE                    |    39 |   663 |       |     1   (0)| 00:00:01 |       |       |
|*103 |       INDEX RANGE SCAN                                 | OBS_FX_RATE_AK                 |    41 |       |       |     1   (0)| 00:00:01 |       |       |
|*104 |     TABLE ACCESS BY INDEX ROWID                        | CURRENCY_DEF                   |     1 |    10 |       |     1   (0)| 00:00:01 |       |       |
|*105 |      INDEX UNIQUE SCAN                                 | PK_CURRENCY_DEF                |     1 |       |       |     1   (0)| 00:00:01 |       |       |
| 106 |    TABLE ACCESS BY INDEX ROWID                         | OBS_FX_RATE                    |    39 |   663 |       |     1   (0)| 00:00:01 |       |       |
|*107 |     INDEX RANGE SCAN                                   | OBS_FX_RATE_AK                 |    41 |       |       |     1   (0)| 00:00:01 |       |       |
|*108 |   TABLE ACCESS BY INDEX ROWID                          | CURRENCY_DEF                   |     1 |    10 |       |     1   (0)| 00:00:01 |       |       |
|*109 |    INDEX UNIQUE SCAN                                   | PK_CURRENCY_DEF                |     1 |       |       |     1   (0)| 00:00:01 |       |       |

[CyBeRSPiN]

Dit is wel een stuk complexere query dan je in de OP liet zien
Lastig dat je weinig speelruimte hebt. Je zou wellicht iets met de LEADING hint kunnen bereiken door de MODEL tabel als driving table te gebruiken. Ook zou je wellicht de partition index(es) expliciet als hint kunnen zetten?

Heb je OEM met het DB tuning pack tot je beschikking? Heb zelf wel goede ervaring met de SQL Tuning advisor, dan kun je evt een SQL profile aanmaken die een beter plan geeft.

[Knakker]

Ja het is een pareltje

De leading hint op de MODEL tabel doet helaas weinig. Ik zal eens kijken of expliciet partitie key aangeven iets uithaalt.

Ik heb geen toegang tot de software die je noemt. Ik ben namelijk ook 'maar gewoon' business consultant (wel met redelijk wat hobbymatige IT-ervaring, dus wellicht wat handiger dan de gemiddelde business user) en die scheiding is hier heel strict.

Ik heb het ondertussen ook bij de in-house database experts gelegd, maar in een grote organisatie als deze is het altijd afwachten maar wanneer je daarop reaktie krijgt, en of je daar vervolgens wat aan hebt.

Dank tot zover iig, en als er nog suggesties zijn: ik hoor het graag

[Verwijderd]

Met een bind als deze is een cost van 89k wel erg veel.

Heb je hier wat aan? http://www.oracle-base.com/articles/misc/with-clause.php Dit kan je gebruiken om een 'virtuele tabel' aan te maken in het geheugen. Ik gebruik het zelf vaak als beter alternatief voor SELECT statements binnen het FROM(of evt. WHERE) stuk van je hoofdquery. Het werkt significant sneller. Kan op basis van de query die je geeft niet helemaal inschatten of het in jouw situatie gaat helpen, aangezien je query complexer dan dit lijkt te zijn. Maar dan heb je iig weer wat om te proberen.

[Knakker]

Ik had al wat met WITH gespeeld, maar zal daar wat meer tijd in stoppen... zodra ik hier weer tijd voor heb.

Kun je uitleggen waarom de 89k cost 'voor een bind als deze' veel is? Ik weet namelijk nooit zo goed hoe ik dat getal moet interpreteren.

[EfBe]

Indexen behoren niet tot het schema, maar tot de data. Je kunt prima indexen toevoegen, het verandert niets aan het schema. De query in de OP gaat alleen performen wanneer je een index toevoegt op Naam.

[JaQ]

Knakker schreef op woensdag 11 juni 2014 @ 13:55:
Wat kan ik eraan doen om in het bovenstaande scenario de full-table scan te vermijden?

Een index toevoegen, immers je filtert op een niet geïndexeerde kolom. Als ik je verhaal lees zou je zelfs een unieke index kunnen toevoegen, echter daarmee veroorzaak je mogelijk op enig moment een applicatie-error.

Knakker schreef op woensdag 11 juni 2014 @ 17:32:
De leading hint op de MODEL tabel doet helaas weinig. Ik zal eens kijken of expliciet partitie key aangeven iets uithaalt.

Uiteraard niet. Je wilt snel filteren op naam, dat kan enkel door een index toe te voegen (of door data redundant op te slaan met een materialized view die je on commit laat bijwerken. Maar doe dat alsjeblieft niet!)

EfBe schreef op maandag 16 juni 2014 @ 09:17:
Indexen behoren niet tot het schema, maar tot de data. Je kunt prima indexen toevoegen, het verandert niets aan het schema. De query in de OP gaat alleen performen wanneer je een index toevoegt op Naam.

Dit dus.

[ Voor 23% gewijzigd door JaQ op 16-06-2014 13:53 ]

[P_de_B]

JaQ schreef op maandag 16 juni 2014 @ 13:51:
[...]

Een index toevoegen, immers je filtert op een niet geïndexeerde kolom. Als ik je verhaal lees zou je zelfs een unieke index kunnen toevoegen, echter daarmee veroorzaak je mogelijk op enig moment een applicatie-error.


[...]

Uiteraard niet. Je wilt snel filteren op naam, dat kan enkel door een index toe te voegen (of door data redundant op te slaan met een materialized view die je on commit laat bijwerken. Maar doe dat alsjeblieft niet!)


[...]

Dit dus.

Eens met het advies om een index toe te voegen, maar als dat niet mogelijk is (hoewel - weer eens - dit zou moeten kunnen) waarom raad je een materialized view af?

[CyBeRSPiN]

EfBe schreef op maandag 16 juni 2014 @ 09:17:
Indexen behoren niet tot het schema, maar tot de data. Je kunt prima indexen toevoegen, het verandert niets aan het schema. De query in de OP gaat alleen performen wanneer je een index toevoegt op Naam.

Totdat de Vendor een update uitbrengt en geen rekening houdt met "gerommel" in hun "eigen" schema waardoor hun update stukloopt.. Lukraak indexes toevoegen is niet altijd een oplossing, in dit geval waarschijnlijk wel, maar er zitten niet enkel voordelen aan indexes..
Weet niet wat voor contract bedrijf van TS heeft met de leverancier, maar m.i. zou de leverancier dit performance issue zelf moeten oplossen.

[ Voor 10% gewijzigd door CyBeRSPiN op 16-06-2014 14:26 ]

[JaQ]

P_de_B schreef op maandag 16 juni 2014 @ 14:16:
Eens met het advies om een index toe te voegen, maar als dat niet mogelijk is (hoewel - weer eens - dit zou moeten kunnen) waarom raad je een materialized view af?

In mijn ervaring zijn materialized views beheer-intensief. Je moet die view verversen (wat soms ook weer fout gaat) en daardoor loop je het risico op incorrecte output (functioneel).

In een OLTP omgeving zal je on commit moeten verversen voor een consistente rapportage, dan doe je dus dubbel de I/O met inserten van data. Allemaal dingen waar ik niet zo blij van word.

Tot slot vind ik mviews schieten met een kanon op een mug. Inzet van Exadata (oftewel super snel I/O en veel RAM voor de database) kan ook helpen, maar is niet zo elegant voor deze use case

(vooral omdat een index aanleggen zoveel simpeler en vooral ook de juiste oplossing is )

Overigens: ik ben ook maar gewoon een techneut met een mening. Ik pretendeer niet de wijsheid in pacht te hebben en roep enkel op basis van eigen ervaringen. (En daarmee wil ik jou niet degraderen, enkel mezelf kwalificeren)

[EfBe]

CyBeRSPiN schreef op maandag 16 juni 2014 @ 14:20:
[...]
Totdat de Vendor een update uitbrengt en geen rekening houdt met "gerommel" in hun "eigen" schema waardoor hun update stukloopt.. Lukraak indexes toevoegen is niet altijd een oplossing, in dit geval waarschijnlijk wel, maar er zitten niet enkel voordelen aan indexes..
Weet niet wat voor contract bedrijf van TS heeft met de leverancier, maar m.i. zou de leverancier dit performance issue zelf moeten oplossen.

De leverancier zou het inderdaad zelf moeten oplossen ALS ze indexes bijleveren in de DDL SQL van hun schema maar dat hoeft niet.

Ik begrijp je argument tegen het plaatsen van deze indexes overigens niet, want als de leverancier het schema wijzigt dan vervallen de indexen gewoon. Indexen zijn tools die een DBA toepast om in te springen op hoe de data gebruikt wordt, niet hoe de data is opgeslagen.

Ik kan me dan ook totaal niet voorstellen dat de leverancier van de software hier ook maar 1 bezwaar tegen zou hebben.

[CyBeRSPiN]

Twee redenen uit eigen ervaring:
1) Niet elk product gebruikt een geavanceerde ORM die wijzigingen aan het schema buiten het product om ondersteunt
2) Een index kan in bepaalde gevallen juist een performanceprobleem introduceren, bijvoorbeeld wanneer een full-table scan gewenst is en er ineens via de nieuwe index gewerkt gaat worden.
Wanneer de leverancier verantwoordelijk is voor de performance, zit deze er niet op te wachten dat er aan het schema geknutseld wordt. (in mijn optiek valt alles wat in een schema(=user) zit onder het schema.. niet enkel tabellen/views, ook indexes, constraints, triggers, etc)

Maar genoeg off-topic

[JaQ]

CyBeRSPiN schreef op dinsdag 17 juni 2014 @ 09:31:
2) Een index kan in bepaalde gevallen juist een performanceprobleem introduceren, bijvoorbeeld wanneer een full-table scan gewenst is en er ineens via de nieuwe index gewerkt gaat worden.

Kan je hier een voorbeeld van bedenken? Ik namelijk niet... (De CBO hoort dit op te lossen)

Edit:
Nou ja, tenzij je een bitmap index op een kolom zet die vaak wordt gewijzigd, dan vertraagd je update enorm (maar dat heeft weinig met FTS te maken). Bijwerken van de index bij insert/update/delete kost wel iets aan performance, maar dat is wederom geen FTS.

[ Voor 27% gewijzigd door JaQ op 18-06-2014 08:41 ]

[juhet]

levert dit:

WHERE NAME = 'Voorbeeld'

altijd 1 rij op? anders kun je dit nog proberen:

WHERE NAME = 'Voorbeeld' and ROWNUM = 1

[CyBeRSPiN]

JaQ schreef op woensdag 18 juni 2014 @ 08:36:
[...]

Kan je hier een voorbeeld van bedenken? Ik namelijk niet... (De CBO hoort dit op te lossen)

Je geeft het antwoord zelf al: in gevallen waarbij de CBO zn werk niet goed doet.. En waarbij dan onduidelijk blijft waarom.. (table stats, column histograms, index stats, system stats, etc.. vele factoren waarbij niet altijd goed te achterhalen is waarom het plan niet voor de FTS gaat maar toch via een index terwijl dat niet efficient is)

Het gaat heel vaak goed, maar als je ineens een query hebt die miljoenen rijen ineens nested-loop 'table access by rowid' gaat doen ipv in een keer een FTS..

En inderdaad: indexes leveren sowieso overhead op bij DML hoewel dat meevalt.
Ook kunnen indexes veel storage in beslag nemen, soms meer dan de tabellen zelf (bij meerdere indexes).

[ Voor 6% gewijzigd door CyBeRSPiN op 18-06-2014 10:04 ]

[EfBe]

CyBeRSPiN schreef op dinsdag 17 juni 2014 @ 09:31:
Twee redenen uit eigen ervaring:
1) Niet elk product gebruikt een geavanceerde ORM die wijzigingen aan het schema buiten het product om ondersteunt

wat heeft dat met het probleem te maken? OP heeft een klassiek 'table scan is traag' probleem wat al jaren geleden is opgelost middels de introductie van een index.

2) Een index kan in bepaalde gevallen juist een performanceprobleem introduceren, bijvoorbeeld wanneer een full-table scan gewenst is en er ineens via de nieuwe index gewerkt gaat worden.
Wanneer de leverancier verantwoordelijk is voor de performance, zit deze er niet op te wachten dat er aan het schema geknutseld wordt. (in mijn optiek valt alles wat in een schema(=user) zit onder het schema.. niet enkel tabellen/views, ook indexes, constraints, triggers, etc)

Tja, als dat zou zijn zou de index voor iedere gebruiker hetzelfde zijn, maar dat is niet het geval: indexes zijn afhankelijk van de table contents en gebruik van de data, dus een andere instance kan wellicht om andere indexes vragen.

En waarom zou een dat een full table scan gewenst kunnen zijn, we praten hier niet over een eventual consistent nosql geval?

[CyBeRSPiN]

Laat maar.. teveel offtopic..
Maar een FTS is dus bijv. veel efficienter als je meerdere columns van een tabel selecteert (die niet allemaal in (dezelfde) index zitten) en je meer dan de helft van de rijen nodig hebt.. Simpel gezegd: Dan fietst de DB sneller de tabel van A-Z door en filtert de gewenste rijen, dan dat ie eerst de index doorfietst en heen en weer slingert om elke rij uit de tabel te pakken.
Heeft niets met issue van TS te maken, maar haakte in op jouw ongenuanceerde uitspraak:

Indexen behoren niet tot het schema, maar tot de data. Je kunt prima indexen toevoegen, het verandert niets aan het schema.

wat mijns inziens niet klopt (definitie van wat schema is en dat een index toevoegen totaal risicoloos is)

@TS: lekker die index toevoegen, dat zal je probleem waarschijnlijk oplossen

[ Voor 13% gewijzigd door CyBeRSPiN op 18-06-2014 10:57 ]

[Knakker]

Een kleine update. Het blijkt dat de MODEL tabel een index op NAME hééft. Helaas doet de database (te) weinig met deze index om te begrijpen dat één NAME slechts één MODEL_ID heeft en dat dit vervolgens moet doorwerken in de relatie tussen alle andere tabellen (20+).

We hebben de doorlooptijd met een factor 4 naar beneden kunnen krijgen, door

• de query te starten met een WITH die de juiste MODEL_ID tevoorschijn tovert en dat als basis te gebruiken in de query ipv de MODEL tabel zelf
• in álle subqueries een expliciete where clause op te nemen die reeds filtert op deze MODEL_ID (dus niet alleen af te laten hangen van de join op de partitie key)
• vier subqueries die onafhankelijk van elkaar zijn (louter UNION ALL) parallel uit te voeren middels een hint.

Optimaal is het niet -de query met een waarde voor MODEL_ID is nog steeds 2-3x sneller, en een stuk 'cleaner'- maar voor nu laten we het er maar bij. Dit rapportage proces wordt begin volgend jaar vervangen door een implementatie in BO, waarbij we wél direct een ID kunnen opgeven. BO brengt weer andere problemen met zich mee, maar dat zien we dan wel weer.

Dank voor alle reakties in ieder geval! Mij is het merendeel van de technischere discussie ontgaan, maar ik neem het allemaal in me op - en wie weet komt er een keer dat het lichtje gaat schijnen.