| SGA size [message #236018] |
Tue, 08 May 2007 12:56  |
vasudevan
Messages: 119
Registered: February 2006
Location: TRICHY
|
Senior Member |
|
|
Hello sir
I need urgent help from ur support
Actually "when user tries to create a file in our project locks up for about 1 min before the file is created this happens for all files that users tries to create"
ur Hardware guys asked me to increase the SGA size, i dont know whether it is right or not
Anyway i want to increase Sga size from 1.2GB to 2.2GB plz help what can i do ,how can i increase the Sga size from 1.2GB to 2.2GB
>show sga(ur database server total sga)
Total System Global Area 1343035792 bytes
Fixed Size 453008 bytes
Variable Size 536870912 bytes
Database Buffers 805306368 bytes
Redo Buffers 405504 bytes
Total RAM size 16GB used 14.2GB free 22MB
OS : Redhat linux 3.0 version
cpu : 2692.816MZ
users :120 (per server)
Thanx sir.
|
|
|
|
|
|
| Re: SGA size [message #236026 is a reply to message #236023] |
Tue, 08 May 2007 13:42  |
vasudevan
Messages: 119
Registered: February 2006
Location: TRICHY
|
Senior Member |
|
|
sir
Actually user said it as file creation means in oracle technical words generates reports from sql.
- thank u for ur responce sir
|
|
|
|
|
|
| Re: SGA size [message #236038 is a reply to message #236029] |
Tue, 08 May 2007 14:35 |
vasudevan
Messages: 119
Registered: February 2006
Location: TRICHY
|
Senior Member |
|
|
sir
Actually ur total RAM size 16GB used:15.2GB free 22MB
WE don't where 15.2GB RAM used in server
Total SGA size 1.2GB
But there was 136 oracle session were used (we found by v$session)so we think all session takes this RAM size
So we decided to increase the SGA size.
Because it might be affects all report generating queries.
- this is ur situation
|
|
|
|
|
|
| Re: SGA size [message #236040 is a reply to message #236039] |
Tue, 08 May 2007 15:02 |
vasudevan
Messages: 119
Registered: February 2006
Location: TRICHY
|
Senior Member |
|
|
sir
please gimme some tips to analyse this problem,we dont know
how to handle this problem
Actually we use more session than what we do for this.
i am really appreciated ur kind response
|
|
|
|
| Re: SGA size [message #236043 is a reply to message #236018] |
Tue, 08 May 2007 15:21 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
What OS name & Version?
Which version of Oracle to 4 decimal places?
When was the last time statistics were collected?
execute DBMS_STATS.GATHER_DATABASE_STATS (NULL, FALSE, 'FOR ALL COLUMNS SIZE 1', NULL, 'DEFAULT', TRUE, NULL, NULL, 'GATHER STALE', 'LIST' );
Enable SQL_TRACE for queries that are deemed to be too slow &
run the resultant trace file thru TKPROF & post results here.
|
|
|
|
|
|
| Re: SGA size [message #237030 is a reply to message #236046] |
Fri, 11 May 2007 12:11 |
vasudevan
Messages: 119
Registered: February 2006
Location: TRICHY
|
Senior Member |
|
|
What OS name & Version?
>Redhat linux(3)
>oracle version 9i
Which version of Oracle to 4 decimal places?
>9.2.0.7.0
When was the last time statistics were collected?
>no statistics avilable
SQL Trace?
>Herewith i attached the SQL trace plan
(If there is any way to tune the same for RBO mode)i mean withour gathering anything.
thank u sir
|
|
|
|
| Re: SGA size [message #237057 is a reply to message #237030] |
Fri, 11 May 2007 14:14 |
sriram717
Messages: 48
Registered: February 2007
Location: UNITED KINGDOM
|
Member |
|
|
Hi ,
what is the result set ? , can you upload the tkprof rather than the explain plan output.
We need to understand the rowsource operation and the plan.
You need to collect the statistics.
Also please upload the statspack report.Is this query fired once in a while or is it fired frequently.
If this is query is frequently fired have you considered using bind variables?
Was this query ever perfroming well ? do have a base line for this?
Thanks
|
|
|
|
| Re: SGA size [message #237069 is a reply to message #237057] |
Fri, 11 May 2007 15:20 |
vasudevan
Messages: 119
Registered: February 2006
Location: TRICHY
|
Senior Member |
|
|
Sir if i try to take statsreport it shows the following below error i dont know what is this
ERROR at line 1:
ORA-01013: user requested cancel of current operation
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at "SYS.DBMS_SHARED_POOL", line 45
ORA-06512: at "SYS.DBMS_SHARED_POOL", line 53
ORA-06512: at "PERFSTAT.STATSPACK", line 2708
ORA-06512: at line 1
- plz help me in regard this
|
|
|
|
| Re: SGA size [message #237197 is a reply to message #237069] |
Sun, 13 May 2007 04:41 |
sriram717
Messages: 48
Registered: February 2007
Location: UNITED KINGDOM
|
Member |
|
|
The message clearly says user cancelled request of operation , you would have done a cancel operation by pressing the control keys.
Please post the command that you had run
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
