1  select name, sum(value/1024) "Value - KB"
  2   from v$statname n,
  3   v$session s,
  4   v$sesstat t
  5   where s.sid=t.sid
  6   and n.statistic# = t.statistic#
  7   and s.type = 'USER'
  8   and s.username is not NULL
  9   and n.name in ('session pga memory', 'session pga memory max',
 10   'session uga memory', 'session uga memory max')
 11*  group by name
SQL> /

NAME                                                             Value - KB
---------------------------------------------------------------- ----------
session pga memory                                               24606.8555
session pga memory max                                           368674.586
session uga memory                                               2417.37891
session uga memory max                                           445290.664
why in my case, uga max is larger than pga max?
Thanks. 

Tom,
When does the PGA memory gets deallocated?. Is it possible to deallocate PGA memory without disconnecting from the session?. Is there an Oracle Note which describes PGA/UGA memory allocation/deallocation?

Thanks & Rgds

Rukshan

Hi Tom
I have your book and its brilliant.
However, when we run a stored proc thro' a loop after a few iterations we get ORA-4030. I was wondering of a way of avoiding this.
eg:
begin
for i in 1..100 loop
stored_proc();
end loop;
end;
/
fails after about 60 iterations.
Rgds

Rukshan

Unfortunately its a Java program. The Oracle stored procedure calls this Java program. But, its not possible to run standalone. Needs apps server etc.

CREATE OR REPLACE procedure MatchcodeLookup(whereIn IN VARCHAR2) AS LANGUAGE JAVA NAME 'MatchcodeLookup.run (java.lang.String)';
/

Java Program:
=============
import capscan.client.McConnection;
import oracle.jdbc.driver.OracleDriver;

import java.io.FileInputStream;
import java.sql.*;
import java.util.Date;
import java.util.Properties;
import java.util.StringTokenizer;
import java.util.Vector;

public class MatchcodeLookup
{
//-------------------------------------------------------------------------
// Mode values
//-------------------------------------------------------------------------
private final static int MODE_INTERACTIVE = 0;
private final static int MODE_BATCH = 1;
private static int prevMode = -1;

//-------------------------------------------------------------------------
// Private static attributes
//-------------------------------------------------------------------------
private static boolean alreadySetup = false;
private static boolean inDebug = false;
private static int pfSelFields; // pre fixed selected fields number

// select & where vectors
private static Vector select = null;
private static Vector where = null;

// default connection parameters
private static int mcType = McConnection.SEARCH;
private static String mcHost = "localhost";
private static String mcPool = "PAF";
private static String mcName = "conntest";

// matchcode tables
private static String confTable = "MATCHCODE_CONFIG";
private static String resTable = "MATCHCODE_TEMP";
private static String errTable = "MATCHCODE_ERROR";

/*
* This is the actual code run as a Java Stored Proceedure, parameters can
* be set up by changing the values set up in the matchcode_config table.
* Results will be written to the matchcode_temp table. Exceptions will be
* written to the matchcode_error table.
*/
public static void run(String query)
{
performSearch(query, MODE_INTERACTIVE);
}

/**
* This method is used when wanting to verify a single address. It is for
* using when performing batch processing, to avoid looping through
* ambiguity lists.
*/
public static void verifyAddress(String query)
{
performSearch(query, MODE_BATCH);
}

private static void performSearch(String query, int mode)
{
Connection cn = null;
PreparedStatement ps = null;
Statement s = null;
McConnection mcConn = null;

// First time stored procedure is run - get setup configuration
if (!alreadySetup || mode != prevMode)
{
// Set as already if setup completed succesfully
alreadySetup = setup(cn, mode);
}

// If still setup wasn't made, exit run
if (!alreadySetup)
return;

prevMode = mode;

try
{
// Use parameter as ADDR criteria
Vector data = new Vector();
data.addElement(query);

// Get DB connection if not yet done (in setup)
if (cn == null)
cn = new OracleDriver().defaultConnection();
s = cn.createStatement();

// Prepare statement for inserting results to temporary table
StringBuffer statement =
new StringBuffer("INSERT INTO " + resTable + " VALUES(");
for(int i = 0; i < select.size() - pfSelFields - 1; i++)
{
statement.append("?, ");
}
statement.append("? )");
ps = cn.prepareStatement(statement.toString());

// Connect to matchcode server and preform search
mcConn = new McConnection(mcHost, mcPool, mcName,
McConnection.CONNORIENTED);
String result[];
// In batch mode, use only crossmatch
if (mode == MODE_BATCH)
result = mcConn.search(McConnection.CROSS_MATCH,
select, where, data, 30);
else
result = mcConn.search(mcType, select, where, data, 30);

// Check for ambiguity - interactive mode only
int ambigCount = 0;
if (mode == MODE_INTERACTIVE)
{
try
{
ambigCount = Integer.parseInt(result[0]);
}
catch(Exception excParse) {}
}

// In case of ambiguity, analyze ambig list field. In batch mode,
// ambigCount will be set to 0 and so this will be false.
if (ambigCount > 0)
{
String ambigList = result[1];

// Break ambiguity list to address tokens
StringTokenizer ambigST = new StringTokenizer(ambigList, "@");
int noTokens = ambigST.countTokens();
if (noTokens > 1)
{
// Build new where and data vectors
Vector where2 = new Vector();
where2.addElement("POSTCODE");
Vector data2;
String results2[];

// Loop through address records
String addrToken, postcode;
StringTokenizer addrST;
while (ambigST.hasMoreTokens())
{
addrToken = ambigST.nextToken();
addrST = new StringTokenizer(addrToken, ";");

// First token is full address, second is full postcode
addrST.nextToken();
postcode = addrST.nextToken();

// Update criteria with current postcode and search
data2 = new Vector();
data2.addElement(postcode);
results2 = mcConn.search(McConnection.POSTCODE_LOOKUP,
select, where2, data2, 5);

// Insert each row to temp results table
insertRow(ps, results2);
}
}
}
// Batch mode, or alternatively interactive more with no ambiguity,
// insert single address to temp results table
else
insertRow(ps, result);

// Debug message
if (inDebug)
System.out.println("MatchcodeLookup results written");
}
catch (Exception excSearch)
{
try
{
s.executeUpdate(
"INSERT INTO " + errTable + " VALUES('" + new Date() + " " +
excSearch.getMessage().replace('\'','\"') + "')");
}
catch (Exception excSQL)
{
System.err.println("MatchcodeLookup error " + new Date() + "\n"
+ excSearch.getMessage() + "\n" + excSQL.getMessage());
}
}
finally
{
try
{
mcConn.disconnect();
s.close();
ps.close();
}
catch (Exception excClose)
{
System.err.println("MatchcodeLookup error disconnecting " +
new Date() + "\n" + excClose.getMessage());
}
}
}

private static boolean setup(Connection cn, int mode)
{
PreparedStatement ps = null;
Statement s = null;
boolean rc = true;

try
{
cn = new OracleDriver().defaultConnection();
ps = cn.prepareStatement(
"SELECT ConfigVal from " + confTable + " where ConfigKey = ?");

s = cn.createStatement();
ResultSet rs = null;

// Get selected field list
ps.setString(1, "SELECT");
rs = ps.executeQuery();

select = new Vector();

// For interactive mode, always select ambiglist
if (mode == MODE_INTERACTIVE)
{
select.addElement("LISTCOUNT");
select.addElement("AMBIGLIST(LF=S)");
pfSelFields = 2;
}
else
pfSelFields = 0;

while (rs.next())
{
select.addElement(rs.getString(1));
}
rs.close();

// Get where field list (should be only ADDR)
ps.setString(1, "WHERE");
rs = ps.executeQuery();

where = new Vector();
while (rs.next())
{
where.addElement(rs.getString(1));
}
rs.close();

// Get host name, pool name, application name and query type
mcHost = getSetupData(ps, "MCHOST");
mcPool = getSetupData(ps, "MCPOOL");
mcName = getSetupData(ps, "MCNAME");
mcType = Integer.parseInt(getSetupData(ps, "MCTYPE"));

// Get debug mode, if exists
ps.setString(1, "DEBUG");
rs = ps.executeQuery();
if (rs.next() && rs.getString(1).toUpperCase().charAt(0) == 'Y')
inDebug = true;
rs.close();

// Debug message
if (inDebug)
System.out.println("MatchcodeLookup setup completed");
//System.out.println(
// "Host: " + mcHost + "\n" +
// "Pool: " + mcPool + "\n" +
// "Name: " + mcName + "\n" +
// "Type: " + mcType);
}
catch (Exception excSetup)
{
rc = false;
try
{
s.executeUpdate(
"INSERT INTO " + errTable + " VALUES('Setup: " + new Date() +
" " + excSetup.getMessage().replace('\'','\"') + "')");
}
catch (Exception excSSQL)
{
System.err.println("MatchcodeLookup setup error " + new Date()
+ "\n" + excSetup.getMessage() + "\n" + excSSQL.getMessage());
}
}
finally
{
try
{
s.close();
ps.close();
// do not close db connection
// cn.close();
}
catch (Exception excSClose)
{
rc = false;
System.err.println("MatchcodeLookup setup error2 " +
new Date() + "\n" + excSClose.getMessage());
}
finally
{
return rc;
}
}
}

private static void insertRow(PreparedStatement ps, String[] result)
throws SQLException
{
for (int i = pfSelFields; i < result.length; i++)
{
ps.setString(i - pfSelFields + 1, result[i]);
}
ps.executeUpdate();
}

private static String getSetupData(PreparedStatement ps, String str)
throws SQLException
{
String result = null;
ResultSet rs;
ps.setString(1, str);
rs = ps.executeQuery();
rs.next();
result = rs.getString(1);
rs.close();

return result;
}
}

Tom:

I am running 8.1.7.0 on Tru64 unix. My understanding from reading this and other postings is that when using dedicated server connections the uga is contained in the pga. Can you explain why the uga session max is so much higher than pga session max in the following:

SQL> select sid,username,server,status from v$session
  2  where sid=34;

       SID USERNAME                       SERVER    STATUS
---------- ------------------------------ --------- --------
        34 DW                             DEDICATED ACTIVE

SQL> select name, sum(value/1024) "Value - KB"
  2   from v$statname n,
  3   v$session s,
  4   v$sesstat t
  5   where s.sid=t.sid
  6   and n.statistic# = t.statistic#
  7   and s.type = 'USER'
  8   and s.username is not NULL
  9   and s.sid=34
 10   and n.name in ('session pga memory', 'session pga memory max', 'session uga memory', 'session uga memory max')
 11   group by name
 12   
SQL> /

NAME                                                             Value - KB
---------------------------------------------------------------- ----------
session pga memory                                               1343.60938
session pga memory max                                           1343.60938
session uga memory                                               130.742188
session uga memory max                                           1573063.65

SQL> 

Thanks as always,

Rob 

Tom:

The session is running an export. Here is the query. I changed it a little since v$mystat is info on the current session and the session in question is not my current session. 


SQL> select name, value                                                         
from v$statname n, v$sesstat t                                                  
where n.statistic# = t.statistic#                                               
and t.sid = 28                                                                  
and n.name in ( 'session pga memory', 'session pga memory max',                 
                'session uga memory', 'session uga memory max')                 
/                                                                               

NAME                                   VALUE     
-----------------------------------------------------
session uga memory                    146512     
session uga memory max            1260850632     
session pga memory                   1535936     
session pga memory max            -721944024     

Tom,

this is my pga info
NAME VALUE
---------------------------------------------------------------- ----------
session uga memory 207980
session uga memory max 207980
session pga memory 312152
session pga memory max 66101520


When i execute this query i get this error
ORA-4030 Out of process memory
Called heap temporary memory

