#1.23
sqlplus "/ as sysdba"
set lines 200 pages 1000
col state for a18
col username for a11
col machine for a15
col event for a30 trunc
col sql_text for a45
col spid for a8
col sql_id for a13
col logon_time for a19
col seconds for 99999
col sid for 9999
col blk_sid for 9999
col blk_inst for 99
col program for a23 trunc
SELECT * FROM (SELECT sid, username,event,sql_id,FINAL_BLOCKING_INSTANCE blk_inst,
FINAL_BLOCKING_SESSION blk_sid, program,to_char(logon_time, 'yy-mm-dd hh24:mi:ss')
logon_time, SECONDS_IN_WAIT seconds,state,WAIT_TIME FROM V$SESSION WHERE (WAIT_CLASS <> 'Idle')
or state <> 'WAITING' ORDER BY SECONDS_IN_WAIT DESC, event)
WHERE SID <> (SELECT SID FROM V$MYSTAT WHERE ROWNUM < 2) and ROWNUM < 201;
col sql_text for a80
select sql_text from v$sqltext where sql_id='&sql_id' order by piece;
set linesize 200 pagesize 1000
col column_name for a30 trunc
select * from table(dbms_xplan.display_cursor('&sql_id',null,'advanced'));
set long 1000000
set longchunksize 1000000
set lin 300
set pages 1000
set trim on
set trimspool on
set echo off
set feedback off
select dbms_sqltune.report_sql_monitor(sql_id=>'&sqlid',type=>'TEXT',report_level=>'ALL') mon_rpt from dual;
select * from table(dbms_xplan.display_awr('&sqlid'));
alter session set statistics_level=all;
select count(0) from t1;
select * from table(dbms_xplan.display_cursor(null,null,'all allstats last outline'));
--根据os pid 查看会话
set lines 170 pages 1000
col username for a11
col machine for a15
col program for a32
col event for a30
select sid,serial#,username,to_char(logon_time,'yyyy-mm-dd hh24:mi:ss')logon_time,program,machine,event,sql_id from v$session where paddr=(select addr from v$process where spid=&pid);
--查看非空闲等待事件
SET line 150 pages 100
col event FOR a40
col p1text FOR a15
col p2text FOR a15
col p3text FOR a20
SELECT * FROM (SELECT SID, EVENT, P1TEXT, P1, P2TEXT,P2, P3TEXT, P3, SECONDS_IN_WAIT seconds FROM V$SESSION_WAIT WHERE WAIT_CLASS <> 'Idle' AND SID <>(SELECT SID FROM V$MYSTAT WHERE ROWNUM<2) ORDER BY SECONDS_IN_WAIT DESC,event) WHERE ROWNUM<201;
--查被阻塞会话
set lin 200 pages 1000
col USERNAME for a10
col PROGRAM for a40
col EVENT for a30
col WAITING_SESSION for a20
WITH tkf_block_info AS
(SELECT a.inst_id || '_' || a.sid waiting_session,
a.username, a.program, a.event, a.sql_id, a.last_call_et,
DECODE(a.blocking_instance || '_' || a.blocking_session,
'_', NULL, a.blocking_instance || '_' || a.blocking_session) holding_session
FROM gv$session a,
(SELECT inst_id, sid
FROM gv$session
WHERE blocking_session IS NOT NULL
UNION
SELECT blocking_instance, blocking_session
FROM gv$session
WHERE blocking_session IS NOT NULL) b
WHERE a.inst_id = b.inst_id
AND a.SID = b.sid)
SELECT LPAD(' ', 3 * (LEVEL - 1)) || waiting_session waiting_session,
username, program, event, sql_id, last_call_et
FROM tkf_block_info
CONNECT BY PRIOR waiting_session = holding_session
START WITH holding_session IS NULL;
select 'blocker(' || lb.sid || ':' || sb.username || ')-sql:' || qb.sql_text blockers,
'waiter (' || lw.sid || ':' || sw.username || ')-sql:' || qw.sql_text waiters
from v$lock lb, v$lock lw, v$session sb, v$session sw, v$sql qb, v$sql qw
where lb.sid = sb.sid
and lw.sid = sw.sid
and sb.prev_sql_addr = qb.address
and sw.sql_address = qw.address
and lb.id1 = lw.id1
and sw.lockwait is not null
and sb.lockwait is null
and lb.block = 1;
--阻塞会话的详细信息
set lines 170 pages 1000
col username for a15
col machine for a15
col program for a35
col event for a30
col sql_text for a45
col spid for a8
select sid,spid,a.username,a.program,machine,event,sql_id,to_char(logon_time,'yyyy-mm-dd hh24:mi:ss')logon_time from v$session a,v$process c where c.addr=a.paddr and sid=&session_id;
col sql_text for a80
select sql_text from v$sqltext where sql_id='&sql_id' order by piece;
--查看历史语句
select dbms_lob.substr(sql_text,2000,1) txt from dba_hist_sqltext where sql_id='&sql_id';
-- 看语句耗费
set lines 180 pages 1000
col sql_text for a50
col cpu_time for 99999999999999
col elapsed_time for 999999999999
select distinct a.sql_id, ELAPSED_TIME,CPU_TIME,EXECUTIONS,DISK_READS,BUFFER_GETS,FETCHES,substr(sql_text,1,50)sql_text,round(ELAPSED_TIME/decode(EXECUTIONS,0,1,EXECUTIONS)) second_per from v$sqlarea a,v$session b where a.sql_id=b.sql_id and b.status='ACTIVE' order by second_per desc;
-- kill sid
set lin 200 pages 100
col sql_text for a55
col ses_stat for a8
col USERNAME for a10
select 'alter system kill session '''||a.sid||','||a.serial#||''' immediate;' sql_text
from v$session a where 1=1 and username='SSCS' and (event='SQL*Net message to client' or event ='latch free' );
select 'alter system kill session '''||a.sid||','||a.serial#||''' immediate;' sql_text
,a.username,b.start_time,b.start_scnb,b.xidusn,b.used_urec,b.used_ublk,a.status ses_stat,a.sql_id
from v$session a ,v$transaction b where a.saddr=b.ses_addr
and username='SSCS' and event='SQL*Net message from client' ;
-- a.sid=2872;
--需绑定变量语句
set lines 150
set pages 100
col txt for a65
select substr(sql_text, 1, 60) txt, count(0)
from v$sqlarea
group by substr(sql_text, 1, 60)
having count(substr(sql_text, 1, 60)) > 50
order by 2 desc;
select dbms_sqltune.report_sql_detail( report_level=>'ALL',type=>'text') mon_rpt from dual;
set timing on
begin
dbms_stats.gather_table_stats(
ownname =>'&owner',
tabname => '&table_name',
no_invalidate => false,
estimate_percent => 3,
degree => 16,
cascade => true
);
end;
/
-- no_invalidate 强制相关游标变为无效，下次执行时重新生成执行计划，过度集中使用会导致硬解析问题
sqlplus "/as sysdba"
set lines 200 pages 100
col txt for a65
select a.sql_id,a.cnt,a.pctload,b.sql_text txt from (select * from (
select sql_id,count(0) cnt,round(count(0)/sum(count(0)) over(),4)*100 pctload
from v$active_session_history A
where A.SAMPLE_TIME>sysdate-5/60/24
and sql_id is not null
GROUP BY SQL_ID
ORDER BY COUNT(0) DESC)
where rownum<11) a left join (select distinct sql_text,sql_id from v$sqltext where piece=0) b on a.sql_id=b.sql_id order by 2 desc ,1;
过滤空行和注释
grep -Ev "^$|[#;]" aaa.txt
cpu使用率最高的前10个进程(linux)
ps aux --sort=-%cpu|grep -m 11 -v `whoami`
--占用cpu最多的进程
ps -ef|sort -rn +7 |head -n 18
cpu使用率最高的前10个进程(aix)
ps aux |head -1 ; ps aux|sort -rn +2|head -20
占用内存最高的前10个进程(linux)
ps aux|head -1;ps aux|grep -v PID|sort -rn -k +4|head
占用内存最高的20个进程(aix)
ps aux |head -1 ; ps aux|sort -rn +4 |head -20
使用内存最多的10个进程
svmon -uP -t 10|grep -p Pid|grep '^.*[0-9]'|grep -v Pid
使用交换区最多的10个进程
svmon -gP -t 10|grep -p Pid|grep '^.*[0-9]'
--10g以上 未绑定变量的
col SQL_TEXT for a80
set pages 1000
set lines 150
col FORCE_MATCHING_SIGNATURE for 999999999999999999999
select * from (
WITH c AS
(SELECT FORCE_MATCHING_SIGNATURE, COUNT(*) cnt
FROM v$sqlarea
WHERE FORCE_MATCHING_SIGNATURE != 0
GROUP BY FORCE_MATCHING_SIGNATURE
HAVING COUNT(*) >20),
sq AS
(SELECT sql_text,
FORCE_MATCHING_SIGNATURE,
row_number() over(partition BY FORCE_MATCHING_SIGNATURE ORDER BY sql_id DESC) p
FROM v$sqlarea s
WHERE FORCE_MATCHING_SIGNATURE IN
(SELECT FORCE_MATCHING_SIGNATURE FROM c))
SELECT sq.sql_text, sq.FORCE_MATCHING_SIGNATURE, c.cnt "unshared count"
FROM c, sq
WHERE sq.FORCE_MATCHING_SIGNATURE = c.FORCE_MATCHING_SIGNATURE
AND sq.p = 1
ORDER BY c.cnt DESC
) where rownum<11;
--绑定变量情况
col NAME for a10
col DATATYPE_STRING for a20
set lines 150 pages 100
col VALUE_STRING for a40
select * from (
select name,datatype_string,value_string,last_captured
from dba_hist_sqlbind where sql_id='&sqlid' order by snap_id desc,last_captured desc ,1
) where rownum<51;
alter session set events '10046 trace name context forever,level 12';
alter session set events '10046 trace name context off';
--最近的长时间会话
col opname for a25
col target for a20
col units for a10
col message for a60
col ELAPSED_SECONDS for 999999
col START_TIME for a20
set lines 180
set pages 1000
select opname,sofar,units,ELAPSED_SECONDS, to_char(START_TIME,'yyyy-mm-dd hh24:mi:ss') START_TIME,message from v$session_longops
where START_TIME>=to_date('2017-02-14 10:00:00','yyyy-mm-dd hh24:mi:ss') and START_TIME<=to_date('2017-02-14 15:00:00','yyyy-mm-dd hh24:mi:ss')
and rownum<51 order by START_TIME;
--监控rman每个备份片备份进度
set lin 120 pages 100
col sid for 999999
col username for a5
col opname for a30
col usernamefor a20
col message for a40
col target for a5
col UNITS for a10
SELECT SID,target,username,opname, -- message,
SOFAR, TOTALWORK, ELAPSED_SECONDS,
ROUND(SOFAR / TOTALWORK * 100, 2) percent
FROM V$SESSION_LONGOPS
WHERE TOTALWORK != 0 AND SOFAR <> TOTALWORK order by target;
select SID,target,username,opname, SOFAR, TOTALWORK, ELAPSED_SECONDS ,percent,round(ELAPSED_SECONDS / (percent/100)/60 ) remain_min
from (SELECT SID,target,username,opname, SOFAR, TOTALWORK, ELAPSED_SECONDS,
ROUND(SOFAR / TOTALWORK * 100, 2) percent FROM V$SESSION_LONGOPS
WHERE TOTALWORK != 0 AND SOFAR <> TOTALWORK order by target) ;
--占用空间大小
set pages 100 lin 120
col segment_name for a32
col segment_type for a20
col owner for a20
select * from (
select owner,segment_name, SEGMENT_TYPE, round(sum(bytes) / 1024 / 1024/1024) g from dba_segments
-- where tablespace_name = 'SYSTEM'
group by owner,segment_name, SEGMENT_TYPE order by g desc) where rownum<51;
--分析过程
col owner for a20
select * from (
select owner,table_name,num_rows,blocks,avg_row_len,
to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss') last_analyzed
from dba_tables where last_analyzed is not null order by last_analyzed desc)
where rownum<501;
--分析时间较长的
create table t1 as
select * from (
select owner,table_name,num_rows,blocks,avg_row_len,last_analyzed
from dba_tables where last_analyzed is not null order by last_analyzed desc)
where last_analyzed>sysdate-6;
select *
from (select owner,table_name, num_rows,al, round((bl - al) * 24) hours
from (select a.table_name,
a.owner,
b.rn2,
a.num_rows,
to_date(a.last_analyzed, 'yyyy-mm-dd hh24:mi:ss') al,
to_date(b.last_analyzed, 'yyyy-mm-dd hh24:mi:ss') bl
from (select t1.*, rownum rn1 from t1 order by last_analyzed) a,
(select t1.*, rownum rn2 from t1 order by last_analyzed) b
where a.rn1 - 1 = b.rn2)
where (bl - al) > 2 / 24
order by hours desc)
where rownum < 21;
exec dbms_system.set_sql_trace_in_session(sid,serial#,false);
exec dbms_workload_repository.create_snapshot;
analyze table t1 compute statistics;
create or replace synonym YXQF.hb_smsg_qf for SMSMAIN.hb_smsG_QF;
hp-ux 创建lv: lvcreate -n data_p_32g_01 -i 2 -I 128 -L 32768 /dev/vgdata02
ps -ef|grep LOCAL=NO|grep -v grep|awk '{print $2}'|xargs -i kill -9 {}
ipcs -m |grep oracle|awk '{print $2}'|xargs ipcrm shm
nohup find ./adump -name *.aud -mtime +5 -exec rm -f {} \; &
find ./audit -name *.aud -mtime +5 -exec rm -f {} \;
find . -name "*.trc" -exec du -sm {} \; | sort -rn|head
重启主机后 find /u01 -type f -name "*.trc" -size +500M -exec du -sm {} \;|sort -rn| head -30
mklv -y pms3000 -T O -w n -s n -r n -t'raw' pmsdbvg 80
chown oracle:oinstall /dev/rpms3000 两个节点都要做
CREATE MATERIALIZED VIEW
ALTER ANY MATERIALIZED VIEW
DROP ANY MATERIALIZED VIEW
alter user sde quota unlimited on sde; 6.112
alter user scott quota unlimited on users;
alter user scott quota 100m on sysaux;
select distinct username,tablespace,contents,sql_id ,segtype from v$sort_usage order by 4;
select distinct username,blocks ,sql_id ,segtype,session_addr,sql_id from v$sort_usage ;
select s.username,s.event,s.prev_sql_id,u.tablespace,u.contents,u.segtype,u.extents,u.blocks,round(((u.blocks*p.value)/1024/1024),2) mb
from v$session s ,v$sort_usage u,v$parameter p
where s.saddr=u.session_addr
and upper(p.name)='DB_BLOCK_SIZE'
order by mb desc;
--查看临时表空间使用情况
select * from (
select se.username,se.sid,se.status,se.sql_hash_value,
se.prev_sql_id,su.tablespace,su.segtype,su.contents,round(su.blocks*8/1024,2)mb
from v$session se,v$sort_usage su
where se.saddr=su.session_addr
order by mb desc) where rownum<40;
select * from dba_hist_active_sess_history h where h.sample_time >= to_date('2013-05-13 15:05:00', 'yyyy-mm-dd hh24:mi:ss') and h.sample_time <= to_date('2013-05-13 15:11:00', 'yyyy-mm-dd hh24:mi:ss')
and h.instance_number=1
col TABLESPACE_NAME for a20
select TABLESPACE_NAME,file_id,round(BYTES_USED/1024/1024,2) BYTES_USED_mb,
round(BLOCKS_USED*8/1024,2) BLOCKS_USED_m,BYTES_FREE/1024/1024 free_mb from v$temp_space_header;
--会话占用内存
col name for a25
col username for a12
col program for a25
col event for a38
select * from (
select s.sid, name, trunc(value / 1024/1024) m,s2.username,s2.program,s2.event,s2.sql_id
from v$sesstat s, v$statname n,v$session s2
where s.STATISTIC# = n.STATISTIC# and s.SID=s2.sid
and name like '%memory%'
order by m desc
) where rownum<51;
--查看隐含参数
set lines 150
col name for a40
col value for a20
col descript for a70
select a.ksppinm name,b.ksppstvl value,a.ksppdesc descript
from x$ksppi a,x$ksppcv b where a.indx=b.indx and a.ksppinm like '%¶%';
export NLS_DATE_FORMAT='YYYY-MM-DD HH24MISS'
export NLS_LANG="SIMPLIFIED CHINESE_CHINA.AL32UTF8"
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
归档切换频率：
select sequence#,
to_char(first_time, 'yyyy-mm-dd hh24:mi:ss') dt,
round((first_time - lag(first_time) over(order by first_time)) * 24 * 60,
2) minutes
from v$log_history
where first_time > sysdate - 1
order by first_time
---
授予创建awr权限：
grant execute on dbms_workload_repository to XXX;
grant select on sys.v_$instance to XXX;
grant select on sys.v_$database to XXX;
grant select on sys.dba_hist_database_instance to XXX;
grant select on sys.dba_hist_snapshot to XXX;
--v$sesison不能反映递归session 所以报ora-00018 超出最大会话数时 看实际的会话
select ksuudnam,count(0) from x$ksuse where bitand(ksspaflg,1)<>0 group by ksuudnam order by 1;
select username,count(0) from v$session group by username order by 1;
--闪回空间使用
select * from v$flash_recovery_area_usage;
收集heap dump，可以获得对内存的分配情况。
Action plan/Solution
=========================
1. Trace file generated with:
alter system set events '10235 trace name context forever, level 65536';
alter system set events '4031 trace name errorstack level 3;name heapdump level 536870914';
2. After trace generation, the events can be disabled with:
alter system set events '4031 trace name errorstack off;name heapdump off';
alter system set events '10235 trace name context off';
=========================
或者采用下面的方式:
conn / as sysdba
oradebug setmypid
oradebug unlimit
oradebug dump heapdump 536870914
oradebug tracefile_name
oradebug close_trace
-----------方法1----------------
挂起时在一个实例上用sysdba 执行：
1 执行挂起分析
sqlplus -prelim / as sysdba
oradebug setmypid
oradebug unlimit
oradebug -g all hanganalyze 3
等30秒
oradebug -g all hanganalyze 3
oradebug tracefile_name
exit
2 打开另一个会话，抓取状态
sqlplus -prelim / as sysdba
oradebug setmypid
oradebug unlimit
oradebug -g all dump systemstate 10
等30秒
oradebug -g all dump systemstate 10
oradebug tracefile_name
exit
-----------方法2----------
抓取系统状态(10g)
sqlplus -prelim / as sysdba
oradebug setmypid
oradebug unlimit
oradebug -g all dump systemstate 266
等30秒
oradebug -g all dump systemstate 266
exit
如果产生的diag文件太大或挂起
在各实例上执行
sqlplus -prelim / as sysdba
oradebug setmypid
oradebug unlimit
oradebug hanganalyze 3
等30秒
oradebug hanganalyze 3
oradebug dump systemstate 258
oradebug tracefile_name
exit
11g 及以上
oradebug setospid
oradebug unlimit
oradebug -g all hanganalyze 3
等30秒
oradebug -g all hanganalyze 3
oradebug -g all dump systemstate 258
exit
---------------------------
ULTRAEDIT 规则表达式 搜索^{ORA-^}^{ERROR^}
PROMPT *********check database flashback ***************
PROMPT
select flashback_on from v\$database;
PROMPT
col name format a50;
col value format a20;
select name,value from v$\parameter where name like 'db_recovery_file_dest%';
vi替换
sed -i "s/^M//" file.sh
替换保存
:
1,$s/REM ... /--/g
1,$s/"USERS"/ "BZH"/g
1,$s/CONNECT /--/g
1,$s/REM / /g
s.sql
UltraEdit替换空行
ctrl+r
%[ ^t]++^p
--内存抖动
col COMPONENT for a30
col STARTED_TIME for a22
col END_TIME for a22
set lin 200 pages 100
SELECT COMPONENT,
OPER_TYPE,
round(a.INITIAL_SIZE/1024/1024) initial_size_mb,
round(FINAL_SIZE/1024/1024) FINAL_SIZE_mb,
round((FINAL_SIZE-INITIAL_SIZE)/1024/1024) resize_mb,
to_char(start_time, 'yyyy-mm-dd hh24:mi:ss') Started_time,
to_char(end_time, 'yyyy-mm-dd hh24:mi:ss') end_time
FROM V$SGA_RESIZE_OPS a
where start_time >= to_date('2019-10-01 00:00', 'yyyy-mm-dd hh24:mi')
and start_time <= to_date('2019-10-10 23:59', 'yyyy-mm-dd hh24:mi')
order by Started_time;
SELECT component, oper_type, to_char(start_time, 'yyyy-mm-dd hh24') Started_time, count(0) cnt
FROM V$SGA_RESIZE_OPS s
where start_time >= to_date('2019-10-01 00:00', 'yyyy-mm-dd hh24:mi')
and start_time <= to_date('2019-10-10 23:59', 'yyyy-mm-dd hh24:mi')
and component='shared pool'
group by component, oper_type,to_char(start_time, 'yyyy-mm-dd hh24')
order by Started_time;
--飘
SET ECHO OFF
SET FEEDBACK ON
SET HEADING ON
SET LINESIZE 180
SET PAGESIZE 2000
SET TIMING OFF
SET TRIMSPOOL ON
SET VERIFY OFF
CLEAR COLUMNS BREAKS COMPUTES
COLUMN DAY FORMAT a8 HEADING 'D/T'
COLUMN H00 FORMAT 999B HEADING '00'
COLUMN H01 FORMAT 999B HEADING '01'
COLUMN H02 FORMAT 999B HEADING '02'
COLUMN H03 FORMAT 999B HEADING '03'
COLUMN H04 FORMAT 999B HEADING '04'
COLUMN H05 FORMAT 999B HEADING '05'
COLUMN H06 FORMAT 999B HEADING '06'
COLUMN H07 FORMAT 999B HEADING '07'
COLUMN H08 FORMAT 999B HEADING '08'
COLUMN H09 FORMAT 999B HEADING '09'
COLUMN H10 FORMAT 999B HEADING '10'
COLUMN H11 FORMAT 999B HEADING '11'
COLUMN H12 FORMAT 999B HEADING '12'
COLUMN H13 FORMAT 999B HEADING '13'
COLUMN H14 FORMAT 999B HEADING '14'
COLUMN H15 FORMAT 999B HEADING '15'
COLUMN H16 FORMAT 999B HEADING '16'
COLUMN H17 FORMAT 999B HEADING '17'
COLUMN H18 FORMAT 999B HEADING '18'
COLUMN H19 FORMAT 999B HEADING '19'
COLUMN H20 FORMAT 999B HEADING '20'
COLUMN H21 FORMAT 999B HEADING '21'
COLUMN H22 FORMAT 999B HEADING '22'
COLUMN H23 FORMAT 999B HEADING '23'
COLUMN TOTAL FORMAT 999,999,999 HEADING 'Total'
BREAK ON report
COMPUTE sum label 'total' avg label 'avg' max label 'max' min label 'min' OF total ON report
SELECT
SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) DAY
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22
, SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23
, COUNT(*) TOTAL
FROM
v$log_history a
where first_time>sysdate-30 --and thread#=1
GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)
ORDER BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)
/
SET ECHO OFF
SET FEEDBACK ON
SET HEADING ON
SET LINESIZE 180
SET PAGESIZE 2000
SET TIMING OFF
SET TRIMSPOOL ON
SET VERIFY OFF
CLEAR COLUMNS BREAKS COMPUTES
BREAK ON instance_name ON thread#
COLUMN instance_name FORMAT a13 HEADING 'Instance Name'
COLUMN thread# FORMAT 999 HEADING 'Thread#'
COLUMN group# FORMAT 999 HEADING 'Group#'
COLUMN member FORMAT a60 HEADING 'Member'
COLUMN type FORMAT a10 HEADING 'Type'
COLUMN status FORMAT a15 HEADING 'Status'
COLUMN bytes FORMAT 999,999 HEADING 'LogSize MB'
COLUMN archived FORMAT a10 HEADING 'Archived'
SELECT
i.instance_name
, i.thread#
, f.group#
, f.member
, f.type
, l.status
, l.bytes/1024/1024 bytes
, l.archived
FROM
gv$logfile f
, gv$log l
, gv$instance i
WHERE
f.group# = l.group#
AND l.thread# = i.thread#
AND i.inst_id = f.inst_id
AND f.inst_id = l.inst_id
ORDER BY
i.instance_name
, f.group#
, f.member
/
--检查分区大小 是否有漏删数据
set timing on
col segment_name for a32
col pt for a10
set lines 120 pages 1000
select * from (
select segment_name,substr(partition_name,1,9) pt,round(sum(bytes)/1024/1024/1024) g
from dba_segments where partition_name is not null
group by segment_name,substr(partition_name,1,9)
order by 1,2,3
)where rownum<501;
--表空间大小
set pages 100
col TABLESPACE_NAME for a20
SELECT d.tablespace_name "TABLESPACE_NAME",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0), '99,999,990.90') "TOTAL_M",
TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0) / 1024 / 1024,
'99999999.99') "USED_M",
TO_CHAR(NVL(NVL(f.bytes, 0), 0) / 1024 / 1024, '99999999.99') "FREE_M",
TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0),
'990.00') "Used %"
FROM dba_tablespaces d,
(select tablespace_name, sum(bytes) bytes
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes) bytes
from dba_free_space
group by tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = f.tablespace_name(+)
AND NOT
(d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY')
UNION ALL
SELECT d.tablespace_name "TABLESPACE_NAME",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0), '99,999,990.90') TOTAL_M,
TO_CHAR(NVL(t.bytes, 0) / 1024 / 1024, '99999999.99') "USED_M",
TO_CHAR(NVL(a.bytes - t.bytes, 0) / 1024 / 1024, '99999999.99') "FREE_M",
TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Used %"
FROM dba_tablespaces d,
(select tablespace_name, sum(bytes) bytes
from dba_temp_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes_cached) bytes
from v$temp_extent_pool
group by tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management like 'LOCAL'
AND d.contents like 'TEMPORARY'
ORDER by 5 desc
/
--表空间使用率（mini）
col tablespace_name for a20
select a.tablespace_name,
round(a.bytes / 1024 / 1024) "Sum MB",
round((a.bytes - b.bytes) / 1024 / 1024) "used MB",
round(b.bytes / 1024 / 1024) "free MB",
round(((a.bytes - b.bytes) / a.bytes) * 100, 2) "percent_used"
from (select tablespace_name, sum(bytes) bytes
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes) bytes, max(bytes) largest
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
order by ((a.bytes - b.bytes) / a.bytes) desc;
--数据文件使用率
select b.file_id ,
b.tablespace_name ,
b.file_name ,
round(b.bytes / 1024 / 1024) size_M,
round(c.max_extents / 1024 / 1024) max_extents_M,
round(b.bytes / 1024 / 1024 + c.max_extents / 1024 / 1024) total_M,
trunc((b.bytes - sum(nvl(a.bytes, 0))) / 1024 / 1024) used_M,
trunc(sum(nvl(a.bytes, 0)) / 1024 / 1024) free_M,
trunc(100-sum(nvl(a.bytes, 0)) / (b.bytes) * 100, 2) used_percent
from dba_free_space a, dba_data_files b, dba_tablespaces c
where a.file_id = b.file_id
and b.tablespace_name = c.tablespace_name
group by b.tablespace_name,
b.file_name,
b.file_id,
b.bytes,
c.max_extents,
b.bytes / 1024 / 1024 + c.max_extents / 1024 / 1024
order by b.file_id;
--表空间大小（全）
set timing on
set lines 150 pages 100
col tablespace_name for a20
col type for a10
col stat for a8
col "Ext MGMT" for a8
col "Seg MGMT" for a8
col Autoextend for a4
SELECT /*+ first_rows */
d.tablespace_name,
NVL(a.bytes / 1024 / 1024, 0) "Size MB",
round(NVL(a.bytes - NVL(f.bytes, 0), 0) / 1024 / 1024,2) "Used MB",
round(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0),2) "Used %",
a.autoext "Autoextend",
round(NVL(f.bytes, 0) / 1024 / 1024,2) "Free MB",
d.status "STAT",
a.count "Files",
d.contents "Type",
d.extent_management "Ext MGMT",
d.segment_space_management "Seg MGMT"
FROM sys.dba_tablespaces d,
(select tablespace_name,
sum(bytes) bytes,
count(file_id) count,
decode(sum(decode(autoextensible, 'NO', 0, 1)),
0,
'NO',
'YES') autoext
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes) bytes
from dba_free_space
group by tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = f.tablespace_name(+)
AND NOT d.contents = 'UNDO'
AND NOT (d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY')
AND d.tablespace_name like '%%'
UNION ALL
SELECT d.tablespace_name,
NVL(a.bytes / 1024 / 1024, 0),
round(NVL(t.bytes, 0) / 1024 / 1024,2),
round(NVL(t.bytes / a.bytes * 100, 0),2),
a.autoext,
round((NVL(a.bytes, 0) / 1024 / 1024 - NVL(t.bytes, 0) / 1024 / 1024),2),
d.status,
a.count,
d.contents,
d.extent_management,
d.segment_space_management
FROM sys.dba_tablespaces d,
(select tablespace_name,
sum(bytes) bytes,
count(file_id) count,
decode(sum(decode(autoextensible, 'NO', 0, 1)),
0,
'NO',
'YES') autoext
from dba_temp_files
group by tablespace_name) a,
(select ss.tablespace_name,
sum((ss.used_blocks * ts.blocksize)) bytes
from gv$sort_segment ss, sys.ts$ ts
where ss.tablespace_name = ts.name
group by ss.tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management = 'LOCAL'
AND d.contents = 'TEMPORARY'
and d.tablespace_name like '%%'
UNION ALL
SELECT d.tablespace_name,
NVL(a.bytes / 1024 / 1024, 0),
round(NVL(u.bytes, 0) / 1024 / 1024,2),
round(NVL(u.bytes / a.bytes * 100, 0),2),
a.autoext,
round(NVL(a.bytes - NVL(u.bytes, 0), 0) / 1024 / 1024,2),
d.status,
a.count,
d.contents,
d.extent_management,
d.segment_space_management
FROM sys.dba_tablespaces d,
(SELECT tablespace_name,
SUM(bytes) bytes,
COUNT(file_id) count,
decode(sum(decode(autoextensible, 'NO', 0, 1)),
0,
'NO',
'YES') autoext
FROM dba_data_files
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) bytes
FROM (SELECT tablespace_name, sum(bytes) bytes, status
from dba_undo_extents
WHERE status = 'ACTIVE'
group by tablespace_name, status
UNION ALL
SELECT tablespace_name, sum(bytes) bytes, status
from dba_undo_extents
WHERE status = 'UNEXPIRED'
group by tablespace_name, status)
group by tablespace_name) u
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = u.tablespace_name(+)
AND d.contents = 'UNDO'
--AND d.tablespace_name LIKE '%%'
ORDER BY 4 desc
/
col PROPERTY_NAME for a30
col PROPERTY_VALUE for a50
col DESCRIPTION for a60
set lines 150 pages 100
select PROPERTY_NAME ,PROPERTY_VALUE,DESCRIPTION from database_properties where property_name like '%CHARACTERSET%' order by 1;
--查索引
set pages 1000 lin 150
col owner for a10
col table_owner for a10
col index_type for a25
col TABLE_NAME for a30
col index_name for a30
col degree for a10
select owner,index_name,index_type,status,BLEVEL,NUM_ROWS,DEGREE,last_analyzed from dba_indexes where table_name=upper('&tblname');
--查看表状态
col owner for a20
select owner,num_rows,blocks,avg_row_len,to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss') last_analyzed
from dba_tables where table_name=upper('&table_name');
--索引字段
col COLUMN_NAME for a20
col index_owner for a15
col index_name for a30
select index_owner,index_name,column_name,column_position,descend from dba_ind_columns where table_name=upper('&tblname') order by 1,2,4;
--字段统计信息
select column_name,num_distinct,NUM_NULLS,histogram,to_char(LAST_ANALYZED,'mmdd hh24:mi')analyzed from dba_tab_col_statistics where table_name=upper('&tbl') order by 1;
--最近执行过的语句
set lines 150 pages 100;
col sql_text for a100
col last_load_time for a20
select * from (select sql_id,sql_text,last_load_time from v$sql order by last_load_time desc) where rownum<6;
--顶级语句
select * from (
select sql_id,count(0) cnt,round(count(0)/sum(count(0)) over(),4)*100 pctload
from v$active_session_history A
where A.SAMPLE_TIME>sysdate-5/60/24
and sql_id is not null
GROUP BY SQL_ID
ORDER BY COUNT(0) DESC)
where rownum<11;
--顶级会话
col program for a30
select *
from (select session_id, user_id, program, count(0) cnt,
round(count(0) / sum(count(0)) over(), 4) * 100 pctload
from v$active_session_history A
where A.SAMPLE_TIME > sysdate - 5 / 60 / 24
and sql_id is not null
GROUP BY session_id, user_id, program
ORDER BY COUNT(0) DESC)
where rownum < 11;
--历史会话被锁情况
col program for a38
col event for a30
col module for a32
set lines 150 pages 100
select session_id,
/*
session_serial#,
user_id,sql_id,
*/
program,module,event,h.blocking_session,h.blocking_inst_id,h.blocking_session_serial#
from dba_hist_active_sess_history h
where h.sample_time >= to_date('2016-08-24 16:16:00', 'yyyy-mm-dd hh24:mi:ss')
and h.sample_time <= to_date('2016-08-24 16:36:00', 'yyyy-mm-dd hh24:mi:ss')
and session_id=3346 and instance_number=1
and rownum<51;
col name for a15
select name,trunc((total_mb-free_mb)/1024) used_g,trunc(total_mb/1024) total_g, trunc(free_mb/1024) free_g,round((total_mb-free_mb)/total_mb*100,2) used_percent
from v$asm_diskgroup order by used_percent desc ;
col path for a30
set lines 150
select GROUP_NUMBER,TOTAL_MB,FREE_MB,NAME,PATH ,STATE from v$asm_disk;
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH SHARED_POOL;
set pages 100
BREAK ON report
COMPUTE sum label 'total' avg label 'avg' max label 'max' min label 'min' OF G ON report
select * from (select to_char(COMPLETION_TIME,'yyyy-mm-dd') dt,round(sum(BLOCKS *BLOCK_SIZE)/1024/1024/1024) G,count(0) cnt from
v$archived_log group by to_char(COMPLETION_TIME,'yyyy-mm-dd')) where g>10 order by 1;
col version for a15
col comments for a40
set lin 150 pages 100
select version,id,bundle_series,comments from dba_registry_history;
sqlplus / as sysdba
col owner for a12
col DIRECTORY_NAME for a25
col DIRECTORY_PATH for a80
set lin 190 pages 100
select * from dba_directories order by 1,3;
sqlplus / as sysdba
col OWNER for a10
col DB_LINK for a20
col USERNAME for a15
col HOST for a40
set line 150
set pages 100
select * from dba_db_links order by db_link;
-- 检查集群负载均衡
with sys_time as (
select inst_id,sum(case stat_name when 'DB time' THEN VALUE END) DB_TIME,
sum(case when stat_name in ('DB CPU','backupground cpu time') then value end) cpu_time
from gv$sys_time_model group by inst_id)
select instance_name,
round(db_time/1000000,2) db_time_secs,
round(db_time*100/sum(db_time) over(),2) db_time_pct,
round(cpu_time/1000000,2) cpu_time_secs,
round(cpu_time*100/sum(cpu_time) over(),2) cpu_time_pct
from sys_time
join gv$instance using (inst_id);
dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbname AICPUAT -sid AICPUAT -characterSet ZHS16GBK -sysPassword Db#zyfd2018 -systemPassword Db#zyfd2018 -totalMemory 2048 -redoLogFileSize 200 -datafileDestination /oradata -createListener LISTENER:1521
dbca -silent -deleteDatabase -sourceDB AICPUAT
select usn,xacts,round(rssize/1024/1024) rssize_m , round(hwmsize/1024/1024) hwmsize_m,shrinks
from v$rollstat order by rssize;
select TABLESPACE_NAME,status, round(sum(bytes)/1024/1024) bytes_m
from dba_undo_extents group by TABLESPACE_NAME,status order by 1,2;
--pga使用
set lines 200;
set pages 200;
column name format a25;
column pname format a12;
col spid for a15
column "UsedMB" format a10;
set numwidth 6;
select s.sid, s.serial#,p.pid, p.spid,p.pname, sn.name, round(ss.value/(1024 *1024))||'Mb' "UsedMB"
from v$sesstat ss, v$statname sn, v$session s, v$process p
where s.paddr = p.addr
and sn.statistic# = ss.statistic#
and s.sid = ss.sid
and sn.name in ('session pga memory' , 'session pga memory max')
and p.pname like 'DIA%'
order by ss.value desc;
select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread# order by 1;
SELECT * FROM V$ARCHIVE_GAP;
col status for a15
select process,status,sequence#,thread# from v$managed_standby;
alter database recover managed standby database disconnect from session;
alter database recover managed standby database using current logfile disconnect from session;
alter database recover managed standby database cancel;
recover standby database; --手工 rman recover database
#主库
select thread#,max(sequence#) from v$archived_log where resetlogs_change#=(select resetlogs_change# from v$database)group by thread# order by thread#;
#备库
select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
备库v$managed_standby;
col status for a12
set pages 100
select process,status,thread#,sequence# from v$managed_standby;
--强制把备库切为主
alter database recover managed standby database finish force;
alter database commit to switchover to primary;
--正常主备切换（简单粗暴）
-主库
SELECT SWITCHOVER_STATUS FROM V$DATABASE;
应该是TO STANDBY状态
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
shu immediate
STARTUP MOUNT
alter database recover managed standby database disconnect from session;
alter database open
-备库
SELECT SWITCHOVER_STATUS FROM V$DATABASE;
应该是 TO PRIMARY状态
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY with session shutdown;
shu immediate
startup
--Login Beijing
sqlplus / as sysdba
startup nomount
exit
rman target sys/Db#zyfd2018@tns_orcldg auxiliary sys/Db#zyfd2018@tns_orcl
duplicate target database for standby from active database dorecover nofilenamecheck;
set lin 120 pages 100
col name for a8
col db_unique_name for a16
col DATABASE_ROLE for a20
col SWITCHOVER_STATUS for a20
col open_mode for a22
select name,db_unique_name,database_role,switchover_status,OPEN_MODE from gv$database;
set pagesize 9999 line 9999
col TS_Name format a30
col PDBNAME format a15
col TS_NAME format a20
col LOGGING format a10
SELECT CON_ID,
PDBNAME,
TS#,
TS_NAME,
TS_SIZE_M,
FREE_SIZE_M,
USED_SIZE_M,
USED_PER,
MAX_SIZE_G,
USED_PER_MAX,
BLOCK_SIZE,
LOGGING,
TS_DF_COUNT
FROM (WITH wt1 AS (SELECT ts.CON_ID,
(SELECT np.NAME
FROM V$CONTAINERS np
WHERE np.CON_ID = tS.con_id) PDBNAME,
(SELECT A.TS#
FROM V$TABLESPACE A
WHERE A.NAME = UPPER(tS.TABLESPACE_NAME)
AND a.CON_ID = tS.con_id) TS#,
ts.TABLESPACE_NAME,
df.all_bytes,
decode(df.TYPE,
'D',
nvl(fs.FREESIZ, 0),
'T',
df.all_bytes - nvl(fs.FREESIZ, 0)) FREESIZ,
df.MAXSIZ,
ts.BLOCK_SIZE,
ts.LOGGING,
ts.FORCE_LOGGING,
ts.CONTENTS,
ts.EXTENT_MANAGEMENT,
ts.SEGMENT_SPACE_MANAGEMENT,
ts.RETENTION,
ts.DEF_TAB_COMPRESSION,
df.ts_df_count
FROM cdb_tablespaces ts,
(SELECT d.CON_ID,
'D' TYPE,
TABLESPACE_NAME,
COUNT(*) ts_df_count,
SUM(BYTES) all_bytes,
SUM(decode(MAXBYTES, 0, BYTES, MAXBYTES)) MAXSIZ
FROM cdb_data_files d
GROUP BY d.CON_ID,
TABLESPACE_NAME
UNION ALL
SELECT d.CON_ID,
'T',
TABLESPACE_NAME,
COUNT(*) ts_df_count,
SUM(BYTES) all_bytes,
SUM(decode(MAXBYTES, 0, BYTES, MAXBYTES))
FROM cdb_temp_files d
GROUP BY d.CON_ID,
TABLESPACE_NAME) df,
(SELECT d.CON_ID,
TABLESPACE_NAME,
SUM(BYTES) FREESIZ
FROM cdb_free_space d
GROUP BY d.CON_ID,
TABLESPACE_NAME
UNION ALL
SELECT d.CON_ID,
tablespace_name,
SUM(d.BLOCK_SIZE * a.BLOCKS) bytes
FROM gv$sort_usage a,
cdb_tablespaces d
WHERE a.tablespace = d.tablespace_name
AND a.CON_ID = d.CON_ID
GROUP BY d.CON_ID,
tablespace_name) fs
WHERE ts.TABLESPACE_NAME = df.TABLESPACE_NAME
AND ts.CON_ID = df.CON_ID
AND ts.TABLESPACE_NAME = fs.TABLESPACE_NAME(+)
AND ts.CON_ID = fs.CON_ID(+))
SELECT T.CON_ID,
(CASE
WHEN T.PDBNAME = LAG(T.PDBNAME, 1)
OVER(PARTITION BY T.PDBNAME ORDER BY TS#) THEN
NULL
ELSE
T.PDBNAME
END) PDBNAME,
TS#,
t.TABLESPACE_NAME TS_Name,
round(t.all_bytes / 1024 / 1024) ts_size_M,
round(t.freesiz / 1024 / 1024) Free_Size_M,
round((t.all_bytes - t.FREESIZ) / 1024 / 1024) Used_Size_M,
round((t.all_bytes - t.FREESIZ) * 100 / t.all_bytes, 3) Used_per,
round(MAXSIZ / 1024 / 1024 / 1024, 3) MAX_Size_g,
round(decode(MAXSIZ,
0,
to_number(NULL),
(t.all_bytes - FREESIZ)) * 100 / MAXSIZ,
3) USED_per_MAX,
round(t.BLOCK_SIZE) BLOCK_SIZE,
t.LOGGING,
t.ts_df_count
FROM wt1 t
UNION ALL
SELECT DISTINCT T.CON_ID,
'' PDBNAME,
to_number('') TS#,
'ALL TS:' TS_Name,
round(SUM(t.all_bytes) / 1024 / 1024, 3) ts_size_M,
round(SUM(t.freesiz) / 1024 / 1024) Free_Size_m,
round(SUM(t.all_bytes - t.FREESIZ) / 1024 / 1024) Used_Size_M,
round(SUM(t.all_bytes - t.FREESIZ) * 100 / SUM(t.all_bytes),
3) Used_per,
round(SUM(MAXSIZ) / 1024 / 1024 / 1024) MAX_Size,
to_number('') "USED,% of MAX Size",
to_number('') BLOCK_SIZE,
'' LOGGING,
to_number('') ts_df_count
FROM wt1 t
GROUP BY rollup(CON_ID,PDBNAME)
)
ORDER BY CON_ID,TS#;
DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id => '&sqlid',
scope => 'COMPREHENSIVE',
time_limit => 600,
task_name => 'whn_sql_test2',
description => 'Task to tune a query on a specified table');
END;
/
exec dbms_sqltune.execute_tuning_task('whn_sql_test2');
--SELECT task_name,status FROM USER_ADVISOR_TASKS WHERE task_name='whn_sql_test2';
set long 99999
set pages 1000 lin 180
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'whn_sql_test2') from DUAL;
exec dbms_sqltune.drop_tuning_task('whn_sql_test2');
按时间模型统计数据库负载历史
select *
from (select a.snap_id,
a.instance_number,
b.begin_interval_time + 0 begin_time,
b.end_interval_time + 0 end_time,
round(value - lag(value, 1, '0')
over(order by a.instance_number, a.snap_id)) "db time"
from (select b.snap_id,
instance_number,
sum(value) / 1000000 / 60 value
from dba_hist_sys_time_model b
where b.dbid = (select dbid from v$database)
and upper(b.stat_name) in upper('DB TIME')
group by b.snap_id, instance_number) a,
dba_hist_snapshot b
where a.snap_id = b.snap_id
and b.dbid = (select dbid from v$database)
and b.instance_number = a.instance_number)
where to_char(begin_time, 'yyyy-mm-dd') >=to_char(sysdate - 7, 'yyyy-mm-dd ')
order by begin_time desc;
--查看一个语句历史执行情况
select (select username from dba_users b where b.user_id = a.user_id) username,
user_id, sql_id, session_id, session_serial# serial#, sql_exec_id, sql_exec_start, count(0),
to_char(max(sample_time), 'yyyymmdd hh24:mi:ss') max_sample_time,
(to_date(to_char(max(sample_time), 'yyyymmdd hh24:mi:ss'),
'yyyymmdd hh24:mi:ss') - sql_exec_start) * 24 * 3600 exec_seconds
from dba_hist_active_sess_history a
where sql_id = '&sqlid'
and to_char(sample_time, 'yyyymmdd hh24:mi') between '20200101 19:00' and '20200101 21:00'
group by user_id, sql_id, session_id, session_serial#, sql_exec_id, sql_exec_start
order by sql_exec_start;
--看回收站对象占用空间大小
with rec_bytes as
(select (sum(space)*(select value from v$parameter where name='db_block_size'))/1024/1024/1024 bytes_in_gb,owner,ts_name
from dba_recyclebin group by owner,ts_name order by 1 desc)
select owner "User",
ts_name "Tablespace Name",
round(bytes_in_gb,1) "Space Consumption(GB)",
(select sum(a.bytes) / 1024/1024/1024
from dba_data_files a
where a.tablespace_name = ts_name) "Size of Tablespace(GB)",
ceil((bytes_in_gb /
(select sum(a.bytes) / 1024/1024/1024
from dba_data_files a
where a.tablespace_name = ts_name)) * 100) "Percent Usage(%)"
from rec_bytes
where bytes_in_gb > 0.1
order by 3 desc;
--查看rman备份
SELECT A.RECID "BACKUP SET",
A.SET_STAMP,
DECODE(B.INCREMENTAL_LEVEL,
'',
DECODE(BACKUP_TYPE, 'L', 'Archivelog', 'Full'),
1,
'Incr-1级',
0,
'Incr-0级',
B.INCREMENTAL_LEVEL) "Type LV",
B.CONTROLFILE_INCLUDED "包含CTL",
DECODE(A.STATUS,
'A',
'AVAILABLE',
'D',
'DELETED',
'X',
'EXPIRED',
'ERROR') "STATUS",
A.DEVICE_TYPE "Device Type",
A.START_TIME "Start Time",
A.COMPLETION_TIME "Completion Time",
A.ELAPSED_SECONDS "Elapsed Seconds",
A.BYTES / 1024 / 1024 / 1024 "Size(G)",
A.COMPRESSED,
A.TAG "Tag",
A.HANDLE "Path"
FROM GV$BACKUP_PIECE A, GV$BACKUP_SET B
WHERE A.SET_STAMP = B.SET_STAMP
AND A.DELETED = 'NO'
oRDER BY A.COMPLETION_TIME DESC;
create or replace function display_raw (rawval raw, type varchar2)
return varchar2
is
cn number;
cv varchar2(32);
cd date;
cnv nvarchar2(32);
cr rowid;
cc char(32);
begin
if (type = 'NUMBER') then
dbms_stats.convert_raw_value(rawval, cn);
return to_char(cn);
elsif (type = 'VARCHAR2') then
dbms_stats.convert_raw_value(rawval, cv);
return to_char(cv);
elsif (type = 'DATE') then
dbms_stats.convert_raw_value(rawval, cd);
return to_char(cd);
elsif (type = 'NVARCHAR2') then
dbms_stats.convert_raw_value(rawval, cnv);
return to_char(cnv);
elsif (type = 'ROWID') then
dbms_stats.convert_raw_value(rawval, cr);
return to_char(cnv);
elsif (type = 'CHAR') then
dbms_stats.convert_raw_value(rawval, cc);
return to_char(cc);
else
return 'UNKNOWN DATATYPE';
end if;
end;
/
select
a.column_name,
a.num_distinct,
display_raw(a.low_value,b.data_type) as low_val,
display_raw(a.high_value,b.data_type) as high_val,
b.data_type
from
dba_tab_col_statistics a, dba_tab_cols b
where
a.owner='SYS' and
a.table_name='T1' and
a.table_name=b.table_name and
a.column_name=b.column_name and
a.column_name = 'CDATE' --ID
and b.owner=a.owner
order by 1, 2;
--主库上监控dest状态
set lin 200 pages 100
col dest_name for a25
col error for a20 trunc
col GAP_STATUS for a12
select DEST_NAME,STATUS,ERROR,TARGET,AFFIRM,COMPRESSION,APPLIED_SCN from v$archive_dest where target='STANDBY';
SELECT DEST_NAME,STATUS,DATABASE_MODE,RECOVERY_MODE,ERROR,GAP_STATUS FROM v$archive_dest_STATUS;
--dg差异
col OPEN_MODE for a10
col PROTECTION_MODE for a20
col DATABASE_ROLE for a18
col SWITCHOVER_STATUS for a17
col thread# for 99
col name for a10
col diff for 9999
set lin 200
select A.THREAD#,C.NAME,C.OPEN_MODE,C.PROTECTION_MODE,C.DATABASE_ROLE,C.SWITCHOVER_STATUS,A.APPLOG,B.NOWLOG, A.APPLOG- B.NOWLOG DIFF from (SELECT THREAD#, MAX(SEQUENCE#) AS "APPLOG" FROM V$ARCHIVED_LOG WHERE APPLIED='YES' and RESETLOGS_CHANGE#=(select RESETLOGS_CHANGE# from v$database) GROUP BY THREAD#) A,(SELECT THREAD#, MAX(SEQUENCE#) AS "NOWLOG" FROM V$LOG GROUP BY THREAD#) B,v$database C where A.THREAD#=B.THREAD#;
set lin 200 pages 100
col PROCESS for a11
col PID for 99999999
col STATUS for a15
col CLIENT_PROCESS for a11
col CLIENT_PID for a11
col GROUP# for a5
col SEQUENCE# for 99999999
col BLOCK# for 99999999
col BLOCKS for 99999999
col DELAY_MINS for 99999999
col THREAD# for 99
select PROCESS,PID,STATUS,CLIENT_PROCESS,CLIENT_PID,GROUP#,THREAD#,SEQUENCE#,BLOCK#,BLOCKS,DELAY_MINS from v$managed_standby;
--sql历史
set lines 200
set pages 1000
col shijian for a12
col execu_d for 999999
col bg_d for 9999999999
col dr_d for 9999999999
col et_d for 99999999
col ct_d for 99999999
col io_time for 999999
col clus_time for 999999
col ap_time for 999999
col cc_time for 999999
col et_onetime for 999999
select to_char(b.END_INTERVAL_TIME, 'yyyymmddhh24') shijian,
plan_hash_value,
sum(a.EXECUTIONS_DELTA) execu_d,
sum(a.BUFFER_GETS_DELTA) bg_d,
sum(a.DISK_READS_DELTA) dr_d,
sum(a.ELAPSED_TIME_DELTA / 1000000) et_d,
sum(a.CPU_TIME_DELTA / 1000000) ct_d,
sum(IOWAIT_DELTA / 1000000) io_time,
sum(CLWAIT_DELTA / 1000000) clus_time,
sum(APWAIT_DELTA / 1000000) ap_time,
sum(ccwait_delta / 1000000) cc_time,
decode(sum(a.EXECUTIONS_DELTA),
0,
sum(a.BUFFER_GETS_DELTA),
round(sum(a.BUFFER_GETS_DELTA) / sum(a.EXECUTIONS_DELTA), 0)) get_onetime,
decode(sum(a.EXECUTIONS_DELTA),
0,
sum(a.rows_processed_delta),
round(sum(a.rows_processed_delta) / sum(a.EXECUTIONS_DELTA), 0)) rows_onetime,
decode(sum(a.EXECUTIONS_DELTA),
0,
sum(a.ELAPSED_TIME_DELTA / 1000),
round(sum(a.ELAPSED_TIME_DELTA / 1000) /
sum(a.EXECUTIONS_DELTA),
0)) et_ms_once
from dba_hist_sqlstat a, dba_hist_snapshot b
where a.SNAP_ID = b.SNAP_ID
and a.INSTANCE_NUMBER = b.INSTANCE_NUMBER
and a.sql_id = '&sql_id'
group by to_char(b.END_INTERVAL_TIME, 'yyyymmddhh24'),plan_hash_value
order by 1,2;
undefine sql_id;
阅读(1737) | 评论(0) | 转发(0) |
