Oracle优化笔记2

常用的跟优化有关的数据字典视图      

v$sql,

v$sql_plan,

v$sqlarea

 

explain plan，autotrace，tkprof，执行计划和静态统计信息的解读  

u       explain plan

       SQL> delete from plan_table;
SQL> explain plan for YOUR_SQL;
SQL> select * from table(dbms_xplan.display);

 

u       autotrace

SQL> set autotrace

Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]

常用的是：

set autotrace traceonly

 

u       tkprof

SQL>

 

u       runstats

SQL>

 

u       statspack

SQL>

 

u       dbms_profiler

SQL>

 

·查询正在执行的语句，查看其执行计划——         

SELECT id || chr(9) || PARENT_ID || chr(9) || operation || chr(9) ||
       options , object_name , cost , TIME
  FROM v$sql_plan
 where hash_value in
       (select sql_hash_value from v$session where status = 'ACTIVE')
 order by id    

 

·识别’低效执行’的SQL语句——         

识别’低效执行’的SQL语句.用下列SQL工具找出低效SQL:
 SELECT EXECUTIONS , DISK_READS, BUFFER_GETS,
        ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio,
        ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,
        SQL_TEXT
FROM   V$SQLAREA
WHERE  EXECUTIONS>0
AND     BUFFER_GETS > 0
AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8
ORDER BY EXECUTIONS DESC    

   

 ·查询某个SQL语句的执行情况——         

SELECT  EXECUTIONS ,

        floor(ELAPSED_TIME / EXECUTIONS) TIME_PER_EXEC,

        DISK_READS, BUFFER_GETS,

        ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio,

        ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,

        SQL_TEXT,

        PARSING_SCHEMA_NAME

FROM   V$SQLAREA

WHERE  EXECUTIONS>1

--AND     BUFFER_GETS > 0

AND upper(sql_text) like '%OS_TASK_RESULT%'  -- 假设SQL语句跟OS_TASK_RESULT有关

order by TIME_PER_EXEC desc

 

--使用以下语句可以清空共享池，即清空统计数据

alter system flush shared_pool;

 

 

 ·对表信息进行统计——        

        exec dbms_stats.gather_table_stats(ownname=>'AIDMCU_WUZS',tabname=>'ACCOUNT',estimate_percent=>'50',method_opt =>'for all columns',cascade=>true,degree=>'1');

    或者是：

        

ANALYZE Statement

Update CBO (Cost Based Optimiser) statistics.

Syntax:

   ANALYZE TABLE tablename COMPUTE|ESTIMATE|DELETE STATISTICS ptnOption options

   ANALYZE INDEX indexname COMPUTE|ESTIMATE|DELETE STATISTICS ptnOption options
   ANALYZE CLUSTER clustername COMPUTE|ESTIMATE|DELETE STATISTICS options