RE: CTAS generates huge LC Object

  • From: Noveljic Nenad <nenad.noveljic@xxxxxxxxxxxx>
  • To: "martin.klier@xxxxxxxxxxxxxxxxx" <martin.klier@xxxxxxxxxxxxxxxxx>, Oracle-L Freelists <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 28 Apr 2020 09:36:20 +0000

The query not showing up in Statspack might indicate that the allocations were 
temporary during optimization/parsing. I’ve seen large TCHK allocations with 
create table as select, see 
https://nenadnoveljic.com/blog/large-tchk-allocations-shared-pool/

Those allocations have been becoming larger with every new Oracle version, so 
typically we started seeing them after the upgrades.

created an empty table and populating it with insert/select might be a 
workaround for the most serious issues.

Best regards,

Nenad

From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> On Behalf 
Of Martin Klier - Performing Databases GmbH
Sent: Dienstag, 28. April 2020 11:20
To: Oracle-L Freelists <oracle-l@xxxxxxxxxxxxx>
Subject: CTAS generates huge LC Object

Hi Listers,

yet another Library Cache issue...

Due to _kgl_large_heap_warning_threshold one "create table as select" causes a 
trace file for its big LC object. (The warning is great, because we have had 
memory issues at this point anyway.)

In the trace file, I can see a data block allocating much memory:
Block:  #='6' name=SQLA^83a0bff2 pins=0 Change=NONE
        Heap=000007FF55229758 Pointer=000007FF17BD9BE8 Extent=000007FF17BD8FA8 
Flags=I/-/P/A/-/E
        FreedLocation=0 Alloc=153032.242188 Size=153034.656250 LoadTime=0

Whenever I look, we don't have this cursor in the cache, so all v$ views are 
useless for me at the moment.
It does not even show up in the STATSPACK "SQL ordered by Sharable Memory" 
lists.

See the whole second part of the trace and some additional infos after the 
signature.

Now the big question is:
How do I find out why this one is getting so big? What can I (or most likely, 
the developer) change to make it smaller?

Thanks for any ideas.
--
Martin Klier // Performing Databases GmbH
Managing Partner // Senior DB Consultant
Oracle ACE Director

martin.klier@xxxxxxxxxxxxxxxxx<mailto:martin.klier@xxxxxxxxxxxxxxxxx> // 
https://www.performing-databases.com





Background info:

Alert Log:
Memory Notification: Library Cache Object loaded into SGA
Heap size 153034K exceeds notification threshold (51200K)
Details in trace file <bla>prod_ora_4472.trc
KGL object name :Create table MadAdmasTemp as select distinct * from <bla>



Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
Windows NT Version V6.1 Service Pack 1
CPU                 : 4 - type 8664, 4 Physical Cores
Process Affinity    : 0x0x0000000000000000
Memory (Avail/Total): Ph:7695M/16383M, Ph+PgF:23455M/32765M
VM name             : VMWare Version (6)
Instance name: xyz
Redo thread mounted by this instance: 1

<...>
*** 2020-04-27 19:35:25.963
*** SESSION ID:(39.22855) 2020-04-27 19:35:25.963
*** CLIENT ID:() 2020-04-27 19:35:25.963
*** SERVICE NAME:(SYS$USERS) 2020-04-27 19:35:25.963
*** MODULE NAME:(JDBC Thin Client) 2020-04-27 19:35:25.963
*** ACTION NAME:() 2020-04-27 19:35:25.963

Memory Notification: Library Cache Object loaded into SGA
Heap size 153034K exceeds notification threshold (51200K)

LibraryHandle:  Address=000007FF66FF19E0 Hash=83a0bff2 LockMode=N PinMode=0 
LoadLockMode=0 Status=VALD
  ObjectName:  Name=Create table MadAdmasTemp as select distinct * from <bla>
    FullHashValue=a48476d0ac28b36c2e3d594583a0bff2 Namespace=SQL AREA(00) 
Type=CURSOR(00) Identifier=2208350194 OwnerIdn=51
  Statistics:  InvalidationCount=0 ExecutionCount=0 LoadCount=2 ActiveLocks=1 
TotalLockCount=1 TotalPinCount=1
  Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=1 Version=0 
