呵呵 这个怎么说呢 其实功能跟你的连接SQL一样的 首先你必须要有传输值 一般我们在SQL存储过程中设置（针对一般计算的存储过程，发邮件就除外），在delphi裏面你直接用一个ADO 连接下就可以了 不是select 而是exec ！
❷ 如何解析oracle执行计划
一、通过PL/SQL Dev工具
1、直接File->New->Explain Plan Window，在窗口中执行sql可以查看计划结果。其中，Cost表示cpu的消耗，单位为n%，Cardinality表示执行的行数，等价Rows。
2、先执行 EXPLAIN PLAN FOR select * from tableA where paraA=1，再 select * from table(DBMS_XPLAN.DISPLAY)便可以看到oracle的执行计划了，看到的结果和1中的一样，所以使用工具的时候推荐使用1方法。
注意：PL/SQL Dev工具的Command window中不支持set autotrance on的命令。还有使用工具方法查看计划看到的信息不全，有些时候我们需要sqlplus的支持。
二、通过sqlplus
1．最简单的办法
Sql> set autotrace on
Sql> select * from al;
执行完语句后，会显示explain plan 与 统计信息。
这个语句的优点就是它的缺点，这样在用该方法查看执行时间较长的sql语句时，需要等待该语句执行成功后，才返回执行计划，使优化的周期大大增长。如果不想执行语句而只是想得到执行计划可以采用：
Sql> set autotrace traceonly
这样，就只会列出执行计划，而不会真正的执行语句，大大减少了优化时间。虽然也列出了统计信息，但是因为没有执行语句，所以该统计信息没有用处，如果执行该语句时遇到错误，解决方法为：
(1）在要分析的用户下：
Sqlplus > @ ?
dbmsadminutlxplan.sql
(2) 用sys用户登陆
Sqlplus > @ ?sqlplusadminplustrce.sql
Sqlplus > grant plustrace to user_name;
- - user_name是上面所说的分析用户
2．用explain plan命令
(1) sqlplus > explain plan for select * from testdb.myuser
(2) sqlplus > select * from table(dbms_xplan.display);
上面这2种方法只能为在本会话中正在运行的语句产生执行计划，即我们需要已经知道了哪条语句运行的效率很差，我们是有目的只对这条SQL语句去优化。其实，在很多情况下，我们只会听一个客户抱怨说现在系统运行很慢，而我们不知道是哪个SQL引起的。此时有许多现成的语句可以找出耗费资源比较多的语句，如：
SELECT ADDRESS, substr(SQL_TEXT,1,20) Text, buffer_gets, executions,
buffer_gets/executions AVG FROM v$sqlarea
WHERE executions>0 AND buffer_gets > 100000 ORDER BY 5;
ADDRESS TEXT BUFFER_GETS EXECUTIONS AVG
-------- ---------------------------------------- ----------- ---------- ------------------------------------------------------------
66D83D64 select t.name, (sel 421531 60104 7.01336017
66D9E8AC select t.schema, t.n 1141739 2732 417.913250
66B82BCC select s.synonym_nam 441261 6 73543.5
从而对找出的语句进行进一步优化。当然我们还可以为一个正在运行的会话中运行的所有SQL语句生成执行计划，这需要对该会话进行跟踪，产生trace文件，然后对该文件用tkprof程序格式化一下，这种得到执行计划的方式很有用，因为它包含其它额外信息，如SQL语句执行的每个阶段(如Parse、Execute、Fetch)分别耗费的各个资源情况(如CPU、DISK、elapsed等)。
3、启用SQL_TRACE跟踪所有后台进程活动：
全局参数设置： .OracleHome/admin/SID/pfile中指定: SQL_TRACE = true (10g)
当前session中设置：
SQL> alter session set SQL_TRACE=true;
SQL> select * from al;
SQL> alter session set SQL_TRACE=false;
对其他用户进行跟踪设置：
SQL> select sid,serial#,username from v$session where username='XXX';
SID SERIAL# USERNAME
------ ---------- ------------------
127 31923 A
128 54521 B
开启跟踪:SQL> exec dbms_system.set_SQL_TRACE_in_session(127,31923,true);
关闭跟踪:SQL> exec dbms_system.set_SQL_TRACE_in_session(127,31923,false);
然后使用oracle自带的tkprof命令行工具格式化跟踪文件。
4、使用10046事件进行查询：
10046事件级别：
Lv1 - 启用标准的SQL_TRACE功能,等价于SQL_TRACE
Lv4 - Level 1 + 绑定值(bind values)
Lv8 - Level 1 + 等待事件跟踪
Lv12 - Level 1 + Level 4 + Level 8
全局设定：
OracleHome/admin/SID/pfile中指定: EVENT="10046 trace name context forever,level 12"
当前session设定：
开启:SQL> alter session set events '10046 trace name context forever, level 8';
关闭:SQL> alter session set events '10046 trace name context off';
对其他用户进行设置：
SQL> select sid,serial#,username from v$session where username='XXX';
SID SERIAL# USERNAME
------ ---------- ------------------
127 31923 A
SQL> exec dbms_system.set_ev(127,31923,10046,8,'A');
5、使用tkprof格式化跟踪文件： (根据下面SQL语句得到的文件都不存在该目录下,郁闷啊,懵懂啊...)
一般，一次跟踪可以分为以下几步：
1、界定需要跟踪的目标范围，并使用适当的命令启用所需跟踪。
2、经过一段时间后，停止跟踪。此时应该产生了一个跟踪结果文件。
3、找到跟踪文件，并对其进行格式化，然后阅读或分析。
--使用一下SQL找到当前session的跟踪文件：
SELECT d.value|| '/' ||lower(rtrim(i.instance, chr( 0 )))|| '_ora_' ||p.spid|| '.trc' trace_file_namefrom( select p.spid from v$mystat m,v$session s, v$process pwhere m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,( select t.instance from v$thread t,v$parameter vwhere v.name = 'thread' and (v.value = 0 or t.thread# = to_number(v.value))) i,( select value from v$parameter where name = 'user_mp_dest' ) d;-- 其它用户的 session SELECT d.value|| '/' ||lower(rtrim(i.instance, chr( 0 )))|| '_ora_' ||p.spid|| '.trc' trace_file_name from ( select p.spid from v$session s, v$process p where s.sid= '27' and s. SERIAL#= '30' and p.addr = s.paddr) p, ( select t.instance from v$thread t,v$parameter v where v.name = 'thread' and (v.value = 0 or t.thread# = to_number(v.value))) i, ( select value from v$parameter where name = 'user_mp_dest' ) d;
--查找后使用tkprof命令,将TRACE文件格式为到D盘的explain_format.txt文件中
SQL> $tkprof d:/oracle/admin/FZLGFM/ump/fzlgfm_ora_3468.trc d:/explain_format.txt
文件内容大致如下(看不太懂....懵懂啊.....天啊....神啊.....过几时就懂了/////////////)
TKPROF: Release 9.2.0.1.0 - Proction on 星期二 4月 20 13:59:20 2010
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Trace file: d:/oracle/admin/FZLGFM/ump/fzlgfm_ora_3468.trc
Sort options: default
********************************************************************************
count = number of times OCI procere was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call********************************************************************************
alter session set events '10046 trace name context forever, level 8'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 0 0
Misses in library cache ring parse: 0
Misses in library cache ring execute: 1
Optimizer goal: CHOOSE
Parsing user id: SYS
❸ mysqldb select 是不是缓存到内存中
ORACLE缓存是把ORACLE近期查看的语句防止在ORACLE设定的缓存当中
ORACLE缓存表是把表某个表放置在缓存当中，缓存是ORACLE在内存中的一个分区
表缓存的设定
oracle中如何将表缓存到内存中
由于在一些静态资料表在数据库中被频繁的访问，所以可以考虑将这些数据量不大的表缓存到内存当中。
将fisher表缓存到内存中
alter table fisher cache;方法一
2）alter table fisher storage(buffer_pool keep);方法二
--取消缓存
1）alter table fisher nocache;
2）alter table fisher storage(buffer_pool default);
select table_name,OWNER,cache,buffer_pool from dba_tables where table_name='FISHER'; --查看是否缓存
select * from dba_segments where segment_name='FISHER' ; --查看表大小
方法一： cache是将表缓存到share pool 中，该操作直接将表缓存的热端，受LRU算法控制。
方法二：将表缓存到一个固定的内存空间中，默认情况下buffer_pool空间为0,。需手动设置空间大小。
设置空间大小：alter system set db_keep_cache_size=50M scope=both sid=‘*';
--表缓存
alter table table_name cache = alter table table_name storage(buffer_pool default);
alter table table_name storage(buffer_pool keep);
--已经加入到KEEP区的表想要移出缓存，使用
alter table table_name nocache;
--查看哪些表被放在缓存区 但并不意味着该表已经被缓存
select table_name from dba_tables where buffer_pool='keep';
--查询到该表是否已经被缓存
select table_name,cache,buffer_pool from user_TABLES where cache like '%Y';
--查询当前用户下表的情况
select table_name,cache,buffer_pool from user_TABLES;
--对于普通LOB类型的segment的cache方法
alter table t2 modify lob(c2) (storage (buffer_pool keep) cache);
--取消缓存
alter table test modify lob(address) (storage (buffer_pool keep) nocache);
keep Buffer Pool
Keep Buffer Pool 的作用是缓存那些需要经常查询的对象但又容易被默认缓冲区置换出去的对象，按惯例，Keep pool设置为合理的大小，以使其中存储的对象不再age out，也就是查询这个对象的操作不会引起磁盘IO操作，可以极大地提高查询性能。
默认的情况下 db_keep_cache_size=0，未启用，如果想要启用，需要手工设置db_keep_cache_size的值，设置了这个值之后 db_cache_size 会减少。
并不是我们设置了keep pool 之后，热点表就一定能够缓存在 keep pool ，keep pool 同样也是由LRU 链表管理的，当keep pool 不够的时候，最先缓存到 keep pool 的对象会被挤出，不过与default pool 中的 LRU 的管理方式不同，在keep pool 中表永远是从MRU 移动到LRU，不会由于你做了FTS而将表缓存到LRU端，在keep pool中对象永远是先进先出。当oracle发现你的表太太，大过你设定keep pool的大小是，根本就不会放到keep池中去的（如keep pool设定100M ，设定的用户缓存的表为200M）。可以用select segment_name from dba_segments where BUFFER_POOL = 'KEEP';语句查看便知。
10g中SGA自动管理，ORACLE并不会为我们管理keep pool ，ORACLE只会管理default pool。
查看 keep pool 大小
SQL> select component,current_size from v$sga_dynamic_components
2 where component='KEEP buffer cache';
COMPONENT CURRENT_SIZE
---------------------------------------------------------------- ------------
KEEP buffer cache 0
手动分配keep pool
SQL> show parameter keep
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_keep string
control_file_record_keep_time integer 7
db_keep_cache_size big integer 0
SQL> alter system set db_keep_cache_size=10m;
系统已更改。
SQL> show parameter keep
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_keep string
control_file_record_keep_time integer 7
db_keep_cache_size big integer 16M这里keep pool 16M，可我前面设置命名是10m了看
SQL> select component,current_size from v$sga_dynamic_components where component='KEEP buffer cache';
COMPONENT CURRENT_SIZE
---------------------------------------------------------------- ------------
KEEP buffer cache 16777216 这里keep pool 16M，可我前面设置命名是10m了看
查看keep pool剩余大小
SQL> select p.name,a.cnum_repl "total buffers",a.anum_repl "free buffers" from x$kcbwds a, v$buffer_pool p
2 where a.set_id=p.LO_SETID and p.name='KEEP';
NAME total buffers free buffers
-------------------- ------------- ------------
KEEP 1984 1984
可以看到没有使用过keep 池
指定table的缓存池
SQL>create table test as select * from dba_objects;;
Table created.
SQL> alter table test storage(buffer_pool keep);
Table altered.
或者是
create table test storage(buffer_pool keep) as select * from dba_objects;
查看放入Keep的对象
SQL> select segment_name from dba_segments where BUFFER_POOL = 'KEEP';
SEGMENT_NAME
--------------------------------------------------------------------------------
TEST
SQL> /
NAME total buffers free buffers
-------------------- ------------- ------------
KEEP 1984 1962 可以看到使用了22个block
查看以上的表占用了db_keep_cache_size 多大的空间看
SQL> select substr(sum(b.NUMBER_OF_BLOCKS) * 8129 / 1024 / 1024, 1, 5) || 'M'
from (SELECT o.OBJECT_NAME, COUNT(*) NUMBER_OF_BLOCKS
FROM DBA_OBJECTS o, V$BH bh, dba_segments dd
WHERE o.DATA_OBJECT_ID = bh.OBJD
AND o.OWNER = dd.owner
and dd.segment_name = o.OBJECT_NAME
and dd.buffer_pool != 'DEFAULT'
GROUP BY o.OBJECT_NAME
ORDER BY COUNT(*)) b; 2 3 4 5 6 7 8 9
SUBSTR(SUM(
-----------
3.643M
SQL> select table_name,cache,blocks from dba_tables where wner='ROBINSON' and buffer_pool='KEEP';
TABLE_NAME CACHE BLOCKS
------------------------------ -------------------- ----------
TEST N 22
可以看到这个表的 22个block 全部cache 到 keep pool ，这里的cache 字段表明 这个表 还没有使用 这个命令 alter table test cache,如果 使用了 alter table test cache ,命令，那么 N 将变成Y
总结：如果表经常使用，而且表较小，可以设置 keep pool ，将table 全部 cache 到 keep pool, keep pool 要么 全部 cache 一个table ，要么不cache 。所以，对于大表来说，如果想要 cache 到 keep pool， 就需要设置 较大的 keep pool ，以容纳大的 table，否者就没有作用了。
Recycle Buffer Pool
Recycle Buffer Pool正好相反。Recycle Buffer Pool用于存储临时使用的、不被经常使用的较大的对象，这些对象放置在Default Buffer Pool显然是不合适的，这些块会导致过量的缓冲区刷新输出，而且不会带来任何好处，因为等你想要再用这个块时，它可已经老化退出了缓存。要把这些段与默认池和保持池中的段分开，这样就不会导致默认池和保持池中的块老化而退出缓存。
SQL> show parameter recyc
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_recycle string
db_recycle_cache_size big integer 12M
recyclebin string on
如何将一个表放入Recycle Buffer Pool中：
SQL> alter table test1 storage (buffer_pool recycle);
Table altered.
很多老的文档会提及buffer_pool_keep和buffer_pool_recycle 这两个参数，其实这两个参数已经废弃，由新参数db_keep_cache_size和db_recycle_cache_size 替代：
SQL>select ISDEPRECATED,NAME from v$parameter where name = 'buffer_pool_keep';
ISDEP NAME
----- -----------------
TRUE buffer_pool_keep
=======================================================================================
--表缓存
alter table ..... storage(buffer_pool keep);
--查看哪些表被放在缓存区 但并不意味着该表已经被缓存
select table_name from dba_tables where buffer_pool='keep';
--查询到该表是否已经被缓存
select table_name,cache,buffer_pool from user_TABLES where cache like '%Y';
--已经加入到KEEP区的表想要移出缓存，使用
alter table table_name nocache;
--批量插入ORACLE建议用
insert all into ...insert into ...select 1 from al;
insert all into ... insert into ...select 1 from al;
--查询当前用户下表的情况
select table_name,cache,buffer_pool from user_TABLES;
--对于普通LOB类型的segment的cache方法
alter table t2 modify lob(c2) (storage (buffer_pool keep) cache);
--取消缓存
alter table test modify lob(address) (storage (buffer_pool keep) nocache);
--查询段
select segment_name,segment_type,buffer_pool from user_segments;
--对基于CLOB类型的对象的cache方法
alter table lob1 modify lob(c1.xmldata) (storage (buffer_pool keep) cache);
--查询该用户下所有表内的大字段情况
select column_name,segment_name from user_lobs;
来一段Tom关于Multiple Buffer Pools的解释，讲解得很清楚：
实际上，这3 个池会以大体相同的方式管理块；将块老化或缓存的算法并没有根本的差异。这样做的目标是让DBA 能把段聚集到逗热地区（hot）、逗温地区（warm）和逗不适合缓存地区（do not care to cache）。
理论上讲，默认池中的对象应该足够热（也就是说，用得足够多），可以保证一直呆在缓存中。缓存会把它们一直留在内存中，因为它们是非常热门的块。可能还有一些段相当热门，但是并不太热；这些块就作为温块。这些段的块可以从缓存刷新输出，为不常用的一些块（逗不适合缓存地块）腾出空间。为了保持这些温段的块得到缓存，可以采取下面的某种做法：将这些段分配到保持池，力图让温块在缓冲区缓存中停留得更久。将逗不适合缓存地段分配到回收池，让回收池相当小，以便块能快速地进入缓存和离开缓存（减少管理的开销）。这样会增加DBA 所要执行的管理工作，因为要考虑3 个缓存，要确定它们的大小，还要为这些缓存分配对象。还要记住，这些池之间没有共享，所以，如果保持池有大量未用的空间，即使默认池或回收池空间不够用了，保持池也不会把未用空间交出来。总之，这些池一般被视为一种非常精细的低级调优设备，只有所有其他调优手段大多用过之后才应考虑使用。
按以上步骤把表storage到keep pool中，然后调用alter system flush buffer_cache清空缓存，再全表扫描该表并打开执行计划跟踪，发现有physical reads，如下：
第一次执行计划如下：
----------------------------------------------------------
0 recursive calls
0 db block gets
253 consistent gets
251 physical reads
0 redo size
520 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
第二次执行计划如下：
----------------------------------------------------------
0 recursive calls
0 db block gets
253 consistent gets
0 physical reads
0 redo size
520 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
不知道是否可以这样理解：对于storage到keep pool中的表，第一次会直接physical reads 到keep pool中，下次就直接从keep pool中读了。flush buffer_cache会清空keep pool，这个试验就可以证明。
像上面这样连续执行2次再看执行计划，和不设置keep pool时的执行计划应该一样的，因为读第二次时，也是从default cache中读。但是当我们多读几个大表到buffer cache后，也就是替换原来从default cache中读取的数据后，再去读放入keep中的表时，就会发现keep确实起作用了，唉，终于明白怎么一回事，害得我为flush buffer导致keep中的表也phisical郁闷了半天。
ORACLE缓存设置
Oracle缓存由两个参数控制SGA_TARGET和PGA_AGGREGATE_TARGET，设置了这两个参数，其他的基本内存部分都由Oracle自动配置为最优值，这也是Oracle推荐的方式。
SGA_TARGET 和PGA_AGGREGATE_TARGET是动态参数，可以在不重启数据库的情况下修改。但是SGA_TARGET受限于 sga_max_size，SGA_TARGET不能超过sga_max_size，所以要增大sga_target先要增大sga_max_size，而sga_max_size是静态参数，修改sga_max_size必须重启Oracle。
所以修改sga_target和pga_aggregate_target的过程如下：
1、修改sga_max_size
SQL>ALTER SYSTEM SET sga_max_size=4g scope=spfile;
2、重启Oracle
3、设置参数sga_target和pga_aggregate_target，
alter system set sga_target=4G;
alter system set pga_aggregate_target=1g;
如果使用的是10g，已经是ASM, oracle会根据统计的信息，自动的来调整你的内存组件的大小，你只需要设置sga_target即可。当然你可以手动设置 db_cache_size，如果设置了的话，Oracle会在自动调整内存大小的时候把这个作为db_cache_size的最小值。
对于sga_target,在动态修改的时候，最大值不能操过sga_max_size, 如果是用scope=spfile这个方式来修改可以超过sga_max_size，应该此时sga_max_size也跟着变大了，如果超过的话。
Oracle 对数据库的cache有他自己的计算的，10g以后，内存是动态的根据对你使用系统的统计来进行调整的，如果出现问题，这块不是原因，你之所以db cache还没有上去，可能是访问的数据比较少，不过你加大db_cache_size的值，会保留这个内存空间的，但是也是一样的，数据 load到内存里，才看得到变化。
数据访问是什么样的访问，你的系统是OLAP还是OLTP，这些应用上的东西对你的决定也有影响的，要谨记，数据库的优化和维护，不仅仅是DBA来做的。如果是到了只能通过DBA来做这一步的话，就相当于看病已经到了拿手术刀这一步了。你的改变带来的风险和代价最高。
要想减少磁盘读,只能增大内存的使用.楼主可以看看这个视图v$db_cache_size,并执行下面的查询:
select block_size, size_for_estimate, size_factor, estd_physical_read_factor, estd_physical_reads from v$db_cache_advice;
Oracle在这个视图中针对db_cache_size的大小会给出一些建议。
下面解释几个列的含义
size_for_estimate：估计的cache size大小
size_factor： 估计的cache size大小与当前大小的比值
estd_physical_reads：在估计的cache size大小情况下，会产生的物理读数量
estd_physical_read_factor：估计的物理读数量与当前物理读数量的比值。
例子：
SIZE_FOR_ESTIMATE SIZE_FACTOR ESTD_PHYSICAL_READ_FACTOR ESTD_PHYSICAL_READS
❹ SQL语句执行流程与顺序原理解析
SQL语句执行流程与顺序原理解析
Oracle语句执行流程
第一步：客户端把语句发给服务器端执行
当我们在客户端执行SQL语句时，客户端会把这条SQL语句发送给服务器端，让服务器端的进程来处理这语句。也就是说，Oracle 客户端是不会做任何的操作，他的主要任务就是把客户端产生的一些SQL语句发送给服务器端。服务器进程从用户进程把信息接收到后， 在PGA 中就要此进程分配所需内存，存储相关的信息，如：在会话内存存储相关的登录信息等。
虽然在客户端也有一个数据库进程，但是，这个进程的作用跟服务器上的进程作用是不相同的，服务器上的数据库进程才会对SQL 语句进行相关的处理。不过，有个问题需要说明，就是客户端的进程跟服务器的进程是一一对应的。也就是说，在客户端连接上服务器后，在客户端与服务器端都会形成一个进程，客户端上的我们叫做客户端进程，而服务器上的我们叫做服务器进程。
第二步：语句解析
当客户端把SQL语句传送到服务器后，服务器进程会对该语句进行解析。这个解析的工作是在服务器端所进行的，解析动作又可分为很多小动作。
1）查询高速缓存（library cache）
服务器进程在接到客户端传送过来的SQL语句时，不会直接去数据库查询。服务器进程把这个SQL语句的字符转化为ASCII等效数字码，接着这个ASCII码被传递给一个HASH函数，并返回一个hash值，然后服务器进程将到shared pool中的library cache（高速缓存）中去查找是否存在相同的hash值。如果存在，服务器进程将使用这条语句已高速缓存在SHARED POOL的library cache中的已分析过的版本来执行，省去后续的解析工作，这便是软解析。若调整缓存中不存在，则需要进行后面的步骤，这便是硬解析。硬解析通常是昂贵的操作，大约占整个SQL执行的70%左右的时间，硬解析会生成执行树，执行计划，等等。
所以，采用高速数据缓存的话，可以提高SQL 语句的查询效率。其原因有两方面：一方面是从内存中读取数据要比从硬盘中的数据文件中读取数据效率要高，另一方面也是因为避免语句解析而节省了时间。
不过这里要注意一点，这个数据缓存跟有些客户端软件的数据缓存是两码事。有些客户端软件为了提高查询效率，会在应用软件的客户端设置数据缓存。由于这些数据缓存的存在，可以提高客户端应用软件的查询效率。但是，若其他人在服务器进行了相关的修改，由于应用软件数据缓存的存在，导致修改的数据不能及时反映到客户端上。从这也可以看出，应用软件的数据缓存跟数据库服务器的高速数据缓存不是一码事。
2）语句合法性检查（data dict cache）
当在高速缓存中找不到对应的SQL语句时，则服务器进程就会开始检查这条语句的合法性。这里主要是对SQL语句的语法进行检查，看看其是否合乎语法规则。如果服务器进程认为这条SQL语句不符合语法规则的时候，就会把这个错误信息反馈给客户端。在这个语法检查的过程中，不会对SQL语句中所包含的表名、列名等等进行检查，只是检查语法。
3）语言含义检查（data dict cache）
若SQL 语句符合语法上的定义的话，则服务器进程接下去会对语句中涉及的表、索引、视图等对象进行解析，并对照数据字典检查这些对象的名称以及相关结构，看看这些字段、表、视图等是否在数据库中。如果表名与列名不准确的话，则数据库会就会反馈错误信息给客户端。
所以，有时候我们写select语句的时候，若语法与表名或者列名同时写错的话，则系统是先提示说语法错误，等到语法完全正确后再提示说列名或表名错误。
4）获得对象解析锁（control structer）
当语法、语义都正确后，系统就会对我们需要查询的对象加锁。这主要是为了保障数据的一致性，防止我们在查询的过程中，其他用户对这个对象的结构发生改变。
5）数据访问权限的核对（data dict cache）
当语法、语义通过检查之后，客户端还不一定能够取得数据，服务器进程还会检查连接用户是否有这个数据访问的权限。若用户不具有数据访问权限的话，则客户端就不能够取得这些数据。要注意的是数据库服务器进程先检查语法与语义，然后才会检查访问权限。
6）确定最佳执行计划
当语法与语义都没有问题权限也匹配，服务器进程还是不会直接对数据库文件进行查询。服务器进程会根据一定的规则，对这条语句进行优化。在执行计划开发之前会有一步查询转换，如：视图合并、子查询解嵌套、谓语前推及物化视图重写查询等。为了确定采用哪个执行计划，Oracle还需要收集统计信息确定表的访问联结方法等，最终确定可能的最低成本的执行计划。
不过要注意，这个优化是有限的。一般在应用软件开发的过程中，需要对数据库的sql语句进行优化，这个优化的作用要大大地大于服务器进程的自我优化。
当服务器进程的优化器确定这条查询语句的最佳执行计划后， 就会将这条SQL语句与执行计划保存到数据高速缓存（library cache）。如此，等以后还有这个查询时，就会省略以上的语法、语义与权限检查的步骤，而直接执行SQL语句，提高SQL语句处理效率。
第三步：绑定变量赋值
如果SQL语句中使用了绑定变量，扫描绑定变量的声明，给绑定变量赋值，将变量值带入执行计划。若在解析的第一个步骤，SQL在高速缓冲中存在，则直接跳到该步骤。
第四步：语句执行
语句解析只是对SQL语句的语法进行解析，以确保服务器能够知道这条语句到底表达的是什么意思。等到语句解析完成之后，数据库服务器进程才会真正的执行这条SQL语句。
对于SELECT语句：
1）首先服务器进程要判断所需数据是否在db buffer存在，如果存在且可用，则直接获取该数据而不是从数据库文件中去查询数据，同时根据LRU 算法增加其访问计数；
2）若数据不在缓冲区中，则服务器进程将从数据库文件中查询相关数据，并把这些数据放入到数据缓冲区中（buffer cache）。
其中，若数据存在于db buffer，其可用性检查方式为：查看db buffer块的头部是否有事务，如果有事务，则从回滚段中读取数据；如果没有事务，则比较select的scn和db buffer块头部的scn，如果前者小于后者，仍然要从回滚段中读取数据；如果前者大于后者，说明这是一非脏缓存，可以直接读取这个db buffer块的中内容。
对于DML语句（insert、delete、update）：
1）检查所需的数据库是否已经被读取到缓冲区缓存中。如果已经存在缓冲区缓存，则直接执行步骤3；
2）若所需的数据库并不在缓冲区缓存中，则服务器将数据块从数据文件读取到缓冲区缓存中；
3）对想要修改的表取得的数据行锁定（Row Exclusive Lock），之后对所需要修改的数据行取得独占锁；
4）将数据的Redo记录复制到redo log buffer；
5）产生数据修改的undo数据；
6）修改db buffer；
7）dbwr将修改写入数据文件；
其中，第2步，服务器将数据从数据文件读取到db buffer经经历以下步骤：
1）首先服务器进程将在表头部请求TM锁（保证此事务执行过程其他用户不能修改表的结构），如果成功加TM锁，再请求一些行级锁（TX锁），如果TM、TX锁都成功加锁，那么才开始从数据文件读数据。
2）在读数据之前，要先为读取的文件准备好buffer空间。服务器进程需要扫描LRU list寻找free db buffer，扫描的过程中，服务器进程会把发现的所有已经被修改过的db buffer注册到dirty list中。如果free db buffer及非脏数据块缓冲区不足时，会触发dbwr将dirty buffer中指向的缓冲块写入数据文件，并且清洗掉这些缓冲区来腾出空间缓冲新读入的数据。
3）找到了足够的空闲buffer，服务器进程将从数据文件中读入这些行所在的每一个数据块（db block）（DB BLOCK是ORACLE的最小操作单元，即使你想要的数据只是DB BLOCK中很多行中的一行或几行，ORACLE也会把这个DB BLOCK中的所有行都读入Oracle DB BUFFER中）放入db buffer的空闲的区域或者覆盖已被挤出LRU list的非脏数据块缓冲区，并且排列在LRU列表的头部，也就是在数据块放入db buffer之前也是要先申请db buffer中的锁存器，成功加锁后，才能读数据到db buffer。
若数据块已经存在于db buffer cache（有时也称db buffer或db cache），即使在db buffer中找到一个没有事务，而且SCN比自己小的非脏缓存数据块，服务器进程仍然要到表的头部对这条记录申请加锁，加锁成功才能进行后续动作，如果不成功，则要等待前面的进程解锁后才能进行动作（这个时候阻塞是tx锁阻塞）。
在记redo日志时，其具体步骤如下：
1）数据被读入到db buffer后，服务器进程将该语句所影响的并被读入db buffer中的这些行数据的rowid及要更新的原值和新值及scn等信息从PGA逐条的写入redo log buffer中。在写入redo log buffer之前也要事先请求redo log buffer的锁存器，成功加锁后才开始写入。
2）当写入达到redo log buffer大小的三分之一或写入量达到1M或超过三秒后或发生检查点时或者dbwr之前发生，都会触发lgwr进程把redo log buffer的数据写入磁盘上的redo file文件中（这个时候会产生log file sync等待事件）。
3）已经被写入redo file的redo log buffer所持有的锁存器会被释放，并可被后来的写入信息覆盖，redo log buffer是循环使用的。Redo file也是循环使用的，当一个redo file写满后，lgwr进程会自动切换到下一redo file（这个时候可能出现log file switch（check point complete）等待事件）。如果是归档模式，归档进程还要将前一个写满的redo file文件的内容写到归档日志文件中（这个时候可能出现log file switch（archiving needed）。
在为事务建立undo信息时，其具体步骤如下：
1）在完成本事务所有相关的redo log buffer之后，服务器进程开始改写这个db buffer的块头部事务列表并写入scn（一开始scn是写在redo log buffer中的，并未写在db buffer）。
2）然后包含这个块的头部事务列表及scn信息的数据副本放入回滚段中，将这时回滚段中的信息称为数据块的“前映像”，这个“前映像”用于以后的回滚、恢复和一致性读。（回滚段可以存储在专门的回滚表空间中，这个表空间由一个或多个物理文件组成，并专用于回滚表空间，回滚段也可在其它表空间中的数据文件中开辟）。
在修改信息写入数据文件时，其具体步骤如下：
1）改写db buffer块的数据内容，并在块的头部写入回滚段的地址。
2）将db buffer指针放入dirty list。如果一个行数据多次update而未commit，则在回滚段中将会有多个“前映像”，除了第一个“前映像”含有scn信息外，其他每个"前映像"的头部都有scn信息和"前前映像"回滚段地址。一个update只对应一个scn，然后服务器进程将在dirty list中建立一条指向此db buffer块的指针（方便dbwr进程可以找到dirty list的db buffer数据块并写入数据文件中）。接着服务器进程会从数据文件中继续读入第二个数据块，重复前一数据块的动作，数据块的读入、记日志、建立回滚段、修改数据块、放入dirty list。
3）当dirty queue的长度达到阀值（一般是25%），服务器进程将通知dbwr把脏数据写出，就是释放db buffer上的锁存器，腾出更多的free db buffer。前面一直都是在说明oracle一次读一个数据块，其实oracle可以一次读入多个数据块（db_file_multiblock_read_count来设置一次读入块的个数）
当执行commit时，具体步骤如下：
1）commit触发lgwr进程，但不强制dbwr立即释放所有相应db buffer块的锁。也就是说有可能虽然已经commit了，但在随后的一段时间内dbwr还在写这条sql语句所涉及的数据块。表头部的行锁并不在commit之后立即释放，而是要等dbwr进程完成之后才释放，这就可能会出现一个用户请求另一用户已经commit的资源不成功的现象。
2）从Commit和dbwr进程结束之间的时间很短，如果恰巧在commit之后，dbwr未结束之前断电，因为commit之后的数据已经属于数据文件的内容，但这部分文件没有完全写入到数据文件中。所以需要前滚。由于commit已经触发lgwr，这些所有未来得及写入数据文件的更改会在实例重启后，由smon进程根据重做日志文件来前滚，完成之前commit未完成的工作（即把更改写入数据文件）。
3）如果未commit就断电了，因为数据已经在db buffer更改了，没有commit，说明这部分数据不属于数据文件。由于dbwr之前触发lgwr也就是只要数据更改，（肯定要先有log）所有dbwr在数据文件上的修改都会被先一步记入重做日志文件，实例重启后，SMON进程再根据重做日志文件来回滚。
其实smon的前滚回滚是根据检查点来完成的，当一个全部检查点发生的时候，首先让LGWR进程将redologbuffer中的所有缓冲（包含未提交的重做信息）写入重做日志文件，然后让dbwr进程将dbbuffer已提交的缓冲写入数据文件（不强制写未提交的）。然后更新控制文件和数据文件头部的SCN，表明当前数据库是一致的，在相邻的两个检查点之间有很多事务，有提交和未提交的。
当执行rollback时，具体步骤如下：
服务器进程会根据数据文件块和db buffer中块的头部的事务列表和SCN以及回滚段地址找到回滚段中相应的修改前的副本，并且用这些原值来还原当前数据文件中已修改但未提交的改变。如果有多个”前映像“，服务器进程会在一个“前映像”的头部找到“前前映像”的回滚段地址，一直找到同一事务下的最早的一个“前映像”为止。一旦发出了commit，用户就不能rollback，这使得commit后dbwr进程还没有全部完成的后续动作得到了保障。
第五步：提取数据
当语句执行完成之后，查询到的数据还是在服务器进程中，还没有被传送到客户端的用户进程。所以，在服务器端的进程中，有一个专门负责数据提取的一段代码。他的作用就是把查询到的数据结果返回给用户端进程，从而完成整个查询动作。
从这整个查询处理过程中，我们在数据库开发或者应用软件开发过程中，需要注意以下几点：
一是要了解数据库缓存跟应用软件缓存是两码事情。数据库缓存只有在数据库服务器端才存在，在客户端是不存在的。只有如此，才能够保证数据库缓存中的内容跟数据库文件的内容一致。才能够根据相关的规则，防止数据脏读、错读的发生。而应用软件所涉及的数据缓存，由于跟数据库缓存不是一码事情，所以，应用软件的数据缓存虽然可以提高数据的查询效率，但是，却打破了数据一致性的要求，有时候会发生脏读、错读等情况的发生。所以，有时候，在应用软件上有专门一个功能，用来在必要的时候清除数据缓存。不过，这个数据缓存的清除，也只是清除本机上的数据缓存，或者说，只是清除这个应用程序的数据缓存，而不会清除数据库的数据缓存。
二是绝大部分SQL语句都是按照这个处理过程处理的。我们DBA或者基于Oracle数据库的开发人员了解这些语句的处理过程，对于我们进行涉及到SQL语句的开发与调试，是非常有帮助的。有时候，掌握这些处理原则，可以减少我们排错的时间。特别要注意，数据库是把数据查询权限的审查放在语法语义的后面进行检查的。所以，有时会若光用数据库的权限控制原则，可能还不能满足应用软件权限控制的需要。此时，就需要应用软件的前台设置，实现权限管理的要求。而且，有时应用数据库的权限管理，也有点显得繁琐，会增加服务器处理的工作量。因此，对于记录、字段等的查询权限控制，大部分程序涉及人员喜欢在应用程序中实现，而不是在数据库上实现。
Oracle SQL语句执行顺序
(8)SELECT (9) DISTINCT (11) <select_list>
(1) FROM <left_table>
(3) <join_type> JOIN <right_table>
(2) ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(6) WITH {CUBE | ROLLUP}
(7) HAVING <having_condition>
(10) ORDER BY <order_by_list>
1）FROM：对FROM子句中的表执行笛卡尔积(交叉联接)，生成虚拟表VT1。
2）ON：对VT1应用ON筛选器，只有那些使为真才被插入到TV2。
3）OUTER (JOIN):如果指定了OUTER JOIN(相对于CROSS JOIN或INNER JOIN)，保留表中未找到匹配的行将作为外部行添加到VT2，生成TV3。如果FROM子句包含两个以上的表，则对上一个联接生成的结果表和下一个表重复执行步骤1到步骤3，直到处理完所有的表位置。
4）WHERE：对TV3应用WHERE筛选器，只有使为true的行才插入TV4。
5）GROUP BY：按GROUP BY子句中的列列表对TV4中的行进行分组，生成TV5。
6）CUTE|ROLLUP：把超组插入VT5，生成VT6。
7）HAVING：对VT6应用HAVING筛选器，只有使为true的组插入到VT7。
8）SELECT：处理SELECT列表，产生VT8。
9）DISTINCT：将重复的行从VT8中删除，产品VT9。
10）ORDER BY：将VT9中的行按ORDER BY子句中的列列表顺序，生成一个游标(VC10)，生成表TV11，并返回给调用者。
以上每个步骤都会产生一个虚拟表，该虚拟表被用作下一个步骤的输入。这些虚拟表对调用者(客户端应用程序或者外部查询)不可用。只有最后一步生成的表才会会给调用者。如果没有在查询中指定某一个子句，将跳过相应的步骤。
❺ sql运行问题
sql运行问题？
数据库运行过程中常见的故障有3类：事物故障、系统故障、介质故障。
恢复策略：
1、事物故障：
发生事务故障时，被迫中断的事务可能已对数据库进行丁修改，为了消除该事务对数据库的影响，要利用日志文件中所记载的信息，强行回滚该事务，将数据库恢复到修改前的初始状态。
为此，要检查日志文件中由这些事务所引起的发生变化的记录，取消这些没有完成的事务所做的一切改变，这类恢复操作称为事务撤销。
2、系统故障：
系统故障的恢复要完成两方面的工作，既要撤销所有末完成的事务，还要重做所有已提交的事务，这样才能将数据库真正恢复到一致的状态。
3、介质故障：
介质故障比事务故障和系统故障发生的可能性要小，但这是最严重的一种故障，破坏性很大，磁盘上的物理数据和日志文件可能被破坏，这需要装入发生介质故障前最新的后备数据库副本，然后利用日志文件重做该副本后所运行的所有事务。
“数据故障恢复”和“完整性约束”、“并发控制”一样，都是数据库数据保护机制中的一种完整性控制。所有的系统都免不了会发生故障，有可能是硬件失灵，有可能是软件系统崩溃，也有可能是其他外界的原因，比如断电等等。
数据库运行的突然中断会使数据库处在一个错误的状态，而且故障排除后没有办法让系统精确地从断点继续执行下去。这就要求DBMS要有一套故障后的数据恢复机构，保证数据库能够回复到一致的、正确地状态去。
❻ SQL语句执行过程详解
SQL语句执行过程详解
一条sql，plsql的执行到底是怎样执行的呢？
一、SQL语句执行原理：
第一步:客户端把语句发给服务器端执行当我们在客户端执行 select 语句时,客户端会把这条 SQL 语句发送给服务器端,让服务器端的
进程来处理这语句。也就是说,Oracle 客户端是不会做任何的操作,他的主要任务就是把客户端产生
的一些 SQL 语句发送给服务器端。虽然在客户端也有一个数据库进程,但是,这个进程的作用跟服务器
上的进程作用事不相同的。服务器上的数据库进程才会对SQL 语句进行相关的处理。不过,有个问题需
要说明,就是客户端的进程跟服务器的进程是一一对应的。也就是说,在客户端连接上服务器后,在客户
端与服务器端都会形成一个进程,客户端上的我们叫做客户端进程;而服务器上的我们叫做服务器进程。
第二步:语句解析
当客户端把 SQL 语句传送到服务器后,服务器进程会对该语句进行解析。同理,这个解析的工作,
也是在服务器端所进行的。虽然这只是一个解析的动作,但是,其会做很多“小动作”。
1. 查询高速缓存(library cache)。服务器进程在接到客户端传送过来的 SQL 语句时,不
会直接去数据库查询。而是会先在数据库的高速缓存中去查找,是否存在相同语句的执行计划。如果在
数据高速缓存中,则服务器进程就会直接执行这个 SQL 语句,省去后续的工作。所以,采用高速数据缓
存的话,可以提高 SQL 语句的查询效率。一方面是从内存中读取数据要比从硬盘中的数据文件中读取
数据效率要高,另一方面,也是因为这个语句解析的原因。
不过这里要注意一点,这个数据缓存跟有些客户端软件的数据缓存是两码事。有些客户端软件为了
提高查询效率,会在应用软件的客户端设置数据缓存。由于这些数据缓存的存在,可以提高客户端应用软
件的查询效率。但是,若其他人在服务器进行了相关的修改,由于应用软件数据缓存的存在,导致修改的
数据不能及时反映到客户端上。从这也可以看出,应用软件的数据缓存跟数据库服务器的高速数据缓存
不是一码事。
2. 语句合法性检查(data dict cache)。当在高速缓存中找不到对应的 SQL 语句时,则服
务器进程就会开始检查这条语句的合法性。这里主要是对 SQL 语句的语法进行检查,看看其是否合乎
语法规则。如果服务器进程认为这条 SQL 语句不符合语法规则的时候,就会把这个错误信息,反馈给客
户端。在这个语法检查的过程中,不会对 SQL 语句中所包含的表名、列名等等进行 SQL 他只是语法
上的检查。
3. 语言含义检查(data dict cache)。若 SQL 语句符合语法上的定义的话,则服务器进程
接下去会对语句中的字段、表等内容进行检查。看看这些字段、表是否在数据库中。如果表名与列名不
准确的话,则数据库会就会反馈错误信息给客户端。所以,有时候我们写 select 语句的时候,若语法
与表名或者列名同时写错的话,则系统是先提示说语法错误,等到语法完全正确后,再提示说列名或表名
错误。
4. 获得对象解析锁(control structer)。当语法、语义都正确后,系统就会对我们需要查询
的对象加锁。这主要是为了保障数据的一致性,防止我们在查询的过程中,其他用户对这个对象的结构发
生改变。
5. 数据访问权限的核对(data dict cache)。当语法、语义通过检查之后,客户端还不一定
能够取得数据。服务器进程还会检查,你所连接的用户是否有这个数据访问的权限。若你连接上服务器
的用户不具有数据访问权限的话,则客户端就不能够取得这些数据。有时候我们查询数据的时候,辛辛苦
苦地把 SQL 语句写好、编译通过,但是,最后系统返回个 “没有权限访问数据”的错误信息,让我们气
半死。这在前端应用软件开发调试的过程中,可能会碰到。所以,要注意这个问题,数据库服务器进程先
检查语法与语义,然后才会检查访问权限。
6. 确定最佳执行计划 ?。当语句与语法都没有问题,权限也匹配的话,服务器进程还是不会直接对
数据库文件进行查询。服务器进程会根据一定的规则,对这条语句进行优化。不过要注意,这个优化是有
限的。一般在应用软件开发的过程中,需要对数据库的 sql 语言进行优化,这个优化的作用要大大地大
于服务器进程的自我优化。所以,一般在应用软件开发的时候,数据库的优化是少不了的。当服务器进程
的优化器确定这条查询语句的最佳执行计划后,就会将这条 SQL 语句与执行计划保存到数据高速缓存
(library cache)。如此的话,等以后还有这个查询时,就会省略以上的语法、语义与权限检查的步骤,
而直接执行 SQL 语句,提高 SQL 语句处理效率。
第三步:语句执行
语句解析只是对 SQL 语句的语法进行解析,以确保服务器能够知道这条语句到底表达的是什么意
思。等到语句解析完成之后,数据库服务器进程才会真正的执行这条 SQL 语句。这个语句执行也分两
种情况。
一是若被选择行所在的数据块已经被读取到数据缓冲区的话,则服务器进程会直接把这个数据传递
给客户端,而不是从数据库文件中去查询数据。
若数据不在缓冲区中,则服务器进程将从数据库文件中查询相关数据,并把这些数据放入到数据缓冲
区中(buffer cache)。
第四步:提取数据
当语句执行完成之后,查询到的数据还是在服务器进程中,还没有被传送到客户端的用户进程。所以,
在服务器端的进程中,有一个专门负责数据提取的一段代码。他的作用就是把查询到的数据结果返回给
用户端进程,从而完成整个查询动作。从这整个查询处理过程中,我们在数据库开发或者应用软件开发过
程中,需要注意以下几点:
一是要了解数据库缓存跟应用软件缓存是两码事情。数据库缓存只有在数据库服务器端才存在,在
客户端是不存在的。只有如此,才能够保证数据库缓存中的内容跟数据库文件的内容一致。才能够根据
相关的规则,防止数据脏读、错读的发生。而应用软件所涉及的数据缓存,由于跟数据库缓存不是一码事
情,所以,应用软件的数据缓存虽然可以提高数据的查询效率,但是,却打破了数据一致性的要求,有时候
会发生脏读、错读等情况的发生。所以,有时候,在应用软件上有专门一个功能,用来在必要的时候清除
数据缓存。不过,这个数据缓存的清除,也只是清除本机上的数据缓存,或者说,只是清除这个应用程序
的数据缓存,而不会清除数据库的数据缓存。
二是绝大部分 SQL 语句都是按照这个处理过程处理的。我们 DBA 或者基于 Oracle 数据库的
开发人员了解这些语句的处理过程,对于我们进行涉及到 SQL 语句的开发与调试,是非常有帮助的。有
时候,掌握这些处理原则,可以减少我们排错的时间。特别要注意,数据库是把数据查询权限的审查放在
语法语义的后面进行检查的。所以,有时会若光用数据库的权限控制原则,可能还不能满足应用软件权限
控制的需要。此时,就需要应用软件的前台设置,实现权限管理的要求。而且,有时应用数据库的权限管
理,也有点显得繁琐,会增加服务器处理的工作量。因此,对于记录、字段等的查询权限控制,大部分程
序涉及人员喜欢在应用程序中实现,而不是在数据库上实现。
DBCC DROPCLEANBUFFERS
从缓冲池中删除所有清除缓冲区。
DBCC FREEPROCCACHE
从过程缓存中删除所有元素。
DBCC FREESYSTEMCACHE
从所有缓存中释放所有未使用的缓存条目
SQL语句中的函数、关键字、排序等执行顺序:
1. FROM 子句返回初始结果集。
2. WHERE 子句排除不满足搜索条件的行。
3. GROUP BY 子句将选定的行收集到 GROUP BY 子句中各个唯一值的组中。
4. 选择列表中指定的聚合函数可以计算各组的汇总值。
5. 此外,HAVING 子句排除不满足搜索条件的行。
6. 计算所有的表达式;
7. 使用 order by 对结果集进行排序。
8. 查找你要搜索的字段。
二、SQL语句执行完整过程：
1.用户进程提交一个 sql 语句:
update temp set a=a*2，给服务器进程。
2.服务器进程从用户进程把信息接收到后,在 PGA 中就要此进程分配所需内存,存储相关的信息,如在会
话内存存储相关的登录信息等。
3.服务器进程把这个 sql 语句的字符转化为 ASCII 等效数字码,接着这个 ASCII 码被传递给一个
HASH 函数,并返回一个 hash 值,然后服务器进程将到shared pool 中的 library cache 中去查找是否存在相
同的 hash 值,如果存在,服务器进程将使用这条语句已高速缓存在 SHARED POOL 的library cache 中的已
分析过的版本来执行。
4.如果不存在,服务器进程将在 CGA 中,配合 UGA 内容对 sql,进行语法分析,首先检查语法的正确性,接
着对语句中涉及的表,索引,视图等对象进行解析,并对照数据字典检查这些对象的名称以及相关结构，并根据
ORACLE 选用的优化模式以及数据字典中是否存在相应对象的统计数据和是否使用了存储大纲来生成一个
执行计划或从存储大纲中选用一个执行计划,然后再用数据字典核对此用户对相应对象的执行权限,最后生成
一个编译代码。
5.ORACLE 将这条 sql 语句的本身实际文本、HASH 值、编译代码、与此语名相关联的任何统计数据
和该语句的执行计划缓存在 SHARED POOL 的 library cache中。服务器进程通过 SHARED POOL 锁存
器(shared pool latch)来申请可以向哪些共享 PL/SQL 区中缓存这此内容,也就是说被SHARED POOL 锁存
器锁定的 PL/SQL 区中的块不可被覆盖,因为这些块可能被其它进程所使用。
6.在 SQL 分析阶段将用到 LIBRARY
CACHE,从数据字典中核对表、视图等结构的时候,需要将数据
字典从磁盘读入 LIBRARY
CACHE,因此,在读入之前也要使用LIBRARY
CACHE 锁存器(library cache
pin,library cache lock)来申请用于缓存数据字典。 到现在为止,这个 sql 语句已经被编译成可执行的代码了,
但还不知道要操作哪些数据,所以服务器进程还要为这个 sql 准备预处理数据。
7.首先服务器进程要判断所需数据是否在 db buffer 存在,如果存在且可用,则直接获取该数据,同时根据
LRU 算法增加其访问计数;如果 buffer 不存在所需数据,则要从数据文件上读取首先服务器进程将在表头部
请求 TM 锁(保证此事务执行过程其他用户不能修改表的结构),如果成功加 TM 锁,再请求一些行级锁(TX
锁),如果 TM、TX 锁都成功加锁,那么才开始从数据文件读数据,在读数据之前,要先为读取的文件准备好
buffer 空间。服务器进程需要扫面 LRU list 寻找 free db buffer,扫描的过程中,服务器进程会把发现的所有
已经被修改过的 db buffer 注册到 dirty list 中, 这些 dirty buffer 会通过 dbwr 的触发条件,随后会被写出到
数据文件,找到了足够的空闲 buffer,就可以把请求的数据行所在的数据块放入到 db buffer 的空闲区域或者
覆盖已经被挤出 LRU list 的非脏数据块缓冲区,并排列在 LRU list 的头部,也就是在数据块放入 DB
BUFFER 之前也是要先申请 db buffer 中的锁存器,成功加锁后,才能读数据到 db buffer。
8.记日志 现在数据已经被读入到 db buffer 了,现在服务器进程将该语句所影响的并被读
入 db buffer 中的这些行数据的 rowid 及要更新的原值和新值及 scn 等信息从 PGA 逐条的写入 redo log
buffer 中。在写入 redo log buffer 之前也要事先请求 redo log buffer 的锁存器,成功加锁后才开始写入,当
写入达到 redo log buffer 大小的三分之一或写入量达到 1M 或超过三秒后或发生检查点时或者 dbwr 之前
发生,都会触发 lgwr 进程把 redo log buffer 的数据写入磁盘上的 redo file 文件中(这个时候会产生log file
sync 等待事件)
已经被写入 redofile 的 redo log buffer 所持有的锁存器会被释放,并可被后来的写入信息覆盖,
redo log buffer是循环使用的。Redo file 也是循环使用的,当一个 redo file 写满后,lgwr 进程会自动切换到
下一 redo file(这个时候可能出现 log fileswitch(checkpoint complete)等待事件)。如果是归档模式,归档进
程还要将前一个写满的 redo file 文件的内容写到归档日志文件中(这个时候可能出现 log file
switch(archiving needed)。
9.为事务建立回滚段 在完成本事务所有相关的 redo log buffer 之后,服务器进程开始改写这个 db buffer
的块头部事务列表并写入 scn,然后 包含这个块的头部事务列表及 scn 信息的数据副本放入回滚段中,将
这时回滚段中的信息称为数据块的“前映像“,这个”前映像“用于以后的回滚、恢复和一致性读。(回滚段可以
存储在专门的回滚表空间中,这个表空间由一个或多个物理文件组成,并专用于回滚表空间,回滚段也可在其它
表空间中的数据文件中开辟。
10.本事务修改数据块 准备工作都已经做好了,现在可以改写 db buffer 块的数据内容了,并在块的头部写
入回滚段的地址。
11.放入 dirty list 如果一个行数据多次 update 而未 commit,则在回滚段中将会有多个“前映像“,除了第
一个”前映像“含有 scn 信息外,其他每个“前映像“的头部都有 scn 信息和“前前映像”回滚段地址。一个
update 只对应一个 scn,然后服务器进程将在 dirty list 中建立一
条指向此 db buffer 块的指针(方便 dbwr 进程可以找到 dirty list 的 db buffer 数据块并写入数据文件中)。
接着服务器进程会从数据文件中继续读入第二个数据块,重复前一数据块的动作,数据块的读入、记日志、建
立回滚段、修改数据块、放入 dirty list。当 dirty queue 的长度达到阀值(一般是 25%),服务器进程将通知
dbwr 把脏数据写出,就是释放 db buffer 上的锁存器,腾出更多的 free db buffer。前面一直都是在说明
oracle 一次读一个数据块,其实 oracle 可以一次读入多个数据块(db_file_multiblock_read_count 来设置一
次读入块的个数)
说明:
在预处理的数据已经缓存在 db buffer 或刚刚被从数据文件读入到 db buffer 中,就要根据 sql 语句
的类型来决定接下来如何操作。
1>如果是 select 语句,则要查看 db buffer 块的头部是否有事务,如果有事务,则从回滚段中读取数据;如
果没有事务,则比较 select 的 scn 和 db buffer 块头部的 scn,如果前者小于后者,仍然要从回滚段中读取数据;
如果前者大于后者,说明这是一非脏缓存,可以直接读取这个 db buffer 块的中内容。
2>如果是 DML 操作,则即使在 db buffer 中找到一个没有事务,而且 SCN 比自己小的非脏
缓存数据块,服务器进程仍然要到表的头部对这条记录申请加锁,加锁成功才能进行后续动作,如果不成功,则要
等待前面的进程解锁后才能进行动作(这个时候阻塞是 tx 锁阻塞)。
用户 commit 或 rollback 到现在为止,数据已经在 db buffer 或数据文件中修改完
成,但是否要永久写到数文件中,要由用户来决定 commit(保存更改到数据文件) rollback 撤销数据的更改)。
1.用户执行 commit 命令
只有当 sql 语句所影响的所有行所在的最后一个块被读入 db buffer 并且重做信息被写入 redo log
buffer(仅指日志缓冲区,而不包括日志文件)之后,用户才可以发去 commit 命令,commit 触发 lgwr 进程,但不
强制立即 dbwr来释放所有相应 db buffer 块的锁(也就是no-force-at-commit,即提交不强制写),也就是说有
可能虽然已经 commit 了,但在随后的一段时间内 dbwr 还在写这条 sql 语句所涉及的数据块。表头部的行锁
并不在 commit 之后立即释放,而是要等 dbwr 进程完成之后才释放,这就可能会出现一个用户请求另一用户
已经 commit 的资源不成功的现象。
A .从 Commit 和 dbwr 进程结束之间的时间很短,如果恰巧在 commit 之后,dbwr 未结束之前断电,因为
commit 之后的数据已经属于数据文件的内容,但这部分文件没有完全写入到数据文件中。所以需要前滚。由
于 commit 已经触发 lgwr,这些所有未来得及写入数据文件的更改会在实例重启后,由 smon 进程根据重做日
志文件来前滚,完成之前 commit 未完成的工作(即把更改写入数据文件)。
B.如果未 commit 就断电了,因为数据已经在 db buffer 更改了,没有 commit,说明这部分数据不属于数
据文件,由于 dbwr 之前触发 lgwr 也就是只要数据更改,(肯定要先有 log) 所有 DBWR,在数据文件上的修改
都会被先一步记入重做日志文件,实例重启后,SMON 进程再根据重做日志文件来回滚。
其实 smon 的前滚回滚是根据检查点来完成的,当一个全部检查点发生的时候,首先让 LGWR 进程将
redo log buffer 中的所有缓冲(包含未提交的重做信息)写入重做日志文件,然后让 dbwr 进程将 db buffer 已
提交的缓冲写入数据文件(不强制写未提交的)。然后更新控制文件和数据文件头部的 SCN,表明当前数据库
是一致的,在相邻的两个检查点之间有很多事务,有提交和未提交的。
像前面的前滚回滚比较完整的说法是如下的说明:
A.发生检查点之前断电,并且当时有一个未提交的改变正在进行,实例重启之后,SMON 进程将从上一个
检查点开始核对这个检查点之后记录在重做日志文件中已提交的和未提交改变,因为
dbwr 之前会触发 lgwr,所以 dbwr 对数据文件的修改一定会被先记录在重做日志文件中。因此,断电前被
DBWN 写进数据文件的改变将通过重做日志文件中的记录进行还原,叫做回滚,
B. 如果断电时有一个已提交,但 dbwr 动作还没有完全完成的改变存在,因为已经提交,提交会触发 lgwr
进程,所以不管 dbwr 动作是否已完成,该语句将要影响的行及其产生的结果一定已经记录在重做日志文件中
了,则实例重启后,SMON 进程根据重做日志文件进行前滚.
实例失败后用于恢复的时间由两个检查点之间的间隔大小来决定,可以通个四个参数设置检查点执行的频
率:
Log_checkpoint_interval:
决定两个检查点之间写入重做日志文件的系统物理块(redo blocks)
的大小,默认值是 0,无限制。
log_checkpoint_timeout:
两 个 检 查 点 之 间 的 时 间 长 度（秒）默 认 值 1800s。
fast_start_io_target:
决定了用于恢复时需要处理的块的多少,默认值是 0,无限制。
fast_start_mttr_target:
直接决定了用于恢复的时间的长短,默认值是 0,无限制(SMON 进程执行的前滚
和回滚与用户的回滚是不同的,SMON 是根据重做日志文件进行前滚或回滚,而用户的回滚一定是根据回滚段
的内容进行回滚的。
在这里要说一下回滚段存储的数据,假如是 delete 操作,则回滚段将会记录整个行的数据,假如是 update,
则回滚段只记录被修改了的字段的变化前的数据(前映像),也就是没有被修改的字段是不会被记录的,假如是
insert,则回滚段只记录插入记录的 rowid。 这样假如事务提交,那回滚段中简单标记该事务已经提交;假如是
回退,则如果操作是 delete,回退的时候把回滚段中数据重新写回数据块,操作如果是 update,则把变化前数据
修改回去,操作如果是 insert,则根据记录的 rowid 把该记录删除。
2.如果用户 rollback。
则服务器进程会根据数据文件块和 DB BUFFER 中块的头部的事务列表和 SCN 以及回滚段地址找到
回滚段中相应的修改前的副本,并且用这些原值来还原当前数据文件中已修改但未提交的改变。如果有多个
“前映像”,服务器进程会在一个“前映像”的头部找到“前前映像”的回滚段地址,一直找到同一事务下的最早的
一个“前映像”为止。一旦发出了 COMMIT,用户就不能rollback,这使得 COMMIT 后 DBWR 进程还没有
全部完成的后续动作得到了保障。到现在为例一个事务已经结束了。
说明:
TM 锁:
符合 lock 机制的,用于保护对象的定义不被修改。 TX 锁:
这个锁代表一个事务,是行
级锁,用数据块头、数据记录头的一些字段表示,也是符合 lock 机制,有 resource structure、lock
structure、enqueue 算法。
❼ SQL为什么预估执行计划与真实执行计划会有差异
第一种，就是优化器的输出结果，成为预估执行计划，里面的操作符或者步骤都是逻辑步骤，因为他们代表着执行计划在优化器中的视图，但是并不表现在实际执行中物理层面的发生。
另外一种计划是表示查询实际执行的输出结果。这种类型叫做实际执行计划，表示查询在实际执行时发生的事情。
这两个计划显示独立的不同的结果集，但是可以看出有巨大的相同之处。大部分情况下，相同的开销的相同的操作符会出现在两种执行计划中。但是当发生重编译，SQLServer会删除计划缓存中的计划并重建它，此时两者就会有明显的差异。这种情况通常发生于统计信息的更改，或存储引擎在处理查询时发生的其他事情。
预估执行计划是存放在计划缓存中的计划，所以对于实际执行计划，只能通过捕捉查询运行的时候产生的执行计划。预估计划从不直接访问数据，但是它对大型的、复杂的、可能运行很久的查询分析相当有效。但是实际计划是首选的，因为它能显示很多运行过程中重要的统计信息如特定操作符实际访问的行数。通常情况下，这种额外的信息使得实际计划成为最常用的方式，但是预估计划机器重要，特别是因为可以从计划缓存中获取。
❽ 存储过程作用是什么，优缺点以及适用范围是什么
中用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是SQL 语句和可选控制流语句的预编译集合，以一个名称存储并作为一个单元处理。存储过程存储在数据库内，可由应用程序通过一个调用执行，而且允许用户声明变量、有条件执行以及其它强大的编程功能。存储过程在创建时即在服务器上进行编译，所以执行起来比单个SQL语句快。 存储过程的优点： 1.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。 2.当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。 3.存储过程可以重复使用,可减少数据库开发人员的工作量 4.安全性高,可设定只有某此用户才具有对指定存储过程的使用权 缺点：1.如果更改范围大到需要对输入存储过程的参数进行更改，或者要更改由其返回的数据，则您仍需要更新程序集中的代码以添加参数、更新 GetValue() 调用，等等，这时候估计比较繁琐了。
2.可移植性差
由于存储过程将应用程序绑定到 SQL Server，因此使用存储过程封装业务逻辑将限制应用程序的可移植性。 优点： 1.由于应用程序随着时间推移会不断更改，增删功能，T－SQL过程代码会变得更复杂，StoredProcere为封装此代码提供了一个替换位置。 2.执行计划（存储过程在首次运行时将被编译，这将产生一个执行计划－－ 实际上是 Microsoft SQL Server为在存储过程中获取由 T-SQL 指定的结果而必须采取的步骤的记录。）缓存改善性能。 ........但sql server新版本，执行计划已针对所有 T-SQL 批处理进行了缓存，而不管它们是否在存储过程中，所以没比较优势了。 3.存储过程可以用于降低网络流量，存储过程代码直接存储于数据库中，所以不会产生大量T-sql语句的代码流量。 4.使用存储过程使您能够增强对执行计划的重复使用，由此可以通过使用远程过程调用 (RPC) 处理服务器上的存储过程而提高性能。RPC 封装参数和调用服务器端过程的方式使引擎能够轻松地找到匹配的执行计划，并只需插入更新的参数值。 5.可维护性高，更新存储过程通常比更改、测试以及重新部署程序集需要较少的时间和精力。 6.代码精简一致，一个存储过程可以用于应用程序代码的不同位置。 7.更好的版本控制，通过使用 Microsoft Visual SourceSafe 或某个其他源代码控制工具，您可以轻松地恢复到或引用旧版本的存储过程。 8.增强安全性： a、通过向用户授予对存储过程（而不是基于表）的访问权限，它们可以提供对特定数据的访问； b、提高代码安全，防止 SQL注入（但未彻底解决，例如，将数据操作语言－－DML，附加到输入参数）； c、SqlParameter 类指定存储过程参数的数据类型，作为深层次防御性策略的一部分，可以验证用户提供的值类型（但也不是万无一失，还是应该传递至数据库前得到附加验证）。 缺点： 1.如果更改范围大到需要对输入存储过程的参数进行更改，或者要更改由其返回的数据，则您仍需要更新程序集中的代码以添加参数、更新 GetValue() 调用，等等，这时候估计比较繁琐了。 2.可移植性差 由于存储过程将应用程序绑定到 SQL Server，因此使用存储过程封装业务逻辑将限制应用程序的可移植性。
❾ 如何清除特定语句的执行计划缓存
应该是与执行计划有关，Oracle执行一个语句要先进行解析分解执行，这需要时间，并把相关结果存储，再次执行相同语句时就不需要进行解析，从而缩段时间。举个例子，比如需要多次在翻页查找某一行记录时，在第1次找时要一页页地去找，待找到这行记录时，就可以记住是在第几页，第2次找时就可以直接翻到。