oracle优化杂记

此文为转载。

ORACLE查询性能最差的SQL脚本

SELECT * FROM
(
SELECT PARSING_USER_ID
EXECUTIONS,
SORTS,
COMMAND_TYPE,
DISK_READS,
sql_text
FROM v$sqlarea
ORDER BY disk_reads DESC
) where rownum < 10

---------------------------------------------------------------------------------------------

Oracle性能优化的基本方法概述

 

1）设立合理的性能优化目标。
2）测量并记录当前性能。
3）确定当前Oracle性能瓶颈（Oracle等待什么、哪些SQL语句是该等待事件的成分）。
4）把等待事件记入跟踪文件。
5）确定当前的OS瓶颈。
6）优化所需的成分（应用程序、数据库、I/O、争用、OS等）。
7）跟踪并实施更改控制过程。
8）测量并记录当前性能
9）重复步骤3到7，直到满足优化目标

1．设立合理的性能优化目标
重点：关于设立目标的最重要的一点是它们必须是可量化和可达到的。
方法：目标必须是当前性能和所需性能的的陈述形式的语句。只需填写下列语句中的空格即可。
花费了 （时/分/秒），但要求它在 （时/分/秒）内执行。
使用了 （资源量），但它不能使用超过 。

2． 测量并记录当前性能
重点：
（1）需要在峰值活动时间获得当前系统性能快照
（2）关键是要在出现性能问题的时间段内采集信息
（3）必须在合理的时间段上采集，一般在峰值期间照几个为期15分钟的快照
方法：执行STATSPACK
-- 建立性能快照表空间
sqlplus sys as sysdba
create tablespace perfstat datafile '/u02/oradata/dbnms/perfstat.dbf' size 500M extent management local;
-- 安装STATSPACK
@$ORACLE_HOME/rdbms/admin/spcreate.sql;
-- 获取性能数据，可以生成多个快照
sqlplus perfstat
execute statspack.snap;
-- 生成性能快照的报表
sqlplus perfstat
select min(snap_id) snapid_min, max(snap_id) snapid_max from stats$snapshot;
@$ORACLE_HOME/rdbms/admin/spreport;
-- 该报告中有关于性能的重要信息，如前5位的等待事件、cache大小、各种内存结构的命中率、每秒及每事务逻辑、物理读写数据块数、性能最差的sql语句等

3． 确定当前Oracle性能瓶颈
重点：从Oracle 等待接口v$system_event、v$session_event和v$session_wait中获得等待事件，进而找出影响性能的对象和sql语句
方法：
-- 首先，利用v$system_event视图执行下面的查询查看数据库中某些常见的等待事件：
select * from v$system_event
where event in ('buffer busy waits',
'db file sequential read',
'db file scattered read',
'enqueue',
'free buffer waits',
'latch free',
'log file parallel write',
'log file sync');
-- 接着，利用下面对v$session_event和v$session视图进行的查询，研究具有对上面显示的内容有贡献的等待事件的会话：
select se.sid,s.username,se.event,se.total_waits,se.time_waited,se.average_wait
from v$session s,v$session_event se
where s.sid = se.sid
and se.event not like 'SQL*Net%'
and s.status = 'ACTIVE'
and s.username is not null;
-- 使用下面查询找到与所连接的会话有关的当前等待事件。这些信息是动态的，为了查看一个会话的等待最多的事件是什么，需要多次执行此查询。
select sw.sid,s.username,sw.event,sw.wait_time,sw.state,sw.seconds_in_wait SEC_IN_WAIT
from v$session s,v$session_wait sw
where s.sid = sw.sid
and sw.event not like 'SQL*Net%'
and s.username is not null
order by sw.wait_time desc;
-- 查询会话等待事件的详细信息
select sid,event,p1text,p1,p2text,p2,p3text,p3
from v$session_wait
where sid between &1 and &2
and event not like '%SQL%'
and event not like '%rdbms%';
-- 利用P1、P2的信息，找出等待事件的相关的段
select owner,segment_name,segment_type,tablespace_name
from dba_extents
where file_id = &fileid_in
and &blockid_in between block_id and block_id + blocks - 1;
-- 获得操作该段的sql语句：
select sid, getsqltxt(sql_hash_value,sql_address)
from v$session
where sid = &sid_in;
-- getsqltxt函数
create or replace
function GetSQLtxt (hashaddr_in in v$sqltext.hash_value%type,
addr_in in v$sqltext.address%type)
return varchar2
is
temp_sqltxt varchar2(32767);
cursor sqlpiece_cur
is
select piece,sql_text
from v$sqltext
where hash_value = hashaddr_in
and address = addr_in
order by piece;
begin
for sqlpiece_rec in sqlpiece_cur
loop
temp_sqltxt := temp_sqltxt || sqlpiece_rec.sql_text;
end loop;
return temp_sqltxt;
end GetSQLtxt;
-- 至此已经找到影响性能的对象和sql语句，可以有针对性地优化

4． 把等待事件记入跟踪文件
重点：如果在跟踪系统上的等待事件时，由于某种原因遇到了麻烦，则可以将这些等待事件记入一个跟踪文件。
方法：
-- 对于当前会话：
alter session set timed_statistics=true;
alter session set max_dump_file_size=unlimited;
alter session set events '10046 trace name context forever, level 12';
-- 执行应用程序，然后在USER_DUMP_DEST指出的目录中找到跟踪文件。
-- 查看文件中以词WAIT开始的所有行。

-- 对于其它的会话
-- 确定会话的进程ID（SPID）。下面的查询识别出名称以A开始的所有用户的会话进程ID：
select S.Username, P.Spid from V$SESSION S, V$PROCESS P
where S.PADDR = P.ADDR and S.Username like 'A%';
-- 以 sysdba 进入sqlplus执行
alter session set timed_statistics=true;
alter session set max_dump_file_size=unlimited;
oradebug setospid ＜SPID＞
oradebug unlimit
oradebug event 10046 trace name context forever, level X /* Where X = (1,4,8,12) */
-- 跟踪某个时间间隔得会话应用程序。
-- 在USER_DUMP_DEST 的值指出的目录中利用SPID查看跟踪文件
-- 查看文件中以词WAIT开始的所有行。

