手动收集统计信息 15:09:09 SQL> select count(*) from HR.EMP; COUNT(*) ---------- 221 15:09:19 SQL> select num_rows from dba_tables where table_name='EMP'; NUM_ROWS ---------- 14 12 0 15:09:39 SQL> select num_rows from dba_tables where table_name='EMP' and owner='HR'; NUM_ROWS ---------- 15:10:03 SQL> exec dbms_stats.gather_table_stats('HR','EMP'); 已順利完成 PL/SQL 程序. 15:10:35 SQL> select num_rows from dba_tables where table_name='EMP' and owner='HR'; NUM_ROWS ---------- 221 15:10:38 SQL>
begin DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(interval => 60,retention =>15*24*60); end;
自定义调用AWR和ADDM诊断报表 AWR报告获取24小时内的 --conn / as sysdba; set echo off; set veri off; set feedback off; set termout on; set heading off; variable rpt_options number; define NO_OPTIONS = 0; define ENABLE_ADDM = 8; -- according to your needs, the value can be 'text' or 'html' define report_type='html'; begin :rpt_options := &NO_OPTIONS; end; / variable dbid number; variable inst_num number; variable bid number; variable eid number; variable dbname varchar2(9); variable btime number; variable etime number; begin select dbid into :dbid from v$database; select instance_number into :inst_num from v$instance; select max(snap_id)-24 into :bid from dba_hist_snapshot where DBID=:dbid; select max(snap_id) into :eid from dba_hist_snapshot where DBID=:dbid; select lower(NAME) into :dbname from v$database; select to_char(end_interval_time,'YYYYMMDDHH24') into :btime from dba_hist_snapshot where snap_id=:bid; select to_char(end_interval_time,'DDHH24') into :etime from dba_hist_snapshot where snap_id=:eid; end; / column ext new_value ext noprint column fn_name new_value fn_name noprint; column lnsz new_value lnsz noprint; --select 'txt' ext from dual where lower('&report_type') = 'text'; select 'html' ext from dual where lower('&report_type') = 'html'; --select 'awr_report_text' fn_name from dual where lower('&report_type') = 'text'; select 'awr_report_html' fn_name from dual where lower('&report_type') = 'html'; --select '80' lnsz from dual where lower('&report_type') = 'text'; select '1500' lnsz from dual where lower('&report_type') = 'html'; select :dbname||'_'||:btime||'to'||:etime||'.'||'&ext' report_name from dual; set linesize &lnsz; -- print the AWR results into the report_name file using the spool command: column report_name new_value report_name noprint; select :dbname||'_'||:btime||'to'||:etime||'.'||'&ext' report_name from dual; set termout off; spool &report_name; select output from table(dbms_workload_repository.&fn_name(:dbid, :inst_num,:bid, :eid,:rpt_options )); set termout on; clear columns sql; ttitle off; btitle off; repfooter off; undefine report_name undefine report_type undefine fn_name undefine lnsz undefine NO_OPTIONS exit ADDM自动诊断报告 set echo off; set veri off; set feedback off; set termout on; set heading off; set pagesize 0; set heading off echo off feedback off verify off; variable task_name varchar2(40); variable dbid number; variable inst_num number; variable bid number; variable eid number; variable dbname varchar2(9); variable btime number; variable etime number; begin select dbid into :dbid from v$database; select instance_number into :inst_num from v$instance; select max(snap_id)-48 into :bid from dba_hist_snapshot where DBID=:dbid; select max(snap_id) into :eid from dba_hist_snapshot where DBID=:dbid; select lower(NAME) into :dbname from v$database; select to_char(end_interval_time,'YYYYMMDDHH24') into :btime from dba_hist_snapshot where snap_id=:bid; select to_char(end_interval_time,'DDHH24') into :etime from dba_hist_snapshot where snap_id=:eid; end; / --prompt --prompt --prompt Running the ADDM analysis on the specified pair of snapshots ... --prompt begin declare id number; name varchar2(100); descr varchar2(500); BEGIN name := ''; descr := 'ADDM run: snapshots [' || :bid || ', ' || :eid || '], instance ' || :inst_num || ', database id ' || :dbid; dbms_advisor.create_task('ADDM',id,name,descr,null); :task_name := name; dbms_advisor.set_task_parameter(name, 'START_SNAPSHOT', :bid); dbms_advisor.set_task_parameter(name, 'END_SNAPSHOT', :eid); dbms_advisor.set_task_parameter(name, 'INSTANCE', :inst_num); dbms_advisor.set_task_parameter(name, 'DB_ID', :dbid); dbms_advisor.execute_task(name); end; end; / --prompt --prompt Generating the ADDM report for this analysis ... --prompt --prompt column report_name new_value report_name noprint; select :dbname||'_'||:btime||'to'||:etime||'.'||'txt' report_name from dual; set termout off; spool &report_name; set long 1000000 pagesize 0 longchunksize 1000 column get_clob format a80 select dbms_advisor.get_task_report(:task_name, 'TEXT', 'TYPICAL') from sys.dual; spool off; set termout on; prompt &report_name undefine dbid undefine inst_num undefine report_name exit
自动优化统计信息收集依赖于该参数‘STATISTICS_LEVEL’值TYPICAL 或者ALL,9i中还有BASIC值。视图v$statistics_level alter system set statistics_level=xxxx; -basic:oracle关闭了所有性能数据的收集,如关闭AWR或statspack收集就将STATISTICS_LEVEL设为BASIC。 typical:除了plan_executetion_statistics和OS statistics不能收集外,其他均可以收集。 all:收集所有statistics BEGIN DBMS_AUTO_TASK_ADMIN.ENABLE( client_name => 'auto optimizer stats collection', operation => NULL, window_name => NULL); END; / client_name:自动维护任务的名称,可查看dba_autotask_client视图 operation:指定作业名称 dba_autotask_operation视图可查看,其实同dba_autotask_client相同。 windows_name:作业窗口选择,其中client_name将被禁止(MONDAY_WINDOW,TUESDAY_WINDOW,WEDNESDAY_WINDOW,THURSDAY_WINDOW,FRIDAY_WINDOW,SATURDAY_WINDOW,SUNDAY_WINDOW)。 。如果operation和window_name为NULL,client_name将启用/禁止。 。如果operation not NULL,windows_name被忽略,operation指定的'client_name'启用/禁止。 。如果operation NULl,windows_name not NULL那么client_name指定的窗口将启用/禁止。 管理自动维护的窗口或JOB Oracle 10g中以dba_scheduler_jobs.job_name形式 Oracle 11g有所改变,视图dba_autotask_window_clients可以查看一周七天执行情况。 DBA_AUTOTASK_WINDOW_CLIENTS WINDOW_NAME ->维护窗口的名称,周一至周七 WINDOW_NEXT_TIME->下一次维护窗口计划的时间,除非此维护窗口被禁用 WINDOW_ACTIVE ->指定维护窗口是否活动 TRUE/FALSE AUTOTASK_STATUS ->系统自动维护任务状态 ENABLED/DISABLED OPTIMIZER_STATS ->优化器统计信息收集状态 ENABLED/DISABLED SEGMENT_ADVISOR ->段顾问状态 ENABLED/DISABLED SQL_TUNE_ADVISOR->SQL优化顾问状态 ENABLED/DISABLED HEALTH_MONITOR ->健康监控状态 ENABLED/DISABLED 如WINDOW_ACTIVE为FALSE表示自动维护相关窗口均失效。 begin dbms_scheduler.enable( name=>'MONDAY_WINDOW'); end; / execute DBMS_AUTO_TASK_ADMIN.ENABLE; execute DBMS_AUTO_TASK_ADMIN.DISABLE; dba_autotask_task dba_autotask_client dba_autotask_client_job dba_autotask_window_clients dba_autotask_client_history dba_scheduler_jobs dba_scheduler_job_classes dba_scheduler_window_groups dba_scheduler_windows dba_scheduler_wingroup_members dba_autotask_job_history Statistics on Table,Index adn Columns DBA_TABLES DBA_OBJECT_TABLES DBA_TAB_STATISTICS DBA_TAB_COL_STATISTICS DBA_TAB_HISTOGRAMS DBA_TAB_COLS DBA_COL_GROUP_COLUMNS DBA_INDEXES DBA_IND_STATISTICS DBA_CLUSTERS DBA_TAB_PARTITIONS DBA_TAB_SUBPARTITIONS DBA_IND_PARTITIONS DBA_IND_SUBPARTITIONS DBA_PART_COL_STATISTICS DBA_PART_HISTOGRAMS DBA_SUBPART_COL_STATISTICS DBA_SUBPART_HISTOGRAMS dba_autotask_client -->dba_scheduler_window_groups -->dba_scheduler_windows -->dba_scheduler_jobs -->dba_autotask_client -->dba_scheduler_job_classes
--关闭sql tuning advisor,避免消耗过多的资源 BEGIN DBMS_AUTO_TASK_ADMIN.disable( client_name => 'sql tuning advisor', operation => NULL, window_name => NULL); END; / --关闭auto space advisor,避免消耗过多的IO,还有避免出现这个任务引起的library cache lock BEGIN DBMS_AUTO_TASK_ADMIN.disable( client_name => 'auto space advisor', operation => NULL, window_name => NULL); END; / --光闭自动统计信息收集,(慎用,除非有其他手工收集统计信息的完整方案,否则不建议关闭) BEGIN DBMS_AUTO_TASK_ADMIN.disable( client_name => 'auto optimizer stats collection', operation => NULL, window_name => NULL); END; / --启动sql tuning advisor BEGIN DBMS_AUTO_TASK_ADMIN.enable( client_name => 'sql tuning advisor', operation => NULL, window_name => NULL); END; / --启动auto space advisor BEGIN DBMS_AUTO_TASK_ADMIN.enable( client_name => 'auto space advisor', operation => NULL, window_name => NULL); END; / --启动自动统计信息收集 BEGIN DBMS_AUTO_TASK_ADMIN.enable( client_name => 'auto optimizer stats collection', operation => NULL, window_name => NULL); END; / --关闭星期天的自动段顾问Job BEGIN dbms_auto_task_admin.disable( client_name => 'auto space advisor', peration => NULL, window_name => 'SUNDAY_WINDOW'); END; /
如对本文有疑问, 点击进行留言回复!!
Oracle查询用户,表结构,表索引,存储过程,触发器等信息整理
oracle的自连接、内连接、外连接、交叉连接、自然连接、等值连接、不等值连接
Oracle数据库使用replace函数批量删除数据中的双引号
网友评论