BucketInUse=0 HandleInUse=0 HandleReferenceCount=0
  Concurrency:  DependencyMutex=000007FF66FF1A90(0, 1, 0, 0) 
Mutex=000007FF66FF1B20(39, 25, 0, 6)
  Flags=RON/PIN/TIM/PN0/DBN/[10012841]
<...>

LibraryHandle:  Address=000007FF6B2631D0 Hash=0 LockMode=N PinMode=X 
LoadLockMode=0 Status=VALD
  Name:  Namespace=SQL AREA(00) Type=CURSOR(00)
  Statistics:  InvalidationCount=0 ExecutionCount=0 LoadCount=1 ActiveLocks=1 
TotalLockCount=1 TotalPinCount=2
  Counters:  BrokenCount=1 RevocablePointer=1 KeepDependency=0 Version=0 
BucketInUse=0 HandleInUse=0 HandleReferenceCount=0
  Concurrency:  DependencyMutex=000007FF6B263280(0, 1, 0, 0) 
Mutex=000007FF66FF1B20(39, 26, 0, 6)
  Flags=RON/PIN/PN0/EXP/CHD/[10012111]
  WaitersLists:
    Lock=000007FF6B263260[000007FF6B263260,000007FF6B263260]
    Pin=000007FF6B263240[000007FF6B263240,000007FF6B263240]
    LoadLock=000007FF6B2632B8[000007FF6B2632B8,000007FF6B2632B8]
  ReferenceList:
    Reference:  Address=000007FF55229988 Handle=000007FF66FF19E0 Flags=CHL[02]
  LibraryObject:  Address=000007FF448E2090 HeapMask=0000-0001-0001-0000 
Flags=EXS[0000] Flags2=[0000] PublicFlags=NST[0001]
    Dependencies:  count='30' size='32' table='000007FF448E2EC8'
      Dependency:  num='0'
        Reference=000007FF448E2648 Position=0 Flags=DEP[0001]
        Handle=000007FF6DE03730 Type=NONE(255) Parent=MYSCHEMA
      Dependency:  num='1'
        Reference=000007FF448E26E0 Position=52 Flags=DEP[0001]
        Handle=000007FF6B131888 Type=VIEW(04) Parent=MYSCHEMA.V_ADMAS
      Dependency:  num='2'
        Reference=000007FF448E2720 Position=186 Flags=DEP[0001]
        Handle=000007FF6CCD8230 Type=TABLE(02) Parent=MYSCHEMA.AGACTIVITE
      Dependency:  num='3'
        Reference=000007FF448E2760 Position=648 Flags=DEP[0001]
        Handle=000007FF6B3417A8 Type=VIEW(04) Parent=MYSCHEMA.V_ADMAS_METIER
      Dependency:  num='4'
        Reference=000007FF448E27A0 Position=268 Flags=DEP[0001]
        Handle=000007FF00175B90 Type=TABLE(02) 
