oracal学习笔记

一、改善数据库性能
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 *   from table_name where “条件” 4、 以上是基于代价的优化，还有基于规则的优化已不常用 5、 使用索引改善性能 1) 索引的建立 create [uniq] index index_name on table table_name (字段1，字段2 ) 注： 带uniq选项表示建立唯一索引 注意： 索引有单列索引，重列索引； 主键、候选键不用建索引； 外键最好键索引； 索引最好建在大表和查询频率高的唯一值的列上； 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_b?r的大小。（在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 p lic 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     = tr 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 [uniq] index   index_name on table_name (字段1、字段2 ) tablespace tablespace_name ; 2、 若表经常被删除、修改 应删除并重建索引 3、 人工强制索引 select   *      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] , …… uniq ( ) 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 ) s(val1,val2,val3); insert into table_name select * from table2 update table_name field set field = s 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 seqnce seqnce_name increment by 1 start with 1 max 1024 min 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 ‘nst?’ 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 s less than ( ) tablespace tableapace_name1,         partition part1 s less than ( ) tablespace tableapace_name2,         partition part1 s less than ( ) tablespace tableapace_name3 )     11、 函数 create [or replace] _name ( <参数1> in/out <type> , …… ) return   type is | as 内部变量1 type; 内部变量2 type; begin …… end execute := _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/ORAST Y/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# 线程号 SEQNCE#        序列号    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