performance issue, memory leak?

You really should not call on Experts by name.  It might drive others away.

>>get Db Console working?

That should have been answered in the original question.  We cannot address this here.

>>How to detrmine if Oracle is using the memory or not

Use OS tools.  Get with your system administrator for this.  Most UNIX/Linux have a top command.  Windows has various utilities from sysinternals but start with Task Manager.

>>if it is teh app that is causing the issue, by memory leak? how to determine this

Easiest way is from a reboot, look at he memory used per 'connection/user' then monitor it over time.

If the average memory used per connection keeps going up, there is likely a leak.

Thank you , will keep that in mind.

1) Will work on getting the DB console working
2) This is a Windows Server. Checked teh Task Manager and as mentioned in the previous thread, there is 5 GB memory FREE and after few hours , there is only 50 MB FREE. h rest is cached. How to determine what is causing teh cache?
3) "look at he memory used per 'connection/user' then monitor it over time" -- where do i look for this, in Task Manager? if so, as i mentioned  earlier Oracle and java are the top cosumers with 700 MB and 165 MB respectively. if not in the task manager,  you mean by downloading sysinternals?

also, from the earlier thread you had seen how much max memory has been allocated to Oracle, based on that do you have any recommendations?

alos, just downloaded the sysinternal Utilities Suite. looking at Process Explorer, is there any other utilitiy inside sysinternal that i should be looking at? i see that there is a Cacheset.exe,

thank you! will look ino this after lunch and get back.

Thanks again!

guess what, the app automatically collects data from external source and writes them into the database every hour. I just tried to check the Process Moniter after reading your post and the screen is frozen i cant ven move the mouse, since its the peak of the hour the app is doing what it is supposed to do and it is taking up all the resources i guess. i see CPU is 100% pegged. it may eventually ( after 5-10 minutes) will release these once its job is completed.

