Home » RDBMS Server » Performance Tuning » the query is too slow
the query is too slow [message #285703] Wed, 05 December 2007 06:11 Go to next message
daria
Messages: 12
Registered: December 2007
Location: Russian Federation
Junior Member
Hello. Is there any suggestions, haw can I improve query to make it work more rapidly?
From time to time I receive nothing at all and terminate execution after several minutes.

select /*+ index (BUF_V_INT i_but_v_int_u1,GR_GR i_gr_gr_u) */ round(sum(rash_poln)) t1
from cnt.GR_GR g, cnt.BUF_V_INT b
where b.n_ob = 275000
and g.n_ob = 275000
and b.dd_mm_yyyy = TO_DATE('24.11.2007','DD/MM/YYYY')
and g.n_gr_integr = 9
and g.N_FID = b.N_FID and g.N_GR_TY = b.N_GR_TY
group by trunc(n_inter_ras/2+0.6)

group by trunc(n_inter_ras/2+0.6) is to sum measurement made once in 30 min and represent results by hours. BUF_V_INT has about 23000000 rows. Is that a reason for acting slow?

I have an awful feeling, that the question is hopelessly silly. Sorry.
Re: the query is too slow [message #285705 is a reply to message #285703] Wed, 05 December 2007 06:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64107
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Always post your Oracle version (4 decimals).

Also read How to Identify Performance Problem and Bottleneck .

Regards
Michel
Re: the query is too slow [message #285720 is a reply to message #285705] Wed, 05 December 2007 07:01 Go to previous messageGo to next message
daria
Messages: 12
Registered: December 2007
Location: Russian Federation
Junior Member
I did ) Thanks.
I use Oracle 8, it does not have ASH, does he?
The next problem is that I am granted select permission, but nothing more. I can not use EXPLAIN PLAN and tune Oracle also. But I have another application, acting much better. It makes the same selection in seconds. I just cannot imagine, what is the trick. One table, same parameters. As it is not open source, I cannot take a look.

[Updated on: Wed, 05 December 2007 07:12]

Report message to a moderator

Re: the query is too slow [message #285735 is a reply to message #285720] Wed, 05 December 2007 07:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64107
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you don't have the privileges to make research and tune your query, you can't do it.
Ask to have these privileges or tell you can't do this job.

By the way, the link contains much more than ASH.

Regards
Michel
Re: the query is too slow [message #285748 is a reply to message #285735] Wed, 05 December 2007 07:33 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Quote:

But I have another application, acting much better. It makes the same selection in seconds.

One wild guess, if those databases are the same (so, same datamodel, same oracle version, same data): ask the administrator if in both databases the statistics are up to date.

Besides that, you obviously have to have the privileges to tune statements if it's part of your job to tune statements...
Re: the query is too slow [message #285766 is a reply to message #285748] Wed, 05 December 2007 09:09 Go to previous messageGo to next message
daria
Messages: 12
Registered: December 2007
Location: Russian Federation
Junior Member
I am afraid it is the same table ) One database, one schema, one table. I just want to get the same data in my application. And I do, I get absolutely the same numbers, but very slowly.
Re: the query is too slow [message #285768 is a reply to message #285766] Wed, 05 December 2007 09:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64107
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

ask the administrator if in both databases the statistics are up to date.

Regards
Michel
Re: the query is too slow [message #285978 is a reply to message #285768] Thu, 06 December 2007 04:13 Go to previous messageGo to next message
daria
Messages: 12
Registered: December 2007
Location: Russian Federation
Junior Member
Hi! I've got a Plan Table now ) There in attachment it is. Looks like all indexes are used. Does It matter?./fa/3526/0/
  • Attachment: t.GIF
    (Size: 4.42KB, Downloaded 326 times)

[Updated on: Thu, 06 December 2007 19:48] by Moderator

Report message to a moderator

Re: the query is too slow [message #286010 is a reply to message #285978] Thu, 06 December 2007 05:18 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
It's very nice that the query is using indexes, but we don't know the definitions:(columns and order of columns, data types, etc.).

A number of questions:
1. How many distinct values do you have for n_ob column of BUF_V_INT table?
-- Use
select count(distinct n_ob) from BUF_V_INT


to find it.

2. How many rows are selected by following statement:

SELECT COUNT(*) from BUF_V_INT
WHERE n_ob = 275000 and 
   dd_mm_yyyy = TO_DATE('24.11.2007','DD/MM/YYYY')


3. What are data types of n_ob and dd_mm_yyyy columns of BUF_V_INT table?

4. Do you have an index with these 2 columns as leading columns?

5. Do you have an index on GR_GR tables with following leading columns:
a. n_ob
b. n_gr_integr
c. N_FID
d. N_GR_TY

?
Re: the query is too slow [message #286023 is a reply to message #286010] Thu, 06 December 2007 05:35 Go to previous messageGo to next message
daria
Messages: 12
Registered: December 2007
Location: Russian Federation
Junior Member
Ok. Thanks!
SQL> describe cnt.buf_v_int;
 Name                            Null?    Type
 ------------------------------- -------- ----
 SYB_RNK                                  NUMBER(3)
 N_OB                                     NUMBER(8)
 N_FID                                    NUMBER(4)
 N_GR_TY                                  NUMBER(4)
 N_SH                                     NUMBER(10)
 DD_MM_YYYY                               DATE
 N_INTER_RAS                              NUMBER(4)
 KOL_DB                                   NUMBER(7)
 KOL                                      NUMBER(7)
 VAL                                      NUMBER
 STAT                                     VARCHAR2(1)
 MIN_0                                    NUMBER(4)
 MIN_1                                    NUMBER(4)
 INTERV                                   NUMBER(2)
 AK_SUM                                   NUMBER
 POK_START                                NUMBER
 RASH_POLN                                NUMBER
 IMPULSES                                 NUMBER



And about distinct:

SQL> edit
Wrote file afiedt.buf

  1* select count(distinct n_ob) from cnt.BUF_V_INT
SQL> /


COUNT(DISTINCTN_OB)
-------------------
                 18


About index in attachment )

And the last.

SQL> edit
Wrote file afiedt.buf

  1  SELECT COUNT(*) from cnt.BUF_V_INT
  2  WHERE n_ob = 275000 and
  3*    dd_mm_yyyy = TO_DATE('24.11.2007','DD/MM/YYYY')
SQL> /

 COUNT(*)
---------
     5568


  • Attachment: t2.GIF
    (Size: 58.87KB, Downloaded 160 times)

[Updated on: Thu, 06 December 2007 05:37]

Report message to a moderator

Re: the query is too slow [message #286027 is a reply to message #286023] Thu, 06 December 2007 05:42 Go to previous messageGo to next message
daria
Messages: 12
Registered: December 2007
Location: Russian Federation
Junior Member
May be I should reorganize query to get unique scan in Buf table?

[Updated on: Thu, 06 December 2007 05:42]

Report message to a moderator

Re: the query is too slow [message #286042 is a reply to message #286027] Thu, 06 December 2007 06:19 Go to previous messageGo to next message
daria
Messages: 12
Registered: December 2007
Location: Russian Federation
Junior Member
GR_GR is a very small table. And only 2-12 rows are selected from there.
Re: the query is too slow [message #286205 is a reply to message #286042] Thu, 06 December 2007 19:54 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
- Remove the hint on GR_GR
/*+ index (BUF_V_INT i_but_v_int_u1) */ 

This should allow Oracle to perform a FULL TABLE SCAN and HASH JOIN on that table, which will be much better than the INDEXED NESTED LOOPS it is currently performing.

- Make sure that n_ob and dd_mm_yyyy are the FIRST TWO columns in the index i_but_v_int_u1 (in that order or reverse order). If not, make it so - add another index if necessary.

Ross Leishman
Re: the query is too slow [message #286221 is a reply to message #286205] Thu, 06 December 2007 23:16 Go to previous messageGo to next message
daria
Messages: 12
Registered: December 2007
Location: Russian Federation
Junior Member
No, this hint does not play any roll... plan is the same.
dd_mm_yyyy is the 5-th in index, and I can not change anything. I am just a user for that database, because it is a part of licenced programm which is supported by other company. I can not add my index there.
Re: the query is too slow [message #286233 is a reply to message #286221] Thu, 06 December 2007 23:54 Go to previous messageGo to next message
daria
Messages: 12
Registered: December 2007
Location: Russian Federation
Junior Member
I am totally puzzled...
select /*+ full(BUF_V_INT) */  round(sum(rash_poln)) t1
from cnt.GR_GR@cnt g, cnt.BUF_V_INT@cnt b 
where g.n_ob = 275000 
and b.n_ob = 275000 
and b.dd_mm_yyyy = TO_DATE('24.11.2007','DD/MM/YYYY') 
and g.n_gr_integr = 1
and b.N_FID = g.N_FID and b.N_GR_TY = g.N_GR_TY
and b.syb_rnk = 2 and b.N_gr_ty = g.N_gr_ty
group by trunc(n_inter_ras/2+0.6)

has the plan in attachment.. And works 36 sec. In index variant it takes 81 sec for querying.
  • Attachment: 2.GIF
    (Size: 6.69KB, Downloaded 147 times)

[Updated on: Fri, 07 December 2007 00:08]

Report message to a moderator

Re: the query is too slow [message #286235 is a reply to message #285703] Fri, 07 December 2007 00:05 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>has the same plan.
What has same plan as what?
Is that good or bad & why?
Re: the query is too slow [message #286236 is a reply to message #286235] Fri, 07 December 2007 00:09 Go to previous messageGo to next message
daria
Messages: 12
Registered: December 2007
Location: Russian Federation
Junior Member
Sorry, anacedent. I mixed plans in plan_table first time. Full access looks better than indexed. Is that bad or good? I don't know. but half a minute is still slow. It takes 2 sec in the other application.

[Updated on: Fri, 07 December 2007 00:13]

Report message to a moderator

Re: the query is too slow [message #286237 is a reply to message #285703] Fri, 07 December 2007 00:13 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>Sorry, anacedent. I mixed plans in plan_table first time.
So you are re-writing your posts/history to erase mistakes.
Re: the query is too slow [message #286238 is a reply to message #286237] Fri, 07 December 2007 00:16 Go to previous messageGo to next message
daria
Messages: 12
Registered: December 2007
Location: Russian Federation
Junior Member
Yes. And as I am translating a lot with dictionary to English it takes much more time than for you to answer.
Re: the query is too slow [message #286242 is a reply to message #286238] Fri, 07 December 2007 00:24 Go to previous message
daria
Messages: 12
Registered: December 2007
Location: Russian Federation
Junior Member
Laughing
!!! I love all of you! I found!
select  /*+ index(b) full(g)*/  round(sum(rash_poln)) t1
from cnt.GR_GR@cnt g, cnt.BUF_V_INT@cnt b 
where g.n_ob = 275000 
and b.n_ob = 275000 
and b.dd_mm_yyyy = TO_DATE('24.11.2007','DD/MM/YYYY') 
and g.n_gr_integr = 9
and b.N_FID = g.N_FID and b.N_GR_TY = g.N_GR_TY
and b.syb_rnk = 2 and b.N_gr_ty = g.N_gr_ty
group by trunc(n_inter_ras/2+0.6)

works 0,02 sec!
Previous Topic: Query with aggregate function not using index
Next Topic: disadvantages of optimization
Goto Forum:
  


Current Time: Mon Dec 05 04:42:31 CST 2016

Total time taken to generate the page: 0.18552 seconds