Home » SQL & PL/SQL » SQL & PL/SQL » how to execute the a long query step by step (Oracle 10g)
how to execute the a long query step by step [message #314039] Tue, 15 April 2008 11:26 Go to next message
ven_vick
Messages: 3
Registered: April 2008
Junior Member
Hi,

Is there any tool to execute a long query step by step which displays the intermediate results?

Thanks,
Ven
Re: how to execute the a long query step by step [message #314041 is a reply to message #314039] Tue, 15 April 2008 11:36 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Welcome to the forum. Apologies for being bit rude in your very first post. Check this link.
http://catb.org/~esr/faqs/smart-questions.html

So what we require is the following
a) spend some time in reading the forum guidelines.
b) Ask yourself, is there any ambiguity in the question ?

Regards

Raj

[Updated on: Tue, 15 April 2008 11:39]

Report message to a moderator

Re: how to execute the a long query step by step [message #314043 is a reply to message #314039] Tue, 15 April 2008 12:21 Go to previous messageGo to next message
ven_vick
Messages: 3
Registered: April 2008
Junior Member
My question is ---- I am trying to understand, real long queries spanning multiple pages with several sub queries and joins , written by others.

So is there any way to see the how each sub query is executed and the result being sent to the outer queries, statement by statement.

In SQL Developer I see a drop down menu "DEBUG" but it is grayed out as I am not using PL/SQL code.

Are there any other tools for this purpose?

Thanks,
Ven
Re: how to execute the a long query step by step [message #314044 is a reply to message #314043] Tue, 15 April 2008 12:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Are there any other tools for this purpose?

No there is none.

Regards
Michel
Re: how to execute the a long query step by step [message #314045 is a reply to message #314043] Tue, 15 April 2008 12:29 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
explain plan
for
<query>;
select * from table(dbms_xplan.display());

Right, to start with execute the above statement and you can see the execution path which oracle could potentially use to give you the output. This will give you some sort of an idea.
Debug option is used to debug your code and it will not help you to debug or trace through your access path of your query.

for example :
SQL> explain plan
     for
     select * from emp;
SQL> /

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2872589290

----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |
|   1 |  TABLE ACCESS FULL| EMP  |
----------------------------------

Note
-----

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   - rule based optimizer used (consider using cbo)

12 rows selected.


SQL> exec dbms_stats.gather_table_stats(NULL,'EMP');

PL/SQL procedure successfully completed.

SQL> explain plan for
  2  select * from emp;

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2872589290

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |   518 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |   518 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

8 rows selected.

It is also worthwhile visiting the forum guidelines/stick notes in the performance tuning section.

Hope that helps.

Regards

Raj

[Updated on: Tue, 15 April 2008 12:31]

Report message to a moderator

Re: how to execute the a long query step by step [message #314055 is a reply to message #314043] Tue, 15 April 2008 13:18 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Check this link. It might be of some more help to you.

http://www.oracle-developer.net/display.php?id=316

Regards

Raj
Previous Topic: ORA-01747 (merged 2 threads)
Next Topic: Error in PL/SQL Language Reference .... could be that possible ?
Goto Forum:
  


Current Time: Tue Dec 06 04:41:19 CST 2016

Total time taken to generate the page: 0.14034 seconds