| Performance Tuning.very critical [message #65952] |
Fri, 04 February 2005 21:58  |
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 #65969 is a reply to message #65953] |
Wed, 09 February 2005 05:47  |
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 #65973 is a reply to message #65971] |
Fri, 11 February 2005 08:11 |
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
