RE: How to disable Extended Statistics in Oracle 12c

From: Tiwari, Yogesh <"Tiwari,>
Date: Fri, 18 Jan 2019 03:26:01 +0000
Message-ID: <16B7E76F772C9141A2BCA83CD8D9250162841C4D_at_INDEL7005WIN.intl.intlroot.fid-intl.com>



Thanks for sharing note, Sourav.
However, interestingly note mentions somethings odd, though.

"The parameter is valid only for integrated extract(IE) and it do not have any effect for Classic Extract because **CE can't mine hidden columns.**"

Thanks,
Yogi | Technical Consultant - Databases | Fidelity International 5th Floor, Building 9, Candor Tech Space, Sector 48, Gurgaon 122 002, Haryana, India. T: +91 124 615 3656 | I: 8 779 3656 | E: yogesh.tiwari_at_fil.com<mailto:yogesh.tiwari_at_fil.com> Disclaimer: The information transmitted is intended for the person or entity to which it is addressed and may contain confidential, privileged or copyrighted material or attorney work product. If you receive this in error, please contact the sender and delete the material from any system. Any unauthorised copying, disclosure or distribution of the material in this e-mail is strictly forbidden. Any comments or statements made are not necessarily those of Fidelity. All e-mails may be monitored or recorded.

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Sourav Biswas Sent: 17 January 2019 21:47
To: Oracle Mailinglist <oracle-l_at_freelists.org>; Stefan Knecht <knecht.stefan_at_gmail.com> Subject: Re: How to disable Extended Statistics in Oracle 12c

Hi Stefan,
Using Golgengate 12.2.0.2, it replicates all the hidden columns, including System generated. Even "defgen" utility is able to get details of all such hidden columns. However, I came across Doc ID 2292517.1, which helped me fix my issue. To implement the fix, I'd to upgrade the Extract process from Classic to Integrated. And rest now the trail files no longer contain hidden columns. Although, defgen still reflects them, but I manually removed those selected columns and everything is in good shape. Thanks and regards,
Sourav Biswas



From: Stefan Knecht <knecht.stefan_at_gmail.com<mailto:knecht.stefan_at_gmail.com>> Sent: Thursday, January 17, 2019 5:15:47 PM To: biswas.sourav_at_hotmail.com<mailto:biswas.sourav_at_hotmail.com>; Oracle Mailinglist Subject: Re: How to disable Extended Statistics in Oracle 12c

This sounds like a Goldengate bug to me. These columns can't and shouldn't be dealt with during replication, at least not that I could see it make any sense. They're internal and maintained by Oracle.

Have you tried filing an SR against GoldenGate ?

Stefan

On Thu, Jan 17, 2019 at 6:01 PM Sourav Biswas <biswas.sourav_at_hotmail.com<mailto:biswas.sourav_at_hotmail.com>> wrote: Hi Jonathan,

Thanks for your prompt reply.

We have a Goldengate(12.2.0.2) setup, that replicates these tables to Abinitio(Using Goldengate BigDataAdapter), which further is consumed by Hadoop.

Now, when we have Hidden Columns on Goldengate trailfiles, there respective processes fail as they are not able to match data. In case we have to stick with DB hidden columns, then the application team need to re-write a lot of codes to map all cols with correct data type. In particular these are SIEBEL tables, which have huge number columns in the tables.

Best Regards,
Sourav Biswas
+91-9650017306



From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> <oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>> on behalf of Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk<mailto:jonathan_at_jlcomp.demon.co.uk>> Sent: Thursday, January 17, 2019 4:08 PM To: Oracle Mailinglist
Subject: Re: How to disable Extended Statistics in Oracle 12c

When you have a descending column in an index Oracle will ALWAYS create a column that is hidden, virtual, and not user_generated. It takes no space in the table segment, only a little space in the data dictionary so that Oracle can hold its definition and gather stats on it.

I am curious to know why you think you need to get rid of it.

Regards
Jonathan Lewis



From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> <oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>> on behalf of Sourav Biswas <biswas.sourav_at_hotmail.com<mailto:biswas.sourav_at_hotmail.com>> Sent: 17 January 2019 10:28
To: Oracle Mailinglist
Subject: How to disable Extended Statistics in Oracle 12c

