frequently used query
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: frequently used query

  1. #1
    Join Date
    Feb 2002
    Posts
    9

    Unhappy

    HI,
    this query takes less time, but it's getting executed 20000 times a day. could any one suggest me?

    We have a table called RCR that contains 4.5 million records. One particular query on this table takes an average of 3 seconds to execute even though the table is indexed. The query is:

    SELECT NVL(SUM(NVL(RCR_ROUNDUP_DURATION,0)/60),0),
    NVL(SUM(DECODE(RCR_RATE_TYPE,'P',NVL(RCR_ROUNDUP_DURATION,0)/60,0)),0),
    NVL(SUM(DECODE(RCR_RATE_TYPE,'N',NVL(RCR_ROUNDUP_DURATION,0)/60,0)),0),
    NVL(SUM(DECODE(NVL(RCR_CHG_ROUNDUP_DURATION,-1),-1,0,NVL(RCR_ROUNDUP_DURATION,0)-NVL(RCR_CHG_ROUNDUP_DURATION,0))/60),0)
    INTO S_SUM_NWC_CALL_MIN,
    S_SUM_NWC_PEAK_MIN,
    S_SUM_NWC_NPEAK_MIN,
    W_RCR_9TO9_FREE_MIN
    FROM RCR_20020617
    WHERE RCR_CUST_CODE = '1001038356'
    AND RCR_ACCOUNT_CODE = '001'
    AND RCR_MOBILE_NO = '62876013'
    AND NVL(RCR_POSTED_IND,'N') <> 'Y'
    AND NVL(RCR_ROAMING_IND,'L') <> 'R'
    AND TRUNC(RCR_START_TIMESTAMP) <= TO_DATE('20020616','YYYYMMDD')
    AND NVL(RCR_FREE_AIRTIME_IND, '1') <> '3'
    AND RCR_SEQ_NO IS NULL
    AND RCR_CALL_RATING_IND = 'NW';


    There are three indexes on this table:
    1. (RCR_INVOICE_NO, RCR_MOBILE_NO)
    2. (RCR_CUST_CODE, RCR_ACCOUNT_NO, RCR_MOBILE_NO) - all three fields are VARCHAR2(x)
    3. RCR_SEQ_NO.

    Even if we include only the first three conditions of the WHERE clause (till RCR_MOBILE_NO), then also it is taking the same time.

    This table and its indexes are created every month (actually, different tables are created each time, but they have the same description; so for simplicity, you can consider it as the same table). After table creation, some process inserts 4.5 million records into this table over the period of a month. After the month is over, another process reads and updates this table. The above query is one of these reads.

    One option we tried was dropping and recreating the index before reading this table. There was some minor reduction in the execution time of the above query.

    What other options do we have for drastically tuning this query? We have already checked the row chaining (nil) and fragmentation. We checked disk I/O as well but did not have any major recommendation for this table. The disk array is RAID 1.

    Or is this performance normal for these conditions?

    The server is a 6 CPU (each CPU 700 MHz), 12 GB RAM Compaq Alpha GS140 box. The OS is Alpha OpenVMS 7.2-1. The Oracle server version is 7.3.3.6. The database size is around 220 GB.

    Any suggestions will be appreciated.


    Regards,

    Prashant & Bhargav
    NWM project, Hong Kong

    --------------------------------------------------------------------

    Some more info:

    RCR TABLE ATTRIBUTES
    --------------------
    PCTFREE 10
    PCTUSED 80
    INITRANS 3
    MAXTRANS 255

    INITIAL 209715200
    NEXT 20971520
    MINEXTENTS 1
    MAXEXTENTS 600
    FREELISTS 3 FREELIST GROUPS 1
    NOCACHE

    INDEX 2 ATTRIBUTES
    --------------------
    PCTFREE 10

    INITIAL 26214400
    NEXT 10485760
    PCTINCREASE 0

    thanx
    malay

  2. #2
    Join Date
    Mar 2002
    Posts
    534
    Hi,

    Could you please show the explain plan of this query (in SQL*Plus you see it by typing "SET AUTOTRACE ON").

    How many results will be returned?

    How many rows have in the average the same RCR_CUST_CODE, RCR_ACCOUNT_NO, RCR_MOBILE_NO values?

    Have you tried it with an index organized table?

    Is it correct that when you execute this type of query, the table does not any more get inserts/updates?


Posting Permissions

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


Click Here to Expand Forum to Full Width