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_ROWS BLOCKS

---------- ---------- ---------- ---------- ----------

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_ROWS BLOCKS

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 id,name from scott.test_advisor where id=1000;

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

SQL> select id,name from scott.test_advisor where id=1000;


Execution Plan

----------------------------------------------------------

Plan hash value: 3695065845



| Id  | Operation  | Name | Rows  | Bytes | Cost (%CPU)| Time

 |


|   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 id):

---------------------------------------------------


   1 - filter(TO_NUMBER("ID")=1000)



Statistics

----------------------------------------------------------

 1  recursive calls

 0  db block gets

375  consistent gets

 0  physical reads

 0  redo size

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 id,name from scott.test_advisor where id=1000';

  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 size 99999;

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 ID   : 7gns85v297ncy

SQL Text   : select id,name from scott.test_advisor where id=1000


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 ID 1 of the

  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 id,name from scott.test_advisor where TO_NUMBER("TEST_ADVISOR"."ID")=1000;

结果还是全表扫描cost为103


在看看第一条建议

create index SCOTT.IDX$$_023A0001 on SCOTT.TEST_ADVISOR(TO_NUMBER("ID"));

在查询结果为走索引,代价为3,的确提高了98.4%性能!!!!!!!

相关文章

相关标签/搜索