Home » RDBMS Server » Performance Tuning » SQL tuning (merged)
| SQL tuning (merged) [message #387934] |
Sat, 21 February 2009 23:56  |
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
Thanks
-
Attachment: cost.JPG
(Size: 53.66KB, Downloaded 1728 times)
|
|
|
|
|
|
|
|
| Re: SQL tuning (merged) [message #388034 is a reply to message #387951] |
Mon, 23 February 2009 01:03  |
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 #388223 is a reply to message #388135] |
Mon, 23 February 2009 22:42 |
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 
|
|
|
|
Goto Forum:
Current Time: Sun Jun 30 13:39:42 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