SELECT WP.FCTY_CODE PRO_FCTY_CODE,
WP.WELL_CODE PRO_WELL,
WCS.WELL_CONDITION PRO_WELL_STATUS,
WDC.PRODUCING_HOURS PRO_HOURS,
WCS.CHOKE_CHANGE_TIME PRO_CC_TIME,
WCS.CHOKE_SETTING PRO_CH_SETTING,
WDC.WELLHEAD_TEMPERATURE PRO_WHT,
WDC.WELLHEAD_PRESSURE PRO_WHP,
WDC.SUBSEA_RISER_PRESSURE PRO_SUB_R_PRESSURE,
WDC.CASING_INNER_PRESSURE PRO_SUB_I_PRESSURE,
WDC.CASING_MIDDLE_PRESSURE PRO_SUB_M_PRESSURE,
WDC.CASING_OUTER_PRESSURE PRO_OUTER_PRESSURE,
WDC.GAS_LIFT_VOLUME PRO_GAS_LIFT_VOL,
WAR.GAS_PRODUCED_COMMERCIAL PRO_GAS_COMMERCIAL,
WAR.OIL_PRODUCTION_VOLUME PRO_OIL_VOL,
WARY.OIL_PRODUCTION_VOLUME PRO_OIL_VOL_YTD
FROM odtkrun.V_WELLS_PRODUCTION WP,
odtkrun.V_WELLS_SEGREGATION WS,
odtkrun.O_WELL_DAILY_CONDITION WDC,
odtkrun.O_WELL_CHOKE_SETTINGS WCS,
odtkrun.O_WELL_ALLOC_RESULT WAR,
odtkrun.O_WELL_ALLOC_RESULT_YTD WARY
WHERE WP.FCTY_CODE = 'BERA'
AND WP.SYST_CODE = 'BERYL'
AND WP.SYST_CODE = WS.SYST_CODE
AND WP.FCTY_CODE = WS.FCTY_CODE
AND WP.WELL_CODE = WS.WELL_CODE
AND WS.FLD_CODE IN ('BUK', 'SKN')
AND NVL(WS.DATE_EFFECTIVE, LAST_DAY('31-AUG-2003')) <= LAST_DAY('31-AUG-2003')
AND NVL(WS.DATE_RETIRED , LAST_DAY('31-AUG-2003')) >= LAST_DAY('31-AUG-2003')
AND WS.DATE_EFFECTIVE = (SELECT MAX(WS2.DATE_EFFECTIVE)
FROM odtkrun.V_WELLS_SEGREGATION WS2
WHERE WS2.SYST_CODE = WS.SYST_CODE
AND WS2.FCTY_CODE = WS.FCTY_CODE
AND WS2.WELL_CODE = WS.WELL_CODE
AND WS2.FLD_CODE = WS.FLD_CODE)
AND WS.SYST_CODE = WDC.SYST_CODE
AND WS.FCTY_CODE = WDC.FCTY_CODE
AND WS.WELL_CODE = WDC.WELL_CODE
AND WDC.DAY = LAST_DAY('31-AUG-2003')
AND WS.SYST_CODE = WCS.SYST_CODE
AND WS.FCTY_CODE = WCS.FCTY_CODE
AND WS.WELL_CODE = WCS.WELL_CODE
AND WCS.CHOKE_CHANGE_TIME = (SELECT MAX(WCS1.CHOKE_CHANGE_TIME)
FROM odtkrun.O_WELL_CHOKE_SETTINGS WCS1
WHERE WCS1.SYST_CODE = WCS.SYST_CODE
AND WCS1.FCTY_CODE = WCS.FCTY_CODE
AND WCS1.WELL_CODE = WCS.WELL_CODE
AND WCS1.CHOKE_CHANGE_TIME < LAST_DAY('31-AUG-2003')+1.25)
AND WS.SYST_CODE = WAR.SYST_CODE
AND WS.FCTY_CODE = WAR.FCTY_CODE
AND WS.WELL_CODE = WAR.WELL_CODE
AND WAR.DAY = LAST_DAY('31-AUG-2003')
AND WS.SYST_CODE = WARY.SYST_CODE
AND WS.FCTY_CODE = WARY.FCTY_CODE
AND WS.WELL_CODE = WARY.WELL_CODE
AND WARY.DAY = LAST_DAY('31-AUG-2003')
ORDER BY WP.WELL_CODE
/

Thanks.

Sorry tom, my db version is 817

hash_area_size=20971520
sort_area_size=10485760

Operating System is HP-UX 11.0

No, the machine is not memory starved, but the Sys Admin has set user process to 64Mb.

Thanks.

The limitation was taken off but i am still getting the same error, or can you please comment on the query.

Thanks.

Please see below. How do I interpret the result from the query? This a new session and I have not done any query that needs sort space. I was wondering why there is a difference between uga memory and uga memory max. Also, what is the meaning of pga memory max? Is pga memory max 417068 bytes already allocated for the process in its pga? Thanks.

SQL> select b.sid, a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic#= b.statistic# and a.name like '%ga %';


       SID NAME                           VALUE
---------- ------------------------- ----------
        12 session uga memory             76960
        12 session uga memory max        273352
        12 session pga memory            220460
        12 session pga memory max        417068

SQL> show parameter sort

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_sort                             string
sort_area_retained_size              integer     0
sort_area_size                       integer     524288 

When I configure my database "auto start" with Windows Is there PGA allocated for any process? Is it using any sort of authentication to startup? Password file/OS

Dear Tom:
We are using web application. We are using WebSphere as an Application Server and oracle 9i (9202) is being used as a Database. There are normally 40 connected sessions from the App Server. I wanted to ask what should be the PGA size? and should it be MTS or Dedicated Server Configuration. And can you advice about the shared pool, db cache size, log buffer, pga aggregate size, sort area size etc??

I confused with session data! My understanding is session data contains table data. But in the discussion session data contains pl/sql variables, cursors etc...
Please give me a clear meaning of session data.

sample output of a top command :

PID USERNAME THR PRI NICE SIZE RES STATE TIME CPU COMMAND
5379 oracle 1 59 0 161M 139M sleep 0:00 0.16% oracle
5364 oracle 11 55 0 162M 140M sleep 0:00 0.15% oracle
5560 oracle 1 58 0 161M 139M sleep 0:00 0.11% oracle
5555 oracle 1 59 0 161M 142M sleep 0:00 0.09% oracle

The SGA for the oracle instance is 126M. So is it right if I say that each user connection approximately takes up 161-126=35M. Besides the PGA (the UGA being part of it in this dedicated server example), the rest of the 35M is the memory required to run the Oracle executable?

Thanks
ana

Tom,

How about in the RAC environment, the uga is in the pga area or in the sga area?

<quote>
The shared server you are attached to, had 417,068 bytes of memory allocated and used at some point. Currently, it has 220,460 allocated and used.
</quote>

Does that mean that, in shared server mode, if I repeatedly look at the 'session pga memory max' statistic in a single session, it might change (and in fact even decrease) just because I get a different shared server that happened to have been using less PGA in its lifetime as the one I got the last time? Hmmm...

Hello Tom,
When I execute some procedures I get the error
"Buffer Over flow".What can be the reason behind this?
The System also hangs forever.I am not able to proceed further.
Is there any way that enables us to flush the PGA?

the system is not "hanging forever" from a "serveroutput buffer overflow" (something else might be happening, but it isn't that)

SQL> set serveroutput on size 1000000



 

Tom
I need to have a formula for calculating the PGA/UGA
in MTS and dedicated server mode.

What about if the client uses dedicated connection even though the server is configured in MTS

Thanks

Tom,

you are saying:
PGA is a process piece of memory, it is per process
UGA is a session state, it is per session

I understand that. These notions are from different worlds - PGA is process memory, an OS notion if you will, UGA is per session so it is an Oracle notion. Then how can they be used in the same context like e.g. (Metalink Note):

- Dedicated Server Configuration.
The sort area is allocated in the Program Global Area (PGA)
- Multi-threaded Server Configuration.
The sort area is allocated in the User Global Area (UGA)

This is very confusing. According to you sort areas are always in UGA whether or not UGA itself is in PGA. To add to the confusion:
"session pga memory" statistic is per session. How can PGA be measured per session if it is strictly a per process notion? How do I calculate total PGA size (total for Oracle instance) and total UGA size and UGA's breakdown (what's in PGA and what's in SGA) assuming we have a mix of shared server and dedicated connections at the same time? we are using Oracle 8i

Thank you

Tom, thank you for prompt answer

what about the second part of my question? in the meanwhile I was doing some research on Metalink about "session pga memory" statistics...please confirm my worst suspicion :-) There is no way to get an approximation of the total PGA size for an instance out of Oracle data dictionary? (for 8i that is)

Thank you

Thanks Tom for your insight about PGA & UGA, it's very helpful. However I getting the dreaded 403-Out of process memory error when I run this query below.

SELECT OACCT.ACCOUNT_ID ACCOUNTID, TRIM(OACCT.ACCOUNT_SHORT_NM) ACCOUNTNAME FROM ODR_VW_ACCOUNTS OACCT WHERE ORACLE_ID = 'SNARAYAN' AND OACCT.ACCOUNT_ID NOT IN ('0002272','0002609','0007655','0007661','0007665','011007164100','012100001343','012100001445','012100002655','012100002656','012100002906','012100003138','012100004057','012101262707','012101430135','012101431003','012101431211','012101431222','012101431223','012105012808','012105015208','012105027302','012105089600','012105094300','012105153709','012105165607','012105166009','012105198500','012105217703','012105218301','012105249108','012105292408','012105308008','012105338805','012105374908','012105381801','012105394405','012105399302','012105402904','012105403002','012105403208','012105424203','012105439803','012105439901','012105460306','012105518200','012105632602','012105664104','012105671506','012105875500','012105906102','012105917500','012105931806','012108730448','012108730575','012108731239','012108731544','012108731637','012110000271','012110000352','012110000405','012110000518','012110000522','012110000524','012110000525','012110000526','012110001076','012110001077','012110001078','012110001155','012110001424','012110001516','012110001522','012110001541','012110001855','012110001858','012110001859','012110001874','012110001922','012110001974','012110002015','012110002085','012110002086','012110002293','012110002344','012110002345','012110002412','012110002414','012110002427','012110002428','012110002505','012110002523','012110002565','012110002654','012110002831','012110002848','012110002900','012110002901','012110003084','012110003149','012110003321','012110003322','012110003800','012110003959','012110004072','012110004109','012110004139','012110004175','012110004176','012110004192','012110004224','012110004277','012110004278','01211000430','012110004342','012110004461','012110004783','012110004825','012110004848','012110004980','012110005209','012110005213','012110005257','012110005258','012110005372','012110005373','012110005376','012110005416','012110005421','012110005583','012110005630','012110005723','012110005852','012110005853','012110005875','012110005876','012110144354','012110144362','012110145160','012110145215','012110146103','012110146105','012110146170','012110146216','012110146236','012110146245','012110146255','012110146277','012110146280','012110146284','012110146293','012110146295','012110146301','012110146338','012110146339','012110146344','012110146354','012110146355','012110146364','012110146380','012110146384','012110146386','012110146390','012110146391','012110146399','012110146404','012110146406','012110146407','012110146411','012110146414','012110146415','012110146416','012110146422','012110146423','012110146430','012110146432','012110146438','012110146442','012110146443','012110146444','012110146449','012110146450','012110146452','012110146453','012110146459','012110146463','012110146466','012110146467','012110146468','012110146469','012110146481','012110403521','012110406407','012110406486','012110486209','012110486375','012110486405','012110486428','012110616219','012110616227','012110616251','012110616294','012110616298','012110616309','012110616310','012110616328','012110616332','012110616333','012110616358','012110616359','012110616363','012110616369','012110616378','012110616379','012110616389','012110616394','012110616396','012110616403','012110616424','012110616425','012110616436','012110616439','012110616454','012110616465','012110616470','012110616471','012110616473','012110616476','012110616477','012110616478','012110616483','012110616484','012110616485','012110616508','012110876106','012110876246','012110876272','012110876273','012110876317','012110876385','012110876456','012110876457','012110876458','012110876462','012111262604','012111262622','012111262623','012111430609','012111430616','012111430617','012111430655','012111430656','012111430665','012111430687','012111430692','012111430694','012111430708','012111430709','012111430730','012111430733','012111430818','012111430914','012119000226','012119000227','012119000228','012119000229','012119000230','012119000231','012119000232','012119000233','012119000235','012119000236','012119000238','012119000239','012119000240','012119000241','012119000242','012119000243','012119000244','012119000245','012119000246','012119000247','012119000248','012119000250','012119000251','012119000253','012119000254','012119000255','012119000257','012119000258','012119000259','012119000260','012119000261','012119000262','012119000264','012119000265','012119000266','012119000267','012119000268','012119000270','012119000271','012119000273','012119000274','012119000275','012119000276','012119000277','012119000278','012119000280','012119000281','012119000282','012119000283','012119000284','012119000285','012119000286','012119000287','012119000288','012119000289','012119000290','012119000291','012119000292','012119000293','012119000294','012119000295','012119000297','012119000298','012119000299','012119000300','012119000301','012119000302','012119000303','012119000304','012119000305','012119000306','012119000307','012119000311','012119000312','012119000313','012119000314','012119000315','012119000318','012119000319','012119000320','012119000321','012119000322','012119000323','012119000324','012119000325','012119000326','012119000327','012119000328','012119000329','012119000330','012119000332','012119000333','012119000338','012119000339','012119000340','012119000342','012119000343','012119000344','012119000345','012119000346','012119000347','012119000348','012119000349','012119000351','012119000354','012119000355','012119000356','012119000357','012119000358','012119000361','012119000362','012119000363','012119000365','012119000366','012119000368','012119000369','012119000370','012119000371','012119000372','012119000374','012119000375','012119000376','012119000377','012119000379','012119000380','012119000381','012119000382','012119000383','012119000385','012119000386','012119000387','012119000388','012119000389','012119000390','012119000393','012119000394','012119000395','012119000396','012119000397','012119000398','012119000399','012119000400','012119000402','012119000403','012119000404','012119000405','012119000406','012119000407','012119000408','012119000410','012119000411','012119000412','012119000413','012119000414','012119000415','012119000416','012119000417','012119000418','012119000419','012119000420','012119000421','012119000422','012119000425','012119000426','012119000427','012119000428','012119000429','012119000430','012119000431','012119000432','012119000433','012207105900','012207106200','012207106300','012207106310','012207106400','012207106600','012207106700','012207107000','012207107100','012207107200','020200000601','020200000603','020200007980','020200008001','020200009500','020200009505','020201320000','020201320001','020201320005','020201320010','020201320011','020201320012','020201320022','020201320023','020201320024','020201320035','020201320038','020201320048','020201320050','020201320051','020201320054','020201320060','020201320062','020201320063','020201320064','020201320066','020201320071','020201320072','020201320073','020201320075','020201320076','020201320077','020201320078','020201320080','020201320085','020201320089','020201320090','020201320094','020201320095','020201320096','020201320100','020201320101','020201320102','020201320103','020201320104','020201320105','020201320106','020201320107','020201320109','020201320110','020201320111','020201320112','020201320113','020201320116','020201320117','020201320118','020201320119','020201320122','020201320127','020201320128','020201320129','020201320131','020201320132','020201320137','020201320138','020201320139','020201320141','020201320142','020201320144','020201320145','020201320148','020201320150','020201320155','020201320157','020201320160','020201320190','020201320191','020201320195','020201320196','020201320197','020201320198','020201320199','020201320200','020201320201','020201320202','020201320204','020201320206','020201320207','020201320208','020201320209','020201320210','020201320212','020201320213','020201320214','020201320215','020201320217','020201320219','020201320220','020201320221','020201320224','020201320225','020201320226','020201320227','020201320228','020201320230','020201320231','020201320236','020201320237','020201320241','020201320242','020201320244','020201320245','020201320247','020201320250','020201320251','020201320252','020201320254','020201320255','020201320256','020201320301','020201320303','020201320309','020201320311','020201320313','020201320315','020201320317','020201320319','020201320321','020201320323','020201320325','020201320327','020201320329','020201320331','020201320333','020201320335','020201320336','020201321000','020201335993','020201335997','020201335999','020201336000','020201336001','020201336005','020201336006','020201336007','020201336008','020201420005','020201420025','020201420026','020201435018','020201435025','020201435026','020201435027','020208420035','020208420036','020208420041','020208420042','020208420045','020208420046','020208420049','020208420052','020208420053','020208420055','020208420087','020208420092','020208420093','020208420094','020208420095','020208420096','020208420097','020208420100','020208420101','020208420102','020208420112','020208420201','020208420205','020208420260','020208420261','020208420263','020208420264','020208420268','020208420269','020208420272','020208420273','020208420274','020208420275','020208420280','020208420286','020208420297','020208420299','020208420368','020208420588','020208435000','020208435007','020208435008','020208435011','020208435012','020208520040','020208520047','020208550303','020208550331','020208550350','020208550518','020208550528','020208550540','020208550727','020208550742','020208550803','020208550816','020208550867','020208551094','020208551103','020208551191','020208551295','020208551367','020208551409','020208551717','020208551781','020208551816','020208552484','020208552540','020208552555','020208553033','020208950490','020209049460','020209049461','020300004373','020300004374','020300004553','020300004669','020300004671','020300004672','020300005000','020300005006','020300005110','020300005115','020300005125','020300005682','020300007000','020300007003','020300007004','020300007005','020300007006','020300007007','020300007008','020300007010','020300007020','020300007025','020300007030','020300007683','020300007686','020300007689','020300007690','020300007691','020300007692','020300007693','020300007694','020300007695','020300007701','020300007705','020300007706','020300007707','020300007708','020300007709','020300007710','020300007711','020300007712','020300007713','020300007714','020300007715','020300007716','020300007717','020300007718','020300007719','020300007720','020300007721','020300007722','020300007727','020300007728','020300007729','020300007730','020300007731','020300007732','020300007733','020300007734','020300007735','020300007736','020300007737','020300007738','020300007739','02030000774','020300007740','020300007741','020300007742','020300007743','020300007744','020300007745','020300007746','020300007747','020300007748','020300007749','020300008001','020300008002','020300008003','020300008004','020300008005','020301420010','020307420147','020307420148','020307420150','020307420151','020307420152','020307520001','020307520100','020307520101','020307520145','020307520200','020307520201','020307520202','020307520205','020307520207','020307520208','020307520209','020307520210','020307520211','020307520212','020307520213','020307520214','020307520215','020307520216','020307520217','020307520218','020307520219','020307520220','020307520221','020307520300','020307520301','020307520304','020307520319','020307520325','020307520336','020307520400','020307520401','020307520402','020307520405','020307520408','020307520410','020307520412','020307520425','020307520426','020307520428','020307520429','020307520430','020307520431','020307520432','020307520433','020307520434','020307520435','020307520436','020307520437','020307520438','020307520439','020307520500','020307520502','020307520503','020307520504','020307520508','020307520600','020307520601','020307520602','020307520603','020307520604','020307520605','020307520606','020307520609','020307520611','020307520700','020307520701','020307520704','020307520800','020307520803','020307520804','020307520901','020307521000','020307521001','020307521006','020307521008','020307521009','020307521010','020307521011','020307521012','020307521013','020307521014','020307521015','020307521016','020307521017','020307521018','020307521019','020307521020','020307521102','020307521111','020307521201','020307521401','020307521500','020307521501','020307521502','020307521504','020307521510','020307521511','020307521512','020307521513','020307521514','020307521600','020307521601','020307521602','020307521603','020307521700','020307521701','020307521702','020307521703','020307521704','020307521705','020307521706','020307521707','020307521800','020307521801','020307521803','020307521804','020307521805','020307521900','020307521902','020307522000','020307522001','020307522003','020307522100','020307522101','020307522200','020407420339','020407420340','020407420341','020407420349','020407420351','020407420356','020407420359','020407420360','020407420367','020407420369','020407420370','020407420371','020407420375','020407420376','020407420377','020407420379','020407420380','020407420400','020407420500','020407420501','020407420502','020407420503','020407420504','020407420505','020407420506','020407420507','020407420510','020407420511','020407420512','020407420513','020407420514','020407420515','020407420516','020407420517','020407420518','020407420519','020407420520','020407420521','020407420522','020407420900','020407420901','020407420902','020407420903','020407420904','020407420905','020407420906','020407420907','020407420908','02040742522','020407440001','020407440012','020407440013','020407440021','020407440022','020407440026','020407440027','020407440034','020407440064','020407440068','020407440070','020407440073','020407440099','020407440100','020407440101','020407440106','020407440107','020407440112','020407440113','020407440117','020407440118','020407440119','020407440172','020407440173','020407440174','020407440175','020407440176','020407440177','020407440178','020502120351','020502120395','020502120397','020502120398','020502120409','020502120423','020502120424','020502120425','020502120427','020502120448','020502120450','020502120451','020502120472','020502120474','020502120494','020502120516','020502120521','020502120526','020502120594','020502120609','020502120720','020502120721','020502120722','020502120724','020502120725','020502120727','020502120729','020502120950','020502143503','020502700003','020502720000','020503200706','020503577003','020503660250','020503660350','020503660555','020504000000','020504000001') AND ROWNUM < 2001 ORDER BY 1

