oracle中的临时表

临时表通常用来保存一个事务或者会话期间的数据.
临时表中保存的数据是具有独立性的,只对各自会话可见,并且每个会话
都只能查询和修改属于此会话的数据,在对temporary table作dml操作时,
不需要申请锁资源,因此lock语句对于临时表来说是没有作用的.

在空间方面,在创建永久性表时通常是需要为表分配initial extent,但是对于
临时表是不需要的,临时表只是在使用的时候,根据数据来分配创建临时段.

对临时表的 DML 操作不会产生数据修改的重做日志,但是将产生被修改数据的撤销记录，

及撤销记录的重做日志.

我们来看一下临时表所产生的redo size情况.

SQL> select * from v$sesstat where sid=159 and statistic#=134;

       SID STATISTIC#      VALUE
---------- ---------- ----------
       159        134     929956

SQL> create global temporary table temp_ses on     
  2  commit preserve rows
  3  as
  4  select * from dba_objects;

Table created.

SQL> select * from v$sesstat where sid=159 and statistic#=134;

       SID STATISTIC#      VALUE
---------- ---------- ----------
       159        134     948420

再来看一下创建一个同样大小数据量的永久性表:
SQL> select * from v$sesstat where sid=142 and statistic#=134;

       SID STATISTIC#      VALUE
---------- ---------- ----------
       142        134       1432

SQL> create table pert as select * from dba_objects;

Table created.

SQL> select * from v$sesstat where sid=142 and statistic#=134;

       SID STATISTIC#      VALUE
---------- ---------- ----------
       142        134    5724360

SQL> select 5724360-1432 from dual;

5724360-1432
------------
     5722928

SQL>

可以看到两者的差距已经不仅仅是一个数据级了.

临时表一共有两种:
会话级别和事务级别的.

先来看一个会话级别的.

SQL> create global temporary table tmp_ses on commit preserve rows
  2  as  select * from dba_objects;

Table created.

SQL>

on commit preserve rows表明这一个基于会话的临时表,在会话断开以后.
所有数据都将被抹去.

SQL> create global temporary table tmp_trans on commit delete rows
  2  as select * from dba_objects;

Table created.

SQL>

on commit delete rows表示这是一个基于事务的临时表，在会话提交的时候，数据

就会被自动清除掉。

SQL> insert into tmp_trans select * from dba_objects;

49772 rows created.

SQL> select count(*) from tmp_trans;

  COUNT(*)
----------
     49772

SQL> commit;

Commit complete.

SQL> select count(*) from tmp_trans;

  COUNT(*)
----------
         0

这里可以看到在事务commit以后，临时表中的数据被全部清空。而这个清空的过程

是几乎不存在开销，oracle完成的仅仅是把临时段回收的一个动作。

临时段的分配

临时表使用临时段来分配数据，因此在创建临时表的时候，oracle并不会为其分配段，

而是在使用的时候才分配。我们可以使用v$sort_usage来观察某个临时表所占用的

空间大小。

SQL> select * from v$sort_usage;

no rows selected

SQL> insert into tmp_ses select * from dba_objects;

49772 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from tmp_ses;

  COUNT(*)
----------
     49772

SQL> select distinct sid from v$mystat;

       SID
----------
       142

SQL> select sid,serial# from v$session where sid=142;

       SID    SERIAL#
---------- ----------
       142        168

SQL>  select username,user,session_num,
  2  tablespace segtype,segfile#,segblk# ,blocks from v$sort_usage;

USERNAME   USER       SESSION_NUM SEGTYPE      SEGFILE#    SEGBLK#     BLOCKS
---------- ---------- ----------- ---------- ---------- ---------- ----------
TEST       TEST               168 TEMP              201       2313        768

这里可以看到，通过与v$session视图中的serial#相关联，可以得出某个会话的

所拥有的临时表中的数据的大小，当然，这里与永久表一样，在delete的时候

是不会释放出空间的：

SQL> select username,user,session_num,tablespace segtype,segfile#,segblk# ,blocks from v$sort_usage;

USERNAME   USER       SESSION_NUM SEGTYPE      SEGFILE#    SEGBLK#     BLOCKS
---------- ---------- ----------- ---------- ---------- ---------- ----------
TEST       TEST               168 TEMP              201       2313        768

SQL> delete tmp_ses;

19772 rows deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from tmp_ses;

  COUNT(*)
----------
         0

SQL> select username,user,session_num,tablespace segtype,segfile#,segblk# ,blocks from v$sort_usage;

USERNAME   USER       SESSION_NUM SEGTYPE      SEGFILE#    SEGBLK#     BLOCKS
---------- ---------- ----------- ---------- ---------- ---------- ----------
TEST       TEST               168 TEMP              201       2313        768

SQL>

