I have been working with an application that is integrated with spring and Hibernate 4.X.X and its transaction is managed by JTA in Weblogic application server. After 3 years, there are about 40 million records only into one table from 100 tables that exist in my DB. The DB is Oracle 11g. The response time of a query is about 5 minutes because of increasing the count of records of this tables. I customized the query and put it into SQL Developer and run the query advisor plan for suggestion some Index. Totally after doing such this, its response time is reduced to 2 minutes. But even so, this response time does not satisfy the Customer. To further clarify I put the query, It is as following:

select *
  from (select (count(storehouse0_.ID) over()) as col_0_0_,
               storehouse3_.storeHouse_ID as col_1_0_,
               (DBPK_PUB_STOREHOUSE.get_Storehouse_Title(storehouse5_.id, 1)) as col_2_0_,
               storehouse5_.Organization_Code as col_3_0_,
               publicgood1_.Goods_Item_Id as col_4_0_,
               storehouse0_.storeHouse_Inventory_Id as col_5_0_,
               storehouse0_.Id as col_6_0_,
               storehouse3_.samapel_Item_Id as col_7_0_,
               samapelite10_.MAINNAME as col_8_0_,
               publicgood1_.serial_Number as col_9_0_,
               publicgood1_1_.production_Year as col_10_0_,
               samapelpar2_.ID_SourceInfo as col_11_0_,
               samapelpar2_.Pn as col_12_0_,
               storehouse3_.expire_Date as col_13_0_,
               publicgood1_1_.Status_Id as col_14_0_,
               baseinform12_.Topic as col_15_0_,
               publicgood1_.public_Num as col_16_0_,
               cast(publicgood1_1_.goods_Status as number(10, 0)) as col_17_0_,
               publicgood1_1_.goods_Status as col_18_0_,
               publicgood1_1_.deleted as col_19_0_
          from amd.Core_StoreHouse_Inventory_Item storehouse0_,
               amd.Core_STOREHOUSE_INVENTORY      storehouse3_,
               amd.Core_STOREHOUSE                storehouse5_,
               amd.SMP_SAMAPEL_CODE               samapelite10_
         cross join amd.Core_Goods_Item_Public publicgood1_
         inner join amd.Core_Goods_Item publicgood1_1_
            on publicgood1_.Goods_Item_Id = publicgood1_1_.Id
          left outer join amd.SMP_SOURCEINFO samapelpar2_
            on publicgood1_1_.Samapel_Part_Number_Id =
               samapelpar2_.ID_SourceInfo, amd.App_BaseInformation
         where not exists
         (select ssec.samapelITem_id
                  from core_security_samapelitem ssec
                 inner join core_goods_item g
                    on ssec.samapelitem_id = g.samapel_item_id
                 where not exists (SELECT aa.groupid
                          FROM app_actiongroup aa
                         where aa.groupid in
                               (select au.groupid
                                  from app_usergroup au
                                 where au.userid = 1)
                           and aa.actionid = 9054)
                   and ssec.isenable = 1
                   and storehouse0_.goods_Item_ID = g.id)
           and not exists
         (select *
                  from CORE_POWER_SECURITY cps
                 where not exists (SELECT aa.groupid
                          FROM app_actiongroup aa
                         where aa.groupid in
                               (select au.groupid
                                  from app_usergroup au
                                 where au.userid = 1)
                           and aa.actionid = 9055)
                   and cps.inventory_id =
                   and cps.goodsitemtype = 6)
           and storehouse0_.storeHouse_Inventory_Id = storehouse3_.Id
           and storehouse3_.storeHouse_ID = storehouse5_.Id
           and storehouse3_.samapel_Item_Id = samapelite10_.MAINCODE
           and publicgood1_1_.Status_Id = baseinform12_.ID
           and 1 <> 2
           and storehouse0_.goods_Item_ID = publicgood1_.Goods_Item_Id
           and publicgood1_1_.edited = 0
           and publicgood1_1_.deleted = 0
           and (exists (select storehouse13_.Id
                          from amd.Core_STOREHOUSE storehouse13_
                         cross join amd.core_power power16_
                         cross join amd.core_power power17_
                         where storehouse5_.powerID = power16_.Id
                           and storehouse13_.powerID = power17_.Id
                           and (storehouse13_.Id in (741684217))
                           and storehouse13_.storeHouseType = 2
                           and (power16_.hierarchiCode like
                               power17_.hierarchiCode || '%')) or
                (storehouse3_.storeHouse_ID in (741684217)) and
                storehouse5_.storeHouseType = 1)
           and (storehouse5_.storeHouse_Status not in (2, 3))
           order by  storehouse3_.samapel_Item_Id)
 where rownum <= 10

[Note: This query is generated by Hibernate].
It is clear that order by 40 million holds so much time. I find the main issue of this query. I omitted the “order by” and run the query, its response time was reduced to about 5 seconds. I was wondering why the “order by” affects so much the response time. (Somebody may think that if this table is partitioned or use another facility of the oracle, it may get better response time. Ok, it may be right but my emphasis is the “order by” performance. If there is a way that does the “order by” responsibility, why not to do it). Anyway, I am not able to omit the “order by” because the Customer needs to order and it is necessary for paging. I find a solution that is explained by an example. This solution I order only some records that are needed. How I will explain later. It is clear when Oracle wants to sort 40 million records, it naturally takes so much time. I replace “order by” with “where clause”. With doing this replacement the response time was reduces from 2 minutes to about 5 seconds and this is very exciting for me. I explain my solution via an example, anybody that read this Post tells me whether this solution is good or there is another solution that I do not know exists. Another hand I have a solution that is explained later if it is ok or not. Whether I use it or not. I explain my solution: Let’s assumed that there is two table as below:

Post table
Id  Others fields
…   …

Post_comment table
Id  post_id
1   5
2   5
3   5
4   5
6   5
7   2
8   2
9   2
10  3
11  1
12  1
13  1
14  1
15  1
16  1
17  1
18  1
19  1
20  1
21  1
22  1
23  1
24  1
25  1
26  4
27  4

There is a form that shows the result of join between POST table and POST_COMMENT table.
I explain both queries with “order by” all records of that table and “order by” only specific records that are needed. The result of two query is exactly the same but the response time of the second approach is the better than that one.
You assume that the page size is 10 and you are on page 3.
The first query with the “order by” all records of that table:

select *
  from (Select res.*, rownum as rownum_
          from (Select * from POST_COMMENT Order by post_id asc) res
         Where rownum <= 30)
 where rownum_ > 20

The second solution:
Before execution the query, I query as below:

select *
  from (select post_id, count(id) from POST_COMMENT group by post_id)
 order by post_id asc

So the result of it is the below:

Post_id Count(id)   Sum(count(id))
1   15                15
2   3                 18
3   1                 19
4   2                 21
5   5                 26

It needs to say that the third column that is "Sum(count(id))" is calculated after that query.Any entry of this column is sum all before records.
So there is a formula that specifics which post_id must be selected. The formula is the below:

pageSize = 10, pageNumber = 3
from : (pageNumber – 1) * pageCount  2 * 10 = 20
to : (pageNumber – 1) * pageCount + pageCount  20 + 10 = 30

So I need the posts that are between (20, 30] of Sum(count(id)). According to this, I need only two post_id that have value 4,5. According to this the main query of the second approach is:

select *
  from (select rownum as rownum_, res.*
          from (select *
                  from (select * from POST_COMMENT where post_id in (4, 5))
                 order by post_id asc) res
         where rownum <= 30)
 where rownum_ > 20

If you look at both query, you will see the biggest difference. The second query only selects the records of POST_COMENT that have post_id that are 4 and 5. After that, orders this records not all records of that table.


Here is a url that explains set events. The two events that you want are 10046 and 10053.


You can generate better data on how your query is running by doing the following. Using sqlplus turn on tracing, set the trace file identifier so that you can find your trace file.

ALTER SESSION SET tracefile_identifier = 'test_plan1';
ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

Run your SQL here

ALTER SESSION SET EVENTS '10053 trace name context OFF';
ALTER SESSION SET EVENTS '10046 trace name context OFF';

Once you get the SQL to run, go to your diag trace directory, assuming that you are using 11g or 12c. Then use tkprof to generate an explain plan along with stats on physical IO and logical IO.

tkprof <tracefile in> <trace file out> explain=system/pwd waits=yes

Once you do that, you should post the results and maybe someone can help you.


After posting this post, I have searched. finally I am redirected to HERE . I can reach to the response time that is very excited for me. It is reduced from 3 minutes to less than 3 seconds. It is necessary to know, I only use one tip from all of the query optimization guidelines that are in that site that is Duplicate constant condition for different tables whenever possible.
Note: Before doing this tip, there are some indexs on fields that are in where-clause and order-by.

  • Can you update this answer with more details? It's not clear what the solution is exactly. – ypercubeᵀᴹ Dec 17 '17 at 11:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.