Re: Is there a way to skip the creation of a temporal table when gathering statistics?

Thank you Jhonatan, I didn't understand you, can you repeatme please :) I have an standard edition one 11.2, and a lot of tables I didn't found any reason to set special gathering statistics for each table, but I heard some times there are bugs with histograms, etc.

This is the way I gather statistics in my dbs. If you are suggesting me to change the way could you show me in an example please, thank you :)

// table

• DBMS_STATS.GATHER_TABLE_STATS('SOA','HICARTERA_RW',ESTIMATE_PERCENT=>2,method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY FOR ALL COLUMNS');* //full execute dbms_stats.gather_system_stats('Start'); eXEC DBMS_STATS.gather_dictionary_stats; EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS; EXEC DBMS_STATS.GATHER_database_STATS( GATHER_SYS=>TRUE,ESTIMATE_PERCENT=>2,cascade=>true); EXEC DBMS_STATS.GATHER_database_STATS( GATHER_SYS=>TRUE,ESTIMATE_PERCENT=>2,METHOD_OPT=>'FOR ALL COLUMNS SIZE SKEWONLY',cascade=>true); execute dbms_stats.gather_system_stats('Stop'); commit; -- fix bug 11g BEGIN FOR A IN ( SELECT PNAME,PVAL1 FROM SYS.AUX_STATS$ WHERE SNAME = 'SYSSTATS_MAIN' AND PNAME IN ('SREADTIM','MREADTIM') ) LOOP IF (A.PVAL1 > 75) THEN DBMS_STATS.SET_SYSTEM_STATS(A.PNAME, a.PVAL1 / 10000); DBMS_OUTPUT.PUT_LINE('MREADTIM ALTERED TO: ' || a.PVAL1 / 10000); END IF; END LOOP; COMMIT; END; /

2016-10-03 16:11 GMT-04:00 Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>:

>
>
> I am puzzled that that is the only statement you have a problem with.
> That call to dbms_stats ought to produce an insert that lists every single
> column in the table, plus the virtual columns supporting function-based
> indexes.
> As it is your table appears to consist of one column with a function-based
> index (or some variant thereof).
>
> Since you're on 11g you should be using for all columns size 1 with
> auto_sample_size (not 2 percent) to take advantage of the approximate_ndv
> to get the most of the table stats.
>
> If you then think these two columns need a histogram you either live with
> an expensive insert and histogram generation, or you write some SQL that
> creates the histograms from scratch (or, if you're amazingly lucky) use
> method_opt in a separate call to specify just the columns that you want to
> have histograms and live with the tiny sample that Oracle will use for
> generating a histogram with auto_sample_size.
>
>
> Regards
> Jonathan Lewis
> http://jonathanlewis.wordpress.com
> _at_jloracle
> ------------------------------
> *From:* oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on
> behalf of Juan Carlos Reyes Pacheco [jcdrpllist_at_gmail.com]
> *Sent:* 03 October 2016 20:10
> *To:* ORACLE-L
> *Subject:* Is there a way to skip the creation of a temporal table when
> gathering statistics?
>
> when gather statistics
>
> * DBMS_STATS.GATHER_TABLE_STATS('SOA','HICARTERA_RW',ESTIMATE_PERCENT=>2,method_opt=>'FOR
> ALL COLUMNS SIZE SKEWONLY FOR ALL COLUMNS'); *I found an insert that is
> taking a lot of time.
> Is there a way to skip it, like increasing pga memory, or something like
> that.
> Thank you :)
>
>
> INSERT /*+ append */INTO SYS.ora_temp_1_ds_1342719
> SELECT /*+ no_parallel(t) no_parallel_index(t) dbms_stats
> cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
> no_substrb_pad */
> "CAR_QUIEN_CUST", "SYS_NC00097$" sys_ds_alias_2,
> "SYS_NC00094$" sys_ds_alias_3, "CAR_ESVIRF" (cut)
> FROM "SOA"."HICARTERA_RW" SAMPLE (2.0000000000) t
> UNION ALL
> SELECT "CAR_QUIEN_CUST", sys_ds_alias_2, sys_ds_alias_3, "CAR_ESVIRF"
> (cut)
> FROM SYS.ora_temp_1_ds_1342719
> WHERE 1 = 0
>
>
>

--
http://www.freelists.org/webpage/oracle-l