数据库调优的方法

回到顶部 1．引言

数据库调优可以使数据库应用运行得更快，它需要综合考虑各种复杂的因素。将数据均 匀分布在磁盘上可以提高I/O 利用率，提高数据的读写性能；适当程度的非规范化可以改善 系统查询性能；建立索引和编写高效的SQL 语句能有效避免低性能操作；通过锁的调优解 决并发控制方面的性能问题。     数据库调优技术可以在不同的数据库系统中使用,它不必纠缠于复杂的公式和规则，然 而它需要对程序的应用、数据库管理系统、查询处理、并发控制、操作系统以及硬件有广泛 而深刻的理解。

回到顶部 2．计算机硬件调优 2.1 数据库对象的放置策略 利用数据库分区技术，均匀地把数据分布在系统的磁盘中，平衡I/O 访问，避免I/O 瓶 颈：

（1）访问分散到不同的磁盘，即使用户数据尽可能跨越多个设备，多个I/O 运转，避免 I/O 竞争，克服访问瓶颈；分别放置随机访问和连续访问数据。   
（2）分离系统数据库I/O 和应用数据库I/O，把系统审计表和临时库表放在不忙的磁盘 上。   
（3）把事务日志放在单独的磁盘上，减少磁盘I/O 开销，这还有利于在障碍后恢复，提 高了系统的安全性。   
（4）把频繁访问的“活性”表放在不同的磁盘上；把频繁用的表、频繁做Join的表分别 放在单独的磁盘上，甚至把频繁访问的表的字段放在不同的磁盘上，把访问分散到不同的磁 盘上，避免I/O 争夺。

2.2 使用磁盘硬件优化数据库

 RAID (独立磁盘冗余阵列)是由多个磁盘驱动器(一个阵列)组成的磁盘系统。通过将磁盘阵列当作一个磁盘来对待，基于硬件的RAID允许用户管理多个磁盘。使用基于硬件的 RAID与基于操作系统的RAID相比较，基于硬件的RAID能够提供更佳的性能。如果使用基于操作系统的RAID，那么它将占据其他系统需求的CPU周期；通过使用基于硬件的RAID， 用户在不关闭系统的情况下能够替换发生故障的驱动器。   

SQL Server 一般使用RAID等级0、1 和5。
RAID 0 是传统的磁盘镜象，阵列中每一个磁盘都有一个或多个磁盘拷贝，它主要用来 提供最高级的可靠性，使RAID 0成倍增加了写操作却可以并行处理多个读操作，从而提高 了读操作的性能。 RAID 1 是磁盘镜像或磁盘双工，能够为事务日志保证冗余性。 RAID 5带奇偶的磁盘条带化，即将数据信息和校验信息分散到阵列的所有磁盘中，它可以消除一个校验盘的瓶颈和单点失效问题，RAID 5 也会增加写操作，也可以并行处理一个读操作，还 可以成倍地提高读操作的性能。
相比之下，RAID 5 增加的写操作比RAID 0 增加的要少许多。在实际应用中，用户的读操作要求远远多于写操作请求，而磁盘执行写操作的速度很快，以至于用户几乎感觉不到增加的时间，所以增加的写操作负担不会带来什么问题。在性能较好的服务器中一般都会选择使用RAID 5 的磁盘阵列卡来实现，对于性能相对差一些的服务器也可利用纯软件的方式来实现RAID 5。

回到顶部 3．关系系统与应用程序调优

3.1 应用程序优化 

从数据库设计者的角度来看，应用程序无非是实现对数据的增加、修改、删除、查询和体现数据的结构和关系。设计者在性能方面的考虑因素，总的出发点是：把数据库当作奢侈 的资源看待，在确保功能的同时，尽可能少地动用数据库资源。包括如下原则：
（ 1）不访问或少访问数据库；
（2）简化对数据库的访问；
（3）使访问最优；
（4 ）对前期及后续的开发、部署、调整提出要求，以协助实现性能目标。
另外，不要直接执行完整的SQL 语法，尽量通过存储过程来调用SQL Server。客户与服务器连接时，建立连接池，让连接尽量得以重用，以避免时间与资源的损耗。非到不得已， 不要使用游标结构，确实使用时，注意各种游标的特性。

3.2 基本表设计优化

 在基于表驱动的信息管理系统中，基本表的设计规范是第三范式。第三范式的基本特征 是非主键属性只依赖于主键属性。基于第三范式的数据库表设计具有很多优点：一是能消除 冗余数据、节省磁盘存储空间；二是有良好的数据完整性限制（基于主外键的参照完整限制 和基于主键的实体完整性限制），这使得数据容易维护、移植和更新；三是数据的可逆性好， 在做连接查询或者合并表时不遗漏、不重复；四是消除了冗余数据（这里主要指冗余列）， 使得查询时每个数据页存储的数据行增多，这样就有效地减少了逻辑I/O，同时也减少了物 理I/O；五是对大多数事务而言，运行性能好；六是物理设计的机动性较大，能满足日益增 长的用户需求。
基于第三范式设计的库表虽然有其优越性，然而在实际应用中有时不利于系统运行性能 的优化：例如需要部分数据时而要扫描整表，许多过程同时竞争同一数据，反复用相同行计 算相同的结果，过程从多表获取数据时引发大量的连接操作，当数据来源于多表时的连接操 作；这都消耗了磁盘I/O 和CPU 时间。特别需要提出的是，在遇到下述情形时，我们要对 基本表进行扩展设计优化：许多过程要频繁访问一个表、子集数据访问、重复计算和冗余数 据，有时用户要求一些过程优先或低的响应时间，为避免以上不利因素，我们通常根据访问 的频繁程度对相关表进行分割处理、存储冗余数据、存储衍生列、合并相关表处理，这些都 是克服这些不利因素和优化系统运行的有效途径。
（1）分割表     分割表可分为水平分割表和垂直分割表两种：水平分割是按照行将一个表分割为多个 表，这可以提高每个表的查询速度，但是由于造成了多表连接，所以应该在同时查询或更新 不同分割表中的列的情况比较少的情况下使用。垂直分割是对于一个列很多的表，若某些列 的访问频率远远高于其它列，在不破坏第三范式的前提下将主键和这些列作为一个表，将主 键和其它列作为另外一个表。一种是当多个过程频繁访问表的不同列时，可将表垂直分成几 个表，减少磁盘I/O。通过减少列的宽度，增加了每个数据页的行数，一次I/O 就可以扫描 更多的行，从而提高了访问每一个表的速度。垂直分割表可以达到最大化利用Cache 的目的。 分割表的缺点是要在插入或删除数据时要考虑数据的完整性，用存储过程维护。
（2）存储衍生数据     对一些要做大量重复性计算的过程而言，若重复计算过程得到的结果相同，或计算牵扯 多行数据需额外的磁盘I/O 开销，或计算复杂需要大量的C P U 时间，就考虑存储计算结果：若在一行或多行进行重复性计算，就在表内增加列存储结果，但若参与计算的列被更新时， 必须要用触发器或存储过程更新这个新列。总之，存储冗余数据有利于加快访问速度，但违 反了第三范式，这会增加维护数据完整性的代价，必须用触发器立即更新、或存储过程更新， 以维护数据的完整性。

3.3 修改应用技术模式

 引入“中间表”的概念，在实际单据未进入核心业务流程前，采用“中间表”的技术思 路，就是在实际用户操作过程中，实际操作的是一个临时表，在进行数据某个阶段审核（进 入下一个环节）后，将临时表的数据写入正式表，并且删除临时表的数据，这样始终保持用 户操作表的固定的数据量而且控制增长，可以定期清除。     采用临时表技术首先需将要操作的数据集插入到临时表中，这会给系统带来额外的开 销。这里假设临时表中的数据集远小于源数据表中的数据集，因此在进行数据连接操作或对 数据集进行频繁读操作时，系统的性能会提高几倍甚至几十倍不等。   
并非所有情况都适宜用临时表技术。一般来说，下面两种情况适宜采用临时表技术进行 处理：    
（1）对数据量较大的表进行连接操作，并且连接操作的结果是一个小结果集。
（2）对数据量较大的表进行频繁访问，访问的范围比较固定且比较集中。
合理使用临时表技术，有助于提高应用系统对大数据表的实时处理的性能。

回到顶部 4．数据库索引优化

