Home » RDBMS Server » Performance Tuning » performance problem with query
performance problem with query [message #409333] Mon, 22 June 2009 03:31 Go to next message
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 #409424 is a reply to message #409333] Mon, 22 June 2009 09:45 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Any help or suggestion really apprecaited.


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 messageGo to next message
cookiemonster
Messages: 13952
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.
Re: performance problem with query [message #409468 is a reply to message #409429] Mon, 22 June 2009 17:18 Go to previous message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Create an index on COL1

Ross Leishman
Previous Topic: Database Hung due to CPU utilization
Next Topic: import table having 30000000 rows
Goto Forum:
  


Current Time: Sat Nov 09 18:22:50 CST 2024