Hi All,

I am using 12.1.0.2.0 database. Whenever, I create a Function based index, it creates a hidden column for extended statistics.

Ex:

SQL> CREATE UNIQUE INDEX "SIEBEL"."TEMP_CTLG_CAT_U1" ON "SIEBEL"."TEMP_CTLG_CAT" ("NAME", "CTLG_ID", "EFF_END_DT" DESC, "CONFLICT_ID") TABLESPACE "SBLINDEX"; Index created.

SQL> select * from dba_stat_extensions where table_name in ('TEMP_CTLG_CAT');

OWNER      TABLE_NAME           EXTENSION_NAME                 EXTENSION                                                          CREATO DROPPABLE

---------- -------------------- ------------------------------ ------------------------------------------------------------------ ------ ----------
SIEBEL TEMP_CTLG_CAT SYS_NC00053$ ("EFF_END_DT") SYSTEM NO

And when I drop this function based index, the extention name also drops:-

Ex:

SQL> drop index SIEBEL.TEMP_CTLG_CAT_U1;

Index dropped.

SQL> select * from dba_stat_extensions where table_name in ('TEMP_CTLG_CAT');

no rows selected

I tried using "_optimizer_enable_extended_stats" to FALSE, but the behaivior didn't change:-

SQL> alter system set "_optimizer_enable_extended_stats"=FALSE scope=both sid='*';

Please suggest whether there is a way in oracle 12c, to create function based indexes and avoid these extended statistics and automatic creation of hidden columns.

Best Regards,
Sourav Biswas
+91-9650017306
--

http://www.freelists.org/webpage/oracle-l<https://urldefense.proofpoint.com/v2/url?u=http-3A__www.freelists.org_webpage_oracle-2Dl&d=DwMFAg&c=SsZxQMfaWJ1sSVfloc5FVGba8BA_qR4Jzdt8ol2oSPA&r=z73EKtGMkOyHMZwSjVYW896tQVgTZQlAucPsWFx6Th0&m=4GJkWbcUMGxyP6PhIVRGGShK3I_JWNrxKiSMj5DrjNg&s=SqAZzjeRR3ko20LK622klfpfzL_NWEWd6jpepWI7tuc&e=>

--

//
zztat - The Next-Gen Oracle Performance Monitoring and Reaction Framework! Visit us at zztat.net<https://urldefense.proofpoint.com/v2/url?u=http-3A__zztat.net_&d=DwMFAg&c=SsZxQMfaWJ1sSVfloc5FVGba8BA_qR4Jzdt8ol2oSPA&r=z73EKtGMkOyHMZwSjVYW896tQVgTZQlAucPsWFx6Th0&m=4GJkWbcUMGxyP6PhIVRGGShK3I_JWNrxKiSMj5DrjNg&s=tDZH5Rc5eX3f_Z_VdT_cUxul88jx3yth1PCMLGitqf8&e=> | _at_zztat_oracle | fb.me/zztat<https://urldefense.proofpoint.com/v2/url?u=http-3A__fb.me_zztat&d=DwMFAg&c=SsZxQMfaWJ1sSVfloc5FVGba8BA_qR4Jzdt8ol2oSPA&r=z73EKtGMkOyHMZwSjVYW896tQVgTZQlAucPsWFx6Th0&m=4GJkWbcUMGxyP6PhIVRGGShK3I_JWNrxKiSMj5DrjNg&s=3CbwmR3aosG-N6jaLRJuR78on0mlOTQoSFI164FWI4Y&e=> | zztat.net/blog/<https://urldefense.proofpoint.com/v2/url?u=http-3A__zztat.net_blog_&d=DwMFAg&c=SsZxQMfaWJ1sSVfloc5FVGba8BA_qR4Jzdt8ol2oSPA&r=z73EKtGMkOyHMZwSjVYW896tQVgTZQlAucPsWFx6Th0&m=4GJkWbcUMGxyP6PhIVRGGShK3I_JWNrxKiSMj5DrjNg&s=nHtu-sOD7SbY3H8wH43GLQJV2G_xkPEwTcVNczs9e7c&e=>

--

http://www.freelists.org/webpage/oracle-l Received on Fri Jan 18 2019 - 04:26:01 CET

Original text of this message