i dont see anything taking up memory :-( Oracle still is the top consumer with 450 MB.

The amount of FREE memory keeps going up and down in task manager. from 5 GB to 50 MB.

in the Process Explorer, emdctl.exe keeps appearing and disappearing in RED , within seconds. it does'nt stay put for me to click on it and see further into it.

>> from 5 GB to 50 MB

That is a major swing in usage.  Something is wrong there.

By chance does it drop to 50 Meg when the app runs?

I cannot think of an app that needs 5 Gig to run for normal business processing.

I would check with the app development team to see if this is normal.

the application has been shutdown all day today, there is 6 GB FREE on the server.
i tried to update the stats on the tables and go "out of process memory " error. looked at the alert_log and it contained below msgs...

-------

ORA-04030: out of process memory when trying to allocate 64528 bytes (sort subheap,sort key)
Incident details in: d:\app\incher\diag\rdbms\innov\innov\incident\incdir_179398\innov_ora_2220_i179398.trc
Errors in file d:\app\incher\diag\rdbms\innov\innov\trace\innov_ora_2220.trc  (incident=179399):
ORA-04030: out of process memory when trying to allocate 169004 bytes (pga heap,kgh stack)
ORA-04030: out of process memory when trying to allocate 64528 bytes (sort subheap,sort key)
Incident details in: d:\app\incher\diag\rdbms\innov\innov\incident\incdir_179399\innov_ora_2220_i179399.trc
Errors in file d:\app\incher\diag\rdbms\innov\innov\incident\incdir_179398\innov_ora_2220_i179398.trc:
ORA-04030: out of process memory when trying to allocate 169004 bytes (pga heap,kgh stack)
ORA-04030: out of process memory when trying to allocate 64528 bytes (sort subheap,sort key)
Errors in file d:\app\incher\diag\rdbms\innov\innov\trace\innov_ora_2220.trc  (incident=179400):
ORA-04030: out of process memory when trying to allocate 82444 bytes (pga heap,control file i/o buffer)
ORA-04030: out of process memory when trying to allocate 64528 bytes (sort subheap,sort key)
Incident details in: d:\app\incher\diag\rdbms\innov\innov\incident\incdir_179400\innov_ora_2220_i179400.trc
Thu Oct 20 16:27:57 2011
Trace dumping is performing id=[cdmp_20111020162757]
Thu Oct 20 16:27:57 2011
Process startup failed, error stack:
Errors in file d:\app\incher\diag\rdbms\innov\innov\trace\innov_psp0_3696.trc:

and the below is from the last trace file  _3696.trc
---------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Windows NT Version V6.0 Service Pack 2
CPU                 : 4 - type 586, 4 Physical Cores
Process Affinity    : 0x0x00000000
Memory (Avail/Total): Ph:6195M/8186M, Ph+PgF:24846M/27900M, VA:6M/2047M
Instance name: innov
Redo thread mounted by this instance: 1
Oracle process number: 7
Windows thread id: 3696, image: ORACLE.EXE (PSP0)


*** 2011-10-20 16:27:57.521
*** SESSION ID:(696.1) 2011-10-20 16:27:57.521
*** CLIENT ID:() 2011-10-20 16:27:57.521
*** SERVICE NAME:(SYS$BACKGROUND) 2011-10-20 16:27:57.521
*** MODULE NAME:() 2011-10-20 16:27:57.521
*** ACTION NAME:() 2011-10-20 16:27:57.521
 
Process startup failed, error stack:
ORA-27300: OS system dependent operation:CreateThread failed with status: 8
ORA-27301: OS failure message: Not enough storage is available to process this command.
ORA-27302: failure occurred at: ssthrddcr

also, reading on the web i found the following on OTN forums-- seems like a known bug in 10.2. we are using 11g , but 32 bit OS, may be this pplies to 11g as well?

-----------------------

https://forums.oracle.com/forums/thread.jspa?threadID=364644


Re: ORA-27300 in 10gR2
Posted: Dec 21, 2006 4:52 AM    in response to: jo****         Reply  
 
Hello Johan,

Oracle finally admit there was a bug: BUG 5607984 - ORACLE DOES NOT CLOSE TCP CONNECTIONS. REMAINS IN CLOSE_WAIT STATE. [On Windows 32-bit].

The patch 10 (patch number 5639232) is supposed to solve the problem for 10.2.0.2.0. We applied it monday morning and everything is fine up to now.

This bug is also supposed to be solved in the 10.2.0.3.0 patchset that is availlable on the Metalink site.

Hope this information will help you.

Regards.

Carl
 
 

Hi! you there?

will install oracle on a 64-bit machine and move the database to see if that fixes the issue.

>>Hi! you there?

Sorry, I was out on Friday and don't really do Experts-Exchange on Weekends.

Glad it worked for you.  I didn't know you were running 32Bit  Guess I should have asked that.

32Bit could not address that much memory.  Moving to 64Bit should fix it.

Sure, not a problem.

So from your comment above, is this a know issue in 32-Bit Windows?
as you already know , the issue being memory getting cached on the server.

i have not moved the database to 64-Bit OS yet, will do in a little bit. Should i also use 64-Bot Oracle or can i use 32-bit Oracle?

32Bit implies 4 gig addressable, more or less.  There are some smoke and mirrors with PAE but with Oracle, anything close to 2Gig used from past experience does weird things.

I never tried to get even close to using 2Gig for Oracle on Windows 32bit.  Once I went to 64Bit Oracle, I never looked back.

Will 64bit definitely fix this?  I don't know but I can say Oracle will be happier that you are now on 64Bit.

ok, thanks!  

" Should i also use 64-Bit Oracle or can i use 32-bit Oracle? "

64Bit to address more than 2 gig.

also, did you happen to hear about the issue i mentioned from Oracle forums above? sometimes i am not so sure about what all is posted in the forums.

"BUG 5607984 - ORACLE DOES NOT CLOSE TCP CONNECTIONS. REMAINS IN CLOSE_WAIT STATE. [On Windows 32-bit]."

---
 what do you think about the erro i posted from the trc_ file? definitely a OS issue?

Process startup failed, error stack:
ORA-27300: OS system dependent operation:CreateThread failed with status: 8
ORA-27301: OS failure message: Not enough storage is available to process this command.
ORA-27302: failure occurred at: ssthrddcr

I've run into bugs like that when the app doesn't properly close and dispose of connections connections.  Mainly way back in Oracle 8 and early 9.

It depends on how your app works.  If you see a LOT of connections when it runs, you might be hitting this bug.

ok, i guess i need to be more clear and specific in my questions ;-)

In this scenario, what would you recommend. so you suggested to check what is using-up the memoryand see my responce

