Is there a way to get Oracle to recalculate the query plan for each query call?

Question

I have a parameterized query. Depending on the parameter values, the optimal query plan varies significantly. Here's the problem: Oracle uses the plan from the first request call for subsequent calls, which leads to poor performance. I deal with dynamic SQL, but this way is far from elegant. So the question is: is there a way to tell Oracle that the query plan needs to be recalculated?

Answer 1

For Oracle 10g, we would select any table in the query and execute

GRANT SELECT ON table1 TO user1;

, . , , . . .

Answer 2

, , .

? , ( ), , , .

"/* VALUE ONE */" SQL ( , , , , - /* + CARDINALITY */ ).

, , SQL, Statspack , , .

Answer 3

, , thilo

select /* SQLID=1234 */ 1 from dual;
select /* SQLID=1235 */ 1 from dual;

.

, , , .

Answer 4

, , - . , , . .

, ( sql). , , .

sql . , sql , , , .

Answer 5

Oracle, ?

OUTLINE , OUTLINE CATEGORIES:

CREATE OUTLINE ol_use_nl
FOR
SELECT  *
FROM    mytable1 mt1
JOIN    mytable2 mt2
ON      mt1.id = mt2.id
WHERE   mt1.value BETWEEN :a AND :b
CATEGORY FILTERED;

/* Edit the outline to add USE_NL */

CREATE OUTLINE ol_use_nl
FOR
SELECT  *
FROM    mytable1 mt1
JOIN    mytable2 mt2
ON      mt1.id = mt2.id
WHERE   mt1.value BETWEEN :a AND :b
CATEGORY UNFILTERED;

/* Edit the outline to add USE_HASH */

ALTER SESSION SET USE_STORED_OUTLINES = FILTERED;

SELECT  *
FROM    mytable1 mt1
JOIN    mytable2 mt2
ON      mt1.id = mt2.id
WHERE   mt1.value BETWEEN 1 AND 2

/* This will use NESTED LOOPS */

ALTER SESSION SET USE_STORED_OUTLINES = UNFILTERED;

SELECT  *
FROM    mytable1 mt1
JOIN    mytable2 mt2
ON      mt1.id = mt2.id
WHERE   mt1.value BETWEEN 1 AND 1000000

/* This will use HASH JOIN */

Answer 6

- , , , , :

/* + opt_param ('_ OPTIM_PEEK_USER_BINDS', FALSE) */

Answer 7

The OP tells us that it cannot modify SQL statements. With this package dbms_advanced_rewriteyou can intercept SQL-queries and change the operator SQL.