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

Query wait events in the database:

SET lines 100

SET pages 10000

COLUMN wait_class format a12

COLUMN event format a30

COLUMN total_waits format 999999

COLUMN total_us format 999999999

COLUMN pct_time format 99.99

COLUMN avg_us format 999999.99

SET echo on

SELECT wait_class, event, total_waits AS waits

ROUND (time_waited_micro / 1000) AS total_ms

ROUND (time_waited_micro * 100 / SUM (time_waited_micro) OVER ()

two

) AS pct_time

ROUND ((time_waited_micro / total_waits) / 1000, 2) AS avg_ms

FROM v$system_event

WHERE wait_class' Idle'

ORDER BY time_waited_micro DESC

two。 Integrate the time model and wait interface:

SELECT event

Total_waits

Round (time_waited_micro / 1000000) AS time_waited_secs

Round (time_waited_micro * 100 / SUM (time_waited_micro) over (), 2) AS pct_time

FROM (SELECT event, total_waits, time_waited_micro

FROM v$system_event

WHERE wait_class' Idle'

UNION

SELECT stat_name, NULL, VALUE

FROM v$sys_time_model

WHERE stat_name IN ('DB CPU', 'backup cpu time'))

ORDER BY 3 DESC

SELECT sample_seconds

Stat_name

Waits_per_second waits_per_sec

Microseconds_per_second ms_per_sec

Pct_of_time pct

FROM opsg_delta_report

WHERE microseconds_per_second > 0

Monitor the use of the index:

WITH in_plan_objects AS

(SELECT DISTINCT object_name FROM v$sql_plan WHERE object_owner = 'SCOTT')

SELECT table_name

Index_name

CASE

WHEN object_name IS NULL THEN

'NO'

ELSE

'YES'

END AS in_cached_plan

FROM user_indexes

LEFT OUTER JOIN in_plan_objects

ON (index_name = object_name)

4. Identify sql statements that benefit from binding variables:

WITH force_matches AS

(SELECT force_matching_signature

COUNT (*) matches

MAX (sql_id | | child_number) max_sql_child

DENSE_RANK () OVER (ORDER BY COUNT (*) DESC)

Ranking

FROM v$sql

WHERE force_matching_signature 0

AND parsing_schema_name 'SYS'

GROUP BY force_matching_signature

HAVING COUNT (*) > 5)

SELECT sql_id, matches, parsing_schema_name schema, sql_text

FROM v$sql JOIN force_matches

ON (sql_id | | child_number = max_sql_child)

WHERE ranking

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.