A dba faces similar issues frequently, application team or end user complaints the system is slow or a module response is slow. Well there are many reasons known or unknown for a query performance sudden changes.
Few of the reasons that effects query performance:
— Stats
— Object level changes
— User level changes
— Row level changes
— Bind related issues
— Internal data dictionary changes.
What to review, in case of slow query performance:
— Sql query
— Xplan
— 10046 traces
— 10053 (if xplan chang occurs)
— The best tool is to use SQLT to collect the diag information.
Using events:
oradebug setmypid <oracle pid>;
oradebug event 10046 trace name context forever, level 12;
or
oradebug setospid <os pid>;
oradebug event 10046 trace name context forever, level 12;
In 11g, 10053 traces also includes column level stats information. Oracle support request these trace to review the CBO decisions.
Using events:
oradebug setmypid <oracle pid>;
oradebug event 10053 trace name context forever, level 1;
or
oradebug setospid <os pid>;
oradebug event 10053trace name context forever, level1;
Another tool that help troubleshoot query performance issues, and easier to use is SQLT. The SQL Tuning Health-Check Script is a tool developed by the Oracle Server Technologies Center of Expertise. The tool, also known as SQLHC, is used to check the environment in which a single SQL Statement runs, checking Cost-based Optimizer (CBO) statistics, schema object metadata, configuration parameters and other elements that may influence the performance of the one SQL being analyzed.
When used on one sql_id, the script generates html report.
Health-checks are performed over:
- CBO Statistics for schema objects accessed by the one SQL statement being analyzed
- CBO Parameters
- CBO System Statistics
- CBO Data Dictionary Statistics
- CBO Fixed-objects Statistics
Download sqlhc.zip, Doc ID 1366133.1, and unzip it.
Login as sqlplus ‘/as sysdba’
sqlplus / as sysdba
SQL> START sqlhc.sql T 39byxv6kc64h
Once you gather the diagnostic data, we analyze it and take appropriate action. Some common actions are:
if stats are changed, then restore stats:
execute DBMS_STATS.RESTORE_TABLE_STATS ('owner','table',date)
Bind peeking causing the problem, consider disabling bind peeking using _optim_peek_user_binds parameter.
**Always check with Oracle support before using undocumented parameters.