Joining a column from another complex table in sql

Question

I have an sql like this:

 SELECT inst_id,
         CASE WHEN component IS NULL THEN 'others' ELSE component END component,
         ROUND (SUM (size_mb), 1) size_mb,
         ROUND (SUM (used_mb), 1) used_mb
    FROM (SELECT inst_id,
                 CASE
                    WHEN name = 'buffer_cache' THEN 'db_buffer_cache'
                    WHEN name = 'log_buffer' THEN 'log_buffer'
                    ELSE pool
                 END
                    component,
                 ( (bytes / 1024) / 1024) size_mb,
                 CASE
                    WHEN name = 'buffer_cache'
                    THEN
                       (  (  (  bytes
                              -   (SELECT COUNT (*)
                                     FROM gv$bh
                                    WHERE     inst_id = x.inst_id
                                          AND status = 'free')
                                * (SELECT t.VALUE
                                     FROM gv$parameter t
                                    WHERE     t.inst_id = x.inst_id
                                          AND t.name = 'db_block_size'))
                           / 1024)
                        / 1024)
                    WHEN name != 'free memory'
                    THEN
                       ( (bytes / 1024) / 1024)
                 END
                    used_mb
            FROM gv$sgastat x)
GROUP BY component, inst_id
ORDER BY inst_id

The output:

1   db_buffer_cache 28288   28287,8
1   java pool   192 
1   large pool  192 128,7
1   log_buffer  171,5   171,5
1   shared pool 3776    3039,8
1   others  2,2 2,2
2   db_buffer_cache 28288   28287,9
2   java pool   64  
2   large pool  384 251,3
2   log_buffer  171,5   171,5
2   shared pool 4096    3502,5
2   others  2,2 2,2

The problem is, i have another view named gv$instance which has inst_id parameter like the others. The table looks like this:

1    a1
2    a2
3    a3

I want "instance_name" variable from that table inside my query. So my output will be like:

1   a1    db_buffer_cache   28288   28287,8
1   a1    java pool 192 
1   a1    large pool    192 128,7
1   a1    log_buffer    171,5   171,5
1   a1    shared pool   3776    3039,8
1   a1    others    2,2 2,2
2   a2    db_buffer_cache   28288   28287,9
2   a2    java pool 64  
2   a2    large pool    384 251,3
2   a2    log_buffer    171,5   171,5
2   a2    shared pool   4096    3502,5
2   a2    others    2,2 2,2

Answer 1

Try something like this:

SELECT temp.inst_id, 
       gv.instance_name,
         CASE WHEN component IS NULL THEN 'others' ELSE component END component,
         ROUND (SUM (size_mb), 1) size_mb,
         ROUND (SUM (used_mb), 1) used_mb
    FROM (SELECT inst_id,
                 CASE
                    WHEN name = 'buffer_cache' THEN 'db_buffer_cache'
                    WHEN name = 'log_buffer' THEN 'log_buffer'
                    ELSE pool
                 END
                    component,
                 ( (bytes / 1024) / 1024) size_mb,
                 CASE
                    WHEN name = 'buffer_cache'
                    THEN
                       (  (  (  bytes
                              -   (SELECT COUNT (*)
                                     FROM gv$bh
                                    WHERE     inst_id = x.inst_id
                                          AND status = 'free')
                                * (SELECT t.VALUE
                                     FROM gv$parameter t
                                    WHERE     t.inst_id = x.inst_id
                                          AND t.name = 'db_block_size'))
                           / 1024)
                        / 1024)
                    WHEN name != 'free memory'
                    THEN
                       ( (bytes / 1024) / 1024)
                 END
                    used_mb
            FROM gv$sgastat x) temp, gv$instance gv
where gv.inst_id = temp.inst_id 
GROUP BY component, gv.inst_id, gv.instance_name
ORDER BY gv.inst_id

Answer 2

select source.inst_id,myview.inst_name
         source.component,
         source.size_mb,
         source.used_mb
from
(
SELECT inst_id,
         CASE WHEN component IS NULL THEN 'others' ELSE component END component,
         ROUND (SUM (size_mb), 1) size_mb,
         ROUND (SUM (used_mb), 1) used_mb
    FROM (SELECT inst_id,
                 CASE
                    WHEN name = 'buffer_cache' THEN 'db_buffer_cache'
                    WHEN name = 'log_buffer' THEN 'log_buffer'
                    ELSE pool
                 END
                    component,
                 ( (bytes / 1024) / 1024) size_mb,
                 CASE
                    WHEN name = 'buffer_cache'
                    THEN
                       (  (  (  bytes
                              -   (SELECT COUNT (*)
                                     FROM gv$bh
                                    WHERE     inst_id = x.inst_id
                                          AND status = 'free')
                                * (SELECT t.VALUE
                                     FROM gv$parameter t
                                    WHERE     t.inst_id = x.inst_id
                                          AND t.name = 'db_block_size'))
                           / 1024)
                        / 1024)
                    WHEN name != 'free memory'
                    THEN
                       ( (bytes / 1024) / 1024)
                 END
                    used_mb
            FROM gv$sgastat x)
GROUP BY component, inst_id
) source inner join gv$instance myview on source.inst_id=myview.inst_id
order by source.inst_id