Gathering statistics on partitioned tables

Question

Ultimately i need to know if this will be enough. In oracle, there is a setting on a table to incrementally gather statistics, rather than a full table.  Basically, it will only gather stats on partitions where the data has changed.  We need to make sure all partitioned tables have INCREMENTAL set to TRUE.



On Partitioning Tables just setting Incremental to true is enough or do we have to also set Publish command to true as well? If so how can i add it?

        BEGIN

        DBMS_STATS.SET_TABLE_PREFS ('ANT', 'S_WAREHOUSE_PRODUCT_FACT', 'INCREMENTAL', 'TRUE');

        END

P Lease let me know if something needs to be changed or added to the code. Is this necessary for what i am doing?

        1) The PUBLISH value for the partitioned table is true.(Default is TRUE)
        2)The user specifies AUTO_SAMPLE_SIZE for ESTIMATE_PERCENT and AUTO for GRANULARITY when gathering statistics on the table.(Default is ESTIMATE_PERCENT=>AUTO_SAMPLE_SIZE and GRANULARITY=>AUTO)

    How can i verify if tables already has publish set to true?
    Can i leave default value as it is? Default is 
    ESTIMATE_PERCENT=>AUTO_SAMPLE_SIZE and GRANULARITY=>AUTO

I think you need to use a bind variable when invoking a package proc like this. There are a few examples of ORA based exec lines on SO. Some of them might mention this. See: https://ss64.com/ora/exec.html — , Jun 08 '18 at 13:46
You don't use `EXEC` in a `BEGIN..END` block - it's a SQL*Plus command, not a PL/SQL command. Just remove the EXEC and this should work fine. — kfinity, Jun 08 '18 at 13:50
ORA-06550: line 7, column 1: PLS-00103: Encountered the symbol "BEGIN" — user9766188, Jun 08 '18 at 14:24
BEGIN DBMS_STATS.SET_TABLE_PREFS ('DW_FEI', 'WAREHOUSE_PRODUCT_FACT', 'INCREMENTAL', 'TRUE'); COMMIT; END; / — user9766188, Jun 08 '18 at 14:25
Please don't put additional Information in comments, edit your question — Wernfried Domscheit, Jun 08 '18 at 15:52

score 0 · Answer 1 · answered Jun 08 '18 at 14:04

0

Either use

BEGIN
    DBMS_STATS.SET_TABLE_PREFS ('DW_FEI', 'INVOICE_HEADER_FACT', 'INCREMENTAL', 'TRUE');
END; 
/

or

EXEC DBMS_STATS.SET_TABLE_PREFS ('DW_FEI', 'INVOICE_HEADER_FACT', 'INCREMENTAL', 'TRUE');

But not both at the same time.

answered Jun 08 '18 at 14:04

Wernfried Domscheit

54,457
9
76
110

I need to commit as well – user9766188 Jun 08 '18 at 14:16
Then append a commit; - where is the problem? – Wernfried Domscheit Jun 08 '18 at 14:21
I am trying execute mutlpile statements i get same error [Error] Execution (7: 1): ORA-06550: line 7, column 1: PLS-00103: Encountered the symbol "BEGIN" BEGIN DBMS_STATS.SET_TABLE_PREFS ('DW_FEI', 'BMI_SALES_FACT', 'INCREMENTAL', 'TRUE'); COMMIT; END; / BEGIN DBMS_STATS.SET_TABLE_PREFS ('DW_FEI', 'AP_OPEN_HEADER_RAY', 'INCREMENTAL', 'TRUE'); COMMIT; END; / BEGIN DBMS_STATS.SET_TABLE_PREFS ('DW_FEI', 'AP_OPEN_HEADER_FACT', 'INCREMENTAL', 'TRUE'); COMMIT; END; / – user9766188 Jun 08 '18 at 14:29

score 0 · Answer 2 · answered Jun 08 '18 at 14:44

AS Wernfried pointed out, the EXEC is wrong. You can call more than one procedure in a begin end block:

BEGIN
   DBMS_STATS.SET_TABLE_PREFS ('DW_FEI', 'BMI_SALES_FACT', 'INCREMENTAL', 'TRUE'); 
   DBMS_STATS.SET_TABLE_PREFS ('DW_FEI', 'AP_OPEN_HEADER_RAY', 'INCREMENTAL', 'TRUE')
   DBMS_STATS.SET_TABLE_PREFS ('DW_FEI', 'AP_OPEN_HEADER_FACT', 'INCREMENTAL', 'TRUE');
END;
/

And you don't need to COMMIT, DBMS_STATS (and all other DML statements) do that automatically (or implicitly) for you. As proof:

CREATE TABLE t (i NUMBER);

SELECT * FROM USER_TAB_STAT_PREFS WHERE table_name='T';

BEGIN
  DBMS_STATS.SET_TABLE_PREFS (NULL, 'T', 'INCREMENTAL', 'TRUE');
END; 
/

(in other session:)

SELECT * FROM USER_TAB_STAT_PREFS WHERE table_name='T';

TABLE_NAME PREFERENCE_NAME PREFERENCE_VALUE
T          INCREMENTAL     TRUE

Gathering statistics on partitioned tables

2 Answers2