This discussion is archived
6 Replies Latest reply: Dec 6, 2012 4:32 AM by Dom Brooks RSS

Query Tuning - Question

DBA112 Newbie
Currently Being Moderated
Dear Experts,

RDBMS - 11.1.0.7. I'm running below insert statement from SQL Plus and right below query is its explain plan.

It's running too long, any suggestions please?

Edited by: Ora DBA on Dec 11, 2012 12:50 AM
  • 1. Re: Query Tuning - Question
    user503699 Expert
    Currently Being Moderated
    Ora DBA wrote:
    Dear Experts,

    RDBMS - 11.1.0.7. I'm running below insert statement from SQL Plus and right below query is its explain plan.

    It's running too long, any suggestions please?
    Usual questions...
    1) What are your expectations about performance and how much time does it take at present?
    2) Are the stats up-to-date?
    3) Why are you using a SQL Profile?
    4) Post other details as mentioned in those FAQ threads

    If your estimates in EXPLAIN PLAN are anywhere near actual data volume, then the obvious time (& resource) consuming operation that stands out is
    |  18 |                NESTED LOOPS            |                          |    43M|  4164M|       |   320K  (2)| 01:04:08 |
    |  19 |                 TABLE ACCESS FULL      | ORDER_RELEASE_STATUS     |    77M|  5894M|       |   318K  (1)| 01:03:38 |
    |* 20 |                 INDEX RANGE SCAN       | XIE1_SNI_PA_OTM_ARC_DRVR |     1 |    20 |       |     1   (0)| 00:00:01 |
    It will take a lot of time to do a NL join on 77m records. It could be due to that sql profile.
  • 2. Re: Query Tuning - Question
    DBA112 Newbie
    Currently Being Moderated
    Thanks for the reply. I see most tables in the schema have old stats, gathering stats now to see if it helps
  • 3. Re: Query Tuning - Question
    Dom Brooks Guru
    Currently Being Moderated
    Why will gathering stats make any difference if there is a SQL profile in place?
  • 4. Re: Query Tuning - Question
    DBA112 Newbie
    Currently Being Moderated
    Hi Brooks,

    Actually the problem is with a number of Jobs running slow in the database. I noticed schema has almost all tables with stale stats. So, I've decided to gather stats for all tables.

    As for SQL profile in place, Yes, I believe it wouldn't make a difference as it used the profile. Can I just disable that? or drop it completely?

    Thanks
  • 5. Re: Query Tuning - Question
    905562 Explorer
    Currently Being Moderated
    I'd check why it is there first....

    Typically things have been added for a reason, and although it may not be obvious to you why that was...I'd still find that out.

    You may be able to fudge the query a little to 'break' it from picking up the profile to test that execution plan/change your category in your session so it is not picked up and check that 'default' plan.
  • 6. Re: Query Tuning - Question
    Dom Brooks Guru
    Currently Being Moderated
    Can I just disable that? or drop it completely?
    Yes. See [url http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_sqltun.htm]DBMS_SQLTUNE.ALTER_SQL_PROFILE to disable.

    Disabling will be better than dropping in case you change your mind about dropping.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points