Forgot to mention.. Also is it possible to set force_matching via SQL PATCH?
Thanks,
Rakesh T
On Thu, Jan 28, 2021 at 11:25 AM Rakesh T <aryan.goti@xxxxxxxxx> wrote:
Hi Listers,
The DB version is 12.1.
Application is having a sql query as below...
WITH temp
AS (
SELECT /*+ use_nl(E LO) */
EU.UserID IS NULL
OR (
EU.UserID IS NOT NULL
AND EU.UserStatusCode <> 90011
)
)
......
The above takes some 20 seconds to execute.
When implementing the below hints, the SQL runs in 2seconds.
WITH temp
AS (
SELECT */*+ use_nl(E LO) */* CASE
WHEN (
EU.UserID IS NULL
OR (
EU.UserID IS NOT NULL
AND EU.UserStatusCode <> 90011
)
)
......
Application cannot introduce the code change and hence I was thinking of
implementing SQLPATCH for this SQL. The outline details for the SQL is
attached. Can someone help me to understand how do we get the nested loops
hinted via SQLPATCH?
declare
v_sql_text CLOB;
BEGIN
select sql_text into v_sql_text from dba_hist_sqltext where
sql_id='3vspnuzbn588y' and rownum=1;
sys.dbms_sqldiag_internal.i_create_patch(
sql_text => v_sql_text,
hint_text => 'USE_NL(@"SEL$897A7E7E" "E"@"SEL$2")',
name => 'tst_patch');
END;
/
Thanks,
Rakesh T
