Jump to content
krnaveen14

Oracle Query execution finishes first time but hangs up from second run

Recommended Posts

We have a very complex view that finishes execution only on first time, but hungs up when run from the same session second time.

**complex view -** view with nested views, lots of inner and outer joins, multiple union all etc... But no locking statements, only pure read.
   

$ sqlplus sys/system as sysdba;
SQL> SELECT * FROM v$version;
    
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0    Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> alter session set current_schema = DB_2017_2018;

SQL> alter session set NLS_DATE_FORMAT = 'YYYY-MM-DD';

SQL> select 1 from complex_view where company_id = 1 and invoice_month = 1 and invoice_year = 2018;
-- runs in 33 sec

SQL> select 1 from complex_view where company_id = 1 and invoice_month = 1 and invoice_year = 2018;
-- hangs up.. Ctrl + C after 3 minutes

SQL> select * from complex_view where company_id = 1 and invoice_month = 1 and invoice_year = 2018;
-- runs in 1m 30 sec

SQL> select * from complex_view where company_id = 1 and invoice_month = 1 and invoice_year = 2018;
-- hangs up.. Ctrl + C after 5 - 10 minutes

-- ALL THESE COMMANDS / QUERIES RUN IN SAME SESSION

I figured that running the exact same query second time is when the problem occurs. If I simply change any select column or where condition, then it runs for first time and hangs up from second time (see the above commands). 

Suspect 1

One thing I suspect from this is may be it's related to oracle's cache. So when I run the following commands before each query execution, I didn't get any query hang up.

SQL> alter system flush shared_pool;
SQL> alter system flush buffer_cache;

Suspect 2 (root cause might be Suspect 1)

Once any query hangs up, even if I run the same query in another session, it still hangs up. So If I kill the active sessions that's running the hang up query, then I can run the query successfully only for first time but stuck with same problem from second run.

SQL> ALTER SYSTEM KILL SESSION '12,256' IMMEDIATE;


AFAIK, only thing I can be sure is that something is badly wrong with oracle's `buffer_cache` and `shared_pool` in our version.

This problem doesn't happen for tables or simple views, only for this complex view (didn't tested other complex views). Also we were facing this kind of strange issue only in last 2 - 3 weeks. It's hosted in cloud (AWS EC2), so it may be related to spectre / meltdown patches by Amazon as that's what changed in last 2 - 3 weeks (unrelated ?!). Or might be due to some db data threshold level reached in that period.

Ready to provide any other analysis or metrics

Post in DBA stackexchange

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×