5． 确定当前OS瓶颈
（1）Windows NT上的监控
使用控制面板-〉管理工具-〉性能即可

（2）UNIX上的监控
使用通用性的工具，包括sar、iostat、cpustat、mpstat、netstat、top、osview等。
1） CPU使用情况
sar -u 5 1000
%sys和%wio的数值应该小于百分之10到15
2） 设备使用情况
sar -d 5 1000
在%busy超过60%时，最佳设备利用率开始降低；在具有足够磁盘高速缓存的系统上，认为avserv为100毫秒的值非常高。
3） 虚拟内存使用情况
vmstat -S 5 1000
执行队列（r）应该明确的平均小于（2*CPU数目）

6．优化所需的成分（应用程序、数据库、I/O、争用、OS等）。

7．跟踪并实施更改控制过程。

8．测量并记录当前性能

9．重复步骤3到7，直到满足优化目标

——————————————————————————————————————————————————

oracle表空间

 

============  查询 ===================

1.查询oracle 用户的默认表空间和临时表空间
select default_tablespace, temporary_tablespace, d.username
  from dba_users d
 where d.username like '%YGJ%'
 group by default_tablespace, temporary_tablespace, d.username

 

2、查看表空间的名称及大小  
select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size  
from dba_tablespaces t, dba_data_files d  
where t.tablespace_name = d.tablespace_name  
group by t.tablespace_name;  

 

3. 查询当前数据库中的所有的临时表空间

select distinct tablespace_name from dba_temp_files;

4、查看表空间物理文件的名称及大小  
select tablespace_name, file_id, file_name,  
round(bytes/(1024*1024),0) total_space  
from dba_data_files  
order by tablespace_name; 

 

