本文共 19238 字,大约阅读时间需要 64 分钟。
一 创建IOT表
SQL> drop tablespace leo1 including contents and datafiles;
drop tablespace leo1 including contents and datafiles*ERROR at line 1:ORA-00959: tablespace 'LEO1' does not existSQL> create tablespace leo1 datafile '/home/oracle/oracle/product/10.2.0/db_1/oradata/LEO1/leo101.dbf' size 50M;Tablespace created.SQL> drop user leo1 cascade;User dropped.SQL> create user leo1 identified by leo1 default tablespace leo1;User created.SQL> grant dba to leo1;Grant succeeded.SQL> conn leo1/leo1Connected.SQL> drop table employee purge;drop table employee purge *ERROR at line 1:ORA-00942: table or view does not existSQL> create table employee (emp_id number constraint pk_employee primary key,emp_name varchar2(20));Table created.SQL> drop table work purge;drop table work purge *ERROR at line 1:ORA-00942: table or view does not existSQL> create table work (work_id number constraint pk_work primary key,work_name varchar2(20));Table created.SQL> drop table leo_iot purge;drop table leo_iot purge *ERROR at line 1:ORA-00942: table or view does not existSQL> create table leo_iot(work_id number,emp_id number,constraint pk_leo_iot primary key(work_id,emp_id))organization index tablespace leo1pctthreshold 20including emp_id overflow tablespace users; 2 3 4 5 6 7 8 9 Table created.SQL> set linesize 20000SQL> select segment_name,segment_type,tablespace_name from user_segments;SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME--------------------------------------------------------------------------------- ------------------ ------------------------------SYS_IOT_OVER_10300 TABLE USERSPK_LEO_IOT INDEX LEO1PK_WORK INDEX LEO1WORK TABLE LEO1PK_EMPLOYEE INDEX LEO1EMPLOYEE TABLE LEO16 rows selected.SQL> select table_name,tablespace_name from user_tables;TABLE_NAME TABLESPACE_NAME------------------------------ ------------------------------EMPLOYEE LEO1WORK LEO1SYS_IOT_OVER_10300 USERSLEO_IOTSQL> select object_id,object_name from user_objects where object_id=10300; OBJECT_ID OBJECT_NAME---------- -------------------------------------------------------------------------------------------------------------------------------- 10300 LEO_IOTSQL> insert into employee values(1,'LEO');1 row created.SQL> insert into employee values(2,'ALAN');1 row created.SQL> insert into work values(1,'DBA');1 row created.SQL> insert into work values(2,'DBA MANAGER');1 row created.SQL> commit;Commit complete.SQL> select * from employee; EMP_ID EMP_NAME---------- -------------------- 1 LEO 2 ALANSQL> select * from work; WORK_ID WORK_NAME---------- -------------------- 1 DBA 2 DBA MANAGERSQL> insert into leo_iot values(1,1);1 row created.SQL> insert into leo_iot values(1,2);1 row created.SQL> insert into leo_iot values(2,1);1 row created.SQL> insert into leo_iot values(2,2);1 row created.SQL> commit;Commit complete.SQL> select * from leo_iot; WORK_ID EMP_ID---------- ---------- 1 1 1 2 2 1 2 2SQL>--EOF--
二 创建索引
1.B tree 索引
场景:重复度较低列上可使用Btree索引 SQL> conn leo1/leo1 Connected.SQL> drop table t purge; drop table t purge *ERROR at line 1:ORA-00942: table or view does not existSQL> create table t(id number); Table created.SQL> create index idx_btree on t(id); Index created.SQL> 2.bitmap 索引 场景:列的基数很少重复值很多,数据不会经常更新可使用bitmap索引 SQL> conn leo1/leo1 Connected.SQL> drop table t1 purge; drop table t1 purge *ERROR at line 1:ORA-00942: table or view does not existSQL> create table t1(sex number); Table created.SQL> create bitmap index idx_bitmap on t1(sex); Index created.SQL> 3.reverse 索引 场景:列值持续增1,不是随机数,导致索引二叉树倾斜,使用反向索引来平衡二叉树。 SQL> conn leo1/leo1 Connected.SQL> drop table t2 purge; drop table t2 purge *ERROR at line 1:ORA-00942: table or view does not existSQL> create table t2(a number); Table created.SQL> create index idx_reverse on t2(a) reverse; Index created.SQL> 4.函数索引 场景:当where子句中使用函数的列上可使用function索引 SQL> conn leo1/leo1 Connected.SQL> drop table t3 purge; drop table t3 purge *ERROR at line 1:ORA-00942: table or view does not existSQL> create table t3(b varchar2(20)); Table created.SQL> create index idx_function on t3(upper(b)); Index created.SQL> 5.复合压缩索引 场景:同时查询多列时要建复合压缩索引,把重复值较多的列放在最前面进行压缩,重复值越高压缩效果越好 SQL> conn leo1/leo1 Connected.SQL> drop table t4 purge; drop table t4 purge *ERROR at line 1:ORA-00942: table or view does not existSQL> create table t4(a int,b int,c int); Table created.SQL> create index idx_compress on t4(a,b,c) compress 1; Index created.SQL>--EOF--
三 缓解SQL硬解析
1.查看cursor_sharing参数默认值
SQL> show parameter cursor_sharing NAME TYPE VALUE------------------------------------ ----------- ------------------------------cursor_sharing string EXACTSQL> 2.调整cursor_sharing参数为SIMILAR SQL> alter system set cursor_sharing=similar; System altered.SQL> show parameter cursor_sharingNAME TYPE VALUE------------------------------------ ----------- ------------------------------cursor_sharing string SIMILARSQL> 3.调整cursor_sharing参数为FORCE SQL> alter system set cursor_sharing=force; System altered.SQL> show parameter cursor_sharingNAME TYPE VALUE------------------------------------ ----------- ------------------------------cursor_sharing string FORCESQL> 4.检查cursor_sharing参数值 show parameter cursor_sharing--EOF--
四 移动表
SQL> create tablespace move_tbs datafile '/home/oracle/oracle/product/10.2.0/db_1/oradata/LEO1/move_tbs.dbf' size 20M;
Tablespace created.SQL> conn leo1/leo1Connected.SQL> drop table t purge;Table dropped.SQL> create table t (a int,b int);Table created.SQL> create index idx_t on t(a);Index created.SQL> insert into t values(1,2);1 row created.SQL> insert into t values(3,4);1 row created.SQL> insert into t values(5,6);1 row created.SQL> commit;Commit complete.SQL> select * from t; A B---------- ---------- 1 2 3 4 5 6SQL> select segment_name,segment_type,tablespace_name from user_segments where segment_name='T';SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME--------------------------------------------------------------------------------- ------------------ ------------------------------T TABLE LEO1SQL> alter table t move tablespace move_tbs;Table altered.SQL> select segment_name,segment_type,tablespace_name from user_segments where segment_name='T';SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME--------------------------------------------------------------------------------- ------------------ ------------------------------T TABLE MOVE_TBSSQL> select index_name,table_name,tablespace_name,status from user_indexes where index_name='IDX_T';INDEX_NAME TABLE_NAME TABLESPACE_NAME STATUS------------------------------ ------------------------------ ------------------------------ --------IDX_T T LEO1 UNUSABLESQL> alter index idx_t rebuild tablespace move_tbs online; Index altered.SQL> select index_name,table_name,tablespace_name,status from user_indexes where index_name='IDX_T';INDEX_NAME TABLE_NAME TABLESPACE_NAME STATUS------------------------------ ------------------------------ ------------------------------ --------IDX_T T MOVE_TBS VALIDSQL>--EOF--
五 缓存大对象
1.创建DBMS_SHARED_POOL包,默认这个包是没有的
SQL> conn / as sysdbaConnected.SQL> @?/rdbms/admin/dbmspool.sqlPackage created.Grant succeeded.View created.Package body created.SQL> 2.使用DBMS_SHARED_POOL把standard包加载到shared pool缓冲池中检查一下standard包是否被保存到shared pool中SQL> col owner for a10;SQL> col name for a30;SQL> col kept for a4;SQL> select owner,name,type,kept from v$db_object_cache where name='STANDARD';OWNER NAME TYPE KEPT---------- ------------------------------ ---------------------------- ----SYS STANDARD NOT LOADED NOSYS STANDARD PACKAGE NOSQL> 最后一列KEPT值为“NO”表明STANDARD包此时没有被保存到Shared Pool 为“YES”表明STANDARD包此时已经被保存到Shared Pool,还代表不能被踢出缓冲区加载standard包到shared poolSQL> exec dbms_shared_pool.keep('standard','p');PL/SQL procedure successfully completed.SQL> 再次确认standard包是否被保存到shared pool中SQL> select owner,name,type,kept from v$db_object_cache where name='STANDARD';OWNER NAME TYPE KEPT---------- ------------------------------ ---------------------------- ----SYS STANDARD PACKAGE BODY YESSYS STANDARD PACKAGE YESSQL> 把standard包从shared pool卸载出SQL> exec dbms_shared_pool.unkeep('standard','p');PL/SQL procedure successfully completed.SQL> Kept=NO 代表大对象现在可以踢出缓冲区,但不代表已经踢出缓冲区创建一个视图获得所有shared pool中大小超过50K的包、存储过程、触发器、函数对象SQL> drop view leo1_view;drop view leo1_view*ERROR at line 1:ORA-00942: table or view does not existSQL> create view leo1_view asselect name,type,sharable_memfrom v$db_object_cachewhere sharable_mem>51200 and type in ('PACKAGE', 'PACKAGE BODY','PROCEDURE','TRIGGER','FUNCTION'); 2 3 4 5 6 7 8 9 10 View created.SQL> select * from leo1_view;NAME TYPE SHARABLE_MEM------------------------------ ---------------------------- ------------DBMS_BACKUP_RESTORE PACKAGE 258511DBMS_BACKUP_RESTORE PACKAGE BODY 95523DBMS_RCVMAN PACKAGE 239899STANDARD PACKAGE 438620DBMS_RCVMAN PACKAGE BODY 375743SQL> 注:sharable_mem :对象在共享池中的大小(单位字节),把大于50k对象抽取出来 type:对象类型包括 包头 包体 存储过程 触发器 函数 --EOF--六 自动段空间管理ASSM
要求使用在线重定义方式迁移表
优点:支持在线读/写,不影响大查询,对海量数据的表进行操作效率非常好,实质只更新数据字典,不移动数据 缺点:在线重定义后表上的主键、索引不会同步过来,必须重建,只变换表名.在finish转换过程中原表是锁定状态 官方文档: PL/SQL Packages and Types Reference -> 搜索在线重定义dbms_redefinition 1.创建MSSM表空间 SQL> conn leo1/leo1 Connected.SQL> drop tablespace MSSM including contents and datafiles; drop tablespace MSSM including contents and datafiles*ERROR at line 1:ORA-00959: tablespace 'MSSM' does not existSQL> create tablespace MSSM datafile '/home/oracle/oracle/product/10.2.0/db_1/oradata/LEO1/mssm01.dbf' size 20m extent management local segment space management manual; Tablespace created.SQL> 2.创建ASSM表空间 SQL> drop tablespace ASSM including contents and datafiles; drop tablespace ASSM including contents and datafiles*ERROR at line 1:ORA-00959: tablespace 'ASSM' does not existSQL> create tablespace ASSM datafile '/home/oracle/oracle/product/10.2.0/db_1/oradata/LEO1/assm01.dbf' size 20m extent management local segment space management auto; Tablespace created.SQL> 3.检查表空间的段空间管理模式 SQL> select segment_space_management,tablespace_name from dba_tablespaces where tablespace_name in ('MSSM','ASSM'); SEGMEN TABLESPACE_NAME------ ------------------------------AUTO ASSMMANUAL MSSMSQL> 4.在MSSM表空间上创建t表 SQL> conn leo1/leo1 Connected.SQL> drop table t purge; Table dropped.SQL> create table t (a number constraint pk_t primary key) tablespace MSSM; Table created.SQL> insert into t values(10); 1 row created.SQL> insert into t values(20); 1 row created.SQL> insert into t values(30); 1 row created.SQL> insert into t values(40); 1 row created.SQL> insert into t values(50); 1 row created.SQL> commit; Commit complete.SQL> select * from t; A---------- 10 20 30 40 50SQL> select table_name,tablespace_name from user_tables where table_name='T'; TABLE_NAME TABLESPACE_NAME------------------------------ ------------------------------T MSSMSQL> 5.使用在线重定义方式转换表存放的表空间,把T表从MSSM表空间迁移到ASSM表空间 基于primary key的在线重定义(场景有主键的表) (1)验证是否满足基于主键在线重定义要求 SQL> execute dbms_redefinition.can_redef_table('LEO1','t',dbms_redefinition.cons_use_pk); PL/SQL procedure successfully completed.SQL>(2)创建在线重定义中间表 SQL> drop table t_interim purge; drop table t_interim purge *ERROR at line 1:ORA-00942: table or view does not existSQL> create table t_interim (a number) tablespace assm; -- 要求两表的字段名必须一致,但字段类型的长度可以不一致(① vachar2(10) ② vachar2(30))Table created.SQL> (3)查看t表和t_interim表所在的表空间 SQL> select table_name,tablespace_name from user_tables where table_name in ('T','T_INTERIM'); TABLE_NAME TABLESPACE_NAME------------------------------ ------------------------------T MSSMT_INTERIM ASSMSQL> (4)启动在线重定义 SQL> exec dbms_redefinition.start_redef_table('leo1','t','t_interim'); --此时不能删除表了,启动的一瞬间就同步了一次数据PL/SQL procedure successfully completed.SQL> select * from t_interim; A---------- 10 20 30 40 50SQL> (5)手工同步数据 数据的差异越小,完成在线重定义的时间越少,对系统的开销也越少SQL> exec dbms_redefinition.sync_interim_table('leo1','t','t_interim');
PL/SQL procedure successfully completed.SQL> (6)完成在线重定义 在finish完成的一瞬间进行最后一次同步数据马上转换表名在finish转换过程中原表是锁定状态SQL> exec dbms_redefinition.finish_redef_table('leo1','t','t_interim');
PL/SQL procedure successfully completed.SQL> (7)再次检查t表和t_interim表所在的表空间 SQL> select table_name,tablespace_name from user_tables where table_name in ('T','T_INTERIM'); TABLE_NAME TABLESPACE_NAME------------------------------ ------------------------------T ASSMT_INTERIM MSSMSQL> select * from t; A---------- 10 20 30 40 50SQL> select * from t_interim; A---------- 10 20 30 40 50SQL> 此时这两个表记录数是一致的 使用online选项重建索引 由于在线重定义不支持主键和索引同步,因此需要重建 SQL> select index_name,table_name,tablespace_name,status from user_indexes where index_name='PK_T'; INDEX_NAME TABLE_NAME TABLESPACE_NAME STATUS------------------------------ ------------------------------ ------------------------------ --------PK_T T_INTERIM MSSM VALIDSQL> alter index pk_t rebuild tablespace assm online; Index altered.SQL> select index_name,table_name,tablespace_name,status from user_indexes where index_name='PK_T'; INDEX_NAME TABLE_NAME TABLESPACE_NAME STATUS------------------------------ ------------------------------ ------------------------------ --------PK_T T_INTERIM ASSM VALIDSQL> 小结:可见此时t表已经从MSSM表空间转换到ASSM表空间,通过交换角色完成。 基于rowid的在线重定义(场景没有主键的表) (1)重新初始化环境 SQL> drop table t purge; Table dropped.SQL> drop table t_interim purge; Table dropped.SQL> create table t (a number) tablespace MSSM; Table created.SQL> insert into t values(10); 1 row created.SQL> insert into t values(20); 1 row created.SQL> insert into t values(30); 1 row created.SQL> insert into t values(40); 1 row created.SQL> insert into t values(50); 1 row created.SQL> commit; Commit complete.SQL> select * from t; A---------- 10 20 30 40 50SQL> select table_name,tablespace_name from user_tables where table_name='T'; TABLE_NAME TABLESPACE_NAME------------------------------ ------------------------------T MSSMSQL> (2)验证是否满足基于rowid的在线重定义要求 SQL> exec dbms_redefinition.can_redef_table('leo1','t',dbms_redefinition.cons_use_rowid); PL/SQL procedure successfully completed.SQL> (3)创建在线重定义中间表 SQL> create table t_interim (a number) tablespace assm; Table created.SQL> select * from t_interim; no rows selectedSQL> (4)查看t表和t_interim表所在的表空间 SQL> select table_name,tablespace_name from user_tables where table_name in ('T','T_INTERIM'); TABLE_NAME TABLESPACE_NAME------------------------------ ------------------------------T MSSMT_INTERIM ASSMSQL> (5)启动在线重定义 启动时就刷了一遍数据,我们要使用这个中间表进行在线重定义因此这个表此时不能dropSQL> exec dbms_redefinition.start_redef_table('leo1','t','t_interim',null,dbms_redefinition.cons_use_rowid);
PL/SQL procedure successfully completed.SQL> select * from t_interim; A---------- 10 20 30 40 50SQL> (6)手工同步数据 SQL> exec dbms_redefinition.sync_interim_table('leo1','t','t_interim'); PL/SQL procedure successfully completed.SQL> (7)完成在线重定义 瞬间交换表名,只有完成在线重定义才能删除表SQL> exec dbms_redefinition.finish_redef_table('leo1','t','t_interim');
PL/SQL procedure successfully completed.SQL> (8)再次查看t表和t_interim表所在的表空间 SQL> select table_name,tablespace_name from user_tables where table_name in ('T','T_INTERIM'); TABLE_NAME TABLESPACE_NAME------------------------------ ------------------------------T ASSMT_INTERIM MSSMSQL> 同样效果--EOF--
七 检查点
SQL> alter system set log_checkpoints_to_alert=false;
System altered.SQL> show parameter checkpointsNAME TYPE VALUE------------------------------------ -------------------------------- ------------------------------log_checkpoints_to_alert boolean FALSESQL> alter system set log_checkpoints_to_alert=true;System altered.SQL> show parameter checkpointsNAME TYPE VALUE------------------------------------ -------------------------------- ------------------------------log_checkpoints_to_alert boolean TRUESQL> alter system checkpoint;System altered.SQL> alter system checkpoint;System altered.SQL> !tail -f /home/oracle/oracle/product/10.2.0/db_1/admin/LEO1/bdump/alert_LEO1.logCompleted: create tablespace ASSM datafile '/home/oracle/oracle/product/10.2.0/db_1/oradata/LEO1/assm01.dbf' size 20m extent management local segment space management autoWed Feb 12 21:44:06 2014ALTER SYSTEM SET log_checkpoints_to_alert=FALSE SCOPE=BOTH;Wed Feb 12 21:44:20 2014ALTER SYSTEM SET log_checkpoints_to_alert=TRUE SCOPE=BOTH;Wed Feb 12 21:44:27 2014Beginning global checkpoint up to RBA [0xf.2444.10], SCN: 448065Completed checkpoint up to RBA [0xf.2444.10], SCN: 448065Beginning global checkpoint up to RBA [0xf.2446.10], SCN: 448067Completed checkpoint up to RBA [0xf.2446.10], SCN: 448067--EOF--
八 ASMM
1.调整sga_max_size值为400M
SQL> alter system set sga_target=300M scope=spfile; System altered.SQL> show parameter sga_max_size; NAME TYPE VALUE------------------------------------ -------------------------------- ------------------------------sga_max_size big integer 300MSQL> alter system set sga_max_size=400M scope=spfile; System altered.SQL> shutdown immediate Database closed.Database dismounted.ORACLE instance shut down.SQL> startup ORACLE instance started.Total System Global Area 419430400 bytesFixed Size 1219784 bytesVariable Size 180355896 bytesDatabase Buffers 234881024 bytesRedo Buffers 2973696 bytesDatabase mounted.Database opened.SQL> show parameter sga_max_size; NAME TYPE VALUE------------------------------------ -------------------------------- ------------------------------sga_max_size big integer 400MSQL> 2.调整sga_target值为352M SQL> alter system set sga_target=352M; System altered.SQL> show parameter sga_target; NAME TYPE VALUE------------------------------------ -------------------------------- ------------------------------sga_target big integer 352MSQL> 3.调整pga_aggregate_target值为100M SQL> alter system set pga_aggregate_target=60M; System altered.SQL> show parameter pga_aggregate_target NAME TYPE VALUE------------------------------------ -------------------------------- ------------------------------pga_aggregate_target big integer 60MSQL> alter system set pga_aggregate_target=100M;System altered.SQL> show parameter pga_aggregate_target NAME TYPE VALUE------------------------------------ -------------------------------- ------------------------------pga_aggregate_target big integer 100MSQL> 4.调整java_pool_size值为52M SQL> alter system set java_pool_size=0; System altered.SQL> show parameter java_pool_size NAME TYPE VALUE------------------------------------ -------------------------------- ------------------------------java_pool_size big integer 0SQL> alter system set java_pool_size=52M; System altered.SQL> show parameter java_pool_size NAME TYPE VALUE------------------------------------ -------------------------------- ------------------------------java_pool_size big integer 52MSQL> shutdown immediate Database closed.Database dismounted.ORACLE instance shut down.SQL> startupORACLE instance started.Total System Global Area 419430400 bytesFixed Size 1219784 bytesVariable Size 176161592 bytesDatabase Buffers 239075328 bytesRedo Buffers 2973696 bytesDatabase mounted.Database opened.SQL> show parameter java_pool_size NAME TYPE VALUE------------------------------------ -------------------------------- ------------------------------java_pool_size big integer 52MSQL>--EOF--
转载地址:http://wgvai.baihongyu.com/