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
******************** -------------
No comments:
Post a Comment