5、查看表空间的使用情况  
select sum(bytes)/(1024*1024) as free_space,tablespace_name  
from dba_free_space  
group by tablespace_name;  
SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,  
(B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"  
FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C  
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME;  

 

 

============  创建 =================

//创建临时表空间
create temporary tablespace ygj_temp
tempfile '/opt/oracle10g/oradata/orcl/ygj_temp.dbf'
size 32m
autoextend on
next 32m maxsize 2048m
extent management local;

/创建数据表空间
create tablespace ygj_data
logging
datafile '/opt/oracle10g/oradata/orcl/ygj_data1.dbf'
size 32m
autoextend on
next 32m maxsize 2048m
extent management local;

//创建用户并指定表空间
create user atf_ygj  identified by password
default tablespace  ygj_data
temporary tablespace ygj_temp;

//给用户授予权限
grant connect,resource to username;

//以后以该用户登录，创建的任何数据库对象都属于test_temp 和test_data表空间，这就不用在每创建一个对象给其指定表空间了。

=============  移动数据到表空间  ================= 

查询需要移动的表所在的表空间

  select tt.table_name,tt.tablespace_name  from user_all_tables tt where tt.tablespace_name like '%YGJ%'

 移动表到指定表空间

 alter table employees move tablespace ygj_data;

查询要移动的索引所在的表空间

select ii.index_name,ii.table_name,ii.tablespace_name,ii.temporary from user_indexes ii where index_name like  '%EMP_PK%'
 移动（重建）索引到指定表空间

 alter index EMP_PK rebuild tablespace ygj_data;

 

============  修改 =================

重命名表空间

alter tablespace atf_ygj_data rename to ygj_data;

修改系统默认的表空间

alter database default tablespace ygj_data;

 

 

修改用户临时表空间

ALTER USER atf_ygj2 TEMPORARY TABLESPACE  ygj_temp

 

修改用户临时表空间

ALTER USER atf_ygj2 TEMPORARY TABLESPACE  ygj_temp

 

============  删除 =================

 删除表空间（只有表空间中没有任何的数据时才能删除）

drop tablespace ygj_data;

 

删除临时表空间

drop tablespace ygj_temp;

删除用户(CASCADE会把该用户的全部表等关联信息一并删除 )

drop user atf_ygj2 CASCADE;

 

 

 

 

 

 ==============================

在oracle 对象的重命名始终都是个麻烦的事情，这些对象主要是指表名，索引名，列名，表空间名。

 

在8i 的时候提供了对表名和索引名的重命名功能：
SQL> alter table sunwg rename to sunwg01;

表已更改。

SQL> alter index ind_sunwg rename to ind_sunwg01;

索引已更改。

在9i 的时候提供对表中的列的重命名功能：

SQL> alter table sunwg   rename column owner to owner_1;

表已更改。

在10g的时候提供对表空间的重命名功能：

SQL> alter tablespace test rename to test01;

============================================

 

 

 

优化数据库大幅度提高Oracle的性能

 

 

 ORACLE 中，表空间是数据管理的基本方法，所有用户的对象要存放在表空间中，也就是用户有空间的使用权，才能创建用户对象．否则是不充许创建对象，因为就是想创建对象,如表,索引等，也没有地方存放,Oracle 会提示:没有存储配额．

　　因此，在创建对象之前，首先要分配存储空间．
　　分配存储，就要创建表空间：
　　创建表空间示例如下：
CREATE TABLESPACE  "SAMPLE"
    LOGGING
    DATAFILE 'D:\ORACLE \ORADATA\ORA92\LUNTAN.ora' SIZE 5M
EXTENT    MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO 
上面的语句分以下几部分：
第一:  CREATE TABLESPACE  "SAMPLE" 　创建一个名为 "SAMPLE"  的表空间.
    对表空间的命名,遵守Oracle 的命名规范就可了.
   ORACLE 可以创建的表空间有三种类型:
  (1)TEMPORARY: 临时表空间,用于临时数据的存放;
创建临时表空间的语法如下:
CREATE TEMPORARY TABLESPACE "SAMPLE"......
  (2)UNDO : 还原表空间.  用于存入重做日志文件.
创建还原表空间的语法如下:
CREATE UNDO  TABLESPACE "SAMPLE"......
(3)用户表空间: 最重要,也是用于存放用户数据表空间
    可以直接写成: CREATE TABLESPACE  "SAMPLE"
TEMPORARY 和 UNDO 表空间是ORACLE 管理的特殊的表空间.只用于存放系统相关数据.
第二:   LOGGING
  有 NOLOGGING  和 LOGGING  两个选项,
      NOLOGGING:  创建表空间时,不创建重做日志.
     LOGGING 和NOLOGGING正好相反, 就是在创建表空间时生成重做日志.
用 NOLOGGING时,好处在于创建时不用生成日志,这样表空间的创建较快,但是没能日志,数据丢失后,不能恢复,但是一般我们在创建表空间时,是没有数 据的,按通常的做法,是建完表空间,并导入数据后,是要对数据做备份的,所以通常不需要表空间的创建日志,因此,在创建表空间时,选择  NOLOGGING,以加快表空间的创建速度.
第三: DATAFILE 用于指定数据文件的具体位置和大小.
 如: DATAFILE 'D:\ORACLE \ORADATA\ORA92\LUNTAN.ora' SIZE 5M
说明文件的存放位置是  'D:\ORACLE \ORADATA\ORA92\LUNTAN.ora' , 文件的大小为5M.
如果有多个文件,可以用逗号隔开:
 DATAFILE 'D:\ORACLE \ORADATA\ORA92\LUNTAN.ora' SIZE 5M,
    'D:\ORACLE \ORADATA\ORA92\dd.ora' SIZE 5M
但是每个文件都需要指明大小.单位以指定的单位为准如 5M 或 500K.
对具体的文件,可以根据不同的需要,存放大不同的介质上,如磁盘阵列,以减少IO竟争.
指定文件名时,必须为绝对地址,不能使用相对地址.
第四: EXTENT  MANAGEMENT LOCAL  存储区管理方法
在Oracle 8i以前,可以有两种选择,一种是在字典中管理(DICTIONARY),另一种是本地管理(LOCAL ),从9I开始,只能是本地管理方式.因为LOCAL 管理方式有很多优点.
在 字典中管理(DICTIONARY): 将数据文件中的每一个存储单元做为一条记录,所以在做DM操作时,就会产生大量的对这个管理表的Delete和Update操作.做大量数据管理时,将会 产生很多的DM操作,严得的影响性能,同时,长时间对表数据的操作,会产生很多的磁盘碎片,这就是为什么要做磁盘整理的原因.
本地管理(LOCAL): 用二进制的方式管理磁盘,有很高的效率,同进能最大限度的使用磁盘. 同时能够自动跟踪记录临近空闲空间的情况，避免进行空闲区的合并操作。
第五:  SEGMENT SPACE MANAGEMENT  
磁盘扩展管理方法:
SEGMENT SPACE MANAGEMENT: 使用该选项时区大小由系统自动确定。由于 Oracle 可确定各区的最佳大小，所以区大小是可变的。
UNIFORM SEGMENT SPACE MANAGEMENT:指定区大小，也可使用默认值 (1 MB)。
第六: 段空间的管理方式:
AUTO: 只能使用在本地管理的表空间中. 使用LOCAL管理表空间时,数据块中的空闲空间增加或减少后，其新状态都会在位图中反映出来。位图使 Oracle 管理空闲空间的行为更加自动化，并为管理空闲空间提供了更好的性,但对含有LOB字段的表不能自动管理.
MANUAL: 目前已不用,主要是为向后兼容.
 
第七: 指定块大小. 可以具体指定表空间数据块的大小.
 
 
创建例子如下:
 1 CREATE TABLESPACE "SAMPLE"
 2      LOGGING
 3      DATAFILE 'D:\ORACLE \ORADATA\ORA92\SAMPLE.ora' SIZE 5M,
 4      'D:\ORACLE \ORADATA\ORA92\dd.ora' SIZE 5M
 5      EXTENT MANAGEMENT LOCAL
 6      UNIFORM SEGMENT SPACE MANAGEMENT
 7*     AUTO
SQL> /
 
表空间已创建。
 
要删除表空间进,可以
 
SQL> DROP TABLESPACE SAMPLE;
 
表空间已丢弃。

————————————————————————————————————————————————

Problem Description:
1.每个表的结构及主键索引情况
2.每个表的count(*)记录是多少
3.对于创建索引的列，索引的类型是什么？count(distinct indexcol)的值是多少？
4.最后一次对表进行分析是在什么时间，分析后，是否又对相关表做过大的操作
5.索引最后一次rebuild，是在什么时间，此后对表的操作类型又是什么状况？索引中浪费的空间是多少？
6.这些表的存储情况，表的存储参数，表空间的类型，存储参数等
7.执行该SQL语句时，系统等候的资源是什么？ Trace SQL语句的执行过程
8.另一台执行相似SQL速度很快的机器上的相关表的如上信息是什么？

一：SQL tuning 类
1:列举几种表连接方式
  hash join/merge join/nest loop(cluster join)/index join

2:不借助第三方工具，怎样查看sql的执行计划
set autotrace on
set autotrace traceonly

explain plan set statement_id = &item_id for &sql;
select * from table(dbms_xplan.display);
http://download-west.oracle.com/ ... /b10752/ex_plan.htm

3:如何使用CBO,CBO与RULE的区别
  在optimizer_mode=choose时,如果表有统计信息（分区表外）,优化器将选择CBO,否则选RBO。

  RBO遵循简单的分级方法学,使用15种级别要点，当接收到查询，优化器将评估使用到的要点数目,
然后选择最佳级别（最少的数量）的执行路径来运行查询。
CBO尝试找到最低成本的访问数据的方法,为了最大的吞吐量或最快的初始响应时间,计算使用不同
的执行计划的成本，并选择成本最低的一个,关于表的数据内容的统计被用于确定执行计划。

4:如何定位重要(消耗资源多)的SQL
  select sql_text
  from v$sql
where disk_reads > 1000 or (executions > 0 and buffer_gets/executions > 30000);

5:如何跟踪某个session的SQL
  exec dbms_system.set_sql_trace_in_session(sid,serial#,&sql_trace);

select sid,serial# from v$session where sid = (select sid from v$mystat where rownum = 1);
  exec dbms_system.set_ev(&sid,&serial#,&event_10046,&level_12,'');

6:SQL调整最关注的是什么
  查看该SQL的response time(db block gets/consistent gets/physical reads/sorts (disk))

7:说说你对索引的认识（索引的结构、对dml影响、为什么提高查询性能）
  b-tree index/bitmap index/function index/patitional index(local/global)
  索引通常能提高select/update/delete的性能,会降低insert的速度,
 
8:使用索引查询一定能提高查询的性能吗？为什么
  索引就是为了提高查询性能而存在的,
如果在查询中索引没有提高性能,
只能说是用错了索引,或者讲是场合不同

9:绑定变量是什么？绑定变量有什么优缺点？
  绑定变量是相对文本变量来讲的,所谓文本变量是指在SQL直接书写查询条件，
这样的SQL在不同条件下需要反复解析,绑定变量是指使用变量来代替直接书写条件，
查询bind value在运行时传递，然后绑定执行。
 
优点是减少硬解析,降低CPU的争用,节省shared_pool
缺点是不能使用histogram,sql优化比较困难

10:如何稳定(固定)执行计划
  query_rewrite_enabled = true
  star_transformation_enabled = true
  optimizer_features_enable = 9.2.0

创建并使用stored outline
  http://download-west.oracle.com/ ... /outlines.htm#26854
 
这个贴子:
http://www.cnoug.org/viewthread.php?tid=27598
 
11:和排序相关的内存在8i和9i分别怎样调整，临时表空间的作用是什么
   8i中sort_area_size/sort_area_retained_size决定了排序所需要的内存
  如果排序操作不能在sort_area_size中完成,就会用到temp表空间

  9i中如果workarea_size_policy=auto时,
  排序在pga内进行,通常pga_aggregate_target的1/20可以用来进行disk sort;
  如果workarea_size_policy=manual时,排序需要的内存由sort_area_size决定

   在执行order by/group by/distinct/union/create index/index rebuild/minus等操作时,
  如果在pga或sort_area_size中不能完成,排序将在临时表空间进行（disk sort）,
  临时表空间主要作用就是完成系统中的disk sort.

12:存在表T(a,b,c,d),要根据字段c排序后取第21—30条记录显示，请给出sql
   create table t(a number(,b number(,c number(,d number();
  /
  begin      
    for i in 1 .. 300 loop
      insert into t values(mod(i,2),i/2,dbms_random.value(1,300),i/4);
    end loop;
  end;
  /
   select * from (select c.*,rownum as rn from (select * from t order by c desc) c) where rn between 21 and 30;
   /
  select * from (select * from test order by c desc) x where rownum < 30
  minus
   select * from (select * from test order by c desc) y where rownum < 20 order by 3 desc
   相比之 minus性能较差

二：数据库基本概念类

1:pctused and pctfree 表示什么含义有什么作用
  pctused与pctfree控制数据块是否出现在freelist中,
pctfree控制数据块中保留用于update的空间,当数据块中的free space小于pctfree设置的空间时,
该数据块从freelist中去掉,当块由于dml操作free space大于pct_used设置的空间时,该数据库块将
被添加在freelist链表中。
 --PCTFREE存储参数
　　PCTFREE存储参数告诉ORACLE什么时候应该将数据块从对象的空闲列表中移出。ORACLE的默认参数是PCTFREE=10；
也就是说，一旦一个INSERT操作使得数据块的90%被使用，这个数据块就从空闲列表(free list)中移出。
　--PCTUSED存储参数
　　PCTUSED存储参数告诉ORACLE什么时候将以前满的数据块加到空闲列表中。当记录从数据表中删除时，数据库的数
据块就有空间接受新的记录，但只有当填充的空间降到PCTUSED值以下时，该数据块才被连接到空闲列表中，才可以往
其中插入数据。PCTUSED的默认值是PCTUSED=40。
 --存储参数规则小结
　　（1）PCTUSED较高意味着相对较满的数据块会被放置到空闲列表中，从而有效的重复使用数据块的空间，但会导致
I/O消耗。PCTUSED低意味着在一个数据块快空的时候才被放置到空闲列表中，数据块一次能接受很多的记录，因此可以
减少I/O消耗，提高性能。
　　（2）PCTFREE的值较大意味着数据块没有被利用多少就从空闲列表中断开连接，不利于数据块的充分使用。PCTFREE
过小的结果是，在更新时可能会出现数据记录迁移(Migration)的情况。(注：数据记录迁移(Migration)是指记录在是
UPDATE操作扩展了一个VARCHAR2类型的列或BLOB列后,PCTFREE参数所指定的空间不够扩展，从而记录被ORACLE强制迁移到
新的数据块，发生这种情况将较严重的影响ORACLE的性能，出现更新缓慢)。
　　（3）在批量的插入、删除或者更新操作之前，先删除该表上的索引，在操作完毕之后在重新建立，这样有助于提高
批量操作的整体速度，并且保证B树索引在操作之后有良好的性能。

---------------------------------------------------------------------------------------------------------------------------------------------------
[e-mail] 17/05/2007--
--表的pctfree和pctused两个参数进行估算的方法

对于不同的应用系统，表的pctfree 和pctused两个参数有不同的设计原则，以下是根据特定的应用系统进行估算的例子，从中可以掌握基本的估算方法。
 
表的存储参数调整，一般情况，设置为pctfree 5 pctused 85即可（缺省为pctfree 10 pctused 40）
1.对于Pctfree参数
除了可以按字段及字段长度估算平均行长外，下面的方面可以根据已有数据分析出平均行长和每块行数
例：
analyze table 病人信息 compute statistics for table for all indexes for all indexed columns;
Select Num_Rows,Blocks,Round(Num_Rows / Blocks) Avg_Rows_Block, Avg_Row_Len From User_Tables Where Table_Name = '病人信息'

    NUM_ROWS BLOCKS AVG_ROWS_BLOCK AVG_ROW_LEN   
 857291 14161 61 117 

对于一般8192的块，实际可用空间为8100左右.
假设以前该表的pctfree为15，改为5后，pctfree减少10，就可以再存入约7行.

相同的1万4千块就可以多存放约10万行数据，
这10万行数据，如果按每块60行算，就可以少占用约1700块(约13M的空间)
如果全表扫描该表的话，少读1700块数据，少106次IO操作(按缺省db_file_multiblock_read_count=16计算)
少占13M的内存

另外，需要考虑的两个因素
1。更新操作时，数据增长量大不大，例如：主要是把状态字段由1改为3，还是把摘要由空改为一段文字
2。并发事务的多少，因为一个事务信息在块中要占用约24Byte，如果有10个并发事务的话，至少额外考虑240Byte的空闲空间。

2.对于Pctused参数
主要考虑删除后插入数据的情况多不多，以及平均行长大小
例如：
病人费用记录，医保如果存在校对操作的话，是先产生预交结算数据，正式结算时，删除这些数据再重新生成
所以，病人预交记录,Pctused不能设置太高，否则重用那些低于Pctused的块，只能插入少量数据行，增加了IO操作
analyze table 病人预交记录 compute statistics for table for all indexes for all indexed columns;
Select Num_Rows,Blocks,Round(Num_Rows / Blocks) Avg_Rows_Block, Avg_Row_Len From User_Tables Where Table_Name = '病人预交记录'

    NUM_ROWS BLOCKS AVG_ROWS_BLOCK AVG_ROW_LEN   
 181758 2147 85 83 

如果设置pctfree 5 pctused 85，那么当删除一些行使块的已用空间低于85%时，块会被重用，但是因为要预留5%的空闲空间，
所以,对于已用空间刚刚低于85%的块，重用空间就只有10%,对于8K的块，可用810Byte,平均行长83,可以再放入9行，所以这个参数也是可以的。

但是，如果是病人费用记录,平均行长229，这样设置，只能放下3行，这个参数就不太合适了
 
    NUM_ROWS BLOCKS AVG_ROWS_BLOCK AVG_ROW_LEN   
 925133 38278 24 229 

根据分析，病人费用记录的数据更新量不大，但是并发操作比较大，最好把Pctfree设置高一点
所以，可以设置为pctfree 10 pctused 75（重用的块至少可以放5行，约1-2张单据）,甚至pctused 70也是可以的。

如果一个块的数据行数太多，可能造成热块争用，但是相对于减少存储，减少IO，减少内存占用带来的好处来说，热块不是特别突出的情况下可以不考虑。

[e-mail] 17/05/2007--

PCTFREE=(Average Row Size-Initial Row Size)*100/Average Row Size
PCTUSED=(100-PCTFREE) -Average Row Size * 100/Availabe Data Space
　　
　　Oracle的其中一个优点时它可以管理每个表空间中的自由空间。Oracle负责处理表和索引的空间管理，这样就可以让我们无需懂得Oracle的表和索引的
内部运作。不过，对于有经验的Oracle调优专家来说，他需要懂得Oracle是如何管理表的extent和空闲的数据块。对于调整拥有高的insert或者update的系
统来说，这是非常重要的。
　　
　　要精通对象的调整，你需要懂得freelists和freelist组的行为，它们和pctfree及pctused参数的值有关。这些知识对于企业资源计划（ERP）的应用是
特别重要的，因为在这些应用中，不正确的表设置通常是DML语句执行慢的原因。　
　　对于初学者来说，最常见的错误是认为默认的Oracle参数对于所有的对象都是最佳的。除非磁盘的消耗不是一个问题，否则在设置表的pctfree和pctused
参数时，就必须考虑平均的行长和数据库的块大小，这样空的块才会被有效地放到freelists中。当这些设置不正确时，那些得到的freelists也是"dead"块，
因为它们没有足够的空间来存储一行，这样将会导致明显的处理延迟。
　　Freelists对于有效地重新使用Oracle表空间中的空间是很重要的，它和pctfree及pctused这两个存储参数的设置直接相关。如果将pctused设置为一个高的值，
这时数据库就会尽快地重新使用块。不过，高性能和有效地重新使用表的块是对立的。在调整Oracle的表格和索引时，需要认真考虑究竟需要高性能还是有效的空
间重用，并且据此来设置表的参数。以下我们来看一下这些freelists是如何影响Oracle的性能的。
　　
　　当有一个请求需要插入一行到表格中时，Oracle就会到freelist中寻找一个有足够的空间来容纳一行的块。你也许知道，freelist串是放在表格或者索引的第
一个块中，这个块也被称为段头（segment header）。pctfree和pctused 参数的唯一目的就是为了控制块如何在freelists中进出。虽然freelist link和 unlink
是简单的Oracle功能，不过设置freelist link (pctused) 和unlink (pctfree) 对Oracle的性能确实有影响。
　　
　　由DBA的基本知识知道，pctfree参数是控制freelist un-links的（即将块由freelists中移除）。设置pctfree=10 意味着每个块都保留10%的空间用作行扩展。
pctused参数是控制freelist re-links的。设置pctused=40意味着只有在块的使用低于40%时才会回到表格的freelists中。
　　
　　许多新手对于一个块重新回到freelists后的处理都有些误解。其实，一旦由于一个删除的操作而令块被重新加入到freelist中，它将会一直保留在freelist中
即使空间的使用超过了60%，只有在到达pctfree时才会将数据块由freelist中移走。
　　
　　表格和索引存储参数设置的要求总结
　　
　　以下的一些规则是用来设置freelists, freelist groups, pctfree和pctused存储参数的。你也知道，pctused和pctfree的值是可以很容易地通过alter table
命令修改的，一个好的DBA应该知道如何设置这些参数的最佳值。
　　
　　有效地使用空间和高性能之间是有矛盾的，而表格的存储参数就是控制这个方面的矛盾：
　　
　　. 对于需要有效地重新使用空间，可以设置一个高的pctused值，不过副作用是需要额外的I/O。一个高的pctused值意味着相对满的块都会放到freelist中。因
此，这些块在再次满之前只可以接受几行记录，从而导致更多的I/O。
　　
　　. 追求高性能的话，可以将pctused设置为一个低的值，这意味着Oracle不会将数据块放到freelists中直到它几乎是空的。那么块将可以在满之前接收更多的行，
因此可以减少插入操作的I/O。要记住Oracle扩展新块的性能要比重新使用现有的块高。对于Oracle来说，扩展一个表比管理freelists消耗更少的资源。
　　
　　让我们来回顾一下设置对象存储参数的一些常见规则：
　　
　　．经常将pctused设置为可以接收一条新行。对于不能接受一行的free blocks对于我们来说是没有用的。如果这样做，将会令Oracle的性能变慢，因为Oracle将
在扩展表来得到一个空的块之前，企图读取5个"dead" 的free block。
　　
　　．表格中chained rows的出现意味着pctfree太低或者是db_block_size太少。在很多情况下，RAW和LONG RAW列都很巨大，以至超过了Oracle的最大块的大小，
这时chained rows是不可以避免的。
　　
　　．如果一个表有同时插入的SQL语句，那么它需要有同时删除的语句。运行单一个一个清除的工作将会把全部的空闲块放到一个freelist中，而没有其它包含有任何
空闲块的freelists出现。
　　
　　．freelist参数应该设置为表格同时更新的最大值。例如，如果在任何时候，某个表最多有20个用户执行插入的操作，那么该表的参数应该设置为freelists=20。
　　
　　应记住的是freelist groups参数的值只是对于Oracle Parallel Server和Real Application Clusters才是有用的。对于这类Oracle，freelist groups应该设置
为访问该表格的Oracle Parallel Server实例的数目。

---------------------------------------------------------------------------------------------------------------------------------------------------

2:简单描述table / segment / extent / block之间的关系
  table创建时,默认创建了一个data segment,
每个data segment含有min extents指定的extents数,
每个extent据据表空间的存储参数分配一定数量的blocks

3:描述tablespace和datafile之间的关系
一个tablespace可以有一个或多个datafile,每个datafile只能在一个tablespace内,
table中的数据,通过hash算法分布在tablespace中的各个datafile中,
tablespace是逻辑上的概念,datafile则在物理上储存了数据库的种种对象。

4:本地管理表空间和字典管理表空间的特点，ASSM有什么特点
  本地管理表空间（Locally Managed Tablespace简称LMT）
  8i以后出现的一种新的表空间的管理模式，通过位图来管理表空间的空间使用。
  字典管理表空间（Dictionary-Managed Tablespace简称DMT）
  8i以前包括以后都还可以使用的一种表空间管理模式，通过数据字典管理表空间的空间使用。
  动段空间管理（ASSM），
  它首次出现在Oracle920里有了ASSM，链接列表freelist被位图所取代，它是一个二进制的数组，
  能够迅速有效地管理存储扩展和剩余区块（free block），因此能够改善分段存储本质，
  ASSM表空间上创建的段还有另外一个称呼叫Bitmap Managed Segments（BMB 段）。

5:回滚段的作用是什么
事务回滚：当事务修改表中数据的时候，该数据修改前的值（即前影像）会存放在回滚段中，
          当用户回滚事务（ROLLBACK）时，ORACLE将会利用回滚段中的数据前影像来将修改的数据恢复到原来的值。
　
  事务恢复：当事务正在处理的时候，例程失败，回滚段的信息保存在undo表空间中，
           ORACLE将在下次打开数据库时利用回滚来恢复未提交的数据。

　读一致性：当一个会话正在修改数据时，其他的会话将看不到该会话未提交的修改。
           当一个语句正在执行时，该语句将看不到从该语句开始执行后的未提交的修改（语句级读一致性）
      当ORACLE执行SELECT语句时，ORACLE依照当前的系统改变号（SYSTEM CHANGE NUMBER-SCN）
      来保证任何前于当前SCN的未提交的改变不被该语句处理。可以想象：当一个长时间的查询正在执行时，
      若其他会话改变了该查询要查询的某个数据块，ORACLE将利用回滚段的数据前影像来构造一个读一致性视图。
  http://www.itpub.net/showthread. ... E%B5%C4%D7%F7%D3%C3

6:日志的作用是什么
  记录数据库事务,最大限度地保证数据的一致性与安全性

  重做日志文件：含对数据库所做的更改记录，这样万一出现故障可以启用数据恢复,一个数据库至少需要两个重做日志文件
  归档日志文件：是重做日志文件的脱机副本，这些副本可能对于从介质失败中进行恢复很必要。

http://www.cnoug.org/viewthread. ... hlight=%C8%D5%D6%BE

7:SGA主要有那些部分，主要作用是什么
  SGA：db_cache/shared_pool/large_pool/java_pool
db_cache:
  数据库缓存（Block Buffer）对于Oracle数据库的运转和性能起着非常关键的作用，
   它占据Oracle数据库SGA（系统共享内存区）的主要部分。Oracle数据库通过使用LRU
   算法，将最近访问的数据块存放到缓存中，从而优化对磁盘数据的访问.
shared_pool:
  共享池的大小对于Oracle 性能来说都是很重要的。
  共享池中保存数据字典高速缓冲和完全解析或编译的的PL/SQL 块和SQL 语句及控制结构
large_pool:
  使用MTS配置时，因为要在SGA中分配UGA来保持用户的会话，就是用Large_pool来保持这个会话内存
   使用RMAN做备份的时候，要使用Large_pool这个内存结构来做磁盘I/O缓存器
java_pool:
  为java procedure预备的内存区域,如果没有使用java proc,java_pool不是必须的
  
8racle系统进程主要有哪些，作用是什么
  数据写进程(dbwr)：负责将更改的数据从数据库缓冲区高速缓存写入数据文件
  日志写进程(lgwr)：将重做日志缓冲区中的更改写入在线重做日志文件
  系统监控(smon)  ：检查数据库的一致性如有必要还会在数据库打开时启动数据库的恢复
  进程监控(pmon)  ：负责在一个Oracle 进程失败时清理资源
  检查点进程(chpt)：负责在每当缓冲区高速缓存中的更改永久地记录在数据库中时,更新控制文件和数据文件中的数据库状态信息。
  归档进程(arcn)  ：在每次日志切换时把已满的日志组进行备份或归档
  作业调度器(cjq)  :负责将调度与执行系统中已定义好的job,完成一些预定义的工作.
恢复进程(reco)  :保证分布式事务的一致性,在分布式事务中,要么同时commit,要么同时rollback;

三：备份恢复类

1:备份如何分类
逻辑备份：exp/imp
物理备份：
    RMAN备份
     full backup/incremental backup(累积/差异)
     热备份:alter tablespace begin/end backup;
     冷备份:脱机备份(database shutdown)
    
2:归档是什么含义
关于归档日志：Oracle要将填满的在线日志文件组归档时,则要建立归档日志（archived redo log）。
其对数据库备份和恢复有下列用处：
    <1>数据库后备以及在线和归档日志文件，在操作系统和磁盘故障中可保证全部提交的事物可被恢复。
    <2>在数据库打开和正常系统使用下，如果归档日志是永久保存，在线后备可以进行和使用。
  数据库可运行在两种不同方式下：
   NOARCHIVELOG方式或ARCHIVELOG 方式
数据库在NOARCHIVELOG方式下使用时，不能进行在线日志的归档,
如果数据库在ARCHIVELOG方式下运行，可实施在线日志的归档。

3:如果一个表在2004-08-04 10:30:00 被drop，在有完善的归档和备份的情况下，如何恢复?
  手工拷贝回所有备份的数据文件
sql>startup mount;
sql>alter database recover automatic until time '2004-08-04:10:30:00';
sql>alter database open resetlogs;

4:rman是什么,有何特点?
RMAN(Recovery Manager)是DBA的一个重要工具，用于备份、还原和恢复oracle数据库,
RMAN 可以用来备份和恢复数据库文件、归档日志、控制文件、系统参数文件,也可以用来执行完全或不完全的数据库恢复。
RMAN有三种不同的用户接口：
  COMMAND LINE方式、GUI 方式（集成在OEM 中的备份管理器）、API 方式（用于集成到第三方的备份软件中）。
具有如下特点：
1）功能类似物理备份，但比物理备份强大N倍；
2）可以压缩空块；
3）可以在块水平上实现增量；
4）可以把备份的输出打包成备份集，也可以按固定大小分割备份集；
5）备份与恢复的过程可以自动管理；
6）可以使用脚本（存在Recovery catalog 中）
7）可以做坏块监测

5:standby的特点
备用数据库（standby database）：ORACLE推出的一种高可用性(HIGH AVAILABLE)数据库方案，
在主节点与备用节点间通过日志同步来保证数据的同步，备用节点作为主节点的备份
可以实现快速切换与灾难性恢复,从920开始，还开始支持物理与逻辑备用服务器。
  9i中的三种数据保护模式分别是：
  1)、MAXIMIZE PROTECTION ：最大数据保护与无数据分歧，LGWR将同时传送到备用节点，
    在主节点事务确认之前，备用节点也必须完全收到日志数据。如果网络不好，引起LGWR不能传送数据，将引起严重的性能问题，导致主节点DOWN机。
  2)、MAXIMIZE AVAILABILITY ：无数据丢失模式，允许数据分歧，允许异步传送。
    正常情况下运行在最大保护模式，在主节点与备用节点的网络断开或连接不正常时，自动切换到最大性能模式，
   主节点的操作还是可以继续的。在网络不好的情况下有较大的性能影响。
  3)、MAXIMIZE PERFORMANCE：这种模式应当可以说是从8i继承过来的备用服务器模式，异步传送，
    无数据同步检查，可能丢失数据，但是能获得主节点的最大性能。9i在配置DATA GUARD的时候默认就是MAXIMIZE PERFORMANCE

6:对于一个要求恢复时间比较短的系统(数据库50G,每天归档5G)，你如何设计备份策略
  rman/每月一号 level 0 每周末/周三 level 1 其它每天level 2 

四：系统管理类

1:对于一个存在系统性能的系统，说出你的诊断处理思路
1 做statspack收集系统相关信息
  了解系统大致情况/确定是否存在参数设置不合适的地方/查看top 5 event/查看top sql等
2 查v$system_event/v$session_event/v$session_wait
   从v$system_event开始,确定需要什么资源(db file sequential read)等
  深入研究v$session_event,确定等待事件涉及的会话
  从v$session_wait确定详细的资源争用情况(p1-p3的值:file_id/block_id/blocks等)
3 通过v$sql/v$sqltext/v$sqlarea表确定disk_reads、(buffer_gets/executions)值较大的SQL

2:列举几种诊断IO、CPU、性能状况的方法
    top/vmstat
  statspack
  sql_trace/tkprof
    查v$system_event/v$session_event/v$session_wait
  查v$sqlarea(disk_reads或buffer_gets/executions较大的SQL)

3:对statspack有何认识
StapSpack是Oracle公司提供的一个收集数据库运行性能指标的软件包，该软件包从8i起，在9i、10g都有显著的增强
该软件包的辅助表（存储相关参数与收集的性能指标的表）由最初的25个增长到43个
收集级别参数由原来的3个（0、5、10）增加到5个（0、5、6、7、10）
通过分析收集的性能指标，数据库管理员可以详细地了解数据库目前的运行情况，对数据库实例、等待事件、SQL等进行优化调整
利用statspack收集的snapshot,可以统计制作数据库的各种性能指标的统计趋势图表。

4:如果系统现在需要在一个很大的表上创建一个索引，你会考虑那些因素，如何做以尽量减小对应用的影响
在系统比较空闲时
nologging选项（如果有dataguard则不可以使用nologging）
大的sort_ared_size或pga_aggregate_target较大

5:对raid1+0 和raid5有何认识
RAID 10(或称RAID 1＋0)与RAID 0＋1不同，它是用硬盘驱动器先组成RAID 1阵列，然后在RAID 1阵列之间再组成RAID 0阵列。
RAID 10模式同RAID 0+1模式一样具有良好的数据传输性能，但却比RAID 0+1具有更高的可靠性。RAID 10阵列的实际容量为M×n/2，
磁盘利用率为50％。RAID 10也需要至少4个硬盘驱动器构成，因而价格昂贵。
  RAID 10的可靠性同RAID 1一样，但由于RAID 10硬盘驱动器之间有数据分割，因而数据传输性能优良。

RAID 5与RAID 3很相似，不同之处在于RAID 5的奇偶校验信息也同数据一样被分割保存到所有的硬盘驱动器，
而不是写入一个指定的硬盘驱动器，从而消除了单个奇偶校验硬盘驱动器的瓶颈问题。RAID 5磁盘阵列的性能比RAID 3有所提高，
但仍然需要至少3块硬盘驱动器。其实际容量为M×(n-1)，磁盘利用率为(n-1)/n 。

五：综合随意类

1:你最擅长的是oracle哪部分?
pl/sql及sql优化

2:喜欢oracle吗？喜欢上论坛吗？或者偏好oracle的哪一部分？
喜欢,sql的优化

3:随意说说你觉得oracle最有意思的部分或者最困难的部分
latch free的处理

4:为何要选择做DBA呢?
兴趣所在

[e-mail] 17/05/2007--
消耗在准备利用Oracle执行计划机制提高查询性能新的SQL语句的时间是Oracle SQL语句执行时间的最重要的组成部分。
但是通过理解Oracle内部产生执行计划的机制，你能够控制Oracle花费在评估连接顺序的时间数量，并且能在大体上提高查询性能。
准备执行SQL语句
当SQL语句进入Oracle的库缓存后，在该语句准备执行之前，将执行下列步骤：
1) 语法检查：检查SQL语句拼写是否正确和词序。

2) 语义分析：核实所有的与数据字典不一致的表和列的名字。

3) 轮廓存储检查：检查数据字典，以确定该SQL语句的轮廓是否已经存在。

4) 生成执行计划：使用基于成本的优化规则和数据字典中的统计表来决定最佳执行计划。

