Home » RDBMS Server » Performance Tuning » Sudden decrease in performance of query (Oracle, 10g, Linux)
icon5.gif  Sudden decrease in performance of query [message #606583] Mon, 27 January 2014 03:32 Go to next message
qmg1
Messages: 2
Registered: January 2014
Junior Member


I have a query like below:

SELECT col1,col2,col3,col4,col5
FROM
(select x.*,
to_number(substr(it_specs,instr(it_specs,',',1,1)+1,instr(it_specs,',',1,2)-instr(it_specs,',',1,1)-1)) unit1,
to_number(substr(it_specs,instr(it_specs,',',1,1)+1,instr(it_specs,',',1,2)-instr(it_specs,',',1,1)-1)) unit2,
to_number(substr(it_specs,instr(it_specs,',',1,1)+1,instr(it_specs,',',1,2)-instr(it_specs,',',1,1)-1)) unit3,
to_number(substr(it_specs,instr(it_specs,',',1,1)+1,instr(it_specs,',',1,2)-instr(it_specs,',',1,1)-1)) unit4,
to_number(substr(it_specs,instr(it_specs,',',1,1)+1,instr(it_specs,',',1,2)-instr(it_specs,',',1,1)-1)) unit5
from
(select
a.*,
(select IT_PKG.IT_SPECS(charges, TRUNC(usrdate), 'N')
from dual) AS it_Specs
from
tabl1 A
ORDER BY a.col1,a.col2, a.col3, a.col4, a.col5) x) a, tabl2 B
WHERE A.CA = B.CA
group by
col1,col2,col3,col4,col5

The number of records tabl1 ranges from 100 to 25000. The number of records in tabl2 are one thousands.
There are two table involved in function IT_SPECS. Each have half million records.
This query was working fine for few months with no performance issue.It can be seen from below trace:

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.01 0 2 0 0
Fetch 6177 0.90 1.04 375 761 0 12351
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6179 0.92 1.06 375 763 0 12351

But performance decreased significantly apparently for no reason.

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.03 0 0 0 0
Execute 1 0.02 0.10 239 153 0 0
Fetch 107 5.58 26.54 680 70660 0 10637
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 109 5.64 26.67 919 70813 0 10637

My question why I am getting high query value and elapsed time is also high?
The fetch count is second case is less but it has no impact on performance.
Re: Sudden decrease in performance of query [message #606586 is a reply to message #606583] Mon, 27 January 2014 03:38 Go to previous messageGo to next message
John Watson
Messages: 4693
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read our OraFAQ Forum Guide and please read How to use [code] tags and make your code easier to read

I've formatted your code, here:
SELECT col1,
       col2,
       col3,
       col4,
       col5
FROM   (SELECT x.*,
               To_number(Substr(it_specs, Instr(it_specs, ',', 1, 1) + 1,
                                   Instr(it_specs, ',', 1, 2) -
                                   Instr(it_specs, ',', 1, 1) - 1)
               ) unit1,
               To_number(Substr(it_specs, Instr(it_specs, ',', 1, 1) + 1,
                                   Instr(it_specs, ',', 1, 2) -
                                   Instr(it_specs, ',', 1, 1) - 1)
               ) unit2,
               To_number(Substr(it_specs, Instr(it_specs, ',', 1, 1) + 1,
                                   Instr(it_specs, ',', 1, 2) -
                                   Instr(it_specs, ',', 1, 1) - 1)
               ) unit3,
               To_number(Substr(it_specs, Instr(it_specs, ',', 1, 1) + 1,
                                   Instr(it_specs, ',', 1, 2) -
                                   Instr(it_specs, ',', 1, 1) - 1)
               ) unit4,
               To_number(Substr(it_specs, Instr(it_specs, ',', 1, 1) + 1,
                                   Instr(it_specs, ',', 1, 2) -
                                   Instr(it_specs, ',', 1, 1) - 1)
               ) unit5
        FROM   (SELECT a.*,
                       (SELECT it_pkg.It_specs(charges, Trunc(usrdate), 'N')
                        FROM   dual) AS it_Specs
                FROM   tabl1 A
                ORDER  BY a.col1,
                          a.col2,
                          a.col3,
                          a.col4,
                          a.col5) x) a,
       tabl2 B
WHERE  A.ca = B.ca
GROUP  BY col1,
          col2,
          col3,
          col4,
          col5 
You see how much easier that is to read?
You'll need to get an execution plan for the statement and provide the DDL of tables and indexes. All this is described in the Guide.
Re: Sudden decrease in performance of query [message #606605 is a reply to message #606586] Mon, 27 January 2014 04:37 Go to previous messageGo to next message
cookiemonster
Messages: 11076
Registered: September 2008
Location: Rainy Manchester
Senior Member
Are you sure that's the correct query?
Because as far as I can see it's equivalent to this:
SELECT col1,
       col2,
       col3,
       col4,
       col5
FROM tabl1 A,
     tabl2 B
WHERE  A.ca = B.ca
GROUP  BY col1,
          col2,
          col3,
          col4,
          col5 

Re: Sudden decrease in performance of query [message #606614 is a reply to message #606605] Mon, 27 January 2014 05:42 Go to previous messageGo to next message
qmg1
Messages: 2
Registered: January 2014
Junior Member
Here is correct query:

SELECT col1,col2,col3,col4,col5,unit1,unit2,unit3,unit4,unit5
FROM
(select x.*,
to_number(substr(it_specs,instr(it_specs,',',1,1)+1,instr(it_specs,',',1,2)-instr(it_specs,',',1,1)-1)) unit1,
to_number(substr(it_specs,instr(it_specs,',',1,1)+1,instr(it_specs,',',1,2)-instr(it_specs,',',1,1)-1)) unit2,
to_number(substr(it_specs,instr(it_specs,',',1,1)+1,instr(it_specs,',',1,2)-instr(it_specs,',',1,1)-1)) unit3,
to_number(substr(it_specs,instr(it_specs,',',1,1)+1,instr(it_specs,',',1,2)-instr(it_specs,',',1,1)-1)) unit4,
to_number(substr(it_specs,instr(it_specs,',',1,1)+1,instr(it_specs,',',1,2)-instr(it_specs,',',1,1)-1)) unit5
from
(select
a.*,
(select IT_PKG.IT_SPECS(charges, TRUNC(usrdate), 'N')
from dual) AS it_Specs
from
tabl1 A
ORDER BY a.col1,a.col2, a.col3, a.col4, a.col5) x) a, tabl2 B
WHERE A.CA = B.CA
group by
col1,col2,col3,col4,col5,unit1,unit2,unit3,unit4,unit5
Re: Sudden decrease in performance of query [message #606616 is a reply to message #606586] Mon, 27 January 2014 05:44 Go to previous message
cookiemonster
Messages: 11076
Registered: September 2008
Location: Rainy Manchester
Senior Member
John Watson wrote on Mon, 27 January 2014 09:38
Welcome to the forum. Please read our OraFAQ Forum Guide and please read How to use [code] tags and make your code easier to read

.....
.....

You'll need to get an execution plan for the statement and provide the DDL of tables and indexes. All this is described in the Guide.

Previous Topic: Is this example correct?
Next Topic: Assigning Quota on morethan one tablespace
Goto Forum:
  


Current Time: Fri Oct 24 14:14:06 CDT 2014

Total time taken to generate the page: 0.17875 seconds