Quick Oracle Database Tunning Tips without OEM(Enterprice Manager)


http://docs.oracle.com/cd/B28359_01/server.111/b28274/sql_tune.htm
http://www.pythian.com/news/584/wouldnt-automatic-sql-plan-management-be-the-11g-killer-app/

create a tunning task
---------------------
variable stmt_task VARCHAR2(64);
variable sts_task  VARCHAR2(64);
variable spa_tune_task VARCHAR2(64);
EXEC :stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK( sql_text => 'SELECT S.EMPLID ,S.EMPL_RCD , PDE.EFFDT ,INS.PER_ORG ,INS.ORIG_HIRE_DT ,PDE.SEX ,PRS.BIRTHDATE ,PRS.BIRTHPLACE ,PRS.BIRTHCOUNTRY ,PRS.BIRTHSTATE ,PRS.DT_OF_DEATH ,PDE.HIGHEST_EDUC_LVL ,PDE.FT_STUDENT ,PDE.LANG_CD ,PDE.ALTER_EMPLID ,PDE.MAR_STATUS ,ASG.HOME_HOST_CLASS ,ASG.CMPNY_SENIORITY_DT ,ASG.SERVICE_DT ,ASG.PROF_EXPERIENCE_DT ,ASG.LAST_VERIFICATN_DT ,ASG.OWN_5PERCENT_CO ,ASG.PROBATION_DT ,ASG.ORG_INSTANCE_ERN ,ASG.SENIORITY_PAY_DT FROM PS_PERSON PRS ,PS_PER_ORG_ASGN ASG ,PS_PER_ORG_INST INS ,PS_PERS_DATA_EFFDT PDE ,PS_GP_PYE_STAT_WRK S WHERE S.RUN_CNTL_ID ='ABS_JAN2012_IND' AND S.OPRID = 12345 AND S.EMPLID BETWEEN 1000 AND 19999 AND S.EMPLID = PRS.EMPLID AND S.EMPLID = PDE.EMPLID AND S.FRST_JOB_SEG_IND = 'Y' AND S.EMPLID = ASG.EMPLID AND S.EMPL_RCD = ASG.EMPL_RCD AND ASG.EMPLID = INS.EMPLID AND ASG.ORG_INSTANCE_ERN = INS.ORG_INSTANCE_ERN ORDER BY S.EMPLID, S.EMPL_RCD, PDE.EFFDT DESC ');
---or-----
EXEC :stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(task_name=>'hello1223',sql_id => '4qh14ksgd4dd8',scope='COMPREHENSIVE');
---or-----
DECLARE
sql_text clob;
sqlprofile_name varchar2(30);
task_name varchar2(100);
BEGIN
sql_text :=
task_name:=dbms_sqltune.create_tuning_task(sql_text,task_name=>'TASKREQ01',scope=>'COMPREHENSIVE');
END;
/
execute a tunning task
----------------------
EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(:stmt_task);
display a tunning task
----------------------
SET LONG 1000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.report_tuning_task('TASK_4188') AS recommendations FROM dual
SELECT DBMS_SQLTUNE.report_tuning_task(:stmt_task) AS recommendations FROM dual
accept a recommendation sql profile
execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_4256',task_owner => 'MD86506', replace => TRUE);
disable sql profile
EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE('SYS_SQLPROF_013c237dab800000','STATUS','DISABLED');

view task information
--------------------
SELECT task_name, status FROM dba_advisor_log WHERE owner = 'MD86506';

primary_gl_tb 

SELECT DBMS_SQLTUNE.report_tuning_task('primary_gl_tb') AS recommendations FROM dual ;
execute dbms_sqltune.accept_sql_profile(task_name => 'primary_gl_tb',task_owner => 'MD86506', replace => TRUE);

USER_ADVISOR_TASKS <-- table which have task information
•DBA_ADVISOR_TASKS
•DBA_ADVISOR_FINDINGS
•DBA_ADVISOR_RECOMMENDATIONS
•DBA_ADVISOR_RATIONALE
•DBA_SQLTUNE_STATISTICS
•DBA_SQLTUNE_BINDS
•DBA_SQLTUNE_PLANS
•DBA_SQLSET
•DBA_SQLSET_BINDS
•DBA_SQLSET_STATEMENTS
•DBA_SQLSET_REFERENCES
•DBA_SQL_PROFILES
•V$SQL
•V$SQLAREA
•V$ACTIVE_SESSION_HISTORY
enable/dislabe/delete sql profile
=================================

SELECT *
  FROM DBA_SQL_PROFILES prof,
       DBMSHSXP_SQL_PROFILE_ATTR attr
  WHERE prof.NAME=attr.PROFILE_NAME
  ORDER BY prof.name,
           attr.attr_number;
Enable/Disable
Locate the name of the SQL Profile you would like to disable and plug it in to the following statement:
EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE('PROFILE_NAME','STATUS','DISABLED');
Dropping a SQL Profile
EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('PROFILE_NAME');