472,958 Members | 2,232 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,958 software developers and data experts.

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.oracle and comp.databases.oracle?

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


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


comp.database.oracle 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*@athensgroup.com> wrote in message
news:Gf********************@giganews.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.oracle 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_sampling(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******@jlcomp.demon.co.uk> wrote in message news:<ci**********@titan.btinternet.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*@athensgroup.com> wrote in message
news:Gf********************@giganews.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.oracle 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=CHOOSE (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
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...
2
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....
3
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...
2
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...
5
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...
4
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...
1
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...
1
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...
5
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. ...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
0
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
0
tracyyun
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
4
NeoPa
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
3
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
3
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
2
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...

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.