Optmizer Problem and Hints

[pareshjavkar]

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.

[PAVB]

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 ;-)

[pareshjavkar]

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 ?

[waitecj]

You may need to collect histograms when you analyze (DBMS_STATS I hope, not ANALYZE).

[jhmartin]

How are you invoking the analysis?

[tamilselvan]

Can you post 10053 trace file?