Home » RDBMS Server » Performance Tuning » SQL tuning (merged)
SQL tuning (merged) [message #387934] Sat, 21 February 2009 23:56 Go to next message
cherry
Messages: 56
Registered: December 2007
Member
Hello All

I have a piece of PL Sql which is not performing as it used to before. I have narrowed down to the below sql, which is taking a lot of time to execute. I have extracted the explain plan & have attached below.

Can anyone guide me on how to fine tune this sql?

SELECT /*+ INDEX(GJL GL_JE_LINES_U1) */
       gjh.je_header_id, gjl.row_id rid, je_batch_id,
       RPAD (currency_code, 3, ' ') currency_code, je_source,
       TO_CHAR (default_effective_date, 'RRRR-MM-DD') effective_date,
       TO_CHAR (TO_DATE (gjl.reference_5, 'DD-Mon-RRRR'),
                'YYYY-MM-DD'
               ) posted_date,
       NVL (RPAD (SUBSTR (gjl.description, 1, 30), 30, ' '),
            '                              '
           ) trx_desc_1,
       RPAD (gcc.segment3, 8, ' ') gl_acct_id,
       RPAD (gcc.segment2, 5, ' ') gl_center_id,
       NVL (LPAD (  DECODE (SIGN (NVL (entered_dr, 0)),
                            -1, entered_cr,
                            DECODE (SIGN (NVL (entered_cr, 0)),
                                    -1, (entered_cr * -1),
                                    entered_dr
                                   )
                           )
                  * 1000,
                  15,
                  '0'
                 ),
            '               '
           ) curr_debit_amt,
       NVL
          (LPAD (  DECODE (SIGN (NVL (accounted_dr, 0)),
                           -1, accounted_cr,
                           DECODE (SIGN (NVL (accounted_cr, 0)),
                                   -1, (accounted_cr * -1),
                                   accounted_dr
                                  )
                          )
                 * 1000,
                 15,
                 '0'
                ),
           '               '
          ) kwd_equi_debit_amt,
       NVL (LPAD (  DECODE (SIGN (NVL (entered_cr, 0)),
                            -1, entered_dr,
                            DECODE (SIGN (NVL (entered_dr, 0)),
                                    -1, (entered_dr * -1),
                                    entered_cr
                                   )
                           )
                  * 1000,
                  15,
                  '0'
                 ),
            '               '
           ) curr_credit_amt,
       NVL
          (LPAD (  DECODE (SIGN (NVL (accounted_cr, 0)),
                           -1, accounted_dr,
                           DECODE (SIGN (NVL (accounted_dr, 0)),
                                   -1, (accounted_dr * -1),
                                   accounted_cr
                                  )
                          )
                 * 1000,
                 15,
                 '0'
                ),
           '               '
          ) kwd_equi_credit_amt,
       NVL (RPAD (SUBSTR (gjh.description, 1, 9), 9, ' '),
            '         '
           ) trx_desc_3,
       NVL (RPAD (gjl.attribute1, 3, ' '), '   ') branch_code,
       NVL (RPAD (gjl.attribute2, 5, ' '), '     ') teller_id,
       NVL (RPAD (gjl.attribute3, 5, ' '), '     ') officer_id,
       gjl.reference_6 trx_src_code, gjl.reference_5 reference_5,
       RPAD (gcc.segment4, 6, ' ') gl_project_code,
       RPAD (gcc.segment6, 4, ' ') gl_future_code,
       RPAD (gcc.segment1, 2, ' ') gl_legal_entity
  FROM gl_je_lines_v gjl, gl_code_combinations gcc, gl_je_headers gjh
 WHERE gjl.code_combination_id = gcc.code_combination_id
   AND gjl.je_header_id = gjh.je_header_id
   AND gjh.set_of_books_id =:p_sob_id
   AND gjl.set_of_books_id = :p_sob_id
   AND gjl.set_of_books_id = gjh.set_of_books_id
   AND gjh.actual_flag = 'A'
   AND gcc.detail_posting_allowed_flag = 'Y'
   AND gcc.enabled_flag = 'Y'
   AND gcc.summary_flag = 'N'
   AND NVL (TRUNC (gcc.end_date_active),
            TO_DATE (:l_process_date, 'DD/MM/RRRR')
           ) >= TRUNC (TO_DATE (:l_process_date, 'DD/MM/RRRR'))
   AND gjh.posted_date IS NOT NULL
   AND gjh.global_attribute1 IS NULL
   AND gcc.chart_of_accounts_id = 50288




./fa/5768/0/

Thanks
  • Attachment: cost.JPG
    (Size: 53.66KB, Downloaded 604 times)
Re: SQL tuning (merged) [message #387937 is a reply to message #387934] Sun, 22 February 2009 00:40 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/84315/74940/
which have you tried & what were the results

Re: SQL tuning (merged) [message #387951 is a reply to message #387934] Sun, 22 February 2009 08:05 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Post TKPROF.
Re: SQL tuning (merged) [message #388034 is a reply to message #387951] Mon, 23 February 2009 01:03 Go to previous messageGo to next message
cherry
Messages: 56
Registered: December 2007
Member
Thanks Michael bialik
Here it goes...


TKPROF: Release 10.2.0.1.0 - Production on Mon Feb 23 09:57:58 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Trace file: C:\DOCUME~1\ts25\LOCALS~1\Temp\TOA25E2.tmp
Sort options: default

********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing 
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
--------------------------------------------------------------------------------

*** SESSION ID:(286.34612) 2009-02-23 09:55:05.660

********************************************************************************

begin sys.dbms_system.set_ev(286, 34612, 10046, 0, ''); end; 


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          0          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 44  

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        2.66          2.66
********************************************************************************

SELECT /*+ INDEX(GJL GL_JE_LINES_U1) */
       gjh.je_header_id, gjl.row_id rid, je_batch_id,
       RPAD (currency_code, 3, ' ') currency_code, je_source,
       TO_CHAR (default_effective_date, 'RRRR-MM-DD') effective_date,
       TO_CHAR (TO_DATE (gjl.reference_5, 'DD-Mon-RRRR'),
                'YYYY-MM-DD'
               ) posted_date,
       NVL (RPAD (SUBSTR (gjl.description, 1, 30), 30, ' '),
            '                              '
           ) trx_desc_1,
       RPAD (gcc.segment3, 8, ' ') gl_acct_id,
       RPAD (gcc.segment2, 5, ' ') gl_center_id,
       NVL (LPAD (  DECODE (SIGN (NVL (entered_dr, 0)),
                            -1, entered_cr,
                            DECODE (SIGN (NVL (entered_cr, 0)),
                                    -1, (entered_cr * -1),
                                    entered_dr
                                   )
                           )
                  * 1000,
                  15,
                  '0'
                 ),
            '               '
           ) curr_debit_amt,
       NVL
          (LPAD (  DECODE (SIGN (NVL (accounted_dr, 0)),
                           -1, accounted_cr,
                           DECODE (SIGN (NVL (accounted_cr, 0)),
                                   -1, (accounted_cr * -1),
                                   accounted_dr
                                  )
                          )
                 * 1000,
                 15,
                 '0'
                ),
           '               '
          ) kwd_equi_debit_amt,
       NVL (LPAD (  DECODE (SIGN (NVL (entered_cr, 0)),
                            -1, entered_dr,
                            DECODE (SIGN (NVL (entered_dr, 0)),
                                    -1, (entered_dr * -1),
                                    entered_cr
                                   )
                           )
                  * 1000,
                  15,
                  '0'
                 ),
            '               '
           ) curr_credit_amt,
       NVL
          (LPAD (  DECODE (SIGN (NVL (accounted_cr, 0)),
                           -1, accounted_dr,
                           DECODE (SIGN (NVL (accounted_dr, 0)),
                                   -1, (accounted_dr * -1),
                                   accounted_cr
                                  )
                          )
                 * 1000,
                 15,
                 '0'
                ),
           '               '
          ) kwd_equi_credit_amt,
       NVL (RPAD (SUBSTR (gjh.description, 1, 9), 9, ' '),
            '         '
           ) trx_desc_3,
       NVL (RPAD (gjl.attribute1, 3, ' '), '   ') branch_code,
       NVL (RPAD (gjl.attribute2, 5, ' '), '     ') teller_id,
       NVL (RPAD (gjl.attribute3, 5, ' '), '     ') officer_id,
       gjl.reference_6 trx_src_code, gjl.reference_5 reference_5,
       RPAD (gcc.segment4, 6, ' ') gl_project_code,
       RPAD (gcc.segment6, 4, ' ') gl_future_code,
       RPAD (gcc.segment1, 2, ' ') gl_legal_entity
  FROM gl_je_lines_v gjl, gl_code_combinations gcc, gl_je_headers gjh
 WHERE gjl.code_combination_id = gcc.code_combination_id
   AND gjl.je_header_id = gjh.je_header_id
   AND gjh.set_of_books_id =:p_sob_id
   AND gjl.set_of_books_id = :p_sob_id
   AND gjl.set_of_books_id = gjh.set_of_books_id
   AND gjh.actual_flag = 'A'
   AND gcc.detail_posting_allowed_flag = 'Y'
   AND gcc.enabled_flag = 'Y'
   AND gcc.summary_flag = 'N'
   AND NVL (TRUNC (gcc.end_date_active),
            TO_DATE (:l_process_date, 'DD/MM/RRRR')
           ) >= TRUNC (TO_DATE (:l_process_date, 'DD/MM/RRRR'))
   AND gjh.posted_date IS NOT NULL
   AND gjh.global_attribute1 IS NULL
   AND gcc.chart_of_accounts_id = 50288 

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.02       0.01          0          0          0           0
Fetch        1      0.51       0.49          0      36563          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.53       0.52          0      36563          0           0

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 44  

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        9.40          9.40
********************************************************************************

select value from v$parameter
where  name in ('user_dump_dest', 'timed_statistics', 'max_dump_file_size')
order by name 

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          0          0           3
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          0          0           3

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 44  

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00



********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.00       0.00          0          0          0           0
Execute      3      0.02       0.01          0          0          0           1
Fetch        3      0.51       0.50          0      36563          0           3
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        9      0.53       0.52          0      36563          0           4

Misses in library cache during parse: 3
Misses in library cache during execute: 1

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       4        0.00          0.00
  SQL*Net message from client                     3        9.40         12.06
  SQL*Net more data from client                   1        0.00          0.00


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.01       0.05          0          0          0           0
Execute      3      0.00       0.10          0          0          0           0
Fetch        3      0.00       0.00          0          8          0           3
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        9      0.01       0.15          0          8          0           3

Misses in library cache during parse: 2
Misses in library cache during execute: 2

    3  user  SQL statements in session.
    3  internal SQL statements in session.
    6  SQL statements in session.
********************************************************************************
Trace file: C:\DOCUME~1\ts25\LOCALS~1\Temp\TOA25E2.tmp
Trace file compatibility: 10.01.00
Sort options: default

       1  session in tracefile.
       3  user  SQL statements in trace file.
       3  internal SQL statements in trace file.
       6  SQL statements in trace file.
       5  unique SQL statements in trace file.
     188  lines in trace file.
      24  elapsed seconds in trace file.

Re: SQL tuning (merged) [message #388135 is a reply to message #387934] Mon, 23 February 2009 07:09 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
The statement execution time is about 0.5 sec.
What is the problem?
If it's good enough - why do you care about cost?

Michael
Re: SQL tuning (merged) [message #388223 is a reply to message #388135] Mon, 23 February 2009 22:42 Go to previous message
cherry
Messages: 56
Registered: December 2007
Member

True. I have been monitoring this process for the past 2 weeks.
I tried all options, including tuning the SQL.
When the results wer'nt impressive, I took to posting it here.
When I say, results wer'nt impressive, i mean, the process which took 10-12 mins to complete, was taking 90-120 mins to complete.

But the day on which I took the TKPROF, "something" was right / wrong. This process was back to its processing time of 10-12 mins, which is acceptable. Im surprised with this behavior.

I will post back with my findings, if its behaves abnormally again.

meanwhile, thanks Michael for having a look Smile
Previous Topic: Inconsistent performance in database
Next Topic: Modifying a plan
Goto Forum:
  


Current Time: Sat Dec 10 09:24:00 CST 2016

Total time taken to generate the page: 0.06623 seconds