Re: CTAS generates huge LC Object
From: Martin Klier - Performing Databases GmbH <martin.klier_at_performing-db.com>
Date: Tue, 28 Apr 2020 12:46:52 +0200 (CEST)
Message-ID: <1597450503.11538.1588070812274.JavaMail.zimbra_at_performing-db.com>
Date: Tue, 28 Apr 2020 12:46:52 +0200 (CEST)
Message-ID: <1597450503.11538.1588070812274.JavaMail.zimbra_at_performing-db.com>
-- Martin Klier // Performing Databases GmbH Managing Partner // Senior DB Consultant Oracle ACE Director martin.klier_at_performing-db.com // https://www.performing-databases.comReceived on Tue Apr 28 2020 - 12:46:52 CEST
> Von: "Noveljic Nenad" <nenad.noveljic_at_vontobel.com>
> An: "Martin Klier" <martin.klier_at_performing-db.com>, "Oracle-L Freelists"
> <oracle-l_at_freelists.org>
> Gesendet: Dienstag, 28. April 2020 11:36:20
> Betreff: RE: CTAS generates huge LC Object
> 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/ |
> 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_at_freelists.org <oracle-l-bounce_at_freelists.org> On Behalf Of
> Martin Klier - Performing Databases GmbH
> Sent: Dienstag, 28. April 2020 11:20
> To: Oracle-L Freelists <oracle-l_at_freelists.org>
> 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
> [ mailto:martin.klier_at_performing-db.com | martin.klier_at_performing-db.com ] // [
> https://www.performing-databases.com/ | 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.
> Important Notice
> 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.
> 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.
> 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.
> 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.
> 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 [ https://www.vontobel.com/ | www.vontobel.com ]
> .
-- http://www.freelists.org/webpage/oracle-l