pga_aggregate_target & workarea_size_policy and Some Hidden Parameters

With "workarea_size_policy='AUTO'" oracle internally manages amount memory needed to sort and other task.
But using below hidden parameters oracle puts limit for the amount of memory for a single process, so that, a single process cannot eat up all tha PGA.

"_smm_max_size" maximum work area size in auto mode (serial)
"_pga_max_size" Maximum size of the PGA memory for one process

Value of above parameter depends on the size of "pga_aggregate_target".
The default value varies for versions and for value of "pga_aggregate_target" (or "memory_target" for 11g, if AMM in enabled).

We can use below queries to find values for above parameters:

1. Find the current value for "_smm_max_size"
select a.ksppinm name, b.ksppstvl value, a.ksppdesc description
from sys.x$ksppi a, sys.x$ksppcv b
where a.indx = b.indx
and a.ksppinm = '_smm_max_size';

2. Find the current value for "_pga_max_size"
select a.ksppinm name, b.ksppstvl value, a.ksppdesc description
from sys.x$ksppi a, sys.x$ksppcv b
where a.indx = b.indx
and a.ksppinm = '_pga_max_size';

3. Calculate value of "_smm_max_size" & "_pga_max_size" from "pga_aggregate_target"
select a.name, a.value, a.DESCRIPTION,a.value*.20 "_pga_max_size",(a.value*.20)*.5/1024 "_smm_max_size"
from v$parameter a
where name = 'pga_aggregate_target';

