Hi Nenad,
wow, thanks, interesting.
We don't fit the profile of your post exactly, since here it's not TCHK,
instead I have SQLA, but I will try if an "insert select distinct *" behaves
better.
Thanks again.
--
Martin Klier // Performing Databases GmbH
Managing Partner // Senior DB Consultant
Oracle ACE Director
martin.klier@xxxxxxxxxxxxxxxxx // https://www.performing-databases.com ;
Von: "Noveljic Nenad" <nenad.noveljic@xxxxxxxxxxxx>
An: "Martin Klier" <martin.klier@xxxxxxxxxxxxxxxxx>, "Oracle-L Freelists"
<oracle-l@xxxxxxxxxxxxx>
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@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
[ mailto:martin.klier@xxxxxxxxxxxxxxxxx ;| martin.klier@xxxxxxxxxxxxxxxxx ] //
[
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 ]
.