a) i do not see anything , shutdown the app, all day, still see memory being cached in task manager.
b) tried to update statitccs on teh database and it caused about mentioned errors.

I'll need to reread the thread but if your SGA is trying to reference memory close to 2Gig it might be causing the problem.


On mobile right not and cannot remember what your SGA/PGA parameters were.

If 64Bit is an option, I would do it.  It couldn't hurt anything but might fix everything.

The vendor mentioned that they only tested the app on 32-Bit.

Hopefully you will get a chance to re-read the thread , i posted lot of information information Friday.

This might be a repeat question:  the app runs on the same server as the database?

If so and the developers think this might be an issue, can you separate it, an app server and database server?

the databse has been seperated from the application. the database is now on a 64-Bit machine. will moniter the application performance for a day and let you know.

any suggestions? this thread seems to be going no where.

seems like a database issue to me , i was trying a data pump export ( as suggested in the new thread) and ran int o the following error. The application is not running. this is the same error as i mentioned in the past , when i try to update statistics on the tables or indexes.

ORA-39097: Data Pump job encountered unexpected error -1423
ORA-39065: unexpected master process exception in MAIN
ORA-01423: error encountered while checking for extra rows in exact fetch
ORA-04030: out of process memory when trying to allocate 123404 bytes (QERHJ hash-joi,kllcqas:kllsltba)
Job "SYSTEM"."STARWARFULLJOB" stopped due to fatal error at 09:57:36

>>any suggestions? this thread seems to be going no where.
>>seems like a database issue to me

Move to 64Bit and give Oracle more memory.

But going from 5Gig to 50G used the problem will likely not go away.

I would suggest you bring in an Oracle Consultant to work you this system hands-on.

well to move to 64-bit i need to export the database, hence the data pump and the above error.

so there is no way to fix the above error besides bringin-in a Oracle Consultant? :-(
What steps woul you take if you encounter such issue?  moving to 64-Bit? Ok, but i cant even export

>>well to move to 64-bit i need to export the database

Good point.  How large is the database?  You could try the old exp/imp commands.

From a sqlplus prompt please post the output of:
show parameter memory
show parameter pool
show parameter pga

i opened 3 questions in past 10 days, , which are all interlinked.

let me refresh your memory, we had question where we exchanged all this information  several times. and then considering that you deserve more points , i closed that one awarded the points and opened this one after dicussing with you ( 10 days back) and now we are going back in circle.

The dump file size is 4 GB. NO, I CANNOT try exp/imp since you suggested ( in another currently open question) cause it was not exporting tables with 0 records. you suggested i use data pump, and again we are going in circles ;-)

based on another recommendation from you, i just increased the SGA_MAX_SIZE and SGA_TARGET from 1600M to 2700M. and now i cant even startup the database- see rrro below

-------

SQL> startup
ORA-27102: out of memory
OSD-00029: additional error information
O/S-Error: (OS 8) Not enough storage is available to process this command.

Not sure if I have asked this before or not:  Do you have Oracle Support?

>>and again we are going in circles ;-)

Trust me, I know!  I've just lost track of what all has been tried and what hasn't.

also this question has gotten way side-tracked.  That could possibly what is leading to the confusion.

>>ORA-27102: out of memory

32Bit.  Cannot go above 2 Gig wothout the /PAE 'trick' and I say 'trick' because no one I've ever heard of has actually done it.

>>The dump file size is 4 GB. NO, I CANNOT try exp/imp since

Can you break up the tables/objects/schemas into smaller pieces?

can we plz start with bringing the databse back online?

how do i get the SGA back under 2 Gb and start the database?

Depending on how far it got try another alter system against the spfile.

If that doesn't work:
SQL> create pfile='c:\mypfile.ora' from spfile;
-- edit c:\mypfile.ora in notepad and manually change the sga parameters back to 2G.  save and exit

SQL> startup pfile='c:\mypfile.ora';


--if everything works:
SQL> create spfile from pfile='c:\mypfile.ora';

thanks much! the database is back online!

So , whats next. I cannot update stats or can do a expdp without out of process memory error.
you are suggesting to increase the memory to over come this erro r, but as we just saw increasing is causing other issues.
does this mean Oracle is not supported on 32-bit? we know that's not the case.

below are the parameters you asked for ...

