SQL Access Advisor
In the previous post, we've talked about how to run a SQL tuning for a specific SQL_ID, which can generate some useful findings for us. The report in which some findings guide us to run SQL Access Advisor for deeper information.
So in this post, we'd like to see how to run a SQL Access Advisor task for a SQL statement by using SYS.DBMS_ADVISOR package.
Normally, we should create a workload (SQL Tuning Set, STS) as an input for DBMS_ADVISOR to create an advisor task, but in this case, there's only one SQL statement should be tuned, so we use DBMS_ADVISOR.QUICK_TUNE to perform the task.
There're 4 steps to get the report.
Prepare the Tuning
First of all, let's see the environment.
SQL> show user
USER is "SYS"
SQL> show con_name
CON_NAME
------------------------------
ORCLPDB
We need a directory object to put the script file. The step is optional, you may also use an existing directory object.
SQL> create directory advisor_file_dir as '/u01/app/oracle/advisor';
Directory created.
Basically, DBMS_ADVISOR.QUICK_TUNE accepts only full SQL statement, not SQL_ID. However, if the poorly performed SQL statement is still in the shared SQL area, we can find its content from V$SQL by SQL_ID, or vice versa.
SQL> select distinct sql_id from v$sql where sql_text = 'select employee_id from hr.employees where salary between 5000 and 10000';
SQL_ID
-------------
6t04amnphzcwd
For a lengthy SQL statement, using SQL_ID may be more precise to perform the tuning.
DBMS_ADVISOR.QUICK_TUNE
Next, we perform the quick tuning.
v_sql_text clob;
begin
select sql_text into v_sql_text from v$sql where sql_id = '6t04amnphzcwd' and rownum = 1;
DBMS_ADVISOR.QUICK_TUNE ( DBMS_ADVISOR.SQLACCESS_ADVISOR, 'SQLACCESS_6t04amnphzcwd', v_sql_text );
end;
/
As you can see, we use SQL_ID to retrieve its literal statement into v_sql_text in the PL/SQL block. Of course, you can directly use its full SQL statement as an input.
The execution is shown as below:
SQL> declare
2 v_sql_text clob;
3 begin
4 select sql_text into v_sql_text from v$sql where sql_id = '6t04amnphzcwd' and rownum = 1;
5 DBMS_ADVISOR.QUICK_TUNE ( DBMS_ADVISOR.SQLACCESS_ADVISOR, 'SQLACCESS_6t04amnphzcwd', v_sql_text );
6 end;
7 /
PL/SQL procedure successfully completed.
DBMS_ADVISOR.GET_TASK_SCRIPT
To get the executable script, we output the result to a file in a directory object that we created previously.
Next, we create the script file for the task.
SQL> exec DBMS_ADVISOR.CREATE_FILE ( DBMS_ADVISOR.GET_TASK_SCRIPT ( 'SQLACCESS_6t04amnphzcwd' ), 'ADVISOR_FILE_DIR', 'SQLACCESS_6t04amnphzcwd.sql' );
PL/SQL procedure successfully completed.
Let's see the content.
[oracle@test advisor]$ cat SQLACCESS_6t04amnphzcwd.sql
Rem SQL Access Advisor: Version 19.0.0.0.0 - Production
Rem
Rem Username: SYS
Rem Task: SQLACCESS_6t04amnphzcwd
Rem Execution date:
Rem
CREATE INDEX "HR"."EMPLOYEES_IDX$$_003E0000"
ON "HR"."EMPLOYEES"
("SALARY","EMPLOYEE_ID")
COMPUTE STATISTICS;
OK, the SQL Access Advisor told us to CREATE INDEX and the index seems reasonable.
DBA_ADVISOR_ACTIONS
In fact, the advices are generated in the dictionary DBA_ADVISOR_ACTIONS, we should check the result in tools like SQL Developer:
select * from dba_advisor_actions where task_name = 'SQLACCESS_6t04amnphzcwd';
Note, DBMS_ADVISOR.GET_TASK_REPORT may not work well in such case.