SQL语句的执行步骤

SQL语句的执行步骤
一条SQL语句的处理过程要经过以下几个步骤。
1 语法分析 分析语句的语法是否符合规范，衡量语句中各表达式的意义。
2 语义分析 检查语句中涉及的所有数据库对象是否存在，且用户有相应的权限。
3 视图转换 将涉及视图的查询语句转换为相应的对基表查询语句。
4 表达式转换 将复杂的SQL表达式转换为较简单的等效连接表达式。
5 选择优化器 不同的优化器一般产生不同的“执行计划”
6 选择连接方式 Oracle有三种连接方式，对多表连接Oracle可选择适当的连接方式。
7 选择连接顺序 对多表连接Oracle选择哪一对表先连接，选择这两表中哪个表做为源数据表。
8 选择数据的搜索路径 根据以上条件选择合适的数据搜索路径，如是选用全表搜索还是利用索引或是其他的方式。
9 运行“执行计划”
　　Oracle的优化器

　　Oracle有两种优化器：基于规则的优化器（RBO， Rule Based Optimizer），和基于代价的优化器（CBO， Cost Based Optimizer）。
　　RBO自Oracle 6版以来被采用，有着一套严格的使用规则，只要你按照它去写SQL语句，无论数据表中的内容怎样，也不会影响到你的“执行计划”，也就是说对数据不“敏感

”，Oracle公司已经不再发展这种技术了。
　　CBO自Oracle 7版被引入，Oracle自7版以来采用的许多新技术都是基于CBO的，如星型连接排列查询，哈希连接查询，和并行查询等。CBO计算各种可能“执行计划”的“代价

”，即cost，从中选用cost最低的方案，作为实际运行方案。各“执行计划”的cost的计算根据，依赖于数据表中数据的统计分布，Oracle数据库本身对该统计分布并不清楚，须

要分析表和相关的索引，才能搜集到CBO所需的数据。
　　一般而言，CBO所选择的“执行计划”都不会比RBO的“执行计划”差，而且相对而言，CBO对程序员的要求没有RBO那么苛刻，节省了程序员为了从多个可能的“执行计划”中

选择一个最优的方案而花费的调试时间，但在某些场合下也会存在问题。2007-10-31 11:14 AM vunhbb
较典型的问题有：有时，表明明建有索引，但查询过程显然没有用到相关的索引，导致查询过程耗时漫长，占用资源巨大，问题到底出在哪儿呢？按照以下顺序查找，基本上能发

现原因所在。
　　查找原因的步骤
　　首先，我们要确定数据库运行在何种优化模式下，相应的参数是：optimizer_mode。可在svrmgrl中运行“show parameter optimizer_mode"来查看。Oracle V7以来缺省的设

置应是"choose"，即如果对已分析的表查询的话选择CBO，否则选择RBO。如果该参数设为“rule”，则不论表是否分析过，一概选用RBO，除非在语句中用hint强制。
　　其次，检查被引的列或组合索引的首列是否出现在PL/SQL语句的WHERE子句中，这是“执行计划”能用到相关索引的必要条件。
　　第三，看采用了哪种类型的连接方式。Oracle的共有Sort Merge Join（SMJ）、Hash Join（HJ）和Nested Loop Join（NL）。在两张表连接，且内表的目标列上建有索引时，

只有Nested Loop才能有效地利用到该索引。SMJ即使相关列上建有索引，最多只能因索引的存在，避免数据排序过程。HJ由于须做HASH运算，索引的存在对数据查询速度几乎没有

影响。
第四，看连接顺序是否允许使用相关索引。假设表emp的deptno列上有索引，表dept的列deptno上无索引，Where语句有emp.deptno=dept.deptno条件。在做NL连接时，emp做为

外表，先被访问，由于连接机制原因，外表的数据访问方式是全表扫描，emp.deptno上的索引显然是用不上，最多在其上做索引全扫描或索引快速全扫描。　　
第五，是否用到系

统数据字典表或视图。由于系统数据字典表都未被分析过，可能导致极差的“执行计划”。但是不要擅自对数据字典表做分析，否则可能导致死锁，或系统性能下降。

　　第六，索引列是否函数的参数。如是，索引在查询时用不上。
　　第七，是否存在潜在的数据类型转换。如将字符型数据与数值型数据比较，Oracle会自动将字符型用to_number()函数进行转换，从而导致第六种现象的发生。

　　第八，是否为表和相关的索引搜集足够的统计数据。对数据经常有增、删、改的表最好定期对表和索引进行分析，可用SQL语句:
analyze table xxxx compute statistics for all indexes;
Oracle掌握了充分反映实际的统计数据，才有可能做出正确的选择
第九，索引列的选择性不高。
　　我们假设典型情况，有表emp，共有一百万行数据，但其中的emp.deptno列，数据只有4种不同的值，如10、20、30、40。虽然emp数据行有很多，Oracle缺省认定表中列的值是

在所有数据行均匀分布的，也就是说每种deptno值各有25万数据行与之对应。假设SQL搜索条件DEPTNO=10，利用deptno列上的索引进行数据搜索效率，往往不比全表扫描的高，

ORACLE理所当然对索引“视而不见”，认为该索引的选择性不高。

　　但我们考虑另一种情况，如果一百万数据行实际不是在4种deptno值间平均分配，其中有99万行对应着值10，5000行对应值20，3000行对应值30，2000行对应值40。在这种数据

分布图案中对除值为10外的其它deptno值搜索时，毫无疑问，如果索引能被应用，那么效率会高出很多。我们可以采用对该索引列进行单独分析，或用analyze语句对该列建立直方

图，对该列搜集足够的统计数据，使Oracle在搜索选择性较高的值能用上索引。

　　第十，索引列值是否可为空（NULL）。如果索引列值可以是空值，在SQL语句中那些需要返回NULL值的操作，将不会用到索引，如COUNT（*），而是用全表扫描。这是因为索引

中存储值不能为全空。

　　第十一，看是否有用到并行查询（PQO）。并行查询将不会用到索引。
　　第十二，看PL/SQL语句中是否有用到bind变量。由于数据库不知道bind变量具体是什么值，在做非相等连接时，如“”,“like”等。Oracle将引用缺省值，在某些情

