oracle学习笔记.doc 2002-3-7
一、改善数据库性能
1、 用explain plan 来统计检查sql 语句的性能 具体操作： 生成plan_table表
通过运行 rdbms\\admin\%utlxplain.sql
explain plan set statement_id =?statement1? into plan_table for select * from table_name ;
select statement_id, operation, options position from plan_table 注：position 是对开销得描述 operation 操作对象 option 2、用set autotrace set autotrace on set autotrace off 或
set autot on set autot off
3、 选择优化sql 语句的方法 1） 基于代价的优化 两个参数frist_rows 和 all_rows 用修改optimizer_mode 参数的方法： optimizer_mode 参数在init.ora中。
optimizer_mode 参数 还可以设置为 choose . 用修改会话的方法：
alter session set optimizer_goal = frist_rows alter session set optimizer_goal = all_rows 用修改提示的方法：
select --+frist_rows * from table_name where “条件” 或
select /*+frist_rows*/ * from table_name where “条件”
4、 以上是基于代价的优化，还有基于规则的优化已不常用 5、 使用索引改善性能 1) 索引的建立
create [unique] index index_name on table table_name (字段1，字段2 ) 注： 带unique选项表示建立唯一索引 注意： 索引有单列索引，重列索引； 主键、候选键不用建索引；
外键最好键索引；
索引最好建在大表和查询频率高的唯一值的列上； LONG型的列不能键索引； 索引不宜建的太多。 1) 索引的删除 drop index index_name ;
6、 编写共享池中已有的sql语句
用select sql_text from V$sqlarea 可查看共享池中已有的sql语句
二、调整数据库性能
1、 提供足够的内存，减少I/O 操作。
设置 shared_pool_size 、db_block_size、db_block_buffer的大小。（在init.ora中） 2、 减少磁盘竞争
1） 将数据文件和日志文件放在不同磁盘上 如 create database db_name
datafile ?c:\\oracle\\data\\ db_name01.dbf ? logfile ?d:\\ oracle\\data\\ log_name01.dbf? archivelog
2) 将索引和数据建在不同磁盘上
将索引表空间，数据表空间建在不同的磁盘上，分别基于这两个表空间建表和索引。 Create table table_name
( NO number(5) primary key, name varchar2（10） )
tablespace tablespace_in_c;
create index index_name on table_name(NO)
tablespace tablespace_in_d; 3、 调整回滚段 建回滚段：
create public rollback segment rollback_name tablespace RBS optimal to 500k;
注：回滚段建好后和表空间一样处于脱机状态，用修改表空间命令将其联机 修改回滚段：
alter rollback segment rollback_name online| offline storage …… shrink to …… ; 使用回滚段：
savepoint savepoint_7 ; rollback to savepoint_7;
4、 调整网络多线程服务器 1） 设置网络协议的进程调度个数 网络协议的进程调度个数参数 mts_dispatachers = ?tcp,5?,?ipc,5? mts_max_dispatachers = 30 修改网络协议的进程调度个数参数
alter system mts_dispatchers ?tcp,8?, ?ipc,8? , 2） 观察V$dispatacher 数据字典
select name, network, busy, idle from V$dispatacher ; 3） 设置、修改服务器进程个数 mts_servers = 5 mts_max_servers = 20
alter system mts_servers 8 ; 4） 减少检测点
log_checkpoint_interval = 1200 log_checkpoint_timeout = 0 5) 启动检测点监控进程 checkpoint_process = true
5、增加日志组文件
1） create database db_name datafile ?c:\\oracle\\data\\db_name.dbf? logfile group 1 ?c:\\oralce\\log01.dbf? logfile group 2 ?d:\\oralce\\log01.dbf? logfile group 3 ?e:\\oralce\\log01.dbf? archivelog ;
2) alter database db_name
add logfile group 4 ?f:/oracle/log4.daf? ; 3) alter database db_name drop logfile group 4 ; 三、加快数据收索速度 1、 建立索引
create [unique] index index_name
on table_name (字段1、字段2 ) tablespace tablespace_name ; 2、 若表经常被删除、修改 应删除并重建索引 3、 人工强制索引
select * /*+ index (字段1) */ from table_name 4、 建数据簇
create cluster cluster_name (id interger ) size 50 hash is id hashkey 500 ; create table table_name
( 定义子段 …… ) cluster cluster_name （） 5、 并行处理
并行处理机制适用于多机处理机制，它能使用分类、连接、表查询、创建索引等操作并行处理，这对于大型表，尤其是多个大型表的处理十分有效。 Parallel_max_servers = 50 Parallel_min_servers = 5
Parallel_server_idle_time = 10 (单位是分钟) 并行度= Parallel_default_size
= 表中的行数/ Parallel_default_scansize 最大并行度 = Parallel_default_max_scan = 50
创建表时加上parallel 选项：
create table table_name ( 字段1， …… )
parallel 15
select --+parallel ( table_name, 50) from table_name ;
select --+noparallel ( table_name) from table_name ;
对索引并行处理： alter table table_name enable primary key using index parallel alter table table_name enable primary key using index noparallel
四、防止访问冲突 1、 加锁
lock table table_name | view_name
in lock_type mode [ nowait ]
其中lock_type 包括 row share , row exclusive ,share updata , share, exclusive, share row exclusive .
[2002-3-12]
在数据库中对oracle数据库对象的常用操作
一、对象： 表、视图、快照、索引、簇、序列、同义词、数据库链、存储过程、函数、触发器和包、聚集。
1、 table :
建表：
create table table_name (
field_name_1 type( length ) [not null] , …… unique ( ) primary key ( )
foreign key( ) references other_table.field on delete/updata cascade ……
) tablespace tablespace_name;
create table table_name as select * from other_table 查询
select field1, field2,…… from table_name where 条件 group by field having 条件
orader by field asc| desc 删除：
drop table table_name ; 修改：
alter table table_name
add (field_1 type [not null ], ……) alter table table_name
modify (field_1 type [not null ], …… )
insert into table_name (field1,field2,field3 ) values(val1,val2,val3);
insert into table_name select * from table2
update table_name field set field = values where ……
alter table table_name add constraints pk_name primary key (“field_name”) ;
alter table table_name drop constraints pk_name;
alter table child_table_name add constraints fk_name foreign key ( “child_field_name.field ”) references parent_table_name ( parent_table_name ) on delete cascade ;