5) 建立二进制代码：基于执行计划，Oracle生成二进制执行代码。

一旦为执行准备好了SQL语句，以后的执行将很快发生，因为Oracle认可同一个SQL语句，并且重用那些语句的执行。然而，对于生成
特殊的SQL语句，或嵌入了文字变量的SQL语句的系统，SQL执行计划的生成时间就很重要了，并且前一个执行计划通常不能够被重用。
对那些连接了很多表的查询，Oracle需要花费大量的时间来检测连接这些表的适当顺序。

评估表的连接顺序

在SQL语句的准备过程中，花费最多的步骤是生成执行计划，特别是处理有多个表连接的查询。当Oracle评估表的连接顺序时，它必须
考虑到表之间所有可能的连接。例如：六个表的之间连接有720（6的阶乘，或6 * 5 * 4 * 3 * 2 * 1 = 720）种可能的连接线路。
当一个查询中含有超过10个表的连接时，排列的问题将变得更为显著。对于15个表之间的连接，需要评估的可能查询排列将超过1万亿
（准确的数字是1,307,674,368,000）种。

使用optimizer_search_limit参数来设定限制

通过使用optimizer_search_limit参数，你能够指定被优化器用来评估的最大的连接组合数量。使用这个参数，我们将能够防止优化器
消耗不定数量的时间来评估所有可能的连接组合。如果在查询中表的数目小于optimizer_search_limit的值，优化器将检查所有可能的
连接组合。

