We are using Oracle 11, our application written on Java. Once a day, usually afternoon our database freezing because of many big sql queries. I want optimize this queries somehow. This queries consists of many joins of different tables. My question is: is it better for performance to use left join, or it is better put all information in one table and use one select? Suppose I will build a good indexes for this table.
For information: in average, one query fetch 100 megabytes. Also this queries lock each other sometimes
Update
Table that are joining 8 tables,they are usual tables with 3-5 columns, one of the column are some ID. Sql query looks like:
SELECT t1.c1,t2.c5, t6.c2, ... FROM t1
LEFT JOIN t2 ON t1.c1 = t2.c1
LEFT JOIN t3 ON t3.c1 = t2.c2
LEFT JOIN t4 ON t4.c1 = t2.c1
LEFT JOIN t5 ON t5.c5 = t1.c1
LEFT JOIN t6 ON t6.c1 = t2.c1
LEFT JOIN t7 ON t7.c3 = t3.c1
LEFT JOIN t8 ON t8.c1 = t2.c1
WHERE something
My question is, is it better to create one new tables, that consist of all joining tables, and use query like this:
SELECT c1,c5, c2, ... FROM SOME_NEW_TABLE
Update 2
Here is report, it would be great if some one can explain it in general.
The question can be generally answered I think. In performance tuning this type of query you will a number of things to consider:
Parse time
How long does it take to establish an execution plan for the statement? If the query runs slow the first time and fast all times later, parse time is an issue. I assume that there are no changing constants in the query. If not, please use bind variables or as a last resort use dynamic bind variables, but I can be a bad idea to automatically introduce bind variables , see "alter session set cursor_sharing=similar".
Especially with older versions and many joins (Oracle 8 was really bad in parsing statements with more than a 6 similar identity joins....) parse time can be expensive. Oracle 11 typically cuts the parse time by stopping after a number of execution plans have been considered. On Oracle 11 parse time still can be an issue, especially with union/union all.
Also, in this query you use ANSI style joins. Note that Oracle 11 has some performance drawbacks when using the more elegant ANSI style joins with complex statements. For automatically generated statements I therefore recommend Oracle style (c (+) = d), for statements that need to be maintained you need to study whether it really is a problem.
When parse time is an issue, I would recommend using a hint like /*+ ordered */ as a starting point. With this make sure your join order is such that the amount is data produced temporarily is as little as possible and the correct indexes are present.
Execution time
During execution, Oracle executes the execution plan. Oracle does this really efficient compared to other database platforms. But if the execution plan stinks, the execution takes time. In your question you talk about whether to prejoin everything or not.
In general it is best to always start of with a fully normalized model. In a fully normalized model data is stored only once. So when the query is efficiently planned, the least amount of data is processed. This assumes that the Oracle server has sufficient memory to cache it all or large parts of it, since join strategies need sometimes a lot of work space in memory plus the data already fetched from disk.
When performance is insufficient, I would start by introducing hints but staying with the normalized model. Always try to keep the amount of data eligible for output during interim steps as small as possible. When it really doesn't work, you might go for a derived table but I find this generally a sign of weak development skills.
In all this, I am assuming that one of the tables that start the execution plan has a large data volume and the other are smaller, maybe a little smaller or a lot smaller. If not, you are running a "Wiebertje" query (I don't have another name for it, it is shape of Dutch candy). Then please read page 9 and further of conference presentation 2006
Fetch time
At the end of the cycle, Oracle starts sending back the data at some moment. Especially the volume can highly the time needed to transfer it all. It is not uncommon for applications to fetch absolutely everything, but only displaying the first 50 rows. Please introduce windowing or "fetch to displayed watermark + constant" to reduce the fetch time. You may need to introduce a hint such as /*+ first_rows */ in the statement or session for interactive use.
