Database: Oracle 9iR2
OS: HP UX 11i
Currently we are using multiple buffer pools 8K and 16K.
The 8K is of 5 GB
and 16K is of 4 GB
The table CDR_DATA and associated INDEXES are assgined to 16K block size tablespace. From last few months our client is complaining that the billing process is running slow. I did a detail analysis using SQL_TRACE using 10046 with SYSTEM_STATISTICS=ALL
I found that the below SQL is leading to huge no. of "db block sequential reads"
SELECT CU.CDR_DATA_PARTITION_KEY, TYPE_ID_USG, ELEMENT_ID, PROVIDER_ID,
POINT_TARGET, POINT_ORIGIN, POINT_ID_TARGET, POINT_ID_ORIGIN, CU.TRANS_DT,
CU.MSG_ID, CU.MSG_ID2, CU.MSG_ID_SERV, RATED_UNITS, JURISDICTION, AMOUNT,
CCARD_ID, CCARD_ID_SERV, CUSTOMER_TAG, RATE_PERIOD, POINT_TAX_CODE_ORIGIN,
POINT_TAX_CODE_TYPE_ORIGIN, POINT_TAX_CODE_TARGET,
POINT_TAX_CODE_TYPE_TARGET, BASE_AMT, UNROUNDED_AMOUNT, BILL_CLASS,
ZONE_CLASS, EXT_TRACKING_ID, ANNOTATION, REV_RCV_COST_CTR, AGGR_USAGE_ID,
RATE_CURRENCY_CODE, PRIMARY_UNITS, SECOND_UNITS, THIRD_UNITS,
CORRIDOR_PLAN_ID, CU.SPLIT_ROW_NUM, OPEN_ITEM_ID, NVL(NUM_RECORDS,1),
AMOUNT_REDUCTION, AMOUNT_REDUCTION_ID, ACCESS_REGION_ORIGIN,
ACCESS_REGION_TARGET, CU.SUBSCR_NO, CU.SUBSCR_NO_RESETS, TAX_PKG_COUNT,
GEOCODE, AUX_TAX_INFO, TAX_RATE_ACTIVE_DT, TAX_RATE_INACTIVE_DT,
ADD_IMPLIED_DECIMAL, RATE_CLASS, POINT_CLASS_ORIGIN, POINT_CLASS_TARGET,
COMPONENT_ID, EQUIP_CLASS_CODE
FROM
CDR_UNBILLED CU, CDR_DATA CD, FILE_STATUS
WHERE CU.ACCOUNT_NO = :B10 AND
CU.TRANS_DT < :B9 AND
CU.CDR_DATA_PARTITION_KEY = CD.CDR_DATA_PARTITION_KEY AND
CU.MSG_ID = CD.MSG_ID AND
CU.MSG_ID2 = CD.MSG_ID2 AND
CU.MSG_ID_SERV = CD.MSG_ID_SERV AND
CU.SPLIT_ROW_NUM = CD.SPLIT_ROW_NUM AND
CDR_STATUS IN (:B8, :B7, :B6, :B5) AND
NO_BILL = 0 AND
( :B4 = 0 OR
( :B4 = 1 AND
CU.RATE_DT < :B3)
) AND
CD.FILE_ID = FILE_STATUS.FILE_ID AND
CD.FILE_ID_SERV = FILE_STATUS.FILE_ID_SERV AND
(FILE_STATUS.FILE_STATUS = :B2 OR
FILE_STATUS.FILE_STATUS = :B1)
ORDER BY CU.ACCOUNT_NO,CU.TRANS_DT
Rows Row Source Operation
------- ---------------------------------------------------
6497 SORT ORDER BY (cr=58534 r=8771 w=0 time=150209662 us)
6497 NESTED LOOPS (cr=58534 r=8771 w=0 time=149973079 us)
6497 NESTED LOOPS (cr=32546 r=8771 w=0 time=149525666 us)
6497 TABLE ACCESS CLUSTER CDR_UNBILLED (cr=6558 r=58 w=0 time=783420 us)
1 INDEX UNIQUE SCAN CDR_UNBILLED_XC_CCNT_N_TRNS_JN (cr=3 r=0 w=0 time=35 us)(object id 434351)
6497 PARTITION RANGE ITERATOR PARTITION: KEY KEY (cr=25988 r=8713 w=0 time=148683930 us)
6497 TABLE ACCESS BY LOCAL INDEX ROWID CDR_DATA PARTITION: KEY KEY (cr=25988 r=8713 w=0 time=148630331 us)
6497 INDEX UNIQUE SCAN CDR_DATA_PK PARTITION: KEY KEY (cr=19491 r=2562 w=0 time=37627876 us)(object id 75426)
6497 PARTITION LIST ITERATOR PARTITION: KEY KEY (cr=25988 r=0 w=0 time=333196 us)
6497 TABLE ACCESS BY LOCAL INDEX ROWID FILE_STATUS PARTITION: KEY KEY (cr=25988 r=0 w=0 time=295733 us)
6497 INDEX UNIQUE SCAN FILE_STATUS_PK PARTITION: KEY KEY (cr=19491 r=0 w=0 time=203930 us)(object id 257683)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
6497 SORT (ORDER BY)
6497 NESTED LOOPS
6497 NESTED LOOPS
6497 TABLE ACCESS GOAL: ANALYZED (CLUSTER) OF 'CDR_UNBILLED'
1 INDEX (UNIQUE SCAN) OF 'CDR_UNBILLED_XC_CCNT_N_TRNS_JN'
(NON-UNIQUE)
6497 PARTITION RANGE (ITERATOR) PARTITION:KEYKEY
6497 TABLE ACCESS GOAL: ANALYZED (BY LOCAL INDEX ROWID) OF
'CDR_DATA' PARTITION:KEYKEY
6497 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'CDR_DATA_PK'
(UNIQUE) PARTITION:KEYKEY
6497 PARTITION LIST (ITERATOR) PARTITION:KEYKEY
6497 TABLE ACCESS GOAL: ANALYZED (BY LOCAL INDEX ROWID) OF
'FILE_STATUS' PARTITION:KEYKEY
6497 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'FILE_STATUS_PK'
(UNIQUE) PARTITION:KEYKEY
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 73928 0.41 1179.61
latch free 94 0.03 0.36
SQL*Net message to client 62 0.00 0.00
SQL*Net more data to client 1075 0.23 1.03
SQL*Net message from client 62 2.68 21.47
buffer busy waits 2 0.02 0.02
I am suggesting to move the CDR_DATA and associated index to 8K block size (default) and assign them KEEP BUFFER POOL
Please advise.
Thanks for your time !!!