Long Running Query by OCI Performance Hub Mustafa Kalaycı, 2026-01-27 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: Oracle PL/SQL 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 123456789101112131415161718192021222324252627282930313233343536373839404142 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 thaving count(*) > 0 What can we do? This is run by OCI so I have zero chance to change the query so I examined the query and tried a few tricks and the easiest one is adding MATERIALIZE hint to the first with clause select, “latest_snap_id”. that helps the optimizer to create a better execution plan and as a result faster execution with less resources. so it will be like: WITH latest_snap_id AS ( SELECT <strong>/*+ materialize */</strong> snap_id, ... 1234 WITH latest_snap_id AS ( SELECT <strong>/*+ materialize */</strong> snap_id,... but I have already said that I cannot change the query. Thanks to Oracle database it provides many tools us and one of them is SQL Patching, DBMS_SQLDIAG package. sql patching allows us to add HINTs to existing queries whenever they executed. all you need to do is finding the SQL_ID and give the hint that you want to add. Let’s give a try: Oracle PL/SQL declare xx varchar2(8000); begin xx := dbms_sqldiag.create_sql_patch( sql_id =>'dqj7bkgy6urkn', -- your sql id here hint_text => '/*+ MATERIALIZE */ ', name => 'TBS_QUERY_TUNING', description => 'this hint will help the sql'); dbms_output.put_line(xx); end; / 1234567891011 declare xx varchar2(8000);beginxx := dbms_sqldiag.create_sql_patch( sql_id =>'dqj7bkgy6urkn', -- your sql id here hint_text => '/*+ MATERIALIZE */ ', name => 'TBS_QUERY_TUNING', description => 'this hint will help the sql');dbms_output.put_line(xx);end;/ how about the result? well, it didn’t work! because we want to add the hint to the first with clause not to the main SQL. DBMS_SQLDIAG will add the hint to the final select statement and in our sql it is: ... SELECT -- hint will be added here!!!! round(SUM(t.allocated_space)/1024, 2) AS "StorageAllocated", round(SUM(t.used_space)/1024, 2) AS "StorageUsed" from tb_usage t ... 1234567 ...SELECT -- hint will be added here!!!! round(SUM(t.allocated_space)/1024, 2) AS "StorageAllocated", round(SUM(t.used_space)/1024, 2) AS "StorageUsed"from tb_usage t... adding hint to main query will just materialized entire output and it will not help! So how can we add hint to first with clause? we cannot but we can tell to the optimizer add this hint to a specific block by using QUERY BLOCK name. to find query block name check the execution plan. Use dbms_xplan.display_cursor to get full execution plan and you will see a line as this: ... Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$0FF60CE5 7 - SEL$1 / LATEST_SNAP_ID@SEL$5 8 - SEL$1 9 - SEL$641071AC / from$_subquery$_001@SEL$1 10 - SEL$641071AC 12 - SEL$641071AC / AWR_CDB_SNAPSHOT@SEL$4 ... 1234567891011 ...Query Block Name / Object Alias (identified by operation id):------------------------------------------------------------- 1 - SEL$0FF60CE5 7 - SEL$1 / LATEST_SNAP_ID@SEL$5 8 - SEL$1 9 - SEL$641071AC / from$_subquery$_001@SEL$1 10 - SEL$641071AC 12 - SEL$641071AC / AWR_CDB_SNAPSHOT@SEL$4... number 7 shows the block name: 7 – SEL$1 / LATEST_SNAP_ID@SEL$5 SEL$1 is our block name for latest_snap_id query block. Based on that information our sql patch should be like: declare xx varchar2(8000); begin xx := dbms_sqldiag.create_sql_patch( sql_id =>'dqj7bkgy6urkn', -- your sql id here hint_text => '/*+ MATERIALIZE(@"SEL$1") */ ', name => 'TBS_QUERY_TUNING', description => 'this hint will help the sql'); dbms_output.put_line(xx); end; / 1234567891011 declare xx varchar2(8000);beginxx := dbms_sqldiag.create_sql_patch( sql_id =>'dqj7bkgy6urkn', -- your sql id here hint_text => '/*+ MATERIALIZE(@"SEL$1") */ ', name => 'TBS_QUERY_TUNING', description => 'this hint will help the sql');dbms_output.put_line(xx);end;/ that is it. now it is working. I hope this helps and please consider using HINTs should be the last solution. Wish you all healthy, beautiful days. 19c 21c 23ai 26ai add hintdbms_sqldiagoci performance hubsql patch