oracle性能调优学习0622

1. 查询数据库中的等待事件：

   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 (),
   2
   ) 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;

2.整合时间模型和等待接口：

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;
监控索引的使用情况：

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.识别从绑定变量中获益的sql语句：

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 <= 10
ORDER BY matches DESC;