PL/SQL (MOSC)

MOSC Banner

Query taking long time to execute

edited Jul 31, 2019 2:14AM in PL/SQL (MOSC) 8 commentsAnswered

Below is my query, its taking long time to execute around 5 to 10mins for just 12 records Please correct me If something wrong in the query.

in the query, I am using one function, xxmec_getprevyeardata which returns average value. please see the function, below the select statement.

the query should fetch within 10 to 15 seconds for JUN-2019 period(parameter value which I am passing)

SELECT
    lookup_code,
    meaning period,
    period period_name,
    SUM(NVL(regular,0)) regular,
    SUM(NVL(incidental,0)) incidental,
    SUM(NVL(regular,0))+SUM(NVL(incidental,0)) total,
    ROUND(xxmec_getprevyeardata(period,'PYD')/(xxmec_getprevyeardata(period,'NO_PERIODS'))) average,
    jrnl_plan
FROM

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

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center