ITPub博客

首页 > 数据库 > Oracle > SQL语句大全—查看表空间(二)

SQL语句大全—查看表空间(二)

原创 Oracle 作者:polalisi 时间:2007-10-16 13:46:31 0 删除 编辑

数据库各个表空间增长情况的检查:
SQL>SELECT A.TABLESPACE_NAME,(
1-(A.TOTAL)/B.TOTAL)*100 USED_PERCENT
FROM (SELECT TABLESPACE_NAME,SUM(BYTES) TOTAL FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) A,(SELECT TABLESPACE_NAME,SUM(BYTES) TOTAL FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) B WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME;

SQL>SELECT UPPER(F.TABLESPACE_NAME)
"表空间名",
D.TOT_GROOTTE_MB
"表空间大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES
"已使用空间(M)", TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100, 2), '990.99') "使用比",F.TOTAL_BYTES"空闲空间(M)",
F.MAX_BYTES "
最大块(M)" FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (
1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (
1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,ROUND(SUM(DD.BYTES) / (
1024 * 1024), 2) TOT_GROOTTE_MB FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY
4 DESC;
查看各个表空间占用磁盘情况:
SQL>COL TABLESPACE_NAME FORMAT A20;
SQL>SELECT B.FILE_ID FILE_ID,
B.TABLESPACE_NAME TABLESPACE_NAME,
B.BYTES BYTES,
(B.BYTES-SUM(NVL(A.BYTES,
0))) USED,
SUM(NVL(A.BYTES,
0)) FREE,
SUM(NVL(A.BYTES,
0))/(B.BYTES)*100 PERCENT
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;
数据库对象下一扩展与表空间的free扩展值的检查:
SQL>SELECT A.TABLE_NAME, A.NEXT_EXTENT, A.TABLESPACE_NAME
FROM ALL_TABLES A,(SELECT TABLESPACE_NAME, MAX(BYTES) AS BIG_CHUNK
FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME ) F WHERE F.TABLESPACE_NAME = A.TABLESPACE_NAME AND A.NEXT_EXTENT > F.BIG_CHUNK
UNION SELECT A.INDEX_NAME, A.NEXT_EXTENT, A.TABLESPACE_NAME
FROM ALL_INDEXES A,(SELECT TABLESPACE_NAME, MAX(BYTES) AS BIG_CHUNK
FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME ) F WHERE F.TABLESPACE_NAME = A.TABLESPACE_NAME AND A.NEXT_EXTENT > F.BIG_CHUNK;
Disk Read最高的SQL语句的获取:
SQL>SELECT SQL_TEXT FROM (SELECT * FROM V$SQLAREA ORDER BY DISK_READS)
WHERE ROWNUM<=
5;
查找前十条性能差的sql
SELECT * FROM (SELECT PARSING_USER_ID
EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,
SQL_TEXT FROM V$SQLAREA ORDER BY DISK_READS DESC)
WHERE ROWNUM<
10 ;
等待时间最多的5个系统等待事件的获取:
SQL>SELECT * FROM (SELECT * FROM V$SYSTEM_EVENT WHERE EVENT NOT LIKE
'SQL%' ORDER BY TOTAL_WAITS DESC) WHERE ROWNUM<=5;
查看当前等待事件的会话:
COL USERNAME FORMAT A10
SET LINE
120
COL EVENT FORMAT A30
SELECT SE.SID,S.USERNAME,SE.EVENT,SE.TOTAL_WAITS,SE.TIME_WAITED,SE.AVERAGE_WAIT
FROM V$SESSION S,V$SESSION_EVENT SE WHERE S.USERNAME IS NOT NULL AND SE.SID=S.SID
AND S.STATUS=
'ACTIVE' AND SE.EVENT NOT LIKE '%SQL*NET%';

SELECT SID, EVENT, P1, P2, P3, WAIT_TIME, SECONDS_IN_WAIT, STATE FROM V$SESSION_WAIT WHERE EVENT NOT LIKE
'%MESSAGE%' AND EVENT NOT LIKE 'SQL*NET%' AND EVENT NOT LIKE '%TIMER%' AND EVENT != 'WAKEUP TIME MANAGER';
找到与所连接的会话有关的当前等待事件:

SELECT SW.SID,S.USERNAME,SW.EVENT,SW.WAIT_TIME,SW.STATE,SW.SECONDS_IN_WAIT SEC_IN_WAIT
FROM V$SESSION S,V$SESSION_WAIT SW WHERE S.USERNAME IS NOT NULL AND SW.SID=S.SID
AND SW.EVENT NOT LIKE
'%SQL*NET%' ORDER BY SW.WAIT_TIME DESC;
Oracle所有回滚段状态的检查:

SQL>SELECT SEGMENT_NAME,OWNER,TABLESPACE_NAME,INITIAL_EXTENT,NEXT_EXTENT,DBA_ROLLBACK_SEGS.STATUS FROM DBA_ROLLBACK_SEGS,V$DATAFILE WHERE FILE_ID=FILE#;
Oracle回滚段扩展信息的检查:
COL NAME FORMAT A10
SET LINESIZE
140
SELECT SUBSTR(NAME,
1,40) NAME,EXTENTS,RSSIZE,OPTSIZE,AVEACTIVE,EXTENDS,WRAPS,SHRINKS,HWMSIZE
FROM V$ROLLNAME RN,V$ROLLSTAT RS WHERE (RN.USN=RS.USN);

EXTENTS
:回滚段中的盘区数量。Rssize:以字节为单位的回滚段的尺寸。optsize:为optimal参数设定的值。Aveactive:从回滚段中删除盘区时释放的以字节为单位的平均空间的大小。Extends:系统为回滚段增加的盘区的次数。Shrinks:系统从回滚段中清除盘区(即回滚段收缩)的次数。回滚段每次清除盘区时,系统可能会从这个回滚段中消除一个或多个盘区。Hwmsize:回滚段尺寸的上限,即回滚段曾经达到的最大尺寸。(如果回滚段平均尺寸接近OPTIMAL的值,那么说明OPTIMAL的值设置正确,如果回滚段动态增长次数或收缩次数很高,那么需要提高OPTIMAL的值)

查看回滚段的使用情况,哪个用户正在使用回滚段的资源:
SELECT S.USERNAME, U.NAME FROM V$TRANSACTION T,V$ROLLSTAT R,
V$ROLLNAME U,V$SESSION S WHERE S.TADDR=T.ADDR AND
T.XIDUSN=R.USN AND R.USN=U.USN ORDER BY S.USERNAME;
如何查看一下某个shared_server正在忙什么:
SELECT A.USERNAME,A.MACHINE,A.PROGRAM,A.SID,
A.SERIAL#,A.STATUS,C.PIECE,C.SQL_TEXT
FROM V$SESSION A,V$PROCESS B,V$SQLTEXT C
WHERE B.SPID=
13161 AND B.ADDR=A.PADDR
AND A.SQL_ADDRESS=C.ADDRESS(+) ORDER BY C.PIECE;
数据库共享池性能检查:
SELECT NAMESPACE,GETS,GETHITRATIO,PINS,PINHITRATIO,RELOADS,INVALIDATIONS FROM V$LIBRARYCACHE WHERE NAMESPACE IN(
'SQLAREA','TABLE/PROCEDURE','BODY','TRIGGER');
检查数据重载比率:
SELECT SUM(RELOADS)/SUM(PINS)*
100 "RELOAD RATIO" FROM
V$LIBRARYCACHE;
检查数据字典的命中率:
SELECT
1-SUM(GETMISSES)/SUM(GETS) "DATA DICTIONARY HIT
RATIO" FROM V$ROWCACHE;
(对于library cache, gethitratiopinhitratio应该大于90%,对于数据重载比率,reload ratio应该小于1%,对于数据字典的命中率,data dictionary hit ratio应该大于85%)

检查共享内存的剩余情况:
SELECT REQUEST_MISSES, REQUEST_FAILURES FROM V$SHARED_POOL_RESERVED;
(对于共享内存的剩余情况, request_misses request_failures应该接近0)

数据高速缓冲区性能检查:
SELECT
1-P.VALUE/(B.VALUE+C.VALUE) "DB BUFFER CACHE HIT RATIO" FROM V$SYSSTAT P,V$SYSSTAT B,V$SYSSTAT C WHERE P.NAME='PHYSICAL READS' AND B.NAME='DB BLOCK GETS' AND C.NAME='CONSISTENT GETS';
检查buffer pool HIT_RATIO执行
SELECT NAME, (PHYSICAL_READS/(DB_BLOCK_GETS+CONSISTENT_GETS)) "MISS_HIT_RATIO" FROM V$BUFFER_POOL_STATISTICS WHERE (DB_BLOCK_GETS+ CONSISTENT_GETS)>
0;(正常时db buffer cache hit ratio 应该大于90%,正常时buffer pool MISS_HIT_RATIO 应该小于10%)

数据库回滚段性能检查:检查Ratio执行
SELECT SUM(WAITS)*
100 /SUM(GETS) "RATIO", SUM(WAITS) "WAITS", SUM(GETS) "GETS" FROM V$ROLLSTAT;
检查count/value执行:
SELECT CLASS,COUNT FROM V$WAITSTAT WHERE CLASS LIKE
'%UNDO%';
SELECT VALUE FROM V$SYSSTAT WHERE NAME=
'CONSISTENT GETS';(两者的value值相除)
检查average_wait执行:
SELECT EVENT,TOTAL_WAITS,TIME_WAITED,AVERAGE_WAIT FROM V$SYSTEM_EVENT WHERE EVENT LIKE
'%UNDO%';
检查RBS header get ratio执行:
SELECT N.NAME,S.USN,S.WRAPS, DECODE(S.WAITS,
0,1,1- S.WAITS/S.GETS)"RBS HEADER GET RATIO" FROM V$ROLLSTAT S,V$ROLLNAME N WHERE S.USN=N.USN;(正常时Ratio应该小于1%, count/value应该小于0.01%,average_wait最好为0,该值越小越好,RBS header get ratio应该大于95%)

杀会话的脚本:
SELECT A.SID,B.SPID,A.SERIAL#,A.LOCKWAIT,A.USERNAME,A.OSUSER,A.LOGON_TIME,A.LAST_CALL_ET/
3600 LAST_HOUR,A.STATUS, 'ORAKILL '||SID||' '||SPID HOST_COMMAND,'ALTER SYSTEM KILL SESSION '''||A.SID||','||A.SERIAL#||'''' SQL_COMMAND FROM V$SESSION A,V$PROCESS B WHERE A.PADDR=B.ADDR AND SID>6;
查看排序段的性能:
SQL>SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME IN (
'SORTS (MEMORY)', 'SORTS (DISK)');
7、查看数据库库对象:
SELECT OWNER, OBJECT_TYPE, STATUS, COUNT(*) COUNT# FROM ALL_OBJECTS GROUP BY OWNER, OBJECT_TYPE, STATUS;
8、查看数据库的版本: 
SELECT * FROM V$VERSION;
9、查看数据库的创建日期和归档方式:
SELECT CREATED, LOG_MODE, LOG_MODE FROM V$DATABASE;

10、捕捉运行很久的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;
11、查看数据表的参数信息:
SELECT PARTITION_NAME, HIGH_VALUE, HIGH_VALUE_LENGTH, TABLESPACE_NAME,PCT_FREE, PCT_USED, INI_TRANS, MAX_TRANS, INITIAL_EXTENT,NEXT_EXTENT, MIN_EXTENT, MAX_EXTENT, PCT_INCREASE, FREELISTS,FREELIST_GROUPS, LOGGING, BUFFER_POOL, NUM_ROWS, BLOCKS,EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN, SAMPLE_SIZE,LAST_ANALYZED FROM DBA_TAB_PARTITIONS
--WHERE TABLE_NAME = :TNAME AND TABLE_OWNER = :TOWNER
ORDER BY PARTITION_POSITION;
12、查看还没提交的事务:
SELECT * FROM V$LOCKED_OBJECT;
SELECT * FROM V$TRANSACTION;
13、查找object为哪些进程所用:
SELECT P.SPID,S.SID,S.SERIAL# SERIAL_NUM,S.USERNAME USER_NAME,
A.TYPE OBJECT_TYPE,S.OSUSER OS_USER_NAME,A.OWNER,A.OBJECT OBJECT_NAME,DECODE(SIGN(
48 - COMMAND),1,
TO_CHAR(COMMAND),
'ACTION CODE #' || TO_CHAR(COMMAND) ) ACTION,
P.PROGRAM ORACLE_PROCESS,S.TERMINAL TERMINAL,S.PROGRAM PROGRAM,S.STATUS SESSION_STATUS FROM V$SESSION S, V$ACCESS A, V$PROCESS P WHERE S.PADDR = P.ADDR AND S.TYPE =
'USER' AND A.SID = S.SID AND A.OBJECT='SUBSCRIBER_ATTR'ORDER BY S.USERNAME, S.OSUSER;
14、查看回滚段:
SQL>COL NAME FORMAT A10
SQL>SET LINESIZE
100
SQL>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;
15、耗资源的进程(top session):
SELECT S.SCHEMANAME SCHEMA_NAME,DECODE(SIGN(
48 - COMMAND), 1, TO_CHAR(COMMAND), 'ACTION CODE #' || TO_CHAR(COMMAND) ) ACTION,STATUS SESSION_STATUS,S.OSUSER OS_USER_NAME,S.SID,P.SPID,S.SERIAL# SERIAL_NUM,NVL(S.USERNAME,'[ORACLE PROCESS]') USER_NAME,S.TERMINAL TERMINAL,S.PROGRAM PROGRAM,ST.VALUE CRITERIA_VALUE FROM V$SESSTAT ST,V$SESSION S,V$PROCESS P WHERE ST.SID = S.SID AND ST.STATISTIC# = TO_NUMBER('38') AND ('ALL'='ALL' OR S.STATUS ='ALL') AND P.ADDR=S.PADDR ORDER BY ST.VALUE DESC,P.SPID ASC,S.USERNAME ASC,S.OSUSER ASC;
根据PID查找相应的语句:

SELECT A.USERNAME, A.MACHINE,A.PROGRAM,A.SID,A.SERIAL#,A.STATUS,C.PIECE,C.SQL_TEXT FROM V$SESSION A,V$PROCESS B,V$SQLTEXT C WHERE B.SPID=SPID AND B.ADDR=A.PADDR AND A.SQL_ADDRESS=C.ADDRESS(+) ORDER BY C.PIECE;

[@more@]

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10455649/viewspace-976950/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
  • 博文量
    59
  • 访问量
    56096