SQL tuning
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: SQL tuning

  1. #1
    Join Date
    Mar 2006
    Posts
    176

    SQL tuning

    Hi all,

    I have questions about this sql query:

    Code:
    select start, medication, id_event, JOB_status from client_sim
    where id_even='ATC' and JOB_status in ('ACTIVE', NACTIVE', 'RUNNING')
    there are around 150,000 rows on two values and less then 20,000 rows on one value of job_status out out 40 mil. There are only less than 100 rows on id_even.

    I did two tests: the first test I created two indexes: one index for each column and the second test I created one index for both columns. The index NEVER get picked up from both tests. When I added the INDEX hint on both tests, then the indexes got picked up; however, I noticed the cost is even higher then FTS. From these two tests, I can possibly conclude that the index is being used in a FULL SCAN and and it's better to do FTS.

    1. is it correct analysis?
    2. I am thiking about partition the table, what's best method to deal with this table?
    3. Any other advises that can optimize the query?


    Thanks so much for your inputs.

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by matthew00 View Post
    Hi all,

    I have questions about this sql query:

    Code:
    select start, medication, id_event, JOB_status from client_sim
    where id_even='ATC' and JOB_status in ('ACTIVE', NACTIVE', 'RUNNING')
    there are around 150,000 rows on two values and less then 20,000 rows on one value of job_status out out 40 mil. There are only less than 100 rows on id_even.

    I did two tests: the first test I created two indexes: one index for each column and the second test I created one index for both columns. The index NEVER get picked up from both tests. When I added the INDEX hint on both tests, then the indexes got picked up; however, I noticed the cost is even higher then FTS. From these two tests, I can possibly conclude that the index is being used in a FULL SCAN and and it's better to do FTS.

    1. is it correct analysis?
    2. I am thiking about partition the table, what's best method to deal with this table?
    3. Any other advises that can optimize the query?


    Thanks so much for your inputs.
    Shall we understand there are about 100 rows that match condition (id_even='ATC') out of 150,000 rows in the table?

    If this is the case an index on id_even should be used - provided you have gathered performance statistics on table and index. Did you "analyzed" tablle and index? how old are those stats?

    When no stats are present Oracle assumes normal distribution then FTS is most likely - build a non-unique index on ID_EVEN, gather starts, try again.
    Last edited by PAVB; 06-11-2011 at 04:35 PM. Reason: typo
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Mar 2006
    Posts
    176
    Quote Originally Posted by PAVB View Post
    Shall we understand there are about 100 rows that match condition (id_even='ATC') out of 150,000 rows in the table?

    If this is the case an index on id_even should be used - provided you have gathered performance statistics on table and index. Did you "analyzed" tablle and index? how old are those stats?

    When no stats are present Oracle assumes normal distribution then FTS is most likely - build a non-unique index on ID_EVEN, gather starts, try again.
    PAVB,

    No, sorry if I am not clear with my post.

    On the table client_sim, there are only 100 rows on the columns ID_EVEN out of 40 million rows. On the column JOB_STATUS, it had the three values from the where clause and they are around 150,000 rows on two values: 'ACTIVE', NACTIVE' and around 20,000 rows on values: 'RUNNING'

    I did build index in ID_EVEN and JOB_STATUS seperately and I also update stat using DBMS_STATS.GATHER_TABLE_STATS, but for some reason the index never been used unless I force the hint on the index.

    Thoughts?

  4. #4
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    I did build index in ID_EVEN and JOB_STATUS seperately and I also update stat using DBMS_STATS.GATHER_TABLE_STATS, but for some reason the index never been used unless I force the hint on the index.
    how about Index stats? are you using cascade=> 'TRUE' option while gathering stats?

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  5. #5
    Join Date
    Mar 2006
    Posts
    176
    I had done that but still no luck.....

    thoughts?

  6. #6
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    generate 10053 trace and see on what basis oracle is selecting the current plan.

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width