Server Administration :: Settings Infringe On Any Available Memory On System That Is Already Tight On Memory?
Jul 25, 2012
I have the following setup
SQL> show parameter sga;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 3G
sga_target big integer 2G
from what I read I beleive this will initially grab 2GB of memory on startup and will grab up to to 3GB of memory total for the SGA. The "total" memory can be allocated to different peices of the SGA when needed but will never exceed 3GB. Is this correct or would these settings infringe on any available memory on a system that is already tight on memory?
Secondly, what happens if both these values are set to the same value?
View 8 Replies
ADVERTISEMENT
Nov 16, 2013
I installed timesten server and client on different machines in LAN, but with the same user and group - ttadmin:ttgroup When I tried to connect to server with ttisqlcs -connStr "DSN=sampleCS"The output gave me error messages as below connect "DSN=sampleCS";S1000: Failed to retrieve IP address of the system. System error: -2The command failed.Done. The related part in sys.ttconnect.ini is set as below
[192.168.0.206]Description=TimesTen ServerNetwork_Address=192.168.0.206TCP_PORT=53397
And the
sys.odbc.ini [ODBC Data Sources]sampleCS=TimesTen 11.2.2 Client Driver [sampleCS]TTC_SERVER=192.168.0.206TTC_SERVER_DSN=nredb_ds
What does it mean by "retrieve IP" ?Does it try to resolve IP by the hostname even when I already gave it the ip address?By the way I can ping 192.168.0.206 and telnet at port 53397 with no problem.
View 4 Replies
View Related
Feb 11, 2011
I have few queries on PGA memory management.Since these queries are based on 2-3 examples not exactly same by nature I am summarising it after my understanding for the same
As I understand many workareas can be allocated to a single sql statement and number and sizes of theses workareas is controlled internally by Oracle when Automatic Memory management (PGA_aggregate_target and workarea_size_policy=Auto are set) Since many sessions share the PGA memory, the amount of memory available to each session may vary and if less amount of memory is available for a session for sorting then TEMP tablespace is used
[1] Can we say paging happens and can be checked at this time?
[2] Is there a difference in handling memory while populating pl/sql tables?
As I have encountered ora-04030 while some our developers were populating pl/sql tables but never encountered this error for sorting, hash joins etc Though I don't remember the width of pl/sql table, I am sure the developer used 'LIMIT' clause during bulk collect and still faced the issue.
With a single session on the server, I noticed that the difference in values displayed issuing 'free' command in linux and output values from sesstat did not match at all while there wasn't any heavy OS process involved during the period. I was expecting 'used' and 'free' values displayed by free command (linux) will change and difference would be approximately equals 'before and after values of session pga memory.
[3] Isn't it expected to match?
[4] Can we say in dedicated server, at any moment of time, the SUM of 'session pga memory' represents all the memory used by Oracle SGA, at that point of time?
select sum(value)/1024/1024 "memory in MB" from v$sesstat where statistic#=20;
During one of the tests I got following output (divide value by 10 for my visibility and avoid formatting)
SQL> select a.name, to_char(b.value/10, '999,999,999') value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and a.name like '%ga memory%'; 2 3 4
[code]...
The above query is showing above values even when the pl/sql block execution is completed 30 minutes back
[5] Do we call this as 'memory leak' where memory is not released even while some time has passed since session has done something?Of course I am not checking at OS level as mentioned in question [3] above the values won't match!
Still the output of free command for reference(After the pl/sql block executed)
SQL> !free
total used free shared buffers cached
Mem: 3016796 2999660 17136 0 4308 1173260
-/+ buffers/cache: 1822092 1194704
Swap: 1048568 636124 412444
--(After the pl/sql block executed)
SQL> select * from v$pgastat;
NAMEVALUEUNIT
aggregate PGA target parameter 524288000bytes
aggregate PGA auto target 456256512bytes
global memory bound 26214400bytes
total PGA inuse 17328128bytes
[code]...
[6] What could be the significance of negative values of 'session pga memory/max'?
Last We have an OLTP system and in the night we run batch processes in 2-4 sessions
Suppose I have 10 GB RAM and with PGA setting of 3.5 GB Now I want the batch process sessions to use max possible memory during nighttime and toggle the setting back in the morning
[7] With above settings (10 GB RAM and 3.5 GB PGA) how can I divide the memory among 4 sessions?
Shall I set 1) PGA_aggregate_target=0 2)Workarea_size_policy=manual 3) Sort_are_size 4) Hash_area_size
[8] What would be approx values for parameter 3 and 4? will it be straight 3.5 GB/ 4?
View 8 Replies
View Related
Apr 14, 2012
We are running oracle9i database in windows 2000 server few months before i re sized the SGA to sga_max_size =800M and pga-aggregate_target to 400M, actually we have 4gb RAM out of the we are allocated 1200Mb for SGA and PGA.
My question is if the allocated memory got filled in SGA or PGA whether the system will show any error, because when I am querying from sql> show parameter ; the sga free memory is reducing daily what will happen when it reaches its max_size.
View 1 Replies
View Related
May 11, 2011
estimate the percentage of memory should i can allocate from the total physical memory (it is 6G) as attached in the image file as a print screen.
View 4 Replies
View Related
Mar 18, 2013
I need to ask about PGA memory. Is this memory for every user or every session
i.e. PGA 200M
I have 2 users with 300 connection with one of them. Is 200M for 300 connection or for every connect.
View 2 Replies
View Related
Jan 25, 2010
I am facing a problem as listed below:
$sqlplus /nolog
SQL>conn /as sydba
connected.
SQL>conn upt/upt
not connected
ora-01034 not available
ora-27101 shared memory realm doesntnot exists
linux error-2 no such file ...
SQL>...
UPT is the user in which our data resides.
wat cud be the problem??????
I tried increasing sizes in INIT.ORA.... listener,tns is OK
.bash_profile of ORACLE user is also correct.
View 6 Replies
View Related
Dec 27, 2011
I am facing this problem continuously from last 1 week on my production oracle database.I have performed all kind of changes on parameter but still am getting this error.
Oracle version -> 11g
O/S -> windows server 2003
RAM -> 11 GB
#########################INIT CONFIG###################
convdb.__db_cache_size=536870912
convdb.__java_pool_size=10485760
convdb.__large_pool_size=4194304
[code].....
View 5 Replies
View Related
Mar 28, 2010
I read in few blog and they are saying, oracle11g allocate 60% of the memory to SGA and 40% of the memory to PGA when we enable MEMORY_ TARGET to non-zero.Here are few blogs. URL.....It is not working on that way(60:40) on my database.
Here is my DB version.
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production
[code]....
I am not able to find 60%:40% concept in oracle document..so all the above blogs info are wrong?
View 2 Replies
View Related
Apr 7, 2013
I have read this article:
[URL].........
And i see:
Quote:
If MEMORY_TARGET is set to non zero value:
SGA_TARGET, SGA_MAX_SIZE and PGA_AGGREGATE_TARGET are set to 0, 60% of memory mentioned in MEMORY_TARGET is allocated to SGA and rest 40% is kept for PGA.
So, I have set:
alter system set sga_target=0 scope=spfile;
alter system set pga_aggregate_target=0 scope=spfile;
alter system set sga_max_size=0 scope=spfile;
alter system set memory_max_target=512M scope=spfile;
alter system set memory_target=300M scope=spfile;
, and then bounced the instance.
After startup, I see:
SQL> startup
ORACLE instance started.
Total System Global Area 272027648 bytes
Fixed Size 1384012 bytes
Variable Size 100663732 bytes
Database Buffers 163577856 bytes
Redo Buffers 6402048 bytes
Database mounted.
Database opened.
SQL>
But Total System Global Area should't be in that case 60% of memory mentioned in MEMORY_TARGET? Memory mentioned in MEMORY_TARGET was 300M, and 60% of 300M is 180M, which is not 272027648 bytes.
I just want to use automatic memory management, so I've set the other parameters above to 0. My instance is on my local machine, so just for my own personal use.
View 8 Replies
View Related
May 23, 2013
Can i know the internal process of initialization of DB into memory in timesten , when a new connection is establishing? Will timesten create tables and indexes in RAM when first connection is established if the RAM policy is default?
want to know the internal functional flow of timesten when any command is fired against it.
View 3 Replies
View Related
Sep 18, 2012
i have altered the sga_max_size to 2gb.i found the system free memory is also decreased.Is system free memory changes when sga_max_size is changes?
View 1 Replies
View Related
Jan 17, 2013
i did this step
C:UsersAdministrator>sqlplus
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jan 17 15:59:33 2013
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Enter user-name: sys@orcl as sysdba
Enter password:
Connected to an idle instance.
SQL> create pfile from spfile;
[code].....
here its my init.ora file
##############################################################################
# Copyright (c) 1991, 2001, 2002 by Oracle Corporation
##############################################################################
###########################################
# Cache and I/O
###########################################
[code].....
View 7 Replies
View Related
Oct 8, 2011
I have installed Oracle 10g on my PC and create a Database on it. I wanted to connect it with PL Sql Developer with the same PC but it`s not connecting. It gives the following error "ORA-01019: unable to allocate memory in the user " And some time blank error message box.
Here is system information:
OS: Winsows 7
Oracle Database 10g Express Edition
PL SQL Developer 8.0.3.1510
Login Setting
User Name: book
Password: oracle
Database: XE
Connect as: Normal
View 5 Replies
View Related
Jun 9, 2011
Oracle 10.2.0.4 (user or developer getting error for not able to allocate ora 04031.
I am trying to do alter system shared_pool_size = 250M but it says
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-04033: Insufficient memory to grow pool
I checked the show parameter sga_max_size = 376 M
the set upin the init.ora for pools:
###########################################
# Pools
###########################################
java_pool_size=157286400
large_pool_size=33554432
shared_pool_size=157286400
From show parameter sga;
[code]...
no rows selected
OS Linux. this was the message in alert.log;
ORA-04031: unable to allocate 88 bytes of shared memory ("shared pool","select /*+ rule */ bucket_cn...","sql area","opn: qkexrInitOpn")
View 5 Replies
View Related
Sep 16, 2003
All of a sudden my oracle application has stopped functioning.When I try to log in I get the following messages:
ORA-27101 SHARED MEMORY REALM DOES NOT EXIST
ORA-01034 ORACLE NOT AVAILABLE
From the command line when I try to connect as sysdba and startup the following message appears: "oracle instance terminated.Disconnection forced"
View 18 Replies
View Related
Jun 17, 2011
I have a 11g database installed on a windows 2003 server. When I created the database I could not assign more than 2GB for SGA_TARGET where as I have 16GB of RAM available on the server. I created the database with SGA_TARGET as 1.5 GB and Memory_TARGET as 2GB. I could create the database successfully. Later when I again tried to increase SGA_TARGET to 6GB and MEMORY_TARGET to 8GB, I could not start the database. I got below error;
ORA-27102: out of memory
OSD-00022: additional error information
O/S-Error: (OS 8) Not enough storage is available to process this command
Quote:I added /pae in the boot.ini
Added AWE_WINDOW_SIZE key to registry as 2000000000
Set use_indirect_data_buffers=true
added db_block_buffer=131072 (ie. 2GB DB_BLOCK_SIZE=16kb)
java_pool_size= 1000M
large_pool_size=1000M
shared_pool_size=2000M
I got again the same error
I could not use SGA_TARGET. So is AMM not allowed with AWE?
View 21 Replies
View Related
Nov 20, 2002
I'm using Oracle8i, with VB6 as front end When i try to connect Oracle using RDO in VB i get an error message from Oracle It is S1000:Oracle ODBC.ora Ora:1043 User Side Memory Corruption.
View 11 Replies
View Related
Apr 12, 2013
i try to startup a database, and get that obsolete/deprecated parameter specified(ORA-32004: obsolete and/or deprecated parameter(s) specified)so i try to reset the parameter, but it seems it is already reset(not in spfile), so maybe this is in memory. SO now i try to reset with scope = memory or both, it gives this error(ORA-32029: resetting with SCOPE=MEMORY or SCOPE=BOTH is currently not supported).
what to do, because now i cannot reset with scope = memory and in spfile it is not there but still it comes in deprecated parameter.
View 7 Replies
View Related
Sep 7, 2010
When user insert rows in table oracle interrupts and raise error- ORA-04031: unable to allocate 256 bytes of shared memory? how to do?
I tried to increase the size of shared_pool also but cant.
large_pool_size = 0
shared_pool_size=134217728(128M)
sga_max_size=218103808(208M)
And when I fire 'select* from v$sga;'
Fixed Size 2142976
Variable Size 159386880
Database Buffers 50331648
Redo Buffers 5296128
View 3 Replies
View Related
Jan 1, 2013
I have a package when it runs, it terminates with "unable to allocate 4120 bytes of shared memory"error at a particular INSERT statement. So everytime we had this error, we had to flush the shared pool area, after which it would run without issues(for 3-4 weeks).
It had a dynamic subpartition clause framed, which made it to repharse the query every time.So to reduce this, we removed the subpartition clause(made it to static query), which did benefit of not getting shared pool error.
I would like to find the current shared pool memory usage of my program.Is there a way I could find the live consumption of shared pool area of my program(pkg).
View 10 Replies
View Related
Jun 20, 2012
we have 96GB Memory on the UNIX server and 85% of its usage shows oracle processes I want to determine which Oracle processes are taking most of the memory
SGA is around 36G
SGA_TARGET is 40G
PGA is around 4G
the total of around 40-45 GB of usage is understandable but what other oracle process are chewing up the remaining 30-40 GB on the server is not known
load averages: 7.35, 6.46, 6.15; up 248+11:33:21 12:25:03
2202 processes: 2196 sleeping, 1 zombie, 5 on cpu
CPU states: 83.8% idle, 10.5% user, 5.8% kernel, 0.0% iowait, 0.0% swap
Memory: 96G phys mem, 15G free mem, 128G total swap, 128G free swap
PID USERNAME LWP PRI NICE SIZE RES STATE TIME CPU COMMAND
21720 oracle 258 0 0 40G 40G cpu/48 215:28 2.04% oracle
10709 oracle 1 0 2 1816K 1448K cpu/9 0:02 0.90% res_conf_email_
[code]......
View 6 Replies
View Related
Sep 24, 2013
i am continuously inserting data to oracle database after some time like 2 hours oracle disconnects,it creates erros like
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Process ID: 0
Session ID: 0 Serial number: 0
after restarting the database with shutdown immediate ans startup if i start inserting records it will show erros like
ORA-01653: unable to extend table SYSTEM.GLT_PROT_TRAFFIC_SUM_VOIP by 8192 in tablespace GLCOMM
but i have created the Tablespace with BigFile Auto Extend and max size unlimited, i am having 400GB disk space created Redo logs with 15gb,i have tried serveral times reinstalling the oracle but problem is not solved.
same problem is happenging with small datafiles also,
operating system: windows server 2008 R2 standard
oracle server : 11g
oracle client : 64 bit
View 14 Replies
View Related
Jan 15, 2011
I have a confusion with MEMORY_TARGET and MEMORY_MAX_TARGET parameter. if i set SGA_TARGET, SGA_MAX_SIZE along with MEMORY_TARGET and MEMORY_MAX_TARGET then how oracle will manage the memory? Because as per my understanding if we set MEM
View 3 Replies
View Related
Aug 27, 2013
we have 7 databases running in a server .
OS : solaris
i want to add a new database on that server . So i want to find , whether we have a sufficient memory to add a new database . how to find it ?
View 5 Replies
View Related
May 12, 2013
I'm running Oracle 11.2g enterprise and we have a database on a server. We want to take a backup from this database and restore it in another database with fewer memory. In restore procedure after edition of pfile I try to restore controlfiles with my edited pfile(initdb.ora), but i'm getting an error related to memory shortage. So i decreased the amount of *.memory_target(3.5G to 2.5G) and the restore procedure continued. At the end when i try to Open database(alter database open resetlogs) i got the following error and the database does not work normally.
RMAN> alter database open resetlogs
database opened
RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT row
s
ORACLE error from target database:
ORA-06553: Please-801: internal error [56319]
ORA-00604:error occurred at recursive sql level 1.
View 13 Replies
View Related
Apr 18, 2011
the erelationship between sga_max_size,sga_targt,shared_pool_size,pga_aggregate_target and the server memory.
In short how shud i choose the above parameters for a server with a fixed RAM.
View 1 Replies
View Related
Aug 24, 2012
We are using the 11.1.0.7 database, we implemented the Memory_Max_Target and Memory_target in the database.Here is the value of the memory parameters:
SQL> show parameter memory_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 3G
memory_target big integer 2G
shared_memory_address integer 0
We want to increase the value of the Memory_target=3G, means, I want to increase the value of the memory_target upto Memory_max_target by using below command:alter system set MEMORY_TARGET=3G scope=both SID='OLTP1'; but I am getting below error:
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00846: could not shrink MEMORY_TARGET to specified value
I tried to give the memory_target value less than the memory_max_target value like:alter system set MEMORY_TARGET=2900M scope=both SID='OLTP1'; but get the same error:
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00846: could not shrink MEMORY_TARGET to specified value
View 6 Replies
View Related
Jun 9, 2011
I just want to know what are precautionary measures if tablespaces in a database is in autoextend mode. I'm wondering if these tablespaces reached its maximum sizes.
In our case, we are administering a database (turned over by our outsourcer after a 2-year maintenance) with SAP interface, and we noticed that most of it's tablespaces were created with initial size of 2Gb up to a maximum size of 10Gb, all were 'autoextensible'.
View 1 Replies
View Related
Nov 16, 2012
I am not a major member of performance tuning area.Performance team is saying DB utilizing high memory. DB team is telling DB utilizing low memory.
How it was calculated here ?
View 3 Replies
View Related
