Forum Stats

  • 3,769,456 Users
  • 2,252,967 Discussions
  • 7,875,032 Comments

Discussions

Why does a query respond in two different times, while nothing changes.

bma66
bma66 Member Posts: 18 Red Ribbon
edited Jan 23, 2018 3:53AM in APEX Discussions

hi,

i create view like

"select col1,col2,col3 from table where user=v('APP_USER')"

when run query first time

"select count(*) from view "

the time is 0.02 s but

next I will run the same query again

"select count(*) from view"

Time changes to 8 seconds,Again when compiling it ,Time is 0.2 seconds and next query is 8 second.

why?

Tagged:
AndrewSayer

Answers

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited Jan 23, 2018 3:22AM
    bma66 wrote:hi,i create view like"select col1,col2,col3 from table where user=v('APP_USER')"when run query first time"select count(*) from view "the time is 0.02 s butnext I will run the same query again"select count(*) from view"Time changes to 8 seconds,Again when compiling it ,Time is 0.2 seconds and next query is 8 second.why?

    This probably belongs in SQL & PL/SQL

    Could be any number of reasons, different plans (due to feedback etc), different data in the underlying table, different amounts of caching.

    If you want to know then look at where the time is going and compare it, enable extended sql trace and run the query, then tkprof the resultant trace file, what’s different between slow and fast execution?

    Most confident guess would be the change in execution plan, you can check this by finding the sql_id for the query by searching v$sql, then use

    select * from table(dbms_xplan.display_cursor(sql_id=>’<sql id>’))

    To see the plan, if you have multiple sql ids then check all, if you have multiple rows in v$sql for the same sql_id then you have multiple child cursors and can find out why Oracle decided to make a second one using the v$sql_shared_cursor view.

  • Pierre Yotti
    Pierre Yotti Member Posts: 4,040 Bronze Crown
    edited Jan 23, 2018 3:23AM

    Hi,

    first read bout the explain Plan

    https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9010.htm

    you can use it to have a explanation how your sql run.

    When your query takes too long ...

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited Jan 23, 2018 3:32AM

    Btw worth reading Grassroots Oracle: APEX performance issues with v()  to see why your view is always going to be slower than you might hope

    bma66
  • fac586
    fac586 Senior Technical Architect Member Posts: 20,113 Red Diamond
    edited Jan 23, 2018 3:53AM
    Andrew Sayer wrote:Btw worth reading Grassroots Oracle: APEX performance issues with v() to see why your view is always going to be slower than you might hope

    Which in APEX 5.x can be addressed by using a system context reference in the view instead of the v function:

    select col1,col2,col3 from table where user = sys_context('apex$session', 'app_user')

    Also note that while this "table" could not exist as both TABLE and USER are reserved words, a view could successfully be compiled with the column and table names substituted for real values, but leaving USER in place to be interpreted as the USER pseudocolumn, causing it to return either nothing or unexpected results:

    select empno,ename,hiredate from emp where user = sys_context('apex$session', 'app_user')
    AndrewSayerbma66
This discussion has been closed.