Home » RDBMS Server » Performance Tuning » how to find the query optimized or Not? (9i)
how to find the query optimized or Not? [message #362792] Thu, 04 December 2008 02:56 Go to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi ,

I am a PL/SQL Developer. I want to know what are the ways to identify the query is optimized or not? If we observe the explain plan if it shows full table scan it was not optimized .. are there any other steps to identify.

Please let me know.

Thank you

[Updated on: Thu, 04 December 2008 02:58]

Report message to a moderator

Re: how to find the query optimized or Not? [message #362804 is a reply to message #362792] Thu, 04 December 2008 03:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can first read:
- OraFAQ Forum Guide, Performance Tuning section
- How to Identify Performance Problem and Bottleneck
- Tuning sticky
- OraFAQ Performance Tuning
- Rosco's SQL Tuning Guide

You can also have a look at Database Performance Tuning Guide.

Regards
Michel
Re: how to find the query optimized or Not? [message #362805 is a reply to message #362804] Thu, 04 December 2008 03:13 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Micheal,

I have posted the thread in the correct forum,I don't have knowledge in tuning concepts. So I want to know the things like query tuning and statement tuning. That's why am asking I have explain plan for a query. In that query if it shows full table scan means that it was not optimized, like that are any other ways to identify the query was optimized or not? Please let me know.....

Thank you.
Re: how to find the query optimized or Not? [message #362809 is a reply to message #362805] Thu, 04 December 2008 03:19 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Show us the query ?
Show us the explain plan ?
From where did you produce the explain plan (hopefully not toad) ?
Re: how to find the query optimized or Not? [message #362815 is a reply to message #362809] Thu, 04 December 2008 03:30 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

I am asking the methods to identify the query was optimized or not... am not asking how to optimize the query..
Re: how to find the query optimized or Not? [message #362824 is a reply to message #362792] Thu, 04 December 2008 03:49 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you think there's a simple answer to that then you're sadly mistaken.

For example a full scan might mean the query is not optimised, or it might mean it's completely optimised - it depends on the query.
A low cost might mean the query is optimised, but you might have a query for which it is impossible to get a low cost - it depends on the query.

You are not going to get a better answer to your question than what Michel posted above. Read, and understand, the documents he's linked to.

[Updated on: Thu, 04 December 2008 03:51]

Report message to a moderator

Re: how to find the query optimized or Not? [message #362825 is a reply to message #362815] Thu, 04 December 2008 03:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Optimize thousand queries then you will know if query is optimized... you can also read the documentation and books, if there were some few short rules that can be written in a forum there would not be so many books on the subject.
In short, you have to work and work hard for this knowledge.

Regards
Michel
Re: how to find the query optimized or Not? [message #362832 is a reply to message #362825] Thu, 04 December 2008 04:10 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi ,

I observed in the following way.One of the worst assumption among lot of developers.This can be proved wrong easily. Here is a small example.I have created a table and indexed it.
[code]
SQL> create table t
2 as
3 select case when level = 1 then 1 else 99 end no, rpad('*',100,'*') namd
4 from dual
5 connect by level <= 100000
6 /
Table created.
SQL> create index t_idx on t(no)
2 /
Index created.
SQL> alter table t modify no not null
2 /
Table altered.
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade=>true)

PL/SQL procedure successfully completed.
Now lets see the explain plan for the below query.
SQL> explain plan for
  2  select *
  3    from t
  4   where no = 1
  5  / 
Explained.
 
SQL> select * from table(dbms_xplan.display)
  2  / 
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 470836197
-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |   104 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |   104 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IDX |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
   2 - access("NO"=1)
14 rows selected.

Nice!!! Going for a Index scan and the cost is very low.
Now lets try another query

SQL> delete from plan_table
2 /
3 rows deleted.
SQL> explain plan for
  2  select *
  3    from t
  4   where no = 99
  5  / 
Explained.
 
SQL> select * from table(dbms_xplan.display)
  2  / 
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1601196873
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 98466 |     9M|   346   (2)| 00:00:05 |
|*  1 |  TABLE ACCESS FULL| T    | 98466 |     9M|   346   (2)| 00:00:05 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
   1 - filter("NO"=99)
 
13 rows selected.

Oh!!! where did my index go. Why is it going for full scan. Hmmm... This is bad. I love index
so i am going to force the optimizer to use one.

SQL> delete from plan_table
2 /

2 rows deleted.
SQL> explain plan for
  2  select /*+ index(t t_idx) */ *
  3    from t
  4   where no = 99
  5  / 
Explained.
SQL> select * from table(dbms_xplan.display)
  2  / 
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 470836197
-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       | 98466 |     9M|  1719   (1)| 00:00:21 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     | 98466 |     9M|  1719   (1)| 00:00:21 |
|*  2 |   INDEX RANGE SCAN          | T_IDX | 98466 |       |   199   (3)| 00:00:03 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
   2 - access("NO"=99)
 
14 rows selected.

Wow!!!! check out the cost.So, the moral of the story is
for i in 1..Until you believe
loop
	"FULL TABLE SCAN IS NOT EVIL AND INDEX SCAN IS NOT ALL GOD"
end loop;

I have posted the thread after brief investigation. But I posted here to learn for new methodologies. But I didn't get yet. Hope now will get the proper response from you.

Thank you.

[Updated on: Thu, 04 December 2008 04:13]

Report message to a moderator

Re: how to find the query optimized or Not? [message #362855 is a reply to message #362825] Thu, 04 December 2008 05:38 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi ,

Please find the Plan table O/P and let me know the table was optimized or not.

PLAN_TABLE_OUTPUT 
 
------------------------------------------------------------------------------------------------------ 
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| 
------------------------------------------------------------------------------------------------------ 
| 0 | SELECT STATEMENT | | 1 | 116 | 9 (0)| 
| 1 | MERGE JOIN CARTESIAN | | 1 | 116 | 9 (0)| 
| 2 | NESTED LOOPS OUTER | | 1 | 101 | 7 (0)| 
| 3 | NESTED LOOPS | | 1 | 58 | 3 (0)| 
| 4 | TABLE ACCESS BY INDEX ROWID| CCE_SCHEDULE_WORK_BILLROUND | 1 | 45 | 2 (0)| 
| 5 | INDEX UNIQUE SCAN | CCE_SCHEDULE_WORK_BILLROUND_PK | 1 | | 1 (0)| 
| 6 | TABLE ACCESS BY INDEX ROWID| CCE_SUBSCRIBER | 1 | 13 | 1 (0)| 
| 7 | INDEX UNIQUE SCAN | CCE_SUBSCRIBERID_PK | 1 | | 0 (0)| 
| 8 | TABLE ACCESS FULL | CCE_NETWORK_ELEMENT | 1 | 43 | 4 (0)| 
| 9 | BUFFER SORT | | 1 | 15 | 5 (0)| 
| 10 | TABLE ACCESS BY INDEX ROWID | CCE_SUB_SVC_ASSOC | 1 | 15 | 2 (0)| 
| 11 | INDEX RANGE SCAN | CCE_SUB_SVC_ASSOC_ID_IDX | 1 | | 1 (0)| 
------------------------------------------------------------------------------------------------------ 
 
Note 
----- 
- 'PLAN_TABLE' is old version 


Thank you
Re: how to find the query optimized or Not? [message #362857 is a reply to message #362855] Thu, 04 December 2008 05:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
'PLAN_TABLE' is old version

First use the correct tool and upgrade your plan table.

Regards
Michel
Re: how to find the query optimized or Not? [message #362862 is a reply to message #362857] Thu, 04 December 2008 06:06 Go to previous message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Quote:

Plan table old version



Please ignore this.
Previous Topic: Createing index
Next Topic: LOCKWAIT
Goto Forum:
  


Current Time: Sat Dec 03 13:56:26 CST 2016

Total time taken to generate the page: 0.11338 seconds