-
Hi,
This is for tuning gurus. I am putting the problem below.
I am processing some 2 million rows in a cursor and the cursor does few selects from other tables each time. These statements use indexes but due to poor clustering factor there are too many reads for data blocks. These tables are too big to be cached. Is there a way, I can reduce the physical reads and improve the performance. I can not incresae the clustering factor by pre-sorting the table.
The following is the output of tkprof.
SELECT MIN(ACCOUNT_NUM)
FROM
ODS.OES_ACCOUNT WHERE BILL_SITE = :b1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 14404 1.85 1.49 0 0 0 0
Fetch 14404 7.77 252.44 20021 51354 0 14404
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 28809 9.62 253.93 20021 51354 0 14404
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 33 (CDW) (recursive depth: 1)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 SORT (AGGREGATE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'OES_ACCOUNT'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'OES_ACCOUNTXFK1'
(NON-UNIQUE)
********************************************************************************
SELECT MAX(O.SIC_PART_ONE || O.SIC_PART_TWO ),MAX(O.SIC_PART_ONE ||
O.SIC_PART_TWO || O.SIC_PART_THREE || O.SIC_PART_FOUR )
FROM
ODS.OES_SITE_SIC O WHERE O.SITE_NUM = :b1 AND O.PRIMARY_SIC = 'Y' AND
O.ADDED_DATE = (SELECT MAX(ADDED_DATE) FROM ODS.OES_SITE_SIC WHERE
SITE_NUM = :b1 AND PRIMARY_SIC = 'Y' AND SOURCE_ARCHIVE_DT IS NULL ) AND
O.SOURCE_ARCHIVE_DT IS NULL
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 14404 2.10 1.61 0 0 0 0
Fetch 14404 10.86 343.71 24131 105221 0 14404
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 28809 12.96 345.32 24131 105221 0 14404
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 33 (CDW) (recursive depth: 1)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 SORT (AGGREGATE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'OES_SITE_SIC'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'OES_SITE_SICXPK'
(UNIQUE)
0 SORT (AGGREGATE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'OES_SITE_SIC'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'OES_SITE_SICXPK'
(UNIQUE)
-
Why don't you post the PL/SQL block and the number of rows in table, and names of indexes.
-
Tuning problem for gurus
Hi Tamilselvan,
The cursor is processing 2 million rows. The names of tables it is accessing & their indexes are given in TKPROF output included. These other tables also have about million rows each.
Please get back for any other detail.
Kailash Pareek
-
Create a composite index (PRIMARY_SIC+SITE_NUM) on OES_SITE table; and change the WHERE clause aaas follows:
SELECT MAX(O.SIC_PART_ONE || O.SIC_PART_TWO ),
MAX(O.SIC_PART_ONE ||
O.SIC_PART_TWO ||
O.SIC_PART_THREE ||
O.SIC_PART_FOUR )
FROM ODS.OES_SITE_SIC O
WHERE O.PRIMARY_SIC = 'Y'
AND O.SITE_NUM = :b1
AND O.ADDED_DATE = (SELECT MAX(ADDED_DATE)
FROM ODS.OES_SITE_SIC
WHERE PRIMARY_SIC = 'Y'
AND SITE_NUM = :b1
AND SOURCE_ARCHIVE_DT
IS NULL )
AND O.SOURCE_ARCHIVE_DT IS NULL
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
|