Snapper and this SQLd360, SQL diagnostics collection made faster
Great site on how to use the tuning advisor :
https://thehelpfuldba.com/index.php/2018/01/24/instructions-to-run-the-sql-tuning-advisor/
but as websites have a nasty way of disappearing :
Findings and Recommendations ---------------------------- Finding 1: Top SQL Statements Impact is .22 active sessions, 95% of total activity. ----------------------------------------------------- SQL statements consuming significant database time were found. These statements offer a good opportunity for performance improvement. Recommendation 1: SQL Tuning Estimated benefit is .22 active sessions, 95% of total activity. ---------------------------------------------------------------- Action Run SQL Tuning Advisor on the SELECT statement with SQL_ID "gbwan1836hj3c". Related Object SQL statement with SQL_ID gbwan1836hj3c. SELECT time_key + (:"SYS_B_00" / :"SYS_B_01") period, in_value, out_value FROM (SELECT time_key,
Create an SQL Tuning task as follows:
DECLARE l_sql_tune_task_id VARCHAR2(100); BEGIN l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task ( sql_id => 'gbwan1836hj3c', scope => DBMS_SQLTUNE.scope_comprehensive, time_limit => 500, task_name => 'gbwan1836hj3c_tuning_task', description => 'Tuning task1 for statement gbwan1836hj3c'); DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id); END; /
Run the tuning task
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'gbwan1836hj3c_tuning_task');
The above will take a couple of minutes. Depending on the complexity of the query, the time_limit may need to be increased.
After the task is complete, query the output:
set long 65536 set longchunksize 65536 set linesize 100select dbms_sqltune.report_tuning_task('gbwan1836hj3c_tuning_task11') from dual;
