Problems in order by
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Problems in order by

  1. #1
    Join Date
    Jun 2006
    Posts
    31

    Problems in order by

    Hello all,

    I am executing the next select:

    select name from table; and it is very quickly, but if i execute the next:
    select * from table order by name; i need to wait hours.

    I am doing the order by the PK. What can be the problem?, the table has 3000000 of fields and the db_block_size=2048

    Thanks in advanced

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Look at your two explain plans.
    Look at your wait events.

    Your first query is just doing a FTS.
    Your second query has either to access each data row by index after doing doing a Full Scan on your PK index or doing a FTS and then sorting the 3 million rows.

    Once you have your second explain plan, try /* +FIRST_ROWS */ hint and check again your explain plan.

    Either way, waiting hours for a 3 million rows sort may be a symptom of something not being right in your environment
    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
    Jun 2006
    Posts
    31
    YES!!!! all is running quickly now, but is possible to change the optimizer_mode for all the database? because now i only was changing the session.

    Do you think that if i change the optimizer_mode for all the dabatase i will have problems of performance?

    Thanks for all

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    I'm glad it worked out.

    I wouldn't make any change at the database level.

    Be sure you have up-to-date statistics gathered for all tables and indexes, also check you have the proper indexes built to server your more critical queries.

    You may want to check your SGA setup.

    Determine which ones are your critical processes/queries and tackle them one by one.

    Remember, performance tuning is all about wait events, monitor your wait events, fix the issues you find one at a time, give yourself time to see how the database reacts to your changes and you are going to be just fine.
    Last edited by PAVB; 03-25-2008 at 09:31 AM.
    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
    Jun 2006
    Posts
    31
    Thanks, and thanks! now i am going to review all the queries with problems only, and no change the optimizer for all the database.

    Only the last question, why is the better way for monitor the wait events? no for OEM, i want by sqlplus.

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    You may do basic wait events monitoring with a single query like Burleson's http://www.praetoriate.com/oracle_ti...ent_events.htm but for troubleshooting you will have to research about the single wait event you are tacking and find your way to drill down on it.

    Plenty of queries out there but you had to find the ones that are appropriate for the wait event and scenario you are working on.
    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
    Jun 2006
    Posts
    31
    Excuse me, i continue with the problem because:

    I have the query:
    select a, b, c from table order by a; (i have index in the 3 fields (a, b, c)), but the explain plan no use the indexes.

    This is the explain plan:

    Plan Table
    --------------------------------------------------------------------------------
    | Operation | Name | Rows | Bytes| Cost | Pstart| Pstop |
    --------------------------------------------------------------------------------
    | SELECT STATEMENT | | 2M| 66M| 154490 | | |
    | SORT ORDER BY | | 2M| 66M| 154490 | | |
    | TABLE ACCESS FULL | table | 2M| 66M| 10270 | | |
    --------------------------------------------------------------------------------
    How can i force to execute by index, or is neccesary delete some index?

  8. #8
    Join Date
    Jun 2006
    Posts
    31
    Is neccesary an index in a table of 3000000 of registry, where the distinct registry in the field are 1800 ????

  9. #9
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Why do you think is gonna be cheaper to use an index? you have no filter -no WHERE clause therefore I'm tempted to agree with Oracle in doing a FTS.

    If you trully believe using a specific index is going to do any good you can force it using an index hint but, please compare explain plans before and after you made the change.

    By the way, next time please enclose code and explain plans into code-tags for better readability
    Last edited by PAVB; 03-25-2008 at 12:40 PM.
    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.

  10. #10
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by rnavarrc
    Is neccesary an index in a table of 3000000 of registry, where the distinct registry in the field are 1800 ????
    Shall I understand you have a 3 million rows table where a specific columns has only 1800 distinct values?

    Do you need an index there?

    I don't know, you tell me

    Are your queries filtering data based on the value of such a column?
    Are you using this table as a lookup table using such a column as the lookup key?
    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