Oracle Database Fast Tuning

WITH t1
        AS (  SELECT pool "MEMORY_TYPE",
                     Total_Mem "ALLOCATION_K",
                     Free_Mem "FREE_K",
                     '|||' "A",
                     RUBRIQUE "MEMORY_RATIOS",
                     TO_CHAR (ROUND (RATIO * 100, 1), '999.9') "RATIO_PERCENT",
                     TRIM (IDEAL) "IDEAL"
                FROM
                     (SELECT rownum0,
                             A.pool,
                             A.Total_Mem,
                             B.Free_Mem
                        FROM (SELECT (ROWNUM) rownum0, A.*
                                FROM (  SELECT pool,
                                               ROUND (SUM (bytes) / 1024, 0)
                                                  Total_Mem
                                          FROM v$sgastat
                                         WHERE pool IS NOT NULL
                                      GROUP BY pool
                                      UNION
                                      SELECT name, ROUND (bytes / 1024)
                                        FROM v$sgastat
                                       WHERE     pool IS NULL
                                             AND name != 'fixed_sga') A
                              UNION ALL
                              SELECT 6,
                                     'Sort Area Size',
                                     ROUND (VALUE / 1024, 0)
                                FROM v$parameter
                               WHERE name IN ('sort_area_size')
                              UNION ALL
                              SELECT 7,
                                     'Hash Area Size',
                                     ROUND (VALUE / 1024, 0)
                                FROM v$parameter
                               WHERE name IN ('hash_area_size')) A,
                             (SELECT pool, ROUND (bytes / 1024, 0) Free_Mem
                                FROM v$sgastat
                               WHERE name = 'free memory'
                              UNION ALL
                              SELECT 'db_block_buffers',
                                       (SELECT COUNT (*)
                                          FROM v$bh
                                         WHERE status = 'free')
                                     * (SELECT (ROUND (VALUE / 1024, 0))
                                          FROM v$parameter
                                         WHERE name = 'db_block_size')
                                FROM DUAL) B
                       WHERE A.pool = B.pool(+)) SGA,
                     (SELECT 6 rownum0,
                             'DATA DICTIONARY CACHE' "RUBRIQUE",
                             SUM (getmisses) / SUM (gets) "RATIO",
                             ' < 150' "IDEAL"
                        FROM v$rowcache
                      UNION ALL
                      SELECT 3,
                             'SHARED POOL HIT RATIO',
                             SUM (pinhits - reloads) / SUM (pins),
                             ' > 850'
                        FROM v$librarycache
                      UNION ALL
                      SELECT 4,
                             'SHARED POOL RELOAD %',
                             SUM (reloads) / SUM (pins),
                             ' <  020'
                        FROM v$librarycache
                      UNION ALL
                      SELECT 2,
                             'BUFFER CACHE Hit Ratio',
                             (  1
                              - (  SUM (
                                      DECODE (name, 'physical reads', VALUE, 0))
                                 / (  SUM (
                                         DECODE (name,
                                                 'db block gets', VALUE,
                                                 0))
                                    + (SUM (
                                          DECODE (name,
                                                  'consistent gets', VALUE,
                                                  0)))))),
                             ' > 950'
                        FROM v$sysstat
                      UNION ALL
                      SELECT 1,
                             'BUFFER CACHE MISS RATIO',
                             ( (G - F) / (G - F + C + E)),
                             ' < 150'
                        FROM (SELECT SUM (VALUE) C
                                FROM v$sysstat
                               WHERE name LIKE '%- consistent read gets') c,
                             (SELECT VALUE E
                                FROM v$sysstat
                               WHERE name = 'db block gets') e,
                             (SELECT VALUE F
                                FROM v$sysstat
                               WHERE name = 'physical reads direct') f,
                             (SELECT VALUE G
                                FROM v$sysstat
                               WHERE name = 'physical reads') g
                      UNION ALL
                      SELECT 5,
                             'LOG BUFFER REQUESTS Ratio',
                             ( (req.VALUE * 50) / entries.VALUE),
                             ' < 002'
                        FROM v$sysstat req, v$sysstat entries
                       WHERE     req.name = 'redo log space requests'
                             AND entries.name = 'redo entries'
                      UNION ALL
                      SELECT 7,
                             'MEM SORTS/TOTAL SORTS',
                             mem.VALUE / (mem.VALUE + disk.VALUE),
                             ' > 950'
                        FROM v$sysstat mem, v$sysstat disk
                       WHERE     mem.name = 'sorts (memory)'
                             AND disk.name = 'sorts (disk)') RATIOS
               WHERE SGA.rownum0(+) = RATIOS.ROWNUM0
            ORDER BY SGA.rownum0 ASC)
   SELECT memory_type,
          allocation_k,
          free_k,
          memory_ratios,
          TRUNC (
               CAST (REGEXP_REPLACE (RATIO_PERCENT, '[^0-9]+', '') AS NUMBER)
             / 10,
             2)
             ratio,
          TRUNC (
               CAST (
                  REGEXP_REPLACE (TRIM (SUBSTR (ideal, -4, 4)),
                                  '[^0-9]+',
                                  '') AS NUMBER)
             / 10,
             2)
             optimum,
          SUBSTR (TRIM (ideal), 0, 1) SIGN,
          DECODE (
             (SUBSTR (TRIM (ideal), 0, 1)),
             '<', (DECODE (
                      SIGN (
                           (TRUNC (
                                 CAST (
                                    REGEXP_REPLACE (RATIO_PERCENT,
                                                    '[^0-9]+',
                                                    '') AS NUMBER)
                               / 10,
                               2))
                         - (TRUNC (
                                 CAST (
                                    REGEXP_REPLACE (
                                       TRIM (SUBSTR (ideal, -4, 4)),
                                       '[^0-9]+',
                                       '') AS NUMBER)
                               / 10,
                               2))),
                      -1, 'SUPER',
                      0, 'KRITIK',
                      1, 'KOTU')),
             '>', (DECODE (
                      SIGN (
                           (TRUNC (
                                 CAST (
                                    REGEXP_REPLACE (RATIO_PERCENT,
                                                    '[^0-9]+',
                                                    '') AS NUMBER)
                               / 10,
                               2))
                         - (TRUNC (
                                 CAST (
                                    REGEXP_REPLACE (
                                       TRIM (SUBSTR (ideal, -4, 4)),
                                       '[^0-9]+',
                                       '') AS NUMBER)
                               / 10,
                               2))),
                      -1, 'KOTU',
                      0, 'KRITIK',
                      1, 'SUPER')))
             durum
     FROM t1;

