Problems in order by

[rnavarrc]

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

[PAVB]

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

[rnavarrc]

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

[PAVB]

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.

[rnavarrc]

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.

[PAVB]

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.

[rnavarrc]

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?

[rnavarrc]

Is neccesary an index in a table of 3000000 of registry, where the distinct registry in the field are 1800 ????

[PAVB]

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

[PAVB]

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?