oracle dbms_sqltune 使用

创建测试表

CREATE TABLE test_table (id NUMBER PRIMARY KEY,event_date DATE,value NUMBER
);

插入测试数据

DECLAREi NUMBER;
BEGINFOR i IN 1..1000000 LOOPINSERT INTO test_table (id, event_date, value)VALUES (i, SYSDATE - MOD(i, 365), DBMS_RANDOM.VALUE(1, 1000));END LOOP;COMMIT;
END;
/PL/SQL procedure successfully completed.

查询指定日期的记录

SELECT *   
FROM test_table  
WHERE TRUNC(event_date) = TO_DATE('2025-02-04', 'YYYY-MM-DD') ;

使用 DBMS_SQLTUNE 进行优化

我们可以使用 DBMS_SQLTUNE 来分析和优化这个查询
创建一个SQL Tuning Task

DECLAREl_sql_tune_task_id VARCHAR2(100);
BEGINl_sql_tune_task_id := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text => 'SELECT * FROM test_table WHERE TRUNC(event_date) = TO_DATE(''2025-02-04'', ''YYYY-MM-DD'')',user_name => 'test', -- 替换为你的用户名scope => 'COMPREHENSIVE',time_limit => 60,task_name => 'tune_test_table_query',description => 'Tuning task for test_table query');DBMS_OUTPUT.PUT_LINE('Task ID: ' || l_sql_tune_task_id);
END;
/PL/SQL procedure successfully completed.

执行SQL Tuning Task

BEGINDBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'tune_test_table_query');
END;
/
PL/SQL procedure successfully completed.

查看优化建议

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('tune_test_table_query') AS recommendations FROM DUAL;RECOMMENDATIONS
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : tune_test_table_query
Tuning Task Owner  : TEST
Workload Type	   : Single SQL Statement
Scope		   : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status  : COMPLETED
Started at	   : 02/11/2025 09:51:15
Completed at	   : 02/11/2025 09:51:16RECOMMENDATIONS
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Schema Name   : TEST
Container Name: CDB$ROOT
SQL ID	      : 45hf7qdyz87kz
SQL Text      : SELECT * FROM test_table WHERE TRUNC(event_date) =TO_DATE('2025-02-04', 'YYYY-MM-DD')-------------------------------------------------------------------------------
FINDINGS SECTION (3 findings)
-------------------------------------------------------------------------------RECOMMENDATIONS
--------------------------------------------------------------------------------
1- Statistics Finding
---------------------Index "TEST"."SYS_C007727" was not analyzed.Recommendation--------------- Consider collecting optimizer statistics for this index.execute dbms_stats.gather_index_stats(ownname => 'TEST', indname =>'SYS_C007727', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);RationaleRECOMMENDATIONS
-----------------------------------------------------------------------------------------The optimizer requires up-to-date statistics for the index in order toselect a good execution plan.2- Statistics Finding
---------------------Table "TEST"."TEST_TABLE" was not analyzed.Recommendation--------------- Consider collecting optimizer statistics for this table.RECOMMENDATIONS
--------------------------------------------------------------------------------execute dbms_stats.gather_table_stats(ownname => 'TEST', tabname =>'TEST_TABLE', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'FOR ALL COLUMNS SIZE AUTO');Rationale---------The optimizer requires up-to-date statistics for the table in order toselect a good execution plan.3- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------RECOMMENDATIONS
--------------------------------------------------------------------------------The predicate TRUNC("TEST_TABLE"."EVENT_DATE")=TO_DATE(' 2025-02-0400:00:00', 'syyyy-mm-dd hh24:mi:ss') used at line ID 1 of the execution plancontains an expression on indexed column "EVENT_DATE". This expressionprevents the optimizer from selecting indices on table "TEST"."TEST_TABLE".Recommendation--------------- Rewrite the predicate into an equivalent form to take advantage ofindices. Alternatively, create a function-based index on the expression.-------------------------------------------------------------------------------RECOMMENDATIONS
--------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------1- Original
-----------
Plan hash value: 3979868219--------------------------------------------------------------------------------
| Id  | Operation	  | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	       |  3603 |   123K|  1530	 (2)| 00:00:01 |RECOMMENDATIONS
--------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS FULL| TEST_TABLE |  3603 |   123K|  1530	 (2)| 00:00:01 |
--------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------1 - filter(TRUNC(INTERNAL_FUNCTION("EVENT_DATE"))=TO_DATE('2025-02-04 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

创建索引并查询执行计划

CREATE INDEX idx_event_date ON test_table(event_date);

根据 DBMS_SQLTUNE 提供的建议，你可以应用这些优化建议来改进查询性能。例如，可能会建议创建新的索引、修改查询语句等。
优化建议: 查看 DBMS_SQLTUNE 提供的优化建议，了解哪些方面可以改进。
执行计划: 比较优化前后的执行计划，看看是否有显著变化。
执行时间: 比较优化前后的执行时间，看看性能是否有提升。

SQL_ID方式

DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_id => '8v8surz5t2173',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => '8v8surz5t2173_tuning_task111',
description => 'Tuning task1 for statement 8v8surz5t2173');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '8v8surz5t2173_tuning_task111');SET LONG 10000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('8v8surz5t2173_tuning_task111')
FROM   DUAL;查询优化状态
SELECT task_name,status FROM USER_ADVISOR_TASKS WHERE task_name ='8v8surz5t2173_tuning_task111';
SQL> SELECT task_name,status FROM USER_ADVISOR_TASKS WHERE task_name ='8v8surz5t2173_tuning_task111';TASK_NAME                                                    STATUS
------------------------------------------------------------ ----------------------
8v8surz5t2173_tuning_task111                                 COMPLETED删除
SQL> EXEC DBMS_SQLTUNE.DROP_TUNING_TASK('8v8surz5t2173_tuning_task111');PL/SQL procedure successfully completed.SQL> SELECT task_name,status FROM USER_ADVISOR_TASKS WHERE task_name ='8v8surz5t2173_tuning_task111';no rows selected