The parameters pga_aggregate_target = 1555165824
shared_pool = 419430400
And
SID NAME VALUE

59 session uga memory 341224
59 session uga memory max 1126120
59 session pga memory 1250232
59 session pga memory max 118878752

Can you pl. help me out here ?

Tom,

I can see that your suggestion is perfectly valid for something like IN LIST, that is too long, in a where clause. But, what if, one has a very long SELECT LIST. Say -- I have a very large query, which has a number of computed fields in it with different partitions, groups and such (in analytical functions.) In that case, do you have any suggestions – how to tackle a large query?? Also, is there a hard limit on the size of the SQL QUERY?

Thanks,
Neeraj

Hi Tom,

1)Are the memory parameters sort_area_size and sort_area_retained_size derived
from pga_aggregate_target??

2)Are sort_area_size and sort_area_retained_size
mutually exclusive??

3)Is sort_area_retained_size applicable only
for shared server configuration??

Please do reply.
Bye!

Oracle 9.2.0.6, all sessions running in dedicated server mode on a Solaris 8 box with 8GB RAM. Only 1 Oracle instance on the box, nothing else.

Here are the memory related init.ora parameters

# Cache
db_block_size = 8192
db_cache_size = 2000M
db_32k_cache_size = 200M
db_keep_cache_size = 200M
db_recycle_cache_size = 100M

# Shared pool
shared_pool_size = 500M

# Log buffer
log_buffer = 5242880

# PGA
pga_aggregate_target = 3G
workarea_size_policy = AUTO
sort_area_size = 31457280
hash_area_size = 31457280

Still, at peak usage, I see the following using the 'top' utility

Memory: 8.0G real, 2.3G free, 6.7G swap in use, 535M swap free

and the machine is really struggling, everything is slow.

Can you please help me understand how the memory I have allocated is used?

The SGA would be 2000+200+200+100+500=3GB, right?
The PGA agg target is set to 3GB

So, at any time, no more than 6GB of real memory should be used, right? Well within the 8GB RAM the system has. So why is the system struggling so much?

Thanks

Hi Tom,

This is the first time I'm writing to you but I always find your articles very useful and interesting.

I like to know when PGA size can cause the ORA-4030 error when running a query. As far as I know, when there is not enough memory for sorting. The default temp tablespace will be used. I understand that performance will suffer but an error shouldn't be raised. What kind of operations during the execution of a SQL statement will cause this sort of area?

Further there was another problem I came across with ORA-4030. It was in our application when we were extending a PL/SQL tables (I absolutely understand that it's not appropriate to use a PL/SQL table to store so much data) and our developer rewrote the procedure such that it broke the job into smaller jobs. The problem is now resolved. However, I would really want to know how big can the PL/SQL table grow and therefore we can design our application accordingly (of course, we should avoid using PL/SQL table to store a lot of data). Our application are mainly running on Oracle 9206 and Windows Server 2003. The problem can be model easily:

declare
TYPE t_ntr_cvl is TABLE OF CONT_VIEW_LADDER%ROWTYPE; --change CONT_VIEW_LADDER
to another table in your database, ex, USER_TABLES
l_cvl_rs t_ntr_cvl;
begin
l_cvl_rs := t_ntr_cvl();
for i in 1..150000 loop --increase the upper limit to find your PGA max size
l_cvl_rs.extend;
end loop;
end;

The PGA always reaches the same size when the the block returns the ORA-4030. The MAX PGA can be different on different servers. Do you have any idea what oracle parameters, OS hardware and hardware config can have effect on the PGA we can use. I want to be able to estimate this values. We managed to increase the MAX PGA slightly by decreasing db_block_buffer and increasing PGA_AGGREGATE_TARGET. One thing I like to point out is that our statement fail before reach PGA_AGGREGATE_TARGET is reached.

Many Thanks,
Peter Man

Hi Tom,
Very first time I am asking.
How can we determine the cause of hung end-user sessions within Oracle.
Please help me.

Very very thanks TOM,
I have one mor question.
How can we track the Long-Running Queries ?

Should I use v$session_longops ?

Hi Tom,

First, thank you very much for the prompt reply! I really appreciate it.

I've accessed to all the books, in fact I bought them all (Expert Oracle SIGNATURE EDITION, Effective Oracle by Design plus the latest one) and will check it out shortly. I'm preparing for Oracle 10G OCP exam (with around 1 year Oracle experience) and recall something I read from

Oracle® Database
10g OCP Certification
All-in-One Exam Guide
By John Watson


"If a session needs more PGA than it currently has, the total allocation is already
at the target, and no session has unused PGA that can be reassigned, there are two
possibilities. If the memory requirement is invariable, as for example when a session
requires stack space, then Oracle will allocate it and break the target. This should be
avoided at all costs; it implies that the target is far too low. If the requirement is not
absolute, then Oracle will refuse to allocate more memory, and the session will make
use of temporary space in the session’s temporary tablespace instead. This impacts on
performance, through the extra disk I/O, and should be avoided if possible."

So I guess my PL/SQL table is in the stackspace and therefore it can go over the PGA_AGGREGATE_TARGET. When I did another test again yesterday on my new 10G database on WIN XP, PGA_AGGREGATE_TARGET is 10M and my MAX PGA grows slightly over 1000M. If this is OS and hardware dependent, does it mean that if I change the boot.ini file to allow process to use 3GB of memory. I can possibly increase the max PGA (will try shortly but the most important thing is I can explain to my boss what is really going out and can stop trying different oracle parameter setting) ... ...

Regarding to

"If you allow Oracle to use say 100mb of memory to sort (by setting the
appropriate sort area/pga aggregate target parameters)

AND

allocating 100mb of memory would "fail" (you don't have 100mb left to
allocate!), then you'll get the ora-4030.
"

Does it mean that the error only occur when the server is memory exhausted (running out of physical memory + virtual memory) when PGA has not reached its target.

What if my PGA reaches the target (still memory available from the OS)and there is no free resources in the PGA. Will a new session be able to connect because the memory requirement "is invariable"/will it fail to connect and return the out of process memory error?


Many Thanks,
Peter Man

Very helpful thread. Clarified a lot of questions that I had accumulated over the years.

Thanks Tom,

I just want to clarity one thing with you:

declare
TYPE t_ntr_cvl is TABLE OF CONT_VIEW_LADDER%ROWTYPE; --change CONT_VIEW_LADDER
l_cvl_rs t_ntr_cvl;
begin
l_cvl_rs := t_ntr_cvl();
for i in 1..9999 loop --increase the upper limit to find your PGA max size
l_cvl_rs.extend;
end loop;
end;

this script actually returned ORA-06500: PL/SQL: storage error.

If I modified it slightly,

declare
TYPE t_ntr_cvl is TABLE OF CONT_VIEW_LADDER%ROWTYPE; --change CONT_VIEW_LADDER
l_cvl_rs t_ntr_cvl;
l_cvl_rs2 t_ntr_cvl;
begin
l_cvl_rs := t_ntr_cvl();
for i in 1..9999 loop --increase the upper limit to find your PGA max size
l_cvl_rs.extend;
end loop;
l_cvl_rs2 := l_cvl_rs;
end;

I changed the upperbound of the loop and made the block failed at statement
l_cvl_rs2 := l_cvl_rs;

then I received error:
ORA-04030: out of process memory when trying to
allocate 16396 bytes (koh-kghu call ,pl/sql vc2)

