Is it possible to automate the optimization of slow queries in Oracle?

G

Grigory Kalyashov2015-10-12 08:07:10

Oracle

Grigory Kalyashov, 2015-10-12 08:07:10

Tell me, is it possible to automate the optimization of slow queries in Oracle? What to read on this topic and in which direction to dig?
I also want to ask how to detect slow requests? And is it possible to somehow see the query execution plan?

Reply

Answer the question

In order to leave comments, you need to log in

5 answer(s)

M

Max, 2015-10-12
@kalyashov

Google about AWR. cool stuff. Here you have cache usage, and I / O, and slow requests.
If query optimization were easy to automate, Oracle DBAs wouldn't be paid that kind of money.

P

Puma Thailand, 2015-10-12
@opium

1) do not write slow queries, but only fast ones with the correct indexes
2) enable logging of queries running more than n milliseconds
3) explain I think Oracle also has

I

igaraev, 2015-10-22
@igaraev

The ASHVIEWER utility is well suited for identifying slow queries in Online mode. Here is a link to information about the utility.
habrahabr.ru/company/jetinfosystems/blog/245507
To analyze slow queries over a period, an AWR report is suitable, which is easy to get in SQL Developer (view tab - dba)
In SQL Developer, you can also get advice on query optimization
from SQL tuning advise
in the report, find the report "TOP SQL by Waits"
select session and click on "SQL tuning advise"

K

Kamareka, 2015-10-22
@Kamareka

Read about "Automatic SQL Tuning": docs.oracle.com/cd/E28271_01/server.1111/e16638/sq...

G

Grigory Kalyashov, 2015-10-12
@kalyashov

I learned that you can use the v$sqlarea view to identify slow queries, and you can get the query execution plan from v$sql_plan