Database: Oracle 12c

I have a process that selects data from Fact Table, summarizes it and pushes it to Summary Table.

Summary table is Range Partitioned (Trade Date) and List Partitioned (File Id).

the process picks up data from Fact table (where file_id=<> for all Trade Dates), summarizes it in a temp table and use Partition Exchange to move data from Temp table to one of the SubPartitions in Summary Table (as the process works on a File Id level).

Summary table is completely refreshed everyday (100% data will be exchanged).

Before the data is exchanged at the subpartition level, statistics are gathered and exchanged along with the data.

After the process is completed, we run dbms_gather_table_stats at partition level (in a for loop - for each partition) with granularity set as "approx_global and partition".

Even though we collect stats at the global level, user_tab_statistics for the summary table has "STALE_STATS" = YES for this table, however, partition & Subpartition stats are available.

when we run a query against the summary table (for a date range of 3 years), the query spins for a long time - spiking the CPU to 90%, but never returns any data.

I checked the explain plan on the query, Cardinality is showing as 1.

I read about incremental stats, but it seems increment will work if a few partitions change - it may not be the best option in my case, where data across all the partitions change completely.

I m looking for a strategy to gather statistics on the summary table - don't want to run a full gather stats.

  • 1
    If you provide explain plan of the slow running query or SQL trace information it would easier to find where the problem is. – JSapkota Feb 20 '17 at 7:43
  • You said "I m looking for a strategy to gather statistics on the summary table - don't want to run a full gather stats." .... try exporting those stats and copying them back on summary table, after the data-load. If your data stays "similar" on day-to-day basis you might be in luck. You may need to adjust some of the high/low values but it isn't impossible. – Raj Feb 20 '17 at 13:05
  • @JSaptoka: I checked the explain plan and cardinality estimates were wrong - as I mentioned cardinality came back as 1, even though the table has about 100+ Million records. When the query spans across multiple partitions, it is looking for Global Stats at the table level and that's where the problem is. STALE_STATS for object_type Table is YES. – sk411 Feb 20 '17 at 14:59
  • @Raj: I read about that option, but somehow not convinced that it will work. But I'll give it shot and see what happens. – sk411 Feb 20 '17 at 15:00
  • Since the table is completely refreshed every day, it sounds like this may be a good use case for simply locking the table stats and/or locking the partition stats after you have representative data in the table: docs.oracle.com/database/121/ARPLS/d_stats.htm#ARPLS68628, sqlmaria.com/2017/01/24/…, and blogs.oracle.com/optimizer/entry/… – Kris Johnston Feb 20 '17 at 15:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.