诊断oracle性能

--查看session的运行情况
select v.BLOCKING_SESSION,
       v.SID,
       ''''||V.SQL_ID||''',',
       v.EVENT,
       v.ROW_WAIT_OBJ#,
       v.ROW_WAIT_FILE#,
       v.ROW_WAIT_BLOCK#,
       v.ROW_WAIT_ROW#,
       V.*
  from gv$session v
where v.USERNAME = 'RCR'
   AND V.STATUS = 'ACTIVE';

编译包 编译不过的话 直接查 gv$access 这个视图

SELECT /*+ rule */ lpad(' ',decode(l.xidusn ,0,3,0))||l.oracle_username User_name,
        o.owner,o.object_name,o.object_type,s.sid,s.serial# ,s.OSUSER,s.MACHINE
FROM v$locked_object l,dba_objects o,v$session s
WHERE l.object_id=o.object_id
AND l.session_id=s.sid
ORDER BY o.object_id,xidusn DESC
;

2jjxp2ck3sxsw
0w2ydkfjpgkhf

SELECT *
FROM ALL_OBJECTS O
WHERE O.OBJECT_ID=100022;
--查看SQL
SELECT * FROM GV$SQL S WHERE S.SQL_ID='0w2ydkfjpgkhf';
--查看SQL
select * from gv$sqlarea v where v.SQL_ID='0w2ydkfjpgkhf';
--查可绑定变量
SELECT * FROM GV$SQL_BIND_CAPTURE V WHERE V.SQL_ID='2jjxp2ck3sxsw';
--查看执行计划
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor('4uhvrshjuj7sj'));
--杀session
/*ALTER SYSTEM KILL SESSION '1573,22798';
ALTER SYSTEM KILL SESSION '1581,62578';
ALTER SYSTEM KILL SESSION '1631,65232';*/

---------------表分析-----------
BEGIN
   DBMS_STATS.GATHER_TABLE_STATS(
            OWNNAME => 'DMRCR'  ,
            TABNAME => 'DM_Rcr_rev_Task_Fact' ,
            ESTIMATE_PERCENT => 100             , --分析的百分比
            CASCADE => TRUE                    , --是否同时分析索引
            DEGREE => 8 );                       --并行度
END;

--查看表空间
SELECT C.TABLESPACE_NAME "表空间",
       ROUND(A.BYTES / 1024 / 1024 / 1024, 2) "表空间大小",
       ROUND((A.BYTES - B.BYTES) / 1024 / 1024 / 1024, 2) "已使用空间",
       ROUND(B.BYTES / 1024 / 1024 / 1024, 2) "剩余空间",
       ROUND(B.BYTES / A.BYTES * 100, 2) "剩余百分比"
  FROM (SELECT T.TABLESPACE_NAME, SUM(T.BYTES) BYTES
          FROM DBA_DATA_FILES T
         GROUP BY T.TABLESPACE_NAME) A,
       (SELECT A.TABLESPACE_NAME, NVL(SUM(B.BYTES), 0) BYTES
          FROM DBA_DATA_FILES A, DBA_FREE_SPACE B
         WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+)
           AND A.FILE_ID = B.FILE_ID(+)
         GROUP BY A.TABLESPACE_NAME) B,
       DBA_TABLESPACES C
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+)
   AND A.TABLESPACE_NAME = C.TABLESPACE_NAME
   AND C.TABLESPACE_NAME IN ('RCR_DATA', 'RCR_IDX')
ORDER BY ROUND(B.BYTES / 1024 / 1024 / 1024, 2);

--定位SESSION中SQL运行问题-
select/* ash.session_id,*/ ash.sql_id, count(*)
  from dba_hist_active_sess_history ash
where ash.user_id = 351  -- 353 RAS,351 RCR
   and ash.snap_id between 33284 and 33284 + 25
group by/* ash.session_id,*/ ash.sql_id
order by 2 desc;