How Memory Is Calculated
Nov 16, 2012I 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 ?
ADVERTISEMENT
SQL & PL/SQL :: Calculated Column In 10g R2
Nov 19, 2012In my table there are 188 column , i compare these with other table to know which column values have changed. i use ora_hash to get hash value and then i compare with other table.
I want to create calculated column but i am unable to create as it says invalid data type.
alter table STG_TBLDISRUPTIONS add stg_hash as ORA_HASH (INTINTERNALID || VCHIMSNO || CHRFANDDNO || INTINCIDENTNO || VCHDESCRIPTIONFAULT
|| TININCIDENTTYPEID || TINLINEID || DTMINCIDENTFAULTSTARTDATETIME || DTMNOTIFICATIONDATETIME
|| DTMSTARTDATETIME || DTMENDDATETIME || DTMACTUALENDDATETIME || DTMDURATIONENDDATETIME
|| CHRFROMBRSSTATIONID || CHRTOBRSSTATIONID || VCHLOCATIONDESC || CHRDIRECTIONID || SINEXTRARUNNINGTIME
|| CHRPLATFORMNO || BLNMULTIPLESIGNAL || TINNOOFPEOPLETRAPPED || CHRMODEOFFAILURE || TINDAYOFWEEKTABLEID
[code].........
SQL & PL/SQL :: Calculate With Calculated Fields?
Feb 24, 2010can I use calculated fields (b) for another calculation (c) in the same select?
select 10*a as b,
10*b as c
from tab
The code gives an error, but how can I do this?
Server Administration :: Settings Infringe On Any Available Memory On System That Is Already Tight On Memory?
Jul 25, 2012I 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?
Forms :: Continuous Update Of Calculated Field?
Aug 6, 2013I have seen in probably VB or Dot net software being used at some premises that there "formula computation is live and interactive".
What i means that suppose a cashier has made invoice having total of 83$. Mow in the text box for entering amount received, the cashier is about to enter 100 but surprisingly the returned value/remaining value (total invoice - amount received) is being computer LIVE.
This is what happens.
1) The cashier entered 1 and the remaining value instantly showed 82 (83-1)
2) then on other key stroke of 0 by cashier and then then the remaining value instantly showed 73 (83-10)
3) then on other key stroke of 0 by cashier and then then the remaining value instantly showed 17 (83-100)
Hence a continuous updation/changing of the remaining value on every key stroke is observed.
Forms :: Populating Form With Calculated Values
Aug 19, 2010I have a form in which the fields are calculated fields. So i need to populate the form after calculating the values based on a database columns. For example if i have a table as below
COL A | Col B | Col C | Col D
2 3 4 5
3 4 5 6
I have 3 fields on my form which i need to populate such that field1 maps to col A. Field2 value is COL B * COL D for that value of COL A. Field 3 value is COLB * COL D for that value of col A.
I tried something like this
PROCEDURE NAME IS
cursor c1 is SELECT * FROM TABLEL;
begin
go_block('Block1');
clear_block(no_validate);
for i in c1 loop
:BLOCK.field1 := i.COL_A;
[code].......
this fetched all the records for field1 but for field2 and field3 it only calculates and populates the field row.
How Kernel Parameters Values Calculated In 10g
Feb 9, 2013*10g Oracle installation on OEL*
How Kernel Parameters values calculated in 10g
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
+# semaphores: semmsl, semmns, semopm, semmni+
kernel.sem = 250 32000 100 128
[code].......
Run the following command to change the current kernel parameters
/sbin/sysctl -p
** "/etc/security/limits.conf" **
* soft nproc 2047
* hard nproc 16384
* soft nofile 1024
* hard nofile 65536
My question I think 32 bit or 64 bit OS calculation will be same only. I want know how it was calculated ?
TimesTen In-Memory :: Process Of Initialization Of Database Into Memory
May 23, 2013Can 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.
Server Utilities :: Calculated Column Population Using Sql Loader?
Aug 1, 2012I want to insert calculated data while loading the data from sql loader.
eg let say column INTERNAL_TRANSACTION_ID,WAIT_TIME,MESSAGE_GUID,TRS_SIZE,RETURN_MESSAGE_GUID are null in input file then i want to populate column DEV_FLAG as 'U' ELSE 'D'
My control file looks like below but doesn't have DEV_FLAG.
load data
BADFILE '/backup/temp/rajesh/RIO/BadFiles/FILENAME'
append into table TEMP_rio_RESP_TIME_LND
TRAILING NULLCOLS
(
INSTALLATION_ID CHAR
TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
TRANSACTION_ID CHAR
[code]....
Forms :: Frm-41375 - Cannot Set Queryable Property Of Calculated Item
Jun 10, 2013frm-41375: cannot set queryable property of calculated item :emp.sumsal.
View 8 Replies View RelatedPerformance Tuning :: MEMORY-TARGET And MEMORY-MAX-TARGET?
Jan 15, 2011I 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 RelatedNot Able To Increase Value Of Memory-Target Up To Memory-max-target?
Aug 24, 2012We 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
I/O Performance And Out Of Memory?
Jun 12, 2013we 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
[code]....
Memory Of Each Connection?
Mar 18, 2013Currently 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.
PL/SQL :: What Are Objects Using PGA Memory
Feb 5, 20131)collections uses pga memory ,does globle tem table used in sp also uses PGA memory.
2)and does type of table of object type uses pga memory.
Memory Max Target Is Defined As 34 GB
Jul 5, 2012i 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?
View 3 Replies View Related11.2.0.3 Database Running On AIX - Memory?
Nov 12, 2012I 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
[code]...
SQL & PL/SQL :: ROW-00001 / Cannot Allocate Memory
Feb 28, 2012I am facing this error in my application page
OraOLEDB(0x80040E14)
ROW-00001
Cannot allocate memory
Automatic Memory Management In 11g
Nov 27, 2012I 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’?
11gR1 Memory Configuration?
Oct 10, 2012Specs are as follows:
Linux kernel 2.6.18-274.12.1.el5
Total physical memory - 96G
Oracle version:
Oracle Database 11g Release 11.1.0.6.0 - 64bit Production (Standard Edition)
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.
AMM On HP-UX V3 Shared Memory Deallocation?
May 13, 2013When 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]...
Related conf :
lock_sga=FALSE
pre_page_sga=FALSE
pga_aggregate_target 0
sga_target 0
memory_target 5553258496
memory_max_target 5553258496
How To Troubleshoot Package That Consumes All Available Memory
Sep 12, 2011what 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
XE Edition Memory Parameter Invalid Or Not Specified
Jan 25, 2011i 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.
Database Connection And Memory Parameters
Sep 4, 2012I have 3 questions.
I have 11.2.0.1 database on Windows 2008 R2 SP1.
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
Client Tools :: Toad Out Of Memory
Aug 30, 20122 questions:
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?
Server Administration :: SGA Memory Filled
Apr 14, 2012We 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.
Replication :: ORA-04030 - Out Of Process Memory
Apr 4, 2008i 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.
exec M_GET_FACILITIES_DETAILS.GET_AUTOFINANCE_HP_DETAILS('29-Feb-2008');
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.
Windows :: Oracle DB Out Of Memory Error
Sep 4, 2012My 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
How To Calculate Memory Used By Each Databases In A Server
Aug 27, 2013we 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 ?
Find The Process ID Which Is Hogging CPU / Memory?
Nov 17, 2012Trying to find the Process ID which is hogging CPU/Memory
$ ps -e -o pcpu,pid,user,tty,args |grep -i oracle|sort -n -k 1 -r|head
here , this works well ..
TOP 10 PROCESSES CONSUMING MOST CPU
$ watch ps -e -o pcpu,pid,user,args |sort -k 1 -n -r | head -10
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