Parent=MYSCHEMA.WFSUITEPROCEDUREREFERENCE
      Dependency:  num='5'
        Reference=000007FF448E27E0 Position=252 Flags=DEP[0001]
        Handle=000007FF00136618 Type=TABLE(02) Parent=MYSCHEMA.PROCEDURE
      Dependency:  num='6'
        Reference=000007FF448E2820 Position=239 Flags=DEP[0001]
        Handle=000007FF6DF59858 Type=TABLE(02) Parent=MYSCHEMA.PROCESSUS
      Dependency:  num='7'
        Reference=000007FF448E2860 Position=647 Flags=DEP[0001]
        Handle=000007FF66DA1B60 Type=VIEW(04) Parent=MYSCHEMA.V_PROCEDURE
      Dependency:  num='8'
        Reference=000007FF448E28A0 Position=366 Flags=DEP[0001]
        Handle=000007FF6CCD2038 Type=VIEW(04) Parent=MYSCHEMA.HISTORIDS
      Dependency:  num='9'
        Reference=000007FF448E28E0 Position=136 Flags=DEP[0001]
        Handle=000007FF6CCD1190 Type=TABLE(02) Parent=MYSCHEMA.HISTORIDVALEUR
      Dependency:  num='10'
        Reference=000007FF448E2920 Position=626 Flags=DEP[0001]
        Handle=000007FF7B4B27A8 Type=TABLE(02) Parent=MYSCHEMA.WFSUITEPROCEDURE
      Dependency:  num='11'
        Reference=000007FF448E2960 Position=308 Flags=DEP[0001]
        Handle=000007FF6CD2FF10 Type=TABLE(02) Parent=MYSCHEMA.ADRESSE
      Dependency:  num='12'
        Reference=000007FF448E29A0 Position=2845 Flags=DEP[0001]
        Handle=000007FF6CD2D5C0 Type=TABLE(02) Parent=MYSCHEMA.PERSONNE
      Dependency:  num='13'
        Reference=000007FF448E29E0 Position=7 Flags=DEP[0001]
        Handle=000007FF6CD2C3F8 Type=FUNCTION(08) Parent=MYSCHEMA.FUZZY_SEARCH
      Dependency:  num='14'
        Reference=000007FF448E2A20 Position=3257 Flags=DEP[0001]
        Handle=000007FF7B500090 Type=TABLE(02) Parent=MYSCHEMA.MADDOSSIER
      Dependency:  num='15'
        Reference=000007FF448E2A60 Position=2344 Flags=DEP[0001]
        Handle=000007FF7B4FFE70 Type=TABLE(02) Parent=MYSCHEMA.MADMESURE
      Dependency:  num='16'
        Reference=000007FF448E2AA0 Position=3180 Flags=DEP[0001]
        Handle=000007FF7B4FFC50 Type=TABLE(02) 
Parent=MYSCHEMA.MADADMASINFRACTION
      Dependency:  num='17'
        Reference=000007FF448E2AE0 Position=2034 Flags=DEP[0001]
        Handle=000007FF7B4FFA30 Type=TABLE(02) Parent=MYSCHEMA.MADMESUREADMAS
      Dependency:  num='18'
        Reference=000007FF448E2B20 Position=4467 Flags=DEP[0001]
        Handle=000007FF66FF17B0 Type=FUNCTION(08) 
Parent=MYSCHEMA.DECISIONPOURTYPESUITEAVECMOTIF
      Dependency:  num='19'
        Reference=000007FF448E2B60 Position=4329 Flags=DEP[0001]
        Handle=000007FF68DCBA70 Type=FUNCTION(08) Parent=MYSCHEMA.DECISIONPOUR
      Dependency:  num='20'
        Reference=000007FF448E2BA0 Position=1268 Flags=DEP[0001]
        Handle=000007FF66CCC958 Type=FUNCTION(08) 
Parent=MYSCHEMA.RECONSTRUITADRESSECOMPLETE
      Dependency:  num='21'
        Reference=000007FF448E2BE0 Position=4429 Flags=DEP[0001]
        Handle=000007FF68E32D08 Type=TABLE(02) 
Parent=MYSCHEMA.CANTON_CLIENT_ACTIF
      Dependency:  num='22'
        Reference=000007FF448E2C20 Position=1788 Flags=DEP[0001]
        Handle=000007FF00176418 Type=TABLE(02) Parent=MYSCHEMA.ADMASDATAREF
      Dependency:  num='23'
        Reference=000007FF448E2C60 Position=1774 Flags=DEP[0001]
        Handle=000007FF001761F8 Type=TABLE(02) Parent=MYSCHEMA.ADMASDATA
      Dependency:  num='24'
        Reference=000007FF448E2CA0 Position=9240 Flags=DEP[0001]
        Handle=000007FF001B8C38 Type=TABLE(02) 
Parent=MYSCHEMA.MADDOSSIERISSUEPENALE
      Dependency:  num='25'
        Reference=000007FF448E2CE0 Position=3289 Flags=DEP[0001]
        Handle=000007FF001788B8 Type=VIEW(04) Parent=MYSCHEMA.V_ADRESSE
      Dependency:  num='26'
        Reference=000007FF448E2D20 Position=3272 Flags=DEP[0001]
        Handle=000007FF66FF1590 Type=VIEW(04) Parent=MYSCHEMA.V_PERSONNE
      Dependency:  num='27'
        Reference=000007FF40B990A8 Position=0 Flags=DEP[0001]
        Handle=000007FF7B4960C0 Type=TABLE(02) Parent=MYSCHEMA.PROCEDUREREF
      Dependency:  num='28'
        Reference=000007FF40B990E8 Position=0 Flags=DEP[0001]
        Handle=000007FF66D5F5E8 Type=TABLE(02) 
