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
******************** -------------
Thursday, 4 June 2015
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;
/
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
Subscribe to:
Posts (Atom)