1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Optimizing query performance when joining tables

Discussion in 'SQL PL/SQL' started by sellyh19, Jun 21, 2011.

  1. sellyh19

    sellyh19 Active Member

    Messages:
    17
    Likes Received:
    0
    Trophy Points:
    80
    Hi all,

    I am experiencing performance problems on my queries. When i query from one table queries are fast but when i query on more tables by joining tables my queries take long. i tried using indexes on columns that are queried most oftenly but it is not helping. Do you know of any way how i can solve this issue?

    please help, thanks.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You provide no real information in your post; to give you a usable answer we'll need to see the queries and query plans generated by these queries as well as the table definitions and indexes created on them. You certainly wouldn't take your car to the mechanic and say 'It doesn't work' and expect him or her to know immediately the cause of your problems; you cannot expect that of us, either.

    Please provide the requested items.
     
  3. sellyh19

    sellyh19 Active Member

    Messages:
    17
    Likes Received:
    0
    Trophy Points:
    80
    here is my query and i am running it on Oracle application express (APEX)


    select /*+ INDEX (HUAWEIDATA_TB MSISDN_IDX )*/
    *
    from cdruser.huaweidata_tb@MTCCDRT a,
    cdruser.huaweidata_cocconditions_tb@MTCCDRT b
    where open_date >= to_date:)P405_start_date,'dd-mon-rrrr hh24miss') and
    open_date <= to_date:)P405_end_date,'dd-mon-rrrr hh24miss')
    and (record_type = '12' or record_type = '13' or record_type = '14')
    and a.open_date = b.hd_cocc_date (+)
    and a.local_seq_nr = b.hd_cocc_local_seq_nr (+)
    and a.msisdn_nr = :p405_msisdn_number
    order by open_date

    when i query on more than one day it takes very long and does not return anything you will have to stop it because its taking long.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Where is your execution plan, your table and index definitions? How fast is your network connection? You're querying across a database link which isn't necessarily the fastest responding configuration. Without a query execution plan and table/index definitions the query is pretty much worthless with respect to providing any useful tuning advice. You may need to:

    1) set autotrace on <----- type that exact text at the SQL> prompt
    2) let the query run to generate and display the plan and statistics
    3) post what autotrace reports

    so we can get some usable information. The posts thus far which you have made give us nothing with which to work.
     
  5. sellyh19

    sellyh19 Active Member

    Messages:
    17
    Likes Received:
    0
    Trophy Points:
    80
    Ok thanks, I will get back to you.
     
  6. sellyh19

    sellyh19 Active Member

    Messages:
    17
    Likes Received:
    0
    Trophy Points:
    80
    Please see the attached report that i obtained.
     

    Attached Files:

  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    How did you generate that output? It's useless if you used dbms_xplan.display without using the table() operator:

    select * From table(dbms_xplan.display);

    If you simply ran

    select * from dbms_xplan.display;

    it returns as unformatted collection text. Please use the first query above to generate this output or use autotrace to generate a plan and statistics.
     
  8. sellyh19

    sellyh19 Active Member

    Messages:
    17
    Likes Received:
    0
    Trophy Points:
    80
    First i run: explain plan for (my query)
    secondly i run: select * from plan table. then it gave me all the statisctis which i copied into a text document since i cant attach a .csv file.
     
  9. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    The output is useless; please do this:

    explain plan set statement_id= <something here> for <query here>;

    select * from table(dbms_xplan.display);

    The text in your file did not come from PLAN_TABLE, it likely came from dbms_xplan.display. See here:

    http://oratips-ddf.blogspot.com/2009/12/in-pickle.html