Deprecated: Array and string offset access syntax with curly braces is deprecated in /home/u158179386/domains/dba-scripts.com/public_html/wp-content/plugins/easy-table/inc/Encoding.php on line 156
Deprecated: Array and string offset access syntax with curly braces is deprecated in /home/u158179386/domains/dba-scripts.com/public_html/wp-content/plugins/easy-table/inc/Encoding.php on line 158
Deprecated: Array and string offset access syntax with curly braces is deprecated in /home/u158179386/domains/dba-scripts.com/public_html/wp-content/plugins/easy-table/inc/Encoding.php on line 159
Deprecated: Array and string offset access syntax with curly braces is deprecated in /home/u158179386/domains/dba-scripts.com/public_html/wp-content/plugins/easy-table/inc/Encoding.php on line 160
- Renaming a RAC cluster - 27/09/2018
- Stop/Start all RAC databases at once - 26/09/2018
- RMAN Backup script - 08/11/2017
What is SQL Tuning Health Check?
The SQL Tuning Health Check is provided by Oracle (Doc ID 1366133.1) in order to check the environment where the problematic SQL query runs.
It checks the statistics, the metadata, initialization parameters and other elements that may influence the performance of the SQL being analyzed.
The script generates an HTML report with information on data collected by the script. The script has no footprint and can be run on any system.
You must connect as SYS or with a user with the DBA role.
The script takes 2 parameters, the first one is about your licensing (Tuning or Diagnostics or None T|D|N), the second is the SQL_ID that needs to be analyzed. Not that if you want to use both the Diagnostic and Tuninbg pack you have to use T as the first parameter.
Let’s take an example
I will use the script with a sql_id consumming the most resources in my database by using this script on the v$active_session_history view.
I’ve got my SQL_ID, now I can run the script to generate the report. Here I will use the script with the licensing parameter set to none (N) to see what kind of information we can get.
SQL>@sqlhc N agux3azmf389w
The script runs for a few minutes and generates a ZIP file with the following content:
The report
Health Check
The first HTML document called “Health Check” contains observations about instance parameters and statistics with some interesting feedbacks on possible bugs.
Here is the table I get:
| Type | Name | Observation | Details |
|---|---|---|---|
| CBO PARAMETER | DB_FILE_MULTIBLOCK_READ_COUNT | MBRC Parameter is set to "16" overriding its default value. | The default value of this parameter is a value that corresponds to the maximum I/O size that can be performed efficiently.This value is platform-dependent and is 1MB for most platforms. Because the parameter is expressed in blocks it will be set to a value that is equal to the maximum I/O size that can be performed efficiently divided by the standard block size. |
| DBMS_STATS | DBA_SCHEDULER_JOBS | Automatic gathering of CBO statistics is enabled. | Be aware that small sample sizes could produce poor quality histograms which combined with bind sensitive predicates could render suboptimal plans. See 465787.1. |
| DBMS_STATS | SYSTEM STATISTICS | Workload CBO System Statistics are not gathered. CBO is using default values. | Consider gathering workload system statistics using DBMS_STATS.GATHER_SYSTEM_STATS.See also 465787.1. |
| PLAN | PLAN_HASH_VALUE | Plan 2760052716 has operations with Cost 0 and Card 1. Possible incorrect Selectivity. | Review Execution Plans.Look for Plan operations in 2760052716 where Cost is 0 and Estimated Cardinality is 1. Suspect predicates out of range or incorrect statistics. |
| TABLE | XXXXX | Table contains 1 column(s) with no popular values on a "HEIGHT BALANCED" histogram. | A Height-balanced histogram with no popular values is not helpful nor desired. Consider dropping this histogram by collecting new CBO statistics while using METHOD_OPT with SIZE 1. |
| TABLE | XXXXX | Table contains 1 long CHAR column(s) with Histogram. Number of distinct values (NDV) could be incorrect. | Possible Bug 9885553. When building histogram for a varchar column that is long we only use its first 32 characters. Two distinct values that share the same first 32 characters are deemed the same in the histogram. Therefore the NDV derived from the histogram is inaccurate. If NDV is wrong then drop the Histogram. |
| TABLE | XXXXX | Table contains 1 column(s) where the number of distinct values does not match the number of buckets. | Review column statistics for this table and look for "Num Distinct" and "Num Buckets". If there are values missing from the frequency histogram you may have Bug 10174050. If you are referencing in your predicates one of the missing values the CBO can over estimate table cardinality and this may produce a sub-optimal plan. You can either gather statistics with 100% or as a workaround: ALTER system/session "_fix_control"='5483301:OFF'; |
| TABLE | XXXXX | Sample size of 446547 rows may be too small for table with 2565136 rows. | Sample percent used was:17.41%. Consider gathering better quality table statistics with DBMS_STATS.AUTO_SAMPLE_SIZE on 11g or with a sample size of 30% on 10g. |
There is also information about the tables and indexes involved in the query, as said in the report, these tables are meant to compare the values between two different environments.
Diagnostics
This document is almost empty for me because I used the script without the Tuning/Diagnostic Pack license, so the report only shows results for queries against views which are not part of these packs.
It gets data from AWR snapshots (DBA_HIST_SQLSTAT) and from v$active_session_history/DBA_HIST_ACTIVE_SESS_HISTORY views for wait events and wait class.
Execution Plans
This section is interesting because it contains the current SQL Plan with outline data and predicates but also historical SQL Plans from the AWR Snapshots.
Of course I don’t have the historical data because I chose to run the script without the packs.
SQL Detail
Basically, this section display the result of DBMS_SQLTUNE.REPORT_SQL_DETAIL for the SQL_ID provided.
In my case there is nothing in that file because I ran the script without the tuning pack.
Conclusion
This script gives useful information to resolve a performance problem, even if I used it without the Diagnostic and Tuning packs, The health check part gives valuable information that could be used to diagnose the problem.
The other parts of the report are not very useful without the help of the Diagnostic and Tuning packs because you miss the important information to diagnose the problem.
The Diagnostic part is only useful if you can compare historical data (DBA_HIST) with new data (v$active_session_history).
The execution plan report is a comparison between the old plan (if any) and the new plan, for this you need the diagnostic pack.
Finaly the Sql detail part generates an active report which displays information about your sql statement in a graphical way. You need the tuning pack for this.
This is a good and helpful script, but if you want to get the best of the Sql Tuning Health Check, you definitely need to have the Diagnostic and Tuning packs.