Oracle-DB: Retrieving extended statistics in execution plan for a specific SQL only

For performance analysis of problematic SQL statements it is crucial to have the extended statistics of the execution plan available.
These extended statistics per plan line include as total and last gathered values:
  • The number of starts of execution of this plan line
  • The number of rows gotten by execution of this plan line
  • The number of DB buffers scanned in consistent or current mode
  • The number of disk reads and writes
  • The elapsed time during execution of this plan line
There are several ways to force the DB to record these extended statistics:
  • Set the global initialization variable 'STATISTICS_LEVEL to 'ALL' to enable them for the whole DB
  • 'ALTER SESSION SET STATISTICS_LEVEL=ALL;' to enable them for the current session
  • The optimizer hint /*+ GATHER_PLAN_STATISTICS */ in the SQL to enable the extented only for this SQL statement

Unfortunately, to activate the extended statistics for a specific SQL by adding the optimizer hint,
you need to change the SQL syntax in the application.
This may not always be possible, and even if it is, it will require the app to be redeployed, which will cause additional effort and delay.

An appropriate way to force a specific SQL statement to immediately produce extented statistics at the next execution is:
Create a SQL patch which injects the hint GATHER_PLAN_STATISTICS into the SQL without changing it.
Simply execute such PL/SQL snippet to create a SQL patch for a given SQL ID: 
DECLARE
  patch_name VARCHAR2(32767);
BEGIN
  patch_name := sys.DBMS_SQLDiag.create_SQL_patch(
    sql_id      => '2rf0uw3hgtwc1',
    hint_text   => 'GATHER_PLAN_STATISTICS',
    name        => 'GATHER_PLAN_STATISTICS for 2rf0uw3hgtwc1',
    description => 'Show detailed plan statistics'
  );
END;
/
After next execution of the SQL the columns in V$SQL_Plan_Statistics are collected and can be shown e.g. by DBMS_XPLAN.DISPLAY.
Using the analysis tool Panorama, the values from V$SQL_Plan_Statistics are shown as additional columns in the execution plan, calculated per SQL execution and per single start of the plan column.
After investigation of the execution plan you can drop the SQL patch by using the name of the patch: 
EXEC DBMS_SQLDiag.Drop_SQL_Patch('GATHER_PLAN_STATISTICS for 2rf0uw3hgtwc1');
The SQL statement remains in its previous state, without the additional overhead associated with collecting extended statistics.

Creating SQL patches is part of the Standard or Enterprise Edition. You don't need an additional option pack license.
To create SQL patches you need the ADMINISTER SQL MANAGEMENT OBJECT or similar grant.
Location: 01 Dresden, Deutschland

Comments

Post a Comment

Popular posts from this blog

Oracle-DB: New SQL Diagnostic Report in rel. 19.28

Image
One of the new features backported from 23ai into release 19.28 is the SQL Diagnostic Report. This shows a comprehensive summary of diagnostic information for a certain SQL statement. Included are execution plan info, optimizer statistics, object info, ASH info, SQL Monitor reports and lots more. The documentation for 19c already includes the new method DBMS_SQLDIAG.Report_SQL . Which edition or option pack license is needed Including ASH, SQL Monitor etc. raises the question: What about licensing limitations for use of this function. As of current documentation there is no Enterprise Addition or additional management pack license needed for execution of DBMS_SQLDIAG.Report_SQL. The package DBMS_SQLDIAG and it's subroutines are often named as "SQL Repair Advisor". Looking at Features and Licensing there is no limitation for usage of SQL Repair Advisor and there's also a section for SQL Diagnostic Report confirming no limitation: The note "How To U...
Read more

Oracle-DB: Cleanup Unified Audit Trail with dynamic number of rows and oldest timestamp, but limited storage size

For housekeeping of Unified Audit Trail in a recent project there were some objectives: A hard rule that audit records must be kept in all cases for at least x days A hard rule that the stored audit records should not exceed a limited storage size, except the the above age limit requires this A soft objective thet audit records should be stored as long as possible if the storage size limit is not reached The package DBMS_AUDIT_MGMT only provides the method CLEAN_AUDIT_TRAIL with the ability to cut the records to purge at a timestamp limit. This would require to estimate a maximum age of held audit records that ensures also in worst cases that the storage size limit will not be exceeded. In result, outside the worst case of audit volume you will purge the audit records far too early. You could have stored them much longer because there's enough space below the storage limit. The following SQL script accepts all named objectives by ensuring: Audit records younger ...
Read more

Oracle-DB: Estimate network latency of client connections by evaluation of Active Session History

Image
Sometimes it is useful to know about the network latency of client connections to database. Normally this would be done with the established network tools tnsping, ping, traceroute, tcptraceroute etc. from client or DB server side. But what if there is only SQL access to the DB server and no access to the client? This post shows how the network latency can be estimated using the recordings from ASH if the application uses a certain access pattern. The network latency between client and database server can be estimated by the number of number of SQL executions of a single session between two ASH snapshots. If an application executes the same very short running SQL against the DB over and over again in a loop, then: it is a bad architecture approach for the application because the network latency will cause a significant overhead for application performance however, this behavior gives a possibility for a weak estimation of the network latency between client and database ser...
Read more