tableName后面的为表的字段，values后面为要插入的值。。
其中，：后面的为参数形式，需要在执行前将值添加到这个sql中
http://www.upschool.com.cn/edu/1836/2007/16/10du264717_1.shtml关于Oracle临时表数据cache的研究
Global Temporary Table是Oracle 8i中出现的特性，可以用于存储事务或会话中的临时数据。它的出现大大方便了开发人员。但是在使用上面，由于它本身的特性，一直存在一些问题。
简单说一下临时表，它的数据只对调用它的会话可见，一个会话是无法访问其他会话中的临时表的数据。可以在创建时指定它是事务级的还是会话级的。它被创建在用户的默认临时表空间上，在创建时不会分配段，而是在会话中第一次insert的时候从零时表空间分配数据段。DML时，不会产生redo log，但是会产生undo log。并且无法生成临时表或者临时表上索引的统计信息（势必会影响CBO下的查询计划）。
下面研究一下临时表的数据是如何存储，又是如何获得的，如何cache在内存中的：
我们知道，对于普通表(regular tables)，第一对表进行扫描时，会将扫描到的数据放到buffer cache中，以便以后如果有其他事务需要扫描相同数据时，直接从内存中读，而不产生disk read。并且，同时在LUR和MUR链中记录一个点，以决定这些数据什么时候被page out。
那对于临时表呢？以前我是这样认为的，既然临时表的数据只对会话可见，那它的数据就不应该放在公用的buffer cache中，而放在每个会话的PGA里面更合适。那这个观点正确吗？我查了很多资料，并没有明确的指出临时表的数据应该是cache在内存的哪一块。由于这些都是Oracle internal的东东，没有任何公布的资料可查，我们下面来做一些试验来看看Oracle到底怎么管理临时表的数据的。
临时表中的数据到底cache在哪里？
首先，创建测试用的对象：
创建一张普通表：
CREATE TABLE pga_ttt (
A VARCHAR2(100)
)
给表插入测试数据：
INSERT INTO pga_ttt values(1)
创建临时表：
CREATE GLOBAL TEMPORARY TABLE PGA_TEST
(
A VARCHAR2(3000),
B VARCHAR2(2000),
C VARCHAR2(2000)
)
ON COMMIT PRESERVE ROWS
测试过程：
1. 修改db cache size为一个较小的值：
ALTER SYSTEM SET DB_CACHE_SIZE = 50M
2. 重起数据库：
STARTUP FORCE
这时的数据库的内存中应该是比较干净的。
3. 先看一下一些什么对象已经cache在buffer cache中了：
SELECT DISTINCT objd FROM v$bh ORDER BY objd
OBJD
----
2
3
6
7
8
... ...
4294967294
4294967295
这些对象应该都是系统启动时载入的一些系统对象。
4. 另外启动两个会话，分别执行以下语句：
INSERT INTO PGA_TEST VALUES(1, 1, 1)
SELECT * FROM PGA_TEST
5. 再看下buffer cache中的对象：
SELECT DISTINCT objd FROM v$bh ORDER BY objd
OBJD
----
2
3
6
7
... ...
6693385
6693513
4294967294
4294967295
这时，可以发现多出两个对象来了。但不能确定和pga_test有什么关系，也许又是两个系统对象。
6. 查一下pga_test的object number
SELECT object_id FROM dba_objects WHERE object_name = 'PGA_TEST'
53513
比较失望L，这个object number和刚才那两个新cache到buffer cache中的object number并不相同。但是这还并不能说明临时表的数据一定没有cache到buffer cache中去。
接下来继续测试，将buffer cache dump出来！
7. Dump出buffer cache
用level 3将整个buffer cache都dump出来，这将会产生一个比较大的trace文件（折就是为什么要把buffer cache设小一些的原因）
oradebug setmypid
oradebug dump buffers 3
8. 打开trace文件
在trace文件中，找到一下两段：
· 第一段：
BH (183EBEEC) file#: 201 rdba: 0x0066220a (1/2499082) class: 1 ba: 18114000
set: 3 blksize: 8192 bsi: 0 set-flg: 2 pwbcnt: 574
dbwrid: 0 obj: 6693385 objn: 53513 tsn: 3 afn: 201
hash: [201d7a88,201d7a88] lru: [183ebff0,183ebe90]
ckptq: [NULL] fileq: [NULL] objq: [1ea81d98,183ec044]
st: XCURRENT md: NULL tch: 2
flags: buffer_dirty temp_data gotten_in_current_mode redo_since_read
LRBA: [0x0.0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]
buffer tsn: 3 rdba: 0x0066220a (1/2499082)
scn: 0x0000.00550a09 seq: 0x00 flg: 0x08 tail: 0x0a090600
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x18114000 to 0x18116000
18114000 0000A206 0066220A 00550A09 08000000 [....."f...U.....]
... ...
... ...
... ...
18115830 20202020 20202020 20202020 20202020 []
Repeat 123 times
18115FF0 20202020 20202020 20202020 0A090600 [....]
Block header dump: 0x0066220a
Object id on Block? Y
seg/obj: 0x662209 csc: 0x00.00 itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag LckScn/Fsc
0x01 0x0006.01b.00000c9d 0x0080142b.088a.20 ----1 fsc 0x0000.00000000
0x02 0x0000.000.00000000 0x00000000.0000.00 ----0 fsc 0x0000.00000000
data_block_dump,data header at 0x1811405c
===============
tsiz: 0x1fa0
hsiz: 0x14
pbl: 0x1811405c
bdba: 0x0066220a
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x824
avsp=0x810
tosp=0x810
0xe:pti[0]nrow=1 offs=0
0x12:pri[0] offs=0x824
block_row_dump:
tab 0, row 0, @0x824
tl: 6012 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [2000]
31 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
... ...
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
col 1: [2000]
31 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
... ...
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
col 2: [2000]
31 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
... ...
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
end_of_block_dump
· 第二段：
BH (183EC04C) file#: 201 rdba: 0x0066228a (1/2499210) class: 1 ba: 18118000
set: 3 blksize: 8192 bsi: 0 set-flg: 2 pwbcnt: 574
dbwrid: 0 obj: 6693513 objn: 53513 tsn: 3 afn: 201
hash: [201e8d88,183e6a5c] lru: [183ec150,183ebff0]
ckptq: [NULL] fileq: [NULL] objq: [1ea81dd0,183ec1a4]
st: XCURRENT md: NULL tch: 2
flags: buffer_dirty temp_data gotten_in_current_mode redo_since_read
LRBA: [0x0.0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]
buffer tsn: 3 rdba: 0x0066228a (1/2499210)
scn: 0x0000.00550a08 seq: 0x00 flg: 0x08 tail: 0x0a080600
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x18118000 to 0x1811A000
18118000 0000A206 0066228A 00550A08 08000000 [....."f...U.....]
... ...
... ...
... ...
18119830 20202020 20202020 20202020 20202020 []
Repeat 123 times
18119FF0 20202020 20202020 20202020 0A080600 [....]
Block header dump: 0x0066228a
Object id on Block? Y
seg/obj: 0x662289 csc: 0x00.00 itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag LckScn/Fsc
0x01 0x0005.014.00000c58 0x008044a3.060a.08 ----1 fsc 0x0000.00000000
0x02 0x0000.000.00000000 0x00000000.0000.00 ----0 fsc 0x0000.00000000
data_block_dump,data header at 0x1811805c
===============
tsiz: 0x1fa0
hsiz: 0x14
pbl: 0x1811805c
bdba: 0x0066228a
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x824
avsp=0x810
tosp=0x810
0xe:pti[0]nrow=1 offs=0
0x12:pri[0] offs=0x824
block_row_dump:
tab 0, row 0, @0x824
tl: 6012 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [2000]
31 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
... ...
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
col 1: [2000]
31 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
... ...
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
col 2: [2000]
31 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
... ...
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
end_of_block_dump
这两段都是cache在buffer cache中的数据段。注意他们的基础系统中的以下内容：
obj: 6693385 objn: 53513
obj: 6693513 objn: 53513
object number是53513，这正是pga_test的object number，对比一下后面数据块的内容：
col 0: [2000]
31 20 20 20 ...（即’1 ’）
col 1: [2000]
31 20 20 20 ...（即’1 ’）
col 2: [2000]
31 20 20 20 ...（即’1 ’）
正是从pga_test中的扫描到的数据。
再看objn前面的数字，是不是很眼熟？对了，这就是从v$bh中查到的在扫描临时表后buffer cache中多出两个对象的object number！这因该是为了保持各自会话的数据的独立，Oracle创建了一个系统临时对象（丛v$bh中看，这个临时对象时属于sys用户的，不属于当前用户的），保持了与临时表相同的结构，然后在buffer中开辟了一片区域，以系统临时对象的名义存放各自数据，使之相互不影响。同时我们还可以留意到它们的LRU值是不同的。
另外看下他们的flags:
flags: buffer_dirty temp_data gotten_in_current_mode redo_since_read
buffer_dirty:因为临时表需要先插入数据，所以被置了dirty标志；
temp_data:临时表在第一次INSERT时，才在临时表空间上分配临时段，所以是属于临时数据；
gotten_in_current_mode:显然，当前会话这在获取各自临时表中的数据，因此是当前被获取模式下；
redo_since_read:这个不是十分明白。因为临时表的DML是不会产生redo log的，会产生undo log，同时会产生针对这些undo的redo log（而不是临时表的）。
现在，我们基本上可以得出这样的推论：
推论1：临时表的数据是cache在buffer cache中的。并且，为了保持各自会话的数据独立，在buffer cache中为各个会话开辟一片区域来cache它们各自的数据。
以上推论可以和普通表来做一个对比。
在两个会话中分别查询普通表：
SELECT * FROM pga_ttt
Dump 出cache buffer：
oradebug setmypid
oradebug dump buffers 3
查询普通表的object number
SELECT object_id FROM dba_objects WHERE object_name = 'PGA_TTT'
53514
看看trace文件中的内容：虽然我们在两个会话中都扫描了这张表，但是buffer cache只有一段它的数据段：
BH (18BE658C) file#: 5 rdba: 0x014097be (5/38846) class: 1 ba: 18810000
set: 3 blksize: 8192 bsi: 0 set-flg: 2 pwbcnt: 64
dbwrid: 0 obj: 53514 objn: 53514 tsn: 5 afn: 5
hash: [202153d8,202153d8] lru: [18be6690,18be6530]
ckptq: [NULL] fileq: [NULL] objq: [18be6584,18be66e4]
st: XCURRENT md: NULL tch: 2
flags: only_sequential_access
LRBA: [0x0.0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]
buffer tsn: 5 rdba: 0x014097be (5/38846)
scn: 0x0000.005513e7 seq: 0x01 flg: 0x06 tail: 0x13e70601
frmt: 0x02 chkval: 0xa15f type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x18810000 to 0x18812000
18810000 0000A206 014097BE 005513E7 06010000 [[email protected].....]
... ...
... ...
... ...
18810080 00000000 00000000 00000000 00000000 [................]
Repeat 502 times
18811FF0 00000000 2C000000 31010101 13E70601 [.......,...1....]
Block header dump: 0x014097be
Object id on Block? Y
seg/obj: 0xd10a csc: 0x00.5513bd itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x14097b9 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag LckScn/Fsc
0x01 0x0006.01b.00000c9d 0x0080142b.088a.21 --U-1 fsc 0x0000.005513e7
0x02 0x0000.000.00000000 0x00000000.0000.00 ----0 fsc 0x0000.00000000
data_block_dump,data header at 0x18810064
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0x18810064
bdba: 0x014097be
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f93
avsp=0x1f7b
tosp=0x1f7b
0xe:pti[0]nrow=1 offs=0
0x12:pri[0] offs=0x1f93
block_row_dump:
tab 0, row 0, @0x1f93
tl: 5 fb: --H-FL-- lb: 0x1 cc: 1
col 0: [ 1] 31
end_of_block_dump
另外，检查它的基础信息，这时obj和objn是相同的，都是ojb$表中对应的object#。
会话之间的临时表数据是否可以复用？
以上的问题应该可以告一个段落了。下面我想到另外一个问题：如果各个会话的写入临时表中数据都一样，那么会话之间的数据能不能复用呢（即从其他会话的buffer cache中得到一份数据拷贝，而不需要读写临时表空间）？
其实，这个问题应该可以通过一个比较简单的测试来推断：
1. 在两个不同的会话中分别向临时表插入数据：
INSERT INTO PGA_TEST VALUES(1, 1, 1)
2. 然后刷新buffer cache，将buffer中的数据都写入到磁盘中去：
ALTER SYSTEM FLUSH BUFFER_CACHE
以上语句之适用于10g，如果是9i，可以用下面的语句：
ALTER SYSTEM SET EVENTS ‘IMMEDIATE TRACE NAME FLUSH_CACHE’
3. 分别在两个会话中查询临时表：
· SESSION 1:
SQL>SET AUTOT TRACE
SQL>SELECT * FROM PGA_TEST
Execution Plan
----------------------------------------------------------
Plan hash value: 30787903
--------------------------------------
| Id | Operation | Name |
--------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| PGA_TEST |
--------------------------------------
Note
-----
- rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
165 recursive calls
0 db block gets
20 consistent gets
7 physical reads
0 redo size
6533 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed
可以看到第一个会话产生了physical read，显然是从临时数据段上读取了数据。
再在这个会话上查询一次：
SQL>/
Execution Plan
----------------------------------------------------------
Plan hash value: 30787903
--------------------------------------
| Id | Operation | Name |
--------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| PGA_TEST |
--------------------------------------
Note
-----
- rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
6533 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
可以看到，以及没有physical read了，这时不是动临时段上读取数据了，而是直接读取buffer cache了。
· SESSION 2:
SQL>SET AUTOT TRACE
SQL>SELECT * FROM pga_test
Execution Plan
----------------------------------------------------------
Plan hash value: 30787903
--------------------------------------
| Id | Operation | Name |
--------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| PGA_TEST |
--------------------------------------
Note
-----
- rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
2 physical reads
0 redo size
6533 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
请注意，尽管SESSION 1已经是从buffer cache中读取数据了，但是SESSION 2还是有physical read，说明它还是从自己所分配到的临时段上读取的数据，而不是copy其他会话的！
再作一次查询：
SQL>/
Execution Plan
----------------------------------------------------------
Plan hash value: 30787903
--------------------------------------
| Id | Operation | Name |
--------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| PGA_TEST |
--------------------------------------
Note
-----
- rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
6533 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
这时就是从它自己的buffer cache中读取数据了。
推论2：每个会话在第一次INSERT是分配临时段，他们的数据读写是完全独立的，不会有任何联系，即使他们的数据内容完全一样。
我们再与普通表进行对比：
在第一个SESSION中查询普通表：
SQL>SELECT * FROM pga_ttt
Execution Plan
----------------------------------------------------------
Plan hash value: 3071005808
-----------------------------------------------------------------------------
| Id | Operation | Name| Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |52 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| PGA_TTT | 1 |52 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Statistics
----------------------------------------------------------
163 recursive calls
0 db block gets
24 consistent gets
15 physical reads
0 redo size
407 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed
这时产生了physical read，说明数据是从磁盘读取的。
再在第一个SESSION查询这张表：
SQL>SELECT * FROM pga_test
Execution Plan
----------------------------------------------------------
Plan hash value: 30787903
--------------------------------------
| Id | Operation | Name |
--------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| PGA_TEST |
--------------------------------------
Note
-----
- rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
6533 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
希望对你有帮助。
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）