Parent=MYSCHEMA.PROCEDUREREFERENCE
      Dependency:  num='29'
        Reference=000007FF40B99128 Position=0 Flags=DEP[0001]
        Handle=000007FF6DF391D8 Type=PACKAGE(09) Parent=SYS.STANDARD
    ReadOnlyDependencies:  count='1' size='16'
      ReadDependency:  num='0' Table=000007FF448E2F60 
Reference=000007FF448E2548 Handle=000007FF6CD43B58 Flags=DEP/ROD/KPP[61]
    Accesses:  count='2' size='16'
      Dependency:  num='2' Type=0009
      Dependency:  num='1' Type=0009
    DataBlocks:
      Block:  #='0' name=KGLH0^83a0bff2 pins=0 Change=NONE
        Heap=000007FF6CEBC0D0 Pointer=000007FF448E2130 Extent=000007FF448E2010 
Flags=I/-/P/A/-/-
        FreedLocation=0 Alloc=12.929688 Size=15.820313 LoadTime=3667233912
      Block:  #='6' name=SQLA^83a0bff2 pins=0 Change=NONE
        Heap=000007FF55229758 Pointer=000007FF17BD9BE8 Extent=000007FF17BD8FA8 
Flags=I/-/P/A/-/E
        FreedLocation=0 Alloc=153032.242188 Size=153034.656250 LoadTime=0
  NamespaceDump:
    Child Cursor:  Heap0=000007FF448E2130 Heap6=000007FF17BD9BE8 Heap0 Load 
Time=04-27-2020 19:35:07 Heap6 Load Time=04-27-2020 19:35:07



____________________________________________________
Please consider the environment before printing this e-mail.
Bitte denken Sie an die Umwelt, bevor Sie dieses E-Mail drucken.

<html xmlns="http://www.w3.org/1999/xhtml";>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css">p { font-family: Arial;font-size:9pt }</style>
</head>
<body>
<p>
<br>Important Notice</br>
<br />
This message is intended only for the individual named. It may contain 
confidential or privileged information. If you are not the named addressee you 
should in particular not disseminate, distribute, modify or copy this e-mail. 
Please notify the sender immediately by e-mail, if you have received this 
message by mistake and delete it from your system.<br />
Without prejudice to any contractual agreements between you and us which shall 
prevail in any case, we take it as your authorization to correspond with you by 
e-mail if you send us messages by e-mail. However, we reserve the right not to 
execute orders and instructions transmitted by e-mail at any time and without 
further explanation.<br />
E-mail transmission may not be secure or error-free as information could be 
intercepted, corrupted, lost, destroyed, arrive late or incomplete. Also 
processing of incoming e-mails cannot be guaranteed. All liability of Vontobel 
Holding Ltd. and any of its affiliates (hereinafter collectively referred to as 
"Vontobel Group") for any damages resulting from e-mail use is excluded. You 
are advised that urgent and time sensitive messages should not be sent by 
e-mail and if verification is required please request a printed version.</br>
Please note that all e-mail communications to and from the Vontobel Group are 
subject to electronic storage and review by Vontobel Group. Unless stated to 
the contrary and without prejudice to any contractual agreements between you 
and Vontobel Group which shall prevail in any case, e-mail-communication is for 
informational purposes only and is not intended as an offer or solicitation for 
the purchase or sale of any financial instrument or as an official confirmation 
of any transaction.<br />
The legal basis for the processing of your personal data is the legitimate 
interest to develop a commercial relationship with you, as well as your consent 
to forward you commercial communications. You can exercise, at any time and 
under the terms established under current regulation, your rights. If you 
prefer not to receive any further communications, please contact your client 
relationship manager if you are a client of Vontobel Group or notify the sender.
Please note for an exact reference to the affected group entity the corporate 
e-mail signature.
For further information about data privacy at Vontobel Group please consult <a 
href="https://www.vontobel.com";>www.vontobel.com</a>.<br />
</p>
</body>
</html>

Other related posts: