Oracle : Evaluate Oracle Databases SQL's

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;

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!!!!!!