Monday, January 27, 2014

querying backupset to check compression ratio

SELECT  b.database_name 'Database Name',  
CONVERT (BIGINT, b.backup_size / 1048576 ) 'UnCompressed Backup Size (MB)',  
CONVERT (BIGINT, b.compressed_backup_size / 1048576 ) 'Compressed Backup Size (MB)',  CONVERT (NUMERIC (20,2), (CONVERT (FLOAT, b.backup_size) /  CONVERT (FLOAT, b.compressed_backup_size))) 'Compression Ratio',
DATEDIFF (SECOND, b.backup_start_date, b.backup_finish_date) 'Backup Elapsed Time (sec)'  
FROM  msdb.dbo.backupset b  
WHERE  DATEDIFF (SECOND, b.backup_start_date, b.backup_finish_date) > 0  
AND b.backup_size > 0  
ORDER BY  b.backup_finish_date DESC

Sunday, January 26, 2014

Querying the V$WORKAREA_ACTIVE View

Querying the V$WORKAREA_ACTIVE View


SELECT TO_NUMBER(DECODE(sid, 65535, null, sid)) sid,
       operation_type operation,
       TRUNC(expected_size/1024) esize,
       TRUNC(actual_mem_used/1024) mem,
       TRUNC(max_mem_used/1024) "max mem",
       number_passes pass,
       TRUNC(TEMPSEG_SIZE/1024) tsize
  FROM V$SQL_WORKAREA_ACTIVE
 ORDER BY 1,2;
The output of this query might look like the following:


SID         OPERATION     ESIZE       MEM   MAX MEM  PASS   TSIZE
--- ----------------- --------- --------- --------- ----- -------
  8   GROUP BY (SORT)       315       280       904     0
  8         HASH-JOIN      2995      2377      2430     1   20000
  9   GROUP BY (SORT)     34300     22688     22688     0
 11         HASH-JOIN     18044     54482     54482     0
 12         HASH-JOIN     18044     11406     21406     1  120000”

Excerpt From: Corporation, Oracle. “Oracle® Database Performance Tuning Guide, 12c Release 1 (12.1).”