一、改善数据库性能
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 |