dbms_sqldiag – Keep Learning Keep Living

Hi, We discovered that one of the query run by OCI database management is taking too long and consume a lot of resources. Query is basically getting tablespace information via AWR data but due to some optimization issues it chooses very interesting execution plan. Here is the query:

WITH latest_snap_id AS (
    SELECT
        snap_id,
        dbid
    FROM
        (
            SELECT
                MAX(snap_id) snap_id,
                dbid
            FROM
                dba_hist_snapshot
            WHERE
                ( end_interval_time - snap_timezone ) > ( sys_extract_utc(systimestamp) - INTERVAL '1' DAY )
            GROUP BY
                dbid
        )
    WHERE
        ROWNUM = 1
), tb_usage as (
    SELECT
        tablespace_name,
        contents,
        round((tablespace_size * blocksize) /(1024 * 1024 ), 4)   as  allocated_space,
        round((tablespace_usedsize * blocksize) /(1024 * 1024 ), 4) as used_space
    FROM
        dba_hist_tbspc_space_usage tb_usage,
        sys.ts$        all_tbs,
        dba_tablespaces dba_tbs,
        latest_snap_id
    WHERE
      ts# = tablespace_id
      AND tb_usage.snap_id = latest_snap_id.snap_id
      and tb_usage.dbid = latest_snap_id.dbid
      AND dba_tbs.tablespace_name = all_tbs.name
 )
SELECT
    round(SUM(t.allocated_space)/1024, 2) AS "StorageAllocated",
    round(SUM(t.used_space)/1024, 2) AS "StorageUsed"
from
    tb_usage t
having
        count(*) > 0

WITH latest_snap_id AS (

SELECT

snap_id,

dbid

FROM

(

SELECT

MAX(snap_id) snap_id,

dbid

FROM

dba_hist_snapshot

WHERE

( end_interval_time - snap_timezone ) > ( sys_extract_utc(systimestamp) - INTERVAL '1' DAY )

GROUP BY

dbid

)

WHERE

ROWNUM = 1

), tb_usage as (

SELECT

tablespace_name,

contents,

round((tablespace_size * blocksize) /(1024 * 1024 ), 4) as allocated_space,

round((tablespace_usedsize * blocksize) /(1024 * 1024 ), 4) as used_space

FROM

dba_hist_tbspc_space_usage tb_usage,

sys.ts$ all_tbs,

dba_tablespaces dba_tbs,

latest_snap_id

WHERE

ts# = tablespace_id

AND tb_usage.snap_id = latest_snap_id.snap_id

and tb_usage.dbid = latest_snap_id.dbid

AND dba_tbs.tablespace_name = all_tbs.name

)

SELECT

round(SUM(t.allocated_space)/1024, 2) AS "StorageAllocated",

round(SUM(t.used_space)/1024, 2) AS "StorageUsed"

from

tb_usage t

having

count(*) > 0

What…

Tag: dbms_sqldiag

Long Running Query by OCI Performance Hub