I have tested above calculation with default values for 10.2.0.1 & 11.2.0.1. (I don't know the accurate calculation It may vary).
Below is the result:

Oracle version : 10.2.0.1 :
pga_aggregate_target=2447M
_smm_max_size 250570
_pga_max_size 513167360

Calculated value from Query 3:
_pga_max_size _smm_max_size
------------- -------------
513173094 250572.8

Oracle version : 11.2.0.1 :

pga_smm_max_size=419430
_smm_max_size 419430
_pga_max_size 858992640

Calculated value from Query 3:
_pga_max_size _smm_max_size
------------- -------------
858993459.2 419430.4


For most of the systems default configuration works fine.
Under some very special cases if anybody needs more memory (to avoid disk sort), below can be used at session level:

alter session set workarea_size_policy = 'MANUAL';
alter session set sort_area_size=512000;

But be careful, if too many sessions use this and also in case of parallel query, each slave/process consumes memory up to SORT_AREA_SIZE for each sort.

Below links are very useful regarding this issue:
http://christianbilien.wordpress.com/2007/05/01/two-useful-hidden-parameters-_smm_max_size-and-_pga_max-size/
http://hoopercharles.wordpress.com/2009/12/20/sort_area_size-affecting-sorts-to-disk-with-pga_aggregate_target-set/

ORA-14450: attempt to access a transactional temp table already in use

While DDL(alter table ....) on a Global Temporary Table I encountered "ORA-14450".
Then I searched in V$LOCKED_OBJECTS but didn't find anything there(If I am not wrong,V$LOCKED_OBJECTS contains only DML related locks ). The I fired below query to find the session locking my TEMP Table:

SELECT s.INST_ID,
o.object_name,
s.sid,
s.STATUS,
s.serial#,
s.username,
s.osuser,
s.machine,
'alter system kill session ''' || to_char(s.sid) || ',' ||
to_char(s.serial#) || ''';' ks
FROM dba_objects o, gv$lock a, gv$session s
WHERE o.object_name = ''
AND o.owner = ''
AND a.id1 = o.object_id
AND a.type = 'TO'
AND a.sid = s.sid;

For the description of type column we can user below query:

select type,name,description from v$lock_type where type='TO';

Thanks to http://www.oracleoverflow.com/questions/266/alter-temporary-table-throws-ora-14450

Session Trace

Follow steps below to trace the current session or other running session:

1. Find the session info[for current session use v$mystat to get current session info]:
select a.sid, a.serial#, b.spid, a.username
from v$session a, v$process b
where a.username = '[USERNAME]'
and a.sid = [session id]
and a.PADDR = b.ADDR;

2. Start the trace[in this case event = 10046,trace level=12], trace file will be created at user_dump_dest as [DBNAME]_ora_[SPID].trc :
exec dbms_system.set_ev(&sid,&serial,10046,12,'');

3. stop the trace :
exec dbms_system.set_ev(&sid,&serial,10046,0,'');

4. convert the trace to readable format [ommit sys=no is you want sys queries in output file] :
tkprof [trace file name] [output file name] sys=no

**Trace Levels:
0 - No trace. Like switching sql_trace off.
2 - The equivalent of regular sql_trace.
4 - The same as 2, but with the addition of bind variable values.
8 - The same as 2, but with the addition of wait events.
12 - The same as 2, but with both bind variable values and wait events.

**Event Numbers:
10046: enable SQL statement timing
10053: CBO Enable optimizer trace

List of Trace Events:
https://netfiles.uiuc.edu/jstrode/www/oraparm/events.html
http://priitp.wordpress.com/oracle-11g-trace-events/

More about Trace:
http://www.oracle-base.com/articles/10g/SQLTrace10046TrcsessAndTkprof10g.php

spool without trailing white spaces

Below set of sample code will generate a CSV report and the output file will not have any trailing extra white spaces:

sqlplus –s user@DBNAME
set echo off
set feedback off
set pagesize 0
set linesize 2000
set heading off
set trimspool on
spool test.csv
select 'check_list,status,ins_date' from dual;
select t.check_list||','||t.status||','||to_char(t.ins_date,'dd-Mon-yy hh24:mi:ss') from my_tab t;
spool off
exit

Create DB Link in Other Schema

I have tried in several ways but failed and succeed using following set of sql (Thanks to Ritesh Raj Singh) :

SQL> grant create database link to schemauser ;

SQL> create or replace procedure schemauser.zcreatedblink as
begin

execute immediate 'create database link DBLINK_NAME connect to TARGET_USER identified by TARGET_PASSWORD using ''TARGET_TNS''';

end;
/

SQL> exec schemauser.zcreatedblink

SQL> revoke create database link from schemauser ;

SQL> drop procedure schemauser.createdblink;

Please inform if anybody has better idea.

Sql loader: Control Commit Interval

Analyzing the ADDM report we have found below:

Wait class "Commit" was consuming significant database time

It was a DB where an application continuously loads data using sql loader (using default parameters). As a result, huge amount of commits are always going on.

Now we want to control the number of rows between commits.

We changed the sqlldr script by appending ROWS=1000 and found below on sql loader log:

Space allocated for bind array: 495360 bytes(40 rows)

So, commit is occurring after insertion of each 40 rows which uses BIND size of 495360 bytes.

Now we have to calculate the appropriate size for BIND. We calculated as [(495360/40)*1000] bytes.

So, we have to use a new parameter BINDSIZE and append below in the old sqlldr command:

BINDSIZE =12384000 rows=1000

But we have to control BINDSIZE so that it doesn't exceed the max permitted value for READSIZE (read buffer size) for the platform (for 9i database it is 20MB or 20971520 bytes)[http://download.oracle.com/docs/cd/B10501_01/server.920/a96652/ch04.htm#1004855]

Now our sql loader logfile show as below:

Space allocated for bind array: 12384000 bytes(1000 rows)

Read buffer bytes:12384000

So, we are getting exactly 1000 rows interval for commits.

[Thanks to ssunda6: http://www.orafaq.com/forum/t/84903/2/]

Creating a User Defined Context Variable

Creating a user Defined Context Variable

SQL> conn / as sysdba;
SQL> CREATE OR REPLACE CONTEXT my_app_ctx using myuser.ctx_pkg;

Context created

SQL> select * from dba_context where namespace='MY_APP_CTX';

NAMESPACE SCHEMA PACKAGE TYPE
----------- ------- ------- ----------------
MY_APP_CTX MYUSER CTX_PKG ACCESSED LOCALLY

SQL> disc

SQL> conn myuser/password@DBNAME


SQL> CREATE OR REPLACE PACKAGE ctx_pkg IS
PROCEDURE set_ctx(p_name VARCHAR2, p_value VARCHAR2);
END;
/
Package created

SQL> CREATE OR REPLACE PACKAGE BODY ctx_pkg IS
PROCEDURE set_ctx(p_name VARCHAR2, p_value VARCHAR2) IS
BEGIN
dbms_session.set_context('my_app_ctx',p_name,p_value);
END set_ctx;
END;
/
Package body created

SQL> exec ctx_pkg.set_ctx(p_name => 'VAR1',p_value => 'VAL1');

PL/SQL procedure successfully completed

SQL> SELECT sys_context('my_app_ctx', 'VAR1') var1 FROM dual;

VAR1
------------
VAL1