-
Optmizer Problem and Hints
Dear All,
After the RE-ORG activity carried out on Sunday, the db was extremely slow.
We analysed all the objects yesterday and then it came back to normal.
Yet, certain queries are still slow. So, I checked the execution plan and found that those queries are using wrong index.
Query:
SELECT ROWID, warehouse_code, branch_code, client_code, job_srnum,
department, inventory_date, file_no, client_carton_no, description,
ref1, ref2, ref3, destruction_date, subject, pnw_carton_no,
cre_user_id, cre_dt, upd_user_id, branch, upd_dt, status, date1,
date2, num1, carton_size, addendum_date
FROM ISTORET.rmst_inventory_details2
WHERE branch_code = 'BOM'
AND job_srnum = '62100113476'
AND client_carton_no = 'BM4060394822'
AND subject = 'FILES'
ORDER BY file_no
Execution Plan:
Operation Object Name Rows Bytes Cost
SELECT STATEMENT Optimizer Mode=CHOOSE 1 15
SORT ORDER BY 1 155 15
TABLE ACCESS BY INDEX ROWID ISTORET.RMST_INVENTORY_DETAILS2 1 155 5
INDEX RANGE SCAN ISTORET.BRC_JOB_SUB_REF123_INDX 1 4
Query time: More than 5 mins
Same query with hint /*+ index(rmst_inventory_details2 BRC_JOB_CTN_FILE_INDX) */
SELECT STATEMENT Optimizer Mode=CHOOSE 1 6
TABLE ACCESS BY INDEX ROWID ISTORET.RMST_INVENTORY_DETAILS2 1 155 6
INDEX RANGE SCAN ISTORET.BRC_JOB_CTN_FILE_INDX 1 5
Query time: 110 msecs
Index details:
PARAMETER
INDEX- 1
INDEX- 2
Table Owner
ISTORET
ISTORET
Table Name
RMST_INVENTORY_DETAILS2
RMST_INVENTORY_DETAILS2
Index Name
BRC_JOB_CTN_FILE_INDX
BRC_JOB_SUB_REF123_INDX
Uniqueness
NONUNIQUE
NONUNIQUE
Columns
BRANCH_CODE
JOB_SRNUM
CLIENT_CARTON_NO
FILE_NO
BRANCH_CODE
JOB_SRNUM
SUBJECT
REF1
REF2
REF3
Table Type
TABLE
TABLE
Status
VALID
VALID
Tablespace
ISTORET_RID2_INDX
ISTORET_RID2_INDX
Initial Extent Size
4,194,304
4,194,304
Next Extent Size
4,194,304
4,194,304
Minimum Extents
1
1
Maximum Extents
2,147,483,645
2,147,483,645
Percent Increase
0
0
Distinct Keys
42,576,227
27,318,132
Percent Free
10
10
Index Type
NORMAL
NORMAL
Partitioned
No
No
Temporary
No
No
Join Index
No
No
Size in MB
2,456
2,140
Number Extents
614
535
Size in bytes
2,575,302,656
2,243,952,640
Last Analyzed
20/3/2007
20/3/2007
Request you to let me know why is it choosing the wrong index and what can I do to resolve this situation.
-
I'm assuming your query was chosing the "right" index before maintenance window, am I right?
If this is the case, compare the sampling of current stats against previous ones, I hope you have some documentation or at least the old gather-stats script. Be sure the sampling of your stats is large enough. You might want to take "compute" stats on indexes.
If nothing works, you have already solved it by adding a hint ;-)
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Dear PAVB,
Thank You for your reply.
I have done compute statistics while analyzing.
And it was choosing the right index prior to maintenance.
Can i delete the statistics and check ?
-
You may need to collect histograms when you analyze (DBMS_STATS I hope, not ANALYZE).
Assistance is Futile...
-
How are you invoking the analysis?
-
Can you post 10053 trace file?
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
|