Oracle11g Performance笔记2

'PERFORMANCE' is a relative thing; it can be measured but the numbers are only meaningful if you have previous numbers to compare them to. 

RE - performance issues with existing queries
1. What are the indications that there is a performance issue? Is it really slow or did a user just report that 'it seems slow'? Does an existing query take substantially longer to run now than it did before?

2. What changes might have occured in the database that might have degraded performance? db upgrade? batch load of data into one of the tables? one or more missing or disabled indexes?

3. Are the statistics out of date? Indexes missing or disabled? Number of records in one ore more tables changed dramatically?

4. Compare the current execution plan with the excecution plan when the query performed satisfactorily (which many shops never bother to create and save). Is the same plan and joining being performed? Are the same indexess being used?

RE - performance issues for new queries
1. Gather info about how many records are in each table being queried. In your case: how many records are there in ITEM_CHART and MATERIAL tables?

2. Gather info about many records from each table are expected to be accessed. In your case: how many records in the MATERIAL table will have the parameter value :B1? 1% of the records? 40% of the records? How many records would you expect to be in the result table? Five? Fifty million?

3. ALWAYS create an execution plan. This tellsl you what Oracle expects to do to get the result. Look for problem areas such as CARTESIAN joins, excessive SORTs, indexes not being used.

4. Test the query on very small data sets to make sure it is performing the way you expect.

5. Once it performs well create another execution plan and save it for use in the future to troubleshoot performance and other issues.

I rarely look or even care about the actual statistics for a query. Oracle will generally do the best job possible based on the information it has available to it. So I focus on making sure I have given Oracle the best info possible and haven't left anything out. 

One example:
Rows Execution Plan

0 SELECT STATEMENT MODE: ALL_ROWS
0 MERGE JOIN (CARTESIAN)
0 INDEX MODE: ANALYZED (SKIP SCAN) OF 'IDX_MATERIAL'
(INDEX)
0 BUFFER (SORT)
0 MAT_VIEW ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'ITEM_CHART' (MAT_VIEW)
0 INDEX MODE: ANALYZED (RANGE SCAN) OF
'IDX_ITEM_CHART' (INDEX)

When asking performance-related questions it is helpful if you provide the number of records in each table of the query and what indexes are available on them.

Assuming that you actually have a performance problem the likely reason is the CARTESIAN join that is being used.

Your execution plan shows 'MERGE JOIN (CARTESIAN)' which means that every row of ITEM_CHART is being joined to every row of MATERIAL.

This indicates that one or both of the join columns (ITEM_ID = B.MATL_NO) either do not have the proper index needed or it is not being used.

If one of the indexes is missing you need to add it.
If both indexes exist make sure they are enabled.
One main reason an index may not be used is if the statistics do not exist or are out of date.