| how to find the query optimized or Not? [message #362792] |
Thu, 04 December 2008 02:56  |
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 #362805 is a reply to message #362804] |
Thu, 04 December 2008 03:13  |
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 #362824 is a reply to message #362792] |
Thu, 04 December 2008 03:49 |
cookiemonster
Messages: 13925
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 #362832 is a reply to message #362825] |
Thu, 04 December 2008 04:10 |
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 |
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
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
