Hello All,
I want two different queries to use the same plan in Oracle 19c. Test code is as follows.
CREATE TABLE TAB_A
AS
SELECT * FROM DBA_USERS;
CREATE TABLE TAB_B
AS
SELECT T.* FROM DBA_OBJECTS T, (SELECT * FROM DUAL CONNECT BY LEVEL <= 10) WHERE OWNER != 'PUBLIC';
ALTER TABLE TAB_A MODIFY USERNAME PRIMARY KEY;
ALTER TABLE TAB_B ADD CONSTRAINT cnt_test1 FOREIGN KEY (OWNER) REFERENCES TAB_A(USERNAME);
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TAB_A');
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TAB_B');
SELECT USERNAME, COUNT(OBJECT_ID) CNT FROM TAB_A A LEFT JOIN TAB_B B ON A.USERNAME = B.OWNER GROUP BY USERNAME;
SELECT USERNAME, (SELECT COUNT(OBJECT_ID) FROM TAB_B B WHERE B.OWNER = A.USERNAME) CNT FROM TAB_A A;
SELECT SQL_ID, PLAN_HASH_VALUE FROM V$SQLSTATS WHERE SQL_TEXT LIKE 'SELECT USERNAME, COUNT(OBJECT_ID) CNT FROM TAB_A A LEFT JOIN TAB_B B ON A.USERNAME = B.OWNER GROUP BY USERNAME';
>>5v63ymhqpda8s 1502400479
SELECT SQL_ID, PLAN_HASH_VALUE FROM V$SQLSTATS WHERE SQL_TEXT LIKE 'SELECT USERNAME, (SELECT COUNT(OBJECT_ID) FROM TAB_B B WHERE B.OWNER = A.USERNAME) CNT FROM TAB_A A';
>>g1ptcnubcwtu7 592914736
begin DBMS_OUTPUT.PUT_LINE(dbms_spm.load_plans_from_cursor_cache(sql_id=>'g1ptcnubcwtu7')); end;
begin
DBMS_OUTPUT.PUT_LINE(DBMS_SPM.alter_sql_plan_baseline(
sql_handle => 'SQL_b0dbd9363cb6b591',
plan_name => 'SQL_PLAN_b1qyt6sybddcjd9dfaf18',
attribute_name => 'enabled',
attribute_value => 'NO'
));
end;
SELECT sql_handle, plan_name, enabled, accepted FROM DBA_SQL_PLAN_BASELINES WHERE TRUNC(CREATED) = TRUNC(SYSDATE);
>>SQL_b0dbd9363cb6b591 SQL_PLAN_b1qyt6sybddcjd9dfaf18 NO YES
begin DBMS_OUTPUT.PUT_LINE(SYS.DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(SQL_ID=>'5v63ymhqpda8s', PLAN_HASH_VALUE=>'1502400479', SQL_HANDLE=>'SQL_b0dbd9363cb6b591')); end;
SELECT sql_handle, plan_name, enabled, accepted FROM DBA_SQL_PLAN_BASELINES WHERE TRUNC(CREATED) = TRUNC(SYSDATE);
>>SQL_b0dbd9363cb6b591 SQL_PLAN_b1qyt6sybddcjd9dfaf18 NO YES
>>SQL_b0dbd9363cb6b591 SQL_PLAN_b1qyt6sybddcj3f65d065 YES YES
The following query still use the first plan.
SELECT USERNAME, (SELECT COUNT(OBJECT_ID) FROM TAB_B B WHERE B.OWNER = A.USERNAME) CNT FROM TAB_A A;
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
- Failed to use SQL plan baseline for this statement
So what is your opinion?
Thanks