16、oracle数据库维护

小风儿

　　16、数据库维护
　　1、管理优化程序统计信息
　　用于收集统计信息的首选项
　　DBA-> DBMS_STATS
　　SCOPE: statement level| table level| schema level| database level| global level
　　preferences: cascade | degree| estimate_percent|no_invalidate|metho_opt
　　granularity | incremental | publish| stale_percent
　　set| get| delete| export| import
　　exec dbms_stats.set_table_prefs('SH','SALES','STALE_PERCENT','13')
　　desc dbms_stats;
　　其中一个存储过程为：
　　PROCEDURE SET_TABLE_PREFS
　　Argument Name                  Type                    In/Out Default?
　　------------------------------ ----------------------- ------ --------
　　OWNNAME                        VARCHAR2                IN
　　TABNAME                        VARCHAR2                IN
　　PNAME                          VARCHAR2                IN
　　PVALUE                         VARCHAR2                IN
　　PROCEDURE GATHER_TABLE_STATS
　　Argument Name                  Type                    In/Out Default?
　　------------------------------ ----------------------- ------ --------
　　OWNNAME                        VARCHAR2                IN
　　TABNAME                        VARCHAR2                IN
　　PARTNAME                       VARCHAR2                IN     DEFAULT
　　ESTIMATE_PERCENT               NUMBER                  IN     DEFAULT
　　BLOCK_SAMPLE                   BOOLEAN                 IN     DEFAULT
　　METHOD_OPT                     VARCHAR2                IN     DEFAULT
　　DEGREE                         NUMBER                  IN     DEFAULT
　　GRANULARITY                    VARCHAR2                IN     DEFAULT
　　CASCADE                        BOOLEAN                 IN     DEFAULT
　　例子1：
　　(1)create table hr.t1 as select * from dba_objects;
　　因为这张表是刚刚创建的，因此看不了它的行数及block;
　　(2)select OWNER,TABLE_NAME,TABLESPACE_NAME,NUM_ROWS,BLOCKS from dba_tables where OWNER='HR' and TABLE_NAME='T1';
　　OWNER   TABLE_NAME TABLESPACE   NUM_ROWSBLOCKS
　　---------- ---------- ---------- ---------- ----------
　　HR          T1      USERS
　　(3)收集统计信息
　　exec dbms_stats.gather_table_stats('HR','T1');
　　SQL> exec dbms_stats.gather_table_stats('HR','T1');
　　PL/SQL procedure successfully completed.
　　(4)继续查询，有结果
　　SQL> select OWNER,TABLE_NAME,TABLESPACE_NAME,NUM_ROWS,BLOCKS from dba_tables where OWNER='HR' and T
　　ABLE_NAME='T1';
　　OWNER   TABLE_NAME TABLESPACE   NUM_ROWSBLOCKS
　　HR          T1      USERS ##########  1061
　　2、管理自动工作量资料档案库
　　自动工作量资料档案库（AWR）
　　内置性能信息资料档案库
　　每60分钟获取一次数据库快照，保留为8天
　　所有自我管理功能的基础
　　SGA---->MMON--->（60分钟）---》AWR
　　使用数据库自动诊断监视器
　　ADDM
　　在记录每个AWR快照之后运行
　　监事实例
　　在awr中存储结果。
　　DBMS_ADVISOR程序包
　　案例2：
　　oracle是如何给sql优化提出建议的呢？
　　(1)建表  create table scott.test_advisor (id varchar2(20),name varchar2(128));
　　(2)插入数据  insert into scott.test_advisor select object_id,object_name from dba_objects;
　　(3)建立索引  create index scott.idx_test_advisor_id on scott.test_advisor(id);
　　(4)获得统计信息
　　exec dbms_stats.gather_table_stats('SCOTT','test_advisor',cascade=>true);
　　(5)查看执行计划
　　set autotrace traceonly

　　select>　　结果为全表扫描,内容如下

　　SQL> select>　　Execution Plan
　　----------------------------------------------------------
　　Plan hash value: 3695065845

　　|>　　|
　　|   0 | SELECT STATEMENT  | |     1 |    30 |   103   (1)| 00:00:02
　　|
　　|*  1 |  TABLE ACCESS FULL| TEST_ADVISOR |     1 |    30 |   103   (1)| 00:00:02
　　|

