Re: 100% CPU Usage and Generation of Huge trace file

From: <sybrandb_at_hccnet.nl>
Date: Fri, 01 Aug 2008 15:50:37 +0200
Message-ID: <8t4694dhuj6iflij9t65is28ioeajfh94i@4ax.com>


On Fri, 1 Aug 2008 05:49:45 -0700 (PDT), Paul <paulwragg2323_at_hotmail.com> wrote:

>On 1 Aug, 13:38, sybra..._at_hccnet.nl wrote:
>> On Fri, 1 Aug 2008 04:39:33 -0700 (PDT), Paul
>>
>>
>>
>>
>>
>> <paulwragg2..._at_hotmail.com> wrote:
>> >Hi,
>> >I am using Oracle 10.2.0.2.0 on a Windows Server 2000 box.
>> >I am attempting to perform a full export of the database using exp.
>> >When it gets to Exporting Public Synonyms, the CPU reaches 99% for the
>> >Oracle process, and the generation of a trace file in the udump
>> >directory goes into overdrive (it will generate about 1GB per minute.
>> >I then tried exporting individual users - this works for some, but not
>> >for others.
>> >I ran ultirp.sql script, the utlrp.sql. At some point in this script
>> >the CPU reaches 99% again, and again the trace file is generated.
>>
>> >The step on which the CPU usage maxed out was:
>>
>> >Execute dbms_registry_sys.validate_components;
>>
>> >The only way to stop this is to shut the DB down.
>> >Clearly something is not correct and I have no idea what. There is
>> >some information in the trace file at the top, then there is the
>> >following:
>>
>> >INSTANTIATION OBJECT: object=0AEEBF68
>> >type=""[108] lock=00000000 handle=00000000 spec=31911614 level=1
>> >flags=BDY/NST/NBD/[aee] executions=183706240
>> > sqltxt(3408292C)=SELECT SYNNAM, DBMS_JAVA.LONGNAME(SYNNAM2)
>> >SYNNAM2,             DBMS_JAVA.LONGNAME(SYNTAB) SYNTAB,
>> >TABOWN, TABNODE, PUBLIC$, SYNOWN, SYNOWNID, TABOWNID, SYNOBJNO
>> >FROM   SYS.EXU9PTS      ORDER  BY SYNTIME
>> >  hash=503f915b95d68f67f6a948e38ad35fbf
>> >  parent=31911E74 maxchild=01 plk=323CABF0 ppn=n
>> >cursor instantiation=0AEEBF68 used=1217582378
>> > child#0(340827E8) pcs=31912078
>> >  clk=323C7AD8 ci=31911614 pn=3414C3E4 ctx=30EA8F00
>> > kgsccflg=0 llk[0AEEBF6C,0AEEBF6C] idx=0
>> > xscflg=c0100076 fl2=4040401 fl3=2222008 fl4=100
>> > Frames pfr 0B132B60 siz=47140 efr 0AECFF34 siz=46364
>> > kxscphp  0AEB6110 siz=3032 inu=0 nps=1912
>> > kxscdfhp 0AEC0274 siz=1000 inu=0 nps=56
>> > kxscwhp  0AEE01F0 siz=339320 inu=0 nps=330480
>> >----- Call Stack Trace -----
>> >calling              call     entry                argument values in
>> >hex
>> >location             type     point                (? means dubious
>> >value)
>> >-------------------- -------- --------------------
>> >----------------------------
>> >_ksedst+38           CALLrel  _ksedst1+0           0 0
>> >_ksm_4031_dump+1178  CALLrel  _ksedst+0            0
>> >_ksmasg+237          CALLrel  _ksm_4031_dump+0     4014FFF 53AD4D4
>> >3189D084
>> >                                                   24002000 1000
>> >405A5DC
>> >_kghnospc+876        CALLreg  00000000             A12AD10 53AD4D4
>> >1000 1000
>> >                                                   53AD4F8 ACB5CDC
>> >24002000 0
>> >                                                   405A5DC
>> >_kghalo+1536         CALLrel  _kghnospc+0          A12AD10 3189D084
>> >FC0 405A5DC
>> >                                                   4002000
>> >03DBB410             CALLrel  0404E38C             A12AD10 3189D084
>> >FC0 7FFFFFFF
>> >                                                   0 0 4002000 0
>> >405A5DC
>> >03EE4AA4             CALLrel  03DBB1A4             ACB74BC 30E37750
>> >1000 ACB6444
>> >                                                   190009 0
>> >03DBA0FD             CALLreg  00000000             ACB74BC ACB6404
>> >03E2BD37             CALLrel  03DB9BFE             ACB74BC 3EE4A88
>> >ACB6404 0
>> >                                                   ACB5FD0 ACB63B8
>> >ACB63BC
>> >03EE4AED             CALLrel  03E2BBE2             ACB74BC B8B9F9F
>> >3EE4A88
>> >                                                   ACB6404 0 0
>> >03DE65DD             CALLrel  03EE4AB6
>> >03DE688F             CALLrel  03DE65A4
>> >03DE72BD             CALLrel  03DE6750
>> >03DE826E             CALLrel  03DE729A             B8B9F9F 30BBBA08
>> >30E37750 0
>> >                                                   18 5
>> >03EE942A             CALLrel  03DE825C
>> >03F2B990             CALLrel  03EE9414
>> >03F251A8             CALLrel  03F2B968             ACB6538 30BBBA08
>> >03F3D43C             CALLrel  03F217C6             ACB74BC B8B9F9F 0
>> >3DA7E26
>> >                                                   30BBBA08 75 0 0 0
>> >ACB7130 0
>> >                                                   326DF35C 326DF360
>> >03E66D22             CALLrel  03F3D394
>> >03E66EF6             CALLrel  03E66C14             ACB7224 ACBC4D8
>> >03DAA9D9             CALLrel  03E66EA8             ACB74BC 30BBBA08 75
>> >3DA7E26
>> >                                                   326DF35C 326DF360
>> >03DA5491             CALLrel  03DAA5F4             A12AD10 0 30BBBA08
>> >30E37768
>> >                                                   B9B01E8 3DA2C7A 0
>> >27B B9B02EB
>> >03DA7952             CALLrel  03DA2D8C             A12AD10 30BBBA08 4
>> >0 0
>> >                                                   B9B01E8 3DA2C7A
>> >_joxldn+21           CALL???  00000000             A12AD10 30BBBA08 4
>> >0
>> >__VInfreq___PGOSF30  CALLrel  _joxldn+0            A12AD10 30BBBA08 4
>> >0
>> >8__kqlplslod+31
>> >__PGOSF307__kqlmcdl  CALLrel  __PGOSF308__kqlplsl  30BBBA08 ACB9AFC 4
>> >od+12                         od+0
>> >_kqllod+1716         CALL???  00000000             30BBBA08 ACB9AFC 4
>> >_kglobld+912         CALLreg  00000000             A12AD10 30BBBA08
>> >ACB9AFC
>> >_kglobpn+918         CALLrel  _kglobld+0           A12AD10 3189CF8C
>> >ACB9AFC
>> >                                                   323EDB00 0
>> >_kglpim+264          CALLrel  _kglobpn+0           A12AD10 323EDB00
>> >ACB9AFC 1
>> >03DB6259             CALLrel  0404E2D2             A12AD10 ACB9AFC
>> >323EDB00
>> >03F21757             CALLrel  03DB5B50             ACBC9A4 ACBA13C
>> >30BBBA08 0 1
>> >                                                   ACBA144 11
>> >03DBA0FD             CALLreg  00000000             ACBC9A4 ACBA0F4
>> >03E2BD37             CALLrel  03DB9BFE             ACBC9A4 3F21738
>> >ACBA0F4 0
>> >                                                   ACB9CC0 ACBA0A8
>> >ACBA0AC
>> >03F217A7             CALLrel  03E2BBE2             ACBC9A4 B8B9F9F
>> >3F21738
>> >                                                   ACBA0F4 0 0
>> >03E1F5B0             CALLrel  03F2176A
>> >03E2086D             CALLrel  03E1F3CE             B8B9F9F ACBA18C
>> >30BBBA08 0 0
>> >03E20932             CALLrel  03E2083A             B8B9F9F 30BBBA08 0
>> >0 BA05A1EF
>> >03F1B92E             CALLrel  03E208C2             B8B9F9F 2ABF4375 0
>> >0
>> >03E21711             CALLrel  03F1B8FC             B8B9F9F 2ABF4375 0
>> >03EEB7FC             CALLrel  03E216D8             B8B9F9F 2ABF6DED
>> >2ABF2180
>> >03DF9AD5             CALLrel  03EEB710
>> >03DF9A64             CALLrel  03DF9A76             B8B9F9F BA7D7DF
>> >2ABF2180 1E
>> >03DF9BD6             CALLrel  03DF99B0             B8B9F9F 2ABF231D F1
>> >03E17B0D             CALLrel  03DF9B64
>> >03F05A73             CALLrel  03E17AB4
>> >0B8B8DA4             CALL???  00000000
>> >0B8B70AC             CALL???  00000000
>> >0B91ED70             CALL???  00000000
>> >0B8B6D5F             CALL???  00000000
>>
>> >The CALL??? 00000000 then just continues until either the disk runs
>> >out of space or the instance is shutdown.
>>
>> >Does anyone have any ideas/suggestions??
>>
>> >I can post the rest of the file (the top part) if this will help -
>> >just wasn't sure if I needed to!
>>
>> >Thanks in advance!
>>
>> As you are running 10.2.0.2.0 you have Metalink access, otherwise you
>> couldn't have upgraded.
>> My bet is this is a known problem.
>> Also the current version is 10.2.0.4.0.
>> Upgrade time strikes again!!
>>
>> --
>> Sybrand Bakker
>> Senior Oracle DBA- Hide quoted text -
>>
>> - Show quoted text -
>
>Hi Sybrand,
>
>You are correct about both Metalink and versioning. We are moving to a
>new 64 bit box running 10.2.0.4.0.
>The thing is we want to just get some of the data out rather than
>either upgrading the entire instance in situ, or moving everything
>over.
>My intention was just to do full exports, then only import the data we
>need - we're trying to consolidate what we have built up over the last
>couple of years (including various things we don't need) into a nice
>new neat and tidy installation!
>
>I have a full RMAN backup from last night so if need be I can just
>recover everything from that, but I am really quite interested to know
>what is going on.
>
>I should add this is not a production instance hence I can just
>recover from last night - that's not an issue but I want to satisfy my
>curiosity if possible.
>
>For some reason I always seem to have trouble searching metalink - any
>suggestions as to what I should search on? 100% returned no hits!!
>
>Thanks for your reply - further guidance would be greatly appreciated.
>
>

Hi Paul,

My bet to this would be 'exp' and
'dbms_registry_sys.validate_components'

You should have received an error message (which you deleted from your post), the error message usually also is a search keyword, or the number.

I also noticed sometimes you have to choose 'All sources' or you won't get valid responses, especially if your version is somewhat older (ie: not the latest patch level)

One other suggestion: You might run into a situation where exp isn't fixed anymore or not fixed as quickly as you want, as it has been replaced by expdp.
Could you try expdp?

Regards

-- 
Sybrand Bakker
Senior Oracle DBA
Received on Fri Aug 01 2008 - 08:50:37 CDT

Original text of this message