Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Aggregate and Insert

I am querying mgmt views on myManagement repository database to get cpu and memory values. The output is based on environment (PRD1, TST1, TST2 etc.). I want specific values from the output to be aggregated and then inserted in to a new table.

SELECT
COALESCE(MAX(CASE WHEN tp.property_name = 'orcl_gtp_department' THEN tp.property_value END), '-') AS TENANT,
COALESCE(MAX(CASE WHEN tp.property_name = 'orcl_gtp_cost_center' THEN tp.property_value END), '-') AS TENANT_ID,
COALESCE(MAX(CASE WHEN tp.property_name = 'orcl_gtp_line_of_bus' THEN tp.property_value END), '-') AS APPID,
tp.target_name AS DB,
COALESCE(MAX(CASE WHEN tp.property_name = 'orcl_gtp_comment' THEN tp.property_value END), '-') AS ENVIRONMENT,
COALESCE(MAX(CASE WHEN tp.property_name = 'orcl_gtp_location' AND tp.property_value IN ('RCC - Test', 'WSDC - Test', 'RCC - Prod', 'WSDC - Prod', 'WSDC - SVP') THEN tp.property_value END), '-') AS LOCATION,
COALESCE(MAX(CASE WHEN pp.name = 'cpu_count' THEN pp.value END), '-') AS "DB CPU_COUNT",
COALESCE(MAX(CASE WHEN pp.name = 'cpu_count' THEN pp.value END), '-') / 2 AS "CPU CORES",
COALESCE(MAX(CASE WHEN pp.name = 'sga_max_size' THEN ROUND(pp.value / POWER(1024, 3), 2) END), 0) AS "SGA MEMORY(GB)",
COALESCE(MAX(CASE WHEN pp.name = 'pga_aggregate_limit' THEN ROUND(pp.value / POWER(1024, 3), 2) END), 0) AS "PGA MEMORY(GB)",
COALESCE(MAX(CASE WHEN pp.name = 'sga_max_size' THEN ROUND(pp.value / POWER(1024, 3), 2) END), 0) + COALESCE(MAX(CASE WHEN pp.name = 'pga_aggregate_limit' THEN ROUND(pp.value / POWER(1024, 3), 2) END), 0) AS "Total Memory(GB)"
FROM
sysman.mgmt$target_properties tp
LEFT JOIN
sysman.MGMT$DB_INIT_PARAMS pp ON tp.target_name = pp.target_name
WHERE
tp.property_name IN ('orcl_gtp_department', 'orcl_gtp_cost_center', 'orcl_gtp_line_of_bus', 'orcl_gtp_comment')
OR (tp.property_name = 'orcl_gtp_location' AND tp.property_value IN ('RCC - Test', 'WSDC - Test', 'RCC - Prod', 'WSDC - Prod', 'WSDC - SVP'))
GROUP BY
tp.target_name
HAVING
MAX(CASE WHEN tp.property_name = 'orcl_gtp_location' AND tp.property_value IN ('RCC - Test', 'WSDC - Test', 'RCC - Prod', 'WSDC - Prod', 'WSDC - SVP') THEN 1 ELSE 0 END) = 1
ORDER BY
TENANT,ENVIRONMENT;

Output of this command is

TENANT TENANT_ID APPID DB ENVIRONMENT LOCATION DB_CPU_COUNT CPU_CORES SGA MEMORY PGA MEMORY TOTAL mEMORY


CFX 4 A00295 CFXD3_CFXD31 TST1 RCC - Test 2 1 24 16 40
CFX 4 A00295 CFXU1_CFXU11 TST2 WSDC - Test 6 3 24 16 40
CFX 4 A00295 CFXU1_CFXU12 TST2 WSDC - Test 6 3 24 16 40
EBS 19 A0009A EBSD3_EBSD311 TST1 RCC - Test 8 4 16 16 32
EBS 19 A0009A EBSD4_EBSD411 TST1 RCC - Test 8 4 16 16 32
EBS 19 A0009A EBST1_EBST111 TST2 WSDC - Test 10 5 16 30 46

My final output once the insert is done should be

TENANT TENANT_ID APPID ENVIRONMENT LOCATION CPU_CORES TOTAL_MEMORY_GB DATAC1_GB RECOC1_GB ZFS_ALLOCATION_GB
-------------------------------------------------------------------------------------------------------------------------------------------------
CFX 4 A00295 TST1 RCC - Test 1 40
CFX 4 A00295 TST2 WSDC - Test 6 80
EBS 19 A0009A TST1 RCC - Test 8 64
EBS 19 A0009A TST2 WSDC - Test 5 46

First, it should look for tenant - as you can see there are 2 tenants EBS and CFX.
Second, after lookingh for tenant, it should look for environment, as you can see there are different environments like TST1, TST2 etc..
Once this criteria is met, the insert should happen based again on the following conditions
for example, for EBS tenant and environment being TST1, CPU_CORES should be added - it should be 4+4 = 8
if tenant is CFX and environment is TST1, then CPU_CORES will be 1
Similarly for other tenant and environment.
For example, for EBS tenant and environment being TST2, CPU_CORES should be added - it should be 5
Similary for Total memory
So the criteria should always be based on Tenant and then environment.

Comments
Post Details
Added on Apr 2 2024
1 comment
91 views