ora10g SQL.docx

1、ora10g SQL/*/* 文件名：ora_10g.sql */* 作者：赵元杰 E_Mail：zyj5681 */* 修改：2008.3.25 */* 功能：列出ORACLE 性能调整所需的必要信息 */* 使用： */* 1)：将本文档另存为“纯文本”文件 */* 2)：用SQL*Plus登录到SYSDBA帐户 */* 3)：SQLstart ora_10g.txt */* 4)：分析输出的结果（C盘上的ora_10g.lst文件） */*/PROMPT *- - - - - - - - - - - - - - - - - - - - - - - - -*PROMPT *- - - 应用

2、系统调整分析脚本 - - -*PROMPT *- - - - - - - - - - - - - - - - - - - - - - - - -*PROMPT * *ACCEPT user_name PROMPT 请输入需要分析用户名字(大写): PROMPT *- - - - - - - - - - - - - - - - - - - - - - - - -*PROMPT * 正在产生脚本，请等待。 *PROMPT *- - - - - - - - - - - - - - - - - - - - - - - - -*PROMPTset pagesize 100set linesize 160

3、-截断查询结果最后的空格 set trimspool onset trimspool on set trimout on- 显示执行脚本的命令 set echo onset echo on set timing onalter session set nls_date_format=yyyy.mm.dd ;-spool C:10g_info-* 数据库实例基本情况-* ORACLE系统实例名字:select NAME ,CREATED ,LOG_MODE , to_char(sysdate,yyyy/mm/dd:HH24:MI) SYS_DATEfrom v$database;-* ORACL

4、E实例安装的产品列表:COL PARAMETER for a40COL VALUE for a30select * from v$option;-* Oracle及工具 Tools 版本信息:col BANNER for a60select BANNER from v$version;-* Oracle实例是否归档:select dbid, name, log_mode from v$database;-* ORACLE系统用户情况:col USERNAME for a20col DEFAULT_TABLESPACE for a20col TEMPORARY_TABLESPACE for a2

5、0col PROFILE for a20select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,PROFILE,CREATED FROM dba_users;-* ORACLE系统资源文件情况:col RESOURCE_NAME for a28col LIMIT for a20SELECT * from dba_profiles order by PROFILE;-* ORACLE系统字符集情况 COL VALUE$ for a40SELECT name,value$ from sys.props$;-* ORACLE数据库连接的信息 c

6、ol owner for a20col db_link for a38col username for a20SELECT owner,db_link,username from dba_db_links ;-* ORACLE数据库用户权限报告:col Username for a20col Owner for a14col Obj for a20col Obj_Priv for a10col Sys_Priv for a20col Granted_Role for a22select username Username, owner, table_name Obj, privilege Ob

7、j_Priv, Sys_Priv, Granted_Role, 1 dummyfrom dba_users u, dba_tab_privs twhere u.username = t.granteeand u.username = upper(&user_name) unionselect username, , , , privilege, , 2 dummyfrom dba_users u, dba_sys_privs swhere u.username = s.granteeand u.username = upper(&user_name)unionselect username,

8、, , , , granted_role, 3 dummyfrom dba_users u, dba_role_privs rwhere u.username = r.granteeand u.username = upper(&user_name) order by 1, 7;-* ORACLE实例后台进程信息 col DESCRIPTION for a40col name for a30SELECT * from v$bgprocess;-* ORACLE 系统参数情况Show parameter;-* ORACLE 系统参数情况2(9i/10g视图)COL SID for a30 COL

9、 NAME for a30 COL VALUE for a40SELECT sid, name , value FROM V$SPPARAMETER;-* Oracle系统数据文件及表空间情况-* ORACLE系统数据文件情况 :COL FILE_NAME FOR A50COL TABLESPACE_NAME FOR A16COL BYTES FOR 999,999,999,999COL MAXBYTES FOR 999,999,999,999SELECT FILE_NAME ,TABLESPACE_NAME ,BYTES ,AUTOEXTENSIBLE AUTO,MAXBYTES FROM

