搜索到
12
篇与
的结果
-
锁等待及阻塞查询 锁等待及阻塞查询锁等待查询数据库等待时间和实际执行时间的相对百分比select * from v$sysmetric a where a.METRIC_NAME in ('Database CPU Time Ratio', 'Database Wait Time Ratio') and a.INTSIZE_CSEC = (select max(intsize_csec) from v$sysmetric);查询数据库中过去30分钟引起最多等待的sql语句select ash.USER_ID, u.username, sum(ash.WAIT_TIME) ttl_wait_time, s.SQL_TEXT from v$active_session_history ash, v$sqlarea s, dba_users u where ash.SAMPLE_TIME between sysdate - 30 / 2880 and sysdate and ash.SQL_ID = s.SQL_ID and ash.USER_ID = u.user_id group by ash.USER_ID, s.SQL_TEXT, u.username order by ttl_wait_time desc;查询数据库中的等待事件select event, count(*) from v$session_wait group by event order by count(*) desc;查询数据库过去15分钟最重要的等待事件select ash.EVENT, sum(ash.WAIT_TIME + ash.TIME_WAITED) total_wait_time from v$active_session_history ash where ash.SAMPLE_TIME between sysdate - 15 / 2880 and sysdate group by event order by total_wait_time desc;在过去15分钟哪些用户经历了等待select s.SID, s.USERNAME, sum(ash.WAIT_TIME + ash.TIME_WAITED) total_wait_time from v$active_session_history ash, v$session s where ash.SAMPLE_TIME between sysdate - 30 / 2880 and sysdate and ash.SESSION_ID = s.SID group by s.SID, s.USERNAME order by total_wait_time desc;查询等待时间最长的对象select a.CURRENT_OBJ#, d.object_name, d.object_type, a.EVENT, sum(a.WAIT_TIME + a.TIME_WAITED) total_wait_time from v$active_session_history a, dba_objects d where a.SAMPLE_TIME between sysdate - 30 / 2880 and sysdate and a.CURRENT_OBJ# = d.object_id group by a.CURRENT_OBJ#, d.object_name, d.object_type, a.EVENT order by total_wait_time desc;查询过去15分钟等待时间最长的sql语句select a.USER_ID, u.username, s.SQL_TEXT, sum(a.WAIT_TIME + a.TIME_WAITED) total_wait_time from v$active_session_history a, v$sqlarea s, dba_users u where a.SAMPLE_TIME between sysdate - 30 / 2880 and sysdate and a.SQL_ID = s.SQL_ID and a.USER_ID = u.user_id group by a.USER_ID, s.SQL_TEXT, u.username order by total_wait_time desc;哪些SQL消耗更多的IOselect * from (select s.PARSING_SCHEMA_NAME, s.DIRECT_WRITES, substr(s.SQL_TEXT, 1, 500), s.DISK_READS from v$sql s order by s.DISK_READS desc) where rownum < 20查看哪些会话正在等待IO资源SELECT username, program, machine, sql_id FROM V$SESSION WHERE EVENT LIKE 'db file%read';查看正在等待IO资源的对象SELECT d.object_name, d.object_type, d.owner FROM V$SESSION s, dba_objects d WHERE EVENT LIKE 'db file%read' and s.ROW_WAIT_OBJ# = d.object_id;查看redo日志切换频率Select round(FIRST_TIME, 'DD'), THREAD#, Count(SEQUENCE#) From v$log_history Group By round(FIRST_TIME, 'DD'), THREAD# Order By 1, 2 SELECT trunc(first_time) "Date", to_char(first_time, 'Dy') "Day", count(1) "Total", SUM(decode(to_char(first_time, 'hh24'),'00',1,0)) "h0", SUM(decode(to_char(first_time, 'hh24'),'01',1,0)) "h1", SUM(decode(to_char(first_time, 'hh24'),'02',1,0)) "h2", SUM(decode(to_char(first_time, 'hh24'),'03',1,0)) "h3", SUM(decode(to_char(first_time, 'hh24'),'04',1,0)) "h4", SUM(decode(to_char(first_time, 'hh24'),'05',1,0)) "h5", SUM(decode(to_char(first_time, 'hh24'),'06',1,0)) "h6", SUM(decode(to_char(first_time, 'hh24'),'07',1,0)) "h7", SUM(decode(to_char(first_time, 'hh24'),'08',1,0)) "h8", SUM(decode(to_char(first_time, 'hh24'),'09',1,0)) "h9", 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" FROM V$log_history group by trunc(first_time), to_char(first_time, 'Dy') Order by 1;查询阻塞和被阻塞的session，支持RAC，按instance分组SELECT a.INST_ID, a.sid, a.SERIAL#, a.USERNAME, a.SQL_ID, a.PROGRAM, a.EVENT, a.BLOCKING_SESSION, a.WAIT_TIME_MICRO FROM GV$SESSION A WHERE (A.INST_ID, a.SID) in (select b.BLOCKING_INSTANCE, b.BLOCKING_SESSION from gv$session b) union all select a.INST_ID, a.sid, a.SERIAL#, a.USERNAME, a.SQL_ID, a.PROGRAM, a.EVENT, a.BLOCKING_SESSION, a.WAIT_TIME_MICRO FROM GV$SESSION A where a.BLOCKING_SESSION is not null order by BLOCKING_SESSION nulls first;最近30分钟内ASH采样到的等待事件排名，支持RAC,按instance分组select * from (select inst_id, rank() over(partition by inst_id order by cnt desc) rk, event, cnt from (select ash.INST_ID, ash.EVENT, count(*) CNT from gv$active_session_history ash where ash.SAMPLE_TIME > sysdate - 1 / 24 / 60 * 30 and event is not null group by ash.INST_ID, ash.EVENT)) where rk <= 10;某段事件内ASM采样到的等待事件排名，并查询采样中，产生此等待事件的SQL，支持rac，按 instance分组select a.inst_id, a.event, a.sql_id, a.sql_cnt, b.cnt event_cnt, sql_rk from (select inst_id, event, sql_id, sql_cnt, rank() over(partition by event order by sql_cnt desc) sql_rk from (select ash.INST_ID, ash.EVENT, ash.SQL_ID, count(*) sql_cnt from gv$active_session_history ash where to_char(ash.SAMPLE_TIME, 'YYYY-MM-DD HH24:MI:SS') > '2021-07-25 14:00:00' AND to_char(ash.SAMPLE_TIME, 'YYYY-MM-DD HH24:MI:SS') < '2021-07-25 16:00:00' AND EVENT IS NOT NULL GROUP BY ASH.INST_ID, ASH.EVENT, ASH.SQL_ID)) A, (SELECT INST_ID, EVENT, CNT FROM (SELECT INST_ID, rank() over(partition by INST_ID order by cnt desc) rk, EVENT, CNT FROM (SELECT ASH.INST_ID, ASH.EVENT, COUNT(*) CNT FROM GV$ACTIVE_SESSION_HISTORY ASH where to_char(ash.SAMPLE_TIME, 'YYYY-MM-DD HH24:MI:SS') > '2021-07-25 14:00:00' AND to_char(ash.SAMPLE_TIME, 'YYYY-MM-DD HH24:MI:SS') < '2021-07-25 16:00:00' AND EVENT IS NOT NULL GROUP BY ASH.INST_ID, ASH.EVENT)) WHERE RK <= 3) B WHERE A.INST_ID = B.INST_ID AND A.EVENT = B.EVENT AND A.SQL_RK <= 5 ORDER BY INST_ID, EVENT_CNT DESC, SQL_CNT DESC, SQL_RK;查看过去15天sql执行的hash_plan_id和资源消耗统计变化select sql_id, endtime, plan_hash_value, nvl2(exed, exed, 0) "执行次数", nvl2(ems, ems, 0) "单次执行时间(ms)", nvl2(cms, cms, 0) "平均CCWAIT(ms)", nvl2(ams, ams, 0) "平均APWAIT(ms)", nvl2(clms, clms, 0) "平均CMWAIT(ms)", nvl2(ioms, ioms, 0) "平均IOWAIT(ms)", nvl2(ctms, ctms, 0) "平均CPU_WAIT(ms)", nvl2(bfgets, bfgets, 0) "平均buffer gets", nvl2(drd, drd, 0) "平均disk reads", nvl2(sd, sd, 0) "平均sort", nvl2(fet, fet, 0) "平均fetch", nvl2(rpd, rpd, 0) "平均rows process", snap_id from (select a.sql_Id, to_char(b.end_interval_time, 'yyyymmdd hh24:mi:ss') endtime, a.executions_delta exed, a.plan_hash_value, round(decode(a.executions_delta,0,0,a.elapsed_time_delta / a.executions_delta) / 1000,2) ems, round(decode(a.executions_delta,0,0,a.ccwait_delta / a.executions_delta) / 1000,2) cms, round(decode(a.executions_delta,0,0,a.apwait_delta / a.executions_delta) / 1000,2) ams, round(decode(a.executions_delta,0, 0,a.clwait_delta / a.executions_delta) / 1000,2) clms, round(decode(a.executions_delta,0, 0,a.iowait_delta / a.executions_delta) / 1000,2) ioms, round(decode(a.executions_delta, 0,0, a.cpu_time_delta / a.executions_delta) / 1000,2) ctms, round(decode(a.executions_delta, 0, 0,a.buffer_gets_delta / a.executions_delta),2) bfgets, round(decode(a.executions_delta,0, 0, a.disk_reads_delta / a.executions_delta),2) drd, round(decode(a.executions_delta,0,0, a.sorts_delta / a.executions_delta),2) sd, round(decode(a.executions_delta,0, 0,a.fetches_delta / a.executions_delta),2) fet, round(decode(a.executions_delta,0,0, a.rows_processed_delta / a.executions_delta), 4) rpd, b.snap_id from dba_hist_sqlstat a, dba_hist_snapshot b where sql_id = '62qcz0vz20a3t' and a.snap_id = b.snap_id and a.instance_number = b.instance_number --and b.instance_number=1 and a.executions_delta <> 0 and b.end_interval_time > sysdate - 15) order by snap_id;查询某个会话的锁select /*+rule*/SESSION_ID,OBJECT_ID,ORACLE_USERNAME,OS_USER_NAME,PROCESS,LOCKED_MODE from gv$locked_object where session_id=&sid;查询TMTX锁select /*+rule*/* from v$lock where ctime >100 and type in ('TX','TM') order by 3,9;查询数据库中的锁select /*+rule*/s.sid,p.spid,l.type,round(max(l.ctime)/60,0) lock_min,s.sql_id,s.USERNAME,b.owner,b.object_type,b.object_name from v$session s, v$process p,v$lock l,v$locked_object o,dba_objects b where o.SESSION_ID=s.sid and s.sid=l.sid and o.OBJECT_ID=b.OBJECT_ID and s.paddr = p.addr and l.ctime >100 and l.type in ('TX','TM','FB') group by s.sid,p.spid,l.type,s.sql_id,s.USERNAME,b.owner,b.object_type,b.object_name order by 9,1,3,4; # 说明： # dba_hist_active_sess_history/v$active_session_history查看SQL执行历史情况 # v$session_wait/v$session_event查看数据库会话的等待事件 # v$sga_target_advice数据库sga优化顾问视图 # v$pga_target_advice数据库pga优化顾问视图 # v$memory_target_advice内存优化顾问视图 # dba_data_files查询表空间和对应数据文件 # v$session_longops记录执行超过5s的sql，可以查询一个SQL语句执行了多长时间以及还要执行多长时间 # v$fast_start_transactions/v$fast_start_servers可以查看所有回滚的事务 # v$log视图可以查找到联机重做日志的日志序列号v$archived_log视图或 # v$log_history视图中可以查找给定的归档重做日志的日志序列号 # user_tab_columns视图将显示当前用户模式下的每张表的每一列的详细数据情况 # gv$memory_dynamic_components记录内存变更的视图 # gv$sql_monitor SQL监视视图 # dba_tab_statistics表的信息统计**提供运维人员使用，查看长时间持有锁并阻塞其他会话session，生成处理语句SELECT '锁源session: '||A_S.SID||','||A_S.SERIAL#||',@'||A_S.INST_ID||', '||A_S.SCHEMANAME||', From: '||A_S.OSUSER||','||A_S.MACHINE||', '||A_S.PROGRAM||', Cur_SQL: '||A_S.SQL_ID||',Prev_SQL: '|| A_S.PREV_SQL_ID||', Status:'||A_S.STATUS||', Lock_Time: '||A.CTIME||'s.'||CHR(10)|| ' -> 等待锁session:'||B.SID||','||B_S.INST_ID||','||B_S.MACHINE||','||B_S.EVENT||', Blocked_SQL: '||B_S.SQL_ID||', Locked_ON: '||OBJ.OWNER||'.'||OBJ.OBJECT_NAME||', Lock_Mode: '||A.TYPE||','|| DECODE(A.LMODE, 0, '0,none', 1, '1,NULL', 2, '2,row-S(SS)', 3, '3,row-X(SX)', 4, '4,share(S)', 5, '5,S/Row-X(SSX)', 6, '6,exclusive(X)')||CHR(10)|| ' --> 查询锁定数据SQL: '||(DECODE(OBJ.OBJECT_TYPE, 'TABLE', 'SELECT * FROM '||OBJ.OWNER||'.'||OBJ.OBJECT_NAME||' WHERE ROWID = '''|| DBMS_ROWID.ROWID_CREATE(1, OBJ.DATA_OBJECT_ID, B_S.ROW_WAIT_FILE#, B_S.ROW_WAIT_BLOCK#, B_S.ROW_WAIT_ROW#)||''';', NULL))||CHR(10)|| ' --> 查询锁源操作SQL: SELECT DISTINCT ASH.SESSION_ID, ASH.SESSION_SERIAL#, ASH.MACHINE, ASH.PROGRAM, ASH.XID, ASH.SAMPLE_TIME, ASH.SQL_ID , ASH.SQL_OPNAME, ASH.SQL_EXEC_START, ASH.EVENT, Q.SQL_TEXT FROM GV$ACTIVE_SESSION_HISTORY ASH JOIN GV$SQL Q ON ASH.INST_ID=q.INST_ID AND ASH.SQL_ID = q.SQL_ID and ASH.SQL_PLAN_HASH_VALUE=q.PLAN_HASH_VALUE WHERE ASH.SESSION_ID = '|| A_S.SID ||' AND ASH.SESSION_SERIAL# = '|| A_S.SERIAL# ||' AND ASH.INST_ID = '|| A_S.INST_ID ||' ORDER BY 1, 2, 3, 4, 5, 6;'||CHR(10)|| ' --> 查询相关SQL文本: SELECT DISTINCT SQL_ID,SQL_TEXT FROM GV$SQL WHERE SQL_ID IN ('''||A_S.SQL_ID||''','''||A_S.PREV_SQL_ID||''','''||B_S.SQL_ID||''');'||CHR(10)|| ' --> 清理session命令: alter system disconnect session '''||A_S.SID||','||A_S.SERIAL#||',@'||A_S.INST_ID||''' immediate;' AS BLOCK_DETAIL FROM GV$LOCK A, GV$LOCK B, GV$SESSION A_S, GV$SESSION B_S, DBA_OBJECTS OBJ, DBA_USERS U WHERE A.ID1 = B.ID1 AND A.ID2 = B.ID2 AND A_S.USER#=U.USER_ID AND U.ORACLE_MAINTAINED='N' /* 排除Oracle内部账户持有的锁 */ AND A.CTIME >= 30 /* 持有锁时间超过 30s */ AND A.BLOCK > 0 /* 阻塞了其他的会话 */ AND B.REQUEST > 0 AND A_S.TYPE='USER' AND A.SID = A_S.SID AND A.INST_ID = A_S.INST_ID AND B.SID = B_S.SID AND B.INST_ID = B_S.INST_ID AND A_S.SID = B_S.FINAL_BLOCKING_SESSION AND A_S.INST_ID = B_S.FINAL_BLOCKING_INSTANCE AND B_S.ROW_WAIT_OBJ# = OBJ.OBJECT_ID(+) ORDER BY A.INST_ID, A.SID; set pagesize 1000 linesize 1000; select count(*), machine, program, username, osuser from gv$session group by machine, program, username, osuser order by count(*) desc; set pagesize 1000 linesize 1000; select count(*) from gv$session;TOP20慢查询语句SELECT * FROM ( SELECT s.sql_text, s.sql_fulltext, s.sql_id, s.executions exectimes, ROUND(s.elapsed_time / 1000000, 2) exectime, ROUND(s.elapsed_time / 1000000 / s.executions, 2) avgexetime, s.first_load_time fathercreatetime, s.parsing_user_id userid, u.username username FROM v$sqlarea s LEFT JOIN all_users u ON s.parsing_user_id = u.user_id WHERE s.executions > 0 AND u.username = 'HISUSER' ORDER BY avgexetime DESC) WHERE rownum <= 20;数据库历史阻塞事件查询根据awr报告来观察某一时间段主要的等待事件select event,blocking_session,sql_id,count(*) from dba_hist_active_sess_history ash where sample_time>=to_timestamp('2023-12-20 10:00:00','yyyy-mm-dd hh24:mi:ss') and sample_time<=to_timestamp('2023-12-20 11:08:00','yyyy-mm-dd hh24:mi:ss') and event='SQL*Net message from client' group by event,blocking_session,sql_id;根据阻塞的SQL_ID查询到具体的SQL语句select sql_text from v$sql where sql_id='7521kc2bkpdh9';根据阻塞的SQL_ID查询其他更多的信息SELECT DISTINCT s.inst_id, s.machine, s.LOGON_TIME, s.PREV_EXEC_START FROM gv$session s WHERE s.sql_id = '03ukf2c56gs54' order BY s.PREV_EXEC_START; #=====================================# SELECT DISTINCT s.inst_id, s.machine, i.instance_name, i.host_name FROM gv$session s JOIN gv$instance i ON s.inst_id = i.inst_id WHERE s.sql_id = '03ukf2c56gs54'; #=====================================# SELECT DISTINCT s.inst_id, s.machine, i.instance_name, i.host_name, sys_context('USERENV', 'IP_ADDRESS') AS client_ip FROM gv$session s JOIN gv$instance i ON s.inst_id = i.inst_id WHERE s.sql_id = '03ukf2c56gs54'; #=====================================# SELECT DISTINCT s.machine FROM gv$session s JOIN v$sql q ON s.sql_id = q.sql_id WHERE q.sql_id = '11xapz031xfr4' AND s.username IS NOT NULL AND s.logon_time >= TO_DATE('2023-10-08 22:20:00', 'yyyy-mm-dd hh24:mi:ss') AND s.logon_time <= TO_DATE('2023-10-08 22:50:00', 'yyyy-mm-dd hh24:mi:ss'); #=====================================# SELECT DISTINCT s.machine FROM gv$session s WHERE s.sql_id = '11xapz031xfr4' AND s.logon_time <= TO_DATE('2023-10-08 22:30:00', 'yyyy-mm-dd hh24:mi:ss') AND (s.sid, s.serial#) IN ( SELECT sid, serial# FROM gv$session WHERE end_time IS NULL OR end_time >= TO_TIMESTAMP('2023-10-08 22:30:00', 'yyyy-mm-dd hh24:mi:ss') ); #=====================================# SELECT DISTINCT s.machine FROM gv$session s WHERE s.sql_id = '11xapz031xfr4' AND s.logon_time <= TO_TIMESTAMP('2023-10-08 22:30:00', 'yyyy-mm-dd hh24:mi:ss') AND (s.sid, s.serial#) IN ( SELECT sid, serial# FROM gv$session WHERE end_time IS NULL OR end_time >= TO_TIMESTAMP('2023-10-08 22:30:00', 'yyyy-mm-dd hh24:mi:ss') ); #=====================================# SELECT s.SID, s.SERIAL#, s.USERNAME, s.STATUS, s.OSUSER, s.MACHINE, s.PROGRAM, q.SQL_TEXT FROM v$session s JOIN v$sql q ON s.SQL_ID = q.SQL_ID WHERE s.SID = 1723;
-
Oracle表空间操作 Oracle查询表空间使用率Oracle 数据库的表空间如果使用超过100%，会导致数据库死机通过 sql 查询当前数据库所有表空间的使用率：set pagesize 1000 linesize 1000; col TABLESPACE_NAME for a40; select tbs_used_info.tablespace_name, tbs_used_info.alloc_mb, tbs_used_info.used_mb, tbs_used_info.max_mb, tbs_used_info.free_of_max_mb, tbs_used_info.used_of_max || '%' used_of_max_pct from (select a.tablespace_name, round(a.bytes_alloc / 1024 / 1024) alloc_mb, round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024) used_mb, round((a.bytes_alloc - nvl(b.bytes_free, 0)) * 100 / a.maxbytes) used_of_max, round((a.maxbytes - a.bytes_alloc + nvl(b.bytes_free, 0)) / 1048576) free_of_max_mb, round(a.maxbytes / 1048576) max_mb from (select f.tablespace_name, sum(f.bytes) bytes_alloc, sum(decode(f.autoextensible, 'YES', f.maxbytes, 'NO', f.bytes)) maxbytes from dba_data_files f group by tablespace_name) a, (select f.tablespace_name, sum(f.bytes) bytes_free from dba_free_space f group by tablespace_name) b where a.tablespace_name = b.tablespace_name(+)) tbs_used_info order by tbs_used_info.used_of_max desc;查询表空间使用的数据文件SELECT tablespace_name, file_id, file_name, bytes, blocks FROM dba_data_files WHERE tablespace_name = 'table_spaceName';给表空间增加数据文件ALTER TABLESPACE table_spaceName ADD DATAFILE 'DatafilePath' SIZE 1G AUTOEXTEND ON NEXT 1G MAXSIZE 31G;temp数据文件处理# 查询tempfile SELECT name, bytes/1024/1024 AS "MB" FROM v$tempfile ORDER BY bytes; # 修改位置 alter database rename file '+DATA/xxx.dbf' to 'xxx'; # 模糊查询 select file_id,file_name,bytes/1024/1024 MB,status,tablespace_name from dba_temp_files where file_name like '%temp%';