RE: Weirdness with dba_tab_modifications

From: Matt Adams <MAdams_at_TroverSolutions.com>
Date: Wed, 7 Oct 2015 16:51:10 +0000
Message-ID: <66574a6919774a8a9d918cbcba428a19_at_wpvl1dag02.hcrec.com>

That's exactly what I did. Setting the undocumented _optimizer_join_factorization parameter to false fixed the issue. Definitely looks like bug 1398324, which the is fixed in 12.1, but not in 11.2.0.4

-----Original Message-----
From: Jeremy Schneider [mailto:jeremy.schneider_at_ardentperf.com] Sent: Wednesday, October 07, 2015 12:46 PM To: xt.and.r_at_gmail.com
Cc: Matt Adams; Ram Raman; ORACLE-L
Subject: Re: Weirdness with dba_tab_modifications

that bug says it's fixed in 11.2.0.4 (matt's platform) - but matt you might just try setting _optimizer_join_factorization to false anyway and see if it changes the results.

-J
--
http://about.me/jeremy_schneider

On Wed, Oct 7, 2015 at 12:41 PM, Sayan Malakshinov <xt.and.r_at_gmail.com> wrote:
> Maybe "Bug 13984324 wrong result with UNION ALL of similar queries / in
> DBA_TAB_MODIFICATIONS"
> https://support.oracle.com/epmos/faces/DocContentDisplay?id=13984324.8
>
> On Wed, Oct 7, 2015 at 7:27 PM, Matt Adams <MAdams_at_troversolutions.com>
> wrote:
>>
>> No, all queries were run by me in same session, logged in as SYS.
>>
>>
>>
>> Oracle 11.2.0.4 on Solaris 10
>>
>>
>>
>> From: Ram Raman [mailto:veeeraman_at_gmail.com]
>> Sent: Wednesday, October 07, 2015 11:19 AM
>> To: Matt Adams
>> Cc: ORACLE-L
>> Subject: Re: Weirdness with dba_tab_modifications
>>
>>
>>
>> Could you be logged in as two different users/databases for those two
>> queries?
>>
>>
>>
>> On Wed, Oct 7, 2015 at 9:56 AM, Matt Adams <MAdams_at_troversolutions.com>
>> wrote:
>>
>> I’m investigating several foreign keys that do not appear to have
>> corresponding indexes on them. During the course of the investigation, I
>> started querying DBA_TAB_MODIFICATIONS.
>>
>>
>>
>> First I ran the following:
>>
>>
>>
>> SQL> select * from dba_tab_modifications where table_owner = 'HCSC_OWNER'
>> and table_name = 'BILL'
>>
>> /
>>
>> 2
>>
>> TABLE_OWNER TABLE_NAME
>>
>> ------------------------------ ------------------------------
>>
>> PARTITION_NAME SUBPARTITION_NAME INSERTS
>>
>> ------------------------------ ------------------------------ ----------
>>
>> UPDATES DELETES TIMESTAMP TRU DROP_SEGMENTS
>>
>> ---------- ---------- ----------- --- -------------
>>
>> HCSC_OWNER BILL
>>
>> 139434
>>
>> 130149 15329 06-OCT-2015 NO 0
>>
>>
>>
>>
>>
>> Now, since we have (supposedly) identical schemas for various reasons, I
>> wanted to see where else this table might exist and how busy they might be.
>>
>>
>>
>> SQL> select * from dba_tab_modifications where table_name = 'BILL'
>>
>> SQL> /
>>
>>
>>
>> no rows selected
>>
>>
>>
>> I don’t understand how the less restrictive query returns no rows.
>>
>>
>>
>> When I switch both queries to use all_tab_modifications (rather than
>> dba_tab_modifications), I get expected results…1 row for fist query, and 16
>> rows for less restrictive query.
>>
>> I’ve retyped the queries by hand several times, so I’m sure that I don’t
>> have some weird invisible control character in there.
>>
>>
>>
>> Has anybody seen this before?
>>
>>
>>
>> Matt Adams
>>
>> madams_at_troversolutions.com
>>
>>
>>
>> **** This communication may contain privileged and/or confidential
>> information. If you are not the intended recipient, you are hereby notified
>> that disclosing, copying, or distributing of the contents is strictly
>> prohibited. If you have received this message in error, please contact the
>> sender immediately and destroy any copies of this document. ****
>>
>>
>>
>>
>>
>> --
>>
>>
>>
>>
>>
>> **** This communication may contain privileged and/or confidential
>> information. If you are not the intended recipient, you are hereby notified
>> that disclosing, copying, or distributing of the contents is strictly
>> prohibited. If you have received this message in error, please contact the
>> sender immediately and destroy any copies of this document. ****
>
>
>
>
> --
> Best regards,
> Sayan Malakshinov
> Oracle ACE Associate
> Lead performance tuning engineer
> PSBank
> http://orasql.org

**** This communication may contain privileged and/or confidential information. If you are not the intended recipient, you are hereby notified that disclosing, copying, or distributing of the contents is strictly prohibited. If you have received this message in error, please contact the sender immediately and destroy any copies of this document. **** †Ûiÿü0ÁúÞzX¬¶Ê+ƒün– {ú+iÉ^ Received on Wed Oct 07 2015 - 18:51:10 CEST