This forum is currently locked. You can't register or post questions at this time. (read more)

Sql query is taking too much time to execute

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

Raaj A
Senior Member
Posts: 51
Joined: Wed Mar 07, 2012 7:08 am

Sql query is taking too much time to execute

Postby Raaj A » Fri Nov 30, 2012 10:37 am

Hi Tim,

My production db is on oracle 10g(10.2.0.5) on windows 2003 32 bit.
Below query is taking too much time to execute.

Please see the execution plan following query.

Code: Select all

SELECT -- san_10apr10 : comment below line and byfercate by count of soft limits and count of soft manual limits breach.
             COUNT( DISTINCT CASE WHEN chk_lim_data.available_limit < 0 AND lim_sevr_mast.ignore_breach_for_evaluation = 'Y' THEN chk_lim.limit_syscode END) total_soft_breach
             -- san_10apr10 : this will count the total soft limit breaches.
           --Count( distinct case when chk_lim_data.available_limit < 0 and lim_sevr_mast.ignore_breach_for_evaluation = 'Y' AND lim_sevr_mast.limit_severity_syscode = 2 then chk_lim.limit_syscode end) total_soft_breach
             -- san_10apr10 : this will count the total soft manual limit breaches.
          --,Count( distinct case when chk_lim_data.available_limit < 0 and lim_sevr_mast.ignore_breach_for_evaluation = 'Y' AND lim_sevr_mast.limit_severity_syscode = 3 then chk_lim.limit_syscode end) total_soft_manual_breach
          ,COUNT( DISTINCT CASE WHEN chk_lim_data.available_limit < 0 AND lim_sevr_mast.ignore_breach_for_evaluation = 'N' THEN chk_lim.limit_syscode END) total_hard_breach
          ,COUNT( DISTINCT chk_lim.limit_syscode ) total_limit_checked
          -- san_10apr10 : one more variable v_total_soft_manual_breach included here
          INTO v_total_soft_breach,v_total_hard_breach, v_total_limit_checked
          --into v_total_soft_breach,v_total_soft_manual_breach,v_total_hard_breach, v_total_limit_checked
          FROM LMS_CHECKED_LIMITS chk_lim
             inner join LMS_CHECKED_LIMITS_DATA chk_lim_data
                  ON chk_lim.checked_limit_syscode = chk_lim_data.checked_limit_syscode
    --------nel_12mar08 added by nelson: user was hetting mesage as hard limit breached (which was pasive but no popup was shown.
    --------                  when data is inserted in lms_Response_Data only active breaches was considered but here when response was framed pasive was not excluded hence the issue.
    --                   the table has ben added because active_Pasive flag is maintained at tran level
            inner join LMS_CHECKED_LIMITS_TRAN_LEVEL trn_chk_data ON
                   /* san_25sep09 :- commented below 7 lines and write without using Nvl() function for enhancing performance tuning.
                        Nvl(chk_lim_data.instrument_syscode,0) = Nvl(trn_chk_data.instrument_syscode,0) and
                        Nvl(chk_lim_data.account_syscode,0) = Nvl(trn_chk_data.account_syscode,0) and
                        Nvl(chk_lim_data.lov_data_syscode_group1,0) = Nvl(trn_chk_data.lov_data_syscode_group1,0) and
                        Nvl(chk_lim_data.lov_data_syscode_group2,0) = Nvl(trn_chk_data.lov_data_syscode_group2,0) and
                        Nvl(chk_lim_data.lov_data_syscode_group3,0) = Nvl(trn_chk_data.lov_data_syscode_group3,0) and
                        Nvl(chk_lim_data.lov_data_syscode_group4,0) = Nvl(trn_chk_data.lov_data_syscode_group4,0) and
                        Nvl(chk_lim_data.lov_data_syscode_group5,0) = Nvl(trn_chk_data.lov_data_syscode_group5,0) and */
                        chk_lim_data.instrument_syscode = trn_chk_data.instrument_syscode AND
                        chk_lim_data.account_syscode = trn_chk_data.account_syscode AND
                        chk_lim_data.lov_data_syscode_group1 = trn_chk_data.lov_data_syscode_group1 AND
                        chk_lim_data.lov_data_syscode_group2 = trn_chk_data.lov_data_syscode_group2 AND
                        chk_lim_data.lov_data_syscode_group3 = trn_chk_data.lov_data_syscode_group3 AND
                        chk_lim_data.lov_data_syscode_group4 = trn_chk_data.lov_data_syscode_group4 AND
                        chk_lim_data.lov_data_syscode_group5 = trn_chk_data.lov_data_syscode_group5 AND
                        NVL(chk_lim_data.transaction_syscode,trn_chk_data.transaction_syscode) = trn_chk_data.transaction_syscode AND
                        chk_lim.limit_syscode = trn_chk_data.limit_syscode AND
                        chk_lim.transaction_master_syscode =  trn_chk_data.transaction_master_syscode AND
                   chk_lim.inflow_outflow = trn_chk_data.inflow_outflow AND
                   NVL(trn_chk_data.active_passive,'A') = 'A'
             inner join LMS_LIMIT_MASTER lim_mast
                  ON chk_lim.limit_syscode = lim_mast.limit_syscode
                         inner join LMS_LIMIT_VALUES lim_values   --deep020409 added
                               ON lim_values.level_code =  chk_lim.level_code AND lim_values.limit_syscode = chk_lim.limit_syscode
             inner join LMS_LIMIT_SEVERITY_MASTER lim_sevr_mast --deep020409 added
                  ON lim_values.limit_severity_syscode = lim_sevr_mast.limit_severity_syscode
             inner join LMS_PACKET_DETAILS pack_detail --san_22may08
                  ON chk_lim.transaction_master_syscode = pack_detail.transaction_master_syscode
             WHERE pack_detail.process_syscode = 50687;
             --where chk_Lim.transaction_Master_Syscode = p_Tran_Master_Syscode_Curr;

Code: Select all

=========================================================================================================================
TOTAL_SOFT_BREACH TOTAL_HARD_BREACH TOTAL_LIMIT_CHECKED
----------------- ----------------- -------------------
                1                 0                   6
Elapsed: 00:02:10.48

Execution Plan
----------------------------------------------------------
Plan hash value: 4076069282

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                               |     1 |   124 |       |  1768   (2)| 00:00:22 |
|   1 |  SORT GROUP BY                   |                               |     1 |   124 |       |         |     |
|   2 |   NESTED LOOPS                   |                               |     1 |   124 |       |  1768   (2)| 00:00:22 |
|*  3 |    HASH JOIN                     |                               |     1 |   119 |       |  1767   (2)| 00:00:22 |
|   4 |     NESTED LOOPS                 |                               |     1 |   109 |       |  1764   (2)| 00:00:22 |
|   5 |      NESTED LOOPS                |                               |     1 |    99 |       |  1763   (2)| 00:00:22 |
|*  6 |       HASH JOIN                  |                               |    10 |   740 |  2616K|  1753   (2)| 00:00:22 |
|   7 |        TABLE ACCESS FULL         | LMS_CHECKED_LIMITS_DATA       | 59485 |  1916K|       |   153   (2)| 00:00:02 |
|*  8 |        TABLE ACCESS FULL         | LMS_CHECKED_LIMITS_TRAN_LEVEL | 73875 |  2957K|       |  1284   (2)| 00:00:16 |
|*  9 |       TABLE ACCESS BY INDEX ROWID| LMS_CHECKED_LIMITS            |     1 |    25 |       |     1   (0)| 00:00:01 |
|* 10 |        INDEX UNIQUE SCAN         | XPKLMS_CHECKED_LIMITS         |     1 |       |       |     0   (0)| 00:00:01 |
|* 11 |      TABLE ACCESS BY INDEX ROWID | LMS_PACKET_DETAILS            |     1 |    10 |       |     1   (0)| 00:00:01 |
|* 12 |       INDEX UNIQUE SCAN          | XPKLMS_PACKET_DETAILS         |     1 |       |       |     0   (0)| 00:00:01 |
|  13 |     TABLE ACCESS FULL            | LMS_LIMIT_VALUES              |   843 |  8430 |       |     3   (0)| 00:00:01 |
|  14 |    TABLE ACCESS BY INDEX ROWID   | LMS_LIMIT_SEVERITY_MASTER     |     1 |     5 |       |     1   (0)| 00:00:01 |
|* 15 |     INDEX UNIQUE SCAN            | XPKLMS_LIMIT_SEVERITY_MASTER  |     1 |       |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("LIM_VALUES"."LEVEL_CODE"="CHK_LIM"."LEVEL_CODE" AND
              "LIM_VALUES"."LIMIT_SYSCODE"="CHK_LIM"."LIMIT_SYSCODE")
   6 - access("CHK_LIM_DATA"."INSTRUMENT_SYSCODE"="TRN_CHK_DATA"."INSTRUMENT_SYSCODE" AND
              "CHK_LIM_DATA"."ACCOUNT_SYSCODE"="TRN_CHK_DATA"."ACCOUNT_SYSCODE" AND
              "CHK_LIM_DATA"."LOV_DATA_SYSCODE_GROUP1"="TRN_CHK_DATA"."LOV_DATA_SYSCODE_GROUP1" AND
              "CHK_LIM_DATA"."LOV_DATA_SYSCODE_GROUP2"="TRN_CHK_DATA"."LOV_DATA_SYSCODE_GROUP2" AND
              "CHK_LIM_DATA"."LOV_DATA_SYSCODE_GROUP3"="TRN_CHK_DATA"."LOV_DATA_SYSCODE_GROUP3" AND
              "CHK_LIM_DATA"."LOV_DATA_SYSCODE_GROUP4"="TRN_CHK_DATA"."LOV_DATA_SYSCODE_GROUP4" AND
              "CHK_LIM_DATA"."LOV_DATA_SYSCODE_GROUP5"="TRN_CHK_DATA"."LOV_DATA_SYSCODE_GROUP5")
       filter("TRN_CHK_DATA"."TRANSACTION_SYSCODE"=NVL("CHK_LIM_DATA"."TRANSACTION_SYSCODE","TRN_CHK_DATA"."TRANSA
              CTION_SYSCODE"))
   8 - filter(NVL("TRN_CHK_DATA"."ACTIVE_PASSIVE",'A')='A')
   9 - filter("CHK_LIM"."LIMIT_SYSCODE" IS NOT NULL AND "CHK_LIM"."LIMIT_SYSCODE"="TRN_CHK_DATA"."LIMIT_SYSCODE"
              AND "CHK_LIM"."TRANSACTION_MASTER_SYSCODE"="TRN_CHK_DATA"."TRANSACTION_MASTER_SYSCODE" AND
              "CHK_LIM"."INFLOW_OUTFLOW"="TRN_CHK_DATA"."INFLOW_OUTFLOW")
  10 - access("CHK_LIM_DATA"."CHECKED_LIMIT_SYSCODE"="CHK_LIM"."CHECKED_LIMIT_SYSCODE")
  11 - filter("PACK_DETAIL"."PROCESS_SYSCODE"=50687)
  12 - access("CHK_LIM"."TRANSACTION_MASTER_SYSCODE"="PACK_DETAIL"."TRANSACTION_MASTER_SYSCODE")
  15 - access("LIM_VALUES"."LIMIT_SEVERITY_SYSCODE"="LIM_SEVR_MAST"."LIMIT_SEVERITY_SYSCODE")


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
  115263527  consistent gets
       7062  physical reads
          0  redo size
        561  bytes sent via SQL*Net to client
       2869  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed
==========================================================================================================================

when i am taking awr report. it is showing high CPU consumption and enq: TX-row lock contention is high in top 5 wait events.

Please suggest.


Thanks

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Sql query is taking too much time to execute

Postby Tim... » Fri Nov 30, 2012 11:35 am

Hi.

To be 100% sure where the elapsed time is going you need to trace the statement:

http://www.oracle-base.com/articles/10g ... of-10g.php

The AWR report it presumably showing high CPU and transaction locks on the system in general, not specifically for this statement, so I think that could be throwing you off the scent. Transaction locks do not affect queries, since Oracle does not block reads, so that can't be an issue for the elapsed time. Just because the system is experiencing a lot of CPU activity, it doesn't mean this query is used a lot of CPU.

You should trace the statement and see where all the elapsed time is being spent before trying to decide on a solution. The 115 million consistent gets look rather suspicious. :)

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 2 guests

cron