ASH\AWR报告\直方图\执行计划管理\SQL profile

过去30分钟的ASH报告

declare
dbid number;
instance_id number;
begin
select dbid into dbid from v$database;
select instance_number into instance_id from v$instance;
dbms_output.enable(500000);
dbms_output.put_line('<PRE>');
for rc in ( select output from 
   table(dbms_workload_repository.ash_report_text( dbid,instance_id,SYSDATE-31/1440, SYSDATE-1/1440))) loop
   dbms_output.put_line(rc.output);
end loop;
dbms_output.put_line('</PRE>') ;
end;

上一次的AWR报告

declare
dbid number;
instance_id number;
start_id number;
end_id number;
begin
dbms_output.enable(1000000);
select max(snap_id)-1,max(snap_id) into start_id,end_id from dba_hist_snapshot;
select dbid into dbid from v$database;
select instance_number into instance_id from v$instance;

dbms_output.put_line('<PRE>');
for rc in ( select output from 
   table(dbms_workload_repository.awr_report_text(dbid,instance_id,start_id,end_id)) 
           ) loop
  -- dbms_output.put_line('<BR>');
   dbms_output.put_line(rc.output);
end loop;
dbms_output.put_line('</PRE>');
end;

生成ADDM报告

var tname varchar2(30);
begin
 :tname := 'ADDM_for_2PM_3PM';
 SYS.DBMS_ADDM.ANALYZE_DB(:tname,2180,2181);
 end;
 /
 
set serveroutput on; 
SET LONG 1000000 PAGESIZE 0;
SELECT DBMS_ADDM.GET_REPORT(:tname) FROM DUAL;

生成SQL profile

var tunning_task varchar2(100);
declare
   l_sql_id v$session.pre_sql_id@TYPE;
   l_tuning_task varchar2(30);
begin
  l_sql_id := '4sfkhkgshe';
  l_tuning_task := dbms_sqltune.create_tuning_task(sql_id => l_sql_id);
  :tunning_task := l_tuning_task;
  dbms_sqltune.execute_tuning_task(l_tuning_task);
  dbms_output.put_line(l_tuning_task);
 end;
/

print tuning_task;

查看报告

select dbms_sqltune.report_tuning_task(:tunning_task)comments from dual;

接受sql profile

exec dbms_sqltune.accept_sql_profile(task_name => 'xxxx', task_owner => 'xx', replace => TRUE);

生成列的直方图

BEGIN
  DBMS_STATS.GATHER_table_STATS ( 
    OWNNAME    => 'SCOTT', 
    TABNAME    => 'MYOBJECTS', 
    METHOD_OPT => 'FOR COLUMNS SIZE 10 owner' );
END;
/

查看直方图信息

SELECT COLUMN_NAME, NUM_DISTINCT, NUM_BUCKETS, HISTOGRAM 
FROM   USER_TAB_COL_STATISTICS
WHERE  TABLE_NAME = 'MYOBJECTS' AND COLUMN_NAME = 'OWNER';

SELECT ENDPOINT_NUMBER, ENDPOINT_VALUE 
FROM   USER_TAB_HISTOGRAMS
WHERE  TABLE_NAME = 'MYOBJECTS' AND COLUMN_NAME = 'OWNER'
ORDER BY ENDPOINT_NUMBER;

删除直方图信息

BEGIN
  DBMS_STATS.delete_column_STATS ( 
    OWNNAME    => 'SCOTT', 
    TABNAME    => 'MYOBJECTS', 
    colname => 'OWNER',
    col_stat_type => 'HISTOGRAM' 	);
END;
/

显示执行计划

alter session set statistics_level=all;
SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR('atfwcg8anrykp', NULL, 'ALLSTATS LAST');

装载执行计划到SPM

select child_number,plan_hash_value 
from v$sql
where sql_id='d5ukk73tqg6dj'; 

var loaded number
exec :loaded := DBMS_SPM.load_plans_from_cursor_cache('d5ukk73tqg6dj');

print :loaded

查看SQL基线的执行计划

select sql_handle, plan_name, accepted
from dba_sql_plan_baselines
where sql_handle ='SQL_d5ukk73tqg6dj';

SQL_HANDLE                    PLAN_NAME                                                ACCEPTED
------------------------------------------------------------------------------------------ ---------
SQL_8c13f56b062a2bb1          SQL_PLAN_8s4zpdc32naxj67ba4b29                         YES
SQL_8c13f56b062a2bb1          SQL_PLAN_8s4zpdc32naxje572e5d7                         YES

查看一个SQL是否有SQL基线

select sql_handle, plan_name, accepted
from dba_sql_plan_baselines
where sql_text like 'select /* bind_aware */%';

SELECT SQL_HANDLE, sql_text,ACCEPTED,FIXED,AUTOPURGE, ENABLED, created , plan_name, LAST_MODIFIED,REPRODUCED,ORIGIN 
FROM dba_sql_plan_baselines 
WHERE sql_text like 'select /* bind_aware */%';

SQL执行计划演进，将SQL基线内non-accepted执行计划改为accpeted执行计划

var evol_out clob
execute :evol_out := dbms_spm.evolve_sql_plan_baselines(sql_handle=>'SQL_4bsljlsfl',plan_name=>'SQL_PLAN_effneeg',verify=> 'NO');