Results 1 to 3 of 3

Thread: query tuning

  1. #1
    Join Date
    Jan 2004
    Location
    India
    Posts
    191

    Unanswered: query tuning

    Hi,
    Could you please help to tune the below query it is runing forever in dataware housing database. The query is generated by third party tool.

    (a)The query is accessing following tables:
    1.invoice_line( 8825567 -rows)
    2.customer_master(103529)
    3.exchange_rates_year( 297)
    4.fiscal_week ( 473)
    5.db_master(61)
    6.item_master(1908810)

    (b)Invoice_line table is partition on column db_code.

    (c)All tables and indexes are recently analyzed.

    (d) oracle version
    BANNER
    ----------------------------------------------------------------
    Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
    PL/SQL Release 9.2.0.4.0 - Production
    CORE 9.2.0.3.0 Production
    TNS for HPUX: Version 9.2.0.4.0 - Production
    NLSRTL Version 9.2.0.4.0 - Production

    (e) Values for some configuration parameters
    sort_area_size integer 2048000
    db_file_multiblock_read_count integer 32
    db_cache_size big integer 1375731712
    sort_area_retained_size integer 2048000
    sort_area_size integer 2048000
    hash_area_size integer 4096000
    hash_join_enabled boolean TRUE
    optimizer_index_caching integer 0
    optimizer_index_cost_adj integer 100

    (f)Query with tracefile

    PHP Code:
    SELECT
    DISTINCT
      DB_MASTER
    .REPORT_REGION_NAME,
      
    sum(INVOICE_LINE.NET_SALES*EXCHANGE_RATES_FLOAT_YEAR.EXCHANGE_RATE),
      
    LINE_ORDER_WEEK.REL_MONTH,
      
    SHIP_WEEK.REL_MONTH
    FROM
      datap
    .DB_MASTER,
      
    datap.INVOICE_LINE,
      
    datap.EXCHANGE_RATES_YEAR  EXCHANGE_RATES_FLOAT_YEAR,
      
    datap.FISCAL_WEEK  LINE_ORDER_WEEK,
      
    datap.FISCAL_WEEK  SHIP_WEEK,
      
    datap.ITEM_MASTER,
      
    datap.CUSTOMER_MASTER  CUSTOMER_MASTER_SOLD
    WHERE
      
    INVOICE_LINE.ITEM_ID ITEM_MASTER.ITEM_ID  )
      AND  ( 
    INVOICE_LINE.DB_CODE DB_MASTER.DB_CODE  )
      AND  ( 
    CUSTOMER_MASTER_SOLD.CUST_ID=INVOICE_LINE.CUST_SOLD_ID  )
      AND  ( 
    EXCHANGE_RATES_FLOAT_YEAR.CURR_CODEINVOICE_LINE.TRS_CURR And EXCHANGE
    _RATES_FLOAT_YEAR
    .EXCHANGE_RATE_TYPE='B' AND EXCHANGE_RATES_FLOAT_YEAR.FISCAL_YE
    AR
    2004 )
      AND  ( 
    INVOICE_LINE.SHIP_DATE >= SHIP_WEEK.FISCAL_DT_START and INVOICE_LINE.SH
    IP_DATE  
    SHIP_WEEK.FISCAL_DT_END  )
      AND  ( 
    INVOICE_LINE.ORDER_DATE >= LINE_ORDER_WEEK.FISCAL_DT_START and INVOICE_
    LINE
    .ORDER_DATE  LINE_ORDER_WEEK.FISCAL_DT_END  )
      AND  (
      ( 
    ITEM_MASTER.SBG_CODE 'CO'  )
      AND  ( 
    CUSTOMER_MASTER_SOLD.TRD_INTRACO 'TRADE'  )
      AND  ( 
    ITEM_MASTER.F_CURRENT 'Y'  )
      AND  ( 
    INVOICE_LINE.LINE_TYPE ' '  )
      AND  
    SHIP_WEEK.REL_MONTH  BETWEEN   -12 AND -1
      
    AND  ( DB_MASTER.report_region=2  )
      )
    GROUP BY
      DB_MASTER
    .REPORT_REGION_NAME,
      
    LINE_ORDER_WEEK.REL_MONTH,
      
    SHIP_WEEK.REL_MONTH

    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.16       0.16          0          0          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch       21   3522.79    4643.95   16744244   24142029          0         292
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total       23   3522.95    4644.12   16744244   24142029          0         292

    Misses in library cache during parse
    1
    Optimizer goal
    CHOOSE
    Parsing user id
    258  (DATAP)

    Rows     Execution Plan
    -------  ---------------------------------------------------
          
    0  SELECT STATEMENT   GOALCHOOSE
          0   SORT 
    (GROUP BY)
          
    0    NESTED LOOPS
          0     NESTED LOOPS
          0      NESTED LOOPS
          0       NESTED LOOPS
          0        NESTED LOOPS
          0         MERGE JOIN 
    (CARTESIAN)
          
    0          TABLE ACCESS   GOALANALYZED (FULLOF
                         
    'FISCAL_WEEK'
          
    0          BUFFER (SORT)
          
    0           TABLE ACCESS   GOALANALYZED (FULLOF
                          
    'DB_MASTER'
          
    0         PARTITION RANGE (ITERATORPARTITION:KEYKEY
          0          TABLE ACCESS   GOAL
    ANALYZED (FULLOF
                         
    'INVOICE_LINE' PARTITION:KEYKEY
          0        TABLE ACCESS   GOAL
    ANALYZED (BY INDEX ROWIDOF
                       
    'CUSTOMER_MASTER'
          
    0         INDEX   GOALANALYZED (UNIQUE SCANOF
                        
    'PK_CUSTOMER_MASTER' (UNIQUE)
          
    0       TABLE ACCESS   GOALANALYZED (BY INDEX ROWIDOF
                      
    'EXCHANGE_RATES_YEAR'
          
    0        INDEX   GOALANALYZED (UNIQUE SCANOF
                       
    'PK_EXCHANGE_RATE_YEAR' (UNIQUE)
          
    0      TABLE ACCESS   GOALANALYZED (BY INDEX ROWIDOF
                     
    'ITEM_MASTER'
          
    0       INDEX   GOALANALYZED (UNIQUE SCANOF 'PK_ITEM_MASTER'
                      
    (UNIQUE)
          
    0     TABLE ACCESS   GOALANALYZED (FULLOF 'FISCAL_WEEK'


    Elapsed times include waiting on following events:
      
    Event waited on                             Times   MaxWait  Total Waited
      
    ----------------------------------------   Waited  ----------  ------------
      
    SQL*Net message to client                      21        0.00          0.00
      db file scattered read                     745622        2.44       2878.88
      db file sequential read                     68192        0.62        160.72
      latch free                                    345        0.04          0.61
      wait 
    list latch free                            1        0.01          0.01
      buffer busy waits                            5122        0.08         12.93
      SQL
    *Net message from client                    20        0.00          0.04 

    thanks & Regards
    Pagnint
    (No need to search web before posting new question)

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    I suspect the problem is

    PARTITION RANGE (ITERATOR) PARTITION:KEYKEY
    0 TABLE ACCESS GOAL: ANALYZED (FULL) OF
    'INVOICE_LINE' PARTITION:KEYKEY

    as invoice_line is a big table even if it is partitioned.

    So do you have an index on any of the following for invoice_line:
    ship_date
    order_date
    line_type

    as they may help, you may need to do a composite index but best to try various combos of the above I suspect (maybe compress them if appropriate).

    Other things that might help would be to change the following:
    optimizer_index_caching integer 0
    optimizer_index_cost_adj integer 100
    to more realistic figures.

    If it still doesnt do as you want you may need some hints or a more detailed analyze i.e. for all indexed columns size<n>.

    Alan

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    isn't a group-by naturally distinct? how could it not be?

    anyways, you have a cartesian product there also.

    I would be surprised if your query MUST have all those tables joined in
    order to work.

    Perhaps you could break a few table-joins down into smaller groups of data
    and then join them together. AS an example you don't actually retreive any
    data from datap.ITEM_MASTER and datap.CUSTOMER_MASTER so perhaps
    there would be a different method of checking the data in these tables with
    EXISTS or IN clauses.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •