oracle性能调优学习0622
[*] 查询数据库中的等待事件：
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_signature0
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
页:
[1]