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

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 3 Oct 2016 20:11:26 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D90150342324_at_exmbx05.thus.corp>

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

Received on Mon Oct 03 2016 - 22:11:26 CEST