Oracle 12c: SQL query hangs forever only occasionally

Question

I have a SQL query that fetches roughly 200 columns from multiple tables and normally runs in a matter of minutes.

A Java program kicked off by cron calls the SQL every 4 hours, but occasionally hangs forever(=not fetching any data. Neither updates nor inserts are involved).

Here are some outputs from V$SESSION.

STATUS: ACTIVE
ROW_WAIT_OBJ#: 22392　←not changing
ROW_WAIT_FILE#: 6　←not changing
ROW_WAIT_BLOCK#: 8896642　←not changing
ROW_WAIT_ROW#: 0　←not changing
LAST_CALL_ET: 5632　←keeps incresing

★No other heavy SQL queries are running at the same time

What could be the cause of this and what should I look into to solve it?

Can you use some sort of profiler and see what query is creating this bottleneck.!? — user8271644, Jul 19 '17 at 15:49
@user8271644 I am new to Oracle. What sort of profilers are available? — d-_-b, Jul 20 '17 at 01:36
Below links mentions some tools for oracle profiling. https://stackoverflow.com/questions/148648/oracle-is-there-a-tool-to-trace-queries-like-profiler-for-sql-server — user8271644, Jul 20 '17 at 02:01
from the looks of it it might be (the _WAIT_ part). Can you also start checking the v$session_wait ? — access_granted, Jul 20 '17 at 05:19

score 0 · Answer 1 · answered Jul 19 '17 at 16:29

0

You can use TKPROF or SQL Profiler. This reports can help you. We can not replay your question now.

If you attach your tuning reports, we can help you. Because many things can cause performance problems. A comprehensive study is needed to understand this.

Follow this link;

https://docs.oracle.com/cd/E11882_01/server.112/e41573/perf_overview.htm

answered Jul 19 '17 at 16:29

mehmet sahin

802
7
21

Is SQL Profiler a specific or general tool? – d-_-b Jul 20 '17 at 01:38

Oracle 12c: SQL query hangs forever only occasionally

1 Answers1