| speeding up scanning of internal temporary table (of materialize hint) [message #549794] |
Tue, 03 April 2012 23:26  |
 |
orapratap
Messages: 134
Registered: November 2011
Location: Canada
|
Senior Member |
|
|
Hello
I have a query like following
WITH v_results as
(
Select /*+ materialize full(t1) parallel(t1) */
col1,
col2
col3
col4
from t1
where col5 = <value>
and col6 = <value>
)
select /*+ parallel(v_results) full(v_results) ordered */
t2_col1,
t2_col2,
t3_col1,
t3_col2,
v_results.col3
from v_results, t2, t3
where t2.col1 = v_results.col1
...........
..............
Now I have 2 issues here
1) The query inside the WITH clause returns more than few 100k rows in 3-4 seconds (with parallel hint)
it accesses 425984 blocks
But then the hash join starts (as observed from (longops) and it literally crawls for 2-2.5 hours
Of course the tables with which the results of WITH query are joined, are big, too
But is there any workaround to speed up the hash join in such situation?
when observed from logops it reads almost block by block
How do I know why the hash_join is slow? memory or /and something ?
My another question pertains to the WITH clause
We can execute the query in WITH clause using parallel hint but can we later scan the internal temporary table (as created using materialize hint)
in, parallel mode?
Thanks and Regards
Orapratap
|
|
|
|
|
|
|
|
| Re: speeding up scanning of internal temporary table (of materialize hint) [message #549819 is a reply to message #549814] |
Wed, 04 April 2012 01:49  |
John Watson
Messages: 8989
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Quote:I understand that for analysing / suggesting performance related changes certain inputs are required e.g ddl, sql statement, execution plan etc.
Unfortunately in my case I do have only a gui tool (sql developer) and thus I am unable to provide plan etc.
SQL Developer can generate explain plan, DDL, autotrace, no problem. It is actually rather easier with SQL Developer than with SQL*Plus.
|
|
|
|
|
|
|
|
| Re: speeding up scanning of internal temporary table (of materialize hint) [message #549828 is a reply to message #549821] |
Wed, 04 April 2012 03:01 |
John Watson
Messages: 8989
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
The problem you have is not to do with those detailed Qs. The problem is that you are not following the standard technique for tuning SQL (or, indeed, for problem solving in general). It is this:
1. Observe facts about the statement.
2. Construct a hypothesis regarding why the statement is too slow (if it is)
3. Test the hypothesis
4. Evaluate the result of the test
5. If the problem is not solved, construct another hypothesis and try again.
In your particular case, step 1 requires running the statement with no hints, using autotrace. This will give you the necessary facts.
Step 2: your first hypothesis would seem to be that the statement is slow because a sub-query is not being materialized.
Step 3: test this by insertng the hint, and running the statement with autotrace.
Step 4: compare the results of 1 and 3.
Step 5: only at this point can you evaluate the effectiveness of your hypothesis, and consider whether to discard it, modify it, or implement it.
This technique is the universal technique for problem solving. It is known as the Scientific Method.
|
|
|
|
| Re: speeding up scanning of internal temporary table (of materialize hint) [message #549832 is a reply to message #549828] |
Wed, 04 April 2012 03:17 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
SQL Developer can offer sql*plus style explain plans, you just need to write the code as you would in sql*plus.
i.e.
explain plan for select * from dual;
select * from table(dbms_xplan.display());
You would need to copy/paste it from the results pane, as I recall the 'query' run rather than 'script' run provides better formatting (but it may be the other way around, takes a second to test  ).
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
