select sq.SQL_TEXT, s.SID from v$sql sq, v$session s where sq.SQL_ID = s.SQL_ID and s.USERNAME = ‘UFIDA55_WYG1225’;
select s.sid, s.*
from v$session s where s.USERNAME = ‘UFIDA55_WYG1225’
where s.SADDR in (‘C0000002F94E4500’);
select sq.SQL_TEXT,s.session_id,s.sql_id
from v$active_session_history s, v$sql sq
where sq.SQL_ID = s.SQL_ID
and s.SESSION_ID = 4240 –sid
order by s.SAMPLE_TIME desc;
select * from v$sql s where s.SQL_ID = ‘5qcygm1d3wqp5’;
SELECT * FROM V$SQL_BIND_CAPTURE S WHERE S.SQL_ID=’5qcygm1d3wqp5′;
SELECT ROUND((F.BYTES_FREE + F.BYTES_USED) / 1024 / 1024, 2) “total MB”,
ROUND(((F.BYTES_FREE + F.BYTES_USED) – NVL(P.BYTES_USED, 0)) / 1024 / 1024,
2) “Free MB”,
D.FILE_NAME “Datafile_name”,
ROUND(NVL(P.BYTES_USED, 0) / 1024 / 1024, 2) “Used MB”,
ROUND((F.BYTES_FREE + F.BYTES_USED) / 1024, 2) “total KB”,
ROUND(((F.BYTES_FREE + F.BYTES_USED) – NVL(P.BYTES_USED, 0)) / 1024,
2) “Free KB”,
ROUND(NVL(P.BYTES_USED, 0) / 1024, 2) “Used KB”
FROM SYS.V_$TEMP_SPACE_HEADER F,
DBA_TEMP_FILES D,
SYS.V_$TEMP_EXTENT_POOL P
WHERE F.TABLESPACE_NAME(+) = D.TABLESPACE_NAME
AND F.FILE_ID(+) = D.FILE_ID
AND P.FILE_ID(+) = D.FILE_ID;
select blocks * 8192 / 1024 / 1024 used_temp_data_MB,s.*
from v$tempseg_usage s
order by s.blocks desc;
——–数据库排队Sql监控———-
select last_call_et,
— v.USERNAME,
s.CPU_TIME,
s.ELAPSED_TIME,
— v.PROGRAM,
‘kill -9 ‘ || p.spid,
‘alter system kill session ”’ || v.sid || ‘,’ || v.serial# || ”’ immediate;’,
— v.CLIENT_INFO,
s.SQL_TEXT,
–s.SQL_FULLTEXT,
v.SQL_HASH_VALUE,
v.MACHINE,
v.TERMINAL,
s.DISK_READS,s.BUFFER_GETS,s.SORTS,s.SHARABLE_MEM,s.PERSISTENT_MEM,s.RUNTIME_MEM,s.ROWS_PROCESSED
from v$session v, v$process p, v$sql s
where v.last_call_et > 0
and v.status = ‘ACTIVE’
and v.username != ‘SYS’
and p.addr = v.paddr
and s.ADDRESS = v.SQL_ADDRESS
and s.HASH_VALUE = v.SQL_HASH_VALUE
order by last_call_et desc;
——————–
Select Sw.Event, Sw.Sid, Sw.Wait_Time, Sw.Seconds_In_Wait,
Chr(Bitand(P1, -16777216) / 16777215) || Chr(Bitand(P1, 16711680) / 65535) “Lock”, Bitand(P1, 65535) “Lock Mode#”,
Decode(Bitand(P1, 65535), 0, ‘None’, /* Mon Lock equivalent */ 1, ‘Null’, /* N */ 2, ‘Row-S (SS)’, /* L */ 3,
‘Row-X (SX)’, /* R */ 4, ‘Share’, /* S */ 5, ‘S/Row-X (SSX)’, /* C */ 6, ‘Exclusive’, /* X */
To_Char(Bitand(P1, 65535))) “Lock Mode”
From Gv$session_Wait Sw
Where Event Like ‘%enqueue%’ Or Event Like ‘%enq%’;
–查询表的存储空间大小
SELECT A.TABLESPACE_NAME,
ALL_SPACE “all space(M)”,
FREE_SPACE “free space(M)”,
ROUND(FREE_SPACE / ALL_SPACE, 2) “rate”
FROM (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / 1048576, 0) AS ALL_SPACE
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) A,
(SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / 1048576, 0) AS FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME;
–查询表占空间大小
SELECT TABLE_NAME, BLOCKS * 8192 / 1024 / 1024 AS “OCCU_SPACE(M)”, NUM_ROWS
FROM USER_TABLES
WHERE BLOCKS IS NOT NULL
ORDER BY BLOCKS DESC;
–SQL共享池容量
select sum(bytes) sharepool from sys.v$sgastat where pool=’shared pool’;
–查看SQL共享池命中率
select (sum(pins-reloads)) / sum(pins) “Lib Cache” from v$librarycache;
–sql的可重用性
select round(sum(users_opening)/count(*),2),– ‘Avg users Per Statement: ‘,
round(sum(executions)/count(*),2) –‘Avg Executions Per Statement’
from v$sqlarea;
–查询data buffer命中率
select 1-((physical.value-direct.value-lobs.value)/logical.value)
“Buffer Cache Hit Ratio”
from V$SYSSTAT physical,V$SYSSTAT direct,V$SYSSTAT lobs,V$SYSSTAT logical
where physical.name=’physical reads’ and direct.name=’physical reads direct’
and lobs.name=’physical reads direct (lob)’ and logical.name=’session logical reads’;
–如何监控 SGA 中字典缓冲区的命中率？
select parameter, gets,Getmisses , getmisses/(gets+getmisses)*100 “miss ratio”,
(1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 “Hit ratio”
from v$rowcache
where gets+getmisses <>0
group by parameter, gets, getmisses;
–监控 SGA 中重做日志缓存区的命中率，应该小于1%
SELECT name, gets, misses, immediate_gets, immediate_misses,
Decode(gets,0,0,misses/gets*100) ratio1,
Decode(immediate_gets+immediate_misses,0,0,
immediate_misses/(immediate_gets+immediate_misses)*100) ratio2
FROM v$latch WHERE name IN (‘redo allocation’, ‘redo copy’);
–监控内存和硬盘的排序比率，最好使它小于 .10，增加 sort_area_size
SELECT name, value FROM v$sysstat WHERE name IN (‘sorts (memory)’, ‘sorts (disk)’);
–如何监控 SGA 的命中率？
select a.value + b.value “logical_reads”, c.value “phys_reads”,
round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) “BUFFER HIT RATIO”
from v$sysstat a, v$sysstat b, v$sysstat c
where a.statistic# = 38 and b.statistic# = 39
and c.statistic# = 40;
–如何回滚段的争用情况？
select name, waits, gets, waits/gets “Ratio”
from v$rollstat C, v$rollname D
where C.usn = D.usn;
–回滚段等待次数
select name,gets,waits,xacts– ‘active Transactions’
from v$rollname a, v$roustat b
where a.asn=b.usn;
–监控 MTS
select busy/(busy+idle) “shared servers busy” from v$dispatcher;
–此值大于0.5时，参数需加大
select sum(wait)/sum(totalq) “dispatcher waits” from v$queue where type=’dispatcher’;
select count(*) from v$dispatcher;
select servers_highwater from v$mts;
–servers_highwater接近mts_max_servers时，参数需加大
–查詢鎖的狀況的對象有?
V$LOCK, V$LOCKED_OBJECT, V$SESSION, V$SQLAREA, V$PROCESS ;
查詢鎖的表的方法:
SELECT S.SID SESSION_ID, S.USERNAME, DECODE(LMODE, 0, ‘None’, 1, ‘Null’, 2, ‘Row-S (SS)’, 3, ‘Row-X (SX)’, 4, ‘Share’, 5, ‘S/Row-X (SSX)’, 6, ‘Exclusive’, TO_CHAR(LMODE)) MODE_HELD, DECODE(REQUEST, 0, ‘None’, 1, ‘Null’, 2, ‘Row-S (SS)’, 3, ‘Row-X (SX)’, 4, ‘Share’, 5, ‘S/Row-X (SSX)’, 6, ‘Exclusive’, TO_CHAR(REQUEST)) MODE_REQUESTED, O.OWNER||’.’||O.OBJECT_NAME||’ (‘||O.OBJECT_TYPE||’)’, S.TYPE LOCK_TYPE, L.ID1 LOCK_ID1, L.ID2 LOCK_ID2 FROM V$LOCK L, SYS.DBA_OBJECTS O, V$SESSION S WHERE L.SID = S.SID AND L.ID1 = O.OBJECT_ID ;
–如何解鎖?
ALTER SYSTEM KILL SESSION ‘SID,SERIR#’;
–如何查询做比较大的排序的进程？
SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#,
a.username, a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr
ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks ;
–如何查询做比较大的排序的进程的SQL语句？
select /*+ ORDERED */ sql_text from v$sqltext a
where a.hash_value = (
select sql_hash_value from v$session b
where b.sid = &sid and b.serial# = &serial)
order by piece asc ;
–检查表空间的使用情况
SELECT tablespace_name, max_m, count_blocks free_blk_cnt, sum_free_m,to_char(100*sum_free_m/sum_m, ‘99.99’) || ‘%’ AS pct_free
FROM ( SELECT tablespace_name,sum(bytes)/1024/1024 AS sum_m FROM dba_data_files GROUP BY tablespace_name),
( SELECT tablespace_name AS fs_ts_name, max(bytes)/1024/1024 AS max_m, count(blocks) AS count_blocks, sum(bytes/1024/1024) AS sum_free_m FROM dba_free_space GROUP BY tablespace_name )
–如何查看各个表空间占用磁盘情况？
SQL> col tablespace format a20
select
b.file_id 文件ID号,
b.tablespace_name 表空间名,
b.bytes 字节数,
(b.bytes-sum(nvl(a.bytes,0))) 已使用,
sum(nvl(a.bytes,0)) 剩余空间,
sum(nvl(a.bytes,0))/(b.bytes)*100 剩余百分比
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_id,b.bytes
order by b.file_id
–检查剩余表空间
SELECT tablespace_name, sum ( blocks ) as free_blk ,
trunc ( sum ( bytes ) /(1024*1024) ) as free_m,
max ( bytes ) / (1024) as big_chunk_k, count (*) as num_chunks
FROM dba_free_space GROUP BY tablespace_name;
–扩表空间
alter tablespace <name> add datafile ‘<file>’ size <size>
–查看最大的表所占空间
select owner,table_name, NUM_ROWS, BLOCKS*8192/(1024*1024) SizeM, EMPTY_BLOCKS, LAST_ANALYZED
from dba_tables order by 1/(BLOCKS+1);
—-查看最大的表记录数
select table_name from user_tables where num_rows>5000000;
–怎样计算一个表占用的空间的大小？
select owner,table_name,
NUM_ROWS,
BLOCKS*NUM_ROWS/1024/1024 “Size M”,
EMPTY_BLOCKS,
LAST_ANALYZED
from dba_tables
where table_name=’IRP_POLICYBILL’;
SELECT * FROM dba_tables ;
–如何查看碎片程度高的表?
SELECT segment_name table_name , COUNT(*) extents
FROM dba_segments WHERE owner NOT IN (‘SYS’, ‘SYSTEM’) GROUP BY segment_name
HAVING COUNT(*) = (SELECT MAX( COUNT(*) ) FROM dba_segments GROUP BY segment_name);
–重建索引
alter index index_test rebuild online；
alter table iface_exchange rebuild on line;
–清空表空间碎片
alter tablespace 表空间名 coalese;
–清空索引碎片
alter index indexname coalese;
–重建索引
validate index indexname;
alter index indexname rebuild;
–数据库的行连接数
select value from v$sysstat
where name=’table fetch continued row’
–检查无效的数据库对象
SELECT owner, object_name, object_type FROM dba_objects WHERE status=’INVALID’;
–检查不起作用的约束
SELECT owner, constraint_name, table_name, constraint_type, status
FROM dba_constraints WHERE status=’DISABLED’ AND constraint_type=’P’;
–检查无效的trigger
SELECT owner, trigger_name, table_name, status FROM dba_triggers WHERE status = ‘DISABLED’;
–检查数据文件的状态记录状态不是“online”的数据文件，并做恢复。
Select file_name from dba_data_files where status=’OFFLINE’
–怎样查得数据库的SID
select name from v$database;
–字符集登记信息存储在ORACLE数据库字典的V$NLS_PARAMETERS表中
select * from V$NLS_PARAMETERS;
–查看名称包含log字符的表
select object_name,object_id from user_objects
where instr(object_name,’LOG’)>0
–在oracle9i中有一个基表存放的是所有的oracle的隐藏参数：
select * from x$ksppi;
–查询是否是集群(PARALLE=yes,是集群)
select instance_number,instance_name,host_name,PARALLEL from v$instance;
show parameter clust
–何查詢SERVER是不是OPS?
SELECT * FROM V$OPTION;
如果PARALLEL SERVER=TRUE則有OPS能
–如何查看最大会话数？
SELECT * FROM V$PARAMETER WHERE NAME LIKE ‘proc%’;
–show parameter processes
–曾经到达的最大会话数(sessions_highwater)
select * from v$license;
–更新整个数据库统计数据
execte DBMS_STATS.gather_database_stats;
execte dbms_stats.gather_scheme_stats(模式名)
–分析表的统计
analyze table <name> estimate statistics sample 50 percent;
–如何检查是否安装了某个patch?
check that oraInventory
–如何将小表放入keep池中?
alter table xxx storage(buffer_pool keep);
–如把ORACLE设置为MTS或专用模式？
#dispatchers=”(PROTOCOL=TCP) (SERVICE=SIDXDB)”
加上就是MTS，注释就是专用模式，SID是指你的实例名。
Top 10 by Buffer Gets:
set linesize 100
set pagesize 100
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
buffer_gets, executions, buffer_gets/executions “Gets/Exec”,
hash_value,address
FROM V$SQLAREA
WHERE buffer_gets > 10000
ORDER BY buffer_gets DESC)
WHERE rownum <= 10
;
–Top 10 by Physical Reads:
set linesize 100;
set pagesize 100;
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
disk_reads, executions, disk_reads/executions “Reads/Exec”,
hash_value,address
FROM V$SQLAREA
WHERE disk_reads > 1000
ORDER BY disk_reads DESC)
WHERE rownum <= 10
;
–Top 10 by Executions:
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
executions, rows_processed, rows_processed/executions “Rows/Exec”,
hash_value,address
FROM V$SQLAREA
WHERE executions > 100
ORDER BY executions DESC)
WHERE rownum <= 10
;
–Top 10 by Parse Calls:
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
parse_calls, executions, hash_value,address
FROM V$SQLAREA
WHERE parse_calls > 1000
ORDER BY parse_calls DESC)
WHERE rownum <= 10
;
–Top 10 by Sharable Memory:
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
sharable_mem, executions, hash_value,address
FROM V$SQLAREA
WHERE sharable_mem > 1048576
ORDER BY sharable_mem DESC)
WHERE rownum <= 10
;
–Top 10 by Version Count:
–set linesize 100;
–set pagesize 100;
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
version_count, executions, hash_value,address
FROM V$SQLAREA
WHERE version_count > 20
ORDER BY version_count DESC)
WHERE rownum <= 10
相关文章