Oracle Logbook

June 22, 2012

Quickly way to check out if a query have performance problem or not

Filed under: Uncategorized — Helio Dias @ 4:55 pm

This article is for those whom are begining in query performance tuning

(If and when you become experiente, start think about 10046, 10053 and wait event)

So far you best start would be check if the query time make sense.

To accomplish it, you will need to set autotrace on and run your query again
Let´s pick up a query that visit two tables. with 1000 blocks each and the result set is 10 rows.
The best case would be if your query visit 2 blocks (1 from each table) it´s just a dream situation and usually it´s hard to see.
20 Blocks still would be very good (ten blocks from each table)

Ok, but let´s suppose that your query is visiting 1500 blocks, is it good?
DEPENDS, how many rows do you really need from each table? 500 rows? than it seems reasonable. (It´s not wonderful, due the fact that this 500 rows could be within few blocks , but instead apparently in this case the data are spread among many blocks)
The way to improve it , usually have to do with physical organization of the data, e.g. lack of index organized table.

What about 5 rows?  NO WAY, you probably has a lack of index or statistics.

What if you are visiting 10,000 blocks? Big problem , you may have a cartesian join (in a very few cases it make sense, but usually don´t), The clue here is the distinct clause, often is evidence that you didn´t created the perfect join among the tables, (Perfect join would be 2 rows in each table producing 2,1 or zero rows) 3 or 4 rows usually is a clear indication of problem.

Formula of wonderful query:
(Rows in need from table1/avg rows per block for this table)  + (Rows in need from table2/avg rows per block for this table)
e.g. your predicate (filter) over the first table will get 200 rows
each block from table1 usually hold 50 rows, then you need to visit 4 blocks from table 1.
then the same scenario repeat over the second table, and you end up visiting 8 blocks. This is just very very good.

Formula of very good query.
Rows in need from table1+Rows in need from table2
In this case you end up visiting 400 blocks,
Your query seems good but your data access/organization don´t.

Formula of good query.
(Rows in need from table1) * (Rows in need from table2)
In this case you end up visiting 400 blocks,
Your query seems good but your data access/organization don´t.

Formula of very bad query.
(Table 1 number of rows) * (Table 2 number of rows)

Blog at