Re: Shared pool "knlu_mt_insert_btree - 10"

  • From: Biju Thomas <biju.thomas@xxxxxxxxx>
  • To: Maclean Liu <maclean_007@xxxxxxx>
  • Date: Wed, 19 Nov 2014 18:27:11 -0600

Thank you Maclean. FYI All - I opened an SR with Oracle, and did not get a
solid answer. But points in the same direction.

Defect 18672339 : CAPTURE AND APPLY ABORT WITH ORA-4031 WHEN
MESSAGE_TRACKING_FREQUENCY=1
Is under analysis by Development.
In the Defect it was observed that setting message tracking to lower value
will cause continued growth on knlu_mt_insert_btree - 10
Workaround available:
Reset to default value.
EXEC DBMS_CAPTURE_ADM.SET_PARAMETER( capture_name => '<streams_capture>',
parameter => 'message_tracking_frequency',
value => '2000000');

Just wanted to close the thread with an answer (well, kind of an answer).



On Tue, Nov 18, 2014 at 8:21 PM, Maclean Liu <maclean_007@xxxxxxx> wrote:

>
> ​KNL means Kernel replicatioN Logbased. "knlu_mt_insert_btree" is
> something ​  Message Tracking btree insert function.
>
>
>
> -ML
> --
>
> Maclean Liu
> Senior Director Product Management
> ParnassusData Software ,Inc.   http://www.parnassusdata.com/
> Phone : (+86) 400-690-3643  ;  Mobile : (+86) 13764045638
> My Blog: http://askmaclean.com/
> 4/F, No.733 Gaoping Road Shanghai , China
> zip code: 200436
>
> On Wed, Nov 19, 2014 at 2:57 AM, Biju Thomas <biju.thomas@xxxxxxxxx>
> wrote:
>
>> One of the production databases show steady growth of "knlu_mt_insert_btree
>> - 10" area in the shared pool. I could not find much information on how
>> this area is used or what makes it grow.
>>
>> Incidentally, this growth was noticed after fixing a CDC (Streams /
>> Change Data Capture) issue with the following setting:
>>
>> exec dbms_capture_adm.set_parameter('<Change_set>',
>> 'message_tracking_frequency', '1')
>>
>> Are these related?
>>
>> Database version: 11.2.0.3
>> OS Version: Solaris SPARC 64-bit
>> Automatic Shared Memory Management used.
>>
>> SQL> show parameter target
>>
>> NAME                                 TYPE        VALUE
>> ------------------------------------ -----------
>> ------------------------------
>> memory_target                        big integer 0
>> pga_aggregate_target                 big integer 4G
>> sga_target                           big integer 64G
>>
>> SQL> show parameter pool
>>
>> NAME                                 TYPE        VALUE
>> ------------------------------------ -----------
>> ------------------------------
>> java_pool_size                       big integer 128M
>> large_pool_size                      big integer 0
>> olap_page_pool_size                  big integer 4M
>> shared_pool_reserved_size            big integer 248302796
>> shared_pool_size                     big integer 20G
>> streams_pool_size                    big integer 2G
>>
>> SQL> select * from v$sga
>> SQL> /
>>
>> NAME                                  VALUE
>> -------------------- ----------------------
>> Fixed Size                          2176432
>> Variable Size                   26306677328
>> Database Buffers                42010148864
>> Redo Buffers                      114130944
>>
>> SQL> select * from v$sgastat
>>   2  where bytes > 104857600
>>   3* order by pool, bytes desc
>> SQL> /
>>
>> POOL         NAME                                        BYTES
>> ------------ -------------------------- ----------------------
>> java pool    free memory                             888579776
>> large pool   free memory                             121167840
>> shared pool  free memory                           11206749712
>> shared pool  SQLA                                   4894263664
>> shared pool  KGLH0                                  2048385472
>> shared pool  knlu_mt_insert_btree - 10              1959893368
>> shared pool  KQR M PO                                381901568
>> shared pool  db_block_hash_buckets                   373297152
>> shared pool  KGLHD                                   348209208
>> shared pool  KKSSP                                   150372568
>> shared pool  KGLS                                    127196296
>> shared pool  flashback generation buff               117440512
>> shared pool  SQLP                                    107502792
>> streams pool free memory                            1225127360
>> streams pool apply shared t                          788198040
>>              buffer_cache                          42010148864
>>              log_buffer                              114130944
>>
>> Database was restarted only couple of days ago. Before the database
>> cycle, the knlu_mt_insert_btree pool was over 7GB.
>>
>> Thanks in advance...
>>
>> Biju
>>
>>
>>
>>
>


-- 

Biju Thomas
about.me/biju.thomas
[image: Biju Thomas on about.me]
  <http://about.me/biju.thomas>

Other related posts: