Shulou(Shulou.com)06/02 Report--

I still have a lot of knowledge in 10g. Today, my colleague directly found the PGA consumed by the SQL statement in ASH, only to find that there are so many more columns in the ASH of 11gR2.

1. Indicate whether the ASH record has been flushed to disk

There is no such column in IS_AWR_SAMPLE dba_hist_active_sess_history.

2.SQL statement information

Translation name of SQL_OPNAME SQL_OPCODE, type of SQL statement

3. For a recursive SQL, capture the information of its parent SQL

TOP_LEVEL_SQL_ID

TOP_LEVEL_SQL_OPCODE

You can use this column to find the most resource-consuming SQL in storage, or the slowest SQL statement in DDL recursive calls.

SELECT sql_id,count (*) FROM v$active_session_history

WHERE TOP_LEVEL_SQL_ID='5w6mc35fa18tk'

GROUP BY sql_id

ORDER BY 2 DESC

4. Capture execution plan information in ASH, including which step the statement is performing

SQL_PLAN_LINE_ID

SQL_PLAN_OPERATION

SQL_PLAN_OPTIONS

You can find the slowest part of the SQL statement through these columns, and you need to optimize this

SELECT A.SQL_PLAN_HASH_VALUE

A.SQL_PLAN_LINE_ID

A.SQL_PLAN_OPERATION

A.SQL_PLAN_OPTIONS

B.OWNER | |'. | | B.OBJECT_NAME OBJECT_NAME

COUNT (*)

FROM V$ACTIVE_SESSION_HISTORY A, DBA_OBJECTS B

WHERE A.SQL_ID = '11jpuymjh9vsc`

AND A.CURRENT_OBJ# = B.OBJECT_ID (+)

GROUP BY A.SQL_PLAN_HASH_VALUE

A.SQL_PLAN_LINE_ID

A.SQL_PLAN_OPERATION

A.SQL_PLAN_OPTIONS

B.OWNER | |'. | | B.OBJECT_NAME

ORDER BY COUNT (*) DESC

The unique identifier for one execution of 5.SQL, SQL_ID, SQL_EXEC_START, and SQL_EXEC_ID, to indicate the execution of SQL.

Moreover, you can find the starting time of the SQL execution and calculate how long it has been executed this time.

SQL_EXEC_ID

SQL_EXEC_START

Judge whether there is a time when the execution of a SQL is very slow, such as 1s, for a period of time, when the execution is more than 12s.

SELECT SQL_ID, SQL_EXEC_START, SQL_EXEC_ID, COUNT (*)

FROM V$ACTIVE_SESSION_HISTORY A

WHERE A.SQL_ID = '11jpuymjh9vsc`

GROUP BY SQL_ID, SQL_EXEC_START, SQL_EXEC_ID

ORDER BY COUNT (*) DESC

6. Parallel enhancements, adding QC_SESSION_SERIAL# columns and PX_FLAGS status columns

QC_SESSION_SERIAL#

PX_FLAGS

QC_SESSION_ID SESSION_ID 's are all parallel child processes. Added that QC_SESSION_SERIAL# can be defined as the only coordinator

7.Blocking enhancement, 11g solving problems through Blocking is already easy.

BLOCKING_INST_ID 11g added, strangely, 10g v$session has this column, but ASH does not

BLOCKING_HANGCHAIN_INFO indicates whether BLOCKING_SESSION is on hang chain.

REMOTE_INSTANCE# is used for cluster waiting to indicate which instance should provide the requested data block. Only the cluster class waits for this.

8. For the object you are currently dealing with, a new row# has been added, and there is already a CURRENT_OBJ#,CURRENT_FILE#,CURRENT_BLOCK#.

CURRENT_ROW#

You can check the row of the TX waiting for the plunger, and you can find the row of the plunger by assembling the ROWID.

SELECT A.SQL_ID

A.CURRENT_OBJ#

A.CURRENT_FILE#

A.CURRENT_BLOCK#

A.CURRENT_ROW#

COUNT (*)

FROM dba_hist_active_sess_history A

WHERE A.EVENT = 'enq: TX-row lock contention'

GROUP BY A.SQL_ID

A.CURRENT_OBJ#

A.CURRENT_FILE#

A.CURRENT_BLOCK#

A.CURRENT_ROW#

ORDER BY COUNT (*) DESC

9.CONSUMER_GROUP

CONSUMER_GROUP_ID,DBA_RSRC_CONSUMER_GROUPS correspondence

10.Time Mobel

The IN after the TIME_MODEL, according to the binary values, what the session did during this sampling interval

IN_CONNECTION_MGMT connection management call elapsed time

IN_PARSE parse time elapsed

IN_HARD_PARSE hard parse elapsed time

IN_SQL_EXECUTION sql execute elapsed time

IN_PLSQL_EXECUTION PL/SQL execution elapsed time

IN_PLSQL_RPC inbound PL/SQL rpc elapsed time

IN_PLSQL_COMPILATION PL/SQL compilation elapsed time

IN_JAVA_EXECUTION Java execution elapsed time

IN_BIND repeated bind elapsed time

IN_CURSOR_CLOSE

IN_SEQUENCE_LOAD sequence load elapsed time

When a problem with a TM is shown in the AWR, use these columns to find the TOP process or SQL

There is a hard parsed SQL, and the result should be compared with v$sql.

SELECT SQL_PLAN_HASH_VALUE, COUNT (*)

FROM V$ACTIVE_SESSION_HISTORY

WHERE IN_HARD_PARSE ='Y'

GROUP BY SQL_PLAN_HASH_VALUE

ORDER BY 2 DESC

Session marking of the 11.REPLAY feature

REPLAY_OVERHEAD

IS_REPLAYED

DBREPLAY_FILE_ID

DBREPLAY_CALL_COUNTER

twelve。 Time statistics

TM_DELTA_TIME one time statistical interval

TM_DELTA_CPU_TIME in this interval, CPU time

TM_DELTA_DB_TIME in this interval, DB time

Because the granularity of ASH sampling is 1 second, but processes are not all ACTIVE within 1 second. The granularity of the statistics is microseconds (1/1000000 seconds).

TM_DELTA_TIME-TM_DELTA_DB_TIME = INACTIVE TIME

TM_DELTA_DB_ TIME-TM_DELTA_CPU_TIME = WAIT TIME

13.IO network statistics

DELTA_TIME

DELTA_READ_IO_REQUESTS

DELTA_WRITE_IO_REQUESTS

DELTA_READ_IO_BYTES

DELTA_WRITE_IO_BYTES

DELTA_INTERCONNECT_IO_BYTES

SQL with high physical read / write / heartbeat traffic in statistical time

SELECT SQL_ID

SUM (DELTA_READ_IO_REQUESTS)

SUM (DELTA_WRITE_IO_REQUESTS)

SUM (DELTA_READ_IO_BYTES)

SUM (DELTA_WRITE_IO_BYTES)

SUM (DELTA_INTERCONNECT_IO_BYTES)

FROM V$ACTIVE_SESSION_HISTORY

GROUP BY SQL_ID

ORDER BY 2 DESC

14.PGA/TMP current usage statistics

PGA_ALLOCATED

TEMP_SPACE_ALLOCATED

Select * from (

Select sample_time,session_id,sql_id,PGA_ALLOCATED,TEMP_SPACE_ALLOCATED from v$active_session_history

Where TEMP_SPACE_ALLOCATED is not null

Order by TEMP_SPACE_ALLOCATED desc

) where rownum

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.