Currently weblogic has 120 concurrent connections to the database.There is a requirement to scale up the number ofconcurrent connections to 300.We have did some study and quite be quite sure that the current sga and pga is required. (the additional connections will be firing the same sql which is very well tuned.
However each additional connection to the database will take up additional memory just for the connection?Will is be using the memory in the PGA? Or outside the PGA for the connection overhead?We are not using MTS.
1) While connecting to the database as sysdba (sqlplus "/as sysdba"), it takes about 12 to 15 seconds what things I need to check/change so that connection will be faster?
2) Also, I have read somewhere that changing the sga/memory parameters connecting to the database. is it true?
3) This is not concerned with above 2 questions. What should be the memory parameters size for a 8 gb database?
What I have is:
Total System Global Area 1068937216 bytes Fixed Size 2182592 bytes Variable Size 1027605056 bytes Database Buffers 33554432 bytes Redo Buffers 5595136 bytes
I wonder if this JDBC Connection is a TCP/IP connection on the backend? If this is not a TCP/IP connection, how can I make a connection with TimesTen via TCP/IP protocol?
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?
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.
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
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
we are using windows server 2008 R2 and oracle database version 11.2.0.2
Have two points here : I/O performance and Out of memory issue
1.) ADDM Report recommended solution is to stripe all data files for to reduce "Wait class "User I/O" was consuming" But I am not sure It will improve more I/O performance(it will resolved the issue) and same issue we are getting on many servers.
ADDM Report Snipped:
Findings and Recommendations ---------------------------- Finding 1: I/O Throughput Impact is .28 active sessions, 44.97% of total activity. --------------------------------------------------------
The throughput of the I/O subsystem was significantly lower than expected.
Recommendation 1: Host Configuration Estimated benefit is .28 active sessions, 44.97% of total activity.
[code]....
2.) Same issue we got in another server and we increased memory thrice but again we got same below error. Now we are getting same below error another server.
Alert log Snipped:=
Wed Jun 12 07:12:15 2013 Process startup failed, error stack: Errors in file E:\APP\ORACLE\diag\rdbms\oemgc\oemgc\trace\oemgc_psp0_3316.trc: ORA-27300: OS system dependent operation:CreateThread failed with status: 8 ORA-27301: OS failure message: Not enough storage is available to process this command. ORA-27302: failure occurred at: ssthrddcr
i am running 11.2.0.1 on HP-UX PA-RISC (64-bit). My Memory max target is defined as 34 GB and memory target is 32 GB on the database. The host has 256 GB physical memory. i see memory utlization is almost 90% used when seen from Grid Control on the host and wondering, what's going on? when i see into more details, i see the following 3 HP Processes consuming around 3.6 GB memory and rest very small is consumed by Oracle processes. Who's using the rest of the memory?
I have a 11.2.0.3 database runing on AIX. The instance hangs and crashes due to memory usage issues. I checked the alert log of the database and this is what i see:
WARNING: Heavy swapping observed on system in last 5 mins.pct of memory swapped in [11.85%] pct of memory swapped out [3.45%]. make sure there is no memory pressure and the SGA and PGA are configured correctly. Look at DBRM trace file for more details.i also see the following in the alert.log:
Errors in file /opt/oracle/diag/rdbms/iut3px1/iut3px1/trace/iut3px1_cjq0_55312404.trc: ORA-00604: error occurred at recursive SQL level 2 ORA-04031: unable to allocate 3896 bytes of shared memory ("shared pool","select audit$,options from p...","sga heap(1,0)","kglsim object batch")
The file contents of iut3px1_cjq0_55312404.trc are:
==================== Process State Object ==================== ---------------------------------------- SO: 0x70000007b97de70, type: 2, owner: 0x0, flag: INIT/-/-/0x00 if: 0x3 c: 0x3 proc=0x70000007b97de70, name=process, file=ksu.h LINE:12616 ID:, pg=0
I have a quick question about Automatic Memory Management in 11g. I know that it combines the SGA and PGA components together, but the pga_aggregate_target (in 10g) was a target, rather than a strict limit. In certain circumstances you could (and we have) exceed the pga_ aggregate_ target by quite some way.
If I use AMM, does this enforce a limit on the PGA usage – or if I through enough bind variables at it, can I still exceed the ‘target’?
I am having trouble making my spfile parameters take effect and would like to set them manually. Disk reads are very slow, so I thought I would look at the db_cache_size in the spfile: <INSTANCE>.__db_cache_size=805306368 But, when I look at the parameter via 'show parameter cache': db_cache_size big integer 0
Digging deeper, I noticed none of my <INSTANCE>.__ parameters match the values returned from 'show parameter'. Most of my concern is sga_target & pga_aggregate_target both being zero.
My memory targets: memory_max_target big integer 49G memory_target big integer 49G
I would like to increase the db_cache_size in an attempt to reduce the table scan time.
When investigating memory usage on HP-UX V3 for my oracle database 11.2.0.3.4 which use AMM, it seems to me that system is not shifting shared SGA memory to private PGA memory when needed but it allocate other memory.The shared memory don't shrink all the memory_target id allocated. VSZ = RSZ
To test i have used this script which will allocate memory in process PGA.
create or replace package demo_pkg as type array is table of char(2000) index by binary_integer; g_data array; end;
[code]...
I have done the same test on a linux machine (Red Hat Enterprise Linux Server release 6.3) and the size of the POSIX shared memory switched (/dev/shm) to the PGA like demonstrated by Tanel Poder in his blog [URL]...
what I can do to troubleshoot an Oracle package that consumes all Available memory when it executes...The package is basically an ETL process - it reads data from an input table and reads data from the data warehouse for each row in the input to write to another table. The problem is reproducible and is dependent on the number of rows in the input table. The problem also appears to always stop at the same place when processing the input table.
Monitoring the Oracle process memory during execution shows the memory increase over time (usually an hour) before it maxes out and Oracle kills the process.
What should I look for in the package as far as the SQL statements to determine how to re-engineer the SQL to avoid this problem? I have attached the AWR report covering 1 hour when it ran.
awr_report_101_102.html ( 229.09K ) Number of downloads: 2
i was unable to log in to oracle database. i am doing the following steps :
- sqlplus /nolog - connect /as sysdba connect to an idle instance - shutdown ORA - 01034: oracle not available ORA - 27101: shared memory realm does not exist
each command i give is showing the following error: ORA - 44412: XE edition memory parameter invalid or not specified.
I'm fighting with a fairly large set of dynamic queries created by some developer (often SQL is over 2MB) accessing plenty of tables having something like 1000 columns...
Formatting of such query takes ages in Toad while is pretty fast in Oracle SQL Developer... SQL developer is running plenty write operations in local app data directory... moving it onto a ram-drive didn't worked how I could optimize Toad's formatter?
I'm often accessing a small table which has 100 columns of type VARCHAR2(4000).
The problem is that I cannot see the results of such query as Toad is throwing "out of memory" exception. How to deal with that?
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.
i created a materialized view, and whenever i try to refresh data, using the following package(dbms_mview.refresh), im getting the ORA-04030 error
ORA-12008: error in materialized view refresh path ORA-04030: out of process memory when trying to allocate 1052696 bytes (callheap,kllcqas:kllsltba)
As far as i know, there no shortage of ram in this machine, im not sure what is causing this error, Then i tried to do a bulk insert using a "INSERT /*+ APPEND */ " in a package, when i execute it, im getting the same error again.
ORA-04030: out of process memory when trying to allocate 1052696 bytes (callheap,kllcqas:kllsltba)
The number of records im trying to insert is around 600,000, but if i try to repeat the same process with 50000 records, it works fine. i Actually is there any oracle parameter i need to change, cause im afraid if i do that, later in production, it might effect some other modules.
My database is working fine till y.day. But today when I try to connect as sysdba i get the following error- let me know how to resolve the same
C:UsersAdministrator>set ORACLE_SID=sha
C:UsersAdministrator>sqlplus '/ as sysdba' SQL*Plus: Release 11.2.0.1.0 Production on Tue Sep 4 05:11:14 2012 Copyright (c) 1982, 2010, Oracle. All rights reserved. Enter password: Connected to an idle instance. SQL> startup mount
ORA-27102: out of memory OSD-00026: additional error information O/S-Error: (OS 1455) The paging file is too small for this operation to complete
this is not working ... always i am getting following output
QUOTE x processes w/o controlling ttys t by tty *********** output format ********** *********** long options *********** -o,o user-defined -f full --Group --User --pid --cols --ppid -j,j job control s signal --group --user --sid --rows --info -O,O preloaded -o v virtual memory --cumulative --format --deselect -l,l long u user-oriented --sort --tty --forest --version -F extra full X registers --heading --no-heading --context ********* misc options ********* -V,V show version L list format codes f ASCII art forest -m,m,-L,-T,H threads S children in sum -y change -l format -M,Z security data c true command name -c scheduling class -w,w wide output n numeric WCHAN,UID -H process hierarchy
I am using 11g version, and trying to understand the Memory Allocation.
1) The new feature Memory target parameters are dynamic? is in it? But when I set with the ALTER SYSTEM... it was not changed, then I had to restart the DB.
2) With the new 2 parameters (MEMORY_MAX_TARGET, MEMORY_TARGET), the SGA_MAX_SIZE parameter should be 0, right? But in my case, the SGA_MAX_SIZE value is automatically assigned.
As per my understanding , Timesten or IMDB Cache can be connected through DSN by any external client. Want to know whether Radware can be integrated with Timesten or IMDB Cache.
I am getting most of the time 'out of process memory' when i run the 'ord' procedure.here i am providing the tables and the procedure.
i have 2 table orders which holds distinct values, and departments table has ordvalue column holds long string of values for a particular record from orders table. for example if the values in orders table as follows:
ord_code ord_level ordid ordstatus ord_num user utimestamp SR11 1 2 A 101 V SYSDATE SR11 1 2 A 102 V SYSDATE SR11 1 2 A 103 V SYSDATE SR11 1 2 A 104 V SYSDATE SR11 1 2 A 105 V SYSDATE SR11 1 2 A 106 V SYSDATE SR11 1 1 B 101 R SYSDATE SR11 1 1 B 102 R SYSDATE SR11 1 1 B 103 R SYSDATE SR11 1 1 B 104 R SYSDATE SR11 1 1 B 105 R SYSDATE SR11 1 1 B 106 R SYSDATE
ETC... AT TABLE departments data will be like this
ord_code ord_level ordid ordstatus ord_num user utimestamp SR11 1 2 A 101,102,103,104,105,106 V sysdate SR11 1 2 B 101,102,103,104,105,106 R sysdate
from the get_ord procedure the data aggreates using the xmlelement and gets as a long string value into departments table of ord_num column.
CREATE TABLE test.orders ( ord_CODE VARCHAR2(4 BYTE) NOT NULL, ord_LEVEL VARCHAR2(1 BYTE) NOT NULL, ordID NUMBER(5) NOT NULL, ordstatus VARCHAR2(1 BYTE) NOT NULL, ord_num NUMBER(3) not null, user VARCHAR2(8 BYTE), UTIMESTAMP DATE [code]...
I wanted to know what is the standard memory allocation for SGA & PGA in a normal installation of Oracle database 10g or 11g? Are the figures of 270M and 90M standard for a 10g or 11g installation using automatic memory management feature?