SQL> truncate table tmp_ses;

Table truncated.

SQL>  select username,user,session_num,tablespace segtype,segfile#,segblk# ,blocks from v$sort_usage;

no rows selected

可以看到，在删除数据时，oracle采用了节省成本的方式，减少了不必要的开销。

关于临时表的事务，与事务相关的临时表中的数据可以被用户的事务及子事务访问。

但是这些数据不能被同一会话里的两个事务同时访问。不同会话中的事务可以同时

使用同一个事务相关的临时表。如果用户事务对临时表执行了 INSERT 操作，

在此之后此事务的子事务将不能使用这个临时表。

如果在子事务中对临时表执行了 INSERT 操作，临时表中已有的数据将被清除。

子事务结束后，父事务及其他子事务对此临时表访问权利将被恢复。
　

ora-00600[4400]错误的解决

4400错误通常在分布式事务中出现，如到这个错误不会对数据库产生重大的影响，

以下是一个生产库的实际解决的例子。

故障描述：
Errors in file .../udump/ora_15899_$ORACLESID.trc:
ORA-00600: 内部错误代码，自变量: [4400], [48], [], [], [], [], [], []
Tue Feb 5 17:07:18 2008
Errors in file .../bdump/reco_1297_$ORACLESIDl.trc:
ORA-02062: distributed recovery received DBID e23ac21, expected 232acd6
ARC1: media recovery disabled

原因及解决方法：
由于db_link断开，两阶段事务不能正常处理，查询dba_2pc_pending
LOCAL_TRAN_ID          GLOBAL_TRAN_ID                 STATE            MIX A TRAN_COMMENT         FAIL_TIME FORCE_TIM RETRY_TIM OS_USER
---------------------- ------------------------------ ---------------- --- - -------------------- --------- --------- --------- ----------------------------------------------------------------
OS_TERMINAL
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HOST                                                                                                                             DB_USERCOMMIT#
-------------------------------------------------------------------------------------------------------------------------------- ------------------------------ ----------------
192.168.1.1              jpb.6c5f8f5f.192.168.1.1     collecting       no                         05-FEB-08           05-FEB-08 Administrator
DGDX-WR
WORKGROUP\DGDX-WR                                                                                                                IBSS7740378455873

192.168.1.2             jpb.6c5f8f5f.192.168.1.2    collecting       no                         05-FEB-08           05-FEB-08 Administrator
DGDX-WR
WORKGROUP\DGDX-WR                                                                                                                IBSS7740389820541
192.168.1.112             jpb.6c5f8f5f.192.168.1.112    collecting       no                         05-FEB-08           05-FEB-08 Administrator
DGDX-WR
WORKGROUP\DGDX-WR                                                                                                                IBSS7740970651708

通过以下方法来清除这些事务：

SQL> execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('192.168.1.2');
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('192.168.1.1');
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('192.168.1.112');
PL/SQL procedure successfully completed.
SQL> commit;

RAC waits events ad interconnect statistics

The RAC events are listed next in the report if you are running RAC (multiple instances). As stated earlier, you need to run STATSPACK or AWR Report for each instance that you have.For statspack, you run the statspck.snap procedure and the spreport.sql script. on each node you want to monitor to compare to instances.One of the best methods to see if a node is operating efficiently is to compare the report from that node to one from another node that accesses the same database. It’s very important to remember that single-instance tuning should be performed before attempting to tune the processes that communicate via the cluster interconnect. In other words,tune the system in single instance before you move it to RAC.

Some of the top waits events that you may encounter are listed briefly next.

The top global cache(gc) waits to look out for include :

Gc current block busy: happens when an instance requests a CURR data block(wants to do some DML) and the block to be transferred is in use.

Gc buffer busy: A wait event that occurs whenever a session has to waiwt for an ongoing operation on the resource to complete because the block is in use. The process has to wait for a block to become available because another process is obtaining a resource for this block.

Gc cr request: This happens when one instance is waiting for blocks from another instance’s cache(sent via the interconnect). This wait says that the current instance can’t find a consistent read(CR) version of a block in the local cache. If the block is not in the remote cache, then a db file  sequential read wait will also follow this one. Tune the SQL that is causing large amounts of reads that get moved from node to node. Try to put users that are using the same blocks on the same instance so that blocks are not moved from instance to instance. Some non-Oracle application servers will move the same process from node to node looking for the fastest node(unaware that they are moving the same blocks from node to node). Pin these long processes to the same node. Potentially increase the size of the local cache if slow I/O combined with a small cache is the problem. Monitor v$cr_block_server to see if there is an issue like reading UNDO segments.  Correlated to the waits the values for p1, p2, p3=file,block,lenum(look in v$lock_element for row where lock_element_addr has the same value as lenum). Happens when an instance  requests a CR data block and the block to be transferred hasn’t arrived at the requesting instance.  This is one I see the most, and it’s usually because the SQL is poorly tuned and many index blocks are being moved back and forth between instance.

