--查看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):
2) Get the sql_id from v$sql, for example: d9manhbn1a6t0
3) Run SQLT with sqltxtract method:
$ cd sqlt
$ sqlplus
SQL> START run/sqltxtract.sql d9manhbn1a6t0
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