We are never truncating any partition; either dropping or creating. And there are 71 partitions in the table having segment_created='NO'
But strangely, the above piece of code it's executing successfully sometimes. I can say the success:failure ratio is 3%:97%. Every time we are adding a partition and loading some data, this gather_table_stats is being called. And it has been noticed that when the volume of data load is considerably less (<5000 rows), it's a success.
The db version is 220.127.116.11.0. Sometimes, it may happen that, the partition newly created without data. But it also failing for newly created partitions containing data.
Are there any Oracle GURU over there. Please put some pointers.
You still haven't answered one of the questions that TSharma ask you.
And you haven't posted code that shows ANYTHING about this:
Every time we are adding a partition and loading some data, this gather_table_stats is being called.
1. How are you 'adding a partition'
2. 'loading some data'
3. gathering table stats
I can see all the partitions in dba_tab_partitions.
1. How are you 'adding a partition' - ALTER TABLE ADD PARTITION .. from within application code
2. 'loading some data' - Via SQL Inserts inside package
3. gathering table stats - After the above inserts, we are calling the DBMS_STATS.GATHER_TABLE_STATS
1b. Can you be overlapping these adds and gathers? YES, there are 99 % chances of overlapping these 2 opearations
Please ask, if i left any questions unanswered.
It sounds like the overlap is trying to gather stats for partitions that aren't really there yet, but do like rp asks and copy/paste the items for us to peruse. Go into the advanced editor and change to a fixed font to make such pastes legible.
Remember that DDL does two commits; one before and one after. This can cause strangeness if you are doing it in app code, especially if some other concurrent process expects serialization. This can also cause some strange logic if a session calling a package with commits in it expects a transaction to be consistent.
Here goes the code. Below procedure has been scheduled to execute every 30 minutes. As you can see we are taking multiple tables, for one particular table, GATHER_TABLE_STATS is taking 3-5 hours to complete (if some data has been loaded). And that particular table is LIST PARTITIONED by RUN_ID, now having 4779 partitions and 120 GB of size.
CREATE OR REPLACE PROCEDURE pr_daily_sync(p_run_id IN NUMBER)
CURSOR sync_tab_cur IS SELECT sync_table_name, load_table_name FROM coll_tables WHERE etl_load_status=1;
v_sync_count NUMBER := 0;
FOR rec IN sync_tab_cur LOOP
v_sync_count := 0;
IF NOT fn_is_partition_exists(rec.sync_table_name, 'P' || TO_CHAR(p_run_id)) THEN
EXECUTE IMMEDIATE 'ALTER TABLE ' || rec.sync_table_name || ' ADD PARTITION P' || TO_CHAR(p_run_id) || ' VALUES(' || TO_CHAR(p_run_id) || ')';
EXCEPTION WHEN OTHERS THEN
pr_sync_data(rec.sync_table_name, rec.load_table_name, v_sync_count); -- MERGES DATA INTO TABLE
IF v_sync_count > 0 THEN
DBMS_STATS.GATHER_TABLE_STATS(ownname => USER, tabname => rec.sync_table_name, estimate_percent => 10, granularity => 'ALL', cascade => TRUE, no_invalidate => FALSE);