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 ;
alter table child_talbe_name drop constrints fk_name cascade ;
2、 视图
建视图
create view view_name as
select * from table_name
删除视图
drop view view_name
3、 快照
建快照
create snapshot snapshot_name
build immediate
using index tablespace ‘tablespace_name’ storage (initial 12k next 12k pctincrease 10)
refresh [complete/force/fast] with [rowed/primary key]
using local rollback segment “rbs0”
on [commit/demand] for update
as select * from table_name
4、 索引
create index index_name on table table_name(field1,field2) ;
drop index index_name ;
5、 簇
create cluster cluster_name (val type )
size 100
storage ( )
[index][hashkeys 200] ;
create table tab_1 (
field1 type
……)
cluster cluster_name ;
6、 序列
create sequence sequence_name
increment by 1
start with 1
maxvalue 1024
minvalue 1
nocycle/cycle
cache 22
noorder/order
7、 同义词
create synonym synonym_name for view/table ;
create synonym synonym_name for view/table@
8、 数据连接
Create database link link_name connect to user_name identified by password using ‘nstude’
9、 存储过程
create[or replace] procedure procdure_name
( <参数1> in/out <type> ,
……
)
is | as
内部变量1 type;
内部变量2 type;
begin
……
end
运行： execute procdure_name
查看参数 print 参数1
10、 分区
create table table_name(
field1 type ,
field2 type )
partition by range (field1)
( partition part1 values less than (value )
tablespace tableapace_name1,
partition part1 values less than (value )
tablespace tableapace_name2,
partition part1 values less than (value )
tablespace tableapace_name3
)
11、 函数
create [or replace] function function_name
( <参数1> in/out <type> ,
……
)
return type
is | as
内部变量1 type;
内部变量2 type;
begin
……
end
execute value := function_name(参数1) ；
12、 触发器
13、 包
二、数据库安全
1、 角色
create role role_name not identified
create role role_name identified by password
create role role_name identified externally
create role role_name identified globally
grant 权限 to role_name ;
2、 用户
建立用户：
create user user_name
profile “default”
identified by “password”
default tablespace tablespace_name
temporary tablespace temp_tablespace_name
account unlock
修改用户：
alter user user_name
三、数据存储
1、 表空间
create tablespace tablespace_name
logging datafile 'D:\ORACLE\ORADATA\ORASTUDY\TEST.ora' size 5M
default storage (initial 10k next 10k pctincrease 20)
alter tablespace tablespace_name default storage (initial 40k)
alter database rename file ‘path\filename.ora’to ‘path1\filename1.ora’
alter database datafile ‘path\filename.ora’ resize 7m
alter database tablespace_name minimum extent 25k
alter database tablespace_name online
alter database tablespace_name offline [normal/temporary/immediate]
注：normal/temporary/immediate 三个参数的选择参考 p295
2、 回滚段
create rollback segment rollback_name tablespace “RBS”
storage ( INITIAL 10K NEXT 10K OPTIMAL 13K MINEXTENTS 5 MAXEXTENTS 120)
alter rollback segment rollback_name online
alter rollback segment rollback_name offline
注 表空间建好后是离线的，需要将其改为在线。
3、 归档重做日志组
增加日志组
alter database add logfile group n （’path\logn.ora’）size 1024k
修改日志组
alter database rename file （’path\logn.ora’）
to （’other_path\ other_logn.ora’）
增加日志组成员
alter database add logfile member ‘path\name_file.ora’to group n
四、备份与回复
exp
imp
（一）冷备份：
1、 关闭数据库
2、 创建备份路径
3、 copy 数据文件、控制文件、系统初始化文件。
4、 打开数据库。
（二）热备份：
1、 转换到归档模式下 。
connect internal
archive log start
查看归档模式的状态：archive log list
2、置为备份状态。
Alter tablespace temp begin backup
3、copy 数据文件
$ copy path1\name1 path2\name2 ；
5、 结束备份
alter tablespace temp end backup
6、 设置系统检测点
alter system checkpoint
6、备份控制文件
alter database backup controlfile to path\name
7、关闭archive log
archive log stop
（三）数据回复
用 V$log_history 可查看日志文件信息:
RECID
STAMP
THREAD# 线程号
SEQUENCE# 序列号
FIRST_CHANGE# 第一次变化号
FIRST_TIME 第一次变化时间
NEXT_CHANGE# 最后一次变化时间
1、不完全回复
1)基于时间点的不完成回复
connect internal
startup mount
recover database until time ‘02/19/99 12:43:55’;
alter database open
2)基于变化号码的不完成回复
connect internal
startup mount
recover database until change 12345 ;
alter database open
3)基于停止的恢复
connect internal
startup mount
recover database ;
(当提示：<ret>= suggest | filename | auto | cancel )作出相应的选择
alter database open ;
2、 完全恢复
connect internal
startup mount
recover database
alter database open
3、 仅对表空间回复
recover tablespace tablespace_name
alter database open
4、 联机恢复
当数据库工作在archive模式下，当system表空间没有损坏，方可进行联机恢复。
Alter tablespace tablespace_name offline ;
Recover tablespace tablespace_name ;
Alter tablespace tablespace_name online ;
5、 回滚段恢复
create rollback segment rollback_name
tablespace tablespace_name ;
savepoint savepoint_8
rollback to savepoint_8