DB Time in AWR report

DB Time in AWR report is the total time for which all user process are either working or waiting in Database. In other words , DB Time is the time spent in database by all user processes including CPU time, I/O time and Wait time and excludes idle time for a particular Snap Interval.

When We generate AWR report for a particular time interval. DB time can be less than AWR generation time interval or greater than. If DB time is too high as compared to AWR generation time interval then heavy activity is going on database and if less, then database is in idle state for most of the time.

Let suppose AWR is generated for 2 hours, then AWR generation time interval  is 120 mins. Every user process in Oracle database is calculated on the basis of DB Time in AWR report. For Ex:-

Begin Snap:	34436	03-Sep-13 16:00:46	420	23.2
End Snap:	34478	05-Sep-13 10:00:38	360	25.8
Elapsed:		2,519.88 (mins)
DB Time:		22,362.47 (mins)

Here DB time is too high as compared to Elapsed time means heavy activity is going on database.

Statistic Name	Time (s)	% of DB Time
sql execute elapsed time	862,534.11	64.28
DB CPU	573,949.72	42.78
connection management call elapsed time	16,980.05	1.27
parse time elapsed	8,017.36	0.60

Above are statistics consumes DB time , This % can be greater than 100 % . If sql execute elapsed time is having a greater value means your database spends most of its time in executing SQL's.

As a DBA we need to regularly look into AWR reports so that we are aware that every thing is working fine. But for Production databases we can't generate AWR's for every Snap interval. PFB below query which will give the snap time for which DB time is too high and then we can generate AWR report for that Snap period and analyze the things.

Query to generate DB time of all Snap Intervals in a week  :-

Standalone Database :-

 col begin_interval_time_N format A35 trunc
 col end_interval_time_N format A35 trunc
 col HOST_NAME format A20 trunc
 col INSTANCE_NUMBER format 9 heading “I”
 col begin_interval_time_N 
 col end_interval_time_N 
 select  A.SNAP_ID , A.INSTANCE_NUMBER , C.HOST_NAME  , MIN(A.begin_interval_time) OVER (partition by A.dbid, A.snap_id) AS begin_interval_time_N  
 , MIN(A.end_interval_time) OVER (partition by A.dbid, A.snap_id) AS end_interval_time_N  , (B.VALUE - LAG(B.VALUE,1,0) OVER
 (ORDER BY A.SNAP_ID))/1000000/60 as DB_TIME  from dba_hist_snapshot A , DBA_HIST_SYS_TIME_MODEL B,dba_hist_database_instance C
 where A.SNAP_ID=B.SNAP_ID  and A.DBID=B.DBID  And A.INSTANCE_NUMBER=B.INSTANCE_NUMBER  and B.STAT_NAME Like 'DB time'  
 and A.DBID=C.DBID  and A.INSTANCE_NUMBER=C.INSTANCE_NUMBER  and A.startup_time = C.startup_time  and 
 A.begin_interval_time >= trunc(sysdate) - 1 and A.begin_interval_time <= trunc(sysdate) ;

RAC Database , We have to generate instance wise :-

 col begin_interval_time_N format A35 trunc
 col end_interval_time_N format A35 trunc
 col HOST_NAME format A20 trunc
 col INSTANCE_NUMBER format 9 heading “I”
 col begin_interval_time_N 
 col end_interval_time_N 
 select  A.SNAP_ID , A.INSTANCE_NUMBER , C.HOST_NAME  , MIN(A.begin_interval_time) OVER (partition by A.dbid, A.snap_id) AS begin_interval_time_N  
 , MIN(A.end_interval_time) OVER (partition by A.dbid, A.snap_id) AS end_interval_time_N  , (B.VALUE - LAG(B.VALUE,1,0) OVER
 (ORDER BY A.SNAP_ID))/1000000/60 as DB_TIME  from dba_hist_snapshot A , DBA_HIST_SYS_TIME_MODEL B,dba_hist_database_instance C
 where A.SNAP_ID=B.SNAP_ID  and A.DBID=B.DBID  And A.INSTANCE_NUMBER=B.INSTANCE_NUMBER  and B.STAT_NAME Like 'DB time'   and A.DBID=C.DBID  and A.INSTANCE_NUMBER=C.INSTANCE_NUMBER  and A.startup_time = C.startup_time  and 
 A.begin_interval_time >= trunc(sysdate) - 1 and A.begin_interval_time <= trunc(sysdate) and A.INSTANCE_NUMBER=1;

I hope this article helped you.

Regards,
Amit Rath

How to download blob file from oracle table.

Unlike normal data , blob data needs Pl/sql blocks to download in a particular directory. Sometimes we need to download blob(pdf,image etc) from tables. PFB Steps to download BLOB files :-

1. Insert Blob data to a test table. PFB steps to insert Blob data.

2. Create  directory named DATAPUMP where file will be downloaded.

SQL> create directory datapump as '/amit/datapump;

Directory created

SQL> grant read,write on directory datapump to amit;

Grant succeeded.

3. Now as per above link Blob data has been inserted in LOB_INSERT table. PFB structure and data present in LOB_INSERT table:-

SQL> desc lob_insert
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 LOB_ID                                                  NUMBER(12)
 LOB_NAME                                           VARCHAR2(80)
 FILE_SIZE                                            NUMBER
 LOB_FILE                                             BLOB

SQL> select * from lob_insert;

    LOB_ID LOB_NAME                        FILE_SIZE LOB_FILE
---------- ------------------------------ ---------- ----------------------------------------
         1 filenumber 1                                               3C68746D6C3E0A3C686561643E0A3C6D65746120
                                                                               687474702D65717569763D22436F6E74656E742D
                                                                               547970652220636F6E74656E743D22746578742F
                                                                               68746D6C3B20636861727365743D55532D415343

         2 filenumber 2                                               3C68746D6C3E0A3C686561643E0A3C6D65746120
                                                                              687474702D65717569763D22436F6E74656E742D
                                                                             547970652220636F6E74656E743D22746578742F
                                                                             68746D6C3B20636861727365743D55532D415343

         3 filenumber 3                                              3C68746D6C3E0A3C686561643E0A3C6D65746120

    LOB_ID LOB_NAME                        FILE_SIZE LOB_FILE
---------- ------------------------------ ---------- ----------------------------------------
                                                                              687474702D65717569763D22436F6E74656E742D
                                                                             547970652220636F6E74656E743D22746578742F
                                                                             68746D6C3B20636861727365743D55532D415343

LOB_FILE column has blob data.

4. Now to download Blob files to DATAPUMP directory run below PL/SQL procedure :-

DECLARE

l_file UTL_FILE.FILE_TYPE;

l_buffer RAW(32767);

l_amount BINARY_INTEGER := 32767;

l_pos INTEGER := 1;

l_blob BLOB;

l_blobname varchar2(50);

l_blob_len INTEGER;

ctrl integer;

BEGIN

-- Get LOB locator

select max(LOB_ID) into ctrl from LOB_INSERT;   -- used to download all files of table

for i in 1 .. ctrl loop

SELECT LOB_file,LOB_name

INTO l_blob,l_blobname

FROM LOB_INSERT

where lob_id=i;

l_blob_len := DBMS_LOB.getlength(l_blob);

-- Open the file.in Datapump Directory already created.

l_file := UTL_FILE.fopen('DATAPUMP',l_blobname,'WB', 32767);  

-- File will be created with the name stored as in LOB_NAME column

-- Read chunks of the BLOB and write them to the file created in directory until complete.

WHILE l_pos < l_blob_len LOOP

DBMS_LOB.read(l_blob, l_amount, l_pos, l_buffer);

UTL_FILE.put_raw(l_file, l_buffer, TRUE);

l_pos := l_pos + l_amount;

END LOOP;    -- This will end the While loop when condition met.

-- To read next blob file these variables need to be reset again

l_blob_len :=0;               

l_pos :=1;

-- Close the file.

UTL_FILE.fclose(l_file);

end loop;       -- This will end the FOR loop when all BLOB files in the table have been downloaded.

EXCEPTION

WHEN OTHERS THEN

-- Close the file if something goes wrong.

IF UTL_FILE.is_open(l_file) THEN

UTL_FILE.fclose(l_file);

END IF;

RAISE;

END;

/

5. Check Directory DATAPUMP that File has been created with name as it has in LOB_NAME column.

bash-3.2$ cd /amit/datapump/

bash-3.2$ ls -ltr|tail

-rw-r--r--    1 oracle   oinstall    3280618 Sep 24 00:40 filenumber 2

-rw-r--r--    1 oracle   oinstall    3280618 Sep 24 00:40 filenumber 1

-rw-r--r--    1 oracle   oinstall    3280618 Sep 24 00:40 filenumber 3

All blob files have been Downloaded successfully. 

I hope this article helped you.

Regards,

Amit Rath