Oracle queries use ORDER by slow optimization __oracle

Oracle version 10.1.0

A simple SQL, executed in the Pl/sql, the table has more than 20 W records:

SELECT * from table (1s)

SELECT * FROM table ORDER BY date_report Desc (18s)

Plus ORDER BY date_report Desc After the cost of 18 seconds, such speed I think no customer can accept.

Start optimization:

First set up Date_report index in the table, and found that the speed is not much improved

Checked a data, through Sqlplus query that Oracle default parameters (sort) sort_area_size is 64k, is not too small, up to 10240000, restart the database

Try again, no response or 18 seconds, where there is a problem.

Show parameter Sort_area_size view, right, clearly is the size of 10M;

Continue to check the data found in Oracle default parameters Workarea_size_policy=auto, this time sort_area_size is not working, and then continue to set parameters:

Alter system set workarea_size_policy=manual Scope=spfile;

Restart the DB again, flush the SQL, the speed is fast 2s open sql.