Figure shows the AWR Report RAC section. You can see that there are two instances in this cluster. You can also see things like the number of blocks send and received as well as how many of the blocks are being accessed in the local cache(99.1 percent) versus the disk or another instance. As you would guess , it is faster to access block in the local cache, but accessing one of the remote caches on one of the other nodes is almost always faster(given a fast enouth interconnect and no saturation of the interconnect) than going to disk.

     The following is another valuable query to derive session wait information. The instance_id lists the instance where the waiting session resides. The sid is te unique identifier for the waiting session(gv$session). The p1,p2 ,and p3 columns list event-specific information that may be useful for debugging. LAST_SQL lists

The last SQL executed by the waiting session.

Set numwidth 10

Col state format a7 tru

Col event format a25 tru

Col last_sql format a40 tru

Select sw.inst_id instance_id,

       sw.sid sid,

       sw.state state,

       sw.event event,

       Sw.seconds_in_wait seconds_wating,

       sw.p1,

       sw.p2,

       sw.p3,

       Sa.sql_text last_sql

  From gv$session_wait sw, gv$session s, gv$sqlarea sa

 Where sw.event not in

       ('rdbms ipc message', 'smon timer', 'pmon timer',

        'SQL*Net message from client',

        'lock manager wait for remote message', 'ges remote message',

        'gcs remote message', 'gcs for action', 'client message', 'pipe get',

        'PX Idle Wait', 'single-task message', 'listen endpoint status',

        'slave wait', 'wakeup time manager')

   And sw.SECONDS_IN_WAIT > 0

   And (sw.INST_ID = s.inst_id and sw.sid = s.sid)

   And (s.inst_id = sa.inst_id and s.sql_address = sa.address)

 Order by SECONDS_IN_WAIT desc;

Seconds_in_wait:  if wait_time=0, then seconds_in_wait is the seconds spend in the current wait condition. If wait_time>0, then seconds_in_wait is the seconds since the start of the last wait, and seconds_in_wait-wait_time/100 is the active seconds since the last wait ended.

RAC Statistics

Global Cache Load Profile

Global Cache Efficiency Percentages (Target local+remote 100%)

RAC Cluster interconnect performance

The most complex aspect of RAC tuning involves monitoring and the subsequent tuning of process associated with the Global Services Directory(GSD) . The group of processes associated with the GSD is the Global Enqueue Service(GES) and the Global Cache Service(GCS). The GSD processes communicated throught the cluster interconnects . if the cluster interconnects are not configured to process data packets efficiently, then the entire RAC implementation will perform. poorly. This is true regardless of performance-related tuning and configuration efforts in other areas.

Interconnect Traffic-sessions waiting

Sessions that wait on non-idle wait wvents that impact interconnect traffic can be monitored by a query that lists GCS waits using the global dynamic performance view gv$session_wait. You may also see these waits in a STATSPACK or AWR Repot. The major waits that are being monitored are as follows:

Global cache busy: A wait event that occurs whenever a session has to wait for an ongoing operation on the resource to complete.

Gc buffer busy : A wait event that is signaled when a process has to wait for a block to become available because another process is obtaining a resource for this block.

Buffer busy global CR: waits on a consistent read(block needed for reading) via the global cache.

To identify the sessins experiencing  waits on the system, perform. the following tasks.query v$session_wait to determine whether or no any sessions are experiencing RAC-related waits (at the current time)。Identify the objects that are causing contention for these sessions. Try to modify the object or query to reduce contention.For example, query v$session_wait to determine whether or not any session are experiencing RAC cache-related waits. Note that the GV$ views are used much more to show statistics for the entire cluster, whereas the V$ views still show statistics from a single node. If you plan to use RAC, you must extend the v$ views an queries to the GV$ views for multiple nodes. Thie section is only an initial guide to help you see all of the components. This scope of this book does not cover RAC specifically,but some things that will help you tune RAC.

Select inst_id, event, p1 file, p2 block, wait_time

  From gv$session_wait

 Where event in (‘buffer busy global CR’, ‘global cache busy’, ‘buffer busy global cache’);

The output from this query should look something like this:

Inst_id  event                                 file   block wait_time

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

1        global cache busy                      9      150   15

2        global cache busy                      9      150   10

Run this query to identify objects that are causing contention for these sessions and identifying the object that corresponds to file and block for each file/block combination returned:

Select owner, segment_name, segment_type

  From dba_extents

 Where file_id=9

   And 150 between block_id and block_id+blocks-1

Modify the object to reduce the chances for application contention by doing the following:

