Using automatic SQL tuning and SQL Profiles for fast ETL performance troubleshooting

In the previous post we have showed how we can use SQL Profiles and a script that we can download from Metalink in order to fix the execution plan of a query to a specific one that we have found from the available plans in AWR (or Library Cache) that has a better elapsed.

In this post, we will continue our discussion on SQL Profiles and show another way that we can exploit them in order to very fast troubleshoot SQL tuning issues, without the need to write code, deploy new code into production etc.


In the Telco I work, every night, a major workload from ETL flows is imposed on the database (a half-rack Exadata V2) in order to timely load the Enterprise Data Warehouse. A few days ago, there was a delay on  a specific flow. A session executing a PL/SQL procedure that tried to refresh a list of materialzed views was running for hours. The procedure was the following:

PROCEDURE "REFRESH_KPIDW_MVGLOB4_PROC" IS 
  temp_method     VARCHAR2(100);      
  temp_mviews     VARCHAR2(2000);      
BEGIN       
    temp_mviews := 'KPI_DW.WOMS_BB_ACT_DEMANDS_VDSL_MV, KPI_DW.WOMS_BB_DEACT_DEMANDS_VDSL_MV, KPI_DW.WOMS_CONNX_ACT_DEM_VDSL_MV, ' ||    
                   ... list of views continues
                   'KPI_DW.WOMSSIEBEL_BVOIP_MTR_MV, KPI_DW.GNV_RPRENEWALS_COUNT_MV, KPI_DW.P9_KATARG_EKSOT_TLF_RPLAN_MV';  
    temp_method := 'CCCCCCCCCCCCCCCCCCCCCCCC'; 
    DBMS_MVIEW.REFRESH(list => temp_mviews, method => temp_method, parallelism => 8, atomic_refresh=>false);         
END; 

From GV$SESSION we observed a session waiting for hours on the event 'gc cr request'. The SQL statement executed by this session was a specific INSERT INTO SELECT, which of course was invoked by the DBMS_MVIEW.REFRESH procedure.

From the Metalink note "Resolving Issues Where Inefficient SQL waits on 'gc cr request' and Causes Too Many Block Reads Across Nodes" (Doc ID 1475673.1) we see that:

"...Waits for event  'gc cr request' indicate that a session is waiting for consistent read buffers to be delivered from a cache on a remote instance in a Real Application Cluster (RAC) database. If these numbers are excessive and the SQL itelf is extremely resource intensive, it is possible that the query itself is in need of some tuning or manipulation in order to encourage it to choose a mosr acceptable path and consume less resources"

So we have to tune the query!

The SELECT clause in the Insert statement was quite complex and the execution plan had too many operations. It seemed that tuning would take some time. But we didn't have time! It was  12:00 pm and the morning reports had not been delivered to the top management. So lack of time was the real problem!

In such a situation automatic SQL tuning and SQL Profiles can come to the rescue. (see the previous post for a brief intro on SQL Profiles).

We captured the SQL_ID of the statement and then used the following script to create an automatic SQL tuning task for this SQL statement:

DECLARE
 my_task_name VARCHAR2(30);
BEGIN
 my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => trim('&sqlid'), task_name => '&&task_name');
 DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => '&task_name' );
END;

The script can be invoked like this:

nkarag@DWHPRD> @sqltune_exec

Enter value for sqlid: 1vhdjd2cccsph

Enter value for task_name: task1

This creates a recommendation, which you can read with the following script:

SET LONG 100000
SET LONGCHUNKSIZE 100000
SET LINESIZE 999

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( trim('&task_name'))
FROM   DUAL

/

Which can be invoked like this:

nkarag@DWHPRD> @sqltune_report

Enter value for task_name: task1

From the recommendation we got a proposal for accepting the creation of an SQL Profile and the relevant command in order to do it. Like the following:

Solution:
  Recommendation (estimated benefit: 99.99%)
  ------------------------------------------
  - Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name => 'task1', task_owner=>
'NKARAG', replace => TRUE)

You just run this command and thats it! No coding, no tuning (only automatic tuning!), no time spent.

The next time we invoked the above procedure it completed in a few seconds! Check the elapsed time (in secs)  from (AWR) history of executions for this statement before and after. Also check the use of SQL Profiles and the different plan hash value (i.e, the different execution plan used).

SNAP_ID SQL_ID          PLAN_HASH_VALUE  AVG_ETIME SQL_PROFILE
------- --------------- --------------- ---------- -----------------------------
  10398 1vhdjd2cccsph         598773955       1.79 SYS_SQLPROF_0440e2f83ff90000

  10355 1vhdjd2cccsph        2310652946   8,081.35

Of course automatic SQL tuning and SQL profiles is not a panacea. But sometimes it can be proved a fast solution for sql tuning when time is pressing.

Enjoy!
Oracle Learner.