TIP #46 : Using 'Plus archivelog' in backup

I had a client which using Oracle 10gR3 on Windows.Its backup strategy was simple. Daily incremental level 1 backup and weekly level 0 backup.Database was configured to use flash recovery area for archivelogs and backups.
I regularly get flash recovery full problem with retention policy 2 weeks.Suprisingly database is only 1GB.
Bacjup script was as simple as 'backup database ...... plus archivelog ';

Finally, I found that problem was because of plus archivelog.
Regarding to RMAN document, RMAN performs these steps when "plus archivelog" command is in backup script.

1. Runs an ALTER SYSTEM ARCHIVE LOG CURRENT statement.
2. Runs the BACKUP ARCHIVELOG ALL command. Note that if backupoptimization is enabled, then RMAN only backs up logs that have not yetbeen backed up.
3. Backs up the files specified in the BACKUP command.
4. Runs an ALTER SYSTEM ARCHIVE LOG CURRENT statement.
5. Backs up any remaining archived redo logs.

If backup otimization is off (default RMAN), each time all archivelogs are backed up which means that on daily basis backup size grows since archivelogs in flash recovery are not removed as space exists.

In a nutshell, when "plus archivelog" is used in backup script , make sure that optimization is on in RMAN configuration otherwise in each backup whole archive logs are backed up.

TIP #45 : Performance tuning PL/SQL with profiler.

Oracle provides dbms_profiler for tracing PL/SQL code and finding any performace bottlenecks.
This post shows how to use it.
For using dbms_profiler, first it should be installed.

Install dbms_profile and populate tables.

- @?/rdbms/admin/profload.sql (Install dbms_profiler)
- @?/rdbms/admin/proftab.sql (Populate profiler tables).

How to trace :

- exec dbms_profiler.start_profiler('Sample1');
- Run PL/SQL code (it would be better to be proc/func/package instead of anonymous code.
- exec dbms_profiler.stop_profiler;
- exec DBMS_PROFILER.FLUSH_DATA;

How to see the result :

- Which runid is for current trace
SELECT runid,
run_date,
run_comment,
run_total_time
FROM plsql_profiler_runs
ORDER BY runid;

- Total elapsed time of each PL/SQL code.


SELECT u.runid,
u.unit_number,
u.unit_type,
u.unit_owner,
u.unit_name,
d.line#,
d.total_occur,
d.total_time,
d.min_time,
d.max_time
FROM plsql_profiler_units u
JOIN plsql_profiler_data d ON u.runid = d.runid AND u.unit_number = d.unit_number
WHERE u.runid = &1
ORDER BY u.unit_number, d.line#;

FYI : Also putting dbms_utility.get_time(); in PL/SQL code would show exact run time execution of each PL/SQL pieces.

TIP # 44 : Performance tips for PL/SQL code.

Small handy guidelines for improving performance of PL/SQL code.

1. In condition put least expensive condition first. (It stops when evalutes it)
2. Minimize implicit conversion. Try not to use mix of datatype . (using number and pls_integer)
3. Use pls_integer when you can. (Best performance)
4. Use binary_float or binary_double for floatingpoint
5. Mostly allocate varchar2 of size >4000, because Oracle waits for allocating it in memory at runtime (Saving memory).However for varchar2<4000,>
6. Put relevant subprogram into a package, because calling a function/procedure of it will load whole code in memory which can be referenced later.
7. Pin frequent used pacage to prevent it from aging out.(dbms_shared)
8. If using out parameter, it adds some performance overhead to make sure that in the case of any unhandled exception out parameter keep its original value after back to original program. If it is not important, user out ncopy to reduce overhead. (true for funtion with big out collection or LOB out).
9. If you are running SQL statements inside a PL/SQL loop, FORALL statement as a way to replace loops of INSERT, UPDATE, and DELETE statements.
10. If you are looping through the result set of a query, BULK COLLECT clause of the SELECT INTO statement as a way to bring the entire result set into memory in a single operation.
11. Prevent unnecessary function call
    for item in (select distinct(sqlrt(empno)) from emp) ===> change to ===>
    for item in (select sqlrt(empno) from (select distinct empno from emp))
    

Example :

=== Poor performance
begin
For myvar in (select empno from emp) loop
if (myvar.empno>1000) then
delete from emp2 where empno = myvar.empno;
end if;
end loop;
end;
=== Good performance
declare
type mytype is table of number index by pls_integer;
myvar mytype;
i pls_integer:=1;
begin
select empno bulk collect into myvar from scott.emp where empno>1000;
forall i in myvar.first..myvar.last delete from scott.emp2 where empno=myvar(i);
end;
==== Forall with exception %BULK_ROWCOUNT(i), SQL%BULK_EXCEPTIONS.COUNT <==== Only works with forall

declare
type mytype is table of number(10) index by pls_integer;
myvar mytype;
i pls_integer:=1;
errors number;
begin
select empno bulk collect into myvar from scott.emp where empno>1000;
forall i in myvar.first..myvar.last save exceptions delete from scott.emp2 where empno=myvar(i);
exception WHEN others THEN -- Now we figure out what failed and why.
errors := SQL%BULK_EXCEPTIONS.COUNT;
DBMS_OUTPUT.PUT_LINE('Number of statements that failed: ' errors);
FOR i IN 1..errors LOOP
DBMS_OUTPUT.PUT_LINE('Error #' i ' occurred during ''iteration #' SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
DBMS_OUTPUT.PUT_LINE('Error message is ' SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
END LOOP;
end;

TIP #43: Effective DOP.

Many of us use parallel query to get results ASAP.
However the big question is if query runs with determined DOP (Degree Of Parallelism) or Oracle downgrades it to lower DOP because of low in resources.
Doug Burns has a fully analysis in his blog. (http://oracledoug.com/serendipity/index.php?/archives/1231-How-can-I-tell-the-actual-DOP-used-for-my-Parallel-Query.html).

From reading Doug `s blog and different articles , I think that the best way to determine whether or not a session is using determined DOP is to run the following query.

select n.name, s.value
from v$sesstat s, v$statname n
where
s.statistic# = n.statistic#
and
n.name like 'Parallel%'
and sid=&sid_val;

If Parallel operations not downgraded <>0 , it means that session is running with determined DOP. However if Parallel operations downgraded to serial <>0 , it means that query is not running in parallel.
Parallel operations downgraded 75 to 99 pct , Parallel operations downgraded 50 to 75 pct, Parallel operations downgraded 25 to 50 pct and Parallel operations downgraded 1 to 25 pct determines if DOP is downgraded to lower value and in what percentage.

TIP #42: Package compilation takes long time

Last week, I had a request from a client which compilation session was running for hours.It was supposed to be completed in less than a minute. Session was trying to compile a package.
When I checking the compilation session, It was waiting for 'Library cache lock ' and 'Library cache pin'.
Reason : This package was security major package which is used by most sessions.The compiliation did hang because other sessions were executing procedure/function of this package.

Found metalink note 122793.1 very useful which basically recommends two options.

Option 1. Found blocking sessions with enabling trace.

• Run hanging compilation session and find out its process id (PID)
• Login with new session and enable tracing with

ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME SYSTEMSTATE LEVEL 10';

• Check user_dump_dest for trace , find process which match with PID of hanging session (Assume PID of hanging session is 8). Find handle address and find other PID with the same handle.In the following example PID 8 is hanging session and PID 9 is blocking session. With having PID=9, more info about blocking session can be found.

PROCESS 8:
----------------------------------------
.
.
.
.
waiting for 'library cache lock' blocking sess=0x0 seq=253 wait_time=0
handle address=5023ef9c, lock address=5019cad4, 10*mode+namespace=15

PROCESS 9:
----------------------------------------
.
.
.
LIBRARY OBJECT PIN: pin=5019d5e4 handle=5023ef9c mode=X lock=0
user=5005fad4 session=5005fad4 count=1 mask=0511 savepoint=118218 flags=[00]

2. Find blocking sessions with running a query

Run the following query. Basically X$KGLLK has all library cache lock and KGLLKREQ > 0 means that lock was requested by a session but session did not get it .

This query lists all blocking sessions which blocked hanging session.

SELECT SID,USERNAME,TERMINAL,PROGRAM FROM V$SESSION
WHERE SADDR in
(SELECT KGLLKSES FROM X$KGLLK LOCK_A
WHERE KGLLKREQ = 0
AND EXISTS (SELECT LOCK_B.KGLLKHDL FROM X$KGLLK LOCK_B
WHERE KGLLKSES = 'saddr_from_v$session' /* BLOCKED SESSION */
AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL
AND KGLLKREQ > 0)
);