Slow Order By queries

[soestx]

I have a table that since partitioning any query that has an order by clause in it runs extremely slow. If the ORDER BY clause is removed the results come back in seconds, with it in there takes 45 minutes. The table is setup as a range-hash partition with 6 sub partitions and a CLOB partition for each day of the week having 42 data subpartitions, 7 clob partitions, and 7 index partitions. The partitions and indexes have statistics run on them weekly and are up to date. In-memory sort according to statspack is 99.97. Any ideas?

Thanks!!

[slimdave]

Compare the execution plans with and without the order-by clause -- look for differences other than the order-by sort and group.

Check individual queries to see if they are sorting on disk or in memory.

[bolero]

ORDER BY slow could be due to heavy sorting.As slimdave advised check with the execution plan for both queries with & without sorting.

In sqlplus set the sort_area_size to large value & try to execute the qurey with order by clause & clock the time and see how it goes..

Without the qurey & plan we cant conclude anything

Cheers

[mike9]

How many records are returned?

You say: "If the ORDER BY clause is removed the results come back in seconds". So my question is does ALL records comme back within 1 sec? or only the first 100.

As far as I know it is recommanded that the number of hash subpartitions is a power of 2 (8,16,32).

[soestx]

I am getting the following explain plans. There are only about 50-200 records being returned from the queries but as you can see with the order by clause it is doing full table scans. There are indexes on creation_date, creation_time, and event_id.

EXPLAIN plan for
SELECT *
FROM log
WHERE creation_date >= To_Date('12/02/2003 04:45','mm/dd/yyyy hh24:mi') AND
creation_date <= To_Date('12/02/2003 04:46','mm/dd/yyyy hh24:mi')
ORDER BY creation_time, event_id
/

ID OPERATION OPTIONS OBJECT_NAM POSITION
--- ---------------- ---------- ---------- ------------------------
0 SELECT STATEMENT 5868524181316130000000
1 SORT ORDER BY 1
2 PARTITION RANGE ALL 1
3 PARTITION HASH ALL 1
4 TABLE ACCESS FULL EVENT 1

EXPLAIN plan for
SELECT *
FROM log
WHERE creation_date >= To_Date('12/02/2003 04:45','mm/dd/yyyy hh24:mi') AND
creation_date <= To_Date('12/02/2003 04:46','mm/dd/yyyy hh24:mi')
/

ID OPERATION OPTIONS OBJECT_NAM POSITION
--- ---------------- ---------- ---------- ------------------------
0 SELECT STATEMENT 9
1 PARTITION RANGE ALL 1
2 PARTITION HASH ALL 1
3 TABLE ACCESS BY LOCAL I EVENT 1
NDEX ROWID

4 INDEX RANGE SCAN EVENT_CREA 1
TE_DATE


Any ideas why such differences in the explain plans?

Thanks!!

[slimdave]

do you have the creation_date column analyzed?

It wouldn't be my first choice, but you could put an optimizer hint on the query to promote an index scan for the order by query.

[soestx]

I run the following on the table weekly ...

exec dbms_stats.gather_schema_stats( -
ownname => 'LOGGING', -
options => 'GATHER', -
cascade => TRUE, -
granularity => 'ALL', -
degree => 5);

So everything should be analyzed unless I am missing something. Optimizer hints have been working, but trying to avoid having to change the applications queries. The application queries were fast before partitioning was implemented and if that is the only route I will be forced to push it, but it seems to me that something could be done on the database end ... just stumped to figure out what.

Thanks,
Steven

[slimdave]

Try ...

analyze table log compute statistics for columns creation_date

... to get column stats, then look at the exec plan.

By the way, what's your init value optimizer_index_cost_adj set to?

[bolero]

WHERE creation_date >= To_Date('12/02/2003 04:45','mm/dd/yyyy hh24:mi') AND
-------------------------------------
try using "between" and see how it goes.

[jmodic]

Originally posted by bolero
WHERE creation_date >= To_Date('12/02/2003 04:45','mm/dd/yyyy hh24:mi') AND
-------------------------------------
try using "between" and see how it goes.

This can't make any difference whatsoever.