10、DBA_DATA_FILES ORDER BY TABLESPACE_NAME ;-* 表空间的自由空间情况:col TABLESPACE_NAME for a20col FREE_MB for a14col MAX_BYTES for 99,999,999,999col AVG_BYTES for 99,999,999,999col COUNT for 999,999,999col TABS_TYPE for a12SELECT f.tablespace_name TABLESPACE_NAME, Decode(d.EXTENT_MANAGEMENT,DICTIONARY,DICT,LOCA

11、L) TABS_TYPE ,TRUNC(SUM(f.bytes/1024000),2 ) | MB FREE_MB,MAX(f.bytes) MAX_BYTES,AVG(f.bytes) AVG_BYTES ,COUNT(f.tablespace_name) COUNT FROM dba_free_space f, dba_tablespaces d Where f.tablespace_name = d.tablespace_name GROUP BY f.tablespace_name ,d.EXTENT_MANAGEMENT ;-* 是否存在空间无法扩展的情况 :Col 段名 for a

12、20 Col 段类型 for a12 Col 段所有者 for a12 Col 表空间名 for a18 col 段所有者for a18 col 初始扩展 for 999,999,999 col 下次 for 999,999,999 col 增涨 for 999,999 col 最大字节 for 999,999,999,999 Select segment_name 段名, segment_type 段类型, Owner 段所有者, a.tablespace_name 表空间名, Initial_extent 初始扩展, next_extent 下次, pct_increase 增涨, b.b

13、ytes 最大字节 From dba_segments a, ( select tablespace_name, max(bytes) bytes from dba_free_space group by tablespace_name ) b where a.tablespace_name=b.tablespace_name and next_extent b.bytes ;-* 检查对象扩展是否接近MAXEXTENTS :col 已扩展 for 999,999,999col 最大扩展 for 999,999,999,999SELECT owner 段所有者,segment_name 段名

14、, segment_type 段类型, extents 已扩展 , max_extents 最大扩展 , tablespace_name 表空间名 FROM dba_segmentsWHERE extents = (max_extents - 5 ) ;-* 检查自由表空间接近10%的情况:col 表空间 for a18col 文件名 for a50col 总空间(Mb) for 999,999,999,999col 自由空间(Mb) for 999,999,999,999col 自由空间% for 9999.99SELECT a.tablespace_name 表空间, a.file_nam

