OraFAQ Forum: Performance Tuning » Sudden decrease in performance of query

Home » RDBMS Server » Performance Tuning » Sudden decrease in performance of query (Oracle, 10g, Linux)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Sudden decrease in performance of query [message #606583]

Mon, 27 January 2014 03:32

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.

Report message to a moderator

Re: Sudden decrease in performance of query [message #606586 is a reply to message #606583]

Mon, 27 January 2014 03:38

John Watson
Messages: 8960
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.

Report message to a moderator

Re: Sudden decrease in performance of query [message #606605 is a reply to message #606586]

Mon, 27 January 2014 04:37

cookiemonster
Messages: 13958
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

Report message to a moderator

Re: Sudden decrease in performance of query [message #606614 is a reply to message #606605]

Mon, 27 January 2014 05:42

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

Report message to a moderator

Re: Sudden decrease in performance of query [message #606616 is a reply to message #606586]

Mon, 27 January 2014 05:44

cookiemonster
Messages: 13958
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.

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Nov 21 11:06:48 CST 2024