项目现场常用Oracle语句

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

Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *