Diagnostics for Query Tuning Problems

Performance Service Request Diagnostic Collection (SRDC) documents

Service Request Data Collection (SRDC) documents have been specially designed to provide the reader with the necessary instructions to provide a step by step guide to collecting information for a various common Database Performance Issues.

Document 1938786.1 List of Available Database Performance Related SRDC Documents

QUESTIONS AND ANSWERS

Information required to diagnose a Query Performance Problem

N.B. In order for the Cost Based Optimizer to be able to infer an appropriate execution plan for a query, it is necessary for it to have accurate statistics at its' disposal. Gathering accurate and up to date statistics can solve a significant number of query tuning issues. Please see:

Document 388474.1 * Recommendations for Gathering Optimizer Statistics on 9i
Document 605439.1 * Recommendations for Gathering Optimizer Statistics on 10g
Document 749227.1 * Recommendations for Gathering Optimizer Statistics on 11g
Document 44961.1 Statistics Gathering: Frequency and Strategy Guidelines

for Analysis recommendations.

You can check whether the environment that your SQL is running in is suitable and get some useful advice by using the SQL Tuning Health-Check Script (SQLHC). See:

Document 1366133.1 SQL Tuning Health-Check Script (SQLHC)

Summary of required Information:

BASIC REQUIREMENTS

ADDITIONAL REQUIREMENTS

The following diagnostics are in order of importance, often issues can be solved with item 1 and 2 (Query Text and Explain plan) alone.
More complex issues may need all of the diagnostics listed (and possibly more).

Details of how to collect this information are documented below:

BASIC REQUIREMENT

Query Text - The full text of the problematic query
Gather Explain PlanPlease use the following article to generate the plans:
Document 235530.1 * Recommended Method for Obtaining a Formatted Explain Plan

Please ensure that the plan_table in the schema you are using is the current plan table (as opposed to an earlier version).
You can (re)create the plan table using the $ORACLE_HOME/rdbms/admin/utlxplan.sql script.
Trace of query executionGather trace for the session running the SQL using :
Document 376442.1 * Recommended Method for Obtaining 10046 trace for Tuning
There is no need to provide TKProf output from the trace as this can be generated by support. See:
Document 29012.1 TKPROF Usage - Quick Reference, for more details on TKProf.
Which OPTIMIZER_GOAL / OPTIMIZER_MODE is being used?On 10g and above the RBO is desupported so it is likely that the CBO will be used. See:
Document 189702.1 Rule Based Optimizer is to be Desupported in Oracle10g
Check the init
.ora file for the instance or
```
SELECT name,value 
 FROM v$parameter 
 WHERE name = 'optimizer_mode'; 
```

ADDITIONAL REQUIREMENTS

Table/View definitions, Analyze level and parallelism information It is likely that support will require some information regarding the setup of the objects involved in the query. Details of Tables, Views, Partitioned tables and indexes, Index Organised Tables, function based indexes, context Support (domain) indexes etc etc may be required. Only simple Tables and Views are covered in this document.
A describe of the table is usually sufficient:
```
desc TABLE_NAM 
```
Have the tables been analyzed? What is the level of analysis?Do any tables have a degree of parallelism?
```
SELECT table_name, num_rows, degree
FROM dba_tables
WHERE owner = '...'  
AND table_name = '...';  
```
Extracting View definitions (if applicable)
```
set long 2000
SELECT text FROM user_views WHERE view_name = '...';
```
Index details (column names and the order they appear in indexes)
```
SELECT c.index_name, i.uniqueness, c.column_name, c.column_position
FROM user_ind_columns c, user_indexes i
WHERE i.table_name = '...'
AND i.index_name = c.index_name
ORDER BY c.index_name, c.column_position;
```

Initialisation Parameters The following select will extract the majority of parameters relevent to query optimization, the alert log is also a good source of non default parameter settings:

SELECT name, value
FROM v$parameter 
WHERE name like 'optimizer%' 
OR name like 'parallel%' 
OR name in ('cursor_sharing', 
'db_file_multiblock_read_count',
'hash_area_size', 
'hash_join_enabled',
'query_rewrite_enabled',
'query_rewrite_integrity',
 'sort_area_size', 
'star_transformation_enabled',
'bitmap_merge_area_size', 
'partition_view_enabled') 
ORDER BY name;

Row Count and Timing Information If possible, supply the number of rows in each table and also expected proportion of rows returned from each table after the predicates have been applied. Collecting session statistics (especially timings with Parameter:TIMED_STATISTICS=TRUE) can be very useful, especially in conjunction with autotrace or tkprof..
Relevent Application Details Often some application specific information is useful. This can be anything at all but usually the most useful is: "The query should drive off Table XX and then join to Table YY"
Testcases for Query Performance issuesThe following information is required :
- The testcase must have been tested by the customer.
- It is imperative that the testcase reproduces the problem on the site that reported the issue.
- Testcase must be MINIMAL
- MINIMAL tables
- MINIMAL indexes
- MINIMAL columns
- MINIMAL rows (see below)
- Instructions for reproducing the problem (Preferably a script should be supplied).
- Output that demonstrates the problem and the correct results if possible
- Explain plans and Trace files if relevant
- The initialisation parameter settings may be required - at least be aware that they could be important
Providing too much information, for example an export of the whole database and the select, is not advisable and is likely to significantly increase the time taken to analyse the issue. If the dataset is large (and the problem is not data related) then it may be more realistic to use the statistics from the system as opposed to the actual data. An export of the objects with no rows and the statistics should be sufficient to reproduce a number of issues.

For details of how to collect a suitable testcase see:

Document 390610.1 How to Submit a Testcase to Oracle Support for Reproducing an Execution Plan
Additional Suggestions Sometimes, if a query can be divided into its constituent parts and then run separately, the explain plans and timings produced can give the analyst ideas for improving the main select. However, it should be noted that not all operations may be possible in the main select due to merging restrictions.

REFERENCES

NOTE:390610.1 - How to Submit a Testcase to Oracle Support for Reproducing an Execution Plan
NOTE:215187.1 - SQLT Diagnostic Tool
NOTE:1470811.1 - How to Use SQLT (SQLTXPLAIN) to Create a Testcase Without Row Data
NOTE:1465741.1 - How to Use SQLT (SQLTXPLAIN) to Create a Testcase Containing Application Data
NOTE:605439.1 - * How to Gather Optimizer Statistics on 10g
NOTE:727863.1 - How to Create a SQL-testcase Using the DBMS_SQLDIAG Package [Video]
NOTE:1366133.1 - SQL Tuning Health-Check Script (SQLHC)
NOTE:749227.1 - * How to Gather Optimizer Statistics on 11g
NOTE:189702.1 - Rule Based Optimizer Desupported in Oracle10g
NOTE:242489.1 - Transferring Optimizer Statistics to Support
NOTE:29012.1 - QREF: TKPROF Usage - Quick Reference
NOTE:376442.1 - * How To Collect 10046 Trace (SQL_TRACE) Diagnostics for Performance Issues
NOTE:388474.1 - * How to Gather Optimizer Statistics on 9i
NOTE:44961.1 - Statistics Gathering: Frequency and Strategy Guidelines
===================================================================

Note---> This informationmation taken from oracle metalink. all copy rights oracle only.

Oracle DBA Information at one place

Search This Blog