Home » RDBMS Server » Performance Tuning » Performance Tuning.very critical
Performance Tuning.very critical [message #65952] Fri, 04 February 2005 21:58 Go to next message
Vivek Vijai
Messages: 67
Registered: April 2004
Member
I am giving parameter values and Problems which i am facing .. if u could give some valueable suggestions
that will be very helpful for me.

shared_pool 152 M
buffer_cache 104 M
Large Pool 8 M
Java poll 24 M
log buffer - 0.5 M
Total SGA - 184 M
sga_max_size=305 M
db_block_size = 8192
pga_aggregate_target - 24 M

processes = 90
parallel_max_servesr = 5
sessions = 104
undo_management = AUTO
hash_join_enabled = TRUE
sort_area_size = 1 M
open_cursors = 300
aq_tm_processes = 1

Database size 22 GB

The database very rarely does any DML, the application which is using the database is meant
for Reporting only so 95% queries are executed.
No of users (maximum) connected at the database will be 16-17 (and each of them
have around 3-4 sessions open)

These errors, i am continously recording in Alertlog

ORA-00600: internal error code, arguments: [[kohfrm505]], [[]], [[]], [[]], [[]], [[]], [[]], [[]]

and since last 2 days, i am getting this error:

ORA-04030: out of process memory when trying to allocate 16396 bytes (koh-kghu sessi,pmuccd: tds)
and yesterday as i told u, server refused to give any new connectionss o we have to restart the instance

Summarizing the above issues....

1) Queries are taking longer time than before
2) Too many locks are shown in OEM

We have tested all the indexes etc for query performance but nothing happened.

Will it be helpful if i deallocate unused extents from the tables or rebuild the indexes
or increasing the value for PROCESSES and PARALLEL_MAX_SERVERS

please let me know what should i do to tune it..

Thanks in advance

Vivek
Re: Performance Tuning.very critical [message #65953 is a reply to message #65952] Sat, 05 February 2005 01:47 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Please always post your environment.
what is the Oracle version and OS version??????

For ORA-04030 , increase your large_pool and java_pool_size.

ORA-0600 errors are supposed to be handled only by oracle support.
Becuase they are properly equipped to handle these.
Please post the trace file associated with it ( only the relevant parts).
Re: Performance Tuning.very critical [message #65969 is a reply to message #65953] Wed, 09 February 2005 05:47 Go to previous messageGo to next message
Vivek Vijai
Messages: 67
Registered: April 2004
Member
Sorry Mahesh
I am sending the relevant log from Alert file and corresponding TRC file
Oracle version 924 and Platform - WIN 2k
RAM - 1 G
Intel p 3 1400 M Hz

ALERT
ORA-00600: internal error code, arguments: [[kohfrm505]], [[]], [[]], [[]], [[]], [[]], [[]], [[]]
ORA-00600: internal error code, arguments: [[kohfrm505]], [[]], [[]], [[]], [[]], [[]], [[]], [[]]
ORA-04030: out of process memory when trying to allocate 16396 bytes (koh-kghu sessi,pmuccd: tds)
ORA-04030: out of process memory when trying to allocate 16396 bytes (koh-kghu sessi,pmuccst: adt/record)

TRACE
Current SQL statement for this session:
select * from the (select cast(GH_VK_Inventory.Reporting_all(:1, :2) as PLM_tp) from dual)
----- PL/SQL Call Stack -----
object line object
handle number name
516B57B0 468 package body MUSERS.GH_VK_Inventory
----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
_ksedmp+327 CALLrel _ksedst+0
_ksfdmp.108+14 CALLrel _ksedmp+0 3
0276F4EB CALLreg 00000000 3A83F270 3
0276FA05 CALLrel 0276F468 3A83F270 1813C90C 2962EEC 0
.....................
....................
......................

(Trace file is having similar entries like above.. )

I really want to solve this "Out of memory problem"
as the application is getting effected badly.
Please let me know .. how to solve this problem and if it is is application related then how can I ask developers to tune it.

Regards
Vivek.
Re: Performance Tuning.very critical [message #65971 is a reply to message #65969] Wed, 09 February 2005 06:29 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
as told before,
Please increase your large_pool and java_pool_size.
if it wouldnt solve the problem, apply the latest patchsets.
Re: Performance Tuning.very critical [message #65973 is a reply to message #65971] Fri, 11 February 2005 08:11 Go to previous messageGo to next message
Vivek Vijai
Messages: 67
Registered: April 2004
Member
Hey Mahesh thanks a ton for your valueable tips
but i am not very clear that how come large_pool or Java_pool are related with this problem as large_pool is used mainly in shared server mode or for rman and we r not using either ..
so if you could throw some light on it will be very helpful for me

thanks in advance

Vivek
Re: Performance Tuning.very critical [message #65983 is a reply to message #65973] Tue, 15 February 2005 11:43 Go to previous message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
mostly these are to do with memory leaks.
If the case is reproducible, please apply the latest patchsets.
Previous Topic: trace unused tables
Next Topic: Urgent:How to find long running query in Oracle
Goto Forum:
  


Current Time: Thu Feb 22 17:53:14 CST 2024