SQL Statement Tuning

1.SQL Reports in Statspack

  

   SQL order by gets          -------sql语句读取buffer cache的次数

   SQL order by reads         ------sql语句从磁盘读到buffer cache的次数

   SQL order by executions   -----sql语句执行的次数

   SQL order by parse calls    ------ sql语句解析的次数

 

2.explain plan

  explain plan for select last_name from employees;

执行 explain plan需要plan_table这张表，产生这张表需要执行utlxplan.sql

 

SQL> explain plan for select last_name from employees;

已解释。

 

查看解释结果

  1.通过utlxpls.sql语句获得执行计划

   SQL> @E:/oracle/product/10.2.0/db_1/RDBMS/ADMIN/utlxpls.sql;

 

  2.通过dbms_xplan包获得执行计划

SQL> select * from table(dbms_xplan.display);

 

3.sql_trace和tkprof

 1.设定

  alter session set sql_trace = true;

 2.执行sql语句

 3.关闭sql_trace

  alter session set sql_trace = false;

 4.查看产生的trace文件

   SQL> show parameter user_dump_dest

 5.执行tkprof

  转到目录E:/oracle/product/10.2.0/db_1/BIN

E:/oracle/product/10.2.0/db_1/BIN>tkprof xxx.trc myoutput.txt

 

4.sqlplus autotrace

 设置:

 1.cd [ORACLE_HOME]/rdbms/admin

 2.log into sql*plus as system

 3.Run @utlxplan

 4.Run create public synoym plan_table for plan_table;

 5.Run grant all on plan_table to public;

 6.Run @[ORACLE_HOME]/sqlplus/admin/plustrace.sql

 7.grant plustrace to public;

使用:

 SQL> set autot on;

  SQL> select last_name from hr.employees where employee_id = 120;

LAST_NAME
-------------------------
Weiss

执行计划
----------------------------------------------------------
Plan hash value: 1833546154

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

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

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

)| Time     |

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

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

|   0 | SELECT STATEMENT            |               |     1 |    12 |     1   (0

)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |     1 |    12 |     1   (0

)| 00:00:01 |

|*  2 |   INDEX UNIQUE SCAN         | EMP_EMP_ID_PK |     1 |       |     0   (0

)| 00:00:01 |

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

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

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPLOYEE_ID"=120)

统计信息
----------------------------------------------------------
         53  recursive calls
          0  db block gets
          8  consistent gets
          1  physical reads
          0  redo size
        412  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

 

 

 5.收集信息

 dbms_stats.gather_table_stats  ------------dba_tables

  dbms_stats.gather_instance_stats

dbms_stats.gather_columns_stats ----user_tab_col_statistics

 dbms_stats.gather_schema_stats ----针对某用户收集信息

 

6.segment-level statistics

 logical reads

 buffer busy waits

 db block changes

 physical reads

 physical writes

 physical reads direct

 physical writes direct

 global cache cr blocks served

 global cache current blocks served

 ITL waits

 row lock waits

 

相关视图

 v$segstat_name

 v$segstat

 v$segment_statistics 

 

 7.Histograms

 创建直方图

execute dbms_stats.gather_table_stats('HR','EMPLOYEES',method_opt=>'for columns size 10--桶数 salary -- 列名');

 相关视图

  dba_histograms

  dba_tab_histograms

 

 8.监控某张表

 alter table hr.t monitoring;   --视图*_tab_modifications

 

 9.收集系统信息

 dbms_stats.gather_system_stats

 dbms_stats.set_system_stats

 dbms_stats.get_system_stats

 

execute dbms_stats.gather_system_stats(interval => 120---分钟,stattab => 'mystats'---结果要保存到的表名,statid =>'OLTP'----标签为OLTP);

导入到另个系统

 execute dbms_stats.import_system_stats(stattab=>'mystats',statid=>'OLTP';