索引是建立在表上的一种数据组织，它能提高访问表中一条或多条记录的特定查询效 率。利用索引优化系统性能是显而易见的，对所有常用于查询中的Where  子句的列和所有 用于排序的列创建索引，可以避免整表扫描或访问，在不改变表的物理结构的情况下，直接 访问特定的数据列，这样可以减少数据存取时间；利用索引可以优化或排除耗时的分类操作， 把数据分散到不同的页面上，这样就分散了插入的数据；主键自动建立了唯一索引，因此唯 一索引也能确保数据的唯一性（即实体完整性）。总之，索引可以加快查询速度、减少I/O 操作、消除磁盘排序。     优化索引可以避免扫描整个表，减少因查询造成的开销。一般说来建立索引要注意以下 几点：    
（1）检查被索引的列或组合索引的首列是否出现在PL/SQL 语句的WHERE 子句中， 这是“执行计划”能用到相关索引的必要条件。比较一下列中唯一键的数量和表中记录的行 数，就可以判断该列的可选择性。如果该列的“唯一键的数量/表中记录行数”的比值越接近 于1，则该列的可选择行越高。在可选择性高的列上进行查询，返回的数据就较少，比较适 合索引查询。相反，比如性别列上只有两个值，可选择行就很小，不适合索引查询。因此， 在查询中经常作为条件表达式且不同值较多的列上建立索引，不同值较少的列上不要建立索 引。    
（2）索引的创建也是需要代价的，对于删除、某些更新、插入操作，对于每个索引都 要进行相应的删除、更新、插入操作。从而导致删除、某些更新、插入操作的效率变低。因 此频繁进行删除、插入操作的表不要建立过多的索引。    
（3）查询经常用到的列上建立非聚簇索引，在频繁进行范围查询、排序、分组的列上 建立聚簇索引。    
（4 ）对于不存在重复值的列，创建唯一索引优于创建非唯一索引。    
（5）当数据库表更新大数据后，删除并重新建立索引来提高查询速度。    
（6）当对一个表的update 操作远远多于select 操作时，不应创建索引。    
（7）如果索引列是函数的参数，则索引在查询时用不上，该列也不适合索引。    
（8）Hash   Join （HJ ）由于须做HASH 运算，索引的存在对数据查询速度几乎没有影 响。    
（9）在主键上建立索引，尤其当经常用它作为连接的时候；在经常用于连接而又未指 定为外键的列上建立索引。   
 (10)经常同时存取多列，且每列都含有重复值，可以考虑建立复合索引来覆盖一个或 一组查询，并且把查询引用最频繁的列作为前导列。                                    
（11）尽使用较窄的索引，这样数据页每页上能因存放较多的索引行而减少操作。    
（12）并行查询将不会用到索引。    
（13）索引中存储值不能为全空。    
（14）查询中较少用到的列、数据量较大的列均不应建立索引。

回到顶部 5．SQL 语句优化

在完成了系统设计、索引设计等工作以后，就要考虑在使用过程中对语句的设计了。 影响数据库应用程序性能的一个重要因素是SQL 语句，按其影响严重程度，依次可分为： 无谓的SQL，拙劣的SQL，复杂的SQL。     无谓的SQL：它们对数据库的访问，并不存在技术、技能上的问题，但却不是必要的， 超出了实际业务需求。其结果是浪费了宝贵的主机资源、占用了网络流量，降低了系统性能。     拙劣的SQL：它们对数据库的访问并不是多余的，所体现的业务逻辑或结果是正确的， 但是“写法”不够好，导致数据库处理起来不够优化。     复杂的SQL：数据库中多表（或视图）关联，条件复杂、冗长，计算复杂，使用冷僻 的SQL 技术等。     其中，无谓的SQL 和拙劣的SQL 属于开发技能方面的问题；复杂的SQL 属于设计技 能方面的问题，设计到数据库的结构。     在使用结构化查询语言来执行查询时，推荐以下举措：   
（1）择运算应尽可能先做，并在对同一个表进行多个选择运算时，选择影响较大的语 句放在前面；较弱的选择条件写在后面，这样就可以先根据较严格的条件得出数据较小的 信息，再在这些信息中根据后面较弱的条件得到满足条件的信息。   
（2）应避免使用相关子查询。把子查询转换成联结来实现。对于主查询的每一条记录子 查询都要执行一次，嵌套的层次越多效率越低。避免对子句使用数学运算符。即不要对数 据表的属性列进行操作。SQL 概念上将位于WHERE  子句中的相关子查询，处理成获取参 数并且返回一个单独的值或值的集合的函数。因为子查询要对应位于外层查询的每一个元组 进行单独的计算。从而导致大量的随机磁盘I/O 操作。所以在实际应用中若可以用连接代替 的子查询，则用连接实现。例如，有以下相关子查询语句：   

SELECT ProductName FROM Products WHERE EXISTS (SELECT * FROM OrderDetails WHERE Discount >= 25 AND Products.ProductID= OrderDetails.ProjectID) ；
用连接查询实现如下：
SELECT ProductName FROM Products ， OrderDetails WHERE Discount >= 25 AND Products.ProductID= OrderDetails.ProjectID
（3）字段提取按照“ 需多少，提多少” 的原则，避免“SELECT *”。“SELECT *”需 要数据库返回相应表的所有列信息，这对于一个列较多的表无疑是一项费时的操作。
(4)避免使用!=(或<>)、IS NULL 或IS NOT NULL、IN、NOT IN等这样的操作符，避免在WHERE 子句中使用非聚合表达式。这些操作符会使系统无法使用索引，而只能直接搜 索表中的数据。例如，SELECT id，name FROM employee WHERE id!=B% 优化器将无法通过索引来确定将要命中的行数，因此需要搜索该表的所有行。
(5)避免使用OR，用UNION 代替。OR 语句的执行原理并不是利用列上的索引根据每 个语句分别查找再将结果求并集，而是先取出满足每个OR 子句的行，存入临时数据库的 工作表中，再建立唯一索引以去掉重复行，最后从这个临时表中计算结果。这样使用可能 造成索引失效，导致顺序扫描整个表，大大降低查询效率。
（6）在执行连接前对关系作适当的预处理，预处理的方法有两种，在连接属性上建立 索引和对关系进行排序。
（7）将一个大的查询拆成多步执行查询。
（8）如果应用程序使用循环,可考虑在查询内放入循环。

回到顶部 6．事务处理调优

数据库的日常运行过程中可能面临多个用户同时对数据库的并发操作带来的数据不一 致的问题，如：丢失更新、脏读和不可重复读等。并发控制的主要方法是封锁，锁就是在一段时间内禁止用户做某些操作以避免产生数据不一致。   
数据库应用程序将其工作分成若干个事务进行处理。当一个事务执行时，它访问数据库 并执行一些本地计算。开发人员可以假设每一个事务都会被隔离地执行—没有任何并发动 作。因为隔离的概念提供了透明性，这种对事务处理方式的保证有时被称为原子性保证。但 是，如果把应用程序中的事务序列作为一个整体来看，则并没有上面所说的那种保证。在一 个应用程序执行的两个事务之间，可能会执行另外一个应用程序的事务，而且第二个应用程 序的执行可能修改了第一个应用程序中的两个事务（或其中的一个）需要访问的数据项。因 此，事务的长度对保证正确性有着重要影响。   
尽管将事务切分成较小粒度可以提高执行效率，但会因此破坏执行的正确性。这种性能 和正确性之间的矛盾充斥并发控制的整个调优过程。考虑事务的性能我们要考虑到：事务使 用的锁的个数（在所有其他条件相同的情况下，使用的锁个数越少，性能越好）；锁的类型  （读锁对性能更有利）；事务持有锁的时间长短（持有时间越短，性能越好）。
关于锁的调优 有以下建议：

（1）使用特殊的系统程序来处理长的读操作。对于一个只读的事务R 来说，它“看到” 的数据库的状态一直是事务R 开始时的状态。只读查询可以不需要封锁开销，在不造成阻 塞和死锁的情况下，只读的查询可以与其他对同一数据进行更新的较小的事务并行地执行。 （2）消除不必要的封锁。只有一个事务执行时，或所有事务都是只读事务时，用户应 利用配置选项减少锁的个数，从而减小锁管理模块的内存开销和执行封锁操作的处理时间开 销。 （3）根据事务的内容将事务切分成较小的事务。事务所要求的锁越多，它需要等待其 他事务释放某个锁的可能就越大。事务T 执行的时间越长，被T 阻塞的事务等待的时间可 能就越长。因此，在可能发生阻塞的情况下，利用较短的事务较好。
（4 ）在应用程序允许的情况下，适当降低隔离级别。
（5）选择适当的封锁粒度。页级封锁阻止并发事务访问或修改该页面上所有记录，表 级封锁阻止并发事务访问或修改表内所有的页面；记录级封锁（行级锁）比页级封锁粒度好， 页级封锁比表级封锁粒度好。长事务（指要访问表内几乎所有页面的事务）应该尽可能使用 表级封锁来防止死锁，而短事务应该使用记录级封锁来提高并发度。
（6）只在数据库很少被访问时才修改有关数据定义的数据（系统目录或元数据）。每个 能够编译、添加或删除表、添加或删除索引、改变属性定义的事务都必须访问目录数据，因 此，目录很容易成为热点，也因而成为瓶颈。
（7）减少访问热点（大量事务访问和更新的数据）。只有在更新某热点的事务完成滞后， 其他的事务才能获得这个热点上的锁，因此热点可能成为瓶颈。
（8）死锁检测周期的调优。
以上每个建议都可以独立于其他建议来运用，但是在调优时必须检测是否能体现合适 的隔离性保证。

