Friday, December 10, 2010

Basics of PL/SQL Result Cache feature in 11g

In which case it can be useful?

Imagine you are selecting from a list of 200,000 orders related to 100 products references and you want to use a standard product reference description from a function get_product_desc(p_prod_code) with :
SELECT ord_ref,get_product_desc(prod_code) as prod_std_ref,ord_qty,ord_price FROM ORDERS_LINES.

Without RESULT CACHE feature, you will have 200,000 executions of the get_product_desc function.
With RESULT_CACHE enabled, the function will be executed only 100 times, results being kept in the result cache memory.


Related system PARAMETERS:

RESULT_CACHE_MAX_SIZE initialization parameter, by default set to a small size of the shared pool.
You can change the size with the alter system command:
Ex: ALTER SYSTEM SET result_cache_max_size = 4M SCOPE = MEMORY;

RESULT_CACHE_MODE initialization parameter, by default MANUAL, can be set to FORCE.


Related Package: DBMS_RESULT_CACHE

Ex: DBMS_RESULT_CACHE.FLUSH to clear cache. Useful when doing stats etc...
       DBMS_RESULT_CACHE.MEMORY_REPORT to dispay cache memory info.


QUERY PLAN

When Result cache is used we have new information in the query execution plan.


EXAMPLE

CREATE FUNCTION get_product_desc (
                  p_prod_code IN VARCHAR2 )
                  RETURN VARCHAR2 RESULT_CACHE IS

        prodname VARCHAR2(200);

BEGIN

         SELECT substr(prod_ref||’- ‘||prod_desc,1,200)
         INTO prodname
         FROM Products WHERE prod_code=p_prod_code;

         RETURN prodname;

END get_product_desc;


V$ VIEWS

V$RESULT_CACHE_OBJECTS.TYPE 
V$RESULT_CACHE_DEPENDENCY 
V$RESULT_CACHE_STATISTICS

Ex:
SELECT name, value FROM   v$result_cache_statistics
WHERE  name IN ('Create Count Success','Find Count');


CACHE data dependencies

To avoid cache inconsistency, in 11gR1 the "RELIES_ON" clause can be used, in 11gR2 no need for it, all done automagically. If clause is found it will be just ignored.
Good to note that in 11gR2, even dynamic SQL in the function (execute immediate) is managed as a dependency.

Wednesday, November 17, 2010

GCC Customer Story on Oracle Technology Network



A GCC Oracle ADF Customer Story has been published on Oracle Technology Network.


It shows the GCC technology move to new Fusion Middleware technologies and the integration with existing Oracle Forms and Reports and the Infrastructure & Development roadmap of the IS Department.


You can also refer to the ~50 slides presentation covering the technical aspects of this transition.



Monitor LOBs space

If you use LOBs and delete records from the tables with a LOB attribute, you should monitor LOBs segments.
For example, ADF is using a table (PS_TXN) to serialize user session state to the database, a good monitoring of any growing space used by PS_TXN must be done.


Check Space used by LOB segments

select a.tablespace_name LOB_TSpace, b.tablespace_name TAB_TSPACE,
       b.owner TAB_OWNER,a.owner LOB_OWNER,
       table_name,column_name,a.segment_name LOG_SEGMENT,index_name,
       sum(bytes) LOB_SEGMENT_SIZE from all_lobs a , dba_segments b
where a.segment_name= b.segment_name
and a.owner in ('MY_USER')
group by a.tablespace_name,b.tablespace_name, b.owner,a.owner,
table_name,column_name,a.segment_name,index_name;


Free Up LOB space

ALTER TABLE mytable modify lob (image) (shrink space);

To Automatically delete and shrink LOBs space on Log file or ADF PS_TXN file, you can create a DBMS scheduled job 

begin
 dbms_scheduler.drop_job('MYJOB',false);
 dbms_scheduler.drop_schedule('MYJOBSCHED',false);
 dbms_scheduler.create_schedule('MYJOBSCHED',null,'freq=WEEKLY;byday=MON;byhour=18',null,'Weekly Schedule to purge ADF persistency segments');
 dbms_scheduler.create_job('MYJOBS','MYJOBSCHED','PLSQL_BLOCK',
                           'Begin delete from MY_USER.ps_txn where creation_date<sysdate-1; alter table MY_USER.ps_txn modify lob (content) (shrink space); end;',
                            0,'DEFAULT_JOB_CLASS',FALSE,TRUE,'Comment');
 dbms_scheduler.disable('MYJOBSCHED');
 dbms_scheduler.enable('MYJOBSCHED');
end;


You can check if Job ran successfully with

select * from dba_scheduler_job_run_details where job_name like '%MYJOB%';

2 minutes full off-line Database backup using Volume Snapshot

If you use a good Network Storage system, certified with Oracle and for simplicity mount Oracle home and datafiles to NFS volumes, you can make offline backups in less than 5 minutes...

You will :
1/. Shutdown the database
2/. Snapshot the NFS volume on the storage system (StorageName) where Oracle home is located.
    This can also be done if datafiles, archlogs, etc.. are a distinct volume on the Storage.
3/. Restart the database

The snapshot can be mirrored to a DR Storage System and/or backed up to Tape.

Database down,snap & up script (backuporacle.sh)

ORACLE_HOME=/u01/app/oracle/orahome/mydbase
LD_LIBRARY_PATH=$ORACLE_HOME/lib
PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch
ORACLE_SID=mydbase
export ORACLE_HOME
export ORACLE_SID
export PATH
export LD_LIBRARY_PATH
echo Shutting down the database
echo ------------------------------------
/u01/app/oracle/orahome/mydbase/bin/lsnrctl stop<<EOF
EOF
/u01/app/oracle/orahome/mydbase/bin/sqlplus /nolog<<EOF
connect / as sysdba
shutdown immediate
EOF
echo Filer Snapshots - delete day-2 Oracle home volume snapshot (oraHprev),
echo rename day-1 to oraHprev and snap (oraHsnap).
echo ------------------------------------
/usr/bin/rsh StorageName -l rootaccount:rootpassword snap delete orahome oraHprev
/usr/bin/rsh StorageName -l rootaccount:rootpassword snap rename orahome oraHsnap oraHprev
/usr/bin/rsh StorageName -l rootaccount:rootpassword snap create orahome oraHsnap
echo Removing archived logs in both paths
echo ------------------------------------
/bin/rm /u01/app/oracle/Archlogs/arch*.arc
/bin/rm /u01/app/oracle/exports/Archlogs/arch*.arc
echo Restarting the database
echo ------------------------------------
/u01/app/oracle/orahome/mydbase/bin/lsnrctl start<<EOF
EOF
/u01/app/oracle/orahome/mydbase/bin/sqlplus /nolog<<EOF
connect / as sysdba
startup
EOF


Linux cron file to execute the backup script for example at 23h30, Monday to Friday (1-5) :
(2 single lines, no line break)

29 23 * * 1-5 /bin/echo Shutdown the database, rename snapshots, create snapshots, startup the database >> /u01/app/oracle/BUPScripts/backup.log

30 23 * * 1-5 /u01/app/oracle/BUPScripts/backuporacle.sh 1>> /u01/app/oracle/BUPScripts/backup.log 2>&1

Migrate and Integrate Forms, Reports and ADF

Slides from General Construction Co.Ltd Case Study presented at SAOUG 2010 :-
Move to Oracle Application Development Framework (ADF) and integration with Oracle Forms and Reports covering Infrastructure, Security in ADF and Weblogic and a few ADF application screenshots.

This approach can be used for anyone developping with Forms and moving or with the intention to move to ADF.