　　Predicate Information (identified by operation>　　---------------------------------------------------
　　1 - filter(TO_NUMBER("ID")=1000)
　　Statistics
　　----------------------------------------------------------
　　1  recursive calls
　　0  db block gets
　　375  consistent gets
　　0  physical reads

　　0  redo>　　596  bytes sent via SQL*Net to client
　　524  bytes received via SQL*Net from client
　　2  SQL*Net roundtrips to/from client
　　0  sorts (memory)
　　0  sorts (disk)
　　1  rows processed
　　下面定义任务，看看oracle给sql有什么的好建议呢？
　　(6)定义任务
　　SQL> declare
　　2  l_task_id varchar2(20);
　　3  l_sql varchar2(2000);
　　4  begin

　　5  l_sql :='select>　　6  l_task_id :=dbms_sqltune.create_tuning_task(
　　7  sql_text =>l_sql,
　　8  user_name =>'SCOTT',
　　9  scope =>'COMPREHENSIVE',
　　10  time_limit =>30,
　　11  task_name =>'manual_advisor');
　　12  end;
　　13  /
　　PL/SQL procedure successfully completed.
　　(7)执行任务
　　SQL> begin
　　2  dbms_sqltune.execute_tuning_task('manual_advisor');
　　3  end;
　　4  /
　　PL/SQL procedure successfully completed.
　　(8)查看建议

　　set serveroutput on>　　set long 99999;
　　select dbms_sqltune.report_tuning_task('manual_advisor') from dual;
　　建议结果如下：
　　SQL> select dbms_sqltune.report_tuning_task('manual_advisor') from dual;
　　DBMS_SQLTUNE.REPORT_TUNING_TASK('MANUAL_ADVISOR')
　　--------------------------------------------------------------------------------
　　GENERAL INFORMATION SECTION
　　-------------------------------------------------------------------------------
　　Tuning Task Name   : manual_advisor
　　Tuning Task Owner  : SYS
　　Workload Type   : Single SQL Statement
　　Scope   : COMPREHENSIVE
　　Time Limit(seconds): 30
　　Completion Status  : COMPLETED
　　Started at   : 07/02/2017 21:21:56
　　Completed at   : 07/02/2017 21:21:59
　　DBMS_SQLTUNE.REPORT_TUNING_TASK('MANUAL_ADVISOR')
　　Schema Name: SCOTT

　　SQL>
　　SQL Text   : select>　　FINDINGS SECTION (2 findings)
　　1- Index Finding (see explain plans section below)
　　DBMS_SQLTUNE.REPORT_TUNING_TASK('MANUAL_ADVISOR')
　　The execution plan of this statement can be improved by creating one or more
　　indices.
　　Recommendation (estimated benefit: 98.04%)
　　------------------------------------------
　　- Consider running the Access Advisor to improve the physical schema design
　　or creating the recommended index.
　　create index SCOTT.IDX$$_023A0001 on SCOTT.TEST_ADVISOR(TO_NUMBER("ID"));
　　Rationale
　　---------
　　DBMS_SQLTUNE.REPORT_TUNING_TASK('MANUAL_ADVISOR')
　　--------------------------------------------------------------------------------
　　Creating the recommended indices significantly improves the execution plan
　　of this statement. However, it might be preferable to run "Access Advisor"
　　using a representative SQL workload as opposed to a single statement. This
　　will allow to get comprehensive index recommendations which takes into
　　account index maintenance overhead and additional space consumption.
　　2- Restructure SQL finding (see plan 1 in explain plans section)
　　----------------------------------------------------------------

　　The predicate TO_NUMBER("TEST_ADVISOR"."ID")=1000 used at line>　　execution plan contains an implicit data type conversion on indexed column
　　"ID". This implicit data type conversion prevents the optimizer from
　　从上面可以看到有2条建议，说可以提高性能的98.4%(Recommendation (estimated benefit: 98.04%))分别如下：
　　create index SCOTT.IDX$$_023A0001 on SCOTT.TEST_ADVISOR(TO_NUMBER("ID"));
　　TO_NUMBER("TEST_ADVISOR"."ID")=1000
　　下面来看看第二条建议怎么样？

　　select>　　结果还是全表扫描cost为103
　　在看看第一条建议
　　create index SCOTT.IDX$$_023A0001 on SCOTT.TEST_ADVISOR(TO_NUMBER("ID"));
　　在查询结果为走索引，代价为3，的确提高了98.4%性能！！！！！！！