oraclesql疑难解析
1. 如何分析Oracle
以oracle表分析为例：
drop table test;
select count(*) from test;
--创建测试表
create table test
(
id number(9),
nick varchar2(30)
);
--插入测试数据
begin
for i in 1..100000 loop
insert into test(id) values(i);
end loop;
commit;
end;
select * from test;
--更新nick字段，使数据发生严重倾斜
update test set nick='abc' where rownum<99999;
--创建索引
create index idx_test_nick on test(nick);
update test set nick='def' where nick is null;
--只对索引誉毁进行分析
analyze index idx_test_nick compute statistics;
select * from user_indexes;
--查看索引名,对应存储的数据块,不同的key数量,记录数（行数）的分析信息
select index_name, LEAF_BLOCKS, DISTINCT_KEYS, NUM_ROWS
from user_indexes
where index_name = 'IDX_TEST_NICK';
--dba_tab_col_statistics
--查看表的统计信息
select COLUMN_NAME, NUM_BUCKETS, num_distinct
from USER_tab_columns
where table_name = 'TEST';
select * from test where nick ='abc';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST'
2 1 INDEX (RANGE SCAN) OF 'IDX_TEST_NICK' (NON-UNIQUE)
select * from test where nick ='def';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST'
2 1 INDEX (RANGE SCAN) OF 'IDX_TEST_NICK' (NON-UNIQUE)
--由上可以看到,对索引分析之后,sql的执行路径都是基于规则的,索引的庆孙备字段的偏移
--先根据索引找到rowid,然后再根据rowid读取记录,这个过程肯定比全表扫描读取记录要慢
--user_part_col_statistics 分区分析信息
--分析表的第二列nick
analyze table test compute statistics for columns size 2 nick;
select * from test where nick ='abc';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST'
2 1 INDEX (RANGE SCAN) OF 'IDX_TEST_NICK' (NON-UNIQUE)
--根据上面的执行计划，还是按照规则来执行的凯笑
--分析表
analyze table test compute statistics for table;
select * from test where nick ='abc';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=49 Card=99998 Bytes=
1499970)
1 0 TABLE ACCESS (FULL) OF 'TEST' (Cost=49 Card=99998 Bytes=14
99970)
--分析表之后，完全按照成本来执行
--删除所有的统计数据，并只对表与列进行分析，不分析索引，
--ORACLE使用CBO的优化器，并产生了正确的执行计划
analyze table test delete statistics;
--分析列nick
analyze table test compute statistics for table for columns size 2 nick;
select * from test where nick ='abc';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=49 Card=99998 Bytes=
1499970)
1 0 TABLE ACCESS (FULL) OF 'TEST' (Cost=49 Card=99998 Bytes=14
99970)
--
select * from test where nick ='def';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=2 Bytes=30)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=2 Byt
es=30)
2 1 INDEX (RANGE SCAN) OF 'IDX_TEST_NICK' (NON-UNIQUE) (Cost
=1 Card=2)
--创建TEST表ID列上的索引，但不对索引进行分析
create index idx_test_id on test(id);
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1000 Bytes=15
000)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=1000
Bytes=15000)
2 1 INDEX (RANGE SCAN) OF 'IDX_TEST_ID' (NON-UNIQUE) (Cost=1
Card=400)
--当条件中即有id，又有nick时，因为nick上有直方图,ORACLE知道nick='abc'的值特别的多，所以不走IDX_TEST_NICK索引,走IDX_TEST_ID上的索引
select * from test where id=5 and nick='abc';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1000 Bytes=15
000)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=1000
Bytes=15000)
2 1 INDEX (RANGE SCAN) OF 'IDX_TEST_ID' (NON-UNIQUE) (Cost=1
Card=400)
--当条件中即有id，又有nick时，因为nick上有直方图,ORACLE知道nick='def'的值特别的少，所以走IDX_TEST_NICK上的索引,不走IDX_TEST_ID索引
select * from test where id=5 and nick='def';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=15)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=1 Byt
es=15)
2 1 INDEX (RANGE SCAN) OF 'IDX_TEST_NICK' (NON-UNIQUE) (Cost
=1 Card=2)
select * from test where nick='def' and id=5;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=15)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=1 Byt
es=15)
2 1 INDEX (RANGE SCAN) OF 'IDX_TEST_NICK' (NON-UNIQUE) (Cost
=1 Card=2)
--在分析ID列后，ORACLE发现ID列的选择度更高，所以不再选择IDX_TEST_NICK索引，而是选择IDX_TEST_ID
analyze table test compute statistics for columns size 1 id;
select * from test where id=5 and nick='def';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=7)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=1 Byt
es=7)
2 1 INDEX (RANGE SCAN) OF 'IDX_TEST_ID' (NON-UNIQUE) (Cost=1
Card=1)
/*
下面来看另外一种情况，我们删除所有的统计数据，然后在ID列上创建唯一索引,在此条件下，
只分析表与分析列nick,我们看到ORACLE走了正确的执行计划，
走了UK_TEST_ID,其实从这里也给我们带来很多的启示：
在主键与唯一键约束的列上是否需要直方图的问题?
如果在这些列上有像这样的查询where id > 100 and id < 1000,
我们还是需要有直方图的，但除此之外，好像真的没有直方图的必要了!
*/
analyze table test delete statistics;
drop index idx_test_id;
create unique index uk_test_id on test(id);
--分析表的第二列nick
analyze table test compute statistics for table for columns size 2 nick;
select * from test where id=5 and nick='def';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=15)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=1 Byt
es=15)
2 1 INDEX (UNIQUE SCAN) OF 'UK_TEST_ID' (UNIQUE) (Cost=1 Car
d=100000)
从以上一系列的实验可以看出，对ORACLE的优化器CBO来说，表的分析与列的分析才是最重要的，索引的分析次之。还有我们可以考虑我们的哪些列上需要直方图，对于bucket的个数问题,oracle的默认值是75个，所以根据你的应用规则，选择合适的桶数对性能也是有帮助的。因为不必要的桶的个数的大量增加，必然会带来SQL语句硬解析时产生执行计划的复杂度问题。
2. [SQL][oracle疑难问题ORA-00031]：标记要终止的会话 解决方法
问题场景：在操作大数据量的时候，会出现种种问题导致数据不正确导致需要重新处理数据，如果这个时候删除表数据会无法删除成功，然后手动终止会话可能会提示“标记要终止的会话”，这是因为手动终止会话后，进程的状态被设置为“killed”,但是锁定的资源很长时间没有被释放，那么可以在OS级再杀死相应的进程（线程），详细操作如下：
1，查询临时会话的session：
--查找sql执行的会话session id, 用session id 这一栏的数据进行kill掉会话
SELECT B.SID,
B.OBJECT,
A.SQL_ID,
A.LOGON_TIME,
A.SQL_EXEC_START,
A.MODULE,
C.SQL_TEXT,
C.SQL_FULLTEXT,
薯肢 A.OSUSER,
A.EVENT#,
A.EVENT,
A.INST_ID,
A.ACTION,
A.PROCESS,
A.STATUS,
'alter system kill session ''' || a.sid || ', ' || a.serial# ||
', @' || a.inst_id || ''' IMMEDIATE;' kill_session,
A.*
FROM GV$SESSION A, GV$ACCESS B,GV$SQL C
WHERE A.SID = B.SID
AND A.INST_ID = B.INST_ID
AND A.SQL_ID = C.SQL_ID(+)
AND A.INST_ID = C.INST_ID(+)
--AND A.TYPE <> 'BACKGROUND'
--AND A.STATUS = 'ACTIVE'
AND B.OWNER = 'BI_DM'
--AND A.INST_ID =1
--AND A.ACTION IN ('ORA$AT_SA_SPC_SY_4630','ORA$AT_OS_OPT_SY_4629')
AND B.OBJECT = 'TMP_T_JOBNAME'
--AND A.USERNAME LIKE 'DEV%'
--AND A.MACHINE IN (/*'infor',*/'winitdb')
ORDER BY A.SQL_EXEC_START;
2，执行session id 里面是语句
alter system kill session '2761, 65372, @2' IMMEDIATE;
在kill session的时候，当提示RA-00031：标记要终止的会话 时
按如下操作：
1，select a.spid,b.sid,b.serial#,b.username 悔手掘from v$process a,v$session b where a.addr=b.paddr and b.status='KILLED';
2，如果利用上面的命令杀死一个进程后，进程状态被置为"killed"，但是锁定的资源很长时间没有被释放，那么可以在OS级再杀死相应的进程（线程），首先执行下面的语句获得进碧核程（线程）号：
select b.spid,a.osuser,b.program from v$session a,v$process b where a.paddr=b.addr and a.sid=9065 --9065就是上面的sid
.在OS上杀死这个进程（线程）
1)、在unix上，用root身份执行命令：#kill -9 9065（即第2步查询出的spid）
2)、在windows（unix也适用）用orakill杀死线程，orakill是oracle提供的一个可执行命令，语法为：orakill sid thread
其中：
sid：表示要杀死的进程属于的实例名 。可以用select name from v$database;查询
thread：是要杀掉的线程号，即第2步查询出的spid。
例：c:>orakill system 9065
注意：这里要注意的是kill OS进程是在服务端操作，而不是你程序所在客户机
3. oracle sql是怎么解析的
导读：Oracle的后台运作原理是什么？我们的一条命令是如何被执行的？今天我们就从一条简单的Select语句开始，看看Oracle数据库后台的运作机制。
Select语句可以说是DBA和数据库开发者在工作中使用最多的语句之一，但这条语句是如何执行？在Oracle数据库中又是如何运作的呢?今天我们就从一条简单的Select语句开始，看看Oracle数据库后台的运作机制。这对于我们之后的系统管理与故障排除非常有帮助。
第一步：客户端把语句发给服务器端执行
当我们在客户端执行select语句时，客户端会把这条SQL语句发送给服务器端，让服务器端的进程来处理这语句。也就是说，Oracle客户端是不会做任何的操作，他的主要任务就是把客户端产生的一些SQL语句发送给服务器端。虽然在客户端也有一个数据库进程，但是，这个进程的作用跟服务器上的进程作用事不相同的。服务器上的数据库进程才会对SQL语句进行相关的处理。不过，有个问题需要说明，就是客户端的进程跟服务器的进程是一一对应的。也就是说，在客户端连接上服务器后，在客户端与服务器端都会形成一个进程，客户端上的我们叫做客户端进程;而服务器上的我们叫做服务器进程。所以，由于所有的SQL语句都是服务器进程执行的，所以，有些人把服务器进程形象地比喻成客户端进程的“影子”。
第二步：语句解析
当客户端把SQL语句传送到服务器后，服务器进程会对该语句进行解析。同理，这个解析的工作，也是在服务器端所进行的。虽然这只是一个解析的动作，但是，其会做很多“小动作”。
1. 查询高速缓存。服务器进程在接到客户端传送过来的SQL语句时，不会直接去数据库查询。而是会先在数据库的高速缓存中去查找，是否存在相同语句的执行计划。如果在数据高速缓存中，刚好有其他人使用这个查询语句的话，则服务器进程就会直接执行这个SQL语句，省去后续的工作。所以，采用高速数据缓存的话，可以提高SQL语句的查询效率。一方面是从内存中读取数据要比从硬盘中的数据文件中读取数据效率要高，另一方面，也是因为这个语句解析的原因。
不过这里要注意一点，这个数据缓存跟有些客户端软件的数据缓存是两码事。有些客户端软件为了提高查询效率，会在应用软件的客户端设置数据缓存。由于这些数据缓存的存在，可以提高客户端应用软件的查询效率。但是，若其他人在服务器进行了相关的修改，由于应用软件数据缓存的存在，导致修改的数据不能及时反映到客户端上。从这也可以看出，应用软件的数据缓存跟数据库服务器的高速数据缓存不是一码事。
2. 语句合法性检查。当在高速缓存中找不到对应的SQL语句时，则数据库服务器进程就会开始检查这条语句的合法性。这里主要是对SQL语句的语法进行检查，看看其是否合乎语法规则。如果服务器进程认为这条SQL语句不符合语法规则的时候，就会把这个错误信息，反馈给客户端。在这个语法检查的过程中，不会对SQL语句中所包含的表名、列名等等进行SQL他只是语法上的检查。
3. 语言含义检查。若SQL语句符合语法上的定义的话，则服务器进程接下去会对语句中的字段、表等内容进行检查。看看这些字段、表是否在数据库中。如果表名与列名不准确的话，则数据库会就会反馈错误信息给客户端。
所以，有时候我们写select语句的时候，若语法与表名或者列名同时写错的话，则系统是先提示说语法错误，等到语法完全正确后，再提示说列名或表名错误。若能够掌握这个顺序的话，则在应用程序排错的时候，可以节省时间。
4. 获得对象解析锁。当语法、语义都正确后，系统就会对我们需要查询的对象加锁。这主要是为了保障数据的一致性，防止我们在查询的过程中，其他用户对这个对象的结构发生改变。对于加锁的原理与方法，我在其他文章中已经有专门叙述，在这里就略过不谈了。
5. 数据访问权限的核对。当语法、语义通过检查之后，客户端还不一定能够取得数据。服务器进程还会检查，你所连接的用户是否有这个数据访问的权限。若你连接上服务器的用户不具有数据访问权限的话，则客户端就不能够取得这些数据。故，有时候我们查询数据的时候，辛辛苦苦地把SQL语句写好、编译通过，但是，最后系统返回个 “没有权限访问数据”的错误信息，让我们气半死。这在前端应用软件开发调试的过程中，可能会碰到。所以，要注意这个问题，数据库服务器进程先检查语法与语义，然后才会检查访问权限。
6. 确定最佳执行计划。当语句与语法都没有问题，权限也匹配的话，服务器进程还是不会直接对数据库文件进行查询。服务器进程会根据一定的规则，对这条语句进行优化。不过要注意，这个优化是有限的。一般在应用软件开发的过程中，需要对数据库的sql语言进行优化，这个优化的作用要大大地大于服务器进程的自我优化。所以，一般在应用软件开发的时候，数据库的优化是少不了的。
当服务器进程的优化器确定这条查询语句的最佳执行计划后，就会将这条SQL语句与执行计划保存到数据高速缓存。如此的话，等以后还有这个查询时，就会省略以上的语法、语义与权限检查的步骤，而直接执行SQL语句，提高SQL语句处理效率。
第三步：语句执行
语句解析只是对SQL语句的语法进行解析，以确保服务器能够知道这条语句到底表达的是什么意思。等到语句解析完成之后，数据库服务器进程才会真正的执行这条SQL语句。
这个语句执行也分两种情况。一是若被选择行所在的数据块已经被读取到数据缓冲区的话，则服务器进程会直接把这个数据传递给客户端，而不是从数据库文件中去查询数据。若数据不在缓冲区中，则服务器进程将从数据库文件中查询相关数据，并把这些数据放入到数据缓冲区中。
这里仍然要注意一点，就是Oracle数据库中，定义了很多种类的高速缓存。像上面所说的SQL语句缓存与现在讲的数据缓存。我们在学习数据库的时候，需要对这些缓存有一个清晰的认识，并了解各个种类缓存的作用。这对于我们后续数据库维护与数据库优化是非常有用的。
第四步：提取数据
当语句执行完成之后，查询到的数据还是在服务器进程中，还没有被传送到客户端的用户进程。所以，在服务器端的进程中，有一个专门负责数据提取的一段代码。他的作用就是把查询到的数据结果返回给用户端进程，从而完成整个查询动作。
从这整个查询处理过程中，我们在数据库开发或者应用软件开发过程中，需要注意以下几点：
一是要了解数据库缓存跟应用软件缓存是两码事情。数据库缓存只有在数据库服务器端才存在，在客户端是不存在的。只有如此，才能够保证数据库缓存中的内容跟数据库文件的内容一致。才能够根据相关的规则，防止数据脏读、错读的发生。而应用软件所涉及的数据缓存，由于跟数据库缓存不是一码事情，所以，应用软件的数据缓存虽然可以提高数据的查询效率，但是，却打破了数据一致性的要求，有时候会发生脏读、错读等情况的发生。所以，有时候，在应用软件上有专门一个功能，用来在必要的时候清除数据缓存。不过，这个数据缓存的清除，也只是清除本机上的数据缓存，或者说，只是清除这个应用程序的数据缓存，而不会清除数据库的数据缓存。
二是绝大部分SQL语句都是按照这个处理过程处理的。我们DBA或者基于Oracle数据库的开发人员了解这些语句的处理过程，对于我们进行涉及到SQL语句的开发与调试，是非常有帮助的。有时候，掌握这些处理原则，可以减少我们排错的时间。特别要注意，数据库是把数据查询权限的审查放在语法语义的后面进行检查的。所以，有时会若光用数据库的权限控制原则，可能还不能满足应用软件权限控制的需要。此时，就需要应用软件的前台设置，实现权限管理的要求。而且，有时应用数据库的权限管理，也有点显得繁琐，会增加服务器处理的工作量。因此，对于记录、字段等的查询权限控制，大部分程序涉及人员喜欢在应用程序中实现，而不是在数据库上实现。
4. Sql语句解析过程
为了将用户写的SQL文本转化为Oracle认识的且可执行的语句 这个过程就叫做解析过程 解析分为硬解析和软解析 一条SQL语句在第一次被执行时必须进行硬解析
当客户端发出一条SQL语句（也可以是一个存储过程或者一个匿名PL/SQL块）进入shared pool时（注意 我们从前面已经知道 Oracle对这些SQL不叫做SQL语句 而是称为游标 因为Oracle在处理SQL时 需要很多相关的辅助信息 这些辅助信息与SQL语句一起组成了游标） Oracle首先将SQL文本转化为ASCII值 然后根据hash函数计算其对应的hash值（hash_value） 根据计算出的hash值到library cache中找到对应的bucket 然后比较bucket里是否存在该SQL语句
如果不存在 则需要按照我们前面所描述的 获得shared pool latch 然后在shared pool中的可用chunk链表（也就是bucket）上找到一个可用的chunk 之后释放shared pool latch 在获得了chunk以后 这块chunk就可以认为是进入了library cache 接下来 进行硬解析过程 硬解析包括以下几个步骤
对SQL语句进行文法检查 看是否有文法错误 比如没有写from select拼写错误等 如果存在文法错误 则退出解析过程
到数据字典里校验SQL语句涉及的对象和列是否都存在 如果不存在 则退出解析过程 这个过程会加载dictionary cache
将对象进行名称转换 比如将同名词翻译成实际的对象等 比如select * from t中 t是一个同名词 指向hr t 于是Oracle将t转换为hr t 如果转换失败 则退出解析过程
检查发出SQL语句的用户是否伏侍简具有访问SQL语句里所引用的对象的权限 如果没有权限 则退出解析过程
通过优化器创建一个最优的执行计划 这个过程会根据数据字典里记录的对象的统计信息 来计算最优的执行计划 这一步牵涉大量数谈碧学运算 是最消耗CPU资源的
将该游标所产生的执行计划 SQL文本等装载进library cache的heap中
在硬解析的过程中 进程会一直持有library cache latch 直到硬解析结束为止 硬解析结束以后 会为SQL语句产生两个游标 一个是父游标 另一个是子游标 父游标里主要包含两种信息 SQL文本以及优化目标（optimizer goal） 父游标在第一次打开时被锁定 直到其他所有的session都关闭该游标后才被解锁 当父游标缺裤被锁定的时候是不能被交换出library cache的 只有在解锁以后才能被交换出library cache 父游标被交换出内存时 父游标对应的所有子游标也被交换出library cache 子游标包括游标所有的信息 比如具体的执行计划 绑定变量等 子游标随时可以被交换出library cache 当子游标被交换出library cache时 Oracle可以利用父游标的信息重新构建出一个子游标来 这个过程叫reload 可以使用下面的方式来确定reload的比率
select *sum(reloads)/sum(pins) Reload_Ratio from v$librarycache;
一个父游标可以对应多个子游标 子游标具体的个数可以从视图v$sqlarea的version_count字段体现出来 而每个具体的子游标则全都在视图v$sql里体现 当具体绑定变量的值与上次绑定变量的值有较大差异（比如上次执行的绑定变量值的长度是 位 而这次执行绑定变量的值的长度是 位）时或者当SQL语句完全相同 但是所引用的表属于不同的用户时 都会创建一个新的子游标
如果在bucket中找到了该SQL语句 则说明该SQL语句以前运行过 于是进行软解析 软解析是相对于硬解析而言的 如果解析过程中 可以从硬解析的步骤中去掉一个或多个的话 这样的解析就是软解析 软解析分为以下三种类型
第一种是某个session发出的SQL语句与library? cache里其他session发出的SQL语句一致 这时 该解析过程中可以去掉硬解析中的 和 但是仍然要进行硬解析过程中的 也就是表名和列名检查 名称转换和权限检查
* 第二种是某个session发出的SQL语句是该session之前发出的曾经执行过的SQL语句 这时 该解析过程中可以去掉硬解析中的 和 这四步 但是仍然要进行权限检查 因为可能通过grant改变了该session用户的权限
* 第三种是当设置了初始化参数session_cached_cursors时 当某个session第三次执行相同的SQL时 则会把该SQL语句的游标信息转移到该session的PGA里 这样 该session以后再执行相同的SQL语句时 会直接从PGA里取出执行计划 从而跳过硬解析的所有步骤 这种情况下 是最高效的解析方式 但是会消耗很大的内存
我们举一个例子来说明解析SQL语句的过程 在该测试中 绑定变量名称相同 但是变量类型不同时 所出现的解析情况 如下所示
首先 执行下面的命令 清空shared pool里所有的SQL语句
SQL> alter system flush shared_pool;
然后 定义一个数值型绑定变量 并为该绑定变数赋一个数值型的值以后 执行具体的查询语句
SQL> variable v_obj_id number;
SQL> exec :v_obj_id := ;
SQL> select object_id object_name from sharedpool_test
where object_id=:v_obj_id;
OBJECT_ID OBJECT_NAME
AGGXMLIMP
接下来 定义一个字符型的绑定变量 变量名与前面相同 为该绑定变数赋一个字符型的值以后 执行相同的查询
SQL> variable v_obj_id varchar ( );
SQL> exec :v_obj_id := ;
SQL> select object_id object_name from sharedpool_test
where object_id=:v_obj_id;
OBJECT_ID OBJECT_NAME
AGGXMLIMP
然后我们到视图v$sqlarea里找到该SQL的父游标的信息 并到视图v$sql里找该SQL的所有子游标的信息
SQL> select sql_text version_count from v$sqlarea where
sql_text like %sharedpool_test% ;
SQL_TEXT
VERSION_COUNT
select object_id object_name from sharedpool_test where
object_id=:v_obj_id
SQL> select sql_text child_address address from v$sql
where sql_text like %sharedpool_test% ;
SQL_TEXT
CHILD_ADDRESS ADDRESS
select object_id object_name from sharedpool_test where
object_id=:v_obj_id F
B D
select object_id object_name from sharedpool_test where
object_id=:v_obj_id FC
B D
从记录父游标的视图v$sqlarea的version_count列可以看到 该SQL语句有 个子游标 而从记录子游标的视图v$sql里可以看到 该SQL文本确实有两条记录 而且它们的SQL文本所处的地址（ADDRESS列）也是一样的 但是子地址（CHILD_ADDRESS）却不一样 这里的子地址实际就是子游标所对应的heap 的句柄
lishixin/Article/program/Oracle/201311/18653
5. OracleSQL精妙SQL语句讲解
行列转换 行转列
DROP TABLE t_change_lc;
CREATE TABLE t_change_lc (card_code VARCHAR ( ) q NUMBER bal NUMBER);
INSERT INTO t_change_lc
SELECT card_code ROWNUM q trunc(dbms_random VALUE * ) bal FROM al CONNECT BY ROWNUM <=
UNION
SELECT card_code ROWNUM q trunc(dbms_random VALUE * ) bal FROM al CONNECT BY ROWNUM <= ;
SELECT * FROM t_change_lc;
SELECT a card_code
SUM(decode(a q a bal )) q
SUM(decode(a q a bal )) q
SUM(decode(a q a bal )) q
SUM(decode(a q a bal )) q
FROM t_change_lc a
GROUP BY a card_code
ORDER BY ;
行列转换 列转行
DROP TABLE t_change_cl;
CREATE TABLE t_change_cl AS
SELECT a card_code
SUM(decode(a q a bal )) q
SUM(decode(a q a bal )) q
SUM(decode(a q a bal )) q
SUM(decode(a q a bal )) q
FROM t_change_lc a
GROUP BY a card_code
ORDER BY ;
SELECT * FROM t_change_cl;
SELECT t card_code
t rn q
decode(t rn t q t q t q t q ) bal
FROM (SELECT a * b rn
FROM t_change_cl a
(SELECT ROWNUM rn FROM al CONNECT BY ROWNUM <= ) b) t
ORDER BY ;
行列转换 行转列 合并
DROP TABLE t_change_lc_ma;
核局陪CREATE TABLE t_change_lc_ma AS SELECT card_code quarter_ ||q AS q FROM t_change_lc;
SELECT * FROM t_change_lc_ma;
SELECT t card_code substr(MAX(sys_connect_by_path(t q ; )) ) q
FROM (SELECT a card_code
a q
row_number() over(PARTITION BY a card_code ORDER BY a q) rn
FROM t_change_lc_ma a) t
START WITH t rn =
CONNECT BY t card_code = PRIOR t card_code
AND t rn = PRIOR t rn
GROUP BY t card_code;
行列转换 列转行 分割
DROP TABLE t_change_cl_ma;
CREATE TABLE t_change_cl_ma AS
SELECT t card_code substr(MAX(sys_connect_by_path(t q ; )) ) q
FROM (SELECT a card_code
a q
腊历改蠢row_number() over(PARTITION BY a card_code ORDER BY a q) rn
FROM t_change_lc_ma a) t
START WITH t rn =
CONNECT BY t card_code = PRIOR t card_code
AND t rn = PRIOR t rn
GROUP BY t card_code;
SELECT * FROM t_change_cl_ma;
SELECT t card_code
substr(t q
instr( ; || t q ; rn)
instr(t q || ; ; rn) instr( ; || t q ; rn)) q
FROM (SELECT a card_code a q b rn
FROM t_change_cl_ma a
(SELECT ROWNUM rn FROM al CONNECT BY ROWNUM <= ) b
WHERE instr( ; || a q ; rn) > ) t
ORDER BY ;
实现一条记录根据条件多表插入
DROP TABLE t_ia_src;
CREATE TABLE t_ia_src AS SELECT a ||ROWNUM c b ||ROWNUM c FROM al CONNECT BY ROWNUM<= ;
DROP TABLE t_ia_dest_ ;
CREATE TABLE t_ia_dest_ (flag VARCHAR ( ) c VARCHAR ( ));
DROP TABLE t_ia_dest_ ;
CREATE TABLE t_ia_dest_ (flag VARCHAR ( ) c VARCHAR ( ));
DROP TABLE t_ia_dest_ ;
CREATE TABLE t_ia_dest_ (flag VARCHAR ( ) c VARCHAR ( ));
SELECT * FROM t_ia_src;
SELECT * FROM t_ia_dest_ ;
SELECT * FROM t_ia_dest_ ;
SELECT * FROM t_ia_dest_ ;
INSERT ALL
WHEN (c IN ( a a )) THEN
INTO t_ia_dest_ (flag c) VALUES(flag c )
WHEN (c IN ( a a )) THEN
INTO t_ia_dest_ (flag c) VALUES(flag c )
ELSE
INTO t_ia_dest_ (flag c) VALUES(flag ||flag c ||c )
SELECT c c f flag f flag FROM t_ia_src;
如果存在就更新 不存在就插入用一个语句实现
DROP TABLE t_mg;
CREATE TABLE t_mg(code VARCHAR ( ) NAME VARCHAR ( ));
SELECT * FROM t_mg;
MERGE INTO t_mg a
USING (SELECT the code code the name NAME FROM al) b
ON (de = de)
WHEN MATCHED THEN
UPDATE SET a NAME = b NAME
WHEN NOT MATCHED THEN
INSERT (code NAME) VALUES (de b NAME);
抽取/删除重复记录
DROP TABLE t_p;
CREATE TABLE t_p AS SELECT code_ ||ROWNUM code dbms_random string( z ) NAME FROM al CONNECT BY ROWNUM<= ;
INSERT INTO t_p SELECT code_ ||ROWNUM code dbms_random string( z ) NAME FROM al CONNECT BY ROWNUM<= ;
SELECT * FROM t_p;
SELECT * FROM t_p a WHERE a ROWID <> (SELECT MIN(b ROWID) FROM t_p b WHERE de=de);
SELECT de b NAME
FROM (SELECT de
a NAME
row_number() over(PARTITION BY de ORDER BY a ROWID) rn
FROM t_p a) b
WHERE b rn > ;
IN/EXISTS的不同适用环境
t_orders customer_id有索引
SELECT a *
FROM t_employees a
WHERE a employee_id IN
(SELECT b sales_rep_id FROM t_orders b WHERE b customer_id = );
SELECT a *
FROM t_employees a
WHERE EXISTS (SELECT
FROM t_orders b
WHERE b customer_id =
AND a employee_id = b sales_rep_id);
t_employees department_id有索引
SELECT a *
FROM t_employees a
WHERE a department_id =
AND EXISTS
(SELECT FROM t_orders b WHERE a employee_id = b sales_rep_id);
SELECT a *
FROM t_employees a
WHERE a department_id =
AND a employee_id IN (SELECT b sales_rep_id FROM t_orders b);
FBI
DROP TABLE t_fbi;
CREATE TABLE t_fbi AS
SELECT ROWNUM rn dbms_random STRING( z ) NAME SYSDATE + dbms_random VALUE * dt FROM al
CONNECT BY ROWNUM <= ;
CREATE INDEX idx_nonfbi ON t_fbi(dt);
DROP INDEX idx_fbi_ ;
CREATE INDEX idx_fbi_ ON t_fbi(trunc(dt));
SELECT * FROM t_fbi WHERE trunc(dt) = to_date( yyyy mm dd ) ;
不建议使用
SELECT * FROM t_fbi WHERE to_char(dt yyyy mm dd ) = ;
LOOP中的MIT/ROLLBACK
DROP TABLE t_loop PURGE;
create TABLE t_loop AS SELECT * FROM user_objects WHERE = ;
SELECT * FROM t_loop;
逐行提交
DECLARE
BEGIN
FOR cur IN (SELECT * FROM user_objects) LOOP
INSERT INTO t_loop VALUES cur;
MIT;
END LOOP;
END;
模拟批量提交
DECLARE
v_count NUMBER;
BEGIN
FOR cur IN (SELECT * FROM user_objects) LOOP
INSERT INTO t_loop VALUES cur;
v_count := v_count + ;
IF v_count >= THEN
MIT;
END IF;
END LOOP;
MIT;
END;
真正的批量提交
DECLARE
CURSOR cur IS
SELECT * FROM user_objects;
TYPE rec IS TABLE OF user_objects%ROWTYPE;
recs rec;
BEGIN
OPEN cur;
WHILE (TRUE) LOOP
FETCH cur BULK COLLECT
INTO recs LIMIT ;
forall 实现批量
FORALL i IN recs COUNT
INSERT INTO t_loop VALUES recs (i);
MIT;
EXIT WHEN cur%NOTFOUND;
END LOOP;
CLOSE cur;
END;
悲观锁定/乐观锁定
DROP TABLE t_lock PURGE;
CREATE TABLE t_lock AS SELECT ID FROM al;
SELECT * FROM t_lock;
常见的实现逻辑 隐含bug
DECLARE
v_cnt NUMBER;
BEGIN
这里有并发性的bug
SELECT MAX(ID) INTO v_cnt FROM t_lock;
here for other operation
v_cnt := v_cnt + ;
INSERT INTO t_lock (ID) VALUES (v_cnt);
MIT;
END;
高并发环境下 安全的实现逻辑
DECLARE
v_cnt NUMBER;
BEGIN
对指定的行取得lock
SELECT ID INTO v_cnt FROM t_lock WHERE ID= FOR UPDATE;
在有lock的情况下继续下面的操作
SELECT MAX(ID) INTO v_cnt FROM t_lock;
here for other operation
v_cnt := v_cnt + ;
INSERT INTO t_lock (ID) VALUES (v_cnt);
MIT; 提交并且释放lock
END;
硬解析/软解析
DROP TABLE t_hard PURGE;
CREATE TABLE t_hard (ID INT);
SELECT * FROM t_hard;
DECLARE
sql_ VARCHAR ( );
BEGIN
hard parse
java中的同等语句是 Statement execute()
FOR i IN LOOP
sql_ := insert into t_hard(id) values( || i || ) ;
EXECUTE IMMEDIATE sql_ ;
END LOOP;
MIT;
soft parse
java中的同等语句是 PreparedStatement execute()
sql_ := insert into t_hard(id) values(:id) ;
FOR i IN LOOP
EXECUTE IMMEDIATE sql_
USING i;
END LOOP;
MIT;
END;
正确的分页算法
SELECT *
FROM (SELECT a * ROWNUM rn
FROM (SELECT * FROM t_employees ORDER BY first_name) a
WHERE ROWNUM <= )
WHERE rn > ;
分页算法(why not this one)
SELECT a * ROWNUM rn
FROM (SELECT * FROM t_employees ORDER BY first_name) a
WHERE ROWNUM <= AND ROWNUM > ;
分页算法(why not this one)
SELECT b *
FROM (SELECT a * ROWNUM rn
FROM t_employees a
WHERE ROWNUM < =
ORDER BY first_name) b
WHERE b rn > ;
OLAP
小计合计
SELECT CASE
WHEN a deptno IS NULL THEN
合计
WHEN a deptno IS NOT NULL AND a empno IS NULL THEN
小计
ELSE
|| a deptno
END deptno
a empno
a ename
SUM(a sal) total_sal
FROM scott emp a
GROUP BY GROUPING SETS((a deptno) (a deptno a empno a ename) ());
分组排序
SELECT a deptno
a empno
a ename
a sal
可跳跃的rank
rank() over(PARTITION BY a deptno ORDER BY a sal DESC) r
密集型rank
dense_rank() over(PARTITION BY a deptno ORDER BY a sal DESC) r
不分组排序
rank() over(ORDER BY sal DESC) r
FROM scott emp a
ORDER BY a deptno a sal DESC;
当前行数据和前/后n行的数据比较
SELECT a empno
a ename
a sal
上面一行
lag(a sal) over(ORDER BY a sal DESC) lag_
下面三行
lead(a sal ) over(ORDER BY a sal DESC) lead_
FROM scott emp a
lishixin/Article/program/Oracle/201311/16728
6. Oraclesql
前言
sql_trace 是我在工作中经常要用到的调优工具 相比较statspack 我更愿意用这个工具
因为数据逗搜库慢原因的 %以上是由于sql问题造成的 statspack没有sql的执行计划 显示没有它直观 方便 对想要针对性不强
介绍数据库调优需要经常会用到的工具 可以很精确地跟抓取相关session正在运行的sql 再通过tkprof分析出来sql的执行计划等相关信息 从而判断那些sql语句存在问题
统计如下信孝郑息（摘字官方文档）
Parse execute and fetch counts
CPU and elapsed times
Physical reads and logical reads
Number of rows processed
Misses on the library cache
Username under which each parse occurred
Each mit and rollback
使用
使用前需要注意的地方
初始化参数timed_statistics=true 允许sql trace 和其他的一些动态性能视图收集与时间（cpu elapsed）山慎历有关的参数 一定要打开 不然相关信息不会被收集 这是一个动态的参数 也可以在session级别设置
SQL>alter session set titimed_statistics=true
MAX_DUMP_FILE_SIZE跟踪文件的大小的限制 如果跟踪信息较多可以设置成unlimited 可以是KB MB单位 I开始默认为unlimited这是一个动态的参数 也可以在session级别设置
SQL>alter system set max_mp_file_size=
SQL>alter system set max_mp_file_size=unlimited
USER_DUMP_DEST指定跟踪文件的路径 默认路径实在$ORACLE_BASE/admin/ORA_SID/ump这是一个动态的参数 也可以在session级别设置
SQL>alter system set user_mp_dest=/oracle/trace
数据库级别
设置slq_trace参数为true会对整个实例进行跟踪 包括所有进程 用户进程和后台进程 会造成比较严重的性能问题 生产环境一定要慎用
SQL>alter system set sql_trace=true;
Session级别
当前会话
SQL>alter session set sql_trace=true;
SQL>alter session set sql_trace=false;
其他会话
通过oracle提供的系统包 DBMS_SYSTEM SET_SQL_TRACE_IN_SESSION来实现
SQL>execute dbms_system set_sql_trace_in_session(sid serial# true);
SQL>execute dbms_system set_sql_trace_in_session(sid serial# false);
注
sid serial#从v$session视图中获得
DBMS_SYSTEM包里还可以对其他用户的参数（如 timed_statistics max_mp_file）进行设置 在这不做介绍了 很少用到 想了解dbms_system里的程序包可以desc dbms_system看一下
得到trace文件后我们要用tkprof他进行格式化 通过sql语句快速定位到相应的trace文件
Tkprof
tkprof的目的是将sql trace生成的跟踪文件转换成用户可以理解的格式
格式
tkprof tracefile outputfile [optional | parameters ]
参数和选项（这里只介绍最常用的 也是最实用的）
explain=user/password执行explain命令将结果放在SQL trace的输出文件中
sys=[yes/no]确定系统是否列出由sys用户产生或重调的sql语句
sort=sort_option按照指定的方法对sql trace的输出文件进行降序排序
sort_option选项
prscnt按解析次数排序
prscpu按解析所花cpu时间排序
prsela按解析所经历的时间排序
prsdsk按解析时物理的读操作的次数排序
prsqry按解析时以一致模式读取数据块的次数排序
prscu按解析时以当前读取数据块的次数进行排序
execnt按执行次数排序
execpu按执行时花的cpu时间排序
exeela按执行所经历的时间排序
exedsk按执行时物理读操作的次数排序
exeqry按执行时以一致模式读取数据块的次数排序
execu按执行时以当前模式读取数据块的次数排序
exerow按执行时处理的记录的次数进行排序
exemis按执行时库缓冲区的错误排序
fchcnt按返回数据的次数进行排序
fchcpu按返回数据cpu所花时间排序
fchela按返回数据所经历的时间排序
fchdsk按返回数据时的物理读操作的次数排序
fchqry按返回数据时一致模式读取数据块的次数排序
fchcu按返回数据时当前模式读取数据块的次数排序
fchrow按返回数据时处理的数据数量排序
注
这些排序中我经常用到的是fchdsk fckchela fchqry 因为有问题的sql一般都是大的查询造成的 当然更新 插入 删除时也会存在全表扫描 这就需要:exedsk exeqry exeela等选项 根据具体情况具体分析
Cpu时间和Elapsed时间都是以秒为单位 而且两个值基本上一样 但我比较常用elapsed 他是反映的用户相应时间 从运行sql到用户得到结果的时间 会更实际些
tkprof输出文件各列的含义 （理解下面的含义对我们快速定位问题很有帮助）
parse:
将sql语句转换成执行计划 包括检查是否有正确的授权 需要到得表 列及其他引用到得对象是否存在 这些信息分别存在v$librarycache v$rowcache
execute
oracle实际执行的语句 如 insert update delete 这些会修改数据 对于select操作 这部只是确定选择的行数
fetch
返回查询获得的行数 只有执行select会被收集
Count
这个语句被parse execute fetch的次数的统计
Cpu
这个语句所有的parse execute fetch所用的cpu总的时间 以秒为单位 如果TIMED_STATISTICS 关闭的话 值为
Elapsed
这个语句所有的parse execute fetch所消耗的总的时间 以秒为单位 如果TIMED_STATISTICS 关闭的话 值为
Disk
这个语句所有的parse execute fetch从磁盘上的数据文件中读取的数据块的数量
Query
在一致性读的模式下 这个语句所有的parse execute fetch所获取的buffer数量（这部分是从内存读取的也就是逻辑读取的 相当于执行计划里的consistent gets）
Current
在current模式下 这个语句所有的parse execute fetch所获取的buffer数量 一般是current模式下发生的delect insert update的操作都会获取buffer
Rows
语句返回的行数 不包括子查询中返回的记录数目 对于select语句 返回在fetch这步 对于insert delete update操作 返回记录是在execute这步
分析
我一般的思路步骤是
先找磁盘多的sq l(sort= fchdsk ) 意味着全表扫描 找运行时间长的(sort= fchela) 意味着sql可能写的不好或磁盘 逻辑读较多 找出一致性读较多的(sort= fchqry) 当表不是很大的时候（可能全部缓存住了） 没有发生磁盘读 但不意味着不需要建立索引 或者sql需要优化 找出当前模式从缓冲区获得数据的数量(sort=exedsk exeela exeqry) 这些主要集中在dml语句里的操作 看是否有必要优化sql或建立索引之所以排序是为了在sql很多的时候快速定位sql 如果sql比较少的话就没必要排序了 但我们要有分析问题的思路
举例
我自己建立了一个表
create table t (id int);
begin
for v in loop
insert into t values(v );
end loop
mit;
end;
下面是sql_trace所抓到得sql
不正常状态
*******************************************************************************
select *
from t
where id=
call count cpu elapsed disk query current rows
Parse Execute Fetch
total
Misses in library cache ring parse:
Optimizer goal: CHOOSE
Parsing user id: (WH)
Rows Row Source Operation
TABLE ACCESS FULL T
Rows Execution Plan
SELECT STATEMENT GOAL: CHOOSE
TABLE ACCESS (FULL) OF T
首先这是一个select语句 它走了全部扫描
磁盘读（ ）和逻辑读（ ）都很多
运行了 次（Execute） 分析了 次（Parse） 一共用了将近 秒（elapsed）
我只是选择表的一行的数据的结果 就发生这么大的成本 很显然是全表扫描的结果造成的
正常状态
在做跟踪前我为这个表建立了一个索引
Create index t on t (id);
*******************************************************************************
select *
from t
where id=
call count cpu elapsed disk query current rows
Parse Execute Fetch
total
Misses in library cache ring parse:
Optimizer goal: CHOOSE
Parsing user id: (WH)
Rows Row Source Operation
INDEX RANGE SCAN T (object id )
Rows Execution Plan
SELECT STATEMENT GOAL: CHOOSE
INDEX (RANGE SCAN) OF T (NON UNIQUE)
*******************************************************************************
同样的语句
它走了索引 物理读 这个 其实是开始读索引时需要第一次读入的 以后运行就没有了
逻辑读 （平均这个sql一次 个逻辑读）
同样运行了 次（Execute）
分析了 次（Parse） 运行次数越多 分析次数越少越好一共只用了 秒（elapsed）
lishixin/Article/program/Oracle/201311/17866
7. 在登陆sql plus时老是出现错误提示 ora-12154:tns:无法解析指定的连接标识符
一、原因：pl/sql 每当oracle client中service name发生变化，都会按照client安装目录下最新的tnsnames.ora，去作为它的闹茄读取文件；如果该文件在卸载oracle client后被手工毁败删除或手工改动过后，未在oracle client中修改；则会报“ORA-12514：tns:无法解析指定的连接标识符”错误。
二、解决方法：
1、用tnsping 检测 plSQL登陆时database(数据库)别名是否可正确解析，如报“TNS-03505：无法解析名称”，则此database别名有冲突，需更改。
2、手工改动tnsnames.ora后，在oracle client中修改主机名（“对象”--“将数据库添加到树”--“从本地的……”，然后把错误主机名删除或从树中移除）
3、重新打开plSQL，用正确的database别名登陆，即恢复正常
4、如仍有问题，不登陆进入plSQL界面"tools”--"Preferences"--"Oracle"--"Connection"，把"home”（oracle主目录名）里的数据清空，应用后，重新启动plSQL即可纤弯颤。
三、附录：
1、在做以上更改后oracle自带的sqlPLUS，可能会出现不能正常登陆远程服务器的情况；因为其访问的是database下的tnsnames.ora文件，目录与client的不一样，默认为 X（盘符）:oracleproct10.2.0db_1NETWORKADMIN nsnames.ora
2、解决方法：只需将client下tnsnames.ora中定义的主机字符串（service_name）的命令行复制到database 下的tnsnames.ora内，然后保存就可以了。
8. 2020-01-20 oracle中sql如何执行，什么是硬解析和软解析
1.语法检查：检查 SQL 拼写是否正确，如果不正确，Oracle 会报语法错误。
2.语义检查：检查 SQL 中的访问对象是否存在。比如我们在写 SELECT 语句的时候，列名写错了，系统就会提示错误。语法检查和语义侍槐察检查的作用是保证 SQL 语句没有错误。
3.权限检查：看用户是否具备访问该数据的权限。
4.共享池检查：共享池（Shared Pool）是一块内存池，最主要的作用是缓存 SQL 语句和该语句的执行计划。Oracle 通过检查共享池是否存在 SQL 语句的执行计划，来判断进行软解析，还是硬解析。那软解析和硬解析又该怎么理解呢？在共享池中，Oracle 首先对 SQL 语句进行 Hash 运算，然后根据 Hash 值在库缓存（Library Cache）中查找，如果存在 SQL 语句的执行计划，就直接拿来执行，直接进入“执行器”的环节，这就是软解析。如果没有找到 SQL 语句和执行计划，Oracle 就需要创建解析树进行解析明羡，生成执行计划，进入“优化器”这个步骤，这就是硬解析。
5.优化器：优化器中就是要进行硬解析，也就老茄是决定怎么做，比如创建解析树，生成执行计划。
6.执行器：当有了解析树和执行计划之后，就知道了 SQL 该怎么被执行，这样就可以在执行器中执行语句了。
共享池是 Oracle 中的术语，包括了库缓存，数据字典缓冲区等。我们上面已经讲到了库缓存区，它主要缓存 SQL 语句和执行计划。而数据字典缓冲区存储的是 Oracle 中的对象定义，比如表、视图、索引等对象。当对 SQL 语句进行解析的时候，如果需要相关的数据，会从数据字典缓冲区中提取。
如何避免硬解析，尽量使用软解析呢？在 Oracle 中，绑定变量是它的一大特色。绑定变量就是在 SQL 语句中使用变量，通过不同的变量取值来改变 SQL 的执行结果。
9. 【基于ORACLE数据库的SQL语句优化分析】 数据库查询语句的优化
【摘要】随着数据库应用范围及规模的不断扩大，数据库的性能问题逐渐显现，优化数据库有助于维持系统的稳定性以及运行的高效性。本文主要依据笔者在实际工作中的精坦敏拍英，对SQL语句优化的目的、SQL语句优化技术及原则进行全面分析和阐述。
【关键词】ORACLE数据库；SQL语句；优化
1前言
随着现代化信息技术的迅猛发展，互联网应用的日益普及，数据库技术的影响力越来越大。作为信息系统管理的核心，数据库的主要操作就是查询，数据库的应用效率在很大程度上是由查询速度决定的，特别是对于规模较大的数据库而言，查询速度十分关键。查询速度在SQL语句中占有很大比重，所以，通过对查询语句进行优化有助于促进应用系统性能及效率的进一步提升。
2SQL语句优化分析
2.1SQL语句优化的目的
对于一个数据库而言，在确保设计无误的前提下，要想避免出现性能问题必须确保其拥有合理的SQL语句拿唤结构。最简单的数据库寻找数据路径是对SQL语句进行调整，ORACLE数据库性能提升的主要途径就是对SQL语句进行适当的调整。从本质上讲，SQL语句优化就是确保所使用的语句可以被优化器识别，对索引进行有效利用以便控制表扫描的I/O次数，有效防止出现表搜索。用高性能的SQL语句替代低性能的SQL语句，确定最佳的数据查找路径，尽可能使CPU时间与I/O时间保持平衡是进行优化的主要目的。在对SQL语句进行优化的过程中，以系统需求为依据确定最有可能实现性能提升的语句并进行优化。
2.2SQL语句优化技术及原则
当数据量积累到一定程度之后，对于数据库全表SQL语句进行一次扫描，若查询策略较好，一般只用几秒钟，但如果SQL语句性能较低，就需要用几分钟甚至更多时间。从这点不难看出，SQL语句性能对于查询速度具有极大的影响，所以，对于应用系统而言，不仅能满足功能的实现，还要保证SQL语句的质量。
（1）采取适宜的索引。为达到优化查询的目的，一项重要工作就是确定相适应的索引，并严格依照原则加以使用，与此同时，为有效控制I/O竞争，不可以在同一个磁盘中同时建立索引和用户表空间。
语句1：SELECT CUS_NO, CUS_NAME FROM CUSTOMER WHERE CUS_NO NOT IN
(SELECT CUS_NO FROM SERVICE);
语句2： SELECT CUS_NO, CUS_NAME FROM CUSTOMER WHERE NOT EXISTS
(SELECT * FROM SERVICE WHERE SERVICE.CUS_NO=CUSTOMER.CUS_NO);
上述两个语句可以达到一致的查询结果，对二者进行对比，当执行语句1时，由于ORACLE未利用CUSTOMER 表上CUS_NO索引，所以就会扫描整表，在执行语句2的过让羡程中，ORACLE所扫描的只是CUSTOMER 表子查询中的联合查询，并且使用了CUS_NO索引，因此，在执行效率方面明显优于前者。
（2）避免在SELECT子句中出现“*”。ORACLE在进行解析时，需要按照一定顺序对“*”进行转换，该项转换工作的进行需要对数据库的数据字典进行查询，势必需要花费较多的时间，这样就会导致较低的效率，所以，要避免在SELECT子句中出现“*”。
（3）如果必要可以利用COMMIT提交事务。ORACLE能够自动提交DDL语句，而诸如DML等类型的语句的提交则是通过手动方式或者回滚事务实现的。在编写应用程序的过程中，在操作诸如insert、delete以及update 等较为复杂的语境的时候，利用COMMIT提交事务可以讲会话中持有的锁加以释放，将存在于缓存中的未经修改的数据块进行清除，进而将系统资源予以释放，促进系统性能的进一步提升，因此，如果有必要，可以利用COMMIT对相关事务进行提交。
（4）联合查询连接顺序的确定。如果查询操作涉及到多个表，基础表应当是交叉表，所谓交叉表具体是指被其他表引用的表。连接执行效果在很大程度上受到FROM语句中表的顺序的影响，对于FROM中所包含的表，ORACLE解析器进行处理的顺序是由右至左，SQL语句中所选择的基础表会因优化器的不同而有所区别，在使用CBO的情况下，优化器会对SQL语句中各个表的物理大小以及索引状态进行检查，在此基础上确定一个花费最小的执行路径；在使用RBO的情况下，如果全部的连接条件均有索引与之相对应，那么，FROM子句中位置最后面的表就是基础表。
（5）IN用EXISTS取代。在对数个基础表查询过程中，一般需要进行表的连接。因为利用IN的子查询过程中，ORACLE的扫描对象是全表，因此，出于提高查询效率目的的考虑，应当将IN用EXISTS取代。
（6）在索引列中不使用计算。当通过对函数进行引用在WHERE子句中进行计算的时候，假如索引列只是函数的一部分，优化器就会针对全表进行扫描，而不会使用索引，所以，在索引列中不能使用函数。
3结语
综上所述，随着现代化信息技术的迅猛发展，互联网应用的日益普及，数据库技术的影响力越来越大。在信息量迅速激增的形势下，数据库优化调整成为当前所面临的一大关键性问题，特别是对规模较大的数据库而言，及时进行优化的意义更加倍重大。对于数据库的运行性能而言，最主要的影响因素主要体现在以下几点：数据库系统架构的设计是否合理，资源配置是否科学以及SQL语句编写效率等。笔者从事的是电信企业的运营分析工作，每天都要从数据库取各种数据，可以说是离不开数据库，所以在实践中，我觉得严格遵守SQL语句优化原则及方法，并在实践中及时总结经验教训，可以实现对系统响应时间的有效控制，促进运行效率的提升。
参考文献
[1] 许开宇,胡文骅. 如何提高ORACLE数据库应用程序的性能[J]. 计算机应用与软件. 2002(10)
[2] 郑耀,吴建岚. 基于Oracle数据库的语句优化策略[J]. 信息与电脑(理论版). 2011(07)
[3] 高攀,施蔚然. 基于Oracle数据库的SQL语句优化[J]. 电脑编程技巧与维护. 2010(22)
[4] 钟小权,叶猛. Oracle数据库的SQL语句优化[J]. 计算机与现代化. 2011(03)
作者简介：
王勇军，男，（1981.1-），吉林通化人，就职于中国联合网络通信有限公司长春市分公司，通信工程师，本科，研究方向：SQL使用
（作者单位：中国联合网络通信有限公司长春市分公司）
10. oracle SQL查询时提示 用户数据中的connect by 循环 报错是什么原因
一般是数据错误导致了死循环。
如数据为这样：
ID 父ID 值
1 2 10
2 1 20
如图，ID为1的父ID为2，而同时ID为2的父ID是1，这样的话，就会互相认对方的ID为父ID，就会造成一个死循环，这种错误，一般不用修改语句，需要正确检查数据的正确性。