SQL> show parameter memory

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------
hi_shared_memory_address             integer     0
memory_max_target                    big integer 3280M
memory_target                        big integer 3200M
shared_memory_address                integer     0
SQL> show parameter pool

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------
buffer_pool_keep                     string
buffer_pool_recycle                  string
global_context_pool_size             string
java_pool_size                       big integer 0
large_pool_size                      big integer 0
olap_page_pool_size                  big integer 0
shared_pool_reserved_size            big integer 50751078
shared_pool_size                     big integer 0
streams_pool_size                    big integer 0
SQL> show parameter pga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------
pga_aggregate_target                 big integer 0
SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 1680M
sga_target                           big integer 1680M
SQL>

The main 11g memory parameters are larger than 2 Gig.  This could be it.


Try this as a test:

alter system set memory_max_target=1.9G scope=spfile;
alter system set memory_target=1.9G scope=spfile;

alter system set sga_target=0 scope=spfile;
alter system set sga_max_size=0 scope=spfile;

SQL> alter system set memory_max_target=1.9G scope=spfile;
alter system set memory_max_target=1.9G scope=spfile
*
ERROR at line 1:
ORA-32005: error while parsing size specification [1.9G]

hmmm.... thought fractional numbers were allowed.

Oh well:

alter system set memory_max_target=1940M scope=spfile;
alter system set memory_target=1940M scope=spfile;

seems like if 1.9 G is a incorrect value specification?

Thank you Sir! it worked like a charm. What next , shall i try the expdp?

>>What next , shall i try the expdp?

Yes please.

I need to ask:  You did shutdown/restart the database.  

If not, you need to.
If so, let me know how the expdp works.

yes, i restarted it, know some basics ;-)

did the expdp and it completed sucesfully, fantastic! taking lunch lunch break now, be back.

thanks again!

*WHEW*  THAT was a tough one!  Wish I would have thought about looking at memory weeks ago!

It's almost my end of work day.  I'll try to be on later in the evening but will be away from any real computers if I need to test anything major.

thanks again, will close the ticket later tonight. actually you did ask me for the memory parameters, and you were on the road where we lost the track for a bit.

below are the memory parameters from the 64-Bit server, anything you would suggest to change before i move the copy?  Thanks!

SQL> show parameter memory

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
hi_shared_memory_address             integer     0
memory_max_target                    big integer 3280M
memory_target                        big integer 3280M
shared_memory_address                integer     0
SQL> show parameter pool

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
buffer_pool_keep                     string
buffer_pool_recycle                  string
global_context_pool_size             string
java_pool_size                       big integer 0
large_pool_size                      big integer 0
olap_page_pool_size                  big integer 0
shared_pool_reserved_size            big integer 23488102
shared_pool_size                     big integer 0
streams_pool_size                    big integer 0
SQL> show parameter pga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
pga_aggregate_target                 big integer 0
SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 3280M
sga_target                           big integer 0
SQL>

Set sga_max_size to 0.

If you have the memory, crank up the two memory parameters.  This will get you good increase in performance.

some how i am unable to set sga_max_size to 0.  this is what i am doing....

-----

SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 1164M
sga_target                           big integer 0
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.

Total System Global Area 1217835008 bytes
Fixed Size                  1380280 bytes
Variable Size             364904520 bytes
Database Buffers          843055104 bytes
Redo Buffers                8495104 bytes
SQL> alter system set sga_max_size=0 scope=spfile;

System altered.

SQL> commit;

Commit complete.

SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1217835008 bytes
Fixed Size                  1380280 bytes
Variable Size             364904520 bytes
Database Buffers          843055104 bytes
Redo Buffers                8495104 bytes
Database mounted.
Database opened.
SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 1164M
sga_target                           big integer 0
SQL>

OK, I should have checked the docs first.

There is a 'default' value for this so it cannot be 0:
http://download.oracle.com/docs/cd/E11882_01/server.112/e25513/initparams231.htm#REFRN10198

On 64-bit platforms and non-Windows 32-bit platforms, when either MEMORY_TARGET or MEMORY_MAX_TARGET is specified, the default value of SGA_MAX_SIZE is set to the larger of the two parameters. This causes more address space to be reserved for expansion of the SGA.

i set MEMORY_MAX_TARGET and MEMORY_TARGET as the same , 3600M , hope that is OK.

That is what I do.  Mine are the same values.