473,747 Members | 2,321 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

GLOBAL TEMPORARY table - serious join performance problem

Scenario:

1) Create a GLOBAL TEMPORARY table and populate it with
one (1) row.

2) Join that table to another with about 1 million rows.
The join condition selects a few hundred rows.

Performance: 4 seconds, the system is doing a full-table
scan of the second table, and the Explain Plan output
indicates that Oracle thinks the first table has 4000
rows.

Now replace the GLOBAL TEMPORARY table with a real table
and repeat exactly the same query. This runs in 94 milliseconds
and the Explain Plan shows the correct number of rows for
the driving table and an index scan on the second table, as
I would have expected.

Can anyone suggest a solution that will make the GLOBAL TEMPORARY
implementation as fast as the real table version?

BTW, why are there two sets of parallel groups under both
comp.database.o racle and comp.databases. oracle?

Jim Garrison
jh*@athensgroup .com
Jul 19 '05 #1
5 33654
Jim Garrison wrote:


BTW, why are there two sets of parallel groups under both
comp.database.o racle and comp.databases. oracle?


comp.database.o racle was created a couple of years ago by accident. Some
ISPs allowed users to create newsgroups on demand. SOme people still inist
on using that.

comp.databases. oracle itself has been voted out, in favor of
comp.databases. oracle.* heirarchy, so those of us answering could get a bit
of discrimination on the questions.
Jul 19 '05 #2
Jim Garrison wrote:
Scenario:

1) Create a GLOBAL TEMPORARY table and populate it with
one (1) row.

2) Join that table to another with about 1 million rows.
The join condition selects a few hundred rows.

[snip]

I found references to one solution, which is to set the
table statistics (numrows, specifically) manually using
DBMS_STATS.SET_ TABLE_STATS. While this works, it appears
that table statistics are shared among all instances.
That is, even though every session gets its own copy of
the data, there's only one copy of the stats. Two
sessions with greatly differing rowcounts will step
on each other's stats. Oh well.... I guess I'm
going to have to go back to real tables.

GTTs don't seem to be fully baked yet, at least in 9i.
Can anyone confirm that GTTs work better in 10g?

Jim Garrison
jh*@athensgroup .com
Jul 19 '05 #3

Read about dynamic sampling.

Use a level where all tables without
statistics are sampled at run time, and
your problem will go away.
--
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated Sept 19th

"Jim Garrison" <jh*@athensgrou p.com> wrote in message
news:Gf******** ************@gi ganews.com...
Scenario:

1) Create a GLOBAL TEMPORARY table and populate it with
one (1) row.

2) Join that table to another with about 1 million rows.
The join condition selects a few hundred rows.

Performance: 4 seconds, the system is doing a full-table
scan of the second table, and the Explain Plan output
indicates that Oracle thinks the first table has 4000
rows.

Now replace the GLOBAL TEMPORARY table with a real table
and repeat exactly the same query. This runs in 94 milliseconds
and the Explain Plan shows the correct number of rows for
the driving table and an index scan on the second table, as
I would have expected.

Can anyone suggest a solution that will make the GLOBAL TEMPORARY
implementation as fast as the real table version?

BTW, why are there two sets of parallel groups under both
comp.database.o racle and comp.databases. oracle?

Jim Garrison
jh*@athensgroup .com

Jul 19 '05 #4
Jonathan Lewis wrote:
Read about dynamic sampling.

Use a level where all tables without
statistics are sampled at run time, and
your problem will go away.


That does the trick.

I added /*+ dynamic_samplin g(gtt 1) */ and the query
works FASTER than a real table (about 15% faster)
and the execution plan is logical once again.

One note that should be in the docs is that if the
table has an alias in the statement then only the
alias name can be used in the hint. The full table
name is ignored in that case.

Thanks for your assistance.
Jul 19 '05 #5
"Jonathan Lewis" <jo******@jlcom p.demon.co.uk> wrote in message news:<ci******* ***@titan.btint ernet.com>...
Read about dynamic sampling.

Use a level where all tables without
statistics are sampled at run time, and
your problem will go away.
--
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated Sept 19th

"Jim Garrison" <jh*@athensgrou p.com> wrote in message
news:Gf******** ************@gi ganews.com...
Scenario:

1) Create a GLOBAL TEMPORARY table and populate it with
one (1) row.

2) Join that table to another with about 1 million rows.
The join condition selects a few hundred rows.

Performance: 4 seconds, the system is doing a full-table
scan of the second table, and the Explain Plan output
indicates that Oracle thinks the first table has 4000
rows.

Now replace the GLOBAL TEMPORARY table with a real table
and repeat exactly the same query. This runs in 94 milliseconds
and the Explain Plan shows the correct number of rows for
the driving table and an index scan on the second table, as
I would have expected.