I just want to clarify whether the storage error is also resulting from the OS not able to allocate more memory to Oracle when Oracle request it.

Many Thanks,
Peter

Thanks Tom
Please guide me
How can I convert row to column

>> How can I convert row to column

Turn your head sideways.

> Turn your head sideways.

Do you have to take into account character set semantics to determine if you turn to the left or the right?

- Mark

Wouldn't be more like a 90 degree head tilt rather than a turn? But yes, I think NLS settings would dictate left or right.

Language settings aside, it's a totally robust method known to work on all versions of Oracle, DB2, SQL Server etc..

Hi Tom,
I am agin with my query.
I need your help urgently.
We are going to work on a project migrating SQL database to Oracle

Please guide me how can install the Migration Assistant.
and send me some usefull links for migrating SQL database to Oracle

Hi Tom,

Thanks,
Please guide me which toolkit, I should download
and send me some usefull links for migrating SQL server database to Oracle

Hi Tom,

Thanks,
I creating a sequence with following commandL-
CREATE SEQUENCE supplier_seq
MINVALUE 1
START WITH 1
INCREMENT BY 25
CACHE 20;
Now I want to add one paramemter "ORDER YES" THEN PLEASE guide me ?

Hi Tom,
Thanks for the updates.

I am again with one question.

Please explain me "SQLs embeded in Java codes".

Is it SQLJ ?

We are migration a application ( from microsoft sql server
to oracle )
where we have to rewrite SQLs embeded in Java codes.
Please send me some helpful links for this.

This is very urgent.
Please help me ?
Thanks !

Hi Tom,
Thanks very much.

I have a.xls file and this file contain 3 columns
like col1, col2,col3
And there are 2500 rows in this file.

Now I want to upload this data to a table "TechSub".
TechSub table has three columns.

Now please explain me how can I upload data to this table.

Thanks in advance.

This is really urgent, please explain me with details steps.

Come on guys! Stay on topic.

May be Tom should stop entertaining questions which are not relevant to the thread he is too humble to point it out i guess.

Rahul.

How can I donwload the oracle developer 9i suite

Respected Mr. Tom,

In Oracle9i - Database Performance Tuning Guide and Reference Release 2 (9.2), I have got two following points. It’s my humble request to you please to clarify me.

Page no. 19-2
----------------
“A session can be prevented from migrating to another shared server while parallel execution is active.”

My question - During parallel execution, are sessions migratory? Why do the sessions migrate? In addition, you have told to this site “The UGA normally comes from the SGA to allow migration of sessions across processes. If session migration is disabled, UGA memory will be allocated from the PGA.” I am sorry I could not understand migration of session and also the reason of making disable of migration. I request you earnestly to take your time and write it out clearly as smooth as silk.

Same page
---------------
A session can remain nonmigratable even after a request from the client has been processed, because not all the user information has been stored in the UGA. If a server were to process the request from the client, then the part of the user state that was not stored in the UGA would be inaccessible. To avoid this, individual shared servers often need to remain bound to a user session.

My question –

1> User state or session state (UGA) is in SGA in Shared server. So it is accessible. Why does it consider UGA? May be I am wrong in getting this. Please narrate it.

2> How does it make happen – “individual shared servers often need to remain bound to a user session.”? Again the same confusion – users session is in SGA for shared server. So why bound and how to bound.

It’s my fervent belief, on reading my questions you can easily understand I lack some basic concepts. Please, please, please hit those points and make me lucky.

Regards,
Arindam Mukherjee

Respected Mr. Tom,

Thanks for your caring response. On having your reply, today again I have opened your book - “Effective Oracle by Design”, chapter -3 (Architectural Decision). In that chapter you have described clearly how share servers perform each call to the database – i.e., one SQL (parse, fetch first row, second row and close the result set etc.) undergoes different shared servers during processing. I had confused with the word “migration of session” when I asked you last question. Now the word “migration” is clear to me what Oracle document uses but you did not use. I am grateful to you.
In that chapter, you have written one point as disadvantage of using dedicated server connection – “database link concentration”.
Would you really mind if I ask you again one more silly question what the meaning of “concentration” is in this regard.

Regards,
Arindam Mukherjee

Hi Tom,

I found a very detail, excellent explanation of how SHARED_POOL is managed when a contiguous memory is not available in SHARED_POOL (metalink, Note:146599.1). There is one point which is confusing me though.
It said:

A)
DIAGNOSING AND RESOLVING ORA-04031 ERROR

When any attempt to allocate a large piece of contiguous memory in the shared pool fails Oracle first flushes all objects that are not currently in use from the pool and the resulting free memory chunks are merged. If there is still not a single chunk large enough to satisfy the request the ORA-04031 error is returned. NOTE: These errors can occur on an ASM instance as well. The default shared_pool_size should be sufficient in most environments, but can be increased if you are experiencing ORA-04031 errors.

Then it said:

B)
Shared Pool Fragmentation:

Every time a SQL or PL/SQL statement needs to be executed the parse representation is loaded in the library cache requiring a specific amount of free contiguous space. The first resource where the database scans is the free memory available in the shared pool. Once the free memory is exhausted, the database looks for reusing an already allocated piece not in use. If a chunk with the exact size is not available, the scan continues looking for space based on the following criteria:
- The chunk size is larger than the required size
- The space is contiguous
- The chunk is available (not in use)
Then that chunk is split and the remaining free space is added to the appropriate free space list. When the database is operating in this way for a certain period of time the shared pool structure will be fragmented.

In A, it said it would flush out all the unused objects? Does it mean that it will effectively do a "alter system flush shared pool", which i'm not sure if it's good or bad. Good when it's badly designed system exhausting the memory with queries without bind variable, flushing them is better than managed them with LRU list. Bad when the shared_pool is slightly undersized, flushing the system resulting in lot of hard parsed.

My understanding for B is that the process will traverse some kind of memory structure and attempt to free just enough memory for the contiguous memory wanted, rather than just FLUSHING EVERY UNUSED OBJECTS (as mentioned in A, are they contradicting?)! Also, this question lead me to think how the LRU list(s) is effectively used for SHARED_POOL when memory allocation can be different in size? How are the FREELIST(s) and LRU list(s) structured for SHARED_POOL managment to maximize management efficency? Are there different freelist(s) for memory in different sizes? I recog it could be complicated but can you briefly explain it? When "FLUSH SHARED POOL" is executed, will it free and then merge into them into memory chunk? or the process need contiguous memory merge the memory on demand? Also as describe in 'B', when the process is traverse the list, will it try to merge memory into larger chunk even though the combined chunk cannot meet the required size?

I also read from another ASKTOM post saying that large contiguous memory is no longer needed from Oracle 8i and onward as PL/SQL block can now be allocated into multiple chunks of memory and as a result PINNING objects are also not necessary.

Sorry for asking so many questions. Many Thanks in Advance!!!

Peter

Hi Tom,
Thanks in advance.
Please provide me solution for following query.

We are migrating SQL server to Oracle
When I run the folllowing query then I getting
ORA-01799: a column may not be outer-joined to a subquery
Could you please guide me where I am wrong ?


select et.unit_mark,
--right('00000'+rtrim(et.unit_mark_no),6),
(substr(rtrim('00000' || et.unit_mark_no), (length(rtrim('00000' || et.unit_mark_no)) - 5))) ,
et.unit_ck_digit, ttec.equip_cat_cd,
NVL(etec.equip_cat_cd, bec.equip_cat_cd), epc.equip_cat_product_code_cd,
cra.tp_reporting_area_cd, ch.contract_cd, ch.revision_no, ch.contract_type_name,
cst.tp_cd, cst.tp_name, ch.contract_rsp_cd, ch.contract_rsp_desc, ch.contract_rsp_deductible_amt,
-- ch.contract_rsp_term_of_lease_days,
ch.cont_rsp_term_of_lease_days,
ch.rsp_vendor_tp_cd, es.mr_est_ref_no,
es.revision_no, et.et_incident_dt, et.et_dt, dp.tp_cd, dra.tp_reporting_area_cd,
Lp.pickup_dt, Ld.dropoff_dt, Ld.off_hire_dt, crnc.currency_cd,
case ttec.equip_cat_cd when 'TKCH'
--then isnull(chassis_labor_rate,0)
then nvl(chassis_labor_rate,0)
else tank_labor_rate end
* dtl.hours + dtl.matl_cost as totL,
(case ttec.equip_cat_cd when 'TKCH' then
--isnull(chassis_labor_rate,0)
nvl(chassis_labor_rate,0)
else tank_labor_rate end
* dtl.cust_hours_x100 + dtl.cust_matl_cost_x100) / 100 as bfeL,
dtl.matl_cost - (dtl.RSP_matl_cost + (dtl.cust_matl_cost_x100 / 100))
+ (case ttec.equip_cat_cd when 'TKCH' then
--isnull(chassis_labor_rate,0)
nvl(chassis_labor_rate,0)
else tank_labor_rate end
* dtl.hours)
- ((case ttec.equip_cat_cd when 'TKCH' then
--isnull(chassis_labor_rate,0)
nvl(chassis_labor_rate,0)
else tank_labor_rate end
* dtl.RSP_hours)
+ (case ttec.equip_cat_cd when 'TKCH' then
--isnull(chassis_labor_rate,0)
nvl(chassis_labor_rate,0)
else tank_labor_rate end
* dtl.cust_hours_x100 / 100)) as wndL,
case ttec.equip_cat_cd when 'TKCH' then
--isnull(chassis_labor_rate,0)
nvl(chassis_labor_rate,0)
else tank_labor_rate end
* dtl.RSP_hours + dtl.RSP_matl_cost as rspL,

