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,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%性能！！！！！！！ （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |