performance problem with query [message #409333] |
Mon, 22 June 2009 03:31 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
vikram1780
Messages: 222 Registered: January 2007 Location: Bangalore
|
Senior Member |
|
|
Hi,
Please suggest me better ways to tune the below queries.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3719259950
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14
| 1 | TABLE ACCESS BY INDEX ROWID| DEAL_STRUCTURES | 1 | 14
|* 2 | INDEX RANGE SCAN | DESR_DEIT_REFERS_SUCC_FK_I | 1 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DESR_DEIT_ID_REFERS_SUCC"=20002232)
filter("DESR_DEIT_ID_REFERS_SUCC"<>4243434343)
15 rows selected
---------------------------------------------------------------------------------------------------------------
SELECT nvl(ttdi_short_rom_code,tmac_short_rom_code) short_rom_code
INTO l_short_rom_code
FROM deal_items,
tested_dies,
tested_marked_ass_chips
WHERE deit_id = l_deit_id
AND deit_ttdi_id = ttdi_id(+)
AND deit_tmac_id = tmac_id(+)
SQL> explain plan for SELECT nvl(ttdi_short_rom_code,0) short_rom_code
2 FROM deal_items,
3 tested_dies,
4 tested_marked_ass_chips
5 WHERE deit_id = '1111'
6 AND deit_ttdi_id = ttdi_id(+)
7 AND deit_tmac_id = tmac_id(+);
Explained
SQL>
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4158858003
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 3 (0
| 1 | NESTED LOOPS OUTER | | 1 | 28 | 3 (0
| 2 | NESTED LOOPS OUTER | | 1 | 18 | 2 (0
| 3 | TABLE ACCESS BY INDEX ROWID| DEAL_ITEMS | 1 | 11 | 1 (0
|* 4 | INDEX UNIQUE SCAN | DEIT_PK | 1 | | 1 (0
|* 5 | INDEX UNIQUE SCAN | TMAC_PK | 58898 | 402K| 1 (0
| 6 | TABLE ACCESS BY INDEX ROWID | TESTED_DIES | 37189 | 363K| 1 (0
|* 7 | INDEX UNIQUE SCAN | TTDI_PK | 1 | | 1 (0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("DEIT_ID"=1111)
5 - access("DEIT_TMAC_ID"="TMAC_ID"(+))
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
7 - access("DEIT_TTDI_ID"="TTDI_ID"(+))
21 rows selected
request to help me in tuning below qury.
select * from table_name order by col1;
Its taking long time and having app 1 million rows.
Any help really appreciated
Thanks in advance
|
|
|
|
Re: performance problem with query [message #409429 is a reply to message #409333] |
Mon, 22 June 2009 10:36 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Well you've given us:
1) an explain plan without a query
2) A query with an explain plan that looks highly efficient.
3) Another query without an explain plan.
So maybe you should restate your question so that we know what goes with what.
And when you post queries you should always table aliases for every column so we can tell which ones come from which tables.
|
|
|
|