Can anyone suggest a solution that will make the GLOBAL TEMPORARY
implementation as fast as the real table version?

BTW, why are there two sets of parallel groups under both
comp.database.o racle and comp.databases. oracle?

Jim Garrison
jh*@athensgroup .com


I may be way off base here...

Did you collect stats on the 'real table'? If so, then the CBO knows
there is only 1 row which would give you the 'ideal plan' using the
index scan on table 2.

With the temporay table, assuming you have stats on the second table
and not on the temporary table, then Oracle is using the CBO. The
optimizer needs to guess as to how many rows are in the temporary
table. The only information it has to go on is the table definition
(row size) and your block size. The optimizer may assume worst case
and come up with some number of possible rows in the table.
SQL> create global temporary table foobar(col1 number) on commit
preserve rows;
SQL> insert into foobar values(1);
SQL> commit;

SQL> create table t1(col1 number);
SQL> insert into t1 select object_id from user_objects;
3 rows created
SQL> set autotrace on
SQL> select * from t1
2 , foobar
3 where foobar.col1 = t1.col1
4 /

no rows selected
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOS E (Cost=14 Card=8168 Bytes=1
47024)

1 0 HASH JOIN (Cost=14 Card=8168 Bytes=147024)
2 1 TABLE ACCESS (FULL) OF 'T1' (Cost=2 Card=3 Bytes=15)
3 1 TABLE ACCESS (FULL) OF 'FOOBAR' (Cost=11 Card=8168
Bytes=106184)

Note the cardinality for temp table foobar. My block size is 8k.

Jeff W.
en************* *@earthlink.net
Jul 19 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
13737
by: Corrine | last post by:
Hi, I am creating a global temporary table that is session-specific. I insert a BLOB into this table, and then select the BLOB from this table into a ResultSet. The ResultSet sees this BLOB object, and I am able to get the binary input stream from this blob. However, when I invoke InputStream.read(byte) on this input stream, I get the following exception: java.io.IOException: ORA-01410: invalid ROWID
2
3387
by: Yannick Turgeon | last post by:
Hello all, I'm using SS2K on W2K. Brieffing: Many months ago, I created a stored procedure only used by those with admin rights in SS. Now, someone else (without admin rights) has to run it. I gave him rigth to execute the SP but, at the second and more execution, he got a error message concerning a temp table already existing (see further).
3
3456
by: vsaraog | last post by:
Hi everybody, I asked the following question but didn't get any reply. If anyone knows something about the problem, then please reply since I am really in a bind. Here is the question... I am using a Created Global Temporary Table in DB2 OS/390 version 7.1.2 with "ON COMMIT DELETE ROWS". The DBA says that I they don't have the option of "ON COMMIT PRESERVE ROWS" for Created Global Temp. tables. Anyway... I am inserting some data in...
2
4138
by: chettiar | last post by:
I am creating a procedure A which is creating a global temporary table DECLARE GLOBAL TEMPORARY TABLE session.temp (Service CHAR(2), CustomerServiceTypeId INTEGER) WITH REPLACE ON COMMIT PRESERVE ROWS; I am able to compile the proceudre. But when I try to compile procedure B which is referencing the temporary table in procedure A, I get the error.
5
8833
by: Rahul B | last post by:
Hi, I have very little knowledge about creating Procedures/functions in DB2. When i tried to create the test function like CREATE FUNCTION GET_TEST (P_TEST_ID INTEGER, P_SEL_OR_SORT INTEGER,
4
2640
by: hgriva1 | last post by:
Hi, Is there a way in which i can create a global temporary table based on join condition eg: scott@ISNS>CREATE GLOBAL TEMPORARY TABLE x 2 AS 3 SELECT deptno,dname 4 FROM( 5 select e.deptno,d.dname 6 from emp e ,dept d
1
1868
by: Ragianand | last post by:
Hi All, I have created a stored procedure which populates a global emporary table...Now i want to write a function which returns a view that contains the data from the global temporary table.Can anyoune suggest a solution for this...Any sample code to return a view from a function with data from a global temporary table in another procedure. My understanding is that global temporary table will loose the data once we exit the procedure and...
1
1690
by: DB2 | last post by:
After I have istalled express version of DB 9.5 on Vista. I create a stored procedure(used global temporary table), When i try to execute this procedure, it's very very slow, even if there is only one row can be fetched. But I try this on XP instead, it's ok. Does it Needs addtional setting for global temporary table on Vista? Thanks in advance.
5
591
by: Jim Garrison | last post by:
Scenario: 1) Create a GLOBAL TEMPORARY table and populate it with one (1) row. 2) Join that table to another with about 1 million rows. The join condition selects a few hundred rows. Performance: 4 seconds, the system is doing a full-table scan of the second table, and the Explain Plan output
0
8979
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9522
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
9307
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8234
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6790
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4589
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4860
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3296
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
2203
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.