Linux Volume Group Administration

Hi all,

In this post, I will write about creating volume groups on Linux.

LVM stands for Logical Volume Manager.

To a create volume group from one or more pyhsical disk volumes, use the vgcreate command. The vgcreate command creates a new volume group by name and add at least one pyhsical volume to it.

First of all, I added 2 disks on virtual machine.

Screenshot from 2015-08-02 15:24:52

Then check disks.

Screenshot from 2015-08-02 15:29:50

Now, We should format disks with fdisk utility.

Screenshot from 2015-08-02 15:33:18

We have to do this process for both disks.

Now, we can create a volume group.

create_vg

We can see vgdisplay command,  properties of each volume groups.

vg_display

Now, I created logical volume with lvcreate command.

[root@localhost ~]# lvcreate -L15G -n my_volume my_vgroup
 Logical volume "my_volume" created

The size of 15 G.

I created a new ext4 file system with mkfs.ext4 command.

Screenshot from 2015-08-02 15:51:02

And mount and use it :)

mount

We also extend the volume group.

vgextend

and extend the logical volume.

lvextend

Finally, we can resize file system with resize2fs command.

resize

If we don’t want the mount file system, we can use this command before resize file system.

lvm lvchange -a y /dev/mapper/my_vgroup-my_volume

That’s all !

References :

https://access.redhat.com/documentation/en-US/Red_Hat_Enterprise_Linux/6/index.html

How to find growth tablespace ?