Reduct the number of rows per block.

Adjust the block size to a smaller block size.

Modify initrans and freelists.

tom写的一个用来生成为sqlldr准备的格式.

其中有unix和windows两个版本.

在itpub中有人已经上传.

具体可以到这里下载:

http://www.itpub.net/thread-927857-1-1.html

关于这个脚本,需要注意的是,由于tom只是输出一个例子.因此并未把数据写入至文件,

其实稍把sqlldr_exp.sql脚本作一修改即可实现, 如下:

set wrap off
set linesize 100
set feedback off
set pagesize 0
set verify off
set termout off

spool ytmpy.sql

prompt set head off;
prompt set feedback off;
prompt spool aa.txt
prompt prompt LOAD DATA
prompt prompt INFILE *
prompt prompt INTO TABLE &1
prompt prompt REPLACE
prompt prompt FIELDS TERMINATED BY '|'
prompt prompt (
select  'prompt ' || decode(column_id,1,'',',') || lower(column_name)
from    user_tab_columns
where   table_name = upper('&1')
order by column_id
/
prompt prompt )
prompt prompt BEGINDATA

prompt  select
select  lower(column_name)||'||chr(124)||'
from    user_tab_columns
where   table_name = upper('&1') and
    column_id != (select max(column_id) from user_tab_columns where
             table_name = upper('&1'))
    order by column_id
/
select  lower(column_name)
from    user_tab_columns
where   table_name = upper('&1') and
    column_id = (select max(column_id) from user_tab_columns where
             table_name = upper('&1'))
    order by column_id
/
prompt  from    &1
prompt  /
prompt spool off
prompt set feedback on
prompt set head on
spool off
set termout on
@ytmpy.sql
exit

Cache Fusion and Resource Coordination

  Since each node in Real  Application  Cluster has its own memory(cache) that is not shared with other nodes, RAC must coordination the buffer caches of different nodes while minimizing additional disk I/O that could reduct performance. Cache Fusion is the technology that uses high-speed interconnects to provide cache-to-cache transfers of data blocks between instances in a cluster. Cache Fusion functionality allows direct memory writes of dirty blocks to alleviate the need to force a disk write and re-read(or ping) the commited blocks. However, this is not to say that disk writes do not occur. Disk writes and still required for cache replacement and when a checkpoint occurs. Cache Fusion addresses the issues involved in concurrency between instances: concurrent reads on multiple nodes, concurrent reads and writes on different nodes,  and concurrent writes on different nodes.

   Oracle only reads data blocks from disk if they are not already present in the buffer caches of any instance. Because data block writes are deferred, they often contain modifications from multiple transactions. The modified data blocks are written to disk only when a checkpoint occurs. Before we go further, we need to be familiar with a couple of concepts introduced in Oracle 9i RAC:resource modes and resource roles. Because the same data blocks can concurrently exist in multiple instances, there are two identifiers that help to coordinate these blocks:

l       Resource mode: The modes are null, shared, and exclusive. The block can be held in different modes, depending on whether a resource holder intends to modify data or merely read them.

l       Resource role:The roles are locally managed and globally managed.

Global Resource Directory(GRD) is not a database. It is a collection of internal structures and is used to find the current status of the data blocks. Whenever a block is transferred out of a local cache to another instance’s  cache, GRD is updated. The following information about a resource is available in GRD:

l       Data Block identifiers(DBA)

l       Location of most current versions

l       Modes of the data blocks(N,S,X)

l       The roles of the blocks (local or global)

Past Image

To maintain the data integrity, a new concept of past image was introduced in 9i Version of RAC. A past image(PI) of a block is kept in memory before the block is sent and serves as an indication of whether or not it is a dirty block. In the event of failure, GCS can reconstruct the current version of the block by reading PIs. This PI is different from a CR block, which is needed to reconstruct read-consistent images. The CR version of a block represents a consistent snapshot of the data at a point in time.

   As an example, Transaction-A f instance-A has updated row-2 on block-5, and later another Transaction-B of Inst-B has updated row-6 on same block-5. Block-5 has been transferred from Inst-A to B. At this time, Past Image(PI) for block-5 is created on Insta-A.

SCN Processing

System change numbers(SCNs) uniquely identify a commited transaction and the changes it makes. An SCN is a logical time stamp that defines a committed version of a database at one point in time. Oracle assigns every committed transaction a unique SCN.

   Within RAC, since you have multiple instances that perform. commits, the SCN changes need to be maintained within an instance, but at the same time, they must also be synchronized across all instances with a cluster. Therefore, SCN is handled by the Global Cache Service using the Lamport SCN generation scheme, or by using a hardware block or dedicated SCN server. SCNs are recorded in the redo log so that recovery operations can be synchronized in Oracle 9i Real Application Cluster.