oracle性能调优学习0622

23decxf

• 　　查询数据库中的等待事件：
  　　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