性能有关脚本.doc

Oracle 10g/11g 优化设计与性能脚本 赵元杰 2009年12月 目 录Oracle 10g/11g1优化设计与性能脚本11Oracle环境配置与设计问题31.1Oracle系统安装情况检查31.1.1如何查询Oracle 选件安装信息31.1.2查询Oracle 系统字符集有关信息31.1.3了解Oracle系统字符集存储的字节数31.1.4如何知道Oracle系统是否支持冷僻字41.2Oracle系统默认文件分布合理性检查41.2.1查询Oracle 默认数据文件41.2.2查询Oracle系统可用空间信息51.2.3查询Oracle系统日志文件合理性51.2.4查询Oracle系统控制文件合理性61.3段对象设计样例61.3.1创建表与主键要点61.3.2创建索引要点71.3.3增加主键要点71.4段对象设计合理性检查81.4.1查询表的存储设置信息81 Oracle环境配置与设计问题 1.1 Oracle系统安装情况检查 1.1.1 如何查询Oracle 选件安装信息 数据字典视图 Sys下的v$option：-适合版本：9i/10g/11g -以sysdba 连接到Oracle实例select * from v$option;1.1.2 查询Oracle 系统字符集有关信息 以sysdba 连接到Oracle实例 -SELECT parameter, value FROM nls_database_parameters WHERE parameter LIKE %CHARACTERSET%;-查询Oracle系统NLS设置的信息SELECT * FROM nls_instance_parameters;1.1.3 了解Oracle系统字符集存储的字节数 用lengthb函数查询汉字的字节数据 l UTF8 存储用3个字节l ZHS16GBK等其他均为2字节SQL- v$nls_parameterscol VALUE for a45col PARAMETER for a35Select * from V$NLS_PARAMETERS;-SQLconnect scott/tigerorclSQL insert into emp values(1234,赵元杰,Oracle,7782,null,98765,null,20);已创建 1 行。SQL select ename,length(ename),lengthb(ename) from emp where empno=1234;ENAME LENGTH(ENAME) LENGTHB(ENAME)- - -赵元杰 3 61.1.4 如何知道Oracle系统是否支持冷僻字 输入冷僻字，再查询即可： SQL-connect scott/tigerCREATE TABLE HZ ( ID# number(3), name VARCHAR2(20);INSERT INTO hz VALUES (1, 栢);INSERT INTO hz VALUES (2, 喆);INSERT INTO hz VALUES (3, 昇);INSERT INTO hz VALUES (4, 赵);SQL select * from hz; ID# NAME - - 1 ？ 2 ？ 3 ？ 4 赵 1.2 Oracle系统默认文件分布合理性检查 1.2.1 查询Oracle 默认数据文件 数据字典视图 Sys下的DBA_DATA_FILES：-适合版本：9i/10g/11g -以sysdba 连接到Oracle实例SQL CONN / as sysdbaSQL SELECT file_name, blocks, tablespace_name 2 FROM dba_data_files; 要点：如果是生产环境，数据文件与Oracle软件在一起，那就是不合理的文件分布。 1.2.2 查询Oracle系统可用空间信息 数据字典视图 Sys下的dba_free_space与dba_tablespaces：-适合版本：9i/10g/11g -以sysdba 连接到Oracle实例SQL CONN / as sysdbaSELECT f.tablespace_name “表空间”, Decode(d.EXTENT_MANAGEMENT,DICTIONARY,字典,本地) “表空间类型”,TRUNC(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 GROUP BY f.tablespace_name ,d.EXTENT_MANAGEMENT ;要点：要看可用户的较大的连续块，不要看总量；对于10g/11g 建议应用系统采用本地管理表空间。 1.2.3 查询Oracle系统日志文件合理性 数据字典视图 Sys下的v$log与v$logfile：-适合版本：9i/10g/11g -以sysdba 连接到Oracle实例SQL connect sys/zhaoabcorcl as sysdba已连接。-查询日志文件：col member for a50set line 120select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_- - - - - 3 ONLINE C:APPZHAOORADATAORCLREDO03.LOG NO 2 ONLINE C:APPZHAOORADATAORCLREDO02.LOG NO 1 ONLINE C:APPZHAOORADATAORCLREDO01.LOG NO 要点：1) 日志文件组数量:至少2个组或3个组；、2) 每个组有2个成员或3个成员；3) 每组的成员分布在不同磁盘驱动器上；4) 每个成员大一般为100MB左右；1.2.4 查询Oracle系统控制文件合理性 数据字典视图 Sys下的v$controlfile：-适合版本：9i/10g/11g -以sysdba 连接到Oracle实例SQL connect sys/zhaoabcorcl as sysdba已连接。-查询控制文件select * from v$controlfile ; -SELECT VALUE FROM V$PARAMETER WHERE NAME = control_files;要点：1) 控制文件至少2个或3个；2) 每个控制文件分布在不同磁盘驱动器上；1.3 段对象设计样例 1.3.1 创建表与主键要点 要点：要指定表空间与存储参数 -适合版本：9i/10g/11g SQL -下面语句创建一个表，并设置存储初始大小 ：CREATE TABLE insured_autos(policy_id NUMBER,vin VARCHAR2(40),coverage_begin DATE,coverage_term NUMBER,CONSTRAINT pk_insured_autos PRIMARY KEY (policy_id,vin)USING INDEX TABLESPACE indexSTORAGE (INITIAL 10M NEXT 4M PCTINCREASE 0) TABLESPACE USER_DATASTORAGE (INITIAL 20M NEXT 8M PCTINCREASE 0)/1.3.2 创建索引要点 要点：要指定表空间与存储参数 -适合版本：9i/10g/11g SQL CREATE BITMAP INDEX test_bm_idx 2 ON test(c) 3 PCTFREE 30 4 STORAGE(INITIAL 200M NEXT 50M 5 PCTINCREASE 0 ) 6 TABLESPACE indx;索引已创建。1.3.3 增加主键要点 要点：增加主键也不要忘记指定表空间与存储参数 -适合版本：9i/10g/11g SQL select dbms_metadata.get_ddl(TABLE,STUDENT) from dual;DBMS_METADATA.GET_DDL(TABLE,STUDENT)- CREATE TABLE SCOTT.STUDENT ( PER_ID VARCHAR2(15), NAME VARCHAR2(20) NOT NULL ENABLE, SEX CHAR(2), MOD_NO VARCHAR2(15), CHECK (sex in(男,女) ENABLE, PRIMARY KEY (PER_ID) USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)TABLESPACE USERS ENABLE ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE USERS如果：USING INDEX PCTFREE 是默认的PCTFREE 10 ，则可能存在不1.4 段对象设计合理性检查 1.4.1 查询表的存储设置信息 使用下面方法得到DDL脚本 -适合版本：9i/10g/11g SQL select dbms_metadata.get_ddl(TABLE,STUDENT) from dual;DBMS_METADATA.GET_DDL(TABLE,STUDENT)- CREATE TABLE SCOTT.STUDENT ( PER_ID VARCHAR2(15), NAME VARCHAR2(20) NOT NULL ENABLE, SEX CHAR(2), MOD_NO VARCHAR2(15), CHECK (sex in(男,女) ENABLE, PRIMARY KEY (PER_ID) USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)TABLESPACE USERS ENABLE ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE USERS 要点：1)USING INDEX PCTFREE 是默认的PCTFREE 10 ，则可能存在不合理现象；2)如果存储参数是STORAGE(INITIAL 65536，则表示采用了默认的大小，性能问题肯定是存在的。1.4.2 查询表的扩展信息 查询dba_segments 数据字典 -适合版本：9i/10g/11g SQLselect owner,segment_type,segment_name,bytes,INITIAL_EXTENT,EXTENTS from dba_segmentswhere owner=HOUSEorder by bytes descSQL /OWNER SEGMENT_TYPE SEGMENT_NAME BYTES INITIAL_EXTENT EXTENTS- - - - - -HOUSE TABLE PAY_LST_DET_HIS 150994944 134217728 18HOUSE TABLE PER_DETAIL_HIS 100663296 100663296 12HOUSE TABLE PER_FIX_DET_HIS 92274688 67108864 67HOUSE TABLE PAY_LST_DET 51380224 51380224 49扩展次数越高，表明设计时设置的参数存在问题。建议进行重组。1.4.3 查询表的块控制参数信息 查询dba_tables 数据字典 -适合版本：9i/10g/11g 1 select owner,table_name,pct_free,pct_used,tablespace_name 2 from dba_tables 3* where owner=HOUSEorder by table_nameSQL /OWNER TABLE_NAME PCT_FREE PCT_USED TABLESPACE_NAME- - - - -HOUSE ACCT_RATE 5 HOUSE_DATAHOUSE BANK_CHECK 10 HOUSE_DATAHOUSE BANK_CODE 5 HOUSE_DATAHOUSE BANK_UNIT 10 HOUSE_DATAHOUSE BELONG 5 HOUSE_DATAHOUSE ECONOMIC 5 HOUSE_DATAHOUSE FUND_DRAW 5 HOUSE_DATAHOUSE FUND_DRAW_HIS 5 HOUSE_DATAHOUSE FUND_PAY_HIS 10 HOUSE_DATA2 Oracle环境配置与设计问题 2.1 应用系统段问题检查与重组 2.1.1 如何查询表空间的碎片详细信息 * 数据字典视图 dba_free_space：-适合版本：9i/10g/11g SQL -下面语句 ：col 自由空间 for a14col 最小字节 for 999,999,999col 最大字节 for 999,999,999col 平均字节 for 999,999,999col 分布在 for 999,999,999col 表空间类型 for a18SELECT f.tablespace_name 表空间, Decode(d.EXTENT_MANAGEMENT,DICTIONARY,字典,本地) 表空间类型,TRUNC(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 GROUP BY f.tablespace_name ,d.EXTENT_MANAGEMENT ;表空间 表空间类型 自由空间 最小字节 最大字节 平均字节 分布在- - - - - - -RP_TAB0311 本地 64.44MB 65,994,752 65,994,752 65,994,752 1RP_TAB0312 本地 64.44MB 65,994,752 65,994,752 65,994,752 1RP_TAB0401 本地 64.44MB 65,994,752 65,994,752 65,994,752 1RP_TAB0402 本地 64.44MB 65,994,752 65,994,752 65,994,752 1SYSTEM 字典 332.59MB 340,574,208 340,574,208 340,574,208 1TEMP 字典 524.28MB 655,360 472,637,440 5,422,856 99TOOLS 字典 79.76MB 32,768 294,912 35,936 2,2732.1.2 如何合并表空间的碎片 *实现步骤：SQL -下面语句对表空间进行碎片合并 ：ALTER TABLESPACE tablespace_name COALESCE;-下面查询语句查询多表空间，并自动产生合并表空间的命令“SELECT Alter tablespace |TABLESPACE_NAME| coalesce ; FROM DBA_FREE_SPACE_COALESCED where PERCENT_EXTENTS_COALESCED 100 or PERCENT_BLOCKS_COALESCED -下面查询语句产生分析表的批命令 ：select ANALYZE TABLE |table_name| compute statistics; -分析表 FROM USER_TABLES /-分析表-ANALYZE TABLE DEPT compute statistics;ANALYZE TABLE EMP compute statistics;ANALYZE TABLE BONUS compute statistics;ANALYZE TABLE SALGRADE compute statistics;ANALYZE TABLE MY_EMP compute statistics;ANALYZE TABLE ABC compute statistics;ANALYZE TABLE EMP_BJ compute statistics;ANALYZE TABLE TST compute statistics;ANALYZE TABLE STUDENT compute statistics;ANALYZE TABLE ENT_CODE compute statist