况下会对执行计划造成影响。
2007-10-31 11:15 AM vunhbb
如果从以上几个方面都查不出原因的话，我们只好用采用在语句中加hint的方式强制Oracle使用最优的“执行计划”。

　　hint采用注释的方式，有行注释和段注释两种方式。
　　如我们想要用到A表的IND_COL1索引的话，可采用以下方式：

“SELECT /*+ INDEX（A IND_COL1）*/ * FROM A WHERE COL1 = XXX; "
　　注意，注释符必须跟在SELECT之后，且注释中的“+”要紧跟着注释起始符“/*”或“--”，否则hint就被认为是一般注释，对PL/SQL语句的执行不产生任何影响。
2007-11-7 03:43 PM wcvvmd
是这些原因啊 了解了
页: [1]
查看完整版本: 为什么Oracle有时会用索引来查找数据？

1、如何知道数据库目前正在执行的事务有多少，以当前时间点来说

2、如何知道数据库目前已结束，未提交的事务有多少，以当前时间点来说

3、请列举出发生了锁的争用，但是系统不会HANG住，以报错退出的例子（请列举不少于三个）

4、请说明create index on 表，在系统繁忙的时候为什么会产生锁，产生什么锁？ online 的关键字为什么能避免，能简要的说出原因吗？

5、update 无条件全表， select count(*) 无条件全表,请考虑是否有优化的余地

6、请简要的说明EXP/IMP中的参数含义和使用注意事项，请随意写出5个参数做解释QUOTE:
--------------------------------------------------------------------------------
原帖由 棉花糖ONE 于 2007-12-18 09:44 发表
1、如何知道数据库目前正在执行的事务有多少，以当前时间点来说

v$transaction (addr),v$session (taddr) ,查看session 的status为active的正在执行

2.如何知道数据库目前已结束，未提交的事务有多少，以当前时间点来说

v$transaction (addr),v$session (taddr) ,查看session 的status为inactive的

3、请列举出发生了锁的争用，但是系统不会HANG住，以报错退出的例子（请列举不少于三个）

a. ddl+ddl
b.ddl(非online)+dml
c. update全表+select * from table for update nowait;

4.请说明create index on 表，在系统繁忙的时候为什么会产生锁，产生什么锁？ online 的关键字为什么能避免，能简要的说出原因吗？

不加online,tm锁的mode为4,加了online mode为2 ,正常的dml在表上加的mode为3,mode为4的tm锁阻塞mode为3的tm琐,mode为2的tm锁不阻塞mode为3的tm琐

5.update全表,使用分批提交,select count(*) 在非空字段上加索引 ,走index fast full scan

6.owner 导一个用户的数据
commit=y 分buffer提交
consistent=y保证事务级别的一致性
transport_tablespace设置使用表空间传输
buffer 设置缓存区大小

[Blocked Ads]
快速查看HP-UX磁盘信息

#iosacn -funCdisk

#ioscan -funCtape

#ioscan -funCprinter

1、 cd命令的使用
返回上一次的目录（cd -）
2、 Esc键的使用
在命令行中可通过击两次Esc键来匹配当前目录中唯一的文件名，这样可节约击入长文件名的时间
3、 col命令的使用
man cp | col –b >cp.txt
该命令可将cp命令的联机帮助手册输出为名为cp.txt的文本文件
4、启动机器时执行的工作脚本（/etc/rc.config.d/）
5、which命令的使用（查看某命令的绝对路径）
which 命令名
6、显示当前系统补丁安装情况（show_patches）
7、中文环境的配置
a、 查看系统中安装的语言集（locale –a）
b、 显示当前语言环境变量（echo $LANG）
c、 如不为中文则修改（export LANG=zh_CN.hp15CN）
d、 sty –istrip
8、查看系统信息和运行情况
a、 glance
b、 top
c、 performance view
d、 /opt/ignite/bin/print_manifest
/var/opt/ignite/local/manifest/manifest
e、 /var/adm/syslog/syslog（系统运行日志）
f、 /var/adm/shutdownlog
g、 /var/adm/crash/
h、 /var/opt/sna/sna.err
i、 /var/adm/nettl.LOG000
/usr/sbin/netfmt /var/adm/nettl.LOG000 >net.log
j、/usr/contrib./bin/info_col.11
k、/var/adm/btmp（历史登录信息，可用last命令查看）
l、/var/adm/wtmp（比btmp更详细的信息）
m、/etc/rc.log（机器启动时的信息）
n、/var/adm/sw/（有关软件包安装、删除的信息）
9、查看系统当前init级别（who –r）
10、显示系统不成功登录的历史纪录（lastb –R）
11、ftp问题（如无法用某一用户进行ftp）
a、 将/etc/passwd中该用户的sh路径改为/usr/bin/ksh
b、 在/etc/shells文件中将ksh的路径加入
12、在HP11i环境下两台主机rlogin、rcp和ftp速率小于10mbps
a、 安装补丁（PHKL_25233、25389，PHNE_26728、27063）
13、在HP11i环境下主机与路由器之间无法用大包进行ping命令
a、 在主机的/etc/rc.config.d/nddconf文件中增加ip_send_source_quench参数的配置
b、 ndd –c
14、网络配置（含网卡地址、激活状态、路由地址）可通过修改/etc/rc.config.d/netconf文件来实现。
15、swapinfo命令的使用
swapinfo –mt
16、what命令的使用（假如要查看某一core文件的诱因）
what core
17、控制某一特定地址通过telnet、ftp和login协议访问主机的方法
/var/adm/inetd.sec
18、tsm的使用
在tsm状态下可通过ctrl+w来切换屏幕。
19、finger命令的使用
finger –R（查看当前登录到主机的客户机信息）
20、parstaus命令的使用
parstatus –p 查看某一特定分区的信息。
　

扩充文件系统的方法

一、 vg00（例如将/home扩充至2G，逻辑卷名为/dev/vg00/lvol8）
1、 shutdown –y 0 进入单用户模式
2、 umount /home
3、 lvextend –L 2000 /dev/vg00/lvol8
4、 extendfs /dev/vg00/rlvol8
5、 mount /dev/vg00/lvol8 /home
6、 reboot
二、 vg不在MC的情况
除不要启停机器外同上
三、 vg在MC中配置的情况
1、 有应用运行在该逻辑卷上，且不便停应用的情况（例如将/data扩充至6G，逻辑卷名为/dev/vgdata/lvdata，MC环境为node1和node2，应用包名为pkg，缺省运行在node2上）
a、 cmhaltpkg –v pkg
b、 vgchange –c n /dev/vgdata
c、 vgchange –a y /dev/vgdata
d、 lvextend –L 6000 /dev/vgdata/lvdata（扩大到6G）
e、 extendfs /dev/vgdata/rlvdata（如为裸设备，e-n可不做。）
f、 vgexport –p –v –s –m /tmp/vgmap/vgdata.map /dev/vgdata
g、 rcp /tmp/vgmap/vgdata.map node1: /tmp/vgmap/vgdata.map
h、 rlogin node1
i、 ll /dev/vgdata（查看node1上vgdata的设备编号，假设为0x0c0000）
j、 vgexport /dev/vgdata
k、 mkdir /dev/vgdata
l、 mknod /dev/vgdata/group c 64 0x0c0000
m、 vgimport –s –v –m /tmp/vgmap/vgdata.map /dev/vgdata
n、 exit
o、 vgchange –a n /dev/vgdata
p、 vgchange –c y /dev/vgdata
q、 cmmodpkg –e pkg
2、 无应用运行在该逻辑卷上，或可暂停应用的情况（例如将/data扩充至6G，逻辑卷名为/dev/vgdata/lvdata，MC环境为node1和node2，应用包名为pkg，缺省运行在node2上）
1、将前一种情况中的a-c改为（将涉及到该逻辑卷的应用停止，该工作应选择合适时机执行）：
a、将涉及到该逻辑卷的应用停止。
b、umount /data
2、执行前一种情况中的d-n
3、 将前一种情况中的o-q改为
a、 mount /data
b、 激活涉及到该逻辑卷的应用

注：如未在VG中新增PV则可将上述步骤中的f-n略去。

一。
Unix安装后創建文件系統前的準備
*注：因为我的机器只有一个磁盘，所以，此步中除创建逻辑卷外大部分操作可以省略。
①建物理卷。
找出与磁盘相关的设备文件：ioscan(命令)：ioscan –funC disk。
找到disk对应的设备文件名：c1t15d0
因只有一个磁盘，所以只能创建一个物理卷。
在创建前，通过mediainit对磁盘格式化合校验完整性：
#mediainit /dev/rdsk/c1t15d0
l创建物理卷：
#pvcreate /dev/rdsk/c1t15d0
注：如果此时该磁盘以输入某卷组的一部分，而且确信要创建物理卷。在命令行加“-f”来强制执行。
②创建卷组。(仅供理解用，本系统不需要)
1.用卷组名在/dev下创建一目录。
如果卷组名为vg01,则目录为：/dev/vg01。
#mkdir /dev/vg01.
在此目录下为该卷组创建一组设备文件。
#mknod /dev/vg01/group c 64 0x010000
创建卷组（以物理卷为参数）：
#vgcreate /dev/vg01 /dev/dsk/c1t15d0.
建完后，可以用命令：vgdisplay来检查设置是否正确。
③创建逻辑卷。
创建逻辑卷时，相应的字符设备和块设备会在卷组目录下生成。
现在，我们在默认卷组vg00中创建以下逻辑卷：
一个256MB,名为lvoltuxlog的逻辑卷。
#lvcreate -L 256 -n lvoltuxlog。
参数：-L 以MB为单位表示逻辑卷的大小。
-n 指定逻辑卷名称，缺省为：lvol1,lvol2…..。
二、創建文件系統。
注：系统安装完成后，会自动创建一些文件系统和目录。所以，对一般权限的用户来讲，可以在/home目录下安装软件。但是，如果软件过多，会造成比较混乱和不利于管理。最合

适的做法，高权限的用户自己可以建立一些文件系统，这些文件系统在单独的目录下。这样对将来（逻辑卷）的扩展和维护非常有利。
（1）文件系统介绍：
1。高性能文件系统（HFS），现在应用的不多了，只在存储HP-UX内核时使用。
2。日志文件系统（JFS），现在应用最多。
3。网络文件系统（NFS）。
4。 CD-ROM文件系统（CDFS）用于CD-ROM上存储文件。
（2）创建新的文件系统：
文件系统必须建在逻辑卷上。
1．newfs创建文件系统：
我们在 /dev/vg00/lvoldb01上创建JFS文件系统，如下命令：
#newfs –F vxfs /dev/vg00/ lvoltuxlog。
2．挂载文件系统。
mount实现挂载：
把/dev/vg00/ lvoltuxlog挂载到/tuxedo/LOG目录上。（事先创建此目录，具体见下面的*注解1）
#mount -F vxfs /dev/vg00/ lvoltuxlog /tuxedo/LOG。
现在可用bdf显示所有挂起的卷。
*注解1：在根目录下建立目录/tuxedo/LOG：#mkdir tuxedo/LOG。 （3）扩展卷组：（如果将来磁盘空间不够，可以对卷组扩展）
添加一块磁盘，用pvcreate创建一物理卷，然后，将其用vgextend加入卷组中。
如：把磁盘c0t3d0加入卷组vg00中：
#pvcreate /dev/rdsk/c0t3d0
#vgextend vg00 /dev/dsk/c0t3d0
用#vgdisplay -v vg00 看是否扩展成功。
（4）扩展逻辑卷。
扩展了卷组以后，可以通过lvextend命令扩展逻辑卷尺寸。
如把/dev/vg00/ lvoltuxlog扩展到800M。（以前为256M）。
#lvextend -L 800 /dev/vg00/ lvoltuxlog
要在某特定磁盘上扩展逻辑卷，可把磁盘设备文件作为参数添加到lvextend命令的后面。
#lvextend -L 800 /dev/vg00/ lvoltuxlog /dev/dsk/c0t3d0
(5)扩展文件系统。
扩展了逻辑空间不能自动扩展文件系统空间。在/dev/vg00/ lvoltuxlog下扩展JFS文件系统。
1．先卸载#umount /tuxedo/LOG。
2．#Extendsf -F vxfs /dev/vg00/ lvoltuxlog。
3．挂载#mount -F vxfs /dev/vg00/ lvoltuxlog /tuxedo/LOG。
完成上述操作后，可通过bdf命令查看是否分配成功。

令的目的 MS-DOS Linux Linux 的简单实例
复制文件 copy cp cp thisfile.txt /home/thisdirectory
转移文件 move mv mv thisfile.txt /home/thisdirectory
列举文件 dir ls ls
清除屏幕 cls clear clear
关闭 shell 提示 exit exit exit
显示或设置日期 date date date
删除文件 del rm rm thisfile.txt
把输出“回响”到屏幕上 echo echo echo this message
用简单文本编辑器来编辑文件 edit gedit([a]) gedit thisfile.txt
比较文件内容 fc diff diff file1 file2
在文件中寻找字串 find grep grep 词或词组 thisfile.txt
格式化软盘 format a:（如果软盘是在 A: 中的话） mke2fs 或 mformat（） /sbin/mke2fs /dev/fd0（/dev/fd0 是 Linux 中与 A: 等同的驱动器）
显示命令帮助 命令 /? man（[c]） man 命令
创建目录 mkdir mkdir mkdir 目录
查看文件 more less（[d]） less thisfile.txt
重新命名文件 ren mv（[e]） mv thisfile.txt thatfile.txt
显示你在文件系统中的位置 chdir pwd pwd
用指定的路径（绝对路径，absolute path）来改换目录 cd 路径名 cd 路径名 cd /directory/directory
用一个相对路径（relative path）来改换目录 cd .. cd .. cd ..
显示时间 time date date
显示已被使用的内存数量 mem free free
注:
a. Gedit 是图形化文本编辑器；你可以使用其它文本编辑器来代替 Gedit，包括 Emacs 和 vi。
b. 这个命令为 DOS 文件系统格式化软盘。
c. 某些命令你还 可以使用 info。
d. more 分页器也可以用来一个屏幕一个屏幕地在文件中逐页查看。
e. mv 命令可以转移文件或重新命名在同一目录下的文件。如果你想重新命名文件，如例中所示，把这个文件“转移”到同一目录中的新名称。

阿里巴巴公司DBA笔试题

注:以下题目，可根据自己情况挑选题目作答，不必全部作答.您也可以就相关问题直接找负责面试人员面述而不笔答

一:SQL tuning 类

　　1:列举几种表连接方式

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

　　3:如何使用CBO,CBO与RULE的区别

　　4:如何定位重要(消耗资源多)的SQL

　　5:如何跟踪某个session的SQL

　　6:SQL调整最关注的是什么

　　7:说说你对索引的认识(索引的结构、对dml影响、对查询影响、为什么提高查询性能)

　　8:使用索引查询一定能提高查询的性能吗?为什么

　　9:绑定变量是什么?绑定变量有什么优缺点?

　　10:如何稳定(固定)执行计划

　　11:和排序相关的内存在8i和9i分别怎样调整，临时表空间的作用是什么

　　12:存在表T(a,b,c,d),要根据字段c排序后取第21—30条记录显示，请给出sql

二:数据库基本概念类

　　1ctused and pctfree 表示什么含义有什么作用

　　2:简单描述table / segment / extent / block之间的关系

　　3:描述tablespace和datafile之间的关系

　　4:本地管理表空间和字典管理表空间的特点，ASSM有什么特点

　　5:回滚段的作用是什么

　　6:日志的作用是什么

　　7:SGA主要有那些部分，主要作用是什么

　　8racle系统进程主要有哪些，作用是什么

三:备份恢复类

　　1:备份如何分类

　　2:归档是什么含义

　　3:如果一个表在2004-08-04 10:30:00 被drop，在有完善的归档和备份的情况下，如何恢复

　　4:rman是什么，有何特点

　　5:standby的特点

　　6:对于一个要求恢复时间比较短的系统(数据库50G,每天归档5G)，你如何设计备份策略

四:系统管理类

　　1:对于一个存在系统性能的系统，说出你的诊断处理思路

　　2:列举几种诊断IO、CPU、性能状况的方法

　　3:对statspack有何认识

　　4:如果系统现在需要在一个很大的表上创建一个索引，你会考虑那些因素，如何做以尽量减小对应用的影响

　　5:对raid10 和raid5有何认识

五:综合随意类

　　1:你最擅长的是oracle哪部分?

　　2:喜欢oracle吗?喜欢上论坛吗?或者偏好oracle的哪一部分?

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

　　4:为何要选择做DBA呢?
oldhero 发表于 2006-8-30 14:10:00
一：SQL tuning 类

1:列举几种表连接方式

hash join/merge join/nest loop(cluster join)/index join

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

set autot on

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.**.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性能较差

二：数据库基本概念类

1pctused and pctfree 表示什么含义有什么作用

pctused与pctfree控制数据块是否出现在freelist中,

pctfree控制数据块中保留用于update的空间,当数据块中的free space小于pctfree设置的空间时,

该数据块从freelist中去掉,当块由于dml操作free space大于pct_used设置的空间时,该数据库块将

被添加在freelist链表中。

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.**.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呢?

兴趣所在

Oracle新手最经常碰到的6个错误及解决方案(监听器的错误)

Oracle新手最经常碰到的6个错误及解决方案

[以8.1.6为例]：

1、ORA－12541：TNS：没有监听器

　　原因：没有启动监听器或者监听器损坏。如果是前者，使用命令net start OracleOraHome81TNSListener（名字可能有出入）即可；如果是后者，则使用“Net8

Configuration Assistant”工具向导之“监听程序配置”增加一个监听器即可（基本不用写任何信息，一路OK。在添加之前可能需要把所有的监听器先删除！）

2、ORA－12500：TNS：监听程序无法启动专用服务器进程

　　或

　 ORA－12560：TNS：协议适配器错误

　　原因：ORACLE的数据库服务没有启动。使用命令net start ORACLESERVICEORADB（ORADB为数据库名字）即可。如果仍没有解决，请继续向下看。

3、如果数据库服务启动失败，则很有可能是其注册表项值损坏，最好的做法是以下两步：

　　1）ORADIM -DELETE -SID oradb 删除数据库服务项

　　2）ORADIM -NEW -SID oradb 新增数据库服务项

　　注：这个过程中如果出错，就重启计算机！

4、ORA－12154：TNS：能解析服务名

　　原因：ORACLE的网络服务名没有正确配置。请使用“Net8 Configuration Assistant”工具向导之“本地网络服务名配置”配置TNS即可。如果仍没有解决，请继续向下看。

5、ORA－1034 ：TNS：ORACLE不可用

　　原因：ORACLE的数据库服务正确启动，但是数据库没有打开！

　　使用命令：

　　1）svrmgrl 启动服务管理器

　　2）connect internal 以internal身份登陆

　　3）startup 打开数据库

6、ORA－12560：TNS：协议适配器错误（顽固性的）

　　原因：未知。

　　解决：必杀技－－打开“Windows任务管理器”，杀死ORACLE.exe及ORADIM.exe进程，书写自己的ora_startup.bat，执行之！

PS：

1、我的ora_startup.bat：

net start OracleOraHome81TNSListener
net start ORACLESERVICEORADB
svrmgrl 一般情况下不用，不过有时少不了它的，具体步骤见第5步。

2、我的ora_shutdown.bat：

net stop OracleOraHome81TNSListener

net stop ORACLESERVICEORADB

3、ORACLE相关服务名请参见“管理工具”之“服务”中以ORACLE开头的服务名

Sql server进行优化50招特效
来源： 作者： 发布时间：2007-01-27

查询速度慢的原因很多，常见如下几种：

商业智能
1、没有索引或者没有用到索引(这是查询慢最常见的问题，是程序设计的缺陷) 商业智能
2、I/O吞吐量小，形成了瓶颈效应。 本文转载自数据挖掘研究院
3、没有创建计算列导致查询不优化。

数据仓库
4、内存不足

5、网络速度慢 数据挖掘

6、查询出的数据量过大（可以采用多次查询，其他的方法降低数据量） 商业智能

7、锁或者死锁(这也是查询慢最常见的问题，是程序设计的缺陷)

数据仓库

8、sp_lock,sp_who,活动的用户查看,原因是读写竞争资源。

9、返回了不必要的行和列 数据挖掘

10、查询语句不好，没有优化
可以通过如下方法来优化查询 : 数据挖掘

1、把数据、日志、索引放到不同的I/O设备上，增加读取速度，以前可以将Tempdb应放在RAID0上，SQL2000不在支持。数据量（尺寸）越大，提高I/O越重要.

2、纵向、横向分割表，减少表的尺寸(sp_spaceuse)

HAMMER_SHI

3、升级硬件

本文转载自数据挖掘研究院

4、根据查询条件,建立索引,优化索引、优化访问方式，限制结果集的数据量。注意填充因子要适当（最好是使用默认值0）。索引应该尽量小，使用字节数小的列建索引好（参照

索引的创建）,不要对有限的几个值的字段建单一索引如性别字段

数据挖掘

5、提高网速; 数据挖掘

6、扩大服务器的内存,Windows 2000和SQL server 2000能支持4-8G的内存。配置虚拟内存：虚拟内存大小应基于计算机上并发运行的服务进行配置。运行 Microsoft SQL Server?

2000 时，可考虑将虚拟内存大小设置为计算机中安装的物理内存的 1.5 倍。如果另外安装了全文检索功能，并打算运行 Microsoft 搜索服务以便执行全文索引和查询，可考虑：

将虚拟内存大小配置为至少是计算机中安装的物理内存的 3 倍。将 SQL Server max server memory 服务器配置选项配置为物理内存的 1.5 倍（虚拟内存大小设置的一半）。

HAMMER_SHI

7、增加服务器CPU个数;但是必须明白并行处理串行处理更需要资源例如内存。使用并行还是串行程是MsSQL自动评估选择的。单个任务分解成多个任务，就可以在处理器上运行。

例如耽搁查询的排序、连接、扫描和GROUP BY字句同时执行，SQL SERVER根据系统的负载情况决定最优的并行等级，复杂的需要消耗大量的CPU的查询最适合并行处理。但是更新操

作UPDATE,INSERT，DELETE还不能并行处理。

商业智能

8、如果是使用like进行查询的话，简单的使用index是不行的，但是全文索引，耗空间。 like "a%" 使用索引 like "%a" 不使用索引用 like "%a%" 查询时，查询耗时和字段值

总长度成正比,所以不能用CHAR类型，而是VARCHAR。对于字段的值很长的建全文索引。

数据仓库

9、DB Server 和APPLication Server 分离；OLTP和OLAP分离 本文转载自数据挖掘研究院

10、分布式分区视图可用于实现数据库服务器联合体。联合体是一组分开管理的服务器，但它们相互协作分担系统的处理负荷。这种通过分区数据形成数据库服务器联合体的机制

能够扩大一组服务器，以支持大型的多层 Web 站点的处理需要。有关更多信息，参见设计联合数据库服务器。（参照SQL帮助文件"分区视图"）

本文转载自数据挖掘研究院

a、在实现分区视图之前，必须先水平分区表 搜索引擎

b、在创建成员表后，在每个成员服务器上定义一个分布式分区视图，并且每个视图具有相同的名称。这样，引用分布式分区视图名的查询可以在任何一个成员服务器上运行。系统

操作如同每个成员服务器上都有一个原始表的复本一样，但其实每个服务器上只有一个成员表和一个分布式分区视图。数据的位置对应用程序是透明的。

HAMMER_SHI

11、重建索引 DBCC REINDEX ,DBCC INDEXDEFRAG,收缩数据和日志 DBCC SHRINKDB,DBCC SHRINKFILE. 设置自动收缩日志.对于大的数据库不要设置数据库自动增长，它会降低服务

器的性能。 在T-sql的写法上有很大的讲究，下面列出常见的要点：首先，DBMS处理查询计划的过程是这样的： HAMMER_SHI

1、 查询语句的词法、语法检查 本文转载自数据挖掘研究院

2、 将语句提交给DBMS的查询优化器

商业智能

3、 优化器做代数优化和存取路径的优化 本文转载自数据挖掘研究院

4、 由预编译模块生成查询规划

商业智能

5、 然后在合适的时间提交给系统处理执行

搜索引擎

6、 最后将执行结果返回给用户其次，看一下SQL SERVER的数据存放的结构：一个页面的大小为8K(8060)字节，8个页面为一个盘区，按照B树存放。 数据挖掘

12、Commit和rollback的区别 Rollback:回滚所有的事物。 Commit:提交当前的事物. 没有必要在动态SQL里写事物，如果要写请写在外面如： begin tran exec(@s) commit

trans 或者将动态SQL 写成函数或者存储过程。

数据仓库

13、在查询Select语句中用Where字句限制返回的行数,避免表扫描,如果返回不必要的数据，浪费了服务器的I/O资源，加重了网络的负担降低性能。如果表很大，在表扫描的期间

将表锁住，禁止其他的联接访问表,后果严重。

数据挖掘

14、SQL的注释申明对执行没有任何影响
15、尽可能不使用光标，它占用大量的资源。如果需要row-by-row地执行，尽量采用非光标技术,如：在客户端循环，用临时表，Table变量，用子查询，用Case语句等等。游标可

以按照它所支持的提取选项进行分类： 只进 必须按照从第一行到最后一行的顺序提取行。FETCH NEXT 是唯一允许的提取操作,也是默认方式。可滚动性 可以在游标中任何地方随

机提取任意行。游标的技术在SQL2000下变得功能很强大，他的目的是支持循环。有四个并发选项 READ_ONLY：不允许通过游标定位更新(Update)，且在组成结果集的行中没有锁。

OPTIMISTIC WITH valueS:乐观并发控制是事务控制理论的一个标准部分。乐观并发控制用于这样的情形，即在打开游标及更新行的间隔中，只有很小的机会让第二个用户更新某一

行。当某个游标以此选项打开时，没有锁控制其中的行，这将有助于最大化其处理能力。如果用户试图修改某一行，则此行的当前值会与最后一次提取此行时获取的值进行比较。

如果任何值发生改变，则服务器就会知道其他人已更新了此行，并会返回一个错误。如果值是一样的，服务器就执行修改。 选择这个并发选项?OPTIMISTIC WITH ROW

VERSIONING:此乐观并发控制选项基于行版本控制。使用行版本控制，其中的表必须具有某种版本标识符，服务器可用它来确定该行在读入游标后是否有所更改。在 SQL Server 中

，这个性能由 timestamp 数据类型提供，它是一个二进制数字，表示数据库中更改的相对顺序。每个数据库都有一个全局当前时间戳值：@@DBTS。每次以任何方式更改带有

timestamp 列的行时，SQL Server 先在时间戳列中存储当前的 @@DBTS 值，然后增加 @@DBTS 的值。如果某 个表具有 timestamp 列，则时间戳会被记到行级。服务器就可以比较

某行的当前时间戳值和上次提取时所存储的时间戳值，从而确定该行是否已更新。服务器不必比较所有列的值，只需比较 timestamp 列即可。如果应用程序对没有 timestamp 列

的表要求基于行版本控制的乐观并发，则游标默认为基于数值的乐观并发控制。 SCROLL LOCKS 这个选项实现悲观并发控制。在悲观并发控制中，在把数据库的行读入游标结果集

时，应用程序将试图锁定数据库行。在使用服务器游标时，将行读入游标时会在其上放置一个更新锁。如果在事务内打开游标，则该事务更新锁将一直保持到事务被提交或回滚；

当提取下一行时，将除去游标锁。如果在事务外打开游标，则提取下一行时，锁就被丢弃。因此，每当用户需要完全的悲观并发控制时，游标都应在事务内打开。更新锁将阻止任

何其它任务获取更新锁或排它锁，从而阻止其它任务更新该行。然而，更新锁并不阻止共享锁，所以它不会阻止其它任务读取行，除非第二个任务也在要求带更新锁的读取。滚动

锁根据在游标定义的 SELECT 语句中指定的锁提示，这些游标并发选项可以生成滚动锁。滚动锁在提取时在每行上获取，并保持到下次提取或者游标关闭，以先发生者为准。下次

提取时，服务器为新提取中的行获取滚动锁，并释放上次提取中行的滚动锁。滚动锁独立于事务锁，并可以保持到一个提交或回滚操作之后。如果提交时关闭游标的选项为关，则

COMMIT 语句并不关闭任何打开的游标，而且滚动锁被保留到提交之后，以维护对所提取数据的隔离。所获取滚动锁的类型取决于游标并发选项和游标 SELECT 语句中的锁提示。锁

提示 只读 乐观数值 乐观行版本控制 锁定无提示 未锁定 未锁定 未锁定 更新 NOLOCK 未锁定 未锁定 未锁定 未锁定 HOLDLOCK 共享 共享 共享 更新 UPDLOCK 错误 更新 更新

更新 TABLOCKX 错误 未锁定 未锁定 更新其它 未锁定 未锁定 未锁定 更新 *指定 NOLOCK 提示将使指定了该提示的表在游标内是只读的。
16、用Profiler来跟踪查询，得到查询所需的时间，找出SQL的问题所在;用索引优化器优化索引 数据挖掘
17、注意UNion和UNion all 的区别。UNION all好
18、注意使用DISTINCT，在没有必要时不要用，它同UNION一样会使查询变慢。重复的记录在查询里是没有问题的 HAMMER_SHI
19、查询时不要返回不需要的行、列
20、用sp_configure "query governor cost limit"或者SET QUERY_GOVERNOR_COST_LIMIT来限制查询消耗的资源。当评估查询消耗的资源超出限制时，服务器自动取消查询,在查

询之前就扼杀掉。SET LOCKTIME设置锁的时间
21、用select top 100 / 10 Percent 来限制用户返回的行数或者SET ROWCOUNT来限制操作的行 商业智能
22、在SQL2000以前，一般不要用如下的字句: "IS NULL", "<>", "!=", "!>", "!

是表扫描。也不要在WHere字句中的列名加函数，如Convert，substring等,如果必须用函数的时候，创建计算列再创建索引来替代.还可以变通写法：WHERE SUBSTRING

(firstname,1,1) = "m"改为WHERE firstname like "m%"（索引扫描），一定要将函数和列名分开。并且索引不能建得太多和太大。NOT IN会多次扫描表，使用EXISTS、NOT

EXISTS ，IN , LEFT OUTER JOIN 来替代，特别是左连接,而Exists比IN更快，最慢的是NOT操作.如果列的值含有空，以前它的索引不起作用，现在2000的优化器能够处理了。相同

的是IS NULL，"NOT", "NOT EXISTS", "NOT IN"能优化她，而"<>"等还是不能优化，用不到索引。 本文转载自数据挖掘研究院
23、使用Query Analyzer，查看SQL语句的查询计划和评估分析是否是优化的SQL。一般的20%的代码占据了80%的资源，我们优化的重点是这些慢的地方。
HAMMER_SHI
24、如果使用了IN或者OR等时发现查询没有走索引，使用显示申明指定索引： SELECT * FROM PersonMember with (INDEX = IX_Title) WHERE processid IN ("男"，"女")
25、将需要查询的结果预先计算好放在表中，查询的时候再SELECT。这在SQL7.0以前是最重要的手段。例如医院的住院费计算。
26、MIN() 和 MAX()能使用到合适的索引。
27、数据库有一个原则是代码离数据越近越好，所以优先选择Default,依次为Rules,Triggers, Constraint（约束如外健主健CheckUNIQUE……,数据类型的最大长度等等都是约束

）,Procedure.这样不仅维护工作小，编写程序质量高，并且执行的速度快。

28、如果要插入大的二进制值到Image列，使用存储过程，千万不要用内嵌INsert来插入(不知JAVA是否)。因为这样应用程序首先将二进制值转换成字符串（尺寸是它的两倍），服

务器受到字符后又将他转换成二进制值.存储过程就没有这些动作: 方法：

Create procedure p_insert as insert into table(Fimage) values (@image)

在前台调用这个存储过程传入二进制参数，这样处理速度明显改善。

29、Between在某些时候比IN速度更快,Between能够更快地根据索引找到范围。用查询优化器可见到差别。

select * from chineseresume where title in ('男','女') Select * from chineseresume where between

'男' and '女' 是一样的。由于in会在比较多次，所以有时会慢些。

30、在必要是对全局或者局部临时表创建索引，有时能够提高速度，但不是一定会这样，因为索引也耗费大量的资源。他的创建同是实际表一样。

31、不要建没有作用的事物例如产生报表时，浪费资源。只有在必要使用事物时使用它。

32、用OR的字句可以分解成多个查询，并且通过UNION 连接多个查询。他们的速度只同是否使用索引有关,如果查询需要用到联合索引，用UNION all执行的效率更高.多个OR的

字句没有用到索引，改写成UNION的形式再试图与索引匹配。一个关键的问题是否用到索引。

33、尽量少用视图，它的效率低。对视图操作比直接对表操作慢,可以用stored procedure来代替她。特别的是不要用视图嵌套,嵌套视图增加了寻找原始资料的难度。我们看

视图的本质：它是存放在服务器上的被优化好了的已经产生了查询规划的SQL。对单个表检索数据时，不要使用指向多个表的视图，直接从表检索或者仅仅包含这个表的视图上读，

否则增加了不必要的开销,查询受到干扰.为了加快视图的查询，MsSQL增加了视图索引的功能。

34、没有必要时不要用DISTINCT和ORDER BY，这些动作可以改在客户端执行。它们增加了额外的开销。这同UNION 和UNION ALL一样的道理。

select top 20 ad.companyname,comid,position,ad.referenceid,worklocation,
convert(varchar(10),ad.postDate,120) as postDate1,workyear,degreedescription FROM
jobcn_query.dbo.COMPANYAD_query ad where referenceID in('JCNAD00329667','JCNAD132168','JCNAD00337748','JCNAD00338345',
'JCNAD00333138','JCNAD00303570','JCNAD00303569',
'JCNAD00303568','JCNAD00306698','JCNAD00231935','JCNAD00231933',
'JCNAD00254567','JCNAD00254585','JCNAD00254608',
'JCNAD00254607','JCNAD00258524','JCNAD00332133','JCNAD00268618',
'JCNAD00279196','JCNAD00268613') order by postdate desc

35、在IN后面值的列表中，将出现最频繁的值放在最前面，出现得最少的放在最后面，减少判断的次数。

36、当用SELECT INTO时，它会锁住系统表(sysobjects，sysindexes等等)，阻塞其他的连接的存取。创建临时表时用显示申明语句，而不是

select INTO. drop table t_lxh begin tran select * into t_lxh from chineseresume
where name = 'XYZ' --commit

在另一个连接中SELECT * from sysobjects可以看到 SELECT INTO 会锁住系统表，Create table 也会锁系统表(不管是临时表还是系统表)。所以千万不要在事物内使用它！

！！这样的话如果是经常要用的临时表请使用实表，或者临时表变量。

37、一般在GROUP BY 个HAVING字句之前就能剔除多余的行，所以尽量不要用它们来做剔除行的工作。他们的执行顺序应该如下最优：select 的Where字句选择所有合适的行，

Group By用来分组个统计行，Having字句用来剔除多余的分组。这样Group By 个Having的开销小，查询快.对于大的数据行进行分组和Having十分消耗资源。如果Group BY的目的

不包括计算，只是分组，那么用Distinct更快

38、一次更新多条记录比分多次更新每次一条快,就是说批处理好

39、少用临时表，尽量用结果集和Table类性的变量来代替它,Table 类型的变量比临时表好

40、在SQL2000下，计算字段是可以索引的，需要满足的条件如下：

a、计算字段的表达是确定的

b、不能用在TEXT,Ntext，Image数据类型

c、必须配制如下选项 ANSI_NULLS = ON, ANSI_PADDINGS = ON, …….

41、尽量将数据的处理工作放在服务器上，减少网络的开销，如使用存储过程。存储过程是编译好、优化过、并且被组织到一个执行规划里、且存储在数据库中的SQL语句，是

控制流语言的集合，速度当然快。反复执行的动态SQL,可以使用临时存储过程，该过程（临时表）被放在Tempdb中。以前由于SQL SERVER对复杂的数学计算不支持，所以不得不将

这个工作放在其他的层上而增加网络的开销。SQL2000支持UDFs,现在支持复杂的数学计算，函数的返回值不要太大，这样的开销很大。用户自定义函数象光标一样执行的消耗大量

的资源，如果返回大的结果采用存储过程

42、不要在一句话里再三的使用相同的函数，浪费资源,将结果放在变量里再调用更快

43、SELECT COUNT(*)的效率教低，尽量变通他的写法，而EXISTS快.同时请注意区别： select count(Field of null) from Table 和 select count(Field of NOT null)

from Table 的返回值是不同的！！！

44、当服务器的内存够多时，配制线程数量 = 最大连接数+5，这样能发挥最大的效率；否则使用 配制线程数量

最大连接数+5，严重的损害服务器的性能。

45、按照一定的次序来访问你的表。如果你先锁住表A，再锁住表B，那么在所有的存储过程中都要按照这个顺序来锁定它们。如果你（不经意的）某个存储过程中先锁定表B，

再锁定表A，这可能就会导致一个死锁。如果锁定顺序没有被预先详细的设计好，死锁很难被发现

46、通过SQL Server Performance Monitor监视相应硬件的负载 Memory: Page Faults / sec计数器如果该值偶尔走高，表明当时有线程竞争内存。如果持续很高，则内存可

能是瓶颈。

Process:

1、% DPC Time 指在范例间隔期间处理器用在缓延程序调用(DPC)接收和提供服务的百分比。(DPC 正在运行的为比标准间隔优先权低的间隔)。 由于 DPC 是以特权模式执行的

，DPC 时间的百分比为特权时间 百分比的一部分。这些时间单独计算并且不属于间隔计算总数的一部 分。这个总数显示了作为实例时间百分比的平均忙时。

2、%Processor Time计数器　如果该参数值持续超过95%，表明瓶颈是CPU。可以考虑增加一个处理器或换一个更快的处理器。

3、% Privileged Time 指非闲置处理器时间用于特权模式的百分比。(特权模式是为操作系统组件和操纵硬件驱动程序而设计的一种处理模式。它允许直接访问硬件和所有内

存。另一种模式为用户模式，它是一种为应用程序、环境分系统和整数分系统设计的一种有限处理模式。操作系统将应用程序线程转换成特权模式以访问操作系统服务)。 特权时

间的 % 包括为间断和 DPC 提供服务的时间。特权时间比率高可能是由于失败设备产生的大数量的间隔而引起的。这个计数器将平均忙时作为样本时间的一部分显示。

4、% User Time表示耗费CPU的数据库操作，如排序，执行aggregate functions等。如果该值很高，可考虑增加索引，尽量使用简单的表联接，水平分割大表格等方法来降低

该值。 Physical Disk: Curretn Disk Queue Length计数器该值应不超过磁盘数的1.5~2倍。要提高性能，可增加磁盘。 SQLServer:Cache Hit Ratio计数器该值越高越好。如果

持续低于80%，应考虑增加内存。 注意该参数值是从SQL Server启动后，就一直累加记数，所以运行经过一段时间后，该值将不能反映系统当前值。

47、分析select emp_name form employee where salary > 3000 在此语句中若salary是Float类型的，则优化器对其进行优化为Convert(float,3000)，因为3000是个整数，

我们应在编程时使用3000.0而不要等运行时让DBMS进行转化。同样字符和整型数据的转换。

48、查询的关联同写的顺序

select a.personMemberID, * from chineseresume a,personmember b where personMemberID = b.referenceid and a.personMemberID = 'JCNPRH39681' （A = B ,B = '号

码'）

select a.personMemberID, * from chineseresume a,personmember b where a.personMemberID = b.referenceid and a.personMemberID = 'JCNPRH39681' and

b.referenceid = 'JCNPRH39681' （A = B ,B = '号码'， A = '号码'）

select a.personMemberID, * from chineseresume a,personmember b where b.referenceid = 'JCNPRH39681' and a.personMemberID = 'JCNPRH39681' （B = '号码'， A

= '号码'）
49、
(1)IF 没有输入负责人代码 THEN code1=0 code2=9999 ELSE code1=code2=负责人代码 END IF 执行SQL语句为: SELECT 负责人名 FROM P2000 WHERE 负责人代码>=:code1

AND负责人代码 <=:code2

(2)IF 没有输入负责人代码 THEN 　SELECT 负责人名 FROM P2000 ELSE code= 负责人代码 SELECT 负责人代码 FROM P2000 WHERE 负责人代码=:code END IF 第一种方法只

用了一条SQL语句,第二种方法用了两条SQL语句。在没有输入负责人代码时,第二种方法显然比第一种方法执行效率高,因为它没有限制条件;在输入了负责人代码时,第二种方法仍然

比第一种方法效率高,不仅是少了一个限制条件,还因相等运算是最快的查询运算。我们写程序不要怕麻烦

50、关于JOBCN现在查询分页的新方法（如下），用性能优化器分析性能的瓶颈，如果在I/O或者网络的速度上，如下的方法优化切实有效，如果在CPU或者内存上，用现在的方

法更好。请区分如下的方法，说明索引越小越好。

begin
DECLARE @local_variable table (FID int identity(1,1),ReferenceID varchar(20))
insert into @local_variable (ReferenceID)
select top 100000 ReferenceID from chineseresume order by ReferenceID
select * from @local_variable where Fid > 40 and fid <= 60
end
和
begin
DECLARE @local_variable table (FID int identity(1,1),ReferenceID varchar(20))
insert into @local_variable (ReferenceID)
select top 100000 ReferenceID from chineseresume order by updatedate
select * from @local_variable where Fid > 40 and fid <= 60
end
的不同
begin
create table #temp (FID int identity(1,1),ReferenceID varchar(20))
insert into #temp (ReferenceID)
select top 100000 ReferenceID from chineseresume order by updatedate
select * from #temp where Fid > 40 and fid <= 60 drop table #temp
end

15972155155
ttan727@hotmail.com

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/91551/viewspace-994748/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/91551/viewspace-994748/