16、oracle資料庫維護

標籤：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 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%效能！！！！！！！

本文出自 “梁小明的部落格” 部落格，請務必保留此出處http://7038006.blog.51cto.com/7028006/1946646

16、oracle資料庫維護