临时表究竟放在SGA还是PGA？

gdpr-dba

关于临时表有两个疑问：
1）首先临时表是存放SGA还是PGA呢？
按刘大群里的说法是放在PGA的，但是看了以下这篇文章，里面通过dump buffer_cache来证明临时表的数据是放在buffer cache的：
http://www.hellodba.com/reader.php?ID=132&lang=CN

2）如果临时表是放在SGA的话，那alter system checkpoint会不会将临时表的数据写进硬盘呢？

Maclean Liu(刘相兵

=
SQL> drop table temp_mac;

Table dropped.

SQL>
SQL>
SQL>
SQL> create global temporary table temp_mac (t1 varchar2(200)) on commit preserve rows ;

Table created.

SQL> insert into temp_mac values('MACLEANNNNNNNNNNNNNNNNNN');

1 row created.

SQL> commit;

Commit complete.

SQL>  select object_id,data_object_id from dba_objects where object_name='TEMP_MAC';

OBJECT_ID DATA_OBJECT_ID
---------- --------------
     58761


     
     SQL> desc v$bh;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
FILE#                                              NUMBER
BLOCK#                                             NUMBER
CLASS#                                             NUMBER
STATUS                                             VARCHAR2(7)
XNC                                                NUMBER
FORCED_READS                                       NUMBER
FORCED_WRITES                                      NUMBER
LOCK_ELEMENT_ADDR                                  RAW(8)
LOCK_ELEMENT_NAME                                  NUMBER
LOCK_ELEMENT_CLASS                                 NUMBER
DIRTY                                              VARCHAR2(1)
TEMP                                               VARCHAR2(1)
PING                                               VARCHAR2(1)
STALE                                              VARCHAR2(1)
DIRECT                                             VARCHAR2(1)
NEW                                                CHAR(1)
OBJD                                               NUMBER
TS#                                                NUMBER


OBJD=> data object id



SQL> set autotrace on;  
SQL> select * from temp_mac;

T1
--------------------------------------------------------------------------------
MACLEANNNNNNNNNNNNNNNNNN


Execution Plan
----------------------------------------------------------
Plan hash value: 4177623204

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |     1 |   102 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| TEMP_MAC |     1 |   102 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
         28  recursive calls
          0  db block gets
         10  consistent gets
          0  physical reads
          0  redo size
        531  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> /

T1
--------------------------------------------------------------------------------
MACLEANNNNNNNNNNNNNNNNNN


Execution Plan
----------------------------------------------------------
Plan hash value: 4177623204

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |     1 |   102 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| TEMP_MAC |     1 |   102 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        531  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> /

T1
--------------------------------------------------------------------------------
MACLEANNNNNNNNNNNNNNNNNN


Execution Plan
----------------------------------------------------------
Plan hash value: 4177623204

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |     1 |   102 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| TEMP_MAC |     1 |   102 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        531  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
         

SQL> select * from v$bh where objd=58761;

no rows selected


SQL> select * from x$bh where obj=58761;

no rows selected

SQL> oradebug setmypid
Statement processed.
SQL>
SQL> oradebug dump buffers 3
Statement processed.
SQL> oradebug tracefile_name
/s01/admin/G10R25/udump/g10r25_ora_4577.trc

BH (0x703e3938) file#: 201 rdba: 0x00401b8a (1/7050) class: 1 ba: 0x700e4000
  set: 3 blksize: 8192 bsi: 0 set-flg: 2 pwbcnt: 0
  dbwrid: 0 obj: 4201353 objn: 58761 tsn: 3 afn: 201
  hash: [9d3d4238,9d3d4238] lru: [703e3ac8,703e38a8]
  ckptq: [NULL] fileq: [NULL] objq: [703e3b38,9a68a150]
  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: 0x00401b8a (1/7050)
  scn: 0x0000.011644bb seq: 0x00 flg: 0x08 tail: 0x44bb0600
  frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00000000700E4000 to 0x00000000700E6000


buffer cache里确实有 objn: 58761的 buffer header




SQL> alter system set "_trace_pin_time"=1 scope=spfile;

System altered.

SQL> startup force;
ORACLE instance started.

Total System Global Area 1048576000 bytes
Fixed Size                  2101544 bytes
Variable Size             738201304 bytes
Database Buffers          301989888 bytes
Redo Buffers                6283264 bytes
Database mounted.
Database opened.



         
         
         
          alter session set events '10046 trace name context forever,level 1';

SQL> insert into temp_mac values('MACLEANNNNNNNNNNNNNNNNNN');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from temp_mac;

T1
--------------------------------------------------------------------------------
MACLEANNNNNNNNNNNNNNNNNN



SQL> oradebug setmypid;
Statement processed.

SQL> oradebug tracefile_name
/s01/admin/G10R25/udump/g10r25_ora_4484.trc


_trace_pin_time=> 可以打印出select查询过程中 pin的buffer

PARSING IN CURSOR #20 len=22 dep=0 uid=0 oct=3 lid=0 tim=1310380978931144 hv=4225877599 ad='9e2a63a8'
select * from temp_mac
END OF STMT
PARSE #20:c=2999,e=2478,p=0,cr=4,cu=0,mis=1,r=0,dep=0,og=1,tim=1310380978931134
EXEC #20:c=0,e=50,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1310380978931375
pin ktewh25: kteinicnt dba 401b89:4 time 3636791075
pin ktewh26: kteinpscan dba 401b89:4 time 3636791107
pin kdswh01: kdstgr dba 401b8a:1 time 3636791167
FETCH #20:c=0,e=185,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=1,tim=1310380978931652
FETCH #20:c=0,e=17,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1310380978932990

401b89=> datafile 1 block 7049
401b8a=> datafile 1 block 7050

对应上面的buffer

BH (0x703e3938) file#: 201 rdba: 0x00401b8a (1/7050) class: 1 ba: 0x700e4000




SQL> insert into temp_mac values('ZZZZZZZZZZ');

1 row created.

SQL> commit;

Commit complete.



SQL> alter system dump tempfile 1 block 7049;

System altered.

SQL> alter system dump tempfile 1 block 7050;

System altered.


Start dump data blocks tsn: 3 file#: 1 minblk 7050 maxblk 7050
buffer tsn: 3 rdba: 0x00401b8a (1/7050)
scn: 0x0000.011708c7 seq: 0x00 flg: 0x08 tail: 0x08c70600
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x000000000792B800 to 0x000000000792D800
00792B800 0000A206 00401B8A 011708C7 08000000  [......@.........]
00792B810 00000000 00000001 00401B89 00000000  [..........@.....]
00792B820 00000000 00030002 00000000 00190007  [................]
00792B830 00002D3A 008005AF 00090485 00000001  [:-..............]
00792B840 00000000 00000000 00000000 00000000  [................]
00792B850 00000000 00000000 00000000 00010100  [................]
00792B860 0014FFFF 1F7E1F92 00001F7E 1F920001  [......~.~.......]
00792B870 00000000 00000000 00000000 00000000  [................]
        Repeat 502 times
00792D7E0 00000000 00000000 00000000 012C0000  [..............,.]
00792D7F0 5A5A0A01 5A5A5A5A 5A5A5A5A 08C70600  [..ZZZZZZZZZZ....]
Block header dump:  0x00401b8a
Object id on Block? Y
seg/obj: 0x401b89  csc: 0x00.00  itc: 2  flg: O  typ: 1 - DATA
     fsl: 0  fnx: 0x0 ver: 0x01

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0007.019.00002d3a  0x008005af.0485.09  ----    1  fsc 0x0000.00000000
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000

data_block_dump,data header at 0x792b85c
===============
tsiz: 0x1fa0
hsiz: 0x14
pbl: 0x0792b85c
bdba: 0x00401b8a
     76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f92
avsp=0x1f7e
tosp=0x1f7e
0xe:pti[0]      nrow=1  offs=0
0x12:pri[0]     offs=0x1f92
block_row_dump:
tab 0, row 0, @0x1f92
tl: 14 fb: --H-FL-- lb: 0x1  cc: 1
col  0: [10]  5a 5a 5a 5a 5a 5a 5a 5a 5a 5a
end_of_block_dump
End dump data blocks tsn: 3 file#: 1 minblk 7050 maxblk 7050




SQL> select count(*) from v$Bh where dirty='Y';

  COUNT(*)
----------
        89

SQL> select status,dirty ,ts#,file# ,block# from v$bh where block#=7050;

STATUS  D        TS#      FILE#     BLOCK#
------- - ---------- ---------- ----------
xcur    Y          3          1       7050

SQL> alter system checkpoint;

System altered.

SQL> select status,dirty ,ts#,file# ,block# from v$bh where block#=7050;

STATUS  D        TS#      FILE#     BLOCK#
------- - ---------- ---------- ----------
xcur    Y          3          1       7050

SQL> select count(*) from v$Bh where dirty='Y';

  COUNT(*)
----------
        38


可以看到 alter system checkpont后该temp table的脏块状态仍为dirty



结论

1. temporary table的buffer也存放在 buffer cache 中

2. 完全检查点 并不会导致 脏的temp table buffer 被写入到磁盘

gdpr-dba

精彩的分析，受教了，谢谢刘大~~