回到顶部 7．总结

数据库性能优化的基本原则就是通过尽可能少的磁盘访问获得所需要的数据。本文从计 算机硬件、关系系统与应用程序、数据库索引、SQL  语句、事务处理几个比较共性的方面 分析了数据库性能优化的问题,  提出了若干数据库性能优化的策略。当然实现优化的方法还 有很多,  要根据具体情况而定。对于不同的应用情况,我们应该具体情况具体分析,  各方面优 化措施综合运用,  以使数据库性能得到提高。数据库应用系统的性能是一项全民工程，开发 团队的所有人都有责任为性能做贡献，树立性能意识，使之成为日常工作的习惯而不是单独 成为某一阶段的工作，要未雨绸缪，不要寄希望于某一个环节的工作。

[转自] http://www.myfreelinux.com/?p=538

面试中回答关于oracle数据库优化的方法 1关于优化器的优化配置 介绍oracle数据库优化的基本方式，基于规则的优化，基于规则包括全表扫描，扫描第一行 (需要上网查看一下) 基于成本的优化，基于选择的优化 也就是oracle的3种优化器，关于优化器的配置可以通过oracle数据库启动的时候通过初始化 数据库配置文件来配置 通过配置init.ora文件的optimizer_mode也就是优化模式的参数来 配置这个配置是一个全局的共享配置。优化模式可以根据具体的生产环境在用户会话级别和sql 级别进行在配置 ,来覆盖全局的优化模式配置 使用基于成本的优化器要运行一个命令 analyze(分析)让oracle分析表 统计统计数据对象 达到更好的优化效果.(如果针对表执行了analyze 将自动使用基于成本的优化器) 2oracle访问表的方式优化 a 全表扫描模式 全表扫面oracle采用读取数据块的方式对全部表进行扫面块的大小可以在 init.ora中配置 数据块的大小应该是系统的数据块的整数倍。适当调节数据块的大小可以 起到性能优化的作用 b 通过rowid访问数据表 rowid表记录的物理位置信息标记，oracle通过索引与rowid建立 联系通过适当的在字段上建立索引可以高速高效的访问数据表的记录，从而达到数据库优化 查询的目的。 3 共享sql语句 为了不重复解析SQL语句oracle采用共享SQL的方式提高SQL语句执行效率。oracle把执行过的 语句放入到一个叫做SGA内存区域的share buffer pool 中 也就是系统全局区域的缓存共享池 如果客户端执行的SQL语句完全相同oracle首先查找share buffer pool 如果存在这个语句 直接执行这个语句。不过oracel对SQL语句的匹配要求很严格是在字符级别进行匹配的，包括 执行语句的字符匹配。所以在实际的项目中尽量要求编码人员统一规范编写SQL语句尽量做到 统一以提高数据库的性能和执行效率。还有就是oracle基于对象的共享匹配，包括用户对象， 变量的绑定在权限对象分配中和dba沟通做好系统优化的准备。 我们可以适当的提高SGA中share buffer pool 的大小 来优化数据库的执行效率 4 根据oracle的SQL语句解析器进行优化 首先解释一下oracle 解释SQL的语句的顺序，oracle在解释SQL语句的时候是按照从右到左 的顺序进行解析。所有要把检索效率最高的表，记录最少的表写在FROM的最右表也就是基础 表的选择 5 交叉表优先选择 在多对多的表关联中会遇到这样的情况。在sql语句的书写方面尽量把引用表放在FROM的 右边 6 基于wher条件书写顺序的解释优化 首先介绍一下oracle 以上两个数据库优化的基本原则就是根据oracle解析sql语句和oracle自身对在扫描表的时候 做的优化基础上尽量优先选择出记录少的表放在from的最右边

7尽量减少与数据库的交互减少系统的IO访问量 8 不要用* 9 使用decode函数减少处理时间 10 整合简单的无关联的数据库访问 较少数据库服务器的IO操作 11删除重复记录 尽量使用RID 12 用TRUNCATE替代DELETE 13 在编写pl/sql时候尽量多使用cimmit 以便释放系统资源 只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少: COMMIT所释放的资源: a. 回滚段上用于恢复数据的信息. b. 被程序语句获得的锁 c. redo log buffer 中的空间 d. ORACLE为管理上述3种资源中的内部花费 14. 用Where子句替换HAVING子句 避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销.

例如:

低效: SELECT REGION，AVG(LOG_SIZE) FROM LOCATION GROUP BY REGION HAVING REGION REGION != ‘SYDNEY’ AND REGION != ‘PERTH’

高效 SELECT REGION，AVG(LOG_SIZE) FROM LOCATION WHERE REGION REGION != ‘SYDNEY’ AND REGION != ‘PERTH’ GROUP BY REGION (译者按: HAVING 中的条件一般用于对一些集合函数的比较,如COUNT() 等等. 除此而外,一般的条件应该写在WHERE子句中)

15 尽量少用子查询 合并可以合并的子查询 例如: 低效 SELECT TAB_NAME FROM TABLES WHERE TAB_NAME = ( SELECT TAB_NAME FROM TAB_COLUMNS WHERE VERSION = 604) AND DB_VER= ( SELECT DB_VER FROM TAB_COLUMNS WHERE VERSION = 604)

高效 SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER) = ( SELECT TAB_NAME,DB_VER) FROM TAB_COLUMNS WHERE VERSION = 604)

Update 多个Column 例子: 低效: UPDATE EMP SET EMP_CAT = (SELECT MAX(CATEGORY) FROM EMP_CATEGORIES), SAL_RANGE = (SELECT MAX(SAL_RANGE) FROM EMP_CATEGORIES) WHERE EMP_DEPT = 0020;

高效: UPDATE EMP SET (EMP_CAT, SAL_RANGE) = (SELECT MAX(CATEGORY) , MAX(SAL_RANGE) FROM EMP_CATEGORIES) WHERE EMP_DEPT = 0020;

15. 使用表的别名(Alias) 当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上.这样一来, 就可以减少解析的时间并减少那些由Column歧义引起的语法错误.

比较运算符能用 “=”就不用“<>”。“=”增加了索引的使用几率。

明知只有一条查询结果，那请使用 “LIMIT 1”。“LIMIT 1”可以避免全表扫描，找到对应结果就不会再继续扫描了。

为列选择合适的数据类型。能用TINYINT就不用SMALLINT，能用SMALLINT就不用INT，毕竟磁盘和内存消耗越小越好嘛。

将大的DELETE，UPDATE or INSERT 查询变成多个小查询。一个几十行、几百行的SQL语句显得很高级。然而，为了达到更好的性能以及更好的数据控制，可以将他们变成多个小查询。

代替 UNION如果结果集允许重复的话，使用UNION ALL 。因为 UNION ALL 不去重，效率高于 UNION。

为获得相同结果集的多次执行，保持SQL语句前后一致。这样做的目的是为了充分利用查询缓冲。

尽量避免使用 “SELECT *”。如果不查询表中所有的列，尽量避免使用 SELECT *，因为它需要先查询 table meta，增大了数据库服务器的负担，以及它与应用程序客户端之间的网络IO开销。

WHERE 子句里面的列尽量被索引。只是“尽量”哦，并不是说所有的列。因地制宜，根据实际情况进行调整，因为有时索引太多也会降低性能。

JOIN 子句里面的列尽量被索引。同样只是“尽量”哦，并不是说所有的列。

ORDER BY 的列尽量被索引。

使用 LIMIT 实现分页逻辑。不仅提高了性能，同时减少了不必要的数据库和应用间的网络传输。

使用 EXPLAIN 关键字去查看执行计划。EXPLAIN 可以检查索引使用情况以及扫描的行。

开发人员需要具备的DBA技术 背景 在一些小公司或者部门里，通常很少有专门的DBA职位。这时候就需要我们这些程序员充当业余DBA的作用，去监测和维护数据库性能。本文的目的是帮助非DBA专业的开发人员如何定位和解决日常出现数据库问题，并提供一些相关有用的工具。

大部分项目一开始的数据库都是很小的，但随着时间的推进，数据库变得越来越庞大，访问性能也越来越慢。因此优化数据库在所难免，数据库优化的三大核心：保持系统稳定、确保数据完整性和高质量、性能。

了解你的数据库 在开始优化数据库的时候，我们必须要了解自己所使用的数据库的结构和用途以及如下一些基本信息。

首先，平时要进行适度的备份和还原数据库，对备份的数据进行检测，可以在不影响当前数据库的情况下，进行一些输入性质的检测操作。 区分数据来源、哪张表拥有最多数据、运行速度。运行一些脚本去了解你的数据库。哪些表经常变化、哪些表需要建索引 找出表之间的关系依赖 找出数据是如何进来以及何时失效的 调查数据如何以及为什么要进行状态的转换 做记录 使用内置的数据库报表 　　

