ORACLE adds HINT to SQL statements through SPM

A SQL, whose execution plan is fixed through SPM, can be achieved through DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE. You can also use this function to fix the execution plan by adding HINT to the original SQL without modifying it.

DB VERSION: ORACLE 11.2.0.4
OS: CENTOS 6.6

For example:
Original SQL index:
SELECT * FROM SCOTT.TB_SPM WHERE OBJECT_ID=10;

I want to make it perform a full table scan by adding HINT:
SELECT /*+FULL(TB_SPM)*/* FROM SCOTT.TB_SPM WHERE OBJECT_ID=10;

Query in V$SQL, the SQL_ID of the original SQL=064qcdmgt6thw, the SQL_ID of the SQL with HINT=ahdtbgvsd3bht,PLAN_HASH_VALUE=970476072.
Execute the following:
DECLARE
CNT NUMBER;
V_SQL CLOB;
BEGIN
--Get the original statement SQL text
SELECT SQL_FULLTEXT INTO V_SQL FROM V$SQL WHERE SQL_ID = '064qcdmgt6thw' AND ROWNUM=1;
--Add HINT SQL_ID and PLAN_HASH_VALUE of SQL to fix the SQL of the original statement
CNT := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(SQL_ID                                                                   PLAN_HASH_VALUE => 970476072,
Fix the execution plan with HINT on the original statement. Execute the original statement and confirm whether it is fixed in the PLAN_HASH_VALUE column and SQL_PLAN_BASELINE column of V$SQL.

During the test, it was found that for some SQL containing bind variables, the SQL_ID and PLAN_HASH_VALUE of the constant SQL cannot be fixed. At this time, you can try to use EXECUTE IMMEDIATE to generate SQL containing bind variables.
For example:
DECLARE
V_SQL VARCHAR2(3000);
BEGIN
V_SQL := 'SELECT /*+FULL(TB_SPM)*/* FROM SCOTT.TB_SPM WHERE OBJECT_ID=:1';
EXECUTE IMMEDIATE V_SQL
USING 10;
END;
or
var v number;
exec :v :=10
SELECT /*+FULL(TB_SPM)*/* FROM SCOTT.TB_SPM WHERE OBJECT_ID=:V;