SELECT * FROM (SELECT r_t. *, ROWNUM r_rownum FROM (SELECT/* + */subscribersn rule FROM subscriberrentinfo where version & gt;=1 and the version & lt;=: 2) r_t WHERE ROWNUM & lt; : 3) WHERE r_rownum & gt; 4=:
Statement execution performance tests found that trip when the most time consuming, 98% CPU usage, can you tell me how to optimize

CodePudding user response:

Posted on the execution plan, light a statement, don't see what,

CodePudding user response:

The execution plan,,

CodePudding user response:

What is the Oracle version? If it is 10 g and 11 g, 12 c, please remove the rule *//* + hint,

CodePudding user response:

Condition too much is not controllable, the paging SQL can't be in all conditions can be optimized

CodePudding user response:

reference 4 floor minsic78 response:

conditions too much is not controllable, the paging SQL can't be in all conditions can be optimized

If meet the conditions of this version of data is few, so the version of the above indexes can solve the problem, has nothing to do with the paging, if meet the conditions of data is very, very much, almost ran a full table, then the first page of results should also be quick, but if meet the conditions of data more, no less, that's embarrassing,

CodePudding user response:

reference 5 floor minsic78 reply:

Quote: refer to 4th floor minsic78 response:

Condition too much is not controllable, the paging SQL can't be in all conditions can be optimized

If meet the conditions of this version of data is few, so the version of the above indexes can solve the problem, has nothing to do with the paging, if meet the conditions of data is very, very much, almost ran a full table, then the first page of results should also be quick, but if meet the conditions of data more, no less, that's embarrassing,

Carelessly three even: if it is the second case, a lot of data meet the conditions, then you that/* */rule prompted more awkward, remove,

In addition, ripped the things more complicated: because use bind variables, version different values, SQL execution plan is likely to make some adjustments, this version with your database, including some bind variables are some related parameters setting, this depends, such as the bind peeking open or not? ACS is open or not, cardinality feedback open or not?