Execute Procedure in Background - Dynamic Scheduling

To execute procedure in background, most of the time you will be advised to execute the procedure using OS shell. This is not practical if you need it frequently or you have no privilege on OS server.

In Data warehousing environment, often we want to execute several transformations based on same operational data. That is, after loading the operational data in staging area, we have to do several transformation on this raw data. The obvious solution of this problem is to put the transformations in different procedure and schedule them depending on raw data loading. In this case you have to estimate the raw data loading acutely. Otherwise, if you execute a single procedure and within that call the three procedures (one raw data load and two transformation) sequentially, then you have to wait for the other transformation.

In stead all of the above options, you can schedule the two transformation after the raw data load with the main procedure to run them in parallel. For this you have to schedule the two transformations and pass null value as interval parameter in DBMS_JOB.SUBMIT procedure call. As interval is set to null this procedure will be scheduled to run only once. So after execution of the procedure you will not find these jobs dba_jobs view.

You can use the following procedure for this -

CREATE OR REPLACE PROCEDURE proc_run_bg(proc_name_with_param varchar2, delay_time number default -1) IS
job_id NUMBER;
BEGIN
--schedule the procedure to run it after the delay time mentioned in parameter

DBMS_JOB.SUBMIT
(
job => job_id
,what => proc_name_with_param||';'
,next_date => sysdate+(delay_time/(24*60))
,interval => NULL
);

commit;

END proc_run_bg;
/

A sample procedure utilizing this proc_run_bg procedure -


CREATE OR REPLACE PROCEDURE proc_etl(refdate date) IS
BEGIN

load_operational_data(refdate);

