oracle中的global temporary table数据能否被缓冲？

Yong Huang

sqysl,

Please work with Oracle and post back what you find out. Thanks. I won't be surprised if we're told a block caching GTT's data will be aged out faster. But it's better to have a more authoritative answer or lab test.

Yong Huang

sundog315

原帖由 sqysl 于 2010-10-17 22:51 发表
Though YONGHUANG and SUNDOG315 have told and proved that GTT's data is cached in buffer cache,but I believe that GTT'S data is differently delt by oracle system,and my tuning process told the point: when I used the GTTs,the application's performance was very bad,and IOs were very high. then I removed the GTTs,the application's performance became much better,and IOs almost disappeared.I think that we need know the truth and open a SR.
if any further progress,I will post here,thanks.

变慢的原因有很多，执行计划改变之类的。请先确定这些因素没有发生变化，这样比较才是有意义的。

sqysl

hi,everyone,I have opened a SR about GTT,here is the SR,Oracle support's feedback and test:

Problem Description:
recently,I have beening worked with optimizing a application of our company,the key is whether global temporary table's data can be cached in memory in oracle,if so,where the data is cached ?and how to control how the data is cached?in the application,there were about five global temporary tables before I did anything with it,and this led to high IOs and severe performance problem.After I reduced the number of gloabl temporary tables to one,the applications' performance was improved very muche,about ten and several times.Now,I want to know the reason and how global temporary table works inside,thank you.

feedback and test:
=== ODM Research ===

Note: This is INTERNAL ONLY research. No action should be taken by the customer on this information.
This is research only, and may NOT be applicable to your specific situation.

KNOWLEDGE
-----------------
Keywords:Temporary Table
1. Overview of Temporary Tables (Doc ID 68098.1)
--When you create a GLOBAL TEMPORARY table a dictionary definition of the table is created.
--As soon as the table gets populated (on the first INSERT or at creation time for CTAS operations) a temporary segment is created in the users default TEMPORARY tablespace location. This temporary segments contents are just like a normal table.

--Different sessions using the same GLOBAL TEMPORARY table get allocated different temporary segments. The temporary segments are cleaned up automatically at session end or transaction end depending on the specified duration (ON COMMIT PRESERVE ROWS or ON COMMIT DELETE ROWS).

--Apart from the data visibility temporary tables can be used like ordinary tables for most operations.


--1. Data exists only for the duration of either the session or transaction.

This can be specified in the create table command.
For example:

SQL> Create global temporary table emp_temp(eno number) on commit delete rows;

- OR -

SQL> Create global temporary table emp_temp(eno number) on commit preserve rows;


ON COMMIT DELETE ROWS indicates a transaction level duration and PRESERVE indicates a session level duration.

--2. Data is visible only at session or transaction level. Multiple users using the same temporary table can see the definition of the table and their own data segment and nothing else.

--3. Indexes, triggers and views can be created on these tables.

--4. If an Index is created on temporary tables then it MUST be created when the table is empty - ie: When there are NO temporary segments for incarnations of the table. Indexes are implemented as separate temporary segments.

--5. No redo is generated for operations on the temporary table itself BUT undo is generated. Redo *IS* generated for the undo so temporary tables do indirectly generate redo.

--6. The keyword GLOBAL indicates the table definition can be viewed by anybody with sufficient privileges - ie:using the same rules that apply to normal user tables. Currently only GLOBAL TEMPORARYtables are supported.

--7. TRUNCATE operations truncate only the current session's incarnation of the table.

--8. You can only export or import the definition not the data.

--9. Segments get created only on the first insert (or CTAS) operation.


2. How to Create Statistics on Global Temporary Tables (Doc ID 351190.1)


=== ODM Test Case ===

Note: This is INTERNAL ONLY test case.  No action should be taken by the customer on this information.
This is research only, and may NOT be applicable to your specific situation.

Test Case 1: This test case is to verify whether Global Temporary Tables data can be cached in buffer cache.

1. Create GTT table in scott schema
SQL> create global temporary table gtt(name varchar2(100));
SQL> show user
SQL> desc gtt
--------------------------------------------------------------------------------
ID NUMBER
USER_NAME VARCHAR2(4000)
SQL> select * from gtt;
SQL> begin
for i in 1 .. 100000 loop
insert into gtt values (i,'GTT');
end loop;
end;
6 /

PL/SQL
SQL> select * from gtt where rownum<=3;

ID
----------
USER_NAME
--------------------------------------------------------------------------------
1
GTT

2
GTT

3
GTT


