7-2 DBA Advisor The first time the test was last operated

Sqlplus Execution:

the@?/rdbms/admin/awrrpt Production snapshot, a point in time, and then the next point in time.2--
 attachment Job First execution step: 1) SQLPLUS in the execution of production snapshot 2 time points; 2) generate an AWR report. Analyze the contents of the report. Related to the Command window length adjustment view; 3) for sqlid, view the corresponding SQL execution performance analysis; 4) switch to the same SQL Sqlid after adding the init condition to view the corresponding performance analysis; 5) Review the theoretical and practical effectiveness analysis report.  corresponding field interpretation analysis. -- (DBA Consultant Training content) <DBA Consultant Training Content @20141230>

Generate snapshot

Snapshot is Oracle's information collection for the system according to the time period. You can create the snapshot manually, get an application before the start of the system information, and then after the application is completed again to create the snapshot, get the application of the previous period of the system information in more detail about the impact of this application on the system. After the 2nd time snapshot is created, use the Awrrpt script to create a awrrpt based on the two-time snapshot.

Create a method

Execute in Sqlplus

Execute Dbms_workload_repository.create_snapshot ();

Get current system State

Execute in 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

One minute session detail information

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;

One-minute event information

Select EVENT, Session_state, Blocking_session_status, COUNT (*), trunc (sum (time_waited)/count (*)) from V$ac Tive_session_history where sample_time>sysdate-1/1440 GROUP by EVENT, Session_state,blocking_session_status order by 4;

The most CPU-intensive SQL statement in a minute

SELECT sql_id, COUNT (*), Round (COUNT (*)/SUM (COUNT (*)) over (), 2) pctload from V$active_session_history WHERE sample_time > sysdate-1/(*) and Session_type <> ' BACKGROUND ' and session_state = ' on CPUs ' GROUP by sql_id ORDER by Count (*);

In a minute, the CPU-intensive 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 (*);

The most CPU-intensive session details in a minute

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);

Get the actual execution plan

First Get 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;

The string inside the single quotation mark is the part of the original SQL statement

You can also get sql_id by using the method mentioned in ' Get current system state ', or get sql_id from Awrrpt.

Running in Sqlplus

SELECT * FROM table (dbms_xplan.display_cursor (' 31b1ba01z95b1 ', null, ' all Allstats last '));

If you set it in Sqlplus before the statement is run

Alter session set Statistics_level=all;

Alter session set Timed_statistics=true;

The content of the execution plan is richer, and the above settings expire when you exit the session.

From for notes (Wiz)

List of attachments

7-2 DBA Advisor The first time the test was last operated