Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2012
    Posts
    4

    Unanswered: SQL running very slow in application engine

    I have a small sql (running in PeopleSoft application engine) which I am using to calculate depreciation. The below pasted sql is taking a lot of time to run.I have aroung 8 crore rows in the depreciation table/record. I did the indexing on business_unit,asset_id and book as well on the ps_depreciation table/record but no affect.

    UPDATE PS_AZC_INTFC_FIN A
    SET A.DEPR=(
    SELECT SUM(B.DEPR)
    FROM TP_AZ_DEPRECIATION B
    WHERE A.BUSINESS_UNIT=B.BUSINESS_UNIT
    AND A.ASSET_ID=B.ASSET_ID
    AND A.BOOK=B.BOOK
    AND B.FISCAL_YEAR <=2012)
    WHERE EXISTS(
    SELECT 'X' FROM TP_AZ_DEPRECIATION D
    WHERE A.BUSINESS_UNIT=D.BUSINESS_UNIT
    AND A.ASSET_ID=D.ASSET_ID
    AND A.BOOK=D.BOOK
    AND D.FISCAL_YEAR <=2012)

    This PS_AZC_INTFC_FIN is a custom record which has asset_id,businesS_unit and book as keys.

    Please help.

    Regards,
    Vishal

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    post complete results from SQL below

    SELECT * FROM V$VERSION;
    Code:
    UPDATE ps_azc_intfc_fin A 
    SET    A.depr = (SELECT SUM(B.depr) 
                     FROM   tp_az_depreciation B 
                     WHERE  A.business_unit = B.business_unit 
                            AND A.asset_id = B.asset_id 
                            AND A.book = B.book 
                            AND B.fiscal_year <= 2012) 
    WHERE  EXISTS(SELECT 'X' 
                  FROM   tp_az_depreciation D 
                  WHERE  A.business_unit = D.business_unit 
                         AND A.asset_id = D.asset_id 
                         AND A.book = D.book 
                         AND D.fiscal_year <= 2012)
    are all columns in WHERE clause indexed?
    are statistics current for all table & indexes involved
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    Run a 'explain plan' on your sql statement and post results:
    Code:
    update ps_azc_intfc_fin a
      set  a.depr = ( select sum( b.depr )
                       from  tp_az_depreciation b
                      where  a.business_unit  =  b.business_unit
                       and   a.asset_id       =  b.asset_id
                       and   a.book           =  b.book
                       and   b.fiscal_year   <=  2012 )
    where exists ( select 'x'
                    from  tp_az_depreciation d
                   where  a.business_unit  =  d.business_unit
                    and   a.asset_id       =  d.asset_id
                    and   a.book           =  d.book
                    and   d.fiscal_year   <=  2012 )

  4. #4
    Join Date
    Dec 2012
    Posts
    4

    The output is as follows

    SELECT * FROM V$VERSION;
    ---------------------------------
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
    PL/SQL Release 11.2.0.1.0 - Production
    "CORE 11.2.0.1.0 Production"
    TNS for Linux: Version 11.2.0.1.0 - Production
    NLSRTL Version 11.2.0.1.0 - Production
    ------------------------------------

    The sql plan has been attached as an attachement.
    Attached Thumbnails Attached Thumbnails explain_plan.png  

  5. #5
    Join Date
    Dec 2012
    Posts
    4
    The colomns in the where claues are indexed in both the records.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    my eyes can't discern any usable details from attachment
    is COPY & PASTE broken for you?

    Code:
    SQL> set autotrace on explain
    SQL> select sysdate from dual;
    
    SYSDATE
    ---------
    04-DEC-12
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1388734953
    
    -----------------------------------------------------------------
    | Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
    -----------------------------------------------------------------
    |   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
    |   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
    -----------------------------------------------------------------
    
    SQL>
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Dec 2012
    Posts
    4
    Not too much familiar with the DB commands. I was not able to copy SQL plan i got previously.Anywasy here is the traced plan of the sql.

    Plan hash value: 1640086486
    ----------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ----------------------------------------------------------------------------------------------------
    | 0 | UPDATE STATEMENT | | 100K| 6853K| 467K (22)| 01:33:32 |
    | 1 | UPDATE | PS_AZC_INTFC_FIN | | | | |
    | 2 | NESTED LOOPS SEMI | | 100K| 6853K| 66567 (1)| 00:13:19 |
    | 3 | TABLE ACCESS FULL | PS_AZC_INTFC_FIN | 100K| 3524K| 6351 (1)| 00:01:17 |
    |* 4 | TABLE ACCESS BY INDEX ROWID| TP_AZ_DEPRECIATION | 4407K| 142M| 1 (0)| 00:00:01 |
    |* 5 | INDEX RANGE SCAN | TPAZDDEPRECIATION | 1 | | 1 (0)| 00:00:01 |
    | 6 | SORT AGGREGATE | | 1 | 47 | | |
    |* 7 | TABLE ACCESS BY INDEX ROWID| TP_AZ_DEPRECIATION | 1 | 47 | 3 (0)| 00:00:01 |
    |* 8 | INDEX RANGE SCAN | TPAZDDEPRECIATION | 1 | | 2 (0)| 00:00:01 |
    ----------------------------------------------------------------------------------------------------

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

    4 - filter("D"."FISCAL_YEAR"<=2012)
    5 - access("A"."ASSET_ID"="D"."ASSET_ID" AND "A"."BUSINESS_UNIT"="D"."BUSINESS_UNIT" AND
    "A"."BOOK"="D"."BOOK")
    7 - filter("B"."FISCAL_YEAR"<=2012)
    8 - access("B"."ASSET_ID"=:B1 AND "B"."BUSINESS_UNIT"=:B2 AND "B"."BOOK"=:B3)

  8. #8
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    Post list of indexes for tables(ps_azc_intfc_fin, tp_az_depreciation):

    Code:
    select ati.table_owner, ati.table_name, ati.index_name, ati.uniqueness, aic.column_name,
           aic.column_position, aic.descend
     from  all_indexes ati join all_ind_columns aic on ati.table_owner = aic.table_owner
                                                   and ati.table_name  = aic.table_name
                                                   and ati.index_name  = aic.index_name
    where  lower( ati.table_owner )  =  'owner'
     and   lower( ati.table_name  )  =  'table_name'
    order by ati.index_name, aic.column_position

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •