一、几个查询
- 什么是 SS 锁 以及 enq: SS - contention 等待事件
- 查询告警相关信息
select * from v$lock_type where type='SS';
- 查询等待事件信息
select * from v$event_name where name='enq: SS - contention';
- 查询活动会话
- 查询活动会话信息，获取堵塞信息
SELECT INST_ID,
SID
|| ','
|| SERIAL# SID#,
MACHINE,
USERNAME,
TYPE
EVENT,
STATE,
WAIT_TIME_MICRO,
BLOCKING_INSTANCE
|| ( CASE
WHEN BLOCKING_INSTANCE IS NULL THEN NULL
ELSE ','
END )
|| BLOCKING_SESSION BL_SESS,
FINAL_BLOCKING_INSTANCE
|| ( CASE
WHEN FINAL_BLOCKING_INSTANCE IS NULL THEN NULL
ELSE ','
END )
|| FINAL_BLOCKING_SESSION FI_BL_SESS,
FINAL_BLOCKING_SESSION_STATUS,
STATUS,
COMMAND,
SQL_ID,
SQL_CHILD_NUMBER,
PREV_SQL_ID,
PREV_CHILD_NUMBER,
WAIT_CLASS,
WAIT_TIME,
SECONDS_IN_WAIT,
TIME_REMAINING_MICRO,
TIME_SINCE_LAST_WAIT_MICRO,
P1,
P1RAW,
P2,
P2RAW,
P3,
P3RAW,
PADDR,
TADDR,
SADDR,
OSUSER,
PROCESS,
CREATOR_ADDR,
AUDSID,
ROW_WAIT_OBJ#,
ROW_WAIT_FILE#,
ROW_WAIT_BLOCK#,
ROW_WAIT_ROW#,
PROGRAM,
COMMAND,
PLSQL_ENTRY_OBJECT_ID,
PLSQL_ENTRY_SUBPROGRAM_ID,
PLSQL_OBJECT_ID,
PLSQL_SUBPROGRAM_ID,
SQL_EXEC_START,
LOGON_TIME,
CLIENT_IDENTIFIER,
SERVICE_NAME,
RESOURCE_CONSUMER_GROUP
FROM GV$SESSION
WHERE WAIT_CLASS# <> 6
ORDER BY BLOCKING_INSTANCE NULLS FIRST,
BLOCKING_SESSION,
EVENT,
INST_ID,
SID;
- 查询sql temp信息
SELECT instance_number,
sample_time,
session_id
|| ( CASE
WHEN session_id IS NULL THEN NULL
ELSE ','
END )
|| session_serial#
|| ( CASE
WHEN session_serial# IS NULL THEN NULL
ELSE ','
END )
|| instance_number sid#,
du.username,
sql_id,
sql_opname,
sql_exec_start,
plsql_entry_object_id,
event,
blocking_session
|| ( CASE
WHEN blocking_session IS NULL THEN NULL
ELSE ','
END )
|| blocking_session_serial#
|| ( CASE
WHEN blocking_session_serial# IS NULL THEN NULL
ELSE ','
END )
|| blocking_inst_id blk_sid,
program,
machine
|| ':'
|| port,
delta_read_io_bytes,
delta_write_io_bytes,
pga_allocated / 1024 / 1024 pga_mb,
temp_space_allocated / 1024 / 1024 temp_MB
FROM dba_hist_active_sess_history ash,
dba_users du
WHERE sample_time >= To_timestamp ('20190909 1720', 'yyyymmdd hh24mi')
AND sample_time <= To_timestamp ('20190909 1730', 'yyyymmdd hh24mi')
AND temp_space_allocated IS NOT NULL
AND ash.user_id = du.user_id
ORDER BY temp_space_allocated DESC;
- 查询占用temp 较大的sql
SELECT sql_id,
Avg (temp_space_allocated / 1024 / 1024 / 1024) temp_gb
FROM dba_hist_active_sess_history ash,
dba_users du
WHERE sample_time >= To_timestamp ('20190909 1720', 'yyyymmdd hh24mi')
AND sample_time <= To_timestamp ('20190909 1730', 'yyyymmdd hh24mi')
AND temp_space_allocated IS NOT NULL
AND ash.user_id = du.user_id
GROUP BY sql_id
ORDER BY temp_gb DESC;
- 查询sql的具体的信息
- 查询包、存储、函数等的具体信息
select * from dba_source where name='&name';
- 查看对象信息
select * from dba_objects where object_id='&obj_id';
- 查看sql信息
select * from v$sql where sql_id='&sql_id';
- 查询用户及其temp信息
select * from dba_users where temporary_tablespace='&tsp';
- 查询块大小
select value from v$parameter where name='db_block_size';
select block_size from dba_tablespaces where tablespace_name='&tbs_name';
- 查询 temp 表空间大小
- 查询 temp 表空间总大小
SELECT tablespace_name,
Sum (BYTES) / 1024 / 1024 / 1024 gb
FROM dba_temp_files
GROUP BY tablespace_name;
SELECT tablespace_name,
Sum (bytes_used) / 1024 / 1024 / 1024 used_gb,
Sum (blocks_used) used_blocks,
Sum (bytes_free) / 1024 / 1024 / 1024 free_gb,
Sum (blocks_free) free_blocks
FROM v$temp_space_header
GROUP BY tablespace_name;
- 查看 temp 在各节点分布使用情况
不带百分数版
SELECT inst_id,
tablespace_name,
Sum (extents_cached) total_extents,
Sum (extents_used) used_extents,
Sum(blocks_cached) total_blocks,
Sum(blocks_used) used_blocks,
Sum (bytes_cached) / 1024 / 1024 / 1024 cached_gb,
Sum (bytes_used) / 1024 / 1024 / 1024 used_gb
FROM gv$temp_extent_pool
GROUP BY inst_id,
tablespace_name
ORDER BY inst_id,
tablespace_name;
SELECT inst_id,
tablespace_name,
total_extents,
used_extents,
total_blocks,
used_blocks
FROM gv$sort_segment
带百分数版(总利用率)
SELECT c.tablespace_name,
c.bytes / 1024 / 1024 / 1024 total_size_gb,
d.bytes_used / 1024 / 1024 / 1024 used_size_gb,
( c.bytes - d.bytes_used ) / 1024 / 1024 / 1024 free_size_gb,
d.bytes_used * 100 / c.bytes percent_usage
FROM (SELECT tablespace_name,
Sum(bytes) bytes
FROM dba_temp_files
GROUP BY tablespace_name) c,
(SELECT tablespace_name,
Sum(bytes_used) bytes_used
FROM gv$temp_extent_pool
GROUP BY tablespace_name) d
WHERE c.tablespace_name = d.tablespace_name;
SELECT a.tablespace_name,
a.total_size_gb,
b.used_size_gb,
a.total_size_gb - b.used_size_gb free_size_gb,
b.used_size_gb / a.total_size_gb * 100 AS percent_usage
FROM (SELECT tablespace_name,
Sum(bytes) / 1024 / 1024 / 1024 total_size_gb
FROM dba_temp_files
GROUP BY tablespace_name) a,
(SELECT s_s.tablespace_name,
Sum(used_blocks * block_size) / 1024 / 1024 / 1024 used_size_gb
FROM gv$sort_segment s_s,
dba_tablespaces tbs
WHERE tbs.tablespace_name = s_s.tablespace_name
AND tbs.contents = 'TEMPORARY'
GROUP BY s_s.tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name;
- 带百分数版(各节点利用率)
SELECT inst_id,
tablespace_name,
Sum (bytes_cached) / 1024 / 1024 / 1024 total_size_gb,
Sum (bytes_used) / 1024 / 1024 / 1024 used_size_gb,
Sum (bytes_cached - bytes_used) / 1024 / 1024 / 1024 free_size_gb,
Sum(bytes_used) * 100 / Sum(bytes_cached) percent_usage
FROM gv$temp_extent_pool
GROUP BY inst_id,
tablespace_name
ORDER BY tablespace_name,
inst_id;
SELECT inst_id,
gs.tablespace_name,
total_blocks * block_size / 1024 / 1024 / 1024 total_size_gb,
used_blocks * block_size / 1024 / 1024 / 1024 used_size_gb,
( total_blocks - used_blocks ) * block_size / 1024 / 1024 / 1024 free_size_gb,
used_blocks * 100 / total_blocks percent_usage
FROM gv$sort_segment gs,
dba_tablespaces dt
WHERE gs.tablespace_name = dt.tablespace_name
ORDER BY tablespace_name,
inst_id;
