问题描述
您好Oracle Masters，我需要您的帮助，就像所有人一样 :-)
我正在测试我的数据库基线，我问自己是否有可能将SQL_ID 2的执行计划放在SQl_ID 1的基线中。
这是我的理论测试: 外部软件使用ORDER BY进行选择。我的客户说，我们不需要这个订单，所以把它拿走。供应商说不，六个月前没有补丁。好的，我是DBA，我必须找到解决方案 :-)
我的测试案例: 一张表，有九个人叫马丁，一个叫杜邦。
SQL> CREATE TABLE zztest(id NUMBER(10) CONSTRAINT zztest_pk_id PRIMARY KEY, nom VARCHAR2(50 CHAR), prenom VARCHAR2(50 CHAR) );
Table created.
SQL> Begin
For i in 1..9
Loop
INSERT INTO zztest VALUES(i, 'MARTIN', 'TOM');
End loop ;
End ;
/
PL/SQL procedure successfully completed.
SQL> INSERT INTO zztest VALUES(10, 'DUPONT', 'David');
SQL> commit;
Commit complete.
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname =>'HR', tabname=>'ZZTEST', cascade=>TRUE);
我在没有订单的情况下执行选择: SQL_ID akk1mjcvrdz1q，计划哈希值: 3582063246。
SQL> SELECT * FROM zztest;
ID NOM PRENOM
---------- ------------------------------
1 MARTIN TOM
2 MARTIN TOM
3 MARTIN TOM
4 MARTIN TOM
5 MARTIN TOM
6 MARTIN TOM
7 MARTIN TOM
8 MARTIN TOM
9 MARTIN TOM
10 DUPONT David
10 rows selected.
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID akk1mjcvrdz1q, child number 0
-------------------------------------
SELECT * FROM zztest
Plan hash value: 3582063246
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | TABLE ACCESS FULL| ZZTEST | 10 | 140 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
13 rows selected.
现在使用顺序: SQL_ID a1k6qugb0hdz1，计划哈希值: 4012859079。
执行计划不同，还可以，有排序。
SQL> SELECT * FROM zztest ORDER BY nom;
ID NOM PRENOM
---------- -------------------------------
10 DUPONT David
2 MARTIN TOM
3 MARTIN TOM
4 MARTIN TOM
6 MARTIN TOM
7 MARTIN TOM
8 MARTIN TOM
9 MARTIN TOM
1 MARTIN TOM
5 MARTIN TOM
10 rows selected.
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID a1k6qugb0hdz1, child number 0
-------------------------------------
SELECT * FROM zztest ORDER BY nom
Plan hash value: 4012859079
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | SORT ORDER BY | | 10 | 140 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| ZZTEST | 10 | 140 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
14 rows selected.
现在，我想将计划3582063246与SQL_ID a1k6qugb0hdz1相关联: 一个计划没有排序选择与订单。
为此，我使用order BY为订单创建基线。
SQL> variable cnt number; SQL> execute :cnt := dbms_spm.load_plans_from_cursor_cache(sql_id => 'a1k6qugb0hdz1'); SQL> select SQL_TEXT, SQL_HANDLE, SIGNATURE, PLAN_NAME, CREATED, ENABLED, ACCEPTED, FIXED from dba_sql_plan_baselines where UPPER(SQL_TEXT) like 'SELECT * FROM ZZTEST%'; SQL_TEXT SQL_HANDLE SIGNATURE PLAN_NAME CREATED ENA ACC FIX -------------------------------------------------------------------------------------------------------------------------------- --------------------------- SELECT * FROM zztest ORDER BY nom SQL_47464290ab153052 5.1359E+18 SQL_PLAN_4fjk2k2pjac2k192f0eef 28-SEP-19 09.52.12.000000000 AM YES YES NO
现在我想在这个基线中添加SQL_ID akk1mjcvrdz1q的执行计划。
SQL> execute :cnt := dbms_spm.load_plans_from_cursor_cache(sql_handle => 'SQL_47464290ab153052', sql_id => 'akk1mjcvrdz1q', plan_hash_value => '3582063246');
哦，问题，基线中只有一个计划，为什么另一个不在这里？我知道共享池中订单的签名和执行计划有匹配，这是为什么它失败了吗？
SQL> select SQL_TEXT, SQL_HANDLE, SIGNATURE, PLAN_NAME, CREATED, ENABLED, ACCEPTED, FIXED from dba_sql_plan_baselines where UPPER(SQL_TEXT) like 'SELECT * FROM ZZTEST%'; SQL_TEXT SQL_HANDLE SIGNATURE PLAN_NAME CREATED ENA ACC FIX --------------------------------------------------------------------------- --- --- --------------- SELECT * FROM zztest ORDER BY nom SQL_47464290ab153052 5.1359E+18 SQL_PLAN_4fjk2k2pjac2k192f0eef 28-SEP-19 10.42.42.000000 AM YES YES NO
你能告诉我怎么做吗？非常感谢您的回答!
专家解答
My client say, we don't need this ORDER BY, so remove it
他们真的sure关于那个？
大多数没有订单的选择by => 等待发生的错误!
无论如何，我不确定为什么您没有将第二个计划作为基准加载。
“对我有用”:
无论如何，这种方法无济于事。没有排序的计划会更改查询的行为。所以数据库不会使用它:
正如注释所说，它不能使用此基线。
如果您必须删除订单 (我会大力推迟)，SQL翻译框架将允许您拦截查询并重写它。
https://docs.oracle.com/en/database/oracle/oracle-database/19/drdaa/SQL-translation-framework-overview.html#GUID-C7814DA0-96BA-4D15-A348-6F0960D4475F
他们真的sure关于那个？
大多数没有订单的选择by => 等待发生的错误!
无论如何，我不确定为什么您没有将第二个计划作为基准加载。
“对我有用”:
CREATE TABLE zztest(id NUMBER(10) CONSTRAINT zztest_pk_id PRIMARY KEY, nom VARCHAR2(50 CHAR), prenom VARCHAR2(50 CHAR) );
Begin
For i in 1..9
Loop
INSERT INTO zztest VALUES(i, 'MARTIN', 'TOM');
End loop ;
End ;
/
INSERT INTO zztest VALUES(10, 'DUPONT', 'David');
commit;
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname =>user, tabname=>'ZZTEST', cascade=>TRUE);
SELECT * FROM zztest;
SELECT * FROM zztest ORDER BY nom;
variable cnt number;
execute :cnt := dbms_spm.load_plans_from_cursor_cache(sql_id => 'a1k6qugb0hdz1');
select sql_text,
sql_handle,
signature,
plan_name,
created,
enabled,
accepted,
fixed
from dba_sql_plan_baselines
where upper (sql_text) like 'SELECT * FROM ZZTEST%';
SQL_TEXT SQL_HANDLE SIGNATURE PLAN_NAME CREATED ENABLED ACCEPTED FIXED
SELECT * FROM zztest ORDER BY nom SQL_47464290ab153052 5135865614175645778 SQL_PLAN_4fjk2k2pjac2k192f0eef 03-OCT-2019 10.17.57.000000000 YES YES NO
execute :cnt := dbms_spm.load_plans_from_cursor_cache(sql_handle => 'SQL_47464290ab153052', sql_id => 'akk1mjcvrdz1q', plan_hash_value => '3582063246');
select sql_text,
sql_handle,
signature,
plan_name,
created,
enabled,
accepted,
fixed
from dba_sql_plan_baselines
where upper (sql_text) like 'SELECT * FROM ZZTEST%';
SQL_TEXT SQL_HANDLE SIGNATURE PLAN_NAME CREATED ENABLED ACCEPTED FIXED
SELECT * FROM zztest ORDER BY nom SQL_47464290ab153052 5135865614175645778 SQL_PLAN_4fjk2k2pjac2k192f0eef 03-OCT-2019 10.17.57.000000000 YES YES NO
SELECT * FROM zztest ORDER BY nom SQL_47464290ab153052 5135865614175645778 SQL_PLAN_4fjk2k2pjac2kb63e4ae3 03-OCT-2019 10.17.57.000000000 YES YES NO 无论如何，这种方法无济于事。没有排序的计划会更改查询的行为。所以数据库不会使用它:
exec :cnt := dbms_spm.drop_sql_plan_baseline('SQL_47464290ab153052','SQL_PLAN_4fjk2k2pjac2k192f0eef');
set serveroutput off
SELECT * FROM zztest ORDER BY nom;
select *
from table(dbms_xplan.display_cursor(null, null, 'BASIC LAST +NOTE'));
PLAN_TABLE_OUTPUT
EXPLAINED SQL STATEMENT:
------------------------
SELECT * FROM zztest ORDER BY nom
Plan hash value: 4012859079
-------------------------------------
| Id | Operation | Name |
-------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT ORDER BY | |
| 2 | TABLE ACCESS FULL| ZZTEST |
-------------------------------------
Note
-----
- Failed to use SQL plan baseline for this statement 正如注释所说，它不能使用此基线。
如果您必须删除订单 (我会大力推迟)，SQL翻译框架将允许您拦截查询并重写它。
https://docs.oracle.com/en/database/oracle/oracle-database/19/drdaa/SQL-translation-framework-overview.html#GUID-C7814DA0-96BA-4D15-A348-6F0960D4475F
文章转载自ASKTOM，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