proc_run_bg('proc_transform1('''||refdate||''')');

proc_run_bg('proc_transform2('''||refdate||''')');

/* proc_transform1 & proc_transform2 will run in parallel and proc_etl will be completed just after submitting of these two procedure to dbms_job. It does not execute these transformation directly.*/

END proc_etl;
/


I have named this procedure proc_run_bg as in essence it runs the procedure in background after the time (in minute) elapses that you passed as parameter. Thus you can execute two procedure within one session literally but not in reality!!

This is very useful to me for dynamic scheduling as dependent procedures can be scheduled at run time instead of using fixed expression. That is, without scheduling dependent procedures to run everyday at 7 am. (by estimating that staging data get loaded before 7 am.) , I use it to run two transformations just after loading the staging data.

Buffer Sort: Reason and Problems

Buffer sort uses the buffering mechanism of a traditional sort, but it does not do the sort itself.

The system simply buffers the data, in the UGA or PGA, to avoid multiple table scans against real data blocks. The whole sort mechanism is reused, including the swap to disk (TEMP tablespace) when not enough sort area memory is available, but without sorting the data.

So it is similar to temporary table but difference is temporary table gets stored in SGA thus buffer cache; so it introduces latching delay. Now comes the reason of buffer sort:-

1. If sql query has sub-query, then the sub-query result will be buffered in PGA using buffer sort. e.g. select * from A where A.c1 in (select c1 from B). The result of select c1 from B will be buffer sorted.
   
2. If sql query get transformed specially in case of star transformation, thus sub-query comes
   into picture and you will see buffer sort.
   
3. If abnormal join takes place, then you will see buffer sort. An example of this abnormal join is joining of two table where parallel degree of one table is one and that of another table is high then buffer sort appears. The less degree table is buffered then joined with high degree table. This is more discussed in BUFFER SORT and Parallel query. In this case it cause long execution of query. Hence it should be removed.

For case 1 & 2, it is unavoidable in most of the case if your query is OK. In first two cases, the main problem of buffer sort is it uses sort_area of PGA. As usual when PGA is filled up it takes place in temp tablespace. In first case, you can remove it by using result_cache hints to save sort_area. E.g. select * from A where A.c1 in (select /*+result_cache*/ c1 from B). Alternatively, if you want to increase sort_area_size, you can set the following
two parameters -

alter session set sort_area_size=100000;

alter session set workarea_size_policy = 'MANUAL';

Oracle Parameterized View

Oracle has this feature because sometimes parameterized view can increase the performance because here predicate are compiled within the view; thus for this predicate the query will be always soft parsed.

Here is a simple example -

create view myview as

select * from t where x = userenv('client_info');


Here in this view definition, user's default namespace "userenv " and
its attribute "client_info" is used. To retrieve data from this view
you have to set this attribute by execute -

exec dbms_application_info.set_client_info
('client_info_value') ;

Some remarks on Oracle context -

1. "Userenv" is the default namespace or context.
   You can create your own context also if you have the privilege.
   
   
2. Every context(namespace) has a package within which
   you can write the code to set any attribute of your context.
   You cannot set the attribute outside the package.
   If you want to do it outside you will get error -
   ORA-01031: insufficient privileges.
   
   
3. To retrieve data from this package, we should use sys_context function.

So, you can use sys_context function also for parameterized view by calling
this function in view definition and before query the data set
the attribute used in view definition. Thus you can easily write view like user_tables -

select * from dba_tables
where owner = sys_context('USERENV', 'SESSION_USER');

Related links -
http://forums.oracle.com
http://asktom.oracle.com

Maintain shell script log in CSV

Suppose you want to log the start and end of your shell script execution. Also you want to know the total execution time. Then you should do the followings -

1. Put the start time in a variable at beginning of script;
   command - export start_date=`date +%m/%d/%Y,%H:%M:%S`.
   Be careful and use `` this operator to put the date function in a variable. Use the proper date format string as you need.
   
   
2. Put the end in a variable at the end of script;
   command - export end_date=`date +%m/%d/%Y,%H:%M:%S`.
   
   
3. Write the log and separate the field using ','.
   echo 'start,' $start_date ',end,' $end_date >> scriptexlog.csv
   
   
   

This is so simple!!

Some insight on Oracle Exception Handling

Like some high level programming language oracle provide some pretty feature on exception handling. Like other high level language here you can catch specific exception and handle that accordingly. Here you can also create your own exception also.

Firstly, how to catch specific exception :-

Suppose for network problem one of your dblink face problem and generate Oracle Exception ORA-3135. To catch this exception, you should define an exception with this exception number and then use the given name to catch this exception. That is create an exception using following code -

connection_lost Exception;
PRAGMA EXCEPTION_INIT(connection_lost, -3135);

Then in Exception block write the following -

when connection_lost then
/*some statements*/

Secondly, create and raise custom error:

create custom exception by following command -
cust_exception Exception;
PRAGMA EXCEPTION_INIT(cust_exception, -20001);

then command to raise this exception -
RAISE cust_exception;

Then catching this exception is same as other exception. There is an overload of RAISE_application_error which takes a third bool parameter. This parameter determines whether they data will be put on empty stack or top of stack.

Thirdly, Log Exception from the stack:

To get maximum log of exception, call DBMS_UTILITY.FORMAT_ERROR_STACK function and log the output. It will give not only top of stack but also all content of the procedure error stack. Along with this use DBMS_UTILITY.FORMAT_ERROR_BACKTRACE to log line from which error is generated. It is available from Oracle 10g.

For more information visit following links -

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/errors.htm

http://www.oracle-developer.net/display.php?id=318

Console/admin log in Windows server

Sometimes when you want to log in a windows server OS you may get an error like terminal exceeded maximum number of user. But you have to do some admin work on the windows server; suppose disconnecting one of this user, then what you have to do?

just open command prompt, execute remote desktop connection with the following command-

mstsc -v:IP_OF_SERVER /F -admin

Then give your user & password. This is so simple!!

One thing keep in mind every time, that is when exiting the server always use log off. If you do not log off then if disconnect create some problem (which I face several time) then you cannot log in again as admin/console.

How to optimize your PL/SQL code

To increase performance of your pl/sql, you should alter system plsql parameters. It is recommended to use separate parameter value for production and development. For development, the parameter are set to default. So for development plsql_code_type will be 'INTERPRETED' and plsql_optimize_level will be 2. For production environment, you should change the parameter in following way,

alter session set plsql_code_type = 'NATIVE';

alter session set plsql_optimize_level = 3;

One very important effect of setting these two parameters is that - without setting these parameters in such values, you cannot use your function in parallel query.

If your functions are deterministic, you can create function index to increase performance. Thus you can enhance plsql performance.