| the query is too slow [message #285703] |
Wed, 05 December 2007 06:11  |
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 #285720 is a reply to message #285705] |
Wed, 05 December 2007 07:01  |
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 #285748 is a reply to message #285735] |
Wed, 05 December 2007 07:33 |
skooman
Messages: 913
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 |
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 #286010 is a reply to message #285978] |
Thu, 06 December 2007 05:18 |
michael_bialik
Messages: 621
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 |
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 722 times)
[Updated on: Thu, 06 December 2007 05:37] Report message to a moderator |
|
|
|
|
|
|
|
| Re: the query is too slow [message #286205 is a reply to message #286042] |
Thu, 06 December 2007 19:54 |
rleishman
Messages: 3728
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 |
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 |
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 709 times)
[Updated on: Fri, 07 December 2007 00:08] Report message to a moderator |
|
|
|
|
|
| Re: the query is too slow [message #286236 is a reply to message #286235] |
Fri, 07 December 2007 00:09 |
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 #286242 is a reply to message #286238] |
Fri, 07 December 2007 00:24 |
daria
Messages: 12
Registered: December 2007
Location: Russian Federation
|
Junior Member |
|
|
!!! 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!
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
