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.
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.
1 person found this helpful
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
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')