Thursday 4 June 2015

CloudControl: Report for sizing memory/sga across enterprise

In my previous post I showed how to create tnsnames file from grid/cloud repository. I think OMS repository is very useful tool but at the same time too oft underestimated.
Here is my story: My client is running numbers of databases on AIX. I was asked to show distribution of memory across LPARs and Oracle Instances. Yes, sure, you can take calculator and go through your environment. But I am too lazy... Motivation for this task came after watching a seminar with Christo Kutrovsky from Pythian(https://www.youtube.com/watch?v=5HyjTxD4soA) about benefits of RAM memory.
Based on SYSMAN.MGMT_ECM_HW you can build simple query to get hardware configuration:

COL HOSTNAME FORMAT A20
select HOSTNAME,CPU_COUNT,MEMORY_SIZE_IN_MB/1024 MEM_GB from SYSMAN.MGMT_ECM_HW order by hostname;

HOSTNAME              CPU_COUNT     MEM_GB
-------------------- ---------- ----------
utest32                       3         64
integ22                       2         16
integ32                       3         16
unprod32                      3         48
...
unprod62                      8         64
unprod72                      2         24
ucont32                       3         32
ucont32                       3         24
ucont42                       2         16
ucont52                       2         16


But challenge is to get for example SGA size for each instance on every host. You can choose between SYSMAN.MGMT_DB_INIT_PARAMS_ECM and SYSMAN.MGMT_DB_SGA_ECM. Here is my script:
SET pages 100
SET lines 300
col hostname format a20
col db_name format a10
col "Total SGA" format a20
break ON hostname SKIP page ON hostname
COMPUTE sum OF "MB" ON hostname
 SELECT
    c.host_name hostname,
    upper(c.entity_name) db_name,
 sga.sganame AS "Total SGA",
 sga.sgasize AS "MB"
 FROM
    sysman.em_manageable_entities c,
    sysman.mgmt_db_sga_ecm sga,
    sysman.mgmt_ecm_gen_snapshot s
  WHERE
  s.snapshot_guid = sga.ecm_snapshot_id
  AND s.target_guid = c.entity_guid
  AND s.is_current  ='Y'
  AND c.entity_type ='oracle_database'
  AND sga.sganame='Total SGA (MB)'
  ORDER BY
    c.host_name,c.entity_name;

Output:
HOSTNAME                       DB_NAME    Total SGA                    MB
------------------------------ ---------- -------------------- ----------
utest32                        BBAIS      Total SGA (MB)              864
                               BBAIS12    Total SGA (MB)              864
                               BUSYI      Total SGA (MB)             3472
                               BUSYT      Total SGA (MB)             3136
                               DWRT       Total SGA (MB)             3610
                               DWRT12     Total SGA (MB)             3504
                               FEINP8I    Total SGA (MB)              696
                               FEINP8T    Total SGA (MB)              955
                               HRDWRT     Total SGA (MB)             2819
                               HRDWRT12   Total SGA (MB)             2880
                               OPSWT      Total SGA (MB)              696
                               PAIST      Total SGA (MB)             3098
                               OPTRALT    Total SGA (MB)              788
                               PRFT6T     Total SGA (MB)              496
                               RMAN1T     Total SGA (MB)             1559
                               RMANBK22   Total SGA (MB)             1376
******************************                                 ----------
sum                                                                 30813

HOSTNAME                       DB_NAME    Total SGA                    MB
------------------------------ ---------- -------------------- ----------
utest35                        FEINT      Total SGA (MB)              562
******************************                                 ----------
sum                                                                   562
...
HOSTNAME                       DB_NAME    Total SGA                    MB
------------------------------ ---------- -------------------- ----------
unprod62                       DWRP       Total SGA (MB)            33137
                               RMAN2P     Total SGA (MB)             1543
******************************                                 ----------
sum                                                                 34680

HOSTNAME                       DB_NAME    Total SGA                    MB
------------------------------ ---------- -------------------- ----------
unprod72                       HRDWRP     Total SGA (MB)             4970
                               RMAN3P     Total SGA (MB)             1911
******************************                                 ----------
sum                                                                  6881

I made as well a percentage distribution within SGA:
col hostname format a20
col db_name format a10
SET pages 100
SET lines 300
break ON hostname SKIP page ON hostname
COMPUTE sum OF "Total SGA(MB)" ON hostname
SELECT
    c.host_name hostname,
    upper(c.entity_name) db_name,
    round(sga.sgasize) AS "Total SGA(MB)",
    round(sp.sgasize/sga.sgasize*100) AS "SharedPool(%)",
    round(bc.sgasize/sga.sgasize*100) AS "BufferedCache(%)",
    round(rb.sgasize/sga.sgasize*100/1024) AS "RedoBuffers(%)",
    round(lp.sgasize/sga.sgasize*100/1024) AS "LargePool(%)",
    round(jp.sgasize/sga.sgasize*100) AS "JavaPool(%)"
 FROM
    sysman.em_manageable_entities c,
    sysman.mgmt_db_sga_ecm sga,
    sysman.mgmt_db_sga_ecm sp,
    sysman.mgmt_db_sga_ecm bc,
    sysman.mgmt_db_sga_ecm rb,
    sysman.mgmt_db_sga_ecm lp,
    sysman.mgmt_db_sga_ecm jp,
    sysman.mgmt_ecm_gen_snapshot s
  WHERE
  s.snapshot_guid = sga.ecm_snapshot_id
  AND s.snapshot_guid = sp.ecm_snapshot_id
  AND s.snapshot_guid = bc.ecm_snapshot_id
  AND s.snapshot_guid = rb.ecm_snapshot_id
  AND s.snapshot_guid = lp.ecm_snapshot_id
  AND s.snapshot_guid = jp.ecm_snapshot_id
  AND s.target_guid = c.entity_guid
  AND s.is_current  ='Y'
  AND c.entity_type ='oracle_database'
  AND sga.sganame='Total SGA (MB)'
  AND sp.sganame='Shared Pool (MB)'
  AND bc.sganame='Buffered Cache (MB)'
  AND rb.sganame='Redo Buffers (KB)'
  AND lp.sganame='Large Pool (KB)'
  AND jp.sganame='Java Pool (MB)'
  ORDER BY
    c.host_name,c.entity_name;

Output:
HOSTNAME             DB_NAME    Total SGA(MB) SharedPool(%) BufferedCache(%) RedoBuffers(%) LargePool(%) JavaPool(%)
-------------------- ---------- ------------- ------------- ---------------- -------------- ------------ -----------
utest32              BBAIS                864            69                8              1            2          13
                     BBAIS12              864            69               19              1            2           1
                     BUSYI               3472            60               30              6            2           0
                     BUSYT               3136            62               28              7            2           1
                     DWRT                3610            24               43             29            2           0
                     DWRT12              3504            44               47              0            1           1
                     FEINP8I              696            35               30             30            3           1
                     FEINP8T              955            42               27             23            5           2
                     HRDWRT              2819            39               19             37            2           1
                     HRDWRT12            2880            18               74              0            1           1
                     OPSWT                696            35               32             30            2           1
                     PAIST               3098            60               36              0            1           1
                     OPTRALT              788            58               27              3            2           1
                     PRFT6T               496            79               11              0            2           2
                     RMAN1T              1559            45               36             14            3           1
                     RMANBK22            1376            33               55              1            6           1
********************            -------------
sum                                     30813

HOSTNAME             DB_NAME    Total SGA(MB) SharedPool(%) BufferedCache(%) RedoBuffers(%) LargePool(%) JavaPool(%)
-------------------- ---------- ------------- ------------- ---------------- -------------- ------------ -----------
utest35              FEINT                562            89                9              1            0           1
********************            -------------
sum                                       562
...
HOSTNAME             DB_NAME    Total SGA(MB) SharedPool(%) BufferedCache(%) RedoBuffers(%) LargePool(%) JavaPool(%)
-------------------- ---------- ------------- ------------- ---------------- -------------- ------------ -----------
unprod82n0           OPTRALP             1449            49               30             15            0           1
                     PRFT6P              1509            47               34              2            0           1
                     RMAN9P              1575            58               40              0            1           1
********************            -------------
sum                                      4533

HOSTNAME             DB_NAME    Total SGA(MB) SharedPool(%) BufferedCache(%) RedoBuffers(%) LargePool(%) JavaPool(%)
-------------------- ---------- ------------- ------------- ---------------- -------------- ------------ -----------
ucont25              FEINQ                562            89                9              1            0           1
********************            -------------




Monday 1 June 2015

AWR Reports: HowTo track performance changes based on awr text reports


Using oracle function dbms_workload_repository.AWR_REPORT_TEXT with spool and linux comand grep, you can analyze load statistics or even track performance peeks without continuously access to your production database. I know that now we have more and more applications which can deliver such information(for example AWR Datawarehouse). But if you are command line geek… Check this outJ


All we need is dbid, instance id, begin_snap and end_snap_id to generate following string:


SELECT output FROM TABLE(dbms_workload_repository.AWR_REPORT_TEXT(:dbid,:instid,:begin_snap,:end_snap))
 
I created PLSQL coursor to fetch all snap_id’s from given date and time period. For example: create awr reports for day by day between 19:20 and 22:20 (batch load processing)
Here ist my script:


SET serveroutput ON
DECLARE
start_date VARCHAR2(20) := '2015-05-02';
start_hour VARCHAR2(20) := '19:20';
end_hour VARCHAR2(20) := '22:20';
begin_snap NUMBER(10):=0;
dbid NUMBER(10):=0;
instid NUMBER(10):=0;
CURSOR c_snapid
IS
SELECT MIN(snapa) begin_snap,MIN(snapb) end_snap FROM
(SELECT t1.snap_id snapa, t1.begin_interval_time A FROM dba_hist_snapshot t1
WHERE to_char(to_date(start_date||' '||start_hour,'YYYY-MM-DD HH24:MI'),'HH24') >= to_char(begin_interval_time,'HH24')
AND to_char(to_date(start_date||' '||start_hour,'YYYY-MM-DD HH24:MI'),'HH24') < to_char(end_interval_time,'HH24')),
(SELECT t2.snap_id snapb, t2.begin_interval_time b FROM dba_hist_snapshot t2
WHERE to_char(to_date(start_date||' '||end_hour,'YYYY-MM-DD HH24:MI'),'HH24') >= to_char(begin_interval_time,'HH24')
AND to_char(to_date(start_date||' '||end_hour,'YYYY-MM-DD HH24:MI'),'HH24') < to_char(end_interval_time,'HH24'))
WHERE to_char(A,'YYYY-MM-DD')=to_char(b,'YYYY-MM-DD')
AND A>=to_date(start_date||' '||start_hour,'YYYY-MM-DD HH24:MI')
GROUP BY A,b;
i_snapid c_snapid%rowtype;
BEGIN
SELECT dbid INTO dbid FROM v$database;
SELECT instance_number INTO instid FROM v$instance;
OPEN c_snapid;
loop
fetch c_snapid INTO i_snapid;
exit WHEN c_snapid%notfound;
dbms_output.put_line('spool awr_'||i_snapid.begin_snap||'_'||i_snapid.end_snap||'.txt');
dbms_output.put_line('SELECT output FROM TABLE(dbms_workload_repository.AWR_REPORT_TEXT('||dbid||','||instid||','||i_snapid.begin_snap||','||i_snapid.end_snap||'));');
dbms_output.put_line('spool off');
END loop;
CLOSE c_snapid;
exception
WHEN others THEN
dbms_output.put_line (sqlerrm);
END;
/

Output:

spool awr_62663_62666.txt
SELECT output FROM
TABLE(dbms_workload_repository.AWR_REPORT_TEXT(1416487949,1,62663,62666));
spool off
spool awr_62687_62690.txt
SELECT output FROM
TABLE(dbms_workload_repository.AWR_REPORT_TEXT(1416487949,1,62687,62690));
spool off
spool awr_62831_62834.txt
SELECT output FROM
TABLE(dbms_workload_repository.AWR_REPORT_TEXT(1416487949,1,62831,62834));
spool off
spool awr_62951_62954.txt
SELECT output FROM
TABLE(dbms_workload_repository.AWR_REPORT_TEXT(1416487949,1,62951,62954));
spool off
...
spool awr_63095_63098.txt
SELECT output FROM
TABLE(dbms_workload_repository.AWR_REPORT_TEXT(1416487949,1,63095,63098));
spool off
PL/SQL procedure successfully completed.

 
Now you can use grep to find out for example DB Time

/oracle.sw/oracle: grep -i 'DB Time:' awr_69*
awr_62519_62522.txt: DB Time: 97.82 (mins)
awr_62543_62546.txt: DB Time: 80.17 (mins)
awr_62567_62570.txt: DB Time: 1,810.81 (mins)
awr_62591_62594.txt: DB Time: 2,556.04 (mins)
awr_62615_62618.txt: DB Time: 90.30 (mins)
awr_62639_62642.txt: DB Time: 120.58 (mins)
awr_62663_62666.txt: DB Time: 78.02 (mins)
awr_62687_62690.txt: DB Time: 79.95 (mins)
awr_62711_62714.txt: DB Time: 74.97 (mins)
awr_62735_62738.txt: DB Time: 80.06 (mins)
awr_62759_62762.txt: DB Time: 78.12 (mins)
awr_62783_62786.txt: DB Time: 82.12 (mins)
awr_62807_62810.txt: DB Time: 78.77 (mins)
awr_62831_62834.txt: DB Time: 78.10 (mins)
awr_62855_62858.txt: DB Time: 76.02 (mins)
awr_62879_62882.txt: DB Time: 68.94 (mins)
awr_62903_62906.txt: DB Time: 74.38 (mins)
awr_62927_62930.txt: DB Time: 102.24 (mins)
awr_62951_62954.txt: DB Time: 300.03 (mins)
awr_62975_62978.txt: DB Time: 77.88 (mins)
awr_62999_63002.txt: DB Time: 78.58 (mins)
awr_63023_63026.txt: DB Time: 103.45 (mins)


/oracle.sw/oracle/awrrep: grep -i 'Physical read (blocks):' awr_6*
awr_62519_62522.txt: Physical read (blocks): 775.9 803.8
awr_62543_62546.txt: Physical read (blocks): 969.0 455.3
awr_62567_62570.txt: Physical read (blocks): 21,866.0 3,931.0
awr_62591_62594.txt: Physical read (blocks): 73,822.6 11,196.0
awr_62615_62618.txt: Physical read (blocks): 1,029.1 188.3
awr_62639_62642.txt: Physical read (blocks): 1,113.5 628.1
awr_62663_62666.txt: Physical read (blocks): 899.1 547.0
awr_62687_62690.txt: Physical read (blocks): 970.9 1,000.9
awr_62711_62714.txt: Physical read (blocks): 942.2 478.8
awr_62735_62738.txt: Physical read (blocks): 964.3 520.2
awr_62759_62762.txt: Physical read (blocks): 1,002.4 500.6
awr_62783_62786.txt: Physical read (blocks): 981.3 503.0
awr_62807_62810.txt: Physical read (blocks): 972.2 4,575.2
awr_62831_62834.txt: Physical read (blocks): 904.0 6,316.5
awr_62855_62858.txt: Physical read (blocks): 960.3 4,808.0
awr_62879_62882.txt: Physical read (blocks): 952.6 4,764.9
awr_62903_62906.txt: Physical read (blocks): 953.5 4,307.3
awr_62927_62930.txt: Physical read (blocks): 9,890.9 15,813.9
awr_62951_62954.txt: Physical read (blocks): 3,464.7 1,937.7
awr_62975_62978.txt: Physical read (blocks): 994.9 541.3
awr_62999_63002.txt: Physical read (blocks): 899.7 497.0
awr_63023_63026.txt: Physical read (blocks): 1,205.3 1,175.1