Query very slow after a few execution

Question

I'm new of oracle and now I'm becoming crazy with the following situation. I'm working on a oracle 11g database and many times is happening that I run a query with sql developer and this is correctly executed in 5/6 seconds, others time instead the same query take 300/400 second to be executed. There is some tools to debug what is happening when the query employs 300/400 second?

Update 1 This is my sql developer screenshot the problem seems be direct path read temp

Update 2 report

Update 3 report2

Any suggestion?

Answer 1

Try setting a trace. User being whatever user is experiencing the delay
As sys:

GRANT ALTER SESSION TO USER;

As the user executing the trace:

ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';
ALTER SESSION SET TRACEFILE_IDENTIFIER = "MY_TEST_SESSION";

Produce the error/issue, then as the user testing:

ALTER SESSION SET EVENTS '10046 trace name context off';

As system find out where the trace files are kept:

show parameter background_dump_dest;

Go to that directory and look for .trc/.trm files containing MY_TEST_SESSION. For example ORCL_ora_29772_MY_TEST_SESSION.trc.
After that tkprof those files. In linux:

tkprof ORCL_ora_29772_MY_TEST_SESSION.trc output=ORCL_ora_29772_MY_TEST_SESSION.tkprof explain=user/password sys=no

Read the tkprof file and it will will show you wait times on given statements.
For more info on TKPROF read this. For more info on enabling/disabling a trace read this.

Answer 2

The best tool is Real-Time SQL Monitoring. It does not require changing code or access to the operating system. The only downside is it requires licensing the Tuning Pack.

Compare this single line of code with the trace steps in the other answer. Also, the output looks much nicer.

select dbms_sqltune.report_sql_monitor(sql_id => 'your sql id', type => 'text') from dual;

There's almost never a need to use trace in 11g and beyond.