Home » SQL & PL/SQL » SQL & PL/SQL » Performance Issue...
Performance Issue... [message #355867] Tue, 28 October 2008 03:38 Go to next message
dinendra_sl
Messages: 5
Registered: October 2008
Junior Member
Please look at the following query.
SELECT   company, voucher_type, voucher_no, voucher_date, accounting_year,
         accounting_period, year_period_key, correction, ACCOUNT,
         account_desc, code_b, code_b_desc, code_c, code_c_desc, code_d,
         code_d_desc, code_e, code_e_desc, code_f, code_f_desc, code_g,
         code_g_desc, code_h, code_h_desc, code_i, code_i_desc, code_j,
         currency_code, currency_rate, conversion_factor, debet_amount,
         credit_amount, amount, currency_debet_amount, currency_credit_amount,
         currency_amount, third_currency_debit_amount,
         third_currency_credit_amount, third_currency_amount, text, quantity,
         process_code, optional_code, project_activity_id, party_type_id,
         trans_code, transfer_id, corrected, reference_serie,
         reference_number, mpccom_accounting_id, function_group,
         accounting_year_reference
    FROM ifsapp.gen_led_voucher_row_union_qry
   WHERE company = '110'
     AND (    (    accounting_year = 2008
               AND accounting_period BETWEEN 1 AND 9
               AND ACCOUNT BETWEEN '5000' AND '7999'
               AND code_b = '431'
              )
          AND ((    accounting_year = 2008
                AND accounting_period = 1
                AND ACCOUNT = '5019'
                AND code_b = '431'
               )
              )
          AND (simulation_voucher = 'FALSE')
         )
ORDER BY company, voucher_type, accounting_year, voucher_no;

When I execute following query by setting language to 'sv' and it get executed less than one second. Also when I execute is by setting the language to 'en', it took arond 5 minutes to get executed. Finally I realized that the perfomance issue with the ORDER BY clause so I commented it and run and I was able to see the results in less than 1 second.

This query will return only one record.

Since this query works for 'sv' language perfectly and for the language 'en' results fetched quickly without the ORDER BY clause.

The problem reported in oracle 64 bit version.

modified query
SELECT company, voucher_type, voucher_no, voucher_date, accounting_year,
accounting_period, year_period_key, correction, ACCOUNT,
account_desc, code_b, code_b_desc, code_c, code_c_desc, code_d,
code_d_desc, code_e, code_e_desc, code_f, code_f_desc, code_g,
code_g_desc, code_h, code_h_desc, code_i, code_i_desc, code_j,
currency_code, currency_rate, conversion_factor, debet_amount,
credit_amount, amount, currency_debet_amount, currency_credit_amount,
currency_amount, third_currency_debit_amount,
third_currency_credit_amount, third_currency_amount, text, quantity,
process_code, optional_code, project_activity_id, party_type_id,
trans_code, transfer_id, corrected, reference_serie,
reference_number, mpccom_accounting_id, function_group,
accounting_year_reference
FROM ifsapp.gen_led_voucher_row_union_qry
WHERE company = '110'
AND accounting_year = 2008
AND code_b = '431'
AND accounting_period BETWEEN 1 AND 9
AND ACCOUNT = '5019'
AND (simulation_voucher = 'FALSE')
AND accounting_period = 1 
ORDER BY company, voucher_type, accounting_year, voucher_no;

I have modified the query which fetch the same results as the initial query. Here also the same problem exists. That means the query gets executed without 'order by' clause less than 1 second but with 'order by' clause still the problem is there...

Do you have any suggestion?

Since the results get fetched less than 1 second, can't this be a problem with oracle 64 bit version 'order by' functionality? I'm just asking to get an idea...

Thank You.

[Mod-Edit: Frank added [code]-tags to improve readability]

[Updated on: Tue, 28 October 2008 03:45] by Moderator

Report message to a moderator

Re: Performance Issue... [message #355869 is a reply to message #355867] Tue, 28 October 2008 03:49 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
First of all, the initial query has
AND accounting_period = 1
so you can replace the BEWEEN with the exact equation.

secondly: post explain-plans for the different situations to show what the optimizer is doing. Even better: trace the session and run tkprof with explain plan option.
Re: Performance Issue... [message #355887 is a reply to message #355867] Tue, 28 October 2008 05:52 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
For more information check this link.

http://forums.oracle.com/forums/thread.jspa?threadID=722635&tstart=0

Regards

Raj
Re: Performance Issue... [message #355888 is a reply to message #355867] Tue, 28 October 2008 05:52 Go to previous messageGo to next message
dinendra_sl
Messages: 5
Registered: October 2008
Junior Member
Hi..

I have changed it..

now the query seems like this..

SELECT *
FROM ifsapp.gen_led_voucher_row_union_qry
WHERE company = '110'
AND accounting_year = 2008
AND code_b = '431'
AND ACCOUNT = '5019'
AND simulation_voucher = 'FALSE'
AND accounting_period = 1
ORDER BY company, voucher_type, accounting_year, voucher_no;

execution plan is attached here with..

there is no difference in the exection plan for both languages..

  • Attachment: exeplan.PNG
    (Size: 15.05KB, Downloaded 72 times)
Re: Performance Issue... [message #355894 is a reply to message #355869] Tue, 28 October 2008 06:03 Go to previous messageGo to next message
dinendra_sl
Messages: 5
Registered: October 2008
Junior Member
Hi..

that was also reported by me .. Smile
Re: Performance Issue... [message #355897 is a reply to message #355894] Tue, 28 October 2008 06:25 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I know that. But why didn't you supply the required information when you have already been requested what is required and you didn't make any effort to prove what you are saying.

Regards

Raj
Re: Performance Issue... [message #355900 is a reply to message #355897] Tue, 28 October 2008 06:34 Go to previous messageGo to next message
dinendra_sl
Messages: 5
Registered: October 2008
Junior Member
Hi..

yes as I got the replies I modified it.. that's what truly happend..

cld you please help me to figure out the reason for this issue..?
Re: Performance Issue... [message #355901 is a reply to message #355900] Tue, 28 October 2008 06:56 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
What is cld? Looks like there is some problem with your keyboard.

Quote:

you didn't make any effort to prove what you are saying.


Also what happened to the request I have given recently.

For how to do it spend some time in reading the forum guidelines especially the very first post under performance tuning section. Without that information it will be a guess work and I don't want to do that.

Regards

Raj

[Updated on: Tue, 28 October 2008 06:56]

Report message to a moderator

Re: Performance Issue... [message #355987 is a reply to message #355901] Tue, 28 October 2008 22:20 Go to previous message
dinendra_sl
Messages: 5
Registered: October 2008
Junior Member
OK.. thanks a lot...
Previous Topic: Query returning 6 Records
Next Topic: sql query problem
Goto Forum:
  


Current Time: Sat Dec 03 22:18:20 CST 2016

Total time taken to generate the page: 0.09496 seconds