Nothing better to do, try this:
SQL are running in the database, very hard to tell which ones could be benefited if all is hunky dory in the database.
DBA Passtime:
Grab sql_id that have run in the database, you could get from V$session or V$sql or any other view, my choice is the following in order to grab last hr or so run sql_id's :
SQL> SELECT a.sql_id
FROM gv$active_session_history a
WHERE a.sample_time > SYSDATE - 1/24
AND a.session_type = 'FOREGROUND'
GROUP BY a.sql_id
ORDER BY 1 DESC; 2 3 4 5 6
SQL_ID
-------------
g3f3cw3zy5aat
dcuks1btng04z
a8c7taz0kfnvg
8zc85a8249x81
8hbagg1z18dws
1aa2fpqtx557g
0raht0h154b63
create a script:
$vi autotune.sql <----Name your choice
set serverout on
set long 100000
set line 500
set pagesize 1000
DECLARE
v_sql_task_id VARCHAR2(100);
BEGIN
v_sql_task_id := DBMS_SQLTUNE.create_analyse_task (
---begin_snap => 1,
---end_snap => 2,
sql_id => 'SQLID',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 300,
task_name => 'SQLID_analyse_task',
description => 'analyse task for SQLID.');
END;
/
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'SQLID_analyse_task');
SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 400
SELECT DBMS_SQLTUNE.report_tuning_task('SQLID_analyse_task') AS recommendations FROM dual;
exit;
ENJOY!!!!!!
SQL are running in the database, very hard to tell which ones could be benefited if all is hunky dory in the database.
DBA Passtime:
Grab sql_id that have run in the database, you could get from V$session or V$sql or any other view, my choice is the following in order to grab last hr or so run sql_id's :
SQL> SELECT a.sql_id
FROM gv$active_session_history a
WHERE a.sample_time > SYSDATE - 1/24
AND a.session_type = 'FOREGROUND'
GROUP BY a.sql_id
ORDER BY 1 DESC; 2 3 4 5 6
SQL_ID
-------------
g3f3cw3zy5aat
dcuks1btng04z
a8c7taz0kfnvg
8zc85a8249x81
8hbagg1z18dws
1aa2fpqtx557g
0raht0h154b63
create a script:
$vi autotune.sql <----Name your choice
set serverout on
set long 100000
set line 500
set pagesize 1000
DECLARE
v_sql_task_id VARCHAR2(100);
BEGIN
v_sql_task_id := DBMS_SQLTUNE.create_analyse_task (
---begin_snap => 1,
---end_snap => 2,
sql_id => 'SQLID',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 300,
task_name => 'SQLID_analyse_task',
description => 'analyse task for SQLID.');
END;
/
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'SQLID_analyse_task');
SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 400
SELECT DBMS_SQLTUNE.report_tuning_task('SQLID_analyse_task') AS recommendations FROM dual;
exit;
Note: SQLID in above script is variable/placeholder for later substitution
you can run code for single sql_id as where bzv5d5qt53wjv is SQL_ID:
cat autotune.sql|sed "s/SQLID/bzv5d5qt53wjv/g"|sqlplus -s / as sysdba
or:
put sql_id's in a file say sql_id.txt
and
cat sql_id.txt |while read LN
do
cat autotune.sql|sed "s/SQLID/${LN}/g"|sqlplus -s / as sysdba
done
Enjoy reading output, will keep you busy for long time.
Recommendations could be :
1. No recommendations
2. Create profile
3. Gather stats
4. Create index
to name the few.
Caution: Not all recommendation can be accepted blindly. Please use judgement or seek help from seniors.
Followup action(suggested only): Make sure to run following to avoid junk buildup.
exec DBMS_SQLTUNE.drop_tuning_task (task_name => 'SQLID_analyse_task');
cat sql_id.txt |while read LN
do
echo "exec DBMS_SQLTUNE.drop_tuning_task (task_name => '${LN}_analyse_task');"|sed "s/SQLID/${LN}/g"|sqlplus -s / as sysdba
done
ENJOY!!!!!!