set serverout on 
set verify off 
DECLARE 
v_ts_id number; 
v_ts_name varchar2(200) := UPPER('&Tablespace_Name'); 
v_ts_block_size number; 
v_begin_snap_id number; 
v_end_snap_id number; 
v_begin_snap_date date; 
v_end_snap_date date; 
v_numdays number; 
v_ts_begin_size number; 
v_ts_end_size number; 
v_ts_growth number; 
v_ts_allocated_space number; 
BEGIN 
SELECT ts# into v_ts_id FROM v$tablespace where name = v_ts_name; 
SELECT block_size into v_ts_block_size FROM dba_tablespaces where tablespace_name = v_ts_name; 
SELECT min(snap_id), max(snap_id), min(trunc(to_date(rtime,'MM/DD/YYYY HH24:MI:SS'))), max(trunc(to_date(rtime,'MM/DD/YYYY HH24:MI:SS'))) 
into v_begin_snap_id,v_end_snap_id, v_begin_snap_date, v_end_snap_date from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id; 
v_numdays := v_end_snap_date - v_begin_snap_date; 
 
SELECT round(tablespace_size*v_ts_block_size/1024/1024/1024,2) into v_ts_allocated_space from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_begin_snap_id; 
SELECT round(tablespace_usedsize*v_ts_block_size/1024/1024/1024,2) into v_ts_begin_size from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_begin_snap_id; 
SELECT round(tablespace_usedsize*v_ts_block_size/1024/1024/1024,2) into v_ts_end_size from dba_hist_tbspc_space_usage where tablespace_id=v_ts_id and snap_id = v_end_snap_id; 
v_ts_growth := v_ts_end_size - v_ts_begin_size; 
DBMS_OUTPUT.PUT_LINE(CHR(10)); 
DBMS_OUTPUT.PUT_LINE('Tablespace Block Size: '||v_ts_block_size); 
DBMS_OUTPUT.PUT_LINE('---------------------------'); 
DBMS_OUTPUT.PUT_LINE(CHR(10)); 
DBMS_OUTPUT.PUT_LINE('Summary'); 
DBMS_OUTPUT.PUT_LINE('========'); 
DBMS_OUTPUT.PUT_LINE('Current Allocated Space: '||v_ts_allocated_space||' GB'); 
DBMS_OUTPUT.PUT_LINE('Current Used Space: '||v_ts_end_size||' GB'); 
DBMS_OUTPUT.PUT_LINE('% Used Space: '||round(v_ts_end_size/v_ts_allocated_space*100,2)||' %'); 
DBMS_OUTPUT.PUT_LINE(CHR(10)); 
DBMS_OUTPUT.PUT_LINE('History'); 
DBMS_OUTPUT.PUT_LINE('========'); 
DBMS_OUTPUT.PUT_LINE('Used Size on '||v_begin_snap_date||': '||v_ts_begin_size||' GB' ); 
DBMS_OUTPUT.PUT_LINE('Used Size on '||v_end_snap_date||': '||v_ts_end_size||' GB' ); 
DBMS_OUTPUT.PUT_LINE('Total growth during last '||v_numdays||' days between '||v_begin_snap_date||' and '||v_end_snap_date||' : '||v_ts_growth||' GB'); 
DBMS_OUTPUT.PUT_LINE('Per day growth during last '||v_numdays||' days: '||round(v_ts_growth/v_numdays,2)||' GB'); 
DBMS_OUTPUT.PUT_LINE(CHR(10)); 
DBMS_OUTPUT.PUT_LINE('Expected Growth'); 
DBMS_OUTPUT.PUT_LINE('==============='); 
DBMS_OUTPUT.PUT_LINE('Expected growth for next 30 days: '|| round((v_ts_growth/v_numdays)*30,2)||' GB'); 
DBMS_OUTPUT.PUT_LINE('Expected growth for next 60 days: '|| round((v_ts_growth/v_numdays)*60,2)||' GB'); 
DBMS_OUTPUT.PUT_LINE('Expected growth for next 90 days: '|| round((v_ts_growth/v_numdays)*90,2)||' GB'); 
 
EXCEPTION 
WHEN NO_DATA_FOUND THEN 
DBMS_OUTPUT.PUT_LINE(CHR(10)); 
DBMS_OUTPUT.PUT_LINE('Tablespace you entered does not exist'); 
END; 
/