1] SQL PATCH dbms_sqldiag
I needed to speed up a query executed many many times around the clock.
Just a Select Distinct on a 3 Millions rows table that fetchs only 15 rows with a Fulll Table Scan. That table is also constantly Updated !
Hence, creating an Index helped to divide the Logical IOs by 10, but end up in a regression, a rare case of the penalty cost of maintaining the Index.
I, then, think of Caching the Result, but the too many Updates invalidated the Result Cache too often :
LIOs was divided by 20, but LIOs increased slowly as Result cache was refreshed.
Last try was to use Parallelism for the FTS.
The wonderful new feature SQL_PATCH can modify the way the Optimizer choose to execute the SQL query. Without any Code modification and as simple as that :
1.1 The query to speed up
- original SQL
set pages 5000 lines 150
set timing on ;
set autotrace traceonly ;
SELECT DISTINCT "TABLE_1"."COL_1" FROM <SCHEMA_NAME>."TABLE_1"
ORDER BY "TABLE_1"."COL_1" ASC;
15 rows selected.
Elapsed: 00:00:00.99
64240 consistent gets
0 physical reads
- Parallelized SQL is reading disks ( Direct Path Read )
SELECT /*+ PARALLEL (TABLE_1,4) */ DISTINCT "TABLE_1"."COL_1" FROM <SCHEMA_NAME>."TABLE_1" ORDER BY "TABLE_1"."COL_1" ASC;
Elapsed: 00:00:00.68
- Degree of Parallelism is 4 because of table property
64243 consistent gets
64157 physical reads
- Parallelized SQL without any Disk Reads
alter table <SCHEMA_NAME>."TABLE_1" cache ;
Table altered.
SQL> SELECT /*+ PARALLEL (TABLE_1,4) */ DISTINCT "TABLE_1"."COL_1"
FROM <SCHEMA_NAME>."TABLE_1" ORDER BY "TABLE_1"."COL_1"
ASC;
Elapsed: 00:00:00.58
Plan hash value: 3097948216
- Degree of Parallelism is 4 because of table property
65587 consistent gets
0 physical reads
1.2 Patching the SQL to force Parallel Reads !
set serveroutput on
variable x varchar2(100);
exec :x:=dbms_sqldiag.create_sql_patch(sql_id=>'<SQL_ID>',
hint_text=>'PARALLEL(4)', name=> 'SQL_Patch_<SQL_ID>');
- Check New Execution Plan
select ceil(px_servers_executions/executions) , sql_id , plan_hash_value , parsing_schema_name , executions , ceil(buffer_gets/executions) , disk_reads , first_load_time , rows_processed , elapsed_time/1000000/executions Sec , last_load_time , sql_text from v$sql where sql_id = '<SQL_ID>'
4 PX <SQL_ID> 3097948216 <SCHEMA_NAME>
552 63141 3
2026-07-05/21:13:25 8280 0.75 sec.
1.3 Failback
set serveroutput on
exec DBMS_SQLDIAG.drop_sql_patch(name => 'SQL_Patch_<SQL_ID>');
alter table <SCHEMA_NAME>."TABLE_1" nocache ;
