Help - Search - Members - Calendar
Full Version: Problems - Tuning a Query
Oracle DBA Forums > Oracle > Oracle Forum
Francisco Riccio
Hi everybody, i have the following question:

I am querying a join of 2 tables, but it take a lot of time (really i don't know how many minutes becuase i have waited more of 20 minutes and nothing).
But when i do a table scan for each table it gets me the data.
I want to copy some information:

table 1

SQL> select * from SAPDEV.VTTP b;

1055645 rows selected.


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
79929 consistent gets
0 physical reads
0 redo size
47222575 bytes sent via SQL*Net to client
774787 bytes received via SQL*Net from client
70378 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1055645 rows processed

table 2

SQL> select * FROM SAPDEV.VTTK a;

291919 rows selected.


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
44477 consistent gets
26669 physical reads
0 redo size
58082624 bytes sent via SQL*Net to client
214722 bytes received via SQL*Net from client
19463 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
291919 rows processed

But in the join of both tables it doesn't get nothing or takes infinite minutes.
Somebody can tell me what happens or that variable of oracle i can set up for fixing it.
Ah i have updated the statistics and indexes of both tables even i have for foreign key indexes too.
Thank you very much.
HAL9000
Ah, SAP tables, eh?

"I am querying a join of 2 tables"

Have you already joined the tables, or are you talking about doing the join?

Does the SQL have an ORDER BY?

Can you post the actual join?

Do you have an SMP server?

"But when i do a table scan for each table it gets me the data."

Right, it's the "matching" of the join keys that takes the time.

Can you "set autotrace traceonly explain" and post the execution plan for this?

With a million rows in the table, on a busy server, it might take awhile.

If your execution plan show full-table-scans, you may be able to bump-up your hash_area_size.
Francisco Riccio
QUOTE (HAL9000 @ Jul 5 2008, 05:34 AM) *
Ah, SAP tables, eh?

"I am querying a join of 2 tables"

Have you already joined the tables, or are you talking about doing the join?

Does the SQL have an ORDER BY?

Can you post the actual join?

Do you have an SMP server?

"But when i do a table scan for each table it gets me the data."

Right, it's the "matching" of the join keys that takes the time.

Can you "set autotrace traceonly explain" and post the execution plan for this?

With a million rows in the table, on a busy server, it might take awhile.

If your execution plan show full-table-scans, you may be able to bump-up your hash_area_size.


Thank you very much for answering me, look we are passing a system SAP a other server, but in the other server this report of this query has a time of 1 min and now in this server has 3 min, is the same server.
The query of the join i can't get becuase take more of 20 min or more and take a lot resouces of server.
It doeesn't have order by is only a select from with join of 1 field nothing more, is rare.
So do you recommend grow up these 2 variables the dump_up, hash_area_size?
Kriptas
QUOTE (Francisco Riccio @ Jul 6 2008, 01:51 AM) *
Thank you very much for answering me, look we are passing a system SAP a other server, but in the other server this report of this query has a time of 1 min and now in this server has 3 min, is the same server.
The query of the join i can't get becuase take more of 20 min or more and take a lot resouces of server.
It doeesn't have order by is only a select from with join of 1 field nothing more, is rare.
So do you recommend grow up these 2 variables the dump_up, hash_area_size?



please write down the real join query...
records count are very small, so maybe there is some problems in join clause
and show us execution plan
HAL9000
Francisco,

"but in the other server this report of this query has a time of 1 min and now in this server has 3 min, is the same server."

With the same number of rows returned? With the same execution plan?

"The query of the join i can't get becuase take more of 20 min or more and take a lot resouces of server."

You can get the eecution plan withut running the query thusly:

set autotrace traceonly explain
select xxx

"So do you recommend grow up these 2 variables the dump_up, hash_area_size?"

Let's see the SQL and execution plan first!
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.