Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 508
Skizzo Avatar asked Apr 29 '15 13:04

Skizzo


People also ask

Why is my query running slow?

WAITING: Queries can be slow because they're waiting on a bottleneck for a long time. See a detailed list of bottlenecks in types of Waits. RUNNING: Queries can be slow because they're running (executing) for a long time. In other words, these queries are actively using CPU resources.

How do you fix a slow query?

Alter the query It can use a table that is suboptimal, or gather more information than necessary. Slow queries are frequently caused by combining two or more large tables together using a JOIN. Review the number of joins in your query, and determine if the query is pulling more information than is actually needed.

What could most likely cause a SQL query to run as slow as 5 minutes?

What could most likely cause a SQL query to run as slow as 5 minutes? Ans. Most probably, a sudden surge in the volume of data in a particular table could slow down the output of a SQL query. So collect the required stats for the target table.


2 Answers

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.

like image 197
mmmmmpie Avatar answered Sep 28 '22 05:09

mmmmmpie


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.

like image 26
Jon Heller Avatar answered Sep 28 '22 05:09

Jon Heller