Part 1 : Running SQL Tuning Advisor for a slow SQL in a Read Only Standby Database
Posted by FatDBA on January 15, 2022
Here goes my maiden post blog post from series about “Troubleshooting SQLs & other Issues on a Read Only Standby/Dataguard Environments” …
Recently I helped one of my customer stabilize performance of one of their critical DWH/BI decision support system workload running on an ADG (Physical Standby). At one time I have to generate SQL tuning advisory reports for few slow SQLs on the database, but that being a RO dataguard, it always ends with an error "ORA-13792: This operation requires a database link." when I try to call the SQL tuning advisor directly on the standby database.
So, this post is about how to run SQL Tuning advisor on a dataguard environment. Let’s assume we want to get SQLTA recommendation for this below SQL that is going for a FULL TABLE SCAN and is a costly SQL.
SQL> explain plan for select count(*) from bigtab where weight in ( select distinct( weight) from bigtab);
Explained.
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
SQL_ID 0mauvkjsvmcmj, child number 1
Plan hash value: 2140185107
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 74 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | TABLE ACCESS FULL| BIGTAB | 80223 | 1018K| 74 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("WEIGHT" IS NOT NULL)
This is what I have received on the standby database when tried to run the SQL Tuning advisor directly on it where it says that it requires a database link in order to run the advisory.
-- On standby database:
SQL>
SQL> @?/rdbms/admin/sqltrpt.sql
Specify the Sql id
~~~~~~~~~~~~~~~~~~
Enter value for sqlid: 0y521mggg73pk
Sql Id specified: 0y521mggg73pk
Tune the sql
~~~~~~~~~~~~
DECLARE
*
ERROR at line 1:
ORA-13792: This operation requires a database link.
ORA-06512: at line 36
ORA-06512: at "SYS.DBMS_SQLTUNE_UTIL1", line 1571
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_SQLTUNE_UTIL1", line 1563
ORA-06512: at "SYS.DBMS_SQLTUNE", line 798
ORA-06512: at line 20
ERROR:
ORA-13608: The specified name NULL is invalid.
ORA-06512: at "SYS.DBMS_SQLTUNE_UTIL1", line 1571
ORA-06512: at "SYS.PRVT_ADVISOR", line 7080
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.PRVT_ADVISOR", line 85
ORA-06512: at "SYS.PRVT_ADVISOR", line 5938
ORA-06512: at "SYS.PRVT_ADVISOR", line 7011
ORA-06512: at "SYS.DBMS_SQLTUNE_UTIL1", line 1535
ORA-06512: at "SYS.DBMS_SQLTUNE", line 1324
ORA-06512: at line 1
In remote tuning, the database on which you initiate a tuning task differs from the database in which the tuning process executes or in which results are stored. For example, a standby database can have its own workload of queries, some of which may require tuning. You can issue SQL Tuning Advisor statements on a standby database. A standby-to-primary database link enables DBMS_SQLTUNE to write data to and read data from the primary database. The link is necessary because the standby database, which is read-only, cannot write the SQL tuning data.
Okay, for that first you need to create the DB Link between primary and the standby database, and same will be used by the SQL tuning advisor where it will do the write operations on the primary database. We will check its connectivity from the standby database if its working or not …
-- On Primary database
SQL> select username,common,account_status from dba_users where username ='SYS$UMF';
USERNAME COM ACCOUNT_STATUS
--------------- --- --------------------------------
SYS$UMF YES OPEN
-- On Primary database:
SQL> create database link lnk_to_pri connect to "SYS$UMF" identified by "oracle90" using 'DXTPRI';
Database link created.
-- On Standby database:
SQL> select db_unique_name from v$database@lnk_to_pri;
DB_UNIQUE_NAME
------------------------------
DXTPRI
1 row selected.
SQL>
To tune a standby workload on a primary database, specify the database_link_to parameter in DBMS_SQLTUNE procedures. By default, the database_link_to parameter is null, which means that tuning is local. The database_link_to parameter must specify a private database link. This link must be owned by SYS and accessed by the default privileged user SYS$UMF. You issue all statements on the standby database. DBMS_SQLTUNE uses the database link both to fetch data from the primary database, and store data in the primary database.
Let’s create the TUNING TASK for the SQL Text.
-- run it on the STANDBY Database.
SQL>
SQL>
DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := 'select count(*) from bigtab where weight in ( select distinct( weight) from bigtab)';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => my_sqltext,
task_name => 'TEST_sql_tuning_task1',
database_link_to => 'lnk_to_pri');
END;
/
PL/SQL procedure successfully completed.
SQL>
-- Lets check if the tuning task is created
SQL> SELECT task_name, STATUS, EXECUTION_start, EXECUTION_end FROM dba_advisor_log;
TASK_NAME
--------------------------------------------------------------------------------
STATUS EXECUTION EXECUTION
----------- --------- ---------
TEST_sql_tuning_task1
EXECUTING 13-JAN-22
Next you need to execute the tuning task using the task_name you have assigned in the last step while creating the tuning task.
-- On Standby database
SQL> EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '&&your_sta_taks_name', database_link_to => 'lnk_to_pri');
Enter value for your_sta_taks_name: TEST_sql_tuning_task1
PL/SQL procedure successfully completed.
SQL>
Next, lets do the final step, generate the tuning task report with all recommendations for the SQL that is slow in the standby database.
-- Run it on the Standby database.
SQL>
SQL> SET LINES 150
SQL> SET pages 50000
SQL> SET long 5000000
SQL> SET longc 5000000
SQL> select dbms_sqltune.report_tuning_task('&&your_sta_taks_name', database_link_to => 'lnk_to_pri') from dual;
old 1: select dbms_sqltune.report_tuning_task('&&your_sta_taks_name', database_link_to => 'lnk_to_pri') from dual
new 1: select dbms_sqltune.report_tuning_task('TEST_sql_tuning_task1', database_link_to => 'lnk_to_pri') from dual
DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_SQL_TUNING_TASK1',DATABASE_LINK_TO=>'LNK_TO_PRI')
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
----------------------------------------------------
RECOMMENDATIONS
------------------------------------------------------------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : sql_tuning_task_5mxdwvuf9j3vp
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 01/13/2021 22:05:52
Completed at : 01/13/2021 22:06:25
-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID : pdxdwvuf9j3co
SQL Text : select count(*) from bigtab where weight in ( select distinct( weight) from bigtab)
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 99.94%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name =>
'sql_tuning_task_pdxdwvuf9j3co', task_owner => 'SYS', replace =>
TRUE);
Validation results
------------------
So, that’t it, you finally have the tuning recommendations for the slow SQL in the STANDBY database.
Hope It Helped!
Prashant Dixit
Tales From A Lazy Fat DBA 
Shaik Arsalan said
Good One
FatDBA said
thanks!
Sandesh said
Hello Prashant,
Can you please come more details on Creating DBlinks under which ownership which will be used in tuning task in standby.