Home » RDBMS Server » Performance Tuning » How i can increase the performance of my query
How i can increase the performance of my query [message #380594] Mon, 12 January 2009 08:19 Go to next message
eng.oracle
Messages: 48
Registered: December 2007
Member
I have a table have 25,000 record or more as Statistics

and I'm using this code to get Statistics every month
for each employee:
SELECT     COUNT (ch)+ NVL(COUNT(chn),0)  ch,
                  COUNT (wn)    wn,
                  COUNT (sn) sn,
                  COUNT (dn) -  NVL(COUNT(chn),0) dn,
                  COUNT(gi)         gis,
                  COUNT(st)         stu
           INTO   vch, vw, vs, vdn,vgi,vbr
            FROM (
                  SELECT  ky,DECODE(a.rc,3,3,5,5,10,10,13,13) ch,
                              DECODE(a.rc,2,2) wn,
                              DECODE(a.rc,5,5,17,17) sn,
                              DECODE(a.rc,4,4,9,9,10,10,11,11,12,12,14,14,15,15,16,16) dn,
                              DECODE(a.rc,10,10) gi,
                              DECODE(a.rc,17,17) st,
                              DECODE(a.rc,9,DECODE(A.rt,2,9,9)) chn
                 FROM  rdt a
                 WHERE a.ky  =v_ky
                 AND   trunc(a.str_dt,'MM') <= p_month
          
                       );


where v_ky & p_month is variable;
but it is very slow, how i can increase the performance by using
view or others to get the same result, with less time.

thx
Re: How i can increase the performance of my query [message #380606 is a reply to message #380594] Mon, 12 January 2009 10:39 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
A condition like
a.str_dt < add_months(trunc(p_month,'MM'),1)

instead of
trunc(a.str_dt,'MM') <= p_month

could let you use an index on str_dt.

Bye Alessandro

[Updated on: Mon, 12 January 2009 10:40]

Report message to a moderator

Re: How i can increase the performance of my query [message #380607 is a reply to message #380594] Mon, 12 January 2009 11:26 Go to previous messageGo to next message
eng.oracle
Messages: 48
Registered: December 2007
Member
thanks for you,
but this code used in procedure
Re: How i can increase the performance of my query [message #380610 is a reply to message #380594] Mon, 12 January 2009 12:20 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
look at materialized views and query rewrite.

That way you can rewrite the whole query and it will just do a single lookup ont he Mview.
Re: How i can increase the performance of my query [message #380611 is a reply to message #380594] Mon, 12 January 2009 12:51 Go to previous messageGo to next message
eng.oracle
Messages: 48
Registered: December 2007
Member
thanks for replay.
How i can used Materialized View with varablie like p_month & v_ky, please give me example
Re: How i can increase the performance of my query [message #380613 is a reply to message #380606] Mon, 12 January 2009 13:16 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Alessandro Rossi wrote on Mon, 12 January 2009 17:39
could let you use an index on str_dt.

Yes, but the question is: does the queried table RDT (if it is not a view) have any indexes? Also according to
eng.oracle wrote on Mon, 12 January 2009 15:19
and I'm using this code to get Statistics every month for each employee
,it would be more important to index "employee" column (whatever it is) as the first one.
Also knowing the number of distinct "employees" would be useful to know (as thousand times nothing tormented a donkey to death, as one proverb says). You also did not post data types of all columns and variables - an implicit conversion may degrade performance too.

Of course, the exact figures about processing may be obtained from tracing the session (see http://www.orafaq.com/wiki/SQL_Trace) and using tkprof to analyze its output - we could save a lot of guesses here.
Re: How i can increase the performance of my query [message #380736 is a reply to message #380613] Tue, 13 January 2009 03:16 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
An index on ky and str_dt in that case would obviously be faster than one on the only column ky that you were talking about.

I supposed the simplest situation: that rdt is a table and that it contains no more columns than those ones used in the query. In a simple case as this, an index on both the columns would be the only way to optimize its response times.

An analysis of trace files or addm/statspack report may just show problems on the system in this case.

For materialized views I have no idea on how to implement a fast refreshable one to use for the query rewrite.

Bye Alessandro
Re: How i can increase the performance of my query [message #380801 is a reply to message #380594] Tue, 13 January 2009 07:21 Go to previous messageGo to next message
eng.oracle
Messages: 48
Registered: December 2007
Member
thanks for all, I will used the index.
indx1-------- ky
indx2---------str_dt
indx3----------(ky,str_dt)
any one have any note about this indexs

thank you
Re: How i can increase the performance of my query [message #380867 is a reply to message #380613] Tue, 13 January 2009 11:32 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
flyboy wrote on Mon, 12 January 2009 20:16
Also knowing the number of distinct "employees" would be useful to know ... You also did not post data types of all columns and variables - an implicit conversion may degrade performance too.

Of course, the exact figures about processing may be obtained from tracing the session (see http://www.orafaq.com/wiki/SQL_Trace) and using tkprof to analyze its output - we could save a lot of guesses here.

eng.oracle wrote on Tue, 13 January 2009 14:21
indx1-------- ky
indx2---------str_dt
indx3----------(ky,str_dt)
any one have any note about this indexs

Only INDX1 may be used by the query you posted, as Alessandro Rossi already remarked. However if the column KY and variable V_KY have different data types, Oracle will not be able to use it, so full table scan would be performed.
Re: How i can increase the performance of my query [message #380887 is a reply to message #380594] Tue, 13 January 2009 16:51 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
"and I'm using this code to get Statistics every month
for each employee:"

Then you dont need fast refresh mview for this. A standard mview would do for a monthly job.

"How i can used Materialized View with varablie like p_month & v_ky, please give me example "

Have you tried it? and read the manual on query rewrite? Doesnt sound like it.

Also read up on advanced query rewrite, althugh you should not need that for this example.

[Updated on: Tue, 13 January 2009 16:53]

Report message to a moderator

Re: How i can increase the performance of my query [message #381449 is a reply to message #380594] Sat, 17 January 2009 01:13 Go to previous message
eng.oracle
Messages: 48
Registered: December 2007
Member
Thanks for all, i will do that.
Previous Topic: Slowness of sql query (merged)
Next Topic: Working slowley after 6 to 7hrs
Goto Forum:
  


Current Time: Sat Dec 03 16:31:12 CST 2016

Total time taken to generate the page: 0.09634 seconds