调整rac

--获得会话等待信息有价值的查询，instance_id列出保存等待会话的实例，SID是等待会话的为唯一标识符(gv$session)
--p1,p2,p3列出了时间专用信息，last_sql列出等待会话执行的最后SQL;
select sw.inst_id         instance_id,
       sw.sid             sid,
       sw.state           state,
       sw.event           event,
       sw.seconds_in_wait seconds_waiting,
       sw.p1,
       sw.p2,
       sw.p3,
       sa.sql_text        last_sql
  from gv$session_wait sw, gv$session s, gv$sqlarea sa
 where sw.event not in ('rdbms ipc message',
                        'smon timer',
                        'pmon timer',
                        'SQL*Net message from client',
                        'lock manager wait for remote message',
                        'ges remote message',
                        'gcs remote message',
                        'gcs for action',
                        'client message',
                        'pipe get',
                        'null event',
                        'PX Idle Wait',
                        'single-task message',
                        'PX Deq:Execution Msg',
                        'KXFQ:kxfqdeq - normal deqeue',
                        'listen endpoint status',
                        'slave wait',
                        'wakeup time manager')
   and sw.seconds_in_wait > 0
   and (sw.inst_id = s.inst_id and sw.sid = s.sid)
   and (s.inst_id = sa.inst_id and s.sql_address = sa.address)
 order by seconds_waiting desc;

 

--查询上面事件的参数名称：
select distinct event event, p1text, p2text, p3text
  from gv$session_wait sw
 where sw.event not in ('rdbms ipc message',
                        'smon timer',
                        'pmon timer',
                        'SQL*Net message from client',
                        'lock manager wait for remote message',
                        'ges remote message',
                        'gcs remote message',
                        'gcs for action',
                        'client message',
                        'pipe get',
                        'null event',
                        'PX Idle Wait',
                        'single-task message',
                        'PX Deq:Execution Msg',
                        'KXFQ:kxfqdeq - normal deqeue',
                        'listen endpoint status',
                        'slave wait',
                        'wakeup time manager')
   and sw.seconds_in_wait > 0
 order by event;

--当需要将另一个实例以前改变的数据块写到磁盘以响应或缓存老化时，就会出现熔合写；

-- 确定熔合写操作的比率
select a.inst_id "instance", a.value / b.value "cache fusion writes ration"
  from gv$sysstat a, gv$sysstat b
 where a.name = 'DBWR fusion writes'
   and b.name = 'physical writes'
   and b.inst_id = a.inst_id
 order by a.inst_id;

--缓存熔合写操作比率大值表示：

(1),缓存不够大，

(2),检查点不够，

(3),根据缓存或检查点写的大量缓冲器；

--oracle 建议一致数据块请求的平均延迟通常不应该超过15ms.

select b1.inst_id,
       b2.value "GCS CR BLOCKS RECEIVED",
       b1.value "GCS CR BLOCK RECEIVE TIME",
       ((b1.value / b2.value) * 10) "AVG CR BLOCK RECEIVE TIME(ms)"
  from gv$sysstat b1
  join gv$sysstat b2
    on b1.inst_id = b2.inst_id
 where b1.name = 'gc cr block receive time'
   and b2.name = 'gc cr blocks received';

--db_multi_block_read_count参数的高值也可能影响延迟

--为了支持其他用户，该参数可能限制你的并行化处理
SQL> show parameter parallel_adaptive_multi_user;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_adaptive_multi_user         boolean     TRUE
--parallel_max_servers调整实例允许的并行查询服务器进程数
SQL> show parameter parallel_max_servers;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_max_servers                 integer     40

--v$pq_tqstat用于调整长时间运行的查询时，可获得最佳效果；
--这些查询需要在服务器进程间进行特定的调整和数据分布的评估。
select dfo_number, tq_id, server_type, num_rows, bytes, waits, process
  from v$pq_tqstat;

--v$pq_sysstat是当前高负载情况下正在执行的服务器数量，以及并行服务器启动和关闭比率的理想工具；
select Statistic,Value from v$pq_sysstat;
 

转载于:https://www.cnblogs.com/alang85/archive/2011/08/24/2152529.html