exc.exchange_rate,
--(case ttec.equip_cat_cd when 'tkch' then isnull(chassis_labor_rate,0) else tank_labor_rate end
(case ttec.equip_cat_cd when 'TKCH' then nvl(chassis_labor_rate,0) else tank_labor_rate end
--* dtl.hours + dtl.matl_cost) * isnull(exc.exchange_rate,0) as tot$,
* dtl.hours + dtl.matl_cost) * nvl(exc.exchange_rate,0) as tot$,
--(case ttec.equip_cat_cd when 'tkch' then isnull(chassis_labor_rate,0) else tank_labor_rate end
(case ttec.equip_cat_cd when 'TKCH' then nvl(chassis_labor_rate,0) else tank_labor_rate end
--* dtl.cust_hours_x100 + dtl.cust_matl_cost_x100) * isnull(exc.exchange_rate,0) / 100 as bfe$,
* dtl.cust_hours_x100 + dtl.cust_matl_cost_x100) * nvl(exc.exchange_rate,0) / 100 as bfe$,
(dtl.matl_cost - (dtl.RSP_matl_cost + (dtl.cust_matl_cost_x100 / 100))
--+ (case ttec.equip_cat_cd when 'tkch' then isnull(chassis_labor_rate,0) else tank_labor_rate end
+ (case ttec.equip_cat_cd when 'TKCH' then nvl(chassis_labor_rate,0) else tank_labor_rate end
* dtl.hours)
-- - ((case ttec.equip_cat_cd when 'tkch' then isnull(chassis_labor_rate,0) else tank_labor_rate end
- ((case ttec.equip_cat_cd when 'TKCH' then nvl(chassis_labor_rate,0) else tank_labor_rate end
* dtl.RSP_hours)
-- + (case ttec.equip_cat_cd when 'tkch' then isnull(chassis_labor_rate,0) else tank_labor_rate end
+ (case ttec.equip_cat_cd when 'TKCH' then nvl(chassis_labor_rate,0) else tank_labor_rate end
--* dtl.cust_hours_x100 / 100))) * isnull(exc.exchange_rate,0) as wnd$,
* dtl.cust_hours_x100 / 100))) * nvl(exc.exchange_rate,0) as wnd$,
--(case ttec.equip_cat_cd when 'tkch' then isnull(chassis_labor_rate,0) else tank_labor_rate end
(case ttec.equip_cat_cd when 'TKCH' then nvl(chassis_labor_rate,0) else tank_labor_rate end

--* dtl.RSP_hours + dtl.RSP_matl_cost) * isnull(exc.exchange_rate,0) as rsp$,
* dtl.RSP_hours + dtl.RSP_matl_cost) * nvl(exc.exchange_rate,0) as rsp$,

--dv.equip_division_cd, isnull((datediff(day, Lp.pickup_dt, Ld.dropoff_dt) + 1),0),
dv.equip_division_cd, nvl((datediff(day, Lp.pickup_dt, Ld.dropoff_dt) + 1),0),
bc.bc_id, bc.bc_begin_dt, bc.bc_end_dt, ch.rsp_vendor_tp_name, et.et_id,
--1, getdate(), null, object_name(@@procid), user_name(), getdate(),
1, sysdate, null, 'P_RPT_085_RSP_CLAIMS', USER, SYSDATE,
null, null, null
--from dbo.billing_cycle bc,
from billing_cycle bc,
--dbo.equip_tracking et
equip_tracking et
left join equip_category_level eqt
on eqt.equip_cat_level_name = 'EQUIPMENT TYPE'
left join equip_category etec -- present if base equip cat is a subtype
on etec.equip_cat_id in
(select etecc.chain_equip_cat_id
from base_equip_category_chain etecc
where etecc.base_equip_cat_id = et.base_equip_cat_id)
and etec.equip_cat_level_id = eqt.equip_cat_level_id,
(select mr_est_id, sum(material_cost) as matl_cost, sum(rsp_ind * material_cost) as RSP_matl_cost,
sum(cust_alloc_pct * material_cost) as cust_matl_cost_x100, sum(hours) as hours,
sum(rsp_ind * hours) as RSP_hours, sum(cust_alloc_pct * hours) as cust_hours_x100
from mr_estimate_dtl d
group by mr_est_id) dtl,
equip_tracking_type ett,
equip_tracking_type ett,
equip_category bec, -- for base eqType (usually also equip type)
equip_category_product_code epc,
base_equip_category_chain ttecc, -- for tank type
equip_category ttec,
equip_category_level ttecl,
equip e,
equip_division dv,
mr_estimate esc, -- estimate at time of cust auth
mr_estimate es -- current estimate revision
left join lease Ld -- lease at time of drop-off
on Ld.equip_id = es.equip_id
and Ld.clearance_id = es.clearance_id
left join lease Lp -- lease at time of pickup (usually same as drop-off)
on Lp.equip_id = es.equip_id
and Lp.booking_id = Ld.booking_id
and Lp.pickup_dt is not null
left join trading_partner dp -- depot(or manuf) - always present
on dp.tp_id = es.depot_tp_id
left join currency u$
on u$.currency_cd = 'USD'
left join currency crnc
--on crnc.currency_id = isnull(dp.tp_currency_id, u$.currency_id)
on crnc.currency_id = NVL(dp.tp_currency_id, u$.currency_id)
left join currency_exchange_rate exc
on exc.from_currency_id = crnc.currency_id
and exc.to_currency_id = u$.currency_id
and exc.effect_dt =
( --select top 1 exx.effect_dt
select exx.effect_dt
from currency_exchange_rate exx
where exx.from_currency_id = crnc.currency_id
and exx.to_currency_id = u$.currency_id
and exx.effect_dt <= es.cust_auth_dt
and rownum=1 -- I add this for similar to top 1
--order by exx.effect_dt desc Anil
) ,
contract_alt_index ai,
contract_history ch,
trading_partner cst,
trading_partner_reporting_area cra,
depot_profile dpp,
trading_partner_reporting_area dra
--where bc.bc_id = @bc
where bc.bc_id = bc
and ett.et_type_cd = 'es_ecustauth'
and et.et_type_id = ett.et_type_id
--and convert(char(8), et.et_dt, 112) between bc.bc_begin_dt and bc.bc_end_dt
and TO_CHAR(et.et_dt) between bc.bc_begin_dt and bc.bc_end_dt
and bec.equip_cat_id = et.base_equip_cat_id
and ttecl.equip_cat_level_name = 'TANK TYPE'
and ttecc.base_equip_cat_id = et.base_equip_cat_id
and ttec.equip_cat_id = ttecc.chain_equip_cat_id
and ttec.equip_cat_level_id = ttecl.equip_cat_level_id
and epc.equip_cat_product_code_id = bec.equip_cat_product_code_id
and e.equip_id = et.equip_id
and dv.equip_division_id = e.equip_division_id
and cst.tp_id = et.last_pos_tp_id
and cra.tp_reporting_area_id = cst.tp_reporting_area_id
and dpp.tp_id = et.cur_pos_tp_id
and dra.tp_reporting_area_id = dp.tp_reporting_area_id
and esc.mr_est_id = et.et_ref_id
and es.mr_est_ref_no = esc.mr_est_ref_no
and es.revision_no =
(select max(esz.revision_no)
from mr_estimate esz
where esz.mr_est_ref_no = esc.mr_est_ref_no)
and dtl.mr_est_id = es.mr_est_id
and ai.contract_alt_index_id = es.contract_alt_index_id
and ch.contract_cd = ai.contract_cd
and ch.revision_no =
--(select isnull(max(chx.revision_no),1)
(select nvl(max(chx.revision_no),1)
from contract_history chx
where chx.contract_cd = ai.contract_cd
--and convert(char(8), chx.cntrl_create_dt, 112) <= convert(char(8), et.et_dt, 112) )
and to_char( chx.cntrl_create_dt) <= to_char( et.et_dt)
and ch.contract_rsp_cd is not null ) ;

Tom,

This is with reference to your response on "Database Link Concentration July 24, 2006" to "Reviewer: Arindam Mukherjee from Kolkata, India".

You told link concentration is due to too many sessions using the same link, I am not sure how we can distribute links across sessions. of course we can create links for users but how to assign a link on session basis? Can you please show us an example?

Thanks,

SQL> l
  1  SELECT name, AVG(value) FROM v$session se, v$sesstat ss, v$statname sn
  2  WHERE ss.sid=se.sid AND sn.statistic# = ss.statistic#
  3  AND sn.name = 'session pga memory'
  4* AND username like '<username>' GROUP BY name
SQL> 4 AND username like 'CF%' group by name
SQL> /

NAME                                                             AVG(VALUE)
---------------------------------------------------------------- ----------
session pga memory                                               232359.176

I currently have around 36 users of CF% connected to the database.  Would this mean I would be using approx. 36*232359=8,364,924 of the SGA?

And if I had 500 users trying to connect it would possibly cause a ORA-4031 error if my Shared pool was on 30M?
 

But if the instance was using PGA_AGGREGATE_TARGET set to 10485760, wouldn't it prevent that from happening and auto-allocation all the private process memory?

Related to the Above, if the system memory was reached, wouldn't I also get 4030's on other instances also. There is a total of 9 databases on that server.

8G Ram
Shared Pool Large Pool Java Pool Buffer TOTAL
67,108,864 8,388,608 13,695,224 89,192,696
134,217,728 16,777,216 33,554,432 118,552,784 303,102,160
100,663,296 16,777,216 33,554,432 118,552,724 269,547,668
92,274,688 8,388,608 33,554,432 13,695,504 147,913,232
100,663,296 16,777,216 33,554,432 68,221,016 219,215,960
83,886,080 16,777,216 33554432 202,176,800 336,394,528
268,435,456 16,777,216 16,777,216 17,889,488 319,879,376
71,303,168 4,194,304 12,582,912 17,889,608 105,969,992
67,108,864 16,777,216 33,554,432 17,889,308 135,329,820
918,552,576 121,634,816 230,686,720 588,562,456 1,926,545,432

9 instances...What is the dba smoking?

Is any part of the SGA used to keep track of the dedicated server sessions? In other words...if I have 500 dedicated server processes, does that effect shared memory at all?

Why do you say what a waste? The box has 4 CPU's and 8G of RAM.

I have the following:

SQL> show parameter pool

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_keep                     string
buffer_pool_recycle                  string
global_context_pool_size             string
java_pool_size                       big integer 0
large_pool_size                      big integer 8388608
olap_page_pool_size                  integer     33554432
shared_pool_reserved_size            big integer 1677721
shared_pool_size                     big integer 33554432
SQL> select pool,sum(bytes) from v$sgastat
  2  group by pool;

POOL        SUM(BYTES)
----------- ----------
large pool     8388608

shared pool   67108864

              13695224


SQL> select 33554432*2 from dual;

33554432*2
----------
  67108864

Is this just a coincidence that the shared pool = parameter shared_pool_size?  What else could cause this? 

What I mean is, my init.ora parameter SHARED_POOL_SIZE = 33554432
As validated by:
SQL> show parameter pool

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_keep                     string
buffer_pool_recycle                  string
global_context_pool_size             string
java_pool_size                       big integer 0
large_pool_size                      big integer 8388608
olap_page_pool_size                  integer     33554432
shared_pool_reserved_size            big integer 1677721
shared_pool_size                     big integer 33554432

And if I run:
SQL> select pool,sum(bytes) from v$sgastat
  2  group by pool;

POOL        SUM(BYTES)
----------- ----------
large pool     8388608

shared pool   67108864

              13695224

I see my shared pool in the SGA is 67108864

Isn't it odd the the SGA shared pool is exactly twice the size of my SHARED_POOL_SIZE init.ora parameter setting?


 

Hi

I just want to contribute that you cannot rely on v$sesstat to estimate memory requirements for PGA.

v$sesstat reports much less memory, the server process actually consumes much more memory at OS level.

In HP-UX for example any new dedicated connection would use around 3MB in Oracle 9.2.0.7. NEW conection i.e just connected. If you query v$sesstat it only reports 0.25MB roughly which is not the real amount of memory used at OS level.

So be careful when estimate memory requirements for a new server!

Regards

Some background info: Last week we run some export of some partions in a very big partitioned tables (~ 65 GB) in batches (2- 6 GB each.)

We're using shared server configuration. So I think that UGA is supposed to reside in SGA. The SGA size was set to 2 GB. I run the following query in our DB and get the following result (partial):

select value, n.name, s.statistic# , sid
from v$sesstat s , v$statname n
where s.statistic# = n.statistic#
and n.name like '%ga memory%'
order by value;

28380872 session pga memory max 26 654
60771460 session pga memory 25 656
60902532 session pga memory max 26 656
4294414756 session uga memory 20 665

By the way, SID corresponds to the pmon.

Now we're getting OS monitoring alerts regarding memory usage because Oracle has used too much memory (>60%).

My questions are:

1. Why is UGA for this session (SID=20) using about 4GB memory which is even bigger than SGA?

2. Is there a way to shrink it?

Thanks.

Hi

I have to estimate pga_aggregate_target size for 4000 concurrent users (only 100 will be active at same time).

I wonder what is the minimum size a single user needs?

From v$sesstat it shows a user uses 0.35MB PGA where 0.14MB is UGA. Does it mean I need at least 4000 * 0.35 for my pga_aggregate_target? Or 4000 * (0.35 - 0.14)?

Is there any v$ view which shows pga free space?

THANKS!!!!

Hi

Unfortunately we were recommended by Oracle Support to drop Shared Server due to performance reasons. We have used Shared Server for two years then after the recommendation we spent 3 months to move to non Shared Server.

Since we moved from Shared Server to dedicated we didnt use pga_aggregate_target before and that is why I am struggling estimating the size for our system.

I have looked several websites such as metalink and otn looking for some sort of generic formula however there is one, stated by metalink, use 20% of system memory, wouldnt be realistic in our system since we have 48GB physical memory.

I have also looked pmap details of a single server process (just idle connections), with pga_aggregate_target set, it shows 0.5mb mmap-file more usage. i.e if previously a single process uses 3mb with sort_area_size now it uses 3.5mb. As far as I know mmap-file is shared memory so I am pretty sure it is getting the extra 0.5mb from pga. If I do this calculation then I would need 2GB pga_aggregate_target just to accomodate 4000 users connections!

Finally since I cant find to a sensible way to perform the estimation that is why I have come here :-(

Tia

Hi

According to our production DBAs the performance got better with dedicated connection. There isnt anything I can blame them....

:-(

The "top" command indicates a couple of Oracle processes are using about 10% of memory for each process, see below:
---------------------------

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND

26578 oracle 16 0 2152m 1.9g 1.9g S 0.0 12.1 33:54.54 oracle

9798 oracle 16 0 2145m 1.9g 1.9g S 0.0 12.1 32:39.81 oracle

26580 oracle 16 0 2145m 1.9g 1.8g S 0.0 11.9 12:27.87 oracle

26533 oracle 16 0 2204m 1.8g 1.8g S 0.3 11.7 63:33.44 oracle

26582 oracle 16 0 2145m 1.7g 1.7g S 0.0 10.8 3:38.35 oracle

26539 oracle 16 0 2145m 1.4g 1.4g S 0.0 8.9 6:09.20 oracle
---------------------------

This database is pretty quiet now, even though I have run a couple of big exports (about 40 GB)last week. In addition, all of these processes are Oracle background processes. So my questions are:

1. Why are Oracle processes are using so much memory even though the database is almost idle? Does these memories indicate the actual memory usage.

2. Is there a way to release those memories?

By the way, we're using RMAN 3 channels to backup database. Does each channel represent one session or process? Will the 3 channels use 1 UGA or 3 UGAs?

Thanks Tom,
Could you send me the script that do the compare the database ?

I want to compare the two DB with query.
Please send me script.

Hi Tom,

Pls. see the output of my DB (92070) RAC with 2 node. 

SQL> select * from v$sga;

NAME                      VALUE
-------------------- ----------
Fixed Size               753216
Variable Size        3707764736
Database Buffers     4294967296
Redo Buffers            2371584

SQL> show parameter buffer
db_block_buffers          integer   0
log_buffer                integer   2097152
use_indirect_data_buffers boolean   FALSE

SQL> show parameter PGS
pga_aggregate_target    big integer 2684354560

SQL> show parameter hash
hash_area_size          integer     131072
hash_join_enabled       boolean     TRUE

I am using Dedicated connection. 
See the output of PGA and UGA...
First I will show you the list of the inactive users.

SQL> select s.sid,s.serial#, s.username, s.machine, p.pid, s.lockwait,s.server
from v$session s, v$process p
where s.status='INACTIVE'
and s.paddr = p.addr;  2    3    4

SID    SERIAL# USERNAME  PID LOCKWAIT  SERVER
20           3 SYS        18           DEDICATED
106       1061 MULDMS     20           DEDICATED
1642       803 NOCL2      22           DEDICATED
2325      1550 SYS        23           DEDICATED
3737      2809 NOCL2      27           DEDICATED
1074      4131 SYS        30           DEDICATED
2895      2595 SYS        31           DEDICATED
2469      2969 SYS        33           DEDICATED
1744      1821 MULDMS     37           DEDICATED
2860       952 SYS        56           DEDICATED

31 rows selected.

----####----
See the PGA and UGA status.
session pga memory       86.96
session pga memory max  243.44
session uga memory       46.36
session uga memory max  160.67

*** Now I will connect with perfstat user in another console. 
04:09:04 SQL> conn perfstat
Enter password:
Connected.
04:09:13 SQL>

See now perfstat user is visible in the inactive list...

04:09:48 SQL> select s.sid,s.serial#, s.username, s.machine, p.pid, s.lockwait,s.server
from v$session s, v$process p
where s.status='INACTIVE'
and s.paddr = p.addr;04:09:50   2  04:09:50   3  04:09:50   4

SID    SERIAL# USERNAME  PID LOCKWAIT  SERVER
20           3 SYS        18           DEDICATED
106       1061 MULDMS     20           DEDICATED
1642       803 NOCL2      22           DEDICATED
2325      1550 SYS        23           DEDICATED
3190      2968 PERFSTAT   26           DEDICATED
3737      2809 NOCL2      27           DEDICATED
1074      4131 SYS        30           DEDICATED
2895      2595 SYS        31           DEDICATED
2469      2969 SYS        33           DEDICATED
1744      1821 MULDMS     37           DEDICATED
2860       952 SYS        56           DEDICATED

32 rows selected.

04:09:54 SQL>

SEE again the PGA and UGA status after connecting with PERFSTAT USER...

SQL> select S.SID, name, ROUND(sum(value/1024/1024),2) "Value - MB"
 from v$statname n,
 v$session s,
 v$sesstat t
 where s.sid=t.sid
 and n.statistic# = t.statistic#
 and s.type = 'USER'
 and s.username is not NULL
 and n.name in ('session pga memory', 'session uga memory')
 and s.username='PERFSTAT'
 group by name
 , s.sid;

SID        NAME             Value - MB
3190 session pga memory        .33
3190 session uga memory        .07


04:10:44 SQL> SELECT PGA_USED_MEM, PGA_ALLOC_MEM, PGA_FREEABLE_MEM, PGA_MAX_MEM FROM
V$PROCESS WHERE PID=26

NAME                       IN MB
session pga memory          87.39
session pga memory max     243.87
session uga memory          46.43
session uga memory max     160.80

PGA_USED_MEM PGA_ALLOC_MEM PGA_FREEABLE_MEM PGA_MAX_MEM
------------ ------------- ---------------- -----------
      271798        401166            65536      401166

04:10:55 SQL>

---- pid is 26 for perfstat user --- 
Now, i will kill the PERFSTAT session.

04:24:24 SQL> ALTER SYSTEM KILL SESSION '3190,2968';

System altered.

04:26:50 SQL>

04:27:03 SQL> SELECT * FROM TAB;
SELECT * FROM TAB
*
ERROR at line 1:
ORA-00028: your session has been killed


04:27:07 SQL> /

NAME                 Value - MB
session pga memory        90.8
session pga memory ma    243.47
session uga memory        49.55
session uga memory max   160.67

Now, my questions 
1) why this MAX value changes once I connect with that user or kill that user... ? 
2) How do we kill that user from OS level also, from the above query... ? 
3) I am seeing so many entries like below. 
SID   SERIAL#  USER    MACHINE              PID  SERVER
----  -------  ------  -------------------  ---  ---------
1737     1902  MULDMS  Appsdms1.maruti.com  220  DEDICATED

