Table scans from application
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Table scans from application

  1. #1
    Join Date
    Aug 2012
    Posts
    9

    Table scans from application

    Hi,

    We have an application where delete statement is taking a long time. When I see the v$session_longops the sql statement is doing a full table scan. But when I do an explain plan on the delete statement it uses the index. The statement is "delete tablename where column_name =:B1". I am new to Oracle any ideas how I should proceed.

    Thanks,
    SK

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    I would do a 10046 trace so to be sure what the statement is actually doing.

    Couple of questions...
    1- How many rows out of the total number of rows on the table is supposed to delete the statement?
    2- I assume there is an index on the column_name used on the predicate of the delete statement - if this is the case, are table and index statistics up to date?

    Hope this helps.
    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
    Aug 2012
    Posts
    9
    1. 144 rows.
    2. Yes.
    We have few systems configured exactly the same. One few of them the delete from the application runs quickly. Today I did a query on v$sql for the system where it runs fine and on the other one. I see this difference, the columns for IS_BIND_SENSITIVE and IS_BIND_AWARE on the slower system is NO and on the one where the sql runs faster is YES. Any clues , I am going on this.

    Thanks,
    SK

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Are 144 rows deleted?

    How big is the table (number of rows) in the system that runs fast?

    How big is the table (number of rows) in the system that runs slow?

    Answering your question, IS_BIND_SENSITIVE=Y means that Oracle optimizer may generate different execution plans for the same query depending on the value of the bind variable.
    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.

  5. #5
    Join Date
    Aug 2012
    Posts
    9
    yes the 144 rows are deleted.

    4 million rows on the system that runs fast.

    9 million rows on the system that runs slow.


    Thanks,

    SK

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Please check how cursor_sharing parameter is set on both environments.
    Please also confirm exact Oracle version on both environments.
    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.

  7. #7
    Join Date
    Aug 2012
    Posts
    9
    Yes same exact version and patch level. 11.1.0.7.
    cursor_sharing is set to EXACT on both.

    Thanks,
    SK

  8. #8
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,999
    How are these parameters set? Do they favor full table scans?

    Code:
    SQL> show parameter optimizer_index
    
    NAME                                 TYPE                              VALUE
    ------------------------------------ --------------------------------- ------------------------------
    optimizer_index_caching              integer                           90
    optimizer_index_cost_adj             integer                           10
    this space intentionally left blank

  9. #9
    Join Date
    Aug 2012
    Posts
    9
    They are set to default values
    NAME TYPE VALUE
    ------------------------------------ --------------------------------- ------------------------------
    optimizer_index_caching integer 0
    optimizer_index_cost_adj integer 100

  10. #10
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    as weird as it sounds, you may have to "train" the slow Oracle instance by running the offending queries with different bind variable values (with different cardinalities) until you see on v$sql cursors that are both bind aware and bind sensitive.

    Be sure statistics got gathered with column histograms on the target table.

    Please check this document: http://iusoltsev.wordpress.com/the-e...in-oracle-11g/
    Last edited by PAVB; 05-22-2013 at 11:08 AM. 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.

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