Re: Find Data Dictionary Size itself? Multi-tenant in this case

Well, that is very good info in regard to the inner workings of Debezium! Thanks for that.

Good point about the redo log switch check but I wonder on a very busy database how easily that would be to determine.

Thanks

On Thu, Jan 4, 2024 at 7:42 AM Mikhail Velikikh <mvelikikh_at_gmail.com> wrote:

> Debezium should be using DBMS_LOGMNR_D.BUILD
> <https://github.com/debezium/debezium/blob/main/debezium-connector-oracle/src/main/java/io/debezium/connector/oracle/logminer/LogMinerStreamingChangeEventSource.java#L496>
> which calls the same internal routine as in DBMS_CAPTURE_ADM.
> It looks like the dictionary build is indeed performed on each log switch
> based on this excerpt:
> https://github.com/debezium/debezium/blob/main/debezium-connector-oracle/src/main/java/io/debezium/connector/oracle/logminer/LogMinerStreamingChangeEventSource.java#L228-L236
> The call that seemingly initiates the dictionary build is:
> `initializeRedoLogsForMining(jdbcConnection, true, startScn);`
> The dictionary build part of this procedure is:
> https://github.com/debezium/debezium/blob/main/debezium-connector-oracle/src/main/java/io/debezium/connector/oracle/logminer/LogMinerStreamingChangeEventSource.java#L422-L425
> Since the continuous_mine is desupported in 19c (
> https://docs.oracle.com/en/database/oracle/oracle-database/19/sutil/oracle-logminer-utility.html#GUID-3417B738-374C-4EE3-B15C-3A66E01AE2B5),
> the code should be building the LogMiner dictionary on each log switch if I
> got it right.
>
> Regarding the size, you can just enable sql_trace and see what is
> performed when you call DBMS_LOGMNR_D.BUILD. There is a bunch of data loads
> done to populate LOGMNR% tables from SYS.COL$/OBJ$/CDEF$/etc. using
> retrospective (AS OF SCN) queries.
> Therefore, 'redo size' seems to be quite a reliable statistic to estimate
> the LogMiner dictionary size in the redo logs:
> ```
> SQL> select value from v$mystat natural join v$statname where name='redo
> size';
>
> VALUE
> ----------
> 24807052
>
> SQL> exec DBMS_LOGMNR_D.BUILD (options => DBMS_LOGMNR_D.STORE_IN_REDO_LOGS)
>
> PL/SQL procedure successfully completed.
>
> SQL> select value from v$mystat natural join v$statname where name='redo
> size';
>
> VALUE
> ----------
> 49610532
>
> SQL> select thread#, name, first_change#, next_change#, dictionary_begin,
> dictionary_end, blocks*block_size size_ from v$archived_log order by
> first_change#;
>
> THREAD# NAME
> FIRST_CHANGE# NEXT_CHANGE# DIC DIC
> SIZE_
> ----------
> ----------------------------------------------------------------------------------------------------
> ------------- ------------ --- --- ----------
> 1
> +DATA/RACDB/ARCHIVELOG/2024_01_04/thread_1_seq_18.301.1157375151
> 728618 729346 YES NO 25659392
> 2 +DATA/RACDB/ARCHIVELOG/2024_01_04/thread_2_seq_6.303.1157375157
> 728621 729367 NO NO
> 9216
> 1
> +DATA/RACDB/ARCHIVELOG/2024_01_04/thread_1_seq_19.302.1157375153
> 729346 729353 NO YES 2048
>
> 20 rows selected.
> ```
>
> The actual archive log size ~25M is close to the 'redo size' statistic
> (~24M) in this idle database.
> Of course, when sizing the redo logs, you need to take into account other
> activity which might be happening while the LogMiner dictionary is being
> built.
>
> Best regards,
> *Mikhail Velikikh*
>
>
>
> On Thu, 4 Jan 2024 at 12:15, Ilmar Kerm <ilmar.kerm_at_gmail.com> wrote:
>
>> I think in this context "dictionary" means this:
>>
>> https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_CAPTURE_ADM.html#GUID-4539AB47-A932-4A79-AFF5-5A42733B19DF
>>
>> No idea really, how much data it would add to redo.
>>
>> On Thu, 4 Jan 2024 at 12:40, Chris Taylor <
>> christopherdtaylor1994_at_gmail.com> wrote:
>>
>>> On the surface, this question may seem odd (or silly) but I realize I
>>> don't actually know how to find the size of the data dictionary itself in
>>> Oracle.
>>>
>>> The reason this is coming up is that we're playing with Debezium and
>>> Kafka replication, and this article here wants to make sure one redo log is
>>> large enough to house the data dictionary size (so that on a log switch,
>>> every log will have a copy of the data dictionary to compare changes).
>>>
>>> Which leads me to realizing, I don't know how to find the size of the DD
>>> itself (or have forgotten).
>>>
>>> Article that leads me into this question:
>>>
>>> https://debezium.io/blog/2022/09/30/debezium-oracle-series-part-1/#configure-oracle-redo-logs
>>>
>>> Thanks,
>>> Chris
>>>
>>>
>>
>> --
>> Ilmar Kerm
>>
>

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