<<OCM实验选讲>> 第八课 Oracle性能优化实验
来源：互联网 发布：编程学什么语言 编辑：程序博客网 时间：2024/05/03 09:45
一 创建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 [0xf.2444.10], SCN: 448065
Completed checkpoint up to RBA [0xf.2444.10], SCN: 448065
Beginning global checkpoint up to RBA [0xf.2446.10], SCN: 448067
Completed 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 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 Area 419430400 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 Area 419430400 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--
- <<OCM实验选讲>> 第八课 Oracle性能优化实验
- <<OCM实验选讲>> 第七课 Oracle数据库管理实验
- <<OCM实验选讲>> 第二课 手工建库 实验
- <<OCM实验选讲>> 第三课 网络配置 实验
- <<OCM实验选讲>> 第五课 数据备份恢复实验
- <<OCM实验选讲>> 第六课 数据仓库实验
- <<OCM实验选讲>> 第十课 DG实验
- OCM实验目录-转自抹曦阳-Oracle
- OCM实验目录
- OCM实验-手工建库
- 第八次上机实验课
- SQL第六讲--这是最后一次实验课了--第八周
- OCM实验-备份恢复-控制文件
- OCM考试实验-Skillset1----手动建库
- OCM考试实验-Skillset1----Undo & Shared Server
- OCM考试实验-Skillset1----Network configuration
- oracle实验课作业
- 第八章实验七
- WACC使用的技术
- 文本语言模型的参数估计-最大似然估计、MAP及贝叶斯估计
- 【算法导论】快速排序实现
- 随机游走与马尔科夫链的一些基础
- 你是想读书，还是想读完书？
- <<OCM实验选讲>> 第八课 Oracle性能优化实验
- 第一章 first集的计算
- 关于机器学习一些好的博客和资料
- POJ-3614-Sunscreen
- 黑马程序员 java多态
- linux文件权限与目录管理
- 黑马程序员 java基础学习笔记6
- <<OCM实验选讲>> 第十课 DG实验
- ARM处理器启动流程
