ITPub博客

首页 > 数据库 > Oracle > 111

111

原创 Oracle 作者:风行年华 时间:2019-09-28 16:12:33 0 删除 编辑


--查看cpu

select * from dba_hist_snapshot t  order by t.end_interval_time desc ;


select * from (select round(nvl((sqt.cput / 1000000), to_number(null)),2) "Cpu Time (s)",  

              round( nvl((sqt.elap / 1000000), to_number(null)),2) "Elap Time (s)",  

               sqt.exec,  

               round(decode(sqt.exec,0,to_number(null),(sqt.cput / sqt.exec / 1000000)),2) "Cpu per Exec (s)",  

               round((100 * (sqt.elap / (select sum(e.value) - sum(b.value)  

                               from dba_hist_sys_time_model b,  

                                    dba_hist_sys_time_model e  

                              where b.snap_id = &beg_snap and  

                                    e.snap_id = &end_snap and  

                                    e.stat_name = 'DB time' and  

                                    b.stat_name = 'DB time'))) ,2)norm_val,  

               sqt.sql_id, 

                 sqt.plan_hash_value,

               decode(sqt.module, null, null, 'Module: ' || sqt.module) SqlModule,  

               nvl(to_nchar(SUBSTR(st.sql_text,1,2000)) , (' ** SQL Text Not Available ** ')) SqlText  

          from (select sql_id,  

                            plan_hash_value,

                       max(module) module,  

                       sum(cpu_time_delta) cput,

                       sum(elapsed_time_delta) elap,  

                       sum(executions_delta) exec  

                  from dba_hist_sqlstat  

                 where &beg_snap < snap_id and  

                       snap_id <= &end_snap  

                 group by sql_id,plan_hash_value) sqt,  

               dba_hist_sqltext st  

         where st.sql_id(+) = sqt.sql_id 

         order by nvl(sqt.cput, -1) desc,  

                  sqt.sql_id)  

 where rownum < 65 and  

       (rownum <= 10 or norm_val > 1);


SELECT  *
   FROM  ( SELECT  X.*, Y.SQL_TEXT
           FROM  ( SELECT  A.SQL_ID,
                        A.SQL_PLAN_HASH_VALUE,
                        SUM (A.TM_DELTA_CPU_TIME / 1000) CPU_TIMES,
                        SUM (A.TM_DELTA_DB_TIME) / 1000 DB_TIMES,
                        SUM (A.DELTA_READ_IO_REQUESTS) IOPS,
                        SUM (A.DELTA_READ_IO_BYTES) / 1024 / 1024 / 1024 DISK_READS
                   FROM  V$ACTIVE_SESSION_HISTORY A
                  WHERE  A.SAMPLE_TIME > =TO_DATE( '2017-07-22 10:00:00' 'YYYY-MM-DD HH24:MI:SS' )
                    AND  A.SAMPLE_TIME < =TO_DATE( '2017-07-22 11:00:00' 'YYYY-MM-DD HH24:MI:SS' )
                  GROUP  BY  A.SQL_ID, A.SQL_PLAN_HASH_VALUE) X,
                ( SELECT  DISTINCT  B.SQL_ID, B.PLAN_HASH_VALUE, B.SQL_TEXT
                   FROM  V$SQL B) Y
          WHERE  X.SQL_ID = Y.SQL_ID
            AND  X.SQL_PLAN_HASH_VALUE = Y.PLAN_HASH_VALUE)
  WHERE  DISK_READS > 1
  ORDER  BY  DISK_READS  DESC ;





--发现那些SQL运行了大量的PARSE

select sql_text, parse_calls, executions

  from v$sqlarea

 order by parse_calls desc;

 

--SYS的总的PARSE情况

select name, value from v$sysstat where name like 'parse count%';

 

--CPU空间及繁忙情况

select * from v$osstat;

 

--查看每个Session的CPU利用情况:

select ss.sid, se.command, ss.value CPU, se.username, se.program

  from v$sesstat ss, v$session se

 where ss.statistic# in

       (select statistic#

          from v$statname

         where name = 'CPU used by this session')

   and se.sid = ss.sid

   and ss.sid > 6

 order by CPU desc;

 

--比较一下哪个session的CPU使用时间最多,然后查看该Session的具体情况:

select s.sid, s.event, s.MACHINE, s.OSUSER, s.wait_time, w.seq#, q.sql_text

  from v$session_wait w, v$session s, v$process p, v$sqlarea q

 where s.paddr = p.addr

   and s.sid = &p

   and s.sql_address = q.address;

 

--占用CPU最高的10个Session及其SQL语句

select s.sid,

       w.wait_time,

       w.seconds_in_wait,

       w.state,

       w.wait_time_micro,

       w.time_remaining_micro,

       w.time_since_last_wait_micro,

       p.USERNAME,

       status,

       server,

       schemaname,

       osuser,

       machine,

       p.terminal,

       p.program,

       logon_time,

       w.event,

       w.wait_class,

       tracefile,

       sql_text,

       last_active_time

  from v$session_wait w, v$session s, v$process p, v$sqlarea q

 where s.paddr = p.addr

   and s.sid in (select sid

                   from (select ss.sid

                           from v$sesstat ss, v$session se

                          where ss.statistic# in

                                (select statistic#

                                   from v$statname

                                  where name = 'CPU used by this session')

                            and se.sid = ss.sid

                            and ss.sid > 6

                          order by ss.value desc)

                  where rownum < 11)

   and s.sql_address = q.address;

   




SELECT executions,

       end_of_fetch_count,

       elapsed_time / px_servers elapsed_time,

       cpu_time / px_servers cpu_time,

       buffer_gets / executions buffer_gets

  FROM (SELECT sum(executions_delta) as EXECUTIONS,

               sum(case

                     when px_servers_execs_delta > 0 then

                      px_servers_execs_delta

                     else

                      executions_delta

                   end) as px_servers,

               sum(end_of_fetch_count_delta) as end_of_fetch_count,

               sum(elapsed_time_delta) as ELAPSED_TIME,

               sum(cpu_time_delta) as CPU_TIME,

               sum(buffer_gets_delta) as BUFFER_GETS

          FROM DBA_HIST_SQLSTAT s, V$DATABASE d, DBA_HIST_SNAPSHOT sn

         WHERE s.dbid = d.dbid

           AND bitand(nvl(s.flag, 0), 1) = 0

           AND sn.end_interval_time >

               (select systimestamp at TIME ZONE dbtimezone from dual) - 7

           AND s.sql_id = :1

           AND s.snap_id = sn.snap_id

           AND s.instance_number = sn.instance_number

           AND s.dbid = sn.dbid

           AND parsing_schema_name = :2)

  

  

  


select stime,

       sum(nvl(case

                 when event = 'db file sequential read' then

                  round(times, 2)

               end,

               0)) as "db file sequential rea",

       sum(nvl(case

                 when event = 'log file sync' then

                  round(times, 2)

               end,

               0)) as "log file sync",

       sum(nvl(case

                 when event = 'log file parallel write' then

                  round(times, 2)

               end,

               0)) as "log file parallel write",

       sum(nvl(case

                 when event = 'db file scattered read' then

                  round(times, 2)

              end,

               0)) as "db file scattered read"

  from (select t.snap_id,

               to_char(b.begin_interval_time, 'yyyy-mm-dd hh24:mi:ss') stime,

               event_name event,

              decode(sign(total_waits - lag(total_waits, 1)

                           over(partition by event_name order by t.snap_id)),

                      -1,

                      (lag(time_waited_micro, 1)

                       over(partition by event_name order by t.snap_id)) /

                      (lag(total_waits, 1)

                       over(partition by t.instance_number,

                            event_name order by t.snap_id)),

                      0,

                      0,

                      (time_waited_micro - lag(time_waited_micro, 1)

                       over(partition by event_name order by t.snap_id)) /

                      (total_waits - lag(total_waits, 1)

                       over(partition by event_name order by t.snap_id))) / 1000 as times

          from dba_hist_system_event t, dba_hist_snapshot b

         where t.snap_id = b.snap_id

           and b.begin_interval_time > trunc(sysdate-10)

           and b.instance_number = t.instance_number

           and event_name in

               ('db file sequential read', 'log file sync',

                'db file scattered read', 'log file parallel write')

           and t.instance_number = 1)

where times > 0 

 group by snap_id, stime

order by snap_id desc;





select b.event EVENT,

       a.username 用户,

       a.sid SID,

       c.address ADDRESS,

       substr(c.sql_text, 1, 100) SQL

  from gv$session a,

       gv$session_wait b,

       gv$sql c,

       (select event, count(1) num

          from gv$session_wait

         where event not in

               ('SQL*Net message from client',

                'SQL*Net message to client',

                'rdbms ipc message',

                'queue messages',

                'Streams AQ: waiting for messages in the queue')

         group by event

        having(event = 'latch free' and count(1) > 20) or (event = 'db file sequential read' and count(1) > 100) or (event = 'buffer busy waits' and count(1) > 100) or (event = 'db file scattered read' and count(1) > 20) or (event not in('latch free',

                                                                                                                                                                                                                                             'db file sequential read',

                                                                                                                                                                                                                                             'buffer busy waits',

                                                                                                                                                                                                                                             'db file scattered read') and count(1) > 20)) d

where a.sid = b.sid

   and a.sql_address = c.address

   and b.event = d.event

order by 2



 find ./ -mtime +10  -exec rm -rf {} \;



SELECT   s.username,

         decode(l.type, 'TM', 'TABLE LOCK', 'TX', 'ROW LOCK', NULL) LOCK_LEVEL,

         o.owner,

         o.object_name,

         o.object_type,

         s.sid,

         s.serial#

    FROM gv$session s, gv$lock l, dba_objects o

   WHERE l.sid = s.sid

     AND l.id1 = o.object_id(+)

     AND s.username is NOT NULL

     AND O.object_name=upper('');

 

 

 

