SQL Performance (MOSC)

MOSC Banner

Upgrade 8i to 12c - Procedure with parameter slow

edited Sep 16, 2020 4:49PM in SQL Performance (MOSC) 6 commentsAnswered

Hi,

We upgraded from Oracle 8i(8.1.7) to 12c(12.1.0.2) and we are finding that when executed directly against the database the query is fast, but API code in a procedure is slow.

The column in the query being compared and the parameter are both varchar2, so it is not due to an implicit conversion.

Below are the two scenarios with which we are hitting the slow performance.

Issue 1:

Procedure is called with parameters as below.

function get_customer_data(p_x_accno VARCHAR2, p_schedule_id VARCHAR2)

p_schedule_id is causing issue in the below query as highlighted . If we hard code it to ‘1’ it works fine.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

Instructions for posting

×

1. Select a discussion category from the picklist.


2. Enter a title that clearly identifies the subject of your question.


3. In the body, insert detailed information, including Oracle product and version.


Please abide by the Oracle Community guidelines and refrain from posting any customer or personally identifiable information (PI/CI).


Cloud / Fusion customers - Our Cloud community has moved! Please go to Cloud Customer Connect .


New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center