例如：有五个表连接的查询将有120（5! = 5 * 4 * 3 * 2 * 1 = 120）种可能的连接组合，因此如果optimizer_search_limit等于5
（默认值），则优化器将评估所有的120种可能。optimizer_search_limit参数也控制着调用带星号的连接提示的阀值。当查询中的表的
数目比optimizer_search_limit小时，带星号的提示将被优先考虑。

另一个工具：参数optimizer_max_permutations

初始化参数optimizer_max_permutations定义了优化器所考虑组合数目的上限，且依赖于初始参数optimizer_search_limit。
optimizer_max_permutations的默认值是80,000。

参数optimizer_search_limit和optimizer_max_permutations一起来确定优化器所考虑的组合数目的上限：除非（表或组合数目）
超过参数optimizer_search_limit 或者 optimizer_max_permutations设定的值，否则优化器将生成所有可能的连接组合。一旦优
化器停止评估表的连接组合，它将选择成本最低的组合。

使用ordered提示指定连接顺序

你能够设定优化器所执行的评估数目的上限。但是即使采用有很高价值的排列评估，我们仍然拥有使优化器可以尽早地放弃复杂的查询
的重要机会。回想一下含有15个连接查询的例子，它将有超过1万亿种的连接组合。如果优化器在评估了80,000个组合后停止，那么它才
仅仅评估了0.000006%的可能组合，而且或许还没有为这个巨大的查询找到最佳的连接顺序。

在Oracle SQL中解决此问题的最好的方法是手工指定表的连接顺序。为了尽快创建最小的解决方案集，这里所遵循的规则是将表结合起
来，通常优先使用限制最严格的WHERE子句来连接表。

下面的代码是一个查询执行计划的例子，该例子在emp表的关联查询上强制执行了嵌套的循环连接。注意，我已经使用了ordered提示来
直接最优化表的评

______________________________________________________________________________________________________________________-