15、e 文件名, a.avail 总空间(Mb), nvl(b.free,0) 自由空间(Mb), nvl(round(free/avail)*100),2),0) 自由空间%from (select tablespace_name, substr(file_name,1,45) file_name, file_id, round(sum(bytes/(1024*1024),3) avail from sys.dba_data_files group by tablespace_name, substr(file_name,1,45), file_id) a, (select tablespace

16、_name, file_id, round(sum(bytes/(1024*1024),3) free from sys.dba_free_space group by tablespace_name, file_id) bwhere a.file_id = b.file_id (+)order by 1, 2 ;-* 检查是否存在需要合并的表空间 :col 自由空间 for a16col 最小字节 for 999,999,999col 最大字节 for 999,999,999,999col 分布在 for 999,999,999SELECT f.tablespace_name 表空间, TR

17、UNC(SUM(f.bytes/1024000),2 ) | MB 自由空间,MIN(f.bytes) 最小字节, MAX(f.bytes) 最大字节,AVG(f.bytes) 平均字节,COUNT(f.tablespace_name) 分布在FROM dba_free_space f, dba_tablespaces d Where f.tablespace_name = d.tablespace_name Having COUNT(f.tablespace_name) 1GROUP BY f.tablespace_name ,d.EXTENT_MANAGEMENT ;-* 查询表空间的大小

18、,分布等信息:COL FREE_BLK for 999,999,999COL FREE_M for 999,999,999COL num_chunks for 999,999,999SELECT tablespace_name, sum ( blocks ) free_blk , trunc ( sum ( bytes ) / (1024*1024) ) free_m , max ( bytes ) / (1024) as big_chunk_k, count (*) num_chunks FROM dba_free_space GROUP BY tablespace_name;-* 查询表空

19、间的最大连续块,使用百分比:COL max_blocks for 999,999,999COL count_blocks for 999,999,999COL sum_free_block for 999,999,999SELECT tablespace_name, max_blocks, count_blocks, sum_free_blocks, to_char(100*sum_free_blocks/sum_alloc_blocks, 99.99) | % AS pct_freeFROM ( SELECT tablespace_name, sum(blocks) AS sum_alloc

20、_blocksFROM dba_data_filesGROUP BY tablespace_name), ( SELECT tablespace_name AS fs_ts_name, max(blocks) AS max_blocks, count(blocks) AS count_blocks, sum(blocks) AS sum_free_blocksFROM dba_free_spaceGROUP BY tablespace_name )WHERE tablespace_name = fs_ts_name;-* 检查表空间碎片1MB以上:SELECT tablespace_name

21、表空间 , count(*) 分布在, sum(bytes) 自由空间(Mb) from dba_free_space where bytes1000000 group by tablespace_name;-* 表空间碎片情况信息:column tablespace_name format a15 heading Tablespace Name column file_name format a50 heading Data File Name column file_id format 9,999 heading File|ID column block_id format 99,999

22、heading Block|ID column blocks format 999,999 heading Blocks column bytes format 999,999,999 heading Bytes /* 结果过多break on file_name skip 1compute sum of bytes on file_nameselect df.file_name, fs.tablespace_name, fs.file_id, fs.block_id, fs.blocks, fs.bytesfrom sys.dba_free_space fs, sys.dba_data_fi

23、les dfwhere df.file_id = fs.file_idorder by fs.tablespace_name, fs.file_id, fs.block_id;*/-* 表空间有效空间信息 :COLUMN tablespace_name FORMAT a25COLUMN allocation_type FORMAT A4 TRU HEADING ALLOCOLUMN contents FORMAT A4 TRU HEADING MGMTCOLUMN Tot_Size FORMAT 999,999 HEADING TOTAL(M)COLUMN Tot_Free FORMAT 99

24、9,999 HEADING FREE(M)COLUMN Pct_Free FORMAT 999 HEADING FREE %COLUMN Fragments FORMAT 99,999 HEADING FRAGMTSCOLUMN Large_Ext FORMAT 999,999 HEADING LARG(M)set feedback off pages 999 trims on SELECT a.tablespace_name tablespace_name, SUM(a.tots)/1048576 Tot_Size, SUM(a.sumb)/1048576 Tot_Free, SUM(a.s

25、umb)*100/sum(a.tots) Pct_Free, SUM(a.largest)/1048576 Large_Ext, SUM(a.chunks) Fragments, b.contents, b.allocation_typeFROM (SELECt tablespace_name, 0 tots, SUM(bytes) sumb, MAX(bytes) largest, COUNT(*) chunks FROM dba_free_space a GROUP BY tablespace_name UNION SELECT tablespace_name, SUM(bytes) to

26、ts, 0, 0, 0 FROM dba_data_files GROUP BY tablespace_name UNION SELECT tablespace_name, SUM(bytes) tots, 0, 0, 0 FROM dba_temp_files GROUP BY tablespace_name) a, dba_tablespaces bWHERE b.tablespace_name = a.tablespace_nameGROUP BY a.tablespace_name, b.contents, b.allocation_type/-* 产生表空间的合并命令 :SELECT

27、 Alter tablespace |TABLESPACE_NAME| coalesce ; 合并表空间命令from DBA_FREE_SPACE_COALESCED where PERCENT_EXTENTS_COALESCED 100 or PERCENT_BLOCKS_COALESCED100 ;-* 查询数据文件的I/O情况信息:Column TableSpace_Name Format A25 Heading TablespaceColumn Name Format A30 Heading File NameColumn Total Format 99,999,999,990 Hea

28、ding TotalColumn Phyrds Format 99,999,999,990 Heading Physical|Reads Column Phywrts Format 99,999,999,990 Heading Physical| Writes Column Phyblkrd Format 99,999,999,990 Heading Physical |Block ReadsColumn Phyblkwrt Format 99,999,999,990 Heading Physical |Block WritesColumn Avg_Rd_Time Format 90.99 H

29、eading 平均 |读时间|每块Column Avg_Wrt_Time Format 90.99 Heading 平均|写时间|每块Select C.TableSpace_Name, B.Name, A.Phyblkrd + A.Phyblkwrt Total, A.Phyrds, A.Phywrts, A.Phyblkrd, A.Phyblkwrt, (A.ReadTim / Decode(A.Phyrds, 0, 1, A.Phyblkrd) / 100) Avg_Rd_Time, (A.WriteTim / Decode(A.PhyWrts, 0, 1, A.PhyblkWrt) / 100) Avg_Wrt_TimeFrom V$FileStat A, V$DataFile B, Sys.DBA_Data_Files CWhere B.File# = A.File# And B.File# = C.File_IdOrder By TableSpace_Name;-* 查询数据文件I/O统计信息: grant select any dictionary to SYSTEM;CREATE VIEW