i JUST WANT TO KNOW IS THERE ANY WAY TO KILL THESE SESSIONS AUTOMATICALLY IN THE OFF HOURS SO i CAN FREED UP MY PGA MEMORY...? 

Tom, I tried to give complete output but if you still want any clarifications, I would be glad to give. Pls. clear my doubt here. Customer is complaining that there are so many inactive sessions found in auditing.





 

Hi Tom,

Thanks for the prompt response...

Inactive sessions are idle. Correct !!! But sir, those sessions will occupy the PGA / UGA. So my memory will be not utilized for any other thing. Customer is bother because in the OS (AIX topas utility) shows the 100 % HDISK busy and memory also reached to thresh-hold. (In our server we are having 64G and currently consumed 58G (you said that 2-3M per session and we are having 4000 sessions, with dedicated connection plus Shared pool 10G and other memory parameters configured with 5G).

Also explain me that if we kill session from oracle, will it free that from OS also or we need to kill explicitly..?

Regards,
Star Nirav

Cont....

Dear Tom,

Along with that, users are just clicking on the cross button (Top right) rather than doing log-off... they are connecting via JDBC and if it is (form) idle then it disconnects but we have observed that since a long time, users are keep this forms open and those sessions are marked as inactive for 2-3 days. Pls. suggest the best practice...


Thanks a lot..
Star Nirav

Hi tom,

--- extract ---
Followup:
so? memory is paged out by the OS when not used, it is what OS's do for you.

if top did not report near 100% memory usage, then I would be surprised (OS's
use memory for everything, including the UFS cache).

--- end of extract ---

tom, we are having AIX 5.3 ML5 so top command is not there, we are using topas command which gives utilization and all the other required information.

As of now, our HDISKs and CPUs are used completely so I just want to have a look on wait event / queue.

want to calculate PGA/UGA for each session (Active or inactive) so I can check that how much memory is not utilizing or consumed un-necessary.

Thanks

Hey Tom,

Here, OS is AIX 5.2 with ML5 and as per my OS admin, they are complaining that the memory is not getting released which are holding by inactive sessions (Oracle).

They are saying that in oracle, you need to tune some parameters.

They are using topas command and nmon (IBM utility) to verify the memory part.

Can you help me please...?

i DID this whole exercise just to tell you that i need to kill the session manually to release the memory.

Hope, you will understand my scenario and throw some more light which can help me to fix the problem... ?

Regards/Star Nirav

In the response of 9th Dec, you have said that there seems to be have bug in the application.

I just would like to say here that my application is in Java servelets and we are having log-off button. But users are normally click on the cross button to close the screen hence, it is not getting released from Database.

Can you confirm me that If the users are clicking on close button of the application then it should log-out from the system and release the session from the DB... Right ?

We are using dedicated server connection, so it will help us a lot if we configure close button with logout feature.

Regards,
Star Nirav

Tom,

Thank you for sharing your knowledge and expertise. I am learning a lot thru your books and this site.

We are using Oracle 10g release 10.2.0.1.0 on AIX 5.3 in dedicated server mode.


I have a few questions in regards to the previous post:


basically -- pga memory is malloc()'ed in a processes memory space. You can shrink it but it is
just a waste of your time. see dbms_session.free_unused_user_memory.

Why is it a waste? Becuase memory is malloc'ed in a heap and really cannot be shrunk back down
unless the LAST byte of malloc'ed memory isn't used. So, say you have a heap o memory like this:

lo address
xxxxffffffffffffffxx
high address

and xxxx = used stuff, fff = free stuff. You call free_unused_user_memory and we'll tell you
that you are using less PGA however the OS will beg to differ as the process heap cannot be
reduced until that xx stuff at the high address isn't being used anymore.

The reason it is a waste of your time also is because on all modern operating systems -- the fffff
stuff, if needed by another process will be used. It'll page that stuff you are not using out.

So, do not be overly concerned by large PGA's -- look at the resident set size of the process,
that's what counts.


I have ran a small test case using a global collection and monitoring the UGA.


SQL> CREATE OR REPLACE PROCEDURE show_uga
2 IS
3 statname VARCHAR2(50);
4 kbytes NUMBER;
5 BEGIN
6 SELECT stn.name stat_name, sst.value/1024 as kbytes
7 INTO statname, kbytes
8 FROM v$statname stn,
9 v$sesstat sst
10 WHERE stn.statistic# = sst.statistic#
11 AND sst.sid = sys_context('USERENV','SID')
12 AND stn.name LIKE 'session uga memory';
13
14 dbms_output.put_line('');
15 dbms_output.put_line(statname||': '||kbytes||' kb');
16 END;
17 /

Procedure created.

SQL>
SQL> CREATE OR REPLACE PACKAGE global_pkg
2 IS
3 TYPE nt_array_type IS TABLE OF VARCHAR2(2000);
4 g_array nt_array_type := nt_array_type();
5 END;
6 /

Package created.

SQL> SET FEEDBACK OFF
SQL> SET SERVEROUTPUT ON
SQL>
SQL> EXEC show_uga;
session uga memory: 474.0234375 kb
SQL>
SQL> -- Fill up array with strings of 2000 characters
SQL> BEGIN
2 FOR I IN 1..4000
3 LOOP
4 global_pkg.g_array.EXTEND;
5 global_pkg.g_array(I) := RPAD('a',2000,'a');
6 END LOOP;
7 show_uga;
8 END;
9 /
session uga memory: 12674.1484375 kb
SQL>
SQL>
SQL> -- Delete cells
SQL> BEGIN
2 global_pkg.g_array.DELETE;
3 show_uga;
4 END;
5 /
session uga memory: 12674.1484375 kb
SQL>
SQL>
SQL> -- Call to dbms_session.free_unused_user_memory
SQL> BEGIN
2 dbms_session.free_unused_user_memory;
3 show_uga;
4 END;
5 /
session uga memory: 601.7734375 kb


When does UGA memory is freed when using global collections? is it freed when we issue a .DELETE even thought it is not showing when monitoring the UGA?

Or is it freed when we issue the dbms_session.free_unused_user_memory?


Should we bother using dbms_session.free_unused_user_memory or should we expect that the OS will reuse the memory as soon as we issue a .DELETE (even thought it is not showing when monitoring the UGA)

If so how can we monitor that at the OS level.


Our application is running batches in parrallel sessions. The programs are using lots of global collections. We want to make sure we free UGA memory effectively in order for the parrallel programs to reuse that memory thus preventing out of memory errors.


Thank you

We have 10g release2 and using dedicated server.Why uga max is showing up greater then pga max?

SQL> select name,value from v$sysstat
2 where name like '%ga%';

NAME VALUE
---------------------------------------------------------------- ----------
session uga memory 8637694472
session uga memory max 1268774488
session pga memory 101341008
session pga memory max 430824592
calls to kcmgas 526709

SQL> select distinct server from v$session;

SERVER
---------
DEDICATED

Hey Tom, thanks for your explaining about PGA/UGA.

I am not clear about some point of PGA/UGA. Would you please help me to clarify those.

1. While reading this thread, I was curious when Oracle will refuse to allocate memory in PGA and divert it to Temp tablespace. Which requirement is not an absolute requirement ?

"If a session needs more PGA than it currently has, the total allocation is already at the target, and no session has unused PGA that can be reassigned, there are two
possibilities. If the memory requirement is invariable, as for example when a session requires stack space, then Oracle will allocate it and break the target. This should be
avoided at all costs; it implies that the target is far too low. If the requirement is not absolute, then Oracle will refuse to allocate more memory, and the session will make
use of temporary space in the session’s temporary tablespace instead. This impacts on performance, through the extra disk I/O, and should be avoided if possible."

2. I am trying to understand how Query with Order by clause process, so it reuire PGA ? I am thinking like User execute a query with Order by clause, so First of all Oracle evalute it and then try to look into memory for that data, if it's not then it fetch data from disk and put into buffer cache. I am not able to link between that point to "return data to user". What it does after it putting data into buffer cache. How it use sort_area_size/UGA ?

Would you please explain about it or point any document ?

Thanks

~Keyur

Let's say it require 1gb of data needs to be sort and we have only 600 mb of UGA available and colun in orader by clause has unique index on it. What happened once 1gb of data is loaded into Buffer Cache ? How Oracle manuplate data from Buffer Cache and then use UGA (Sort Area, which is 600 mb) to sort data ? Does Oracle transfer all data from Buffer Cache to Sort Area ? Does Oracle just sort based on index and use that result while transfering data to user ?

Thanks

Hello, Tom
We're using 10gR2(10.2.0.1) in Dedicated mode on a PC Server with Windows 2003 Std(3.5G Mem). I didn't turn on the 3G option. So both sga_max_size and sga_target are 700M-900M(at first 900 and then we decreased it) and pga_aggregate_target 500M. But when the burden gets heavey, we get something like

Fri Jul 27 14:58:25 2007
Errors in file c:\oracle\product\10.2.0\admin\gsc\bdump\gsc_smon_7520.trc:
ORA-04030: ¿¿¿¿¿ 123404 ¿¿ (QERHJ hash-joi,kllcqas:kllsltba) ¿¿¿¿¿¿¿

Fri Jul 27 14:58:25 2007
Errors in file c:\oracle\product\10.2.0\admin\gsc\bdump\gsc_smon_7520.trc:
ORA-04030: ¿¿¿¿¿ 123404 ¿¿ (QERHJ hash-joi,kllcqas:kllsltba) ¿¿¿¿¿¿¿

(Sorry, the DB is configured to Chinese Charset)

In the Windows Process Manager, oracle.exe takes up to 1.4G. It's still less than 1.7G.
In gsc_smon_7520.trc we get
Memory (Avail/Total): Ph:1783M/3583M, Ph+PgF:5226M/7012M, VA:47M/2047M
I am wondering why memory is not available. Any suggestion? Thank you in advance.

If Oracle is not able to find a memory then it goes to temp tablespace. If temp tablespace has a space but still you get ora-04030 error. If we increase the temp tablespace, do you think It helps to resolve an issue of ora-04030. Why Oracle give this error even you have space in temp tablespace ?

Thanks

Dear Tom,
We are using Oracle 10g on HP Unix in dedicated server mode and notice memory utilization reaching 99-100% many times.I could even see activation/deactivation,paging swapping occuring.
Physical RAM is 12 GB
My queries are

1)SGA is set to 1500M,PGA to 1200MB.Is it possible that the number of processes(Oracle Sessions) use memory more than total PGA_Aggregate_target memory.If not then how come the memory utilization is reaching 100%.There are other processes running on this server but we dont think they consume that much of memory.
2)In init.ora processes parameter is set to 1500 but in actual,it never exceeds 500 so is it possible that the Oracle instance preallocates memory for 1500 processes at the startup ?