如何让你的数据库更健壮 当与数据交道时，健壮的代码至关重要。需要注意的是事务、error捕获和日志的使用。当设计数据库结构和架构时，都会面临存储过程的选择。存储过程是预先写好的数据库脚本，能够被命令取消。当使用C#或者JavaScript前端写代码时，允许某些地方使用错误的处理和日志记录哪些错误操作。但存储过程就不一样了，因为相比于网站前端，它很少与用户有交互。在使用存储过程时，有三点需要留意：

1. 如果允许，请把脚本写于事务块里面

2. 使用try...catch...监测捕获错误信息

3. 记录错误信息

当错误捕获后，需要记录，你可以使用MS SQL内置的日志系统记录，但这又涉及到权限，可能你没有办法访问。你也可以创建一张日志表记录错误信息。

性能问题 通常我们都会遇到访问速度的问题。速度慢无非就是数据量过大、处理脚本执行效率低、索引创建不合理、硬件或者系统配置等问题。索引是解决性能的优先选择，但是如果拥有太多的索引就会造成性能问题。其次是字段的类型，尽量使用varchar替代nvarchar

使用索引

1. 正确使用索引

2. 使用SQL 索引提示

其他性能问题

1. 非数据库引起的性能问题

一般情况下，更多造成性能问题的是我们不规范和不合理的写法导致的。例如一下2个例子

用between语法比和比较来的快

通过foreach遍历循环查找单条数据，显然效率也会大打折扣。取而代之，可以使用join表关联来查找你要的结果

2. 查询复杂来源的数据消耗太多时间

a. 前期预加载和规范

一般情况下，添加索引、优化查询语句到目前为止能够很好的优化性能。还有一种情况，由于访问数据量很大，且这些大数据是从其他数据量也很大的地方集合过来的，因此它消耗的时间就很长。最好的方式就是预先读取这些数据或者去规范化这些数据。如果你的查询来源是一个pivot表，那么他可能来自一系列的关联数据、视图、存储过程，好的解决方式就是创建一个新的存储过程去搜集这些需要的数据，然后把他们存入一个更规范的数据表中方便阅读和查询。先规范化你的数据，也能够帮你省去很多宝贵的实践，不仅仅在加载数据前，也包括格式排版。比如你访问的数据需要整理成JSON格式的时候，你无需在访问的时候才做格式化操作，完全可以预先加载这些数据整理成JSON格式。

b. 多线程执行存储过程

有时候一些存储过程确实需要花费一些时间去执行，而这些执行过程并不是你所关心的。当你访问这些存储过程并等待时，就可能会面临超时的错误出现。解决方式，就是开始执行存储过程的时候，去检查他是否已经执行完毕而不是卡着线程等待它的结果。这些存储过程通常发生在一个脚本本来执行很快，但随着数据量的增加，他消耗的时间越来越久导致超时。你需要做的就是使用SqlCommand.BeginExecuteNonQuery()方法。这个方法在后台线程执行存储过程，而不影响你当前线程执行其他事情，无需等待。

如何导入数据 数据的导入也是开发人员需要具备的技能。MS SQL提供了内置的导入功能，可以通过text文件\数据库文件、csv或者xml。这里不做细聊

推荐常用的工具

1. DBCC

Database console commands 能够检查数据库级别或者表级别数据的完整性，重建索引的表，执行一系列的维护工作。

DBCC CheckDB: 检查数据库的逻辑和物理的完整性

DBCC CheckTable(‘TableName’) :检查表或者视图的结构完整性

DBCC ShrinkDatabase(‘DatabaseName’): 压缩数据库的物理大小

2. SP_WHO和SP_WHO2

有2个内置的系统存储过程比较有用。SP_WHO和SP_WHO2.这两个脚本可以提供当前sql连接的详细信息，包括连接该数据库的不同应用、连接类型、进程锁定的详细信息。

exec sp_who2的用法

下面2张图可以清晰地看出谁连接了数据库、当前的活动和状态，BlkBy代表进程被谁锁定了

3. SQL Server Profiler的使用

Sql Server Profiler可以实时监测你的数据库，既可以跟踪执行过的语句，也可以查看EF/Linq的语法生成后的sql语句。

4. RedGate SQL Search

安装后sql会有提示功能，可以快速丁文你的数据库、表、触发器、存储过程、索引等。相当好用

5. Brent Ozar

包含一些脚本文件，可以检测数据库的性能、索引分析、计算出最影响查询性能的地方。它还包括一些有用的检查清单。

6. Server monitor

数据库服务器的监控，在发生问题时，会抢先一步客户通过邮件通知你。

oracle相关对象查询： 1、查询用户表，数据行数量及分析时间 select tablespace_name,table_name,num_rows,last_analyzed from user_tables order by num_rows desc

2、查询段名称，段类型，表空间及大小(M)，并按照大小进行排序

select segment_name,segment_type,tablespace_name,SUM (bytes) / (1024 * 1024) "TABLE_SIZE_MB" from user_segments GROUP BY segment_name,segment_type,tablespace_name order by table_size_mb desc;

3、导出序列 select 'create sequence ' || SEQUENCE_NAME || ' minvalue ' || MIN_VALUE || ' maxvalue ' || MAX_VALUE || ' start with ' || LAST_NUMBER || ' increment by ' || INCREMENT_BY || ' nocache ' || ' ;' from DBA_SEQUENCES where SEQUENCE_OWNER = UPPER('HUBEI');--用户名

4、查询dblink

select * from dba_db_links;

5、查询索引及其字段 select i.index_name,i.status, i.index_type, i.table_owner, i.table_name, i.uniqueness, i.tablespace_name, c.column_name, c.column_position, c.column_length from user_indexes i, user_ind_columns c where i.index_name = c.index_name;

6、查询视图及创建语法 select view_name,text from user_views

7、查询函数 select object_name from user_objects where object_type='FUNCTION';

8、查询数据块文件及表空间

select tablespace_name,file_name from dba_data_files;

sqlserver 相关查询

1、查询数据块文件大小及路劲（数据库名.dbo.sysfiles）

select name,filename, convert(float,size) * (8192.0/1024.0)/1024. from testgree.dbo.sysfiles

2、查询数据库用户 SELECT name as UserName, type_desc as UserType, is_disabled as IsDisabled FROM sys.server_principals where type_desc in('WINDOWS_LOGIN', 'SQL_LOGIN') order by UserType, name, IsDisabled

3、查询用户的表 --SELECT NAME FROM SYSOBJECTS WHERE TYPE='U'; select schema_name(t.schema_id) as [Sehema],t.name as TableName,I.rows as [RowCount] from sys.tables as t ,sysindexes as i where t.object_id=i.id and i.indid<=1

4、查询用户的索引

select a.name as tabname ,h.name as idname from sys.objects as a right join sys.indexes as h on a.object_id=h.object_id where a.type<>'s'

5、查询函数、存储过程 select routine_name,routine_definition,routine_type from information_schema.routines where routine_definition like'%exec%' ORDER BY routine_type

MySQL DBA 常用手册 1.mysql的远程连接命令可以远程导入导出数据 mysqldump --default-character-set=gb2312 -h255.255.000.00 -uroot -pxxxxxx mydatabase>d:\data.sql 将指定的数据库导出到一个外部SQL文件中去！ 还原命令： mysql --default-character-set=gb2312 -h255.255.000.00 -uroot -pxxxxxx mydatabase<d:\data.sql

2. MYSQL三种升级方法 安装办法：二进制包编译安装 第一种：适用于任何一种存储引擎。

3. 下载并安装好新版本的MySQL数据库，并将其端口改为3307（避免和旧版本的3306冲突），启动服务。

4. 在新版本下创建同名数据库。

mysqldump -p3307 -uroot create mysqlsystems_com

3. 在旧版本下备份该数据库。

mysqldump -p3306 -uroot mysqlsystems_com > mysqlsystems_com.bk

Note: 你也可以加上–opt选项，这样可以使用优化方式将你的数据库导出，减少未知的问题。

4. 将导出的数据库备份导入到新版本的MySQL数据库中。

mysql -p3307 -uroot mysqlsystems_com < mysqlsystems_com.bk

5. 再将旧版本数据库中的data目录下的mysql数据库全部覆盖到新版本中。

cp -R /opt/mysql-5.1/data/mysql /opt/mysql-5.4/data（权限库保持不变）

Note: 大家也都知道这个默认数据库的重要性。

6. 在新版下执行mysql_upgrade命令，其实这个命令包含一下三个命令：

mysqlcheck –check-upgrade –all-databases –auto-repair

mysql_fix_privilege_tables

mysqlcheck –all-databases –check-upgrade –fix-db-names –fix-table-names

Note: 在每一次的升级过程中，mysql_upgrade这个命令我们都应该去执行，它通过mysqlcheck命令帮我们去检查表是否兼容新版本的数据库同时 作出修复，还有个很重要的作用就是使用mysql_fix_privilege_tables命令去升级权限表。

7. 关闭旧版本，将新版的数据库的使用端口改为3306，重新启动新版本MySQL数据库。到此，一个简单环境下的数据库升级就结束了。

第二种，同样适用任何存储引擎。

