SQL语句合集.doc

静态数据字典这类数据字典主要是由表和视图组成，应该注意的是，数据字典中的表是不能直接被访问的，但是可以访问数据字典中的视图。静态数据字典中的视图分为三类，它们分别由三个前缀够成：user_*、 all_*、 dba_*。 user_*该视图存储了关于当前用户所拥有的对象的信息。（即所有在该用户模式下的对象） all_*该试图存储了当前用户能够访问的对象的信息。（与user_*相比，all_* 并不需要拥有该对象，只需要具有访问该对象的权限即可）。 dba_*该视图存储了数据库中所有对象的信息。（前提是当前用户具有访问这些数据库的权限，一般来说必须具有管理员权限） 从上面的描述可以看出，三者之间存储的数据肯定会有重叠，其实它们除了访问范围的不同以外（因为权限不一样，所以访问对象的范围不一样），其他均具有一致性。具体来说，由于数据字典视图是由SYS（系统用户）所拥有的，所以在却省情况下，只有SYS和拥有DBA系统权限的用户可以看到所有的视图。没有DBA权限的用户只能看到user_*和all_*视。如果没有被授予相关的SELECT权限的话，他们是不能看到 dba_*视图的。 1常用监控库高速（Library Cache）缓存SELECT SUM(pins),SUM(reloads) FROM v$librarycache;如果sum(reload)/sum(pins)0，说明Library Cache的命中率比较合适，若大于1，则需要增加共享池（SHAREDPOOLSIZE）的大小（在初始化参数文件中）。数据字典高速缓存（Dictionary Cache）SELECT (1-(SUM(getmisses)/(SUM(gets)+SUM(getmisses)*100FROM v$rowcache;数据字典高速缓存包括了有关数据库的结构、用户、实体信息等。数据字典的命中率对系统有很大的影响。命中率的计算中，getmisses 表示失败次数，gets表示成功次数。如果该值90%，说明命中率合适。否则，应增大共享池的大小。数据库缓冲区高速缓存SELECT name,value FROM V$SYSSTATWHERE name IN (dbblock gets,consistent gets,physical reads);Oracle 在运行期间向数据库高速缓存读写数据，高速缓存命中表示信息已在内存中，高速缓存失败意味着Oracle必需进行磁盘I/O。保持高速缓存失败率最小的关键是确保高速缓存的大小。初始化参数DBBLOCKBUFFERS控制数据库缓冲区高速缓存的大小。可通过查询V$SYSSTAT命中率，以确定是否应当增加DBBLOCKBUFFERS的值。通过查询结果 命中率=1-physical reads/(dbblock gets+consistent gets) 如果命中率 0 AND Loads 1 AND Kept = NO ORDER BY Owner, Namespace, TYPE, Executions DESC; SELECT TYPE, COUNT(*) FROM V$db_Object_Cache GROUP BY TYPE;查看系统请求情况 SELECT Decode(NAME, summed dirty write queue length, VALUE) / Decode(NAME, write requests, VALUE) Write Request Length FROM V$sysstat WHERE NAME IN (summed dirty queue length, write requests) AND VALUE 0;查询磁盘I/O情况SELECT name,phyrds,phywrts FROM v$datafile df,v$filestat fsWHERE df.file# =fs.file#;磁盘I/O是系统性能的瓶颈，解决好磁盘I/O，可明显提高性能。通过查询V$FILESTAT可以知道每个物理文件的使用频率（phyrds表示每个数据文件读的次数，phywrts表示每个数据文件写的次数）对于使用频率较高的物理文件,可以采用以下策略: 将I/O尽可能平均分配在尽可能多的磁盘上。 为表和索引建立不同的表空间。 将数据文件与重做日志文件分离在不同的磁盘上。 减少不经Oracle SERVER的磁盘I/O。 调整竞争 当多个进程对相同的资源发出申请时，产生竞争。 修改Process参数 该参数定义可以同时连接到Oracle数据库的最大进程数，缺省值为50。注意，Oracle的后台进程也包括在此数目中，建议将该值改为200。 减少调度进程的竞争 减少调度进程的竞争，通过查询v$dispatcher表来判定调度进程的竞争SQLSELECT network ,sum(busy)/sum(busy)+sum(idle) FROM v$dispatcherGROUP BY network; 如果某种协议忙的比率超过50%，应增加MTSDISPATCHERS的值。 减少多线程服务进程竞争 首先查询V$SYSSTAT表判定是否发生多线程服务进程竞争:SQLSELECT DECODE(totalq,0,No request,wait/totalq|hunderths of seconds) FROM v$sysstatWHERE type=common; 如果共享服务进程数量已达到初始化参数文件中MTSMAXSERVERS指定的最大值，但应用运行时，平均请求等待时间仍持续增长，那么，应加大MTSMAXSERVERS的值。 减少重做日志缓冲区竞争 通过查询V$SYSSTAT表判定redo log 文件缓冲区是否足够。SQLSELECT name,value FROM v$sysstatWHERE name=redo log space request; 此处value的值应接近于0，否则，应增大初始化参数文件的LOGBUFFEQS的值。 减少回退段竞争 回退段对性能也有影响，根据事物大小情况来分配合适的回退段。 首先判定回退段的数量能否满足系统运行的需要： 查询V$WAITSTAT表与V$SYSSTAT表SQLSELECT class,count FROM v$waitstatWHERE class IN (system undo header,system undo block,undo header,undo block);SQLSELECT sum(value)FROM v$sysstat WHERE name IN (db block gets,consistent gets); 如果任何一个class/sum(value)10%,那么考虑增加回退段。回退段的数量一般按如下规律设定：用户数 回退段个数 n164 16N32832SELECT class,count FROM v$waitstatWHERE class=free list;SQLSELECT sum(value)FROM v$sysstatWHERE name IN (db block gets,consistent gets); 如果class/sum(value)1%，则应增加该表的Free List 的值。求数据文件的I/O分布 SELECT Df.NAME, Phyrds, Phywrts, Phyblkrd, Phyblkwrt, Singleblkrds, Readtim, Writetim FROM V$filestat Fs, V$dbfile Df WHERE Fs.File# = Df.File# ORDER BY Df.NAME;求DISK READ较多的SQL SELECT St.Sql_Text FROM V$sql s, V$sqltext St WHERE s.Address = St.Address AND s.Hash_Value = St.Hash_Value AND s.Disk_Reads 300;求DISK SORT严重的SQL SELECT Sess.Username, SQL.Sql_Text, Sort1.Blocks FROM V$session Sess, V$sqlarea SQL, V$sort_Usage Sort1 WHERE Sess.Serial# = Sort1.Session_Num AND Sort1.Sqladdr = SQL.Address AND Sort1.Sqlhash = SQL.Hash_Value AND Sort1.Blocks 200;捕捉运行很久的SQL column username format a12 column opname format a16 column progress format a8 SELECT Username, Sid, Opname, Round(Sofar * 100 / Totalwork, 0) | % AS Progress, Time_Remaining, Sql_Text FROM V$session_Longops, V$sql WHERE Time_Remaining 0 AND Sql_Address = Address AND Sql_Hash_Value = Hash_Value;查看用户所有对象使用空间select t.segment_type,sum(bytes/1024/1024) from user_segments Tgroup by t.segment_type生成所有索引重新建立语句select alter index |index_name| rebuild online; from user_indexes t order by t.index_name查看当前锁定对象select b.owner,b.object_name,l.session_id,l.locked_modefrom v$locked_object l, dba_objects bwhere b.object_id=l.object_id查看所有动态视图select * from v$fixed_table;监控索引是否使用 alter index &index_name monitoring usage; alter index &index_name nomonitoring usage; select * from v$object_usage where index_name = &index_name;查看还没提交的事务 select * from v$locked_object; select * from v$transaction;回滚段查看 SELECT Rownum, Sys.Dba_Rollback_Segs.Segment_Name NAME, V$rollstat.Extents Extents, V$rollstat.Rssize Size_In_Bytes, V$rollstat.Xacts Xacts, V$rollstat.Gets Gets, V$rollstat.Waits Waits, V$rollstat.Writes Writes, Sys.Dba_Rollback_Segs.Status Status FROM V$rollstat, Sys.Dba_Rollback_Segs, V$rollname WHERE V$rollname.NAME(+) = Sys.Dba_Rollback_Segs.Segment_Name AND V$rollstat.Usn(+) = V$rollname.Usn ORDER BY Rownum查看某些用户的hash SELECT a.Username, COUNT(b.Hash_Value) Total_Hash, COUNT(b.Hash_Value) - COUNT(UNIQUE(b.Hash_Value) Same_Hash, (COUNT(UNIQUE(b.Hash_Value) / COUNT(b.Hash_Value) * 100 u_Hash_Ratio FROM Dba_Users a, V$sqlarea b WHERE a.User_Id = b.Parsing_User_Id GROUP BY a.Username;查看undo段的使用情况 SELECT d.Segment_Name, Extents, Optsize, Shrinks, Aveshrink, Aveactive, d.Status FROM V$rollname n, V$rollstat s, Dba_Rollback_Segs d WHERE d.Segment_Id = n.Usn(+) AND d.Segment_Id = s.Usn(+);求归档日志的切换频率(生产系统可能时间会很长) SELECT Start_Recid, Start_Time, End_Recid, End_Time, Minutes FROM (SELECT Test.*, Rownum AS Rn FROM (SELECT b.Recid Start_Recid, To_Char(b.First_Time, yyyy-mm-dd hh24:mi:ss) Start_Time, a.Recid End_Recid, To_Char(a.First_Time, yyyy-mm-dd hh24:mi:ss) End_Time, Round(a.First_Time - b.First_Time) * 24) * 60, 2) Minutes FROM V$log_History a, V$log_History b WHERE a.Recid = b.Recid + 1 AND b.First_Time SYSDATE - 1 ORDER BY a.First_Time DESC) Test) y WHERE y.Rn (SELECT To_Number(VALUE) * 0.05 FROM V$parameter WHERE NAME = db_block_buffers);求buffer cache中的块信息 SELECT o.Object_Type, Substr(o.Object_Name, 1, 10) Objname, b.Objd, b.Status, COUNT(b.Objd) FROM V$bh b, Dba_Objects o WHERE b.Objd = o.Data_Object_Id AND o.Owner = &owner GROUP BY o.Object_Type, o.Object_Name, b.Objd, b.Status;求日志文件的空间使用 SELECT Le.Leseq Current_Log_Sequence#,100 * Cp.Cpodr_Bno / Le.Lesiz Percentage_Full FROM X$kcccp Cp, X$kccle Le WHERE Le.Leseq = Cp.Cpodr_Seq;求等待中的对象 SELECT /*+rule */ s.Sid, s.Username, w.Event, o.Owner, o.Segment_Name, o.Segment_Type, o.Partition_Name, w.Seconds_In_Wait Seconds, w.State FROM V$session_Wait w, V$session s, Dba_Extents o WHERE w.Event IN (SELECT NAME FROM V$event_Name WHERE Parameter1 = file# AND Parameter2 = block# AND NAME NOT LIKE control%) AND o.Owner sys AND w.Sid = s.Sid AND w.P1 = o.File_Id AND w.P2 = o.Block_Id AND w.P2 10; alter tablespace name coalesce; alter table name deallocate unused; create or replace view ts_blocks_v as SELECT Tablespace_Name, Block_Id, Bytes, Blocks, free space Segment_Name FROM Dba_Free_Space UNION ALL SELECT Tablespace_Name, Block_Id, Bytes, Blocks, Segment_Name FROM Dba_Extents; select * from ts_blocks_v; SELECT Tablespace_Name, SUM(Bytes), MAX(Bytes), COUNT(Block_Id) FROM Dba_Free_Space GROUP BY Tablespace_Name;查看数据表信息select * from cat; select * from tab; select table_name from user_tables; select table_name from user_tables;select table_name from user_tables;查看数据视图信息select text from user_views where view_name=upper(&view_name); 索引： select index_name,table_owner,table_name,tablespace_name,status from user_indexes order by table_name; 触发器： select trigger_name,trigger_type,table_owner,table_name,status from user_triggers; 快照： select owner,name,master,table_name,last_refresh,next from user_snapshots order by owner,next; 同义词： select * from syn; 序列： select * from seq; 数据库链路： select * from user_db_links; 约束限制： select TABLE_NAME,CONSTRAINT_NAME,SEARCH_CONDITION,STATUS from user_constraints WHERE TABLE_name=upper(&TABLE_Name); 本用户读取其他用户对象的权限： select * from user_tab_privs; 本用户所拥有的系统权限： select * from user_sys_privs; 用户： select * from all_users order by user_id; 表空间剩余自由空间情况： select tablespace_name,sum(bytes) 总字节数,max(bytes),count(*) from dba_free_space group by tablespace_name； 数据字典： select table_name from dict order by table_name; 锁及资源信息： select * from v$lock;不包括DDL锁 数据库字符集： select name,value$ from props$ where name=NLS_CHARACTERSET; inin.ora参数： select name,value from v$parameter order by name; SQL共享池： select sql_text from v$sqlarea; 数据库： select * from v$database 控制文件： select * from V$controlfile; 重做日志文件信息： select * from V$logfile; 来自控制文件中的日志文件信息： select * from V$log; 来自控制文件中的数据文件信息： select * from V$datafile; NLS参数当前值： select * from V$nls_parameters; ORACLE版本信息： select * from v$version; 描述后台进程： select * from v$bgprocess; 查看版本信息： select * from product_component_version;监控事例的等待： select event,sum(decode(wait_time,0,0,1) prev, sum(decode(wait_time,0,1,0) curr,count(*)from v$session_wait group by event order by 4;回滚段的争用情况：select name,waits,gets,waits/gets ratio from v$rollstat a,v$rollname b where a.usn=b.usn;监控表空间的I/O比例：select df.tablespace_name name,df.file_name file,f.phyrds pyr,f.phyblkrd pbr,f.phywrts pyw,f.phyblkwrt pbw from v$filestat f,dba_data_files dfwhere f.file#=df.file_id监空文件系统的I/O比例：select substr(a.file#,1,2) #,substr(,1,30) name,a.status,a.bytes,b.phyrds,b.phywrtsfrom v$datafile a,v$filestat bwhere a.file#=b.file#在某个用户下找所有的索引：select user_indexes.table_name, user_indexes.index_name,uniqueness, column_namefrom user_ind_columns, user_indexeswhere user_ind_columns.index_name = user_indexes.index_nameand user_ind_columns.table_name = user_indexes.table_name order by user_indexes.table_type, user_indexes.table_name,user_indexes.index_name, column_position;监控 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 cwhere a.statistic# = 38 and b.statistic# = 39 and c.statistic# = 40; 监控 SGA 中字典缓冲区的命中率select parameter, gets,Getmisses , getmisses/(gets+getmisses)*100 miss ratio,(1-(sum(getmisses)/ (sum(gets)+sum(getmisses)*100 Hit ratiofrom v$rowcache where gets+getmisses 0group by parameter, gets, getmisses; 监控 SGA 中共享缓存区的命中率应该小于1%select sum(pins) Total Pins, sum(reloads) Total Reloads,sum(reloads)/sum(pins)