SELECT

  A.OWNER,                        --OBJECT所属用户

  A.OBJECT_NAME,                  --OBJECT名称(表名)

  B.XIDUSN,

  B.XIDSLOT,

  B.XIDSQN,

  B.SESSION_ID,                   --锁表用户的session

  B.ORACLE_USERNAME,              --锁表用户的Oracle用户名

  B.OS_USER_NAME,                 --锁表用户的操作系统登陆用户名

  B.PROCESS,

  B.LOCKED_MODE, 

  C.MACHINE,                      --锁表用户的计算机名称(例如:WORKGROUP\UserName)

  C.STATUS,                       --锁表状态

  C.SERVER,

  C.SID,

  C.SERIAL#,

  C.PROGRAM                       --锁表用户所用的数据库管理工具(例如:ob9.exe)

FROM

  ALL_OBJECTS A,

  V$LOCKED_OBJECT B,

  SYS.GV_$SESSION C 

WHERE

  A.OBJECT_ID = B.OBJECT_ID

  AND B.PROCESS = C.PROCESS;




set linesize 300

set pagesize 300

select * from table(dbms_xplan.display_cursor(null, null, 'ADVANCED'));

select * from table(dbms_xplan.display_cursor('', 0, 'allstats last'));

select output from table(dbms_workload_repository.ash_report_html( dbid , inst_num , l_btime , l_etime);

select * from table(dbms_xplan.display_awr('',,null,'ADVANCED'));


EXPLAIN PLAN FOR ;


select * from table(dbms_xplan.display_cursor('', 0, 'iostats last -predicate -note'));


 /*+gather_plan_statistics ab*/


===================DB wait event监控========================

select /*+ordered*/

 sw.seq#,

 sw.event,

 s.username,

 s.OSUSER,

 s.TERMINAL,

 d.event_count,

 'SID' || s.sid,

  s.serial#,

 s.BLOCKING_SESSION,

 s.blocking_instance,

 s.machine,

 sw.p2,

 sw.P1RAW,

 sw.wait_time || 'csec',

 sw.seconds_in_wait || 'sec',

 sw.state,

 (select st.sql_id

    from v$sql st

   where s.sql_hash_value = st.hash_value

     and rownum = 1 ) sql_id,

 (select st.sql_text

    from v$sql st

   where s.sql_hash_value = st.hash_value

     and rownum = 1 ) sql_text

  from (select event, count(1) event_count

          from v$session_wait

         where state <> 'WAITED SHORT TIME'

           and event not like '%SQL*Net%'

           and event not like 'PXDeq%'

           and event not like 'rdbms ipc message'

           and event not like 'queue messages'

           and event not like

               'Streams AQ: waiting for messages in the queue'

           and event not like 'jobq slave wait'

           and event not like 'class slave wait'

           and event not like 'db file parallel write'

         group by event

        having((event = 'latch free' and count(1) > 10) or (event = 'ARCH wait on SENDREQ' and count(1) > 40) or (event = 'latch: cache buffers chains' and count(1) > 10) or (event = 'enqueue' and count(1) > 10) or (event = 'read by other session' and count(1) > 20) or (event = 'db file scattered read' and count(1) > 15) or (event = 'db file sequential read' and count(1) > 60) or (event = 'buffer busy waits' and count(1) > 10) or (event not in ('db file sequential read', 'buffer busy waits', 'db file scattered read', 'latch free', 'enqueue', 'read by other session', 'TCP Socket (KGAS)', 'ARCH wait on SENDREQ') and count(1) > 10))) d,

       v$session_wait sw,

       v$session s

 where d.event = sw.event

   and sw.sid = s.sid

 order by sw.event, s.username



 ---查询热块对象sql语句:  

SELECT *  

  FROM (SELECT O.OWNER, O.OBJECT_NAME, O.OBJECT_TYPE, SUM(TCH) TOUCHTIME,  

          FROM X$BH B, DBA_OBJECTS O  

         WHERE B.OBJ = O.DATA_OBJECT_ID  

           AND B.TS# > 0  

         GROUP BY O.OWNER, O.OBJECT_NAME, O.OBJECT_TYPE  

         ORDER BY SUM(TCH) DESC)  

 WHERE ROWNUM <= 10   ;

   

   

--查找热点块操作语句  

 SELECT /*+rule*/  

 HASH_VALUE, SQL_TEXT  

  FROM V$SQLTEXT  

 WHERE (HASH_VALUE, ADDRESS) IN  

       (SELECT A.HASH_VALUE, A.ADDRESS  

          FROM V$SQLTEXT A,  

               (SELECT DISTINCT A.OWNER, A.SEGMENT_NAME, A.SEGMENT_TYPE  

                  FROM DBA_EXTENTS A,  

                       (SELECT DBARFIL, DBABLK  

                          FROM (SELECT DBARFIL, DBABLK  

                                  FROM X$BH  

                                 ORDER BY TCH DESC)  

                         WHERE ROWNUM < 11) B  

                 WHERE A.RELATIVE_FNO = B.DBARFIL  

                   AND A.BLOCK_ID <= B.DBABLK  

                   AND A.BLOCK_ID + A.BLOCKS > B.DBABLK) B  

         WHERE A.SQL_TEXT LIKE '%' || B.SEGMENT_NAME || '%'  

           AND B.SEGMENT_TYPE = 'TABLE')  

 ORDER BY HASH_VALUE, ADDRESS, PIECE;  


 col name for a40 

 col PARAMETER1 for a15  

 col PARAMETER2 for a15  

 col PARAMETER3 for a15 

 col WAIT_CLASS for a30

select name,PARAMETER1,PARAMETER2,PARAMETER3,WAIT_CLASS from v$event_name where name like '%db file s%';

 


--查看row cache objects 的具体信息:

  SELECTaddr,

        latch#,

        child#,

        level#,

         name,

         gets,

        misses,

        sleeps

    FROMv$latch_children

   WHEREname='rowcache objects' AND gets <>0

ORDER BYgets;

  

SELECT "WHERE",sleep_count, location

  FROMv$latch_misses

 WHEREparent_name = 'row cacheobjects' ANDsleep_count > 0;

 

SELECT DISTINCTr.cache#,

                 r.parameter name,

                 r.TYPE,

                 r.subordinate#,

                 r.gets

    FROMv$rowcache r

ORDER BY1, 4,5;

 

我们可以使用如下SQL检查row cache 是否高效。 pct_succ_gets 接近与100最好,如果接近于0,就说明需要优化。

 

SELECT parameter,

       SUM(gets),

       SUM(getmisses),

       100 * SUM (gets-getmisses)/sum(gets)pct_succ_gets,

sum(modifications)updates

FROM V$ROWCACHE

WHERE gets >0

GROUP BYparameter;



SELECT  sum(VALUE) / 1024 / 1024

  FROM v$session s, v$sesstat st, v$statname sn, v$process p

  WHERE st.SID = s.SID

  AND st.statistic# = sn.statistic#

  AND sn.NAME LIKE 'session pga memory'

  AND p.addr = s.paddr

 AND VALUE > 10 * 1024 * 1024;


 

Oracle latch: row cache objects系统整体慢:可以看到到底是哪个数据字典争用严重。

col cache# head "Cache|no" form 999

col parameter head "Parameter" form a25

col type head "Type" form a12

col subordinate# head "Sub|ordi|nate" form 9999

col rcgets head "Cache|Gets" form 999999999999

col rcmisses head "Cache|Misses" form 999999999999

col rcmodifications head "Cache|Modifica|tions" form 999999999999

col rcflushes head "Cache|Flushes" form 999999999999

col kqrstcln head "Child#" form 999

col lagets head "Latch|Gets" form 999999999999

col lamisses head "Latch|Misses" form 999999999999

col laimge head "Latch|Immediate|gets" form 999999999999

select dc.kqrstcid CACHE#, dc.kqrsttxt PARAMETER, decode(dc.kqrsttyp, 1,'PARENT','SUBORDINATE') type,

decode(dc.kqrsttyp, 2, kqrstsno, null) subordinate#, 

dc.kqrstgrq rcgets, dc.kqrstgmi rcmisses, dc.kqrstmrq rcmodifications, dc.kqrstmfl rcflushes, dc.kqrstcln, 

la.gets lagets, la.misses lamisses, la.immediate_gets laimge

from x$kqrst dc,

v$latch_children la

where dc.inst_id = userenv('instance')

and la.child# = dc.kqrstcln

and la.name = 'row cache objects'

order by rcgets desc

/       



疑问:sql增加字段怎么处理

http://blog.csdn.net/tianlesoftware/article/details/6919280

 x$kcvfh

高水位线

http://blog.csdn.net/qq578473688/article/details/54561458




ORA-04030: (kxs-heap-w,kcbpioqInitClientBuffer



 依赖关系

 crsctl status res para .db   -p 

crsctl status res  serverpool.pl



 SQL> select ksppinm as "hidden parameter", ksppstvl as "value" from x$ksppi join x$ksppcv  using (indx) where ksppinm like '%_pga_max_size%' order by ksppinm;


 --------------------

http://www.cnblogs.com/xudong-bupt/p/3721210.html


http://blog.csdn.net/msdnchina/article/details/46278299

http://www.itpub.net/thread-1310824-2-1.html

http://blog.tanelpoder.com/2012/09/03/optimizer-statistics-driven-direct-path-read-decision-for-full-table-scans-_direct_read_decision_statistics_driven/

enq: KO - fast object checkpoint

-----------------------------------





select FILE# ,to_char(CHECKPOINT_CHANGE#) , LAST_CHANGE#  from  v$datafile; 

select FILE# ,to_char(CHECKPOINT_CHANGE#)  from  v$datafile_header; 

SQL> select sequence#,checkpoint_change#,last_redo_change# from v$thread;


ALTER SYSTEM SET "_system_trig_enabled"=false;


SQL> select event, wait_time_milli,wait_count

2 from v$event_histogram

3 where event = 'log file parallel write';


SELECT SQL_TEXT, EXECUTIONS

  FROM (SELECT SQL_TEXT,

               EXECUTIONS,

               RANK() OVER(ORDER BY EXECUTIONS DESC) EXEC_RANK

          FROM V$SQLAREA)

 WHERE EXEC_RANK <= 15; 

 


SELECT TO_CHAR(N.BEGIN_INTERVAL_TIME, 'YYYY-MM-DD') "DATETIME",

       m.snap_id,

       M.SQL_ID,

       m.plan_hash_value,

       M.EXECUTIONS_DELTA,

       round(m.buffer_gets_delta /m.executions_delta,3) as buffer_gets_delta ,

       round(m.elapsed_time_delta /m.executions_delta,3) as elapsed_time_delta ,  

       round(m.disk_reads_delta /m.executions_delta,3) as disk_reads_delta ,          

  FROM DBA_HIST_SQLSTAT M, DBA_HIST_SNAPSHOT N

 WHERE M.SNAP_ID = N.SNAP_ID

   AND M.DBID = N.DBID

   AND M.INSTANCE_NUMBER = N.INSTANCE_NUMBER

   AND M.INSTANCE_NUMBER = 1

   AND TO_CHAR(N.BEGIN_INTERVAL_TIME, 'YYYY-MM-DD') = '2014-11-20'

   AND M.SQL_ID = &SQL_ID

 ORDER BY M.snap_id desc;

 

SELECT M.SQL_ID ,

       TO_CHAR(N.BEGIN_INTERVAL_TIME, 'YYYY-MM-DD')   "DATETIME",

       SUM(M.EXECUTIONS_DELTA)  EXECUTIONS

FROM DBA_HIST_SQLSTAT M, DBA_HIST_SNAPSHOT N

WHERE M.SNAP_ID  = N.SNAP_ID

  AND M.DBID = N.DBID

  AND M.INSTANCE_NUMBER = N.INSTANCE_NUMBER

  AND M.INSTANCE_NUMBER=1

  AND TO_CHAR(N.BEGIN_INTERVAL_TIME, 'YYYY-MM-DD') ='2014-11-20'

  AND M.SQL_ID=&SQL_ID

GROUP BY M.SQL_ID , TO_CHAR(N.BEGIN_INTERVAL_TIME, 'YYYY-MM-DD')

ORDER BY M.SQL_ID 

 

 col USERNAME for a10

 col SPID for a8

 col PROGRAM for a20

 select p.username,p.spid,p.program,pm.category,pm.used,pm.allocated,pm.max_allocated

   from v$process p ,v$process_memory pm

   where p.pid=pm.pid

   and p.pid in

   (select addr,pid,spid,username from v$process

   where addr in (select distinct paddr from v$session where username is not null));

 


--sql stat in snapshot

select t.snap_id,

       to_char(t.begin_interval_time, 'yyyy-mm-dd hh24:mi:ss') begin_interval_time,

       s.sql_id,

       cpu_time_delta cpu_time,

       round(cpu_time_delta/decode(executions_delta,0,1,executions_delta)) avg_cpu_time,

       executions_delta executions,

       elapsed_time_delta elapsed_time,

       round(elapsed_time_delta/decode(executions_delta,0,1,executions_delta)) avg_elapsed_time,

       buffer_gets_delta buffer_gets,

       round(buffer_gets_delta/decode(executions_delta,0,1,executions_delta)) avg_buffer_gets,

       s.rows_processed_delta,

       disk_reads_delta disk_reads,

       fetches_delta fetches,

       sorts_delta sorts,

       s.plan_hash_value

  from dba_hist_sqlstat s, dba_hist_snapshot t

where s.snap_id = t.snap_id

   and t.begin_interval_time 

   between to_date('', 'yyyy-mm-dd hh24:mi:ss') 

     and to_date('', 'yyyy-mm-dd hh24:mi:ss') 

   and sql_id = ''

--   and plan_hash_value in (1101836845, 2899593734)

   order by s.snap_id desc



select end_time, process from (select to_char(trunc(end_time,'mi'), 'YYYY-MM-DD HH24:MI:SS') end_time,

round(value*limit_value/100) process from v$sysmetric_history,(select limit_value from v$resource_limit where resource_name = 'processes') where metric_name = 'Process Limit %');


select end_time, current_process, process_value, used_pct

  from (select to_char(trunc(end_time, 'mi'), 'YYYY-MM-DD HH24:MI:SS') end_time,

               round(value * limit_value / 100) current_process,

               limit_value process_value,

               round(value, 2) || '%' used_pct

          from v$sysmetric_history,

               (select limit_value

                  from v$resource_limit

                 where resource_name = 'processes')

         where metric_name = 'Process Limit %')

 order by end_time desc



-- unlock user's statistic

SPOOL ./script/unlock_user_statistic.sql

  SELECT 'spool ./log/unlock_user_statistic.log' FROM dual;

  SELECT distinct 'exec dbms_stats.unlock_schema_stats (ownname => '''||owner||''');'

    FROM dba_tab_statistics

   WHERE owner in (select owner from t_user_cfg);

  SELECT 'spool off' FROM dual;

SPOOL OFF


  SELECT distinct 'exec dbms_stats.lock_table_stats (ownname => '''||owner||''', tabname => '''||table_name||''');'


SELECT   /*+ rule */

         s.username

       , s.logon_time

       , s.sid

       , s.serial#

       , s.status

       , s.username

       , s.osuser

       , s.machine

       , DECODE (l.lmode, 0, 'lock waiter', 'lock holder') resp

       , s.program

       , s.module

       , s.sql_address

    FROM v$session s

       , v$lock l

   WHERE l.id1 IN (SELECT c.id1

                     FROM v$lock c

                    WHERE c.lmode = 0)

     AND l.sid = s.sid

ORDER BY l.id1

       , l.BLOCK DESC;


 select l.inst_id,decode(request, 0, 'holder:', 'waiter:') || l.sid holder_msg,

           s.username,

           s.osuser,

           s.machine,

           s.status,

           s.sql_id,

           s.prev_sql_id,

           q.sql_text,

           q2.sql_text pre_sql_text,

           id1,

           id2,

           lmode,

           request,

           l.type,

           ctime

      from gv$lock l left outer join gv$session s on (l.inst_id=s.inst_id and l.sid = s.sid)

           left outer join gv$sql q on (s.inst_id=q.inst_id and s.sql_id=q.sql_id)

           left outer join gv$sql q2 on (s.inst_id=q2.inst_id and s.prev_sql_id=q2.sql_id)    

     where l.type in ('TX','TM')

       and (request <> 0 or block <> 0 )

       and ctime > 600

     order by inst_id,id1, request;




SELECT s.username,

       s.logon_time,

       s.sid,

       s.serial#,

           s.status,

       s.username,

       s.osuser,

       s.machine,

       DECODE (l.lmode, 0, 'lock waiter', 'lock holder') resp,

       s.program,

           s.module,

       a.sql_text

  FROM v$session s, v$sqlarea a, v$lock l

WHERE l.id1 IN (SELECT id1

                   FROM v$lock

                  WHERE lmode = 0)

   AND l.sid = s.sid

   AND s.sql_address = a.address

   ORDER BY l.id1,BLOCK DESC



undo表空间中常用的操作:


(1)查看undo表空间undotbs1的属性:

SQL> select b.tablespace_name,AUTOEXTENSIBLE,RETENTION  from dba_tablespaces a,dba_data_files b 

         where a.TABLESPACE_NAME=b.TABLESPACE_NAME and b.TABLESPACE_NAME='UNDOTBS1';


TABLESPACE_NAME  AUT  RETENTION

----------------------     ---   ----------------

UNDOTBS1               NO  NOGUARANTEE


(2)查看各个回退段的使用信息:

select a.name,b.extents,b.rssize,b.writes,b.xacts,b.wraps

from v$rollname a,v$rollstat b where a.usn=b.usn;


(3)确定哪些用户正在使用undo段:

SQL> select a.username,b.name,c.used_ublk from v$session a,v$rollname b,v$transaction c

         where a.saddr=c.ses_addr and b.usn=c.xidusn; 


USERNAME  NAME                USED_UBLK

----------      -----------------------          ----------

NDMC       _SYSSMU1_1255220753$       1

NDMC       _SYSSMU5_1255220754$       1


(4)每秒生成的UNDO量,可以通过如下SQL得出:

SQL> SELECT (SUM(undoblks))/ SUM((end_time - begin_time) * 86400) FROM v$undostat;


(SUM(UNDOBLKS))/SUM((END_TIME-BEGIN_TIME)*86400)

--------------------------------------------------------------

          7.97590055

或者可以看下AWR报告中的Undo Statistics部分。


(5)当前undo表空间使用状态:

SQL> SELECT DISTINCT STATUS,SUM(BYTES),COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS;


STATUS  SUM(BYTES)   COUNT(*)

---------             ----------------   ----------

UNEXPIRED        3225157632   5667

EXPIRED            1063518208   1588

ACTIVE             1048576        1


(6)查看活动事务v$transaction

SQL> SELECT A.SID, A.USERNAME, B.XIDUSN, B.USED_UREC, B.USED_UBLK  

         FROM V$SESSION A, V$TRANSACTION B WHERE A.SADDR=B.SES_ADDR;


       SID  USERNAME   XIDUSN    USED_UREC USED_UBLK

---------- ------------ ---------- ---------- ----------

       407   NDMC       15         3        1


SQL> SELECT XID AS "txn_id", XIDUSN AS "undo_seg", USED_UBLK "used_undo_blocks",

                     XIDSLOT AS "slot", XIDSQN AS "seq", STATUS AS "txn_status" 

         FROM V$TRANSACTION;


txn_id         undo_seg   used_undo_blocks    slot      seq     txn_status

---------------- ---------- ---------------- ---------- --------- -------------

14001600733A0C00    20           1             22      801395      ACTIVE


 

1)检查long operation

SQL> select sid, target,opname, sofar,totalwork,time_remaining

       from v$session_longops where time_remaining>0;

2)检查占用回滚段较多的事务:

SQL> select xidusn,s.sid,s.last_call_et, s.username, used_ublk,USED_UREC,space,

            RECURSIVE, NOUNDO, START_UBAFIL,start_ubablk, start_uext

       from v$transaction t, v$session s

      where t.ses_addr=s.saddr;



sid and undo:


 select xidusn,s.sid,s.last_call_et, s.username, used_ublk,USED_UREC,space,

            RECURSIVE, NOUNDO, START_UBAFIL,start_ubablk, start_uext

       from v$transaction t, v$session s

      where t.ses_addr=s.saddr;  

 

 

 如果想查询某一个具体的隐含参数的值,只需要在上面原基础上加上 and x.ksppinm ='XXXXX'  过滤。 


例如查询 _gc_undo_affinity 。


col name for a30;

col value for a10;

select

x.ksppinm  name,

y.ksppstvl  value,

y.ksppstdf  isdefault,

decode(bitand(y.ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE')  ismod,

decode(bitand(y.ksppstvf,2),2,'TRUE','FALSE')  isadj

from

sys.x$ksppi x,

sys.x$ksppcv y

where

x.inst_id = userenv('Instance') and

y.inst_id = userenv('Instance') and

x.indx = y.indx and x.ksppinm like '%_undo_%'

order by

translate(x.ksppinm, ' _', ' ')

/

这个语句可以查出来哪些SQL占了比较大的 shared pool ,

并生成将该 SQL 移出 shared pool 的语句(需 sys 才能执行)


Shared pool free size 很小时,不要清理 shared pool ,

只可有选择性的将一些非绑定变量语句移出去。



     select sql_id,sql_text,sql_fulltext,executions,round(sharable_mem/1024/1024,2) sharmemo_used_mb,

           last_active_time,parsing_schema_name,s.service,

           'exec  dbms_shared_pool.purge('''||address||','||hash_value||''',''c'');' remove_sql

      from v$sql s 

     where s.sharable_mem > 1 * 1024 * 1024 

     order by s.sharable_mem desc;

      


    select inst_id,pool,name,round(sum(bytes)/1024/1024,2) size_mb from gv$sgastat where pool='shared pool' and name='free memory' group by inst_id,pool,name;


----cpu_time

select SUBSTR(TO_CHAR(begin_time, 'HH:MI' ),1,4) || '0' as BEGIN_TIME,

       AVG(value) as avg_VALUE,

       min(value) as min_VALUE,

       max(value) as max_VALUE,

       count(*)

  from V$SYSMETRIC_HISTORY

 where metric_name= 'Host CPU Utilization (%)'

 group by SUBSTR(TO_CHAR(begin_time, 'HH:MI' ),1,4)

 order by 1;

1、在source database 创建staging table


BEGIN

DBMS_SPM.CREATE_STGTAB_BASELINE(

   table_name      =>'chad_spm_migrate',

   table_owner     => 'DBMGR',

   tablespace_name => 'USERS');

END;



2、将SQL Planbaselines 导入staging table  ----(根据sql_handle与PLAN_NAME进行导出)


DECLARE

l_plans_packed  PLS_INTEGER;

BEGIN

l_plans_packed := DBMS_SPM.pack_stgtab_baseline(

   table_name      =>'chad_spm_migrate',

   table_owner     => 'DBMGR',

   sql_handle      => 'SQL_43aa553e72757e01',

   plan_name       =>'SQL_PLAN_47akp7tt7azh16a45e050');


 DBMS_OUTPUT.put_line('Plans Packed: ' || l_plans_packed);

END;


 

3、将chad_spm_migrate传输到目标库,可以使用exp/imp ;



4、 将chad_spm_migrate导入目标库 

SET SERVEROUTPUT ON

DECLARE

l_plans_unpacked  PLS_INTEGER;

BEGIN

l_plans_unpacked := DBMS_SPM.unpack_stgtab_baseline(

   table_name      =>'chad_spm_migrate',

   table_owner     => 'DBMGR',

   sql_handle      => 'SQL_43aa553e72757e01',

   plan_name       =>'SQL_PLAN_47akp7tt7azh16a45e050'

   );

DBMS_OUTPUT.put_line('Plans Unpacked: ' || l_plans_unpacked);

END;

/

select sysdate,

       se.username,

       se.sid,

       se.serial#,

       se.status,

       se.machine,

       se.osuser,

       round(st.value / 1024 / 1024) redosize_MB,

       sa.sql_text

  from v$session se, v$sesstat st, v$sqlarea sa

where se.sid = st.sid

   and st.STATISTIC# =

       (select STATISTIC# from v$statname where NAME = 'redo size')

      --and se.username is not null

   and st.value > 10 * 1024 * 1024

   and se.SQL_ADDRESS = sa.ADDRESS

   and se.SQL_HASH_VALUE = sa.HASH_VALUE

order by redosize_MB desc;


select t1.sid,

       t1.REDO_GB,

       t2.status,

       t2.serial#,

       t2.username,

       t2.OSUSER,

       t2.MODULE,

       t2.event,

       t2.last_call_et,

       t2.machine

  from (select m.sid, round(m.value / 1024 / 1024 / 1024, 6) REDO_GB

          from v$sesstat m, v$statname s

         where m.statistic# = s.statistic#

           and s.name = 'redo size'

         order by m.value desc) t1,

       v$session t2

where t1.sid = t2.sid

   and rownum < 10;


 

------DB BLOCK CHANGE情况

SELECT to_char(begin_interval_time,'YY-MM-DD HH24') snap_time,

        dhso.object_name,

        sum(db_block_changes_delta) BLOCK_CHANGED

  FROM dba_hist_seg_stat dhss,

       dba_hist_seg_stat_obj dhso,

       dba_hist_snapshot dhs

  WHERE dhs.snap_id = dhss.snap_id

    AND dhs.instance_number = dhss.instance_number

    AND dhss.obj# = dhso.obj#

    AND dhss.dataobj# = dhso.dataobj#

    AND begin_interval_time BETWEEN to_date('2018-03-09 10:00','YY-MM-DD HH24:MI')

                                AND to_date('2018-03-09 14:00','YY-MM-DD HH24:MI')

    and dhss.TS#=2

  GROUP BY to_char(begin_interval_time,'YY-MM-DD HH24'),

           dhso.object_name

  HAVING sum(db_block_changes_delta) > 0

ORDER BY sum(db_block_changes_delta) desc ;



select

   s.sid,s.serial#,    s.username,s.module,s.status,s.last_call_et,

   --r.name       "RBS name",

   t.start_time --,

   --t.used_ublk  "Undo blocks",

   --t.used_urec  "Undo recs"

from v$session s, v$transaction t, v$rollname r

where t.addr = s.taddr and r.usn  = t.xidusn

order by t.start_time desc;







--DB CPU/time

with t as

(select max(t.snap_id) over() max_snap_id,

         t.snap_id,

         t.stat_name,

         ((max(value)

          over(partition by t.stat_name order by t.snap_id

                rows BETWEEN unbounded preceding AND unbounded following)) -

         (min(value)

          over(partition by t.stat_name order by t.snap_id

                rows BETWEEN unbounded preceding AND unbounded following))) value

    from dba_hist_sys_time_model t, dba_hist_snapshot snap

   where t.stat_name in ('DB CPU', 'DB time')

     and t.snap_id = snap.snap_id

     and snap.end_interval_time between

       to_date('&begin_time', 'YYYY-MM-DD HH24:MI:SS') and

       to_date('&end_time', 'YYYY-MM-DD HH24:MI:SS')

  )

select t.stat_name,

       decode(t.stat_name,

              'DB CPU',

              round(t.value / 1000000 / 60, 2),

              'DB time',

              round(t.value / 1000000 / 60, 2),

              0) time

  from t

where t.snap_id = t.max_snap_id;






--buffer hit

with r as

(select max(s.snap_id) over() max_snap_id,

         s.stat_name,

         s.snap_id,

         ((max(value)

          over(partition by s.stat_name order by s.snap_id

                rows BETWEEN unbounded preceding AND unbounded following)) -

         (min(value)

          over(partition by s.stat_name order by s.snap_id

                rows BETWEEN unbounded preceding AND unbounded following))) value

    from dba_hist_sysstat s, dba_hist_snapshot snap

   where s.snap_id = snap.snap_id

     and s.stat_name in

         ('physical reads direct', 'physical reads', 'session logical reads')

     and snap.end_interval_time between

       to_date('&begin_time', 'YYYY-MM-DD HH24:MI:SS') and

       to_date('&end_time', 'YYYY-MM-DD HH24:MI:SS')),

rr as

(select max(decode(r.stat_name, 'session logical reads', r.value, 0)) lr,

         max(decode(r.stat_name, 'physical reads', r.value, 0)) pr,

         max(decode(r.stat_name, 'physical reads direct', r.value, 0)) prd

    from r

   where r.snap_id = r.max_snap_id)

select round(rr.pr / 3600, 2) physical_reads_ps,

       round((1 - (rr.pr - rr.prd) / rr.lr) * 100, 2) buffer_hit

  from rr;

  

  

  

--event1

  select 

max(sum(nvl(case

               when event = 'log file sync' then

                round(times, 2)

             end,

            0))) as "log file sync",

max(sum(nvl(case

               when event = 'log file parallel write' then

                round(times, 2)

             end,

             0))) as "log file parallel write",

             

max(sum(nvl(case

               when event = 'db file sequential read' then

                round(times, 2)

             end,

             0))) as "db file sequential read",

max(sum(nvl(case

               when event = 'db file parallel write' then

                round(times, 2)

             end,

             0))) as "db file parallel write",


max(sum(nvl(case

               when event = 'direct path read' then

                round(times, 2)

             end,

             0))) as "direct path read"


  from (select t.snap_id,

               to_char(b.begin_interval_time, 'yyyy-mm-dd hh24:mi:ss') stime,

               event_name event,

               decode(sign(total_waits - lag(total_waits, 1)

                           over(partition by event_name order by t.snap_id)),

                      -1,

                      (lag(time_waited_micro, 1)

                       over(partition by event_name order by t.snap_id)) /

                      (lag(total_waits, 1)

                       over(partition by t.instance_number,

                            event_name order by t.snap_id)),

                      0,

                      0,

                      (time_waited_micro - lag(time_waited_micro, 1)

                       over(partition by event_name order by t.snap_id)) /

                     (total_waits - lag(total_waits, 1)

                       over(partition by event_name order by t.snap_id))) / 1000 as times

          from dba_hist_system_event t, dba_hist_snapshot b

         where t.snap_id = b.snap_id

           and b.begin_interval_time between

               to_date('&begin_time', 'YYYY-MM-DD HH24:MI:SS') and

               to_date('&end_time', 'YYYY-MM-DD HH24:MI:SS')

           and b.instance_number = t.instance_number

           and event_name in ('db file sequential read',

                              'log file sync',

                              'direct path read',

                              'log file parallel write',

                              'db file parallel write')

           and t.instance_number = 1)

where times > 0

group by snap_id, stime

order by snap_id;


select t.snap_id,

               to_char(b.begin_interval_time, 'yyyy-mm-dd hh24:mi:ss') stime,

               event_name event,

               decode(sign(total_waits - lag(total_waits, 1)

                           over(partition by event_name order by t.snap_id)),

                      -1,

                      (lag(time_waited_micro, 1)

                       over(partition by event_name order by t.snap_id)) /

                      (lag(total_waits, 1)

                       over(partition by t.instance_number,

                            event_name order by t.snap_id)),

                      0,

                      0,

                      (time_waited_micro - lag(time_waited_micro, 1)

                       over(partition by event_name order by t.snap_id)) /

                     (total_waits - lag(total_waits, 1)

                       over(partition by event_name order by t.snap_id))) / 1000 as times

          from dba_hist_system_event t, dba_hist_snapshot b

         where t.snap_id = b.snap_id

           and b.begin_interval_time between

               to_date('&begin_time', 'YYYY-MM-DD HH24:MI:SS') and

               to_date('&end_time', 'YYYY-MM-DD HH24:MI:SS')

           and b.instance_number = t.instance_number

           and event_name in ('db file sequential read',

                              'log file sync',

                              'direct path read',

                              'log file parallel write',

                              'db file parallel write')

           and t.instance_number = 1

  

--event2 for latch and enq

select event,times from (

select event,round(avg(times),2) times from 

 (

select t.snap_id,

               to_char(b.begin_interval_time, 'yyyy-mm-dd hh24:mi:ss') stime,

               event_name event,

               decode(sign(total_waits - lag(total_waits, 1)

                           over(partition by event_name order by t.snap_id)),

                      -1,

                      (lag(time_waited_micro, 1)

                       over(partition by event_name order by t.snap_id)) /

                      (lag(total_waits, 1)

                       over(partition by t.instance_number,

                            event_name order by t.snap_id)),

                      0,

                      0,

                      (time_waited_micro - lag(time_waited_micro, 1)

                       over(partition by event_name order by t.snap_id)) /

                      (total_waits - lag(total_waits, 1)

                       over(partition by event_name order by t.snap_id))) / 1000 as times

          from dba_hist_system_event t, dba_hist_snapshot b

         where t.snap_id = b.snap_id

           and b.begin_interval_time between

               to_date('&begin_time', 'YYYY-MM-DD HH24:MI:SS') and

               to_date('&end_time', 'YYYY-MM-DD HH24:MI:SS')

           and b.instance_number = t.instance_number

           and (event_name like'%enq%' or event_name like'%latch%')

           and t.instance_number = 1 )

           where times is not null

           and times <>0

           group by event

           order by 2 desc) where times<>0;



--maximum PGA allocated

select ROUND(max(value) / 1024 / 1024 / 1024, 2) PGA_G

  from dba_hist_pgastat p, dba_hist_snapshot s

where name = 'maximum PGA allocated'

   and s.end_interval_time between

       to_date('&begin_time', 'YYYY-MM-DD HH24:MI:SS') and

       to_date('&end_time', 'YYYY-MM-DD HH24:MI:SS')

   and p.snap_id = s.snap_id;

   

   

--fra

select b.TOTAL_G,

       b."TOTAL_G" * (1 - a."USED") "FREE_G",

       b."TOTAL_G" * (1 - a."USED" + a."RECLAIMABLE") "free+reclaimable_G",

       round((a."USED") * 100 , 2) || ' %' as pct_used

  from (select sum(xx.PERCENT_SPACE_USED) / 100 "USED",

               sum(xx.PERCENT_SPACE_RECLAIMABLE) / 100 "RECLAIMABLE"

          from v$flash_recovery_area_usage xx) a,

       (select round(value / 1024 / 1024 / 1024) "TOTAL_G"

          from v$parameter

         where name = 'db_recovery_file_dest_size') b;




---undostat

select to_char(begin_time, 'yyyymmdd hh24'),

       sum(trunc(undoblks * 8192 / 1024 / 1024))

  from v$undostat

where begin_time between trunc(sysdate - 1 / 24, 'hh24') and

       trunc(sysdate, 'hh24')

group by to_char(begin_time, 'yyyymmdd hh24'); 



--hard parse


with t1 as

(

select s.instance_number,s.snap_id,to_char(t.begin_interval_time,'yyyy/mm/dd hh24:mi:ss') time,stat_name,value from dba_hist_sysstat s ,dba_hist_snapshot t

where 

 s.instance_number=1 

 and s.stat_name='parse count (hard)'

and s.snap_id=t.snap_id and t.instance_number=1

order by 1 desc

),

t2 as 

(

select s.instance_number,s.snap_id,to_char(t.begin_interval_time,'yyyy/mm/dd hh24:mi:ss') time,stat_name,value from dba_hist_sysstat s ,dba_hist_snapshot t

where 

  s.instance_number=1

and s.stat_name='parse count (hard)'

and s.snap_id=t.snap_id and t.instance_number=1

order by 1 desc

)


select t1.instance_number,t1.snap_id,t1.time,t1.stat_name,(t1.value-t2.value) value from t1,t2 where t1.snap_id=t2.snap_id+1

order by t1.snap_id desc


select a.tablespace_name,sum(bytes)/1024/1024/1024 from dba_segments a,dba_lobs b

where a.tablespace_name='USERS' and a.segment_name= b.segment_name

and b.owner='AUDSYS'

group by a.tablespace_name;



 

select a.snap_id,

       b.begin_interval_time,

       rows_processed_delta / executions_delta ROWS_PROCESSED_PER_EXECTIONS,

       a.sql_id,

       c.sql_text,

       a.parsing_schema_name,

       executions_delta,

       decode(c.command_type,

              2,

              'INSERT',

              6,

              'UPDATE',

              7,

              'DELETE',

              189,

              'MERGE') "DML_TYPE"

  from dba_hist_sqlstat a, dba_hist_snapshot b, dba_hist_sqltext c

 where a.snap_id = b.snap_id

   and a.sql_id = c.sql_id(+)

   and executions_delta <> 0

   and c.command_type in (2, 6, 7, 189)

   and rows_processed_delta / executions_delta >= 10000

   and a.snap_id between 68050 and 68056

 order by ROWS_PROCESSED_PER_EXECTIONS desc

 

 

 

 select SNAP_ID,OWNER ,OBJECT_NAME, OBJECT_TYPE, DB_BLOCK_CHANGES_DELTA

from dba_objects a,

(select SNAP_ID,obj#,DB_BLOCK_CHANGES_DELTA from DBA_HIST_SEG_STAT where DB_BLOCK_CHANGES_DELTA > 20000 order by snap_id desc, DB_BLOCK_CHANGES_DELTA desc) b

where a.object_id=b.obj# and object_type='TABLE'

order by snap_id desc, DB_BLOCK_CHANGES_DELTA desc


select c.SNAP_ID,c.END_INTERVAL_TIME SNAP_TIME,a.OWNER ,a.OBJECT_NAME, a.OBJECT_TYPE, b.DB_BLOCK_CHANGES_DELTA

from dba_objects a,

(select SNAP_ID,obj#,DB_BLOCK_CHANGES_DELTA from DBA_HIST_SEG_STAT where DB_BLOCK_CHANGES_DELTA > 20000 order by snap_id desc, DB_BLOCK_CHANGES_DELTA desc) b,

DBA_HIST_SNAPSHOT c

where a.object_id=b.obj# and object_type='TABLE' and b.SNAP_ID=c.SNAP_ID

order by snap_id desc, DB_BLOCK_CHANGES_DELTA desc

 

 

 

SELECT SE.SID,

OPNAME,

TRUNC(SOFAR / TOTALWORK * 100, 2) || '%' AS PCT_WORK,

ELAPSED_SECONDS ELAPSED,

ROUND(ELAPSED_SECONDS * (TOTALWORK - SOFAR) / SOFAR) REMAIN_TIME

FROM V$SESSION_LONGOPS SL, V$SQLAREA SA, V$SESSION SE

WHERE SL.SQL_PLAN_HASH_VALUE = SA.PLAN_HASH_VALUE

AND SL.SID = SE.SID

AND SOFAR != TOTALWORK

and sl.SID='1920'

ORDER BY START_TIME;

 

 

select end_time, process from (select to_char(trunc(end_time,'mi'), 'YYYY-MM-DD HH24:MI:SS') end_time,

                   2   round(value*limit_value/100) process from v$sysmetric_history,(select limit_value from v$resource_limit where resource_name = 'processes') where metric_name = 'Process Limit %');



------cpu_time

select * from (select round(nvl((sqt.cput / 1000000), to_number(null)),2) "Cpu Time (s)",

              round( nvl((sqt.elap / 1000000), to_number(null)),2) "Elap Time (s)",

               sqt.exec,

               round(decode(sqt.exec,0,to_number(null),(sqt.cput / sqt.exec / 1000000)),2) "Cpu per Exec (s)",

               round((100 * (sqt.elap / (select sum(e.value) - sum(b.value)

                               from dba_hist_sys_time_model b,

                                    dba_hist_sys_time_model e

                              where b.snap_id = 21890 and

                                    e.snap_id = 21891 and

                                    e.stat_name = 'DB time' and

                                    b.stat_name = 'DB time'))) ,2)norm_val,

               sqt.sql_id,

               decode(sqt.module, null, null, 'Module: ' || sqt.module) SqlModule,

               nvl(to_nchar(SUBSTR(st.sql_text,1,2000)) , (' ** SQL Text Not Available ** ')) SqlText

          from (select sql_id,

                       max(module) module,

                       sum(cpu_time_delta) cput,

                       sum(elapsed_time_delta) elap,

                       sum(executions_delta) exec

                  from dba_hist_sqlstat

                 where 21890 < snap_id and

                       snap_id <= 21891

                 group by sql_id) sqt,

               dba_hist_sqltext st

         where st.sql_id(+) = sqt.sql_id

         order by nvl(sqt.cput, -1) desc,

                  sqt.sql_id)

 where rownum < 65 and

       (rownum <= 10 or norm_val > 1)



select se.USERNAME,se.SID,se.SERIAL#,se.STATUS,SE.EVENT,SE.P1,SE.P1TEXT,

       round(decode(tr.USED_UBLK,null,0,tr.USED_UBLK) * (select value from v$parameter where NAME='db_block_size') /1024/1024/1024,4)  as undo_size_gb ,

       tr.USED_UREC,se.SQL_ID,s.SQL_TEXT

from gv$session se, gv$transaction tr,gv$sql s

where se.TADDR=tr.ADDR(+)

and se.SQL_ID = s.SQL_ID(+)

order by undo_size_gb desc




select

ds.segment_name "Object Name",

round(sum(space_allocated_total) / 1024 / 1024) "Growth (MB)" ,

round(sum(space_used_delta)/1024/1204)

from

dba_hist_snapshot dhs,

dba_hist_seg_stat dhss,

dba_objects db,

dba_segments ds

where begin_interval_time > trunc(sysdate) - &days_back

and dhs.snap_id = dhss.snap_id

and db.object_id = dhss.obj#

and db.owner = ds.owner

and db.object_name = ds.segment_name

group by ds.segment_name

having sum(space_allocated_delta) / 1024 / 1024 >5000

order by 3 desc





1. 查看数据库具体时间点的Block change TOP N,这个AWR中也有

select to_char(begin_interval_time, 'yyyy-mm-dd hh24:mi') snap_time,

       sto.object_name,

       sum(db_block_changes_delta)

  from dba_hist_seg_stat     st,

       dba_hist_seg_stat_obj sto,

       dba_hist_snapshot     ss

where ss.snap_id = st.snap_id

   and ss.instance_number = st.instance_number

   and st.obj# = sto.obj#

   and st.dataobj# = sto.dataobj#

   and begin_interval_time > sysdate - 1 / 24

group by to_char(begin_interval_time, 'yyyy-mm-dd hh24:mi'),

          sto.object_name

order by 3 desc;


2. 查看对应时间的sqlstat,这里边对应的sql一般都会是引起日志突增的语句,因为执行次数,以及rows processed比其他sql要高。这个AWR中也会有。

select to_char(begin_interval_time, 'yyyy_mm_dd hh24:mi'),

       dbms_lob.substr(sql_text, 4000, 1),

       sst.instance_number,

       sst.sql_id,

       executions_delta,

       rows_processed_delta

  from dba_hist_sqlstat sst, dba_hist_snapshot ss, dba_hist_sqltext st

where upper(st.sql_text) like '%JSMX_KC22_02%'

   and sst.snap_id = ss.snap_id

   and sst.instance_number = ss.instance_number

   and sst.sql_id = st.sql_id;


3. 我们还可以进一步在分析数据库代码,帮助开发解决问题。这里使用ash试图,11g以上有top_level_sql_id,这个表示对于递归SQL,捕获其父SQL的信息。一般对定位package/function/trigger等引起的异常非常有用。

当top_level_sql_id为package时,我们需要进一步分析是哪一段代码异常,是否是逻辑错误,重复更新等问题。这里就不在讨论了,具体应用场景不同,相信大家都有很厉害的PLSQL技能。

本次异常可以看到是kettle推数和运营采集数据导致的。联系DA或者直接找开发看看采数是否每月常规还是异常,是否需要扩容FRA。

select sql_id, s.top_level_sql_id,u.username

  from v$active_session_history s,dba_users u

where s.sql_id = '4qappnkgxtrtj'

and s.user_id=u.user_id;

   

v$sga_resize_op   


select *

  from (select t.sample_time,

               s.PARSING_SCHEMA_NAME,

               t.sql_id,

               t.sql_child_number as sql_child,

               round(t.temp_space_allocated / 1024 / 1024 / 1024, 2) || ' G' as temp_used,

               round(t.temp_space_allocated /

                     (select sum(decode(d.autoextensible, 'YES', d.maxbytes, d.bytes))

                        from dba_temp_files d),

                     2) * 100 || ' %' as temp_pct,

               t.program,

               t.module,

               s.SQL_TEXT

          from v$active_session_history t, v$sql s

         where t.sample_time > to_date('&begin_time', 'yyyy-mm-dd hh24:mi:ss')

           and t.sample_time < to_date('&end_time', 'yyyy-mm-dd hh24:mi:ss')

           and t.temp_space_allocated is not null

           and t.sql_id = s.SQL_ID

         order by t.temp_space_allocated desc)

 where rownum < 50

 order by temp_used desc;



 

在标黄处选择owner,或者去掉选择全库。

这个脚本将最多处理8列外键约束(如果你的外键有更多的列,可能就得重新考虑一下你的设计了)

select table_name, constraint_name,

cname1 || nvl2(cname2,','||cname2,null) ||

nvl2(cname3,','||cname3,null) || nvl2(cname4,','||cname4,null) ||

nvl2(cname5,','||cname5,null) || nvl2(cname6,','||cname6,null) ||

nvl2(cname7,','||cname7,null) || nvl2(cname8,','||cname8,null)

columns

from ( select b.table_name,

b.constraint_name,

max(decode( position, 1, column_name, null )) cname1,

max(decode( position, 2, column_name, null )) cname2,

max(decode( position, 3, column_name, null )) cname3,

max(decode( position, 4, column_name, null )) cname4,

max(decode( position, 5, column_name, null )) cname5,

max(decode( position, 6, column_name, null )) cname6,

max(decode( position, 7, column_name, null )) cname7,

max(decode( position, 8, column_name, null )) cname8,

count(*) col_cnt

from (select substr(table_name,1,30) table_name,

substr(constraint_name,1,30) constraint_name,

substr(column_name,1,30) column_name,

position

from dba_cons_columns ) a,

dba_constraints b

where a.constraint_name = b.constraint_name

and b.constraint_type = 'R'

and b.owner='PASDATA'

group by b.table_name, b.constraint_name

) cons

where col_cnt > ALL

( select count(*)

from dba_ind_columns i

where i.table_name = cons.table_name

and i.column_name in (cname1, cname2, cname3, cname4,

cname5, cname6, cname7, cname8 )

and i.column_position <= cons.col_cnt

group by i.index_name

) ;

 

 

 

SELECT  SES.sid,

        SES.serial#,

        SES.process,

        SES.username,

        SES.command,

        SES.osuser,

        SES.machine,

        SES.program,

        SES.sql_id,

        SES.sql_exec_id,

        SES.sql_exec_start,

        SES.plsql_entry_object_id,

        SES.sql_child_number,

        SES.module,

        SES.action,

        SES.client_info,

        

        SES.row_wait_obj#,

        SES.logon_time,

        SES.resource_consumer_group,

        SES.event,

        SES.p1text,

        SES.p1,

        SES.wait_class,

        SES.wait_time,

        SES.wait_time_micro,

        SE.time_waited_micro,

        SES.state,

        SES.time_remaining_micro,

        SES.time_since_last_wait_micro,

        SES.service_name,

        STAT.cpu_time,

        GV_INST.INSTANCE_NAME blck_instance_name,

        BLCK.sid blck_sid,

        BLCK.serial# blck_serial#,

        BLCK.logon_time blck_logon_time,

        BLCK.machine blck_machine,

        BLCK.program blck_program,

        BLCK.USERNAME blck_username

FROM V$SESSION SES

JOIN v$session_event SE

ON (SES.EVENT=SE.EVENT and SES.SID=SE.SID and SES.WAIT_CLASS#=SE.WAIT_CLASS#)

LEFT JOIN GV$INSTANCE GV_INST

ON (SES.BLOCKING_INSTANCE = GV_INST.INSTANCE_NUMBER)

JOIN (

  SELECT sid, VALUE cpu_time

  FROM V$SESSTAT JOIN V$STATNAME USING (STATISTIC#)

  WHERE name = 'CPU used by this session'

) STAT

ON (SES.sid = STAT.sid)

LEFT JOIN (

  SELECT  inst_id,

          sid,

          serial#,

          logon_time,

          machine,

          program,

          username

  FROM GV$SESSION

) BLCK

ON (SES.blocking_session = blck.sid AND SES.blocking_instance = blck.inst_id)

WHERE SES.STATUS = 'ACTIVE'

AND SES.sql_id IS NOT NULL

AND rownum <= 20

 AND (SES.client_info not like '%Oracle-SPI%' OR client_info IS NULL)  AND TYPE = 'USER' 

 

  

 

 

 

 

 ========db stats===========

 with t as

                         (select max(t.snap_id) over() max_snap_id,

                                 t.snap_id,

                                 t.stat_name,

                                 ((max(value)

                                  over(partition by t.stat_name order by t.snap_id

                                        rows BETWEEN unbounded preceding AND unbounded following)) -

                                 (min(value)

                                  over(partition by t.stat_name order by t.snap_id

                                        rows BETWEEN unbounded preceding AND unbounded following))) value

                            from dba_hist_sys_time_model t, dba_hist_snapshot snap

                           where t.stat_name in ('DB CPU', 'DB time')

                             and t.snap_id = snap.snap_id

                             and snap.end_interval_time > sysdate - 1 / 24)

                        select t.stat_name,

                               decode(t.stat_name,

                                      'DB CPU',

                                      round(t.value / 1000000),

                                      'DB time',

                                      round(t.value / 1000000 / 60, 2),

                                      0) time

                          from t

                         where t.snap_id = t.max_snap_id

                        union

                        select 'physical_reads_psround' as name, round(rr.pr / 3600, 2) valuse

                          from (select max(decode(r.stat_name, 'session logical reads', r.value, 0)) lr,

                                       max(decode(r.stat_name, 'physical reads', r.value, 0)) pr,

                                       max(decode(r.stat_name, 'physical reads direct', r.value, 0)) prd

                                  from ((select max(s.snap_id) over() max_snap_id,

                                                s.stat_name,

                                                s.snap_id,

                                                ((max(value)

                                                 over(partition by s.stat_name order by s.snap_id

                                                       rows BETWEEN unbounded preceding AND unbounded

                                                       following)) -

                                                (min(value)

                                                 over(partition by s.stat_name order by s.snap_id

                                                       rows BETWEEN unbounded preceding AND unbounded

                                                       following))) value

                                           from dba_hist_sysstat s, dba_hist_snapshot snap

                                          where s.snap_id = snap.snap_id

                                            and s.stat_name in

                                                ('physical reads direct',

                                                 'physical reads',

                                                 'session logical reads')

                                            and snap.end_interval_time > sysdate - 1 / 24)) r

                                 where r.snap_id = r.max_snap_id) rr

                        union

                        select 'buffer_hit' as name,

                               round((1 - (rr.pr - rr.prd) / rr.lr) * 100, 2) valuse

                          from (select max(decode(r.stat_name, 'session logical reads', r.value, 0)) lr,

                                       max(decode(r.stat_name, 'physical reads', r.value, 0)) pr,

                                       max(decode(r.stat_name, 'physical reads direct', r.value, 0)) prd

                                  from ((select max(s.snap_id) over() max_snap_id,

                                                s.stat_name,

                                                s.snap_id,

                                                ((max(value)

                                                 over(partition by s.stat_name order by s.snap_id

                                                       rows BETWEEN unbounded preceding AND unbounded

                                                       following)) -

                                                (min(value)

                                                 over(partition by s.stat_name order by s.snap_id

                                                       rows BETWEEN unbounded preceding AND unbounded

                                                       following))) value

                                           from dba_hist_sysstat s, dba_hist_snapshot snap

                                          where s.snap_id = snap.snap_id

                                            and s.stat_name in

                                                ('physical reads direct',

                                                 'physical reads',

                                                 'session logical reads')

                                            and snap.end_interval_time > sysdate - 1 / 24)) r

                                 where r.snap_id = r.max_snap_id) rr

                        union (select event, round(max(times), 2) as value

                                 from (select t.snap_id,

                                              to_char(b.begin_interval_time, 'yyyy-mm-dd hh24:mi:ss') stime,

                                              event_name event,

                                              decode(sign(total_waits - lag(total_waits, 1)

                                                          over(partition by event_name order by

                                                               t.snap_id)),

                                                     -1,

                                                     (lag(time_waited_micro, 1)

                                                      over(partition by event_name order by t.snap_id)) /

                                                     (lag(total_waits, 1)

                                                      over(partition by t.instance_number,

                                                           event_name order by t.snap_id)),

                                                     0,

                                                     0,

                                                     (time_waited_micro - lag(time_waited_micro, 1)

                                                      over(partition by event_name order by t.snap_id)) /

                                                     (total_waits - lag(total_waits, 1)

                                                      over(partition by event_name order by t.snap_id))) / 1000 as times

                                         from dba_hist_system_event t, dba_hist_snapshot b

                                        where t.snap_id = b.snap_id

                                          and b.begin_interval_time > sysdate - 1 / 24

                                          and b.instance_number = t.instance_number

                                          and event_name in ('db file sequential read',

                                                             'log file sync',

                                                             'direct path read',

                                                             'log file parallel write',

                                                             'db file parallel write')

                                          and t.instance_number = 1)

                                where times > 0

                                group by event)

                        union (select 'PGA' as names,

                                      ROUND(max(value) / 1024 / 1024 / 1024, 2) PGA_G

                                 from dba_hist_pgastat p, dba_hist_snapshot s

                                where name = 'maximum PGA allocated'

                                  and s.end_interval_time > sysdate - 1 / 24

                                  and p.snap_id = s.snap_id)

                        union (select 'FRA' as name, pct_used

                                 from (select b.TOTAL_G,

                                              b."TOTAL_G" * (1 - a."USED") "FREE_G",

                                              b."TOTAL_G" * (1 - a."USED" + a."RECLAIMABLE") "free+reclaimable_G",

                                              round((a."USED" - a."RECLAIMABLE") * 100, 2) as pct_used

                                         from (select sum(xx.PERCENT_SPACE_USED) / 100 "USED",

                                                      sum(xx.PERCENT_SPACE_RECLAIMABLE) / 100 "RECLAIMABLE"

                                                 from v$flash_recovery_area_usage xx) a,

                                              (select round(value / 1024 / 1024 / 1024) "TOTAL_G"

                                                 from v$parameter

                                                where name = 'db_recovery_file_dest_size') b))

                        union (

                        select name, sum(cnt)

                          from (select case

                                         when event like '%latch%' then

                                          'latch'

                                         else

                                          'enq'

                                       end as name,

                                       count(*) as cnt

                                  from v$session_wait

                                 where (event like '%latch%' or event like '%enq%')

                                 group by event

                                union


                                select 'latch', 0

                                  from dual

                                union

                                select 'enq', 0 from dual)

                        group by name

                        )

                        union

                            (select 'sessions' as name, count(*)

                              from v$session

                              where status='ACTIVE'

                            )

SELECT s.sid, kglpnmod "Mode", kglpnreq "Req"

    FROM x$kglpn p, v$session s

   WHERE p.kglpnuse=s.saddr

     AND kglpnhdl='&P1RAW'

  ;

在 后台用这个语句查一下,看那些是holder


select * from (

SELECT to_char(begin_interval_time, 'YYYY_MM_DD HH24:MI') snap_time,

       dhsso.object_name,

       SUM(db_block_changes_delta)

  FROM dba_hist_seg_stat     dhss,

       dba_hist_seg_stat_obj dhsso,

       dba_hist_snapshot     dhs

 WHERE dhs.snap_id = dhss.snap_id

   AND dhs.instance_number = dhss.instance_number

   AND dhss.obj# = dhsso.obj#

   AND dhss.dataobj# = dhsso.dataobj#

   AND begin_interval_time> sysdate - 120/1440

 GROUP BY to_char(begin_interval_time, 'YYYY_MM_DD HH24:MI'),

          dhsso.object_name

 order by 3 desc)

 where rownum<=5;

SELECT to_char(begin_interval_time, 'YYYY_MM_DD HH24:MI'),

       dbms_lob.substr(sql_text, 4000, 1),

       dhss.instance_number,

       dhss.sql_id,

       executions_delta,

       rows_processed_delta

  FROM dba_hist_sqlstat dhss, dba_hist_snapshot dhs, dba_hist_sqltext dhst

 WHERE UPPER(dhst.sql_text) LIKE '%TEST_REDO%'

   AND dhss.snap_id = dhs.snap_id

   AND dhss.instance_Number = dhs.instance_number

   AND dhss.sql_id = dhst.sql_id;

 

begin 

DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '/paic/stg/oracle/10g/otzj10g/wangsj/arch_620991420_1_339608.arc',OPTIONS => DBMS_LOGMNR.NEW);

DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '/paic/stg/oracle/10g/otzj10g/wangsj/arch_620991420_1_339609.arc',OPTIONS => DBMS_LOGMNR.addfile);

DBMS_LOGMNR.START_LOGMNR( OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG); 

end;

/


select to_char(t.TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS'),

to_char(t.COMMIT_TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS'),

t.SEG_OWNER,

t.SEG_NAME,

t.USERNAME,

t.OPERATION,

t.SQL_REDO

from V$LOGMNR_CONTENTS t

where t.SEG_NAME='PS_PAIC_PLAN_SEQ'

--upper(t.SQL_REDO) like '%PS_PAIC_PLAN_SEQ%';


EXECUTE DBMS_LOGMNR.END_LOGMNR();

1、将PUB_SYS_PACKAGE标记为HOT;

begin

sys.dbms_shared_pool.markhot(schema => 'ELISCDE',objname => 'PUB_SYS_PACKAGE',namespace => 1,global => true);

end;

/

begin

sys.dbms_shared_pool.markhot(schema => 'ELISCDE',objname => 'PUB_SYS_PACKAGE',namespace => 2,global => true);

end;

/

2、确认标记是否成功;

select count(*) from v$db_object_cache

where  name='PUB_SYS_PACKAGE' and property='HOT'; 

 

 

 

 

monitoring监控状态

SELECT u.name owner,

       io.name index_name,

       t.name table_name,

       DECODE(BITAND(i.flags, 65536), 0, 'NO', 'YES') monitoring,

       DECODE(BITAND(ou.flags, 1), 0, 'NO', 'YES') used,

       ou.start_monitoring start_monitoring,

       ou.end_monitoring end_monitoring

  FROM sys.user$        u,

       sys.obj$         io,

       sys.obj$         t,

       sys.ind$         i,

       sys.object_usage ou

 WHERE i.obj# = ou.obj#

   AND io.obj# = ou.obj#

   AND t.obj# = i.bo#

   AND u.user# = io.owner#

   and u.name = 'CISDATA'

   and DECODE(BITAND(ou.flags, 1), 0, 'NO', 'YES')='YES';

 

 

 

alter table *.* allocate extent (SIZE 5G);









select s.username,

       s.sid,

       a.value as cursor´ò¿ªÁ¿,

       (select y.VALUE from v$parameter y where y.NAME like '%open_cursors%') as ÉÏÏÞ

  from v$sesstat a, v$statname b, v$session s

 where a.statistic# = b.statistic#

   and s.sid = a.sid

   and b.name = 'opened cursors current'

   and a.value >

       (select y.VALUE from v$parameter y where y.NAME like '%open_cursors%') * 0.8

 order by value desc

 

 

 



wait  and lock

SELECT /*+ ordered */ gvw.inst_id Waiter_Inst, 

gvw.sid Waiter_Sid, 

gvs_w.osuser waiter_osuser, 

gvs_w.program waiter_program, 

gvs_w.machine waiter_machine, 

gvs_w.client_identifier waiter_identifer, 

gvs_w.client_info waiter_thread, 

gvs_w.seconds_in_wait waiter_secs_in_wait, 

gvs_w.sql_id waiter_sql, 

(select object_name from dba_objects a where a.object_id(+)=gvs_w.row_wait_obj#) waiter_table, 

dbms_rowid.rowid_create(1,gvs_w.ROW_WAIT_OBJ#,gvs_w.ROW_WAIT_FILE#,gvs_w.ROW_WAIT_BLOCK#,gvs_w.ROW_WAIT_ROW#) waiter_rowid_Waiting_on, gvs_w.event waiter_event, decode(gvw.request, 0, 'None', 

1, 'NoLock', 

2, 'Row-Share', 

3, 'Row-Exclusive', 

4, 'Share-Table', 

5, 'Share-Row-Exclusive', 

6, 'Exclusive', 

'Nothing-') Waiter_Mode_Req, 

decode(gvh.type, 'MR', 'Media_recovery', 

'RT', 'Redo_thread', 

'UN', 'User_name', 

'TX', 'Transaction', 

'TM', 'Dml', 

'UL', 'PLSQL User_lock', 

'DX', 'Distrted_Transaxion', 

'CF', 'Control_file', 

'IS', 'Instance_state', 

'FS', 'File_set', 

'IR', 'Instance_recovery', 

'ST', 'Diskspace Transaction', 

'IV', 'Libcache_invalidation', 

'LS', 'LogStaartORswitch', 

'RW', 'Row_wait', 

'SQ', 'Sequence_no', 

'TE', 'Extend_table', 

'TT', 'Temp_table', 

'Nothing-') Waiter_Lock_Type, 

gvh.inst_id Locker_Inst, gvh.sid Locker_Sid, gvs.osuser locker_osuser, gvs.machine locker_machine, gvs.program locker_program, 

gvs.client_identifier locker_identifer, 

gvs.client_info locker_thread, 

gvs.seconds_in_wait locker_secs_in_wait, gvs.serial# Locker_Serial,gvs.event locker_event,gvs.sql_id locker_sql,(select object_name from dba_objects a where a.object_id(+)=gvs.row_wait_obj#) locker_table,gvs.prev_sql_id locker_prev_sql, 

gvs.status locker_Status, gvs.module locker_Module 

FROM gv$lock gvh, gv$lock gvw, gv$session gvs,gv$session gvs_w WHERE (gvh.id1, gvh.id2) in ( 

SELECT id1, id2 FROM gv$lock WHERE request=0 

INTERSECT 

SELECT id1, id2 FROM gv$lock WHERE lmode=0) 

AND gvh.id1=gvw.id1 

AND gvh.id2=gvw.id2 

AND gvh.request=0 

AND gvw.lmode=0 

AND gvh.sid=gvs.sid 

AND gvw.sid=gvs_w.sid 

AND gvh.inst_id=gvs.inst_id 

AND gvw.inst_id=gvs_w.inst_id 

AND gvs_w.sql_id is not null;

sqlt方法:

Download SQLT from note 215187.1 

$ unzip sqlt.zip 

$ cd sqlt/install 

$ sqlplus / as sysdba 

SQL> START sqcreate.sql 


Optional Connect Identifier (ie: @PROD): <just press ENTER> Password for user SQLTXPLAIN: <password of using sqlt> Default tablespace [UNKNOWN]:USERS Temporary tablespace [UNKNOWN]: TEMP Main application user of SQLT: <application user> Oracle Pack license [T]:T 


2) Get the sql_id from v$sql, for example:  d9manhbn1a6t0 


3) Run SQLT with sqltxtract method: 

$ cd sqlt 

$ sqlplus <application_user>/<passwd> 

SQL> START run/sqltxtract.sql  d9manhbn1a6t0 

Password: <password> 


4) Upload the generated sqlt_s*.zip file to this SR $ ls sqlt_s* sqlt_s95236.zip 



优化效率: 


EXEC sqltxadmin.sqlt$a.set_param('test_case_builder', 'N'); EXEC sqltxadmin.sqlt$a.set_param('sta_time_limit_secs', '30'); EXEC sqltxadmin.sqlt$a.set_param('c_gran_segm', 'PARTITION'); EXEC sqltxadmin.sqlt$a.set_param('c_gran_cols', 'PARTITION'); EXEC sqltxadmin.sqlt$a.set_param('c_gran_hgrm', 'PARTITION'); 



sqlplus -prelim / as sysdba

   oradebug setmypid

   oradebug unlimit

   oradebug setinst all   --RAC环境

   oradebug hanganalyze 3  -- 级别一般指定为3足够了

   oradebug -g def dump systemstate 10  --RAC环境

   oradebug tracefile_name


   

   

systemdump方法:

SSD: 


     conn / as sysdba 


oradebug setmypid 


oradebug unlimit 


oradebug -g all hanganalyze 3 


oradebug -g all dump systemstate 258 


- -Wait for 10 seconds 


oradebug -g all hanganalyze 3 


oradebug -g all dump systemstate 258 


oradebug close_trace 

oradebug tracefile_name

dba_hist_memory_resize_ops





    select to_char(first_time, 'yyyy-mm-dd') day,

           sum(decode(to_char(first_time, 'hh24'), '00', 1, 0)) h00,

           sum(decode(to_char(first_time, 'hh24'), '01', 1, 0)) h01,

           sum(decode(to_char(first_time, 'hh24'), '02', 1, 0)) h02,

           sum(decode(to_char(first_time, 'hh24'), '03', 1, 0)) h03,

           sum(decode(to_char(first_time, 'hh24'), '04', 1, 0)) h04,

           sum(decode(to_char(first_time, 'hh24'), '05', 1, 0)) h05,

           sum(decode(to_char(first_time, 'hh24'), '06', 1, 0)) h06,

           sum(decode(to_char(first_time, 'hh24'), '07', 1, 0)) h07,

           sum(decode(to_char(first_time, 'hh24'), '08', 1, 0)) h08,

           sum(decode(to_char(first_time, 'hh24'), '09', 1, 0)) h09,

           sum(decode(to_char(first_time, 'hh24'), '10', 1, 0)) h10,

           sum(decode(to_char(first_time, 'hh24'), '11', 1, 0)) h11,

           sum(decode(to_char(first_time, 'hh24'), '12', 1, 0)) h12,

           sum(decode(to_char(first_time, 'hh24'), '13', 1, 0)) h13,

           sum(decode(to_char(first_time, 'hh24'), '14', 1, 0)) h14,

           sum(decode(to_char(first_time, 'hh24'), '15', 1, 0)) h15,

           sum(decode(to_char(first_time, 'hh24'), '16', 1, 0)) h16,

           sum(decode(to_char(first_time, 'hh24'), '17', 1, 0)) h17,

           sum(decode(to_char(first_time, 'hh24'), '18', 1, 0)) h18,

           sum(decode(to_char(first_time, 'hh24'), '19', 1, 0)) h19,

           sum(decode(to_char(first_time, 'hh24'), '20', 1, 0)) h20,

           sum(decode(to_char(first_time, 'hh24'), '21', 1, 0)) h21,

           sum(decode(to_char(first_time, 'hh24'), '22', 1, 0)) h22,

           sum(decode(to_char(first_time, 'hh24'), '23', 1, 0)) h23,

           count(*) total

      from gv$log_history a

     where first_time >= trunc(sysdate) - 7

     group by to_char(first_time, 'yyyy-mm-dd')

     order by day;


 

select s.username,s.osuser,s.event,count(*) over(partition by event) event_count,

       q.sql_text,q.sql_fulltext,s.status,q.sql_id,q.child_number,

       q.plan_hash_value,s.sql_exec_start,s.machine,s.sid,s.serial#,

       s.pq_status,s.pdml_status,s.pddl_status,

       'alter system kill session '''||s.sid||','||s.serial#||''' immediate;' kill_session

  from v$session s,v$sql q 

 where s.sql_id=q.sql_id 

   and s.username is not null

   and event not in ('SQL*Net message from client','SQL*Net message to client')

order by event_count desc,sql_id;

 


select t.snap_id,

               to_char(b.begin_interval_time, 'yyyy-mm-dd hh24:mi:ss') stime,

               event_name event,

               decode(sign(total_waits - lag(total_waits, 1)

                           over(partition by event_name order by t.snap_id)),

                      -1,

                      (lag(time_waited_micro, 1)

                       over(partition by event_name order by t.snap_id)) /

                      (lag(total_waits, 1)

                       over(partition by t.instance_number,

                            event_name order by t.snap_id)),

                      0,

                      0,

                      (time_waited_micro - lag(time_waited_micro, 1)

                       over(partition by event_name order by t.snap_id)) /

                     (total_waits - lag(total_waits, 1)

                       over(partition by event_name order by t.snap_id))) / 1000 as times

          from dba_hist_system_event t, dba_hist_snapshot b

         where t.snap_id = b.snap_id

           and b.begin_interval_time between

               to_date('&begin_time', 'YYYY-MM-DD HH24:MI:SS') and

               to_date('&end_time', 'YYYY-MM-DD HH24:MI:SS')

           and b.instance_number = t.instance_number

           and event_name in ('db file sequential read',

                              'log file sync',

                              'direct path read',

                              'log file parallel write',

                              'db file parallel write')

           and t.instance_number = 1)

where times > 0

group by snap_id, stime

order by snap_id;



SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ 

  FROM SYS.x$ksppi x, SYS.x$ksppcv y 

  WHERE x.inst_id = USERENV ('Instance') 

  AND y.inst_id = USERENV ('Instance') 

  AND x.indx = y.indx 

  AND x.ksppinm LIKE '%&par%'; 


select ash.sql_id,

       sum(nvl(TM_DELTA_CPU_TIME, 0)) as totalCPU,

       count(*) as exectimes,

       ar.sql_text

  from v$active_session_history ash,v$sqlarea ar

where sample_time >= to_timestamp('2019-09-26 18:34:00', 'yyyy-mm-dd hh24:mi:ss')

   and sample_time <= to_timestamp('2019-09-26 19:55:00', 'yyyy-mm-dd hh24:mi:ss')

   and ash.sql_id is not null

   and ash.sql_id=ar.sql_id

   group by ash.sql_id,ar.sql_text

order by 2 desc

   


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26773184/viewspace-2658617/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2013-11-22

  • 博文量
    16
  • 访问量
    44601