Need some help in Tuning big and complex sorts.

[rborikar]

Hi ,

I am working in a Dataware housing project and we have really huge time consuming sorts(group by). I am thinking of tuning those. I need guidance from you guyes.

Pl let me know.

Thanks,

Rajesh

[jmodic]

It is hard to tell without any further information. The query together with tables and indexes informations, explain plan, statistics collected for this problem querry, ..... would help a lot.

Without this information and assuming the sorting is realy the main bottelneck some things that comes to mind are:

- what is the CPO and I/O load on the server during the execution of this problem query
- are there realy many disk sorts
- how is your temp tablespace configured (temporary or permanent)
- what are default settings (initial, next and pctincrease) for of your temp tablespace
- what is the size of your SORT_AREA_SIZE

[Halo]

One of the first things I would do is increase the sort_area_size. Use this query to find out how much sorting is done in memory as compared to on the disk and increase the parameter accordingly:
select name, value from v$sysstat where name like 'sort%';

[chrisrlong]

Another thought.

In datawarehousing, one often uses multiple cubes (fact tables) that not only use different combinations of your base dimensions but also different levels of summarization. For example, if your warehouse is based on a single star schema with sales being the main fact table, then you might have salesperson, customer and time dimensions. You might have many reports that don't care about the customer, so you create another cube, or fact table, that only has the salesperson and time dimensions. You might further care a lot about sales by quarter, so you may have yet another cube that has salesperson and time dimensions, but the time dimension is summarized to the quarter, rather than the day.

If you are doing a lot of grouping of a specific dimension (days rolled up to quarters), this might be a red flag for you to create a summarized cube. Now, I have yet to implement a star schema in Oracle, so I'm not positive about how to seamlessly implement these in Oracle. I would start with materialized views, however, as they would seem to do the trick.

HTH,

- Chris