1. 同样先安装好新版本的MySQL。

2. 在旧版本中，备份数据库。

mkdir /opt/mysqlsystems_bk ; mysqldump -p3306 -uroot –tab=/opt/mysqlsystems_bk mysqlsystems_com

Note: –tab选项可以在备份目录mysqlsystems_bk下生成后缀为*.sql和*.txt的两类文件；其中，.sql保存了创建表的SQL语句而.txt保存着原始数据。

3. 接下来在新版本的数据库下更新数据。

mysqladmin -p3307 -uroot create mysqlsystems_com

cat /opt/mysqlsystems_bk/*.sql | mysql -p3307 -uroot mysqlsystems_com ( Create Tables )

mysqlimport mysqlsystems_com /opt/mysqlsystems_bk/*.txt ( Load Data )

4. 之后的所有步骤与第一种方法的后三步5、6、7相同。

第三种，适用于MyISAM存储引擎，全部是文件间的拷贝。

1. 安装。

2. 从旧版本mysqlsystems_com数据库下将所有.frm、.MYD 和.MYI文件拷贝到新版本的相同目录下。

3.之后的步骤依然同于第一种的后三步。

以上就是三种升级MySQL的方法，看似没有出现什么问题，其实，在实际的生产环境中，为会有诸多问题发生，这就需要我们在升级之前充分了解新版本中增加了哪些新功能，进一步分析升级以后这些新特性是否将会对我们原来应用产生影响。

3. MYSQL远程连接不了的解决方案

有可能是这个用户权限不够。查看一下权限表。

4. MYSQL忘记密码解决办法

在windows下： 打开命令行窗口，停止mysql服务：Net stop mysql 到mysql的安装路径启动mysql，在bin目录下使用mysqld-nt.exe启动，在命令行窗口执行：mysqld-nt --skip-grant-tables 然后另外打开一个命入令行窗口，执行mysql，此时无需输入密码即可进入。

use mysql update user set password=password("new_pass") where user="root"; flush privileges; exit 使用任务管理器，找到mysqld-nt的进程，结束进程！ 在重新启动mysql-nt服务，就可以用新密码登录了。

在linux下： 如果 MySQL 正在运行，首先杀之： killall -TERM mysqld。 启动 MySQL ：bin/safe_mysqld --skip-grant-tables & 就可以不需要密码就进入 MySQL 了。 然后就是

use mysql update user set password=password("new_pass") where user="root"; flush privileges; 重新杀 MySQL ，用正常方法启动 MySQL 。

5. 更改MYSQL的默认字符集

法1、 用 SET 语法来指定，不加 "GLOBAL" 的话就只对本次会话有效 SET [GLOBAL] character_set_client = utf8; SET [GLOBAL] character_set_connection = utf8; SET [GLOBAL] character_set_database = utf8; SET [GLOBAL] character_set_results = utf8; SET [GLOBAL] character_set_server = utf8;

方法2、 也用SET语法，只对本次会话有效 SET NAMES 'utf8';

方法3、） 直接修改 my.cnf，增加一行内容，然后重启 MySQL，使之全局生效 default-character-set = utf8

6.MYSQL慢查询分析工具：mysqldumpslow

mysqldumpslow命令 /path/mysqldumpslow -s c -t 10 /database/mysql/slow-log 这会输出记录次数最多的10条SQL语句，其中：

* -s, 是表示按照何种方式排序，c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序，ac、at、al、ar，表示相应的倒叙；
* -t, 是top n的意思，即为返回前面多少条的数据；
* -g, 后边可以写一个正则匹配模式，大小写不敏感的；

比如 /path/mysqldumpslow -s r -t 10 /database/mysql/slow-log 得到返回记录集最多的10个查询。 /path/mysqldumpslow -s t -t 10 -g “left join” /database/mysql/slow-log 得到按照时间排序的前10条里面含有左连接的查询语句。

查看mysql的某个选项 show variables like ‘%VAR_NAME%’; select @@VAR_NAME;

在Linux下管理MySQL数据库的时候总有一些很紧急的情况，发现数据库突然变得压力很大了，那么作为一个DBA，也许需要一些常用的手段或者说命令去分析问题出现在哪里，然后解决：

数据库突然产生压力时查看正在查询的SQL:(如果这里内容太多表示并发执行的SQL过多，或许数据库堵塞了，会越来越慢，正常情况下这里应该很少有东西的，也就是连接都在Sleep状态） /usr/local/mysql/bin/mysql -uroot -ppassword databaseName -e “show full processlist” | grep -v Sleep

正在运行的SQL太多了，看不过来，那需要排序了，看持续执行时间最长的那些SQL： /usr/local/mysql/bin/mysql -uroot -ppassword databaseName -e “show full processlist” | grep -v Sleep | sort -k6rn >sort.tmp

如果发现IOWait很高，请查看临时表的生成情况，特别是disk tmp table: /usr/local/mysql/bin/mysql -uroot -ppassword databaseName -e “show global status like ‘%tmp%’”

通过这样一些办法可以查看数据库都在忙什么，那些忙的SQL又具体在哪一个步骤上卡住了，是在创建磁盘临时文件、Sending Data、statistics？依照不同的原因来解决问题

————————————————————— 关于Mysql Replication日常管理，重做，问题分析时常用的办法：

重做Slave，或者Master变化等等，需要将Slave与新的Master同步： change master to master_host=IP,master_user=’replication userName’,master _password=’replication Passwrod’,master_log_file=’log-bin.000001′,master_log_pos=0;

导出数据成SQL文本，慎用，根据你的DB大小会锁表，导致堵塞其他访问： nohup /usr/local/mysql/bin/mysqldump –database DATABASEName -uUserName -pPassWord –lock-all-tables -F >DATA20070519.sql & -F后会刷新Master Log这样配合上面的Change Master可以让Slave进行同步

只导出数据库的结构（没有任何内容） /usr/local/mysql/bin/mysqldump -d DATABASEName -uUserName -pPassWord >DATA20070519.structure

只导出数据库的数据（没有创建表结构的语句等等） /usr/local/mysql/bin/mysqldump -t DATABASEName -uUserName -pPassWord >DATA20070519.data

同步的时候出现问题（或者其他问题）了，根据同步出现问题的位置（偏移量），查看Binlog的具体内容 /usr/local/mysql/bin/mysqlbinlog binlogFileName –start-position=偏移量 呵呵，我们碰到过Master执行的SQL到了Slave会报语法错误，够诡异吧！不过就是这样查到了原因：如果通过存储过程将bit的内容改为1就会出现这样的问题，后来将bit改为tinyint(1)就好了

授权给某一台Slave拥有复制的权限： grant replication slave on . to 用户名@IP identified by ‘密码’;

查看Slave状态： Show slave status \G 查看Master状态： Show master status; 重置Slave（慎用） reset slave; Slave出现问题了，先跳过这一条语句（请确认所要跳过的具体内容不会影响后面的同步，确认方法查看Binlog文件）： set global sql_slave_skip_counter=1; (记得先暂停Slave：stop slave; 然后重启Slave：start slave;)

1.create user username identified by password;//建用户名和密码oracle ,oracle 2.grant connect,resource,dba to username;//授权 grant connect,resource,dba,sysdba to username; 3.connect username/password//进入。 4.select table_name,column_name from user_tab_columns where table_name='mview_log';//查询表中的表名，字段名等等。 5. 如何执行脚本SQL文件? SQL>@PATH/filename.sql; 6.Oracle oledb 提供者在command中执行多条SQL语句与SQL SERVER有少许差别,SQL Server只需使用";"分割多条SQL语句,而Oracle需要遵守ORACLE调用规范,即除分号分割外,还需以begin /end;包围语句体. 使用C#描述应如下所示: this.oleDbCommand1.CommandText = "begin INSERT INTO GROUP_INFO (GROUP_ID, GROUP_NAME) VALUES (1, '2'); INSERT INTO GROUP_INFO(GROUP_ID, GROUP_NAME) VALUES (2, '2'); end;"; 7.查询用户下的所有表 select distinct table_name from user_tab_columns; 8.如何搜索出前N条记录？Select a.,rownum from (select * from cardkind order by cardkind ) a where rownum<n 9.查找用户下的所有表：select * from tab; 2、显示当前连接用户 SQL> show user 3、查看系统拥有哪些用户 SQL> select * from all_users; 4、新建用户并授权 SQL> create user a identified by a;(默认建在SYSTEM表空间下) SQL> grant connect,resource to a; 5、连接到新用户 SQL> conn a/a 6、查询当前用户下所有对象 SQL> select * from tab; 7、建立第一个表 SQL> create table a(a number); 8、查询表结构 SQL> desc a 9、插入新记录 SQL> insert into a values(1); 10、查询记录 SQL> select * from a; 11、更改记录 SQL> update a set a=2; 12、删除记录 SQL> delete from a; 13、回滚 SQL> roll; SQL> rollback; 14、提交 SQL> commit; select * from (select t.,dense_rank() over (order by cardkind) rank from cardkind t) where rank = 2; 46. 如何在字符串里加回车? select 'Welcome to visit'||chr(10)||'www.CSDN.NET' from dual ; 47. 中文是如何排序的？ Oracle9i之前，中文是按照二进制编码进行排序的。 在oracle9i中新增了按照拼音、部首、笔画排序功能。设置NLS_SORT值 SCHINESE_RADICAL_M 按照部首（第一顺序）、笔划（第二顺序）排序 SCHINESE_STROKE_M 按照笔划（第一顺序）、部首（第二顺序）排序 SCHINESE_PINYIN_M 按照拼音排序 48. Oracle8i中对象名可以用中文吗？ 可以 49. 如何改变WIN中SQLPlus启动选项？ SQLPLUS自身的选项设置我们可以在$ORACLE_HOME/sqlplus/admin/glogin.sql中设置。 50. 怎样修改oracel数据库的默认日期? alter session set nls_date_format='yyyymmddhh24miss'; OR 可以在init.ora中加上一行 nls_date_format='yyyymmddhh24miss' 51. 如何将小表放入keep池中? alter table xxx storage(buffer_pool keep); 52. 如何检查是否安装了某个patch? check that oraInventory 53. 如何使select语句使查询结果自动生成序号? select rownum,COL from table; 54. 如何知道数据裤中某个表所在的tablespace? select tablespace_name from user_tables where table_name='TEST'; select * from user_tables中有个字段TABLESPACE_NAME，（oracle）; select * from dba_segments where …; 55. 怎么可以快速做一个和原表一样的备份表? create table new_table as (select * from old_table); 55. 怎么在sqlplus下修改procedure? select line,trim(text) t from user_source where name ='A' order by line; 56. 怎样解除PROCEDURE被意外锁定? alter system kill session ,把那个session给杀掉，不过你要先查出她的session id or 把该过程重新改个名字就可以了。 57. SQL Reference是个什么东西？ 是一本sql的使用手册，包括语法、函数等等，oracle官方网站的文档中心有下载. 58. 如何查看数据库的状态? unix下 ps -ef | grep ora windows下 看服务是否起来 是否可以连上数据库 59. 请问如何修改一张表的主键? alter table aaa drop constraint aaa_key ; alter table aaa add constraint aaa_key primary key(a1,b1) ; 60. 改变数据文件的大小? 用 ALTER DATABASE .... DATAFILE .... ; 手工改变数据文件的大小，对于原来的 数据文件有没有损害。 61. 怎样查看ORACLE中有哪些程序在运行之中？ 查看v$sessions表 62. 怎么可以看到数据库有多少个tablespace? select * from dba_tablespaces; 63. 如何修改oracle数据库的用户连接数？ 修改initSID.ora，将process加大，重启数据库. 64. 如何查出一条记录的最后更新时间? 可以用logminer 察看 65. 如何在PL/SQL中读写文件？ UTL_FILE包允许用户通过PL/SQL读写操作系统文件。 66. 怎样把"&"放入一条记录中？ insert into a values (translate ('at{&}t','at{}','at')); 67. EXP 如何加ＱＵＥＲＹ参数？ EXP USER/PASS FILE=A.DMP TABLES(BSEMPMS) QUERY='"WHERE EMP_NO='S09394'" ﹔ 68. 关于oracle8i支持简体和繁体的字符集问题？ ZHS16GBK可以支 69. Data Guard是什么软件？ 就是Standby的换代产品 70. 如何创建SPFILE? SQL> connect / as sysdba SQL> select * from v$version; SQL> create pfile from spfile; SQL> CREATE SPFILE FROM PFILE='E:\ora9i\admin\eygle\pfile\init.ora'; 文件已创建。 SQL> CREATE SPFILE='E:\ora9i\database\SPFILEEYGLE.ORA' FROM PFILE='E:\ora9i\admin\eygle\pfile\init.ora'; 文件已创建。 71. 内核参数的应用? shmmax 含义：这个设置并不决定究竟Oracle数据库或者操作系统使用多少物理内存，只决定了 最多可以使用的内存数目。这个设置也不影响操作系统的内核资源。 设置方法：0.5物理内存 例子：Set shmsys:shminfo_shmmax=10485760 shmmin 含义：共享内存的最小大小。 设置方法：一般都设置成为1。 例子：Set shmsys:shminfo_shmmin=1： shmmni 含义：系统中共享内存段的最大个数。 例子：Set shmsys:shminfo_shmmni=100 shmseg 含义：每个用户进程可以使用的最多的共享内存段的数目。 例子：Set shmsys:shminfo_shmseg=20： semmni 含义：系统中semaphore identifierer的最大个数。 设置方法：把这个变量的值设置为这个系统上的所有Oracle的实例的init.ora中的最大 的那个processes的那个值加10。 例子：Set semsys:seminfo_semmni=100 semmns 含义：系统中emaphores的最大个数。 设置方法：这个值可以通过以下方式计算得到：各个Oracle实例的initSID.ora里边的 processes的值的总和（除去最大的Processes参数）＋最大的那个Processes×2＋10× Oracle实例的个数。 例子：Set semsys:seminfo_semmns=200 semmsl: 含义：一个set中semaphore的最大个数。 设置方法：设置成为10＋所有Oracle实例的InitSID.ora中最大的Processes的值。 例子：Set semsys:seminfo_semmsl=-200 72. 怎样查看哪些用户拥有SYSDBA、SYSOPER权限？ SQL>conn sys/change_on_install SQL>select * from V_$PWFILE_USERS; 73. 如何单独备份一个或多个表？ exp 用户/密码 tables=(表1,…,表2) 74. 如何单独备份一个或多个用户？ exp system/manager owner=(用户1,用户2,…,用户n) file=导出文件 75. 如何对CLOB字段进行全文检索？ SELECT * FROM A WHERE dbms_lob.instr(a.a,'K',1,1)>0; 76. 如何显示当前连接用户? SHOW USER 77. 如何查看数据文件放置的路径 ? col file_name format a50 SQL> select tablespace_name,file_id,bytes/1024/1024,file_name from dba_data_files order by file_id; 78. 如何查看现有回滚段及其状态 ? SQL> col segment format a30 SQL> SELECT SEGMENT_NAME,OWNER,TABLESPACE_NAME,SEGMENT_ID,FILE _ID,STATUS FROM DBA_ROLLBACK_SEGS 79. 如何改变一个字段初始定义的Check范围？ SQL> alter table xxx drop constraint constraint_name; 之后再创建新约束: SQL> alter table xxx add constraint constraint_name check(); 80. Oracle常用系统文件有哪些？ 通过以下视图显示这些文件信息：v$database,v$datafile,v$logfile v$controlfile v$parameter; 81. 内连接INNER JOIN? Select a. from bsempms a,bsdptms b where a.dpt_no=b.dpt_no; 82. 如何外连接? Select a.* from bsempms a,bsdptms b where a.dpt_no=b.dpt_no(+); Select a.* from bsempms a,bsdptms b wherea.dpt_no(+)=b.dpt_no; 83. 如何执行脚本SQL文件? SQL>@$PATH/filename.sql; 84. 如何快速清空一个大表? SQL>truncate table table_name; 85. 如何查有多少个数据库实例? SQL>SELECT * FROM V$INSTANCE; 86. 如何查询数据库有多少表? SQL>select * from all_tables; 87. 如何测试SQL语句执行所用的时间? SQL>set timing on ; SQL>select * from tablename; 88. CHR()的反函数是? ASCII() SELECT CHAR(65) FROM DUAL; SELECT ASCII('A') FROM DUAL; 89. 字符串的连接 SELECT CONCAT(COL1,COL2) FROM TABLE ; SELECT COL1||COL2 FROM TABLE ; 90. 怎么把select出来的结果导到一个文本文件中？ SQL>SPOOL C:\ABCD.TXT; SQL>select * from table; SQL >spool off; 91. 怎样估算SQL执行的I/O数 ? SQL>SET AUTOTRACE ON ; SQL>SELECT * FROM TABLE; OR SQL>SELECT * FROM v$filestat ; 可以查看IO数 92. 如何在sqlplus下改变字段大小? alter table table_name modify (field_name varchar2(100)); 改大行，改小不行（除非都是空的） 93. 如何查询某天的数据? select * from table_name where trunc(日期字段)＝to_date('2003-05-02','yyyy-mm- dd'); 94. sql 语句如何插入全年日期？ create table BSYEAR (d date); insert into BSYEAR select to_date('20030101','yyyymmdd')+rownum-1 from all_objects where rownum <= to_char(to_date('20031231','yyyymmdd'),'ddd'); 95. 如果修改表名? alter table old_table_name rename to new_table_name; 96. 如何取得命令的返回状态值？ sqlcode=0 97. 如何知道用户拥有的权限? SELECT * FROM dba_sys_privs ; 98. 从网上下载的ORACLE9I与市场上卖的标准版有什么区别？ 从功能上说没有区别，只不过oracle公司有明文规定；从网站上下载的oracle产品不得用于 商业用途，否则侵权。 99. 怎样判断数据库是运行在归档模式下还是运行在非归档模式下？ 进入dbastudio，历程--〉数据库---〉归档查看。 100. sql>startup pfile和ifile,spfiled有什么区别？ pfile就是Oracle传统的初始化参数文件，文本格式的。 ifile类似于c语言里的include，用于把另一个文件引入 spfile是9i里新增的并且是默认的参数文件，二进制格式 startup后应该只可接pfile 101. 如何搜索出前N条记录？ SELECT * FROM empLOYEE WHERE ROWNUM < n ORDER BY empno; 102. 如何知道机器上的Oracle支持多少并发用户数? SQL>conn internal ; SQL>show parameter processes ; 103. db_block_size可以修改吗? 一般不可以﹐不建议这样做的。 104. 如何统计两个表的记录总数? select (select count(id) from aa)+(select count(id) from bb) 总数 from dual; 105. 怎样用Sql语句实现查找一列中第N大值？ select * from (select t.*,dense_rank() over (order by sal) rank from employee) where rank = N; 106. 如何在给现有的日期加上2年？( select add_months(sysdate,24) from dual; 107. USED_UBLK为负值表示什么意思? It is "harmless". 108. Connect string是指什么? 应该是tnsnames.ora中的服务名后面的内容 109. 怎样扩大REDO LOG的大小？ 建立一个临时的redolog组，然后切换日志，删除以前的日志，建立新的日志。 110. tablespace 是否不能大于4G? 没有限制. 111. 返回大于等于N的最小整数值? SELECT CEIL(N) FROM DUAL; 112. 返回小于等于N的最小整数值? SELECT FLOOR(N) FROM DUAL; 113. 返回当前月的最后一天? SELECT LAST_DAY(SYSDATE) FROM DUAL; 114. 如何不同用户间数据导入? IMP SYSTEM/MANAGER FILE=AA.DMP FROMUSER=USER_OLD TOUSER=USER_NEW ROWS=Y INDEXES=Y

115. 如何找数据库表的主键字段的名称? SQL>SELECT * FROM user_constraints WHERE CONSTRAINT_TYPE='P' and table_name='TABLE_NAME';
116. 两个结果集互加的函数? SQL>SELECT * FROM BSEMPMS_OLD INTERSECT SELECT * FROM BSEMPMS_NEW; SQL>SELECT * FROM BSEMPMS_OLD UNION SELECT * FROM BSEMPMS_NEW; SQL>SELECT * FROM BSEMPMS_OLD UNION ALL SELECT * FROM BSEMPMS_NEW;
117. 两个结果集互减的函数? SQL>SELECT * FROM BSEMPMS_OLD MINUS SELECT * FROM BSEMPMS_NEW;
118. 如何配置Sequence? 建sequence seq_custid create sequence seq_custid start 1 incrememt by 1; 建表时: create table cust { cust_id smallint not null, ...} insert 时: insert into table cust values( seq_cust.nextval, ...) 日期的各部分的常用的的写法 119>.取时间点的年份的写法: SELECT TO_CHAR(SYSDATE,'YYYY') FROM DUAL; 120>.取时间点的月份的写法: SELECT TO_CHAR(SYSDATE,'MM') FROM DUAL; 121>.取时间点的日的写法: SELECT TO_CHAR(SYSDATE,'DD') FROM DUAL; 122>.取时间点的时的写法: SELECT TO_CHAR(SYSDATE,'HH24') FROM DUAL; 123>.取时间点的分的写法: SELECT TO_CHAR(SYSDATE,'MI') FROM DUAL; 124>.取时间点的秒的写法: SELECT TO_CHAR(SYSDATE,'SS') FROM DUAL; 125>.取时间点的日期的写法: SELECT TRUNC(SYSDATE) FROM DUAL; 126>.取时间点的时间的写法: SELECT TO_CHAR(SYSDATE,'HH24:MI:SS') FROM DUAL; 127>.日期,时间形态变为字符形态 SELECT TO_CHAR(SYSDATE) FROM DUAL; 128>.将字符串转换成日期或时间形态: SELECT TO_DATE('2003/08/01') FROM DUAL; 129>.返回参数的星期几的写法: SELECT TO_CHAR(SYSDATE,'D') FROM DUAL; 130>.返回参数一年中的第几天的写法: SELECT TO_CHAR(SYSDATE,'DDD') FROM DUAL; 131>.返回午夜和参数中指定的时间值之间的秒数的写法: SELECT TO_CHAR(SYSDATE,'SSSSS') FROM DUAL; 132>.返回参数中一年的第几周的写法: SELECT TO_CHAR(SYSDATE,'WW') FROM DUAL; 虚拟字段
119. CURRVAL 和 nextval 为表创建序列 CREATE SEQUENCE EMPSEQ ... ; SELECT empseq.currval FROM DUAL ; 自动插入序列的数值 INSERT INTO emp VALUES (empseq.nextval, 'LEWIS', 'CLERK', 7902, SYSDATE, 1200, NULL, 20) ;
120. ROWNUM 按设定排序的行的序号 SELECT * FROM emp WHERE ROWNUM < 10 ;
121. ROWID 返回行的物理地址 SELECT ROWID, ename FROM emp WHERE deptno = 20 ;
122. 将N秒转换为时分秒格式？ set serverout on declare N number := 1000000; ret varchar2(100); begin ret := trunc(n/3600) || '小时' || to_char(to_date(mod(n,3600),'sssss'),'fmmi"分 "ss"秒"') ; dbms_output.put_line(ret); end;
123. 如何查询做比较大的排序的进程？ SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#, a.username, a.osuser, a.status FROM v$session a,v$sort_usage b WHERE a.saddr = b.session_addr ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks ;
124. 如何查询做比较大的排序的进程的SQL语句？ select /*+ ORDERED */ sql_text from v$sqltext a where a.hash_value = ( select sql_hash_value from v$session b where b.sid = &sid and b.serial# = &serial) order by piece asc ;
125. 如何查找重复记录？ SELECT * FROM TABLE_NAME WHERE ROWID!=(SELECT MAX(ROWID) FROM TABLE_NAME D WHERE TABLE_NAME.COL1=D.COL1 AND TABLE_NAME.COL2=D.COL2);
126. 如何删除重复记录？ DELETE FROM TABLE_NAME WHERE ROWID!=(SELECT MAX(ROWID) FROM TABLE_NAME D WHERE TABLE_NAME.COL1=D.COL1 AND TABLE_NAME.COL2=D.COL2);
127. 如何快速编译所有视图？ SQL >SPOOL VIEW1.SQL SQL >SELECT 'ALTER VIEW '||TNAME||' COMPILE;' FROM TAB; SQL >SPOOL OFF 然后执行VIEW1.SQL即可。 SQL >@VIEW1.SQL;
128. ORA-01555 SNAPSHOT TOO OLD的解决办法 增加MINEXTENTS的值，增加区的大小，设置一个高的OPTIMAL值。
129. 事务要求的回滚段空间不够，表现为表空间用满（ORA-01560错误），回滚段扩展到达 参数 MAXEXTENTS的值（ORA-01628）的解决办法. 向回滚段表空间添加文件或使已有的文件变大；增加MAXEXTENTS的值。
130. 如何加密ORACLE的存储过程？ 下列存储过程内容放在AA.SQL文件中 create or replace procedure testCCB(i in number) as begin dbms_output.put_line('输入参数是'||to_char(i)); end; SQL>wrap iname=a.sql; PL/SQL Wrapper: Release 8.1.7.0.0 - Production on Tue Nov 27 22:26:48 2001 Copyright (c) Oracle Corporation 1993, 2000. All Rights Reserved. Processing AA.sql to AA.plb 运行AA.plb SQL> @AA.plb ;
131. 如何监控事例的等待？ select event,sum(decode(wait_Time,0,0,1)) "Prev", sum(decode(wait_Time,0,1,0)) "Curr",count(*) "Tot" from v$session_Wait group by event order by 4;
132. 如何回滚段的争用情况？ select name, waits, gets, waits/gets "Ratio" from v$rollstat C, v$rollname D where C.usn = D.usn;
133. 如何监控表空间的 I/O 比例？ select B.tablespace_name name,B.file_name "file",A.phyrds pyr, A.phyblkrd pbr,A.phywrts pyw, A.phyblkwrt pbw from v$filestat A, dba_data_files B where A.file# = B.file_id order by B.tablespace_name;
134. 如何监控文件系统的 I/O 比例？ select substr(C.file#,1,2) "#", substr(C.name,1,30) "Name", C.status, C.bytes, D.phyrds, D.phywrts from v$datafile C, v$filestat D where C.file# = D.file#;
135. 如何在某个用户下找所有的索引？ select user_indexes.table_name, user_indexes.index_name,uniqueness, column_name from user_ind_columns, user_indexes where user_ind_columns.index_name = user_indexes.index_name and user_ind_columns.table_name = user_indexes.table_name order by user_indexes.table_type, user_indexes.table_name, user_indexes.index_name, column_position;
136. 如何监控 SGA 的命中率？ select a.value + b.value "logical_reads", c.value "phys_reads", round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) "BUFFER HIT RATIO" from v$sysstat a, v$sysstat b, v$sysstat c where a.statistic# = 38 and b.statistic# = 39 and c.statistic# = 40;
137. 如何监控 SGA 中字典缓冲区的命中率？ select parameter, gets,Getmisses , getmisses/(gets+getmisses)*100 "miss ratio", (1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 "Hit ratio" from v$rowcache where gets+getmisses <>0 group by parameter, gets, getmisses;
138. 如何监控 SGA 中共享缓存区的命中率，应该小于1% ？ select sum(pins) "Total Pins", sum(reloads) "Total Reloads", sum(reloads)/sum(pins) *100 libcache from v$librarycache; select sum(pinhits-reloads)/sum(pins) "hit radio",sum(reloads)/sum(pins) "reload percent" from v$librarycache;
139. 如何显示所有数据库对象的类别和大小？ select count(name) num_instances ,type ,sum(source_size) source_size , sum(parsed_size) parsed_size ,sum(code_size) code_size ,sum(error_size) error_size, sum(source_size) +sum(parsed_size) +sum(code_size) +sum(error_size) size_required from dba_object_size group by type order by 2;
140. 监控 SGA 中重做日志缓存区的命中率，应该小于1% SELECT name, gets, misses, immediate_gets, immediate_misses, Decode(gets,0,0,misses/gets*100) ratio1, Decode(immediate_gets+immediate_misses,0,0, immediate_misses/(immediate_gets+immediate_misses) *100) ratio2 FROM v$latch WHERE name IN ('redo allocation', 'redo copy');
141. 监控内存和硬盘的排序比率，最好使它小于 .10，增加 sort_area_size SELECT name, value FROM v$sysstat WHERE name IN ('sorts (memory)', 'sorts (disk)');
142. 如何监控当前数据库谁在运行什么SQL语句？ SELECT osuser, username, sql_text from v$session a, v$sqltext b where a.sql_address =b.address order by address, piece;
143. 如何监控字典缓冲区？ SELECT (SUM(PINS - RELOADS)) / SUM(PINS) "LIB CACHE" FROM V$LIBRARYCACHE; SELECT (SUM(GETS - GETMISSES - USAGE - FIXED)) / SUM(GETS) "ROW CACHE" FROM V$ROWCACHE; SELECT SUM(PINS) "EXECUTIONS", SUM(RELOADS) "CACHE MISSES WHILE EXECUTING" FROM V$LIBRARYCACHE; 后者除以前者,此比率小于1%,接近0%为好。 SELECT SUM(GETS) "DICTIONARY GETS",SUM(GETMISSES) "DICTIONARY CACHE GET MISSES" FROM V$ROWCACHE
144. 监控 MTS select busy/(busy+idle) "shared servers busy" from v$dispatcher; 此值大于0.5时，参数需加大 select sum(wait)/sum(totalq) "dispatcher waits" from v$queue where type='dispatcher'; select count(*) from v$dispatcher; select servers_highwater from v$mts; servers_highwater接近mts_max_servers时，参数需加大
145. 如何知道当前用户的ID号? SQL>SHOW USER; OR SQL>select user from dual;
146. 如何查看碎片程度高的表? SELECT segment_name table_name , COUNT() extents FROM dba_segments WHERE owner NOT IN ('SYS', 'SYSTEM') GROUP BY segment_name HAVING COUNT() = (SELECT MAX( COUNT(*) ) FROM dba_segments GROUP BY segment_name);
147. 如何知道表在表空间中的存储情况? select segment_name,sum(bytes),count(*) ext_quan from dba_extents where tablespace_name='&tablespace_name' and segment_type='TABLE' group by tablespace_name,segment_name;
148. 如何知道索引在表空间中的存储情况? select segment_name,count(*) from dba_extents where segment_type='INDEX' and owner='&owner' group by segment_name; 164、如何知道使用CPU多的用户session? 11是cpu used by this session select a.sid,spid,status,substr(a.program,1,40) prog,a.terminal,osuser,value/60/100 value from v$session a,v$process b,v$sesstat c where c.statistic#=11 and c.sid=a.sid and a.paddr=b.addr order by value desc;
149. 如何知道监听器日志文件? 以8I为例 $ORACLE_HOME/NETWORK/LOG/LISTENER.LOG
150. 如何知道监听器参数文件? 以8I为例 $ORACLE_HOME/NETWORK/ADMIN/LISTENER.ORA
151. 如何知道TNS 连接文件? 以8I为例 $ORACLE_HOME/NETWORK/ADMIN/TNSNAMES.ORA
152. 如何知道Sql*Net 环境文件? 以8I为例 $ORACLE_HOME/NETWORK/ADMIN/SQLNET.ORA
153. 如何知道警告日志文件? 以8I为例 $ORACLE_HOME/ADMIN/SID/BDUMP/SIDALRT.LOG
154. 如何知道基本结构? 以8I为例 $ORACLE_HOME/RDBMS/ADMIN/STANDARD.SQL
155. 如何知道建立数据字典视图? 以8I为例 $ORACLE_HOME/RDBMS/ADMIN/CATALOG.SQL
156. 如何知道建立审计用数据字典视图? 以8I为例 $ORACLE_HOME/RDBMS/ADMIN/CATAUDIT.SQL
157. 如何知道建立快照用数据字典视图? 以8I为例 $ORACLE_HOME/RDBMS/ADMIN/CATSNAP.SQL 本讲主要讲的是SQL语句的优化方法! 主要基于ORACLE9I的.
158. /+ALL_ROWS/ 表明对语句块选择基于开销的优化方法,并获得最佳吞吐量,使资源消耗最小化. 例如: SELECT /+ALL+_ROWS/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='CCBZZP';
159. /+FIRST_ROWS/ 表明对语句块选择基于开销的优化方法,并获得最佳响应时间,使资源消耗最小化. 例如: SELECT /+FIRST_ROWS/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='CCBZZP'; 176. /+CHOOSE/ 表明如果数据字典中有访问表的统计信息,将基于开销的优化方法,并获得最佳的吞吐量; 表明如果数据字典中没有访问表的统计信息,将基于规则开销的优化方法; 例如: SELECT /+CHOOSE/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='CCBZZP';
160. /+RULE/ 表明对语句块选择基于规则的优化方法. 例如: SELECT /*+ RULE */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='CCBZZP';
161. /+FULL(TABLE)/ 表明对表选择全局扫描的方法. 例如: SELECT /+FULL(A)/ EMP_NO,EMP_NAM FROM BSEMPMS A WHERE EMP_NO='CCBZZP';
162. /+ROWID(TABLE)/ 提示明确表明对指定表根据ROWID进行访问. 例如: SELECT /+ROWID(BSEMPMS)/ * FROM BSEMPMS WHERE ROWID>='AAAAAAAAAAAAAA' AND EMP_NO='CCBZZP';
163. /+CLUSTER(TABLE)/ 提示明确表明对指定表选择簇扫描的访问方法,它只对簇对象有效. 例如: SELECT /*+CLUSTER */ BSEMPMS.EMP_NO,DPT_NO FROM BSEMPMS,BSDPTMS WHERE DPT_NO='TEC304' AND BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
164. /+INDEX(TABLE INDEX_NAME)/ 表明对表选择索引的扫描方法. 例如: SELECT /*+INDEX(BSEMPMS SEX_INDEX) USE SEX_INDEX BECAUSE THERE ARE FEWMALE BSEMPMS */ FROM BSEMPMS WHERE SEX='M';
165. /+INDEX_ASC(TABLE INDEX_NAME)/

【Oracle数据库DBA必备基本技能】

1）首先知道控制文件，数据文件，以及日志文件的所在目录以及表空间相关使用情况，会优化Top级别SQL语句 select * from v$logfile select * from v$log select * from v$datafile select * from v$controlfile

2）知道警告日志文件的路径 show parameter background_dump_dest ; 或者 select * from v$parameter where name like '%background_dump_dest%'

3）知道数据库是否归档以及数据库名字 select * from v$database

4）了解SGA分配 select * from v$parameter where name like '%sga%'

5）观察系统性能以及相应的等待事件

6）查看缓冲池的命中率 select sum(pinhits)/sum(pins) * 100 from v$librarycache

7）查看排序部分（磁盘排序和内存排序） select * from v$sysstat where name like '%sort%'

8）等待事件 select * from v$session_wait select * from v$session_event select * from v$system_event

9）事物回退率 select * from v$sysstat where name like '%user commits%' or name like '%transaction rollbacks%' select * from dba_rollback_segs select * from v$waitstat select * from v$transaction

10）数据文件损坏或者丢失如何恢复

11）日志文件损坏或者丢失如何恢复 alter system switch logfile

12）控制文件损坏如何恢复

13)Rman备份