Thank You

Thanks Tom,
I could even see lot of open cursors(should be a part of UGA,I guess) for every Oracle session.There are around 450 concurrrent sessions (Active & Inactive).Each Session holds UGA memory anywhere between 3mb to 7mb depending upon the number of open cursors, it holds.
1)Is there anyway to find the non active open cursors.
I use the following statement to find the active ones
select * from v$sesstat where statistic#=3
Could we clean the non active open cursors.?
2)Could you please ellaborate on the contents of open cursor and what is the difference between open cursor and work area.Does open cursor contain the result set of the sql statement ,I mean the records which are fetched ?

3)Please correct me if I am wrong.
For every SQL statement,Oracle opens up a cursor in UGA of the process and once the sql statement is done,closes it back but the explicit cursors which are opened by the application ,if not closed, remains opens in UGA.This memory is not released until the session is closed.


Thank You

Hi Tom

1, is explict cursor is only kept in PGA 's presistant area or implict cursor and explict cursors both are kept in Presistant area.

2, is PGA's run time area is where cursor is run or resultset 's pointers are cache in run time area and is deallocated after cursor is closed.

3, does plsql stored procedure run in run time area. if not where they run.

regards
Amir Riaz

Dear Tom,
I got your point that the work area is used for sort,hash in memory and that is a part of PGA,outside UGA.

1) What happens when the sort is completed,I mean where is the resultset(fetched rows) kept before sending it to the user process.According to me ,it is in UGA.

2)Is it possible that if you open a cursor explictly in an application(like using cursor c1 is select * from EMP order by ename),the resultset(fetched rows) of the sort or any sql statement with in that cursor definition is stored in the UGA permanently even after the resultset(fetched rows) are sent to the user process until the cursor is closed by the application

Please let me know if I am wrong for the following
3)Cursor is a memory area which is allocated to keep the fetched rows(resultset of the query) before sending to user process.So if we have 50 open cursors per session than 50 memory allocations will be done in UGA to store the fetched rows of 50 different queries.

4)Session Cache Cursors
These cursors are different than that of open cursors.?Session cache cursors refer to the memory area allocation done to keep SQL Query plans and not the fetched rows?

So Session Cache cursors are used before parsing and open cursors are used after fetching ?

As always,Thanks.

1. If the sorted data can fit in the sort workarea (possibly still influenced by sort_area_retain_size), then the result set can be kept there (assuming a sort was the final step of an execution plan). Otherwise it spills into the temporary tablespace until fetched (and/or the cursor is closed).
.
2. In general, if using just SQL, no. One way to store results in the UGA would be to declare a PL/SQL array in a package and store the results into that array (e.g. a BULK COLLECT).
Keep in mind that 11g introduces the concept of a results cache. A server side results cache can be stored in the SGA (not UGA) so separate sessions executing the same SQL statement can see the same query results. But I'll leave to others to elaborate on that feature.
.
3. No. The "cursor" in the UGA keeps track of the state of the cursor for that particular session. It knows what bind values were passed in, the number of rows fetched already, pointer to where to fetch the next row from (for example from sort area (for a sort) or next set of data blocks (for a select * from fullscanned_table)).
.
4.Actually cached cursors are open cursors that just look closed as far as the client application is concerned. When the client reopens / reparses the cursor, it is already open on the server side and pointing to the shared cursor where the execution plan is held (amongst other things like the description of columns that will appear in result set).
.
Corrections by Tom welcome of course.

Hi Tom

thanks for quick response

what i want to say that open_cursor represents total number of the explicit cursor we can open in a certain session. implicit cursors are not stored in the presistent area and i think presistent area is number of open_cursors. because in sqlplus when i execute a statement i get a soft parse for each execution. now remember sqlplus is just a front end environment which displays the resultset and if it has to execute the query a cursor must be created. so even at sqlplus cursor is created but is not kept in presistent area. on the other hand a explicit cursor is kept in the presistent area. I hope my obervations are right.

regards
Amir Riaz

read input
while (input <> exit)
loop
   open cursor
   parse input
   bind input
   execute input
   close input
   read input
end loop
commit

The result from the new 10.2 view v$process_memory

PID SERIAL# CATEGORY ALLOCATED USED MAX_ALLOCATED
---------- ---------- --------------- ---------- ---------- -------------
46 6 SQL 995248 701256 6317712
46 6 PL/SQL 119192 60588 123344
46 6 Other 382472349 389887781
46 6 PL/SQL 119192 66540 123344
46 6 SQL 972996 682680 6317712
46 6 Other 382494601 389887781
46 6 SQL 995256 701264 6317712
46 6 PL/SQL 119192 60588 123344
46 6 Other 382472341 389887781
46 18 SQL 48432 12496 310628
46 18 PL/SQL 65412 18600 65412
46 18 Other 1032913 1032913
46 18 PL/SQL 65412 18600 65412
46 18 SQL 48432 12496 310628
46 18 Other 1032913 1032913
46 18 SQL 48432 12496 310628
46 18 PL/SQL 65412 18600 65412
46 18 Other 1032913 1032913

what does the catogary "other" comprises. This catagory takes an enormous amount of memory

ops$tkyte%ORA10GR2> create or replace package my_pkg
  2  as
  3      type array is table of all_objects%rowtype;
  4      g_data array;
  5  end;
  6  /

Package created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> connect /
Connected.
ops$tkyte%ORA10GR2> select *
  2    from v$process_memory
  3   where  pid = (select pid
  4                   from v$process
  5                  where addr = (select paddr
  6                                  from v$session
  7                                 where sid = (select sid
  8                                                from v$mystat where rownum=1)));

       PID    SERIAL# CATEGORY         ALLOCATED       USED MAX_ALLOCATED
---------- ---------- --------------- ---------- ---------- -------------
        16         15 SQL                   6096       1908        262128
        16         15 PL/SQL               26828      21460         26828
        16         15 Other              1199697                  1199697

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> begin
  2      select * bulk collect into my_pkg.g_data from all_objects;
  3  end;
  4  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select *
  2    from v$process_memory
  3   where  pid = (select pid
  4                   from v$process
  5                  where addr = (select paddr
  6                                  from v$session
  7                                 where sid = (select sid
  8                                                from v$mystat where rownum=1)));

       PID    SERIAL# CATEGORY         ALLOCATED       USED MAX_ALLOCATED
---------- ---------- --------------- ---------- ---------- -------------
        16         15 SQL                  16212       5264       1346928
        16         15 PL/SQL               35040      21600        257556
        16         15 Freeable           1179648          0
        16         15 Other             27395769                 27395769

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> begin
  2      select * bulk collect into my_pkg.g_data
  3        from (select * from all_objects union all select * from all_objects );
  4  end;
  5  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select *
  2    from v$process_memory
  3   where  pid = (select pid
  4                   from v$process
  5                  where addr = (select paddr
  6                                  from v$session
  7                                 where sid = (select sid
  8                                                from v$mystat where rownum=1)));

       PID    SERIAL# CATEGORY         ALLOCATED       USED MAX_ALLOCATED
---------- ---------- --------------- ---------- ---------- -------------
        16         15 SQL                  16212       5264       1524372
        16         15 PL/SQL               35040      21620        257556
        16         15 Freeable           1114112          0
        16         15 Other             57804473                 57804473

What is global cache convert,global cache request time and how we can check it these values other than statspack?

Hi,


The manual says:

"any given time, the total amount of PGA memory available to active work areas on the instance is automatically derived from the parameter PGA_AGGREGATE_TARGET."

So from where a) Cursors and SQL Areas and b) Session Memory , get their share of memory?

Does the follwoing line ( from the manual) has got something to do with this:
"This amount is set to the value of PGA_AGGREGATE_TARGET minus the PGA memory allocated by other components of the system (for example, PGA memory allocated by sessions). "

Thanks and regards.

What I understand is, when a session is created, one user process and one server process is created. A PGA is allocated to the server process.

1. But when I go to task manager->Process tab, I can see the number of process increased is only one.If two process are created (one user process and one server process) then the process count should have been increased by two. Could you please tell me why this is happening?

2.Now I am executing the following queries more that one time from the same user.
The queries returns different values.
I am not executing any other SQL statement or PL/SQL blocks from this session, so there is no question of assigning more or less PGA to the server process.
Could you please tell me why these quries are giving me different values?

SQL> SELECT st.VALUE , USERENV ('SESSIONID'),nm.statistic#,se.SID,se.audsid
  2          FROM SYS.v_$session se, SYS.v_$sesstat st, SYS.v_$statname nm
  3          WHERE se.audsid = USERENV ('SESSIONID')
  4          AND st.statistic# = nm.statistic#
  5          AND se.SID = st.SID
  6          AND nm.NAME = 'session pga memory';

     VALUE USERENV('SESSIONID') STATISTIC#        SID     AUDSID
---------- -------------------- ---------- ---------- ----------
   2892488              9634103         20        185    9634103

SQL> /

     VALUE USERENV('SESSIONID') STATISTIC#        SID     AUDSID
---------- -------------------- ---------- ---------- ----------
   2892488              9634103         20        185    9634103

SQL> select VALUE from sys.v_$sesstat where SID = 185 and statistic# = 20;

     VALUE
----------
    402120

SQL> /

     VALUE
----------
    402120

SQL> SELECT st.VALUE , USERENV ('SESSIONID'),nm.statistic#,se.SID,se.audsid
  2          FROM SYS.v_$session se, SYS.v_$sesstat st, SYS.v_$statname nm
  3          WHERE se.audsid = USERENV ('SESSIONID')
  4          AND st.statistic# = nm.statistic#
  5          AND se.SID = st.SID
  6          AND nm.NAME = 'session pga memory';

     VALUE USERENV('SESSIONID') STATISTIC#        SID     AUDSID
---------- -------------------- ---------- ---------- ----------
   2892488              9634103         20        185    9634103

SQL>

ops$tkyte%ORA10GR2> select sid, serial#, paddr from v$session where username = user;

       SID    SERIAL# PADDR
---------- ---------- --------
       288        105 3FF19E04

ops$tkyte%ORA10GR2> set autotrace on statistics;
ops$tkyte%ORA10GR2> select sid, serial#, paddr from v$session where username = user;

       SID    SERIAL# PADDR
---------- ---------- --------
       279        733 3FF19E04
       288        105 3FF19E04


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        572  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

ops$tkyte%ORA10GR2> set autotrace off
ops$tkyte%ORA10GR2> select sid, serial#, paddr from v$session where username = user;

       SID    SERIAL# PADDR
---------- ---------- --------
       288        105 3FF19E04

Hi Tom
Thanks for the response.
I have some doubts, that I am narrating bellow. Please clear my concept.

1.
As far as dedicated server is concerned
there are 3 items

1. client
2. session
3. process

My perception is as following:-

when I open SQLplus then actually I am opening a client. A session is opened for this client and two new processes, one 'client' process & one 'server' process is created. A PGA is assigned to the server process.

If the same client opens another session (like your example: autotrace) the same two process (same client and server process) will work for this session too. No new processess will be created.


As you said in Windows, when a client is opened, only 'Client Process (in window it is thread) is created not the server thread. Then could you please tell me how a PGA corresponding to a new client is assigned in windows environment (As we know PGA is assigned to a Server process whenever a new server process is created for a new client)?


Please correcct me if my perceptions are wrong.


2.
I am not clear about the following queries. In my perception these two queries are same and returns the amount of PGA free for the session where these queries are execuitng. If something else please tell me.

SELECT st.VALUE , USERENV ('SESSIONID'),nm.statistic#,se.SID,se.audsid
2 FROM SYS.v_$session se, SYS.v_$sesstat st, SYS.v_$statname nm
3 WHERE se.audsid = USERENV ('SESSIONID')
4 AND st.statistic# = nm.statistic#
5 AND se.SID = st.SID
6 AND nm.NAME = 'session pga memory';


select VALUE from sys.v_$sesstat where SID = 185 and statistic# = 20

Hi Tom,
I believe we are not connecting via shared servers.

but statspack report

Instance Activity

Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------

session pga memory 13,345,208 3,709.1 8.0
session pga memory max 155,392,480 43,188.6 93.4
session uga memory 133,145,006,384 37,005,282.5 79,993.2
session uga memory max 558,285,328 155,165.5 335.4

How is that uga is more that pga here . please correct me if i am wrong.

Thank You

Just at the moment when I finished reading the architecture chater of your book , this flood of questions on the PGA/UGA seesm to drawf my understanding of oracle back to square 1.Wish you would suggest some pointers on some really solid grounding principles in Oracle.

Hi tom 
Pga is not set in my database.Oracle 9iR2.
Is there any performace affect, if I set the pga?
Forexample sometimes. I hit unable to extent temp, or stuff like that,is there any impact on them?




SQL> show parameter pga

NAME TYPE VALUE
--------------------------------------------------------------------------------
-----------
--------------------------------------------------------------------------------
pga_aggregate_target big integer 0


SQL> select name,value,unit from v$pgastat where name like'total PGA%';

NAME VALUE
--------------------------------------------------------------------------------
----------
UNIT

