SQLPLUS执行: 

生成snapshot

snapshot是oracle对系统按照时间周期进行信息收集。可以通过人工创建snapshot，获得一个应用开始前的系统信息，再应用完成后再次创建snapshot，获得刚才这段应用运行期间的系统信息更详细的了解本次应用对系统的影响。第2次snapshot创建完后，使用awrrpt脚本创建出基于这两次的snapshot的awrrpt即可。

创建方法

在sqlplus中执行

execute dbms_workload_repository.create_snapshot();

获得当前系统状态

在sqllplus中执行

col SQL_PLAN_OPERATION for a20

col EVENT for a34

col SESSION_STATE for a15

col SESSION_ID for 99999

col SESSION_SERIAL#  for 999999

col username for a12

col COMMAND for  99999

col STATUS for a10

col MACHINE for a20

col TERMINAL for a20

col PROGRAM for a20

col TERMINAL for a20

col SQL_ID for a20

一分钟session明细信息

select SAMPLE_TIME ,SESSION_ID,SESSION_SERIAL#,SESSION_TYPE  ,SQL_OPNAME,sql_id, SQL_PLAN_OPERATION,EVENT     ,SESSION_STATE        ,TIME_WAITED, BLOCKING_SESSION_STATUS  ,BLOCKING_SESSION,BLOCKING_SESSION_SERIAL#,PROGRAM, MODULE      from v$active_session_history where SAMPLE_TIME>sysdate -1/1440  order by SAMPLE_TIME ;

一分钟事件信息

select EVENT     ,SESSION_STATE        , BLOCKING_SESSION_STATUS  ,count(*),trunc(sum( TIME_WAITED)/count(*) )   from v$active_session_history where SAMPLE_TIME>sysdate -1/1440   group by EVENT, SESSION_STATE,BLOCKING_SESSION_STATUS   order by 4;

一分钟内，最消耗CPU的sql语句 

SELECT sql_id, count(*), round(count(*) / sum(count(*)) over(), 2) pctload FROM V$ACTIVE_SESSION_HISTORY WHERE sample_time > sysdate - 1/ (24 * 60) AND session_type <> ‘BACKGROUND‘ AND session_state = ‘ON CPU‘ GROUP BY sql_id ORDER BY count(*) ;

一分钟内，最消耗CPU的session

SELECT session_id, count(*) FROM V$ACTIVE_SESSION_HISTORY WHERE session_state = ‘ON CPU‘ AND sample_time > sysdate -1/(24*60) GROUP BY session_id ORDER BY count(*) ;

一分钟内，最消耗CPU的session 详细信息

select USERNAME,COMMAND,STATUS,MACHINE,TERMINAL,PROGRAM,SQL_ID from v$session where sid in (select SESSION_ID from (SELECT session_id, count(*) c FROM V$ACTIVE_SESSION_HISTORY WHERE session_state = ‘ON CPU‘ AND sample_time > sysdate -1/(24*60) GROUP BY session_id order by c desc ) where rownum <10);

获得实际执行计划

首先获得sql_id

select sql_id, child_number,executions,parse_calls,loads,invalidations ,LAST_LOAD_TIME from v$sql where sql_text like   ‘SELECT distinct substr(l.line_name, 1, 1) || substr(l.line_nam%‘ order by LAST_LOAD_TIME;

单引号内的字符串是原始sql语句的部分

还可以通过‘获得当前系统状态’提到的方法获得sql_id，或者从awrrpt中获得sql_id。

在sqlplus 中运行

 select * from table(dbms_xplan.display_cursor(‘31b1ba01z95b1‘,null,‘all ALLSTATS LAST‘));

如果在语句运行前在sqlplus中设置

alter session set statistics_level=all;

alter session set timed_statistics=true;

执行计划的内容会更丰富，以上设置在退出该session就失效。

7-2 DBA顾问第一次上次操作考试