Home » RDBMS Server » Performance Tuning » SGA size
SGA size [message #236018] Tue, 08 May 2007 12:56 Go to next message
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 #236023 is a reply to message #236018] Tue, 08 May 2007 13:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Nothing is urgent in a forum
2/ What is the relation between file creation and Oracle?

Regards
Michel
Re: SGA size [message #236026 is a reply to message #236023] Tue, 08 May 2007 13:42 Go to previous messageGo to next message
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 #236029 is a reply to message #236023] Tue, 08 May 2007 13:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why is this hardware guy that requires something about SGA and not DBA that searches what is the problem?

Regards
Michel
Re: SGA size [message #236038 is a reply to message #236029] Tue, 08 May 2007 14:35 Go to previous messageGo to next message
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 #236039 is a reply to message #236018] Tue, 08 May 2007 14:40 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
>So we decided to increase the SGA size.
>Because it might be affects all report generating queries.
Ready, Fire, Aim!
I am willing to bet you that increasing SGA will NOT have a positive performance impact.
You don't really know where or what the problem actually is & you are shooting blindly & hoping to get lucky.
Do you realize that in some cases making the SGA larger increases response time?
Re: SGA size [message #236040 is a reply to message #236039] Tue, 08 May 2007 15:02 Go to previous messageGo to next message
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 Go to previous messageGo to next message
BlackSwan
Messages: 25045
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 #236046 is a reply to message #236043] Tue, 08 May 2007 15:50 Go to previous messageGo to next message
vasudevan
Messages: 119
Registered: February 2006
Location: TRICHY
Senior Member
ok sir i will give all the above information within tomorrow

thanx.
Re: SGA size [message #237030 is a reply to message #236046] Fri, 11 May 2007 12:11 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: Explain plan Cost vs time elapsed
Next Topic: my production database is taking 50% of CPU consumption in main db server
Goto Forum:
  


Current Time: Wed Dec 07 18:23:19 CST 2016

Total time taken to generate the page: 0.12497 seconds