--------------------------------------------------------------------------------
total PGA inuse 29602816
bytes

total PGA allocated 69943296
bytes

thanks tom.
How can I obtain your book? I live in New Zealand.
One more thing? In your opinion,Is there any performace gain, if I set pga ?

Respected Mr Tom;

1-)
When I install a database, whatever version it is (8i,9i), pga must be allocated.It cannnot be 0.
If I dont use automatic pga management (pga_aggregate_target=0), how can increase the total pga size?

2-)
Assume I dont use automatic pga management (pga_aggregate_target=0)
If my pga size is 1gb and sort_area_size is 1M.
If there is one user,the user will use 1M to sort.
If there are two users:
Does each of them use again 1M
or
0.5M each?

I mean Is the value in sort_area_size "per user" or "total value"?


3-)
Assume I use automatic pga management and my pga size is 1gb

If there is space available in pga, one user can may even use 900m to sort.

If thers are two users and if the available free space is 500M in pga.
Each uses 250M.

Is that correct Recpected Mr Tom?

The answers of the these questions will clear my doubts.I appreciate your enlightened answer..

Respected Mr Tom,
Thanks for your enlightining answers.I am clear now.

1-)
Assume I am not using automatic pga management;
I set the sort_area_size to 100mb.
You mentioned that the sort area size is per STATEMENT.
so,if the user opens 10 queries 1gb of memory will be used.
and
as u said if 10 users open 1 query again 1gb memory is used.
What kind of error message is returned if there is insufficient memory?


2-)
Now I am using automatic pga management
and I set the pga_aggregate_target to 100M.

If a user opens 1 query it can use upto 100M of sort.
If a user open 2 query each uses 50M for sort.
If five user opens queries at the same time, each will use 20M for sort.

Is that correct Respected Mr Tom?
If this it is the case there is no benefit of using automatic pga management unless we size pga_aggregate_target properly.

Because according to my examples,
(pga_aggregate_target=100M) or (sort_area_size=100M)
if there are two users :
In automatic pga each will use 50M.
and
In manual pga,each will use 100Mb for sort which is better.

Am I wrong Respected Mr Tom?

Thanks for your enlightening explanations Respected Mr Tom,
I will try to obtain your enlightining book as soon as possible,because I couldnt find any good resource in the internet.

Is there any relation with "unable to extent temp segment" error message with not sizing pga properly?

Hi Tom,

Assume I had PGA_AGGREGATE_TARGET = 300m (dedicated servermode)

My Physical memory available at time of instance start is 500M.

But physical memory available When I started my DB and similar my session started spawning there only 100m (due to some other applications running) but sessions requires 200m.

As per my knowledge, pga dynamically allocates deallocates. Correct me if iam wrong.

In this case, can we encounter 4030 error and sessions disconnected due to lack of memory.

-Thanks
Suresh

Hi,

Thanks tom,

Can you please explain what is the below suggests.

I know the below the process memory attached to each bg process. But what is the shmid that is associated with is it my sga that not fit the memory comes to /dev/shm. but my shared segment id does not match with it.



sureshp->


sureshp-> pmap `pgrep -f lgwr`
20000000 208896K rwxs- [ shmid=0xd18012 ]
b6f74000 1088K rwx-- /dev/zero
b7084000 1088K rwx-- /dev/zero
b7194000 1920K ----- /dev/zero
b7374000 1088K rwx-- /dev/zero
b7484000 1088K rwx-- /dev/zero
b7594000 1088K rwx-- /dev/zero
b76a4000 832K ----- /dev/zero
b7774000 1088K rwx-- /dev/zero
b7884000 960K ----- /dev/zero
b7974000 1088K rwx-- /dev/zero
b7a84000 960K ----- /dev/zero
b7b74000 1088K rwx-- /dev/zero
b7c84000 1088K rwx-- /dev/zero
b7d94000 1088K rwx-- /dev/zero
b7ea4000 48K rwx-- /dev/zero
b7eb0000 64K rwx-- /dev/zero
b7ec0000 64K rwx-- /dev/zero
b7ed0000 64K rwx-- /dev/zero
b7ee0000 128K rwx-- /dev/zero
b7f00000 144K rwx-- /dev/zero
bfc0e000 84K rwx-- [ stack ]
total 317928K
sureshp->

sureshp-> ipcs -m

------ Shared Memory Segments --------
key shmid owner perms bytes nattch status
0xbe3bb918 13729810 oracle 640 213909504 19

ops$tkyte%ORA11GR1> select to_char( 13729810, 'xxxxxxxxx' ) from dual;

TO_CHAR(13
----------
    d18012

My recent projects, the need for statistical oracle database-related performance indicators, unable to determine the read and write
shared memory hit rate, please help.

In shared server config, where will the cursor state and state space be stored? In the shared pool along with user session or any other pool?

Sorry for my typo mistake in above ques. Please read my ques as follow:

In shred server, where are the cursor state and stack space stored? Are they in Shared pool along with session data or in Large pool?
I got following ans from my collegue.
User session data and cursor state are in Large pool and stack space is stored outside SGA.
Thanks.

Sir
I have just read your enlightining examples about PGA and UGA in your book at chapter 4.
I dont understand one thing.Regarding the output of your watch_stat script.
It shows session pga memory and session pga memory max.
I dont understand why session pga memory doesnt show 0.
Since you mentioned that pga is not preallocated and allocated as "needed basis".
so,Why doesnt it show 0 in ur examples?

Thanks Sir.
Sir in your book you also mentioned that,manuel pga management may prefered for batch jobs.(when they are the only activities in the database).
Considering Automatic pga management;since they will be only activity in the database,
they will use majority of the pga_aggreagate_target, which is good.
So why should we choose manual pga managament for this issue?

Hi Sir,
If we exceed the pga_aggregate_target limit, does the sort operations done on disk? or out of memory error message is returned?

Sir, considering auto pga,
If we are the only game in the town, how much workarea we can get from pga_aggregate_target?
Can you give any value such as %90 of the pga_aggregate_target, or whatever..
What is your estimation in this issue?

Scott@10G> SELECT PR.PROGRAM,
  2    PGA_USED_MEM,
  3    PGA_ALLOC_MEM,
  4    PGA_MAX_MEM
  5  FROM  V$PROCESS PR,
  6      V$SESSION SESS
  7  WHERE PR.ADDR = SESS.PADDR
  8  AND SESS.SID  = USERENV('SID')
  9  /

PROGRAM                  PGA_USED_MEM PGA_ALLOC_MEM PGA_MAX_MEM
------------------------ ------------ ------------- -----------
ORACLE.EXE (SHAD)         826449       1165933     1165933

So the PGA Memory allocated for my Server Process is 1165933 Bytes and Utilized memory is 826449 Bytes and Maximum Memory that can be Allocated is 1165933 Bytes.
Is that Correct Tom?

Dear Sir,
i'm using the following query to find out resource usage by a set of people


select * from (select b.name,sum(a.value) val from
v$sesstat a,v$statname b,v$session c
where a.statistic#=b.statistic#
and b.name in ('CPU used by this session','logons current','opened cursors current','parse time cpu','parse time elapsed',
'session pga memory','session pga memory max','session uga memory','session uga memory max','consistent gets','db block gets','physical reads')
and a.sid=c.sid
and username is not null and username <> 'TEST' and machine <> 'SP03CL21'
group by b.name) where val > 0

is my approch OK? Can you please guide me in this regard

thank you

Sir

thank you for the reply.
i want to know is the memory usage ,cpu usage and IO usage by these users and what % they are using against the total memory and cpu and IO.

Hi Tom,
In one of the above follow-ups , while explaining about the implicit and explicit cursors, you gave one example :
select count(*) into :n from t where x = :a
there is a slight difference between the two ways -
open,bind,execute and fetch for IMPLICIT
parse,bind,open and fetch for EXPLICIT -

Could you please explain why the order of the actions is different ?
Many thanks for your time .

Regards
Bix

Many thanks Tom for your reply .I have few doubts about the 'cursor' ?

1. I understood that the 'cursor' is a 'pointer' at client side which points to the 'state/status' of the SQL statement being executed in the server process , Is it correct ? here the SQL statement can be issued by any client such as PL/SQL or SQLPlus. this cursor ( pointer at client side) will point to the UGA which contains some pointers to keep track of 'fetching' rows etc ..

2. Explict cursor is 'explicit' (we open/fecth/close explicitly in the program) - other than explicit cursor- every SQL statement issued by the client to server process is an 'implicit' cursor , the client can be PL/SQL or SQLplus . Is it correct ?

3.'Explict cursor'is a program feature of PL/SQL ONLY - is it correct ?

4. select * from emp -(issued from sqlplus ) this is also an 'implicit' cursor . - is it correct ?

So finally - Server process sees every SQL statement as a 'cursor' issued by any client - well - we can explicity define a cursor in PL/SQL only

Can you please correct if my understanding is wrong ?

Once again many thanks for your help

Thanks
Bix

Many thanks Tom for the quick reply - thanks a million ..
the below statement clears most of my doubts
"An implicit cursor is just the LANGUAGE doing things for you"

Sorry to bother you , but i want to know what exactly happens when a statemnet such as 'select x into y from emp' in written in a pl/sql block ?

What PL/SQL engine will do ?
when SQL engine will come to picture ?
Is parsing/binding done by SQL engine or Pl/SQL engine ?
You mentioned that 'parse,binding etc' is done by the LANGUAGE internally FOR implicit curoser, Does it happen at 2 levels (language level and Database level for SQL )

Is it the below case:

select x into y from emp where z=:id

1.PL/SQL understands that it is something that needs to be sent to SQL Engine.
2.PL/SQL will parse it,bind it and send the SQL to SQL engine
3.SQL engine will again do it a hard parse / soft parse ..
as per the normal 'statement processsing ' process.

Whether it is implict curose /explict - the SQL engine will always receives a simple SQL statement - right ?
Server does not know whether it is 'implicit' or 'explicit' ?

Sorry for all my questions - but I want to know EXACTLY what happens ? i have read the concepts guide and also am very hardcode fan of your books (the way you explain the things is awesome) but did not get COMPLETE AND CLEAR concept how the things work ..

Once again many thanks
Bix

Onace again thanks Tom, your help is much appreciated for explaining this . One final doubt mostly ,

you mentioned 5-6 steps (open cursor,parse,bind etc )

It means - PL/SQL will ask SQL engine to parse it - right ?
Here SQL engine will deicde whether it can be a 'soft parse' or 'hard parse' ?

We have a chapter in concepts guide -'Statement processing' - the SQL statements are processed by the SQL engine , all the steps mentioned in this chapter are done by SQL engine when asked by the PL/SQL engine - am i correct ?

Can I assume Server process/SQL engine - both do the work for PL/SQL ?

Sorry to bother you again and again ..

many thanks
Bix

Hi Tom,

When a client application (let’ say -VB) makes a call to a pl/sql stored procedure which contains a statement like this:

select x into y from emp where z=:i

1. User process is created on client side (lets say both client/server on different m/cs)
2. Server process is created on Oracle server side (PGA/UGA)
3. Server process will copy the Pl/sql block from database to SGA (if it does not already exist in sga )
4. Server process will invoke Pl/sql engine
5. Plsql engine will implicitly open a cursor (pointer) for the above select statement (???- will the client application open cursor (or) will Pl/sql ??)
6. Pl/sql will store all the variables(such as y,z,i) in a private sql area (UGA) and send the above select statement to SQL engine and ask to parse the statement
7. SQL engine will parse the statement – as part of processing , it will put the above select statement in shared pool (if it is not already present )
8. SQL engine will bind the values in the select statement
9. then SQL engine will call CBO to get the optimized plan
10. once it gets the best plan from CBO- it will start executing the statement
11. If SQL engine does not find the DB blocks in SGA, it will ask Server process to get them from Database
12. ‘Locking’ /’redo/undo’ stuff goes here – statement is executed here
13. Cursor points to the row (result set) on the block to be fetched
14. ‘Fetch ‘ is a call made by the client to Server process – Server process will fetch the value (pointed by the cursor ) into the variable in UGA (I believe unless you make a ‘fetch’ call – the SQL will be executed actually – am I correct here ?)
15. Pl/sql will process the value in the variable
16. pl/sql will close the cursor (pretends as if closing but caches it )

Can you please correct wherever it is wrong in the above points?

I have some idea how Oracle database works and how PL/sql works – but not able to get clear idea how both work together (ex: Server process, SQL engine,User process,Pl/sql engine – how do all these work together which calls which one – what is the order of their calls etc )

B) Second doubt :

If I say
C is a cursor
Open C
Close C
No ‘fetch’ – will the sql in the cursor will be executed here ? of course we don’t make any physical/logical i/os as there is no ‘Fetch’

C) We don’t store the result set any where in the database/instance- we will directlt fetch the values from ‘DB Blocks’ to variable/array etc .. – right ?


Regards
Bix

Thanks a million Tom for your reply ,
yes, I had bought your book already - gone through the chapters about statement processing ,

I may wrong but what I understood is that you mentioned in your book that - 'Once a SQL statement is submitted to Oracle - what happens in the instance/database ? how it is processed / executed in side instance/database all the stuff ..' but along with this- i am interested to know how server process acts and responds to client with the result sets etc .. ?

I have read few chapters on concepts guide - I could see almost always - 'Oracle will do this, do that ...'
but i was intersted to know which part/component of Oracle will do this? Is it server process /SQL engine etc?? ...
that's why all my questions - thanks once again




Regards
Bix