Oracle tcpsocket kgas,111

最新推荐文章于 2021-04-15 18:19:58 发布

蛋丁蛋丝

最新推荐文章于 2021-04-15 18:19:58 发布

阅读量479

点赞数

文章标签： Oracle tcpsocket kgas

--查看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);

--发现那些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)),

(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

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)),

(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)),

(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)),

(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,

'INSERT',

'UPDATE',

'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

查看数据库具体时间点的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;

查看对应时间的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;

我们还可以进一步在分析数据库代码，帮助开发解决问题。这里使用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)),

(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): Password for user SQLTXPLAIN: Default tablespace [UNKNOWN]:USERS Temporary tablespace [UNKNOWN]: TEMP Main application user of SQLT: 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 /

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)),

(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

蛋丁蛋丝

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Oracle tcpsocket kgas,111

--查看cpuselect *　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), t...
复制链接

扫一扫

Oracle tcpsocket kgas,111

“相关推荐”对你有帮助么？