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

Juan,

I opened an SR with Oracle last year on the exact same thing (11.2.0.4 db on AIX), and they had me mark indexes invisible during the gather, change cursor_sharing at the session level, set the hidden parameter _replace_virtual_columns=false and on and on. Is this by any chance a partitioned table?

We finally settled on a “work-around” that included having us set the sample size down to 0.009.

Before recommending you make any of the changes we made, I’d suggest an SR with Oracle.

Thanks,
Scott

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Juan Carlos Reyes Pacheco Sent: Monday, October 03, 2016 3:11 PM
To: ORACLE-L <oracle-l_at_freelists.org> 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 Notice of Confidentiality: **This E-mail and any of its attachments may contain Lincoln National Corporation proprietary information, which is privileged, confidential, or subject to copyright belonging to the Lincoln National Corporation family of companies. This E-mail is intended solely for the use of the individual or entity to which it is addressed. If you are not the intended recipient of this E-mail, you are hereby notified that any dissemination, distribution, copying, or action taken in relation to the contents of and attachments to this E-mail is strictly prohibited and may be unlawful. If you have received this E-mail in error, please notify the sender immediately and permanently delete the original and any copy of this E-mail and any printout. Thank You.**

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