sql优化命令

一．   set autotrace on\off; 打开（关闭）执行计划并显示所有信息。

a.       set autotrace traceonly; 看执行计划和统计信息但不看sql输出结果。

b.       set autotrace on explain; 只看执行计划但输出sql结果。

c.       set autotrace statistics;     只看统计信息但输出sql结果。

a b c虽然真实执行了sql语句，但执行计划未必是真实的。

d.       set autotrace traceonly explain; 会对sql语句进行一次硬解析但不执行sql，只有预估的执行计划。

 

硬解析：

1.语法检查：检查sql的书写是否合法。

1.  语义检查：检查sql的访问对象是否合法，比如对象是否存在，该用户是否拥有权限。

2.  对sql语句相关的表，视图，索引进行解析并选择执行计划。

3.  根据数据字典检查检查这些对象，产生执行计划，输出结果。

   软解析：

    软解析是在硬解析基础上进行的，在对sql语句进行完语法检查和语义检查后，会在共享池中检测是否有完全相同的语句被解析过，如果有则直接执行以前的执行计划，相当于省去了硬解析的解析过程。

      可见软解析的成本低于硬解析，所以一个sql重复运行的时候，应避免硬解析，绑定变量便是一种手段，可以节约大量的成本。

 

二．   explain plan for sql;(要执行的sql语句)

alter session set statistics_level = all; 设置显示更多信息。

select  *  from  table(dbms_xplan.display_cursor(null,null,’allstats’));　　

如果报错：NOTE: cannot fetch plan for SQL_ID: 9babjv8yq8ru3, CHILD_NUMBER: 1

      Please verify value of SQL_ID and CHILD_NUMBER;

      It could also be that the plan is no longer in cursor cache (check v$sql_p

lan)

解决方法：set serveroutput off

示例：

            SQL> explain plan for select ename from emp where empno='7369';

 

已解释。

 

SQL> alter session set statistics_level=all;

 

会话已更改。

 

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats /advanced /last /peeked_binds'));

 

PLAN_TABLE_OUTPUT

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

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

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

SQL_ID  5tmy0xajuqaz4, child number 0

 

explain plan for select ename from emp where empno='7369'

 

NOTE: cannot fetch plan for SQL_ID: 5tmy0xajuqaz4, CHILD_NUMBER: 0

      Please verify value of SQL_ID and CHILD_NUMBER;

      It could also be that the plan is no longer in cursor cache (check v$sql_plan)

 

 

已选择8行。

 

SQL> set serveroutput off

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats'));

 

PLAN_TABLE_OUTPUT

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

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

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

SQL_ID  5qgz1p0cut7mx, child number 1

 

BEGIN DBMS_OUTPUT.DISABLE; END;

 

NOTE: cannot fetch plan for SQL_ID: 5qgz1p0cut7mx, CHILD_NUMBER: 1

      Please verify value of SQL_ID and CHILD_NUMBER;

      It could also be that the plan is no longer in cursor cache (check v$sql_plan)

 

 

已选择8行。

 

SQL> set serveroutput off

SQL> select ename from emp where empno='7369';

 

ENAME

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

SMITH

 

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats'));

 

PLAN_TABLE_OUTPUT

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

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

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

SQL_ID  6kyf2kz6xx5dy, child number 0

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

select ename from emp where empno='7369'

 

Plan hash value: 2949544139

 

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

| Id  | Operation                   | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

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

|   0 | SELECT STATEMENT            |        |      1 |        |      1 |00:00:00.01 |       2 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |      1 |      1 |      1 |00:00:00.01 |       2 |

|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |      1 |      1 |      1 |00:00:00.01 |       1 |

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

 

Predicate Information (identified by operation id):

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

 

   2 - access("EMPNO"=7369)

 

 

已选择19行。

 

这个执行计划是真实执行情况下的执行计划。

            收集all stats有负载，用完后设置回默认值

                            Set statistics_level=typical;

使用AWR查询执行计划：

select plan_table_output

from table(dbms_xplan.display_awr(‘sql_id’,null,null,’advanced+peeked_binds’));

前提是该执行计划已经被采集到ＡＷＲ仓库中。

如何获取sql_id:

         Select sql_id ,sql_text from v$SQL where sql_text like’%sql%’;   ’%sql%’是用来匹配sql的字符串.

SQL> alter session set sql_trace=true;

 

会话已更改。

SQL> select * from scott.emp;

 

SQL> desc v$mystat;

 名称

 

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

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

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

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

 SID

 

 STATISTIC#

 

 VALUE

 

 

SQL> select sid from v$mystat where rownum=1;

 

       SID

----------

        10

SQL> select spid from v$session s,v$process p where s.paddr=p.addr and s.sid=10;

 

SPID

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

3940

 

SQL> oradebug setmypid

已处理的语句

SQL> oradebug tracefile_name

f:\app\frank\diag\rdbms\frank\frank\trace\frank_ora_3940.trc

 

C:\Users\Frank>tkprof f:\app\frank\diag\rdbms\frank\frank\trace\frank_ora_3940.trc f:\sql_trace.txt

 

 

SQL> select sql_id from v$sql where sql_text='select * from scott.emp';

 

SQL_ID

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

ggqns3c1jz86c

 

 

查询CPU使用率：

 

SQL> select b.value*100/(a.value+b.value)||'%' "cpu_use" from v$osstat a,v$osstat b where a.stat_name='IDLE_TIME' AND b.stat_name='BUSY_TIME';

 

cpu_use

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

14.1060937498112672950336940723522246647%

 （如有错误，请留言。）

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30175600/viewspace-1656935/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30175600/viewspace-1656935/