Home » RDBMS Server » Performance Tuning » speeding up scanning of internal temporary table (of materialize hint) (Oracle 10.2.0.4)
speeding up scanning of internal temporary table (of materialize hint) [message #549794] Tue, 03 April 2012 23:26 Go to next message
orapratap
Messages: 113
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 #549795 is a reply to message #549794] Tue, 03 April 2012 23:28 Go to previous messageGo to next message
BlackSwan
Messages: 23133
Registered: January 2009
Senior Member
Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: speeding up scanning of internal temporary table (of materialize hint) [message #549814 is a reply to message #549795] Wed, 04 April 2012 01:06 Go to previous messageGo to next message
orapratap
Messages: 113
Registered: November 2011
Location: Canada
Senior Member
Hello BlackSwan

Thanks for your reply

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.

And I really have generic questions.

Table(s) are accessed fast using parallel hint but then some execution steps stumble

1) Can we expedite scanning of internal temporary table created by 'materialize hint'?
2) Can we speed up hash_join by means other than altering pga?
3 How do I know my hash joins is slow because og pga only?

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 Go to previous messageGo to next message
John Watson
Messages: 4857
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 #549821 is a reply to message #549819] Wed, 04 April 2012 02:06 Go to previous messageGo to next message
orapratap
Messages: 113
Registered: November 2011
Location: Canada
Senior Member
Hello John

Thanks for your reply

There are many ocassions when the plan displayed in sql developer wasn't the actual plan used by the sql statement

I do not have access to tkprof and the server where trace files are generated

You would agree that Autotrace is also not what will give us the exact picture
ex. on particular machine following won't be possible ..will autotrace indicate it!
select /*+ parallel(dbo 132) */ * from dbo;


BTW do we really think the questions I asked need all these details?

Though I have learnt many things from this forum I would say I am not convinced why all generic questions would need details like sqls/ ddls and plan etc. almost every time!

Thanks and Regards
Orapratap





Re: speeding up scanning of internal temporary table (of materialize hint) [message #549825 is a reply to message #549821] Wed, 04 April 2012 02:29 Go to previous messageGo to next message
Michel Cadot
Messages: 59968
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do not think, just provide and let us decide what is needed and what is not, it you don't know the answer to your questions, you can't determine this for us.

Regards
Michel
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 Go to previous messageGo to next message
John Watson
Messages: 4857
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 Go to previous message
Roachcoach
Messages: 1233
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 Smile ).
Previous Topic: Parallel operations not executing with expected degree
Next Topic: session doing FTS not appearing in longops
Goto Forum:
  


Current Time: Wed Dec 17 17:42:07 CST 2014

Total time taken to generate the page: 0.09081 seconds