CODEWITHSUNDEEP
oracle-databaseoracle11g
Skizzo
Skizzo

Reputation: 2983

Query very slow after a few execution

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

enter image description here

Update 2 report

Update 3 report2

Any suggestion?

Upvotes: 10

Views: 2624

Answers (3)

Ian M
Ian M

Reputation: 11

This behaviour can be caused by cardinality feedback bugs / issues in 11gR2. I had a similar issue. You can test if this is the case by turning off this feature with _optimizer_use_feedback=false

Also try applying the latest updates.

Upvotes: 1

Jon Heller
Jon Heller

Reputation: 36807

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.

Upvotes: 3

mmmmmpie
mmmmmpie

Reputation: 3039

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.

Upvotes: 9

Related Questions