-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|