<> 第八课 Oracle性能优化实验
一 创建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 exist
SQL> 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/leo1
Connected.
SQL> drop table employee purge;
drop table employee purge
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> 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 exist
SQL> 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 exist
SQL> create table leo_iot
(
work_id number,
emp_id number,
constraint pk_leo_iot primary key(work_id,emp_id))
organization index tablespace leo1
pctthreshold 20
including emp_id
overflow tablespace users;2 3 4 5 6 7 8 9
Table created.
SQL> set linesize 20000
SQL> select segment_name,segment_type,tablespace_name from user_segments;
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
--------------------------------------------------------------------------------- ------------------ ------------------------------
SYS_IOT_OVER_10300 TABLE USERS
PK_LEO_IOT INDEX LEO1
PK_WORK INDEX LEO1
WORK TABLE LEO1
PK_EMPLOYEE INDEX LEO1
EMPLOYEE TABLE LEO1
6 rows selected.
SQL> select table_name,tablespace_name from user_tables;
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
EMPLOYEE LEO1
WORK LEO1
SYS_IOT_OVER_10300 USERS
LEO_IOT
SQL> select object_id,object_name from user_objects where object_id=10300;
OBJECT_ID OBJECT_NAME
---------- --------------------------------------------------------------------------------------------------------------------------------
10300 LEO_IOT
SQL> 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 ALAN
SQL> select * from work;
WORK_ID WORK_NAME
---------- --------------------
1 DBA
2 DBA MANAGER
SQL> 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 2
SQL>
--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 exist
SQL> 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 exist
SQL> 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 exist
SQL> 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 exist
SQL> 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 exist
SQL> 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 EXACT
SQL>
2.调整cursor_sharing参数为SIMILAR
SQL> alter system set cursor_sharing=similar;
System altered.
SQL> show parameter cursor_sharing
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string SIMILAR
SQL>
3.调整cursor_sharing参数为FORCE
SQL> alter system set cursor_sharing=force;
System altered.
SQL> show parameter cursor_sharing
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string FORCE
SQL>
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/leo1
Connected.
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 6
SQL> select segment_name,segment_type,tablespace_name from user_segments where segment_name='T';
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
--------------------------------------------------------------------------------- ------------------ ------------------------------
T TABLE LEO1
SQL> 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_TBS
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 LEO1 UNUSABLE
SQL> 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 VALID
SQL>
--EOF--
五 缓存大对象
1.创建DBMS_SHARED_POOL包，默认这个包是没有的
SQL> conn / as sysdba
Connected.
SQL> @?/rdbms/admin/dbmspool.sql
Package 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 NO
SYS STANDARD PACKAGE NO
SQL>
最后一列KEPT值为“NO”表明STANDARD包此时没有被保存到Shared Pool
为“YES”表明STANDARD包此时已经被保存到Shared Pool，还代表不能被踢出缓冲区
加载standard包到shared pool
SQL> 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 YES
SYS STANDARD PACKAGE YES
SQL>
把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 exist
SQL> create view leo1_view
as
select name,type,sharable_mem
from v$db_object_cache
where 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 258511
DBMS_BACKUP_RESTORE PACKAGE BODY 95523
DBMS_RCVMAN PACKAGE 239899
STANDARD PACKAGE 438620
DBMS_RCVMAN PACKAGE BODY 375743
SQL>
注：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 exist
SQL> 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 exist
SQL> 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 ASSM
MANUAL MSSM
SQL>
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
50
SQL> select table_name,tablespace_name from user_tables where table_name='T';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
T MSSM
SQL>
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 exist
SQL> 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 MSSM
T_INTERIM ASSM
SQL>
（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
50
SQL>
（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 ASSM
T_INTERIM MSSM
SQL> select * from t;
A
----------
10
20
30
40
50
SQL> select * from t_interim;
A
----------
10
20
30
40
50
SQL>
此时这两个表记录数是一致的
使用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 VALID
SQL> 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 VALID
SQL>
小结：可见此时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
50
SQL> select table_name,tablespace_name from user_tables where table_name='T';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
T MSSM
SQL>
（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 selected
SQL>
（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 MSSM
T_INTERIM ASSM
SQL>
（5）启动在线重定义
启动时就刷了一遍数据，我们要使用这个中间表进行在线重定义因此这个表此时不能drop
SQL> 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
50
SQL>
（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 ASSM
T_INTERIM MSSM
SQL>
同样效果
--EOF--
七 检查点
SQL> alter system set log_checkpoints_to_alert=false;
System altered.
SQL> show parameter checkpoints
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
log_checkpoints_to_alert boolean FALSE
SQL> alter system set log_checkpoints_to_alert=true;
System altered.
SQL> show parameter checkpoints
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
log_checkpoints_to_alert boolean TRUE
SQL> 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.log
Completed: 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
Wed Feb 12 21:44:06 2014
ALTER SYSTEM SET log_checkpoints_to_alert=FALSE SCOPE=BOTH;
Wed Feb 12 21:44:20 2014
ALTER SYSTEM SET log_checkpoints_to_alert=TRUE SCOPE=BOTH;
Wed Feb 12 21:44:27 2014
Beginning global checkpoint up to RBA , SCN: 448065
Completed checkpoint up to RBA , SCN: 448065
Beginning global checkpoint up to RBA , SCN: 448067
Completed checkpoint up to RBA , 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 300M
SQL> 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 Area419430400 bytes
Fixed Size 1219784 bytes
Variable Size 180355896 bytes
Database Buffers 234881024 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SQL> show parameter sga_max_size;
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
sga_max_size big integer 400M
SQL>
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 352M
SQL>
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 60M
SQL> alter system set pga_aggregate_target=100M;
System altered.
SQL> show parameter pga_aggregate_target
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
pga_aggregate_target big integer 100M
SQL>
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 0
SQL> alter system set java_pool_size=52M;
System altered.
SQL> show parameter java_pool_size
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
java_pool_size big integer 52M
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area419430400 bytes
Fixed Size 1219784 bytes
Variable Size 176161592 bytes
Database Buffers 239075328 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SQL> show parameter java_pool_size
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
java_pool_size big integer 52M
SQL>
--EOF--
版权声明：本文为博主原创文章，未经博主允许不得转载。
页:
[1]