SQL>

2. Open another session by sys account
a. Global temporary table is created in temp segment, then we check the TS# of TEMP tablespace
SQL> select ts#,name from ts$ where name='TEMP';

TS# NAME
---------- ------------------------------
3 TEMP
b. We could see one TEMP segment is used by SCOTT account, with block number 2185. We could see that block 2186 of TEMP file is owned by this table as well
SQL> select username,user,CONTENTS,SEGTYPE, SEGFILE#,SEGBLK#,EXTENTS,BLOCKS, SEGRFNO# from V$TEMPSEG_USAGE;

USERNAME USER CONTENTS SEGTYPE SEGFILE# SEGBLK# EXTENTS BLOCKS SEGRFNO#
------------------------------ ------------------------------ --------- --------- ---------- ---------- ---------- ---------- ----------
SCOTT SYS TEMPORARY DATA 201 2185 2 256 1


c. Checked x$bh which is associated with Buffer Cache, and see that block 2186 is cached.
SQL> select ts#,file#,obj,dbablk from x$bh where ts#=3 and dbablk in (2185,2186,2187);

TS# FILE# OBJ DBABLK
---------- ---------- ---------- ----------
3 1 4196489 2186
3 1 4196489 2185
3 1 4196489 2187

d. Verify again that block 2186 of TEMP is used by GTT table
SQL> ALTER SYSTEM DUMP TEMPFILE 1 BLOCK 2186;
SQL> oradebug setmypid
SQL> oradebug tracefile_name
d:\oracle\admin\ora10g\udump\ora10g_ora_5460.trc
SQL>

[user@host udump]$ sed -n '537,540p' ora10g_ora_5460.trc
80341C0 0202012C 470307C1 012C5454 06C10202 [,......GTT,.....] <========== We can see the data of GTT table in block 2186 of tempfile.
80341D0 54544703 0202012C 470305C1 012C5454 [.GTT,......GTT,.]
80341E0 04C10202 54544703 0202012C 470303C1 [.....GTT,......G]
80341F0 012C5454 02C10202 54544703 57020600 [TT,......GTT...W]


Conclusion: The global temporary table could be cached in Buffer Cache as heap table.

Test Case 2: This test case is to verify whether Global Temporary Tables data can be pin in Keep Pool

SQL> alter table gtt cache;

SQL> select table_name,cache from user_tables;

TABLE_NAME                     CACHE
------------------------------ ----------
GTT                                Y


SQL>

Conclusion: global temporary table can be cached in KEEP POOL


=== ODM Answer ===

Dear customer,

You could get more details of behavior about the global temporary table from the note 68098.1.
As per test case, the data of global temporary table can be cached in Buffer Cache just as heap table (conventional table).

Thanks
Best Regards,

These are all about gtt from oracle support,I will continue to contact with oracle support,if any further progress and information,I will post it here,
thanks.

Yong Huang

Thanks, sqysl. The question they haven't answered is whether GTT's data in buffer cache will be preferably aged out in order to keep non-GTT data as long as possible.

I suggest you post only the relevant part of the SR to the forum. If needed, you can post the entire SR in an attachment so a search engine won't index it but a human can type the itpub-generated number in image to fetch it.

Yong Huang

sqysl

thanks, YONGHUANG,your suggestion is good.
yeah,Oracle supports have had no answer about whether GTT's data in buffer cache will be preferably aged out ,I will contact them until they have a reasonable test or authoritative documents.

sqysl

Hi,everyone,Oracle support closed SR about gtt after asking for me,here is his conclusions,but no documents and tests:
1、gtt caches its' data as normal tables;
2、gtt's data in buffers doesn't age out  very soon,but in the  same way that normal tables cache their data;
3、gtt's data is cached together with normal tables's data in buffers;
4、don't use gtt when large volume data is stored temporarily,it is better to slove this kind of problems by applications' ways,because operations on gtt don't use gtt's statistics very well,but the statistics can be produced;

thanks

Yong Huang

Thanks for posting back. It looks like caching and aging of GTT buffers in buffer cache is no different from that of buffers of a regular table. If I were an Oracle kernel developer, I would consider perhaps placing GTT buffers at the LRU end of the LRU list (even if the GTT is not full-scanned). If Oracle starts to do that in a future version, I won't be surprised.

Yong Huang

sangxian

helps a lot ,thank you all.

mpcs

Yong Huang 发表于 2010-10-12 05:42
Looks like this is a problem we can't easily solve. But I'm pretty sure GTT's data can be cached. I  ...

回复~