Home » RDBMS Server » Performance Tuning » how to tune query
how to tune query [message #218282] Wed, 07 February 2007 10:42 Go to next message
mjschwenger
Messages: 137
Registered: July 2005
Location: US
Senior Member
I have a problem with the query below and I cannot make it better myself - need help. The problem is that the tables have hundred of millions of rows and because of the not equal sign the index full scan performed slows down the execution.
I have all indexes needed sr.ent_id/sr.rel_id and s.ENT_ID.
Below are the 2 ways I tried but witht he same plan and timing.

select sr.rel_id, sr.ent_id, sr.l_conf, sr.rel_conf, sr.match_id
from sep_rel sr
where
not exists(select 1 from search s where s.ENT_ID = sr.ENT_ID)
--OR
--sr.ent_id not in (select ent_id from search s where s.ENT_ID = sr.ENT_ID)
and sr.rel_id in (?,?,?)

Thanks a lot, mj
Re: how to tune query [message #218325 is a reply to message #218282] Wed, 07 February 2007 14:49 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
1. How many rows are in sep_rel table?
2. How many distinct values has a rel_id column?
Re: how to tune query [message #218359 is a reply to message #218282] Wed, 07 February 2007 19:04 Go to previous messageGo to next message
mjschwenger
Messages: 137
Registered: July 2005
Location: US
Senior Member
Here are my numbers:
SQL> select count(*) from sep_rel;

COUNT(*)
-----------
408165802

SQL> select count(distinct rel_id) from sep_rel;

COUNT(DISTINCT REL_ID)
----------------------
112728065


Thanks a lot, mj
Re: how to tune query [message #218368 is a reply to message #218359] Wed, 07 February 2007 21:08 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Do you have an index on search.ent_id?
What about on sep_rel.rel_id?

Post the Explain Plan.

Ross Leishman
Re: how to tune query [message #218518 is a reply to message #218282] Thu, 08 February 2007 09:23 Go to previous messageGo to next message
mjschwenger
Messages: 137
Registered: July 2005
Location: US
Senior Member
Yes, I have indexes as follow:
sr.ent_id/sr.rel_id, (it is used for other reports as well)
sr.rel_id
and s.ENT_ID.
The plan includes full index scan on index sr.rel_id and range/table access by rowid on s.ENT_ID.
Thanks a lot ,mj
Re: how to tune query [message #218522 is a reply to message #218282] Thu, 08 February 2007 09:35 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
According to your indexes you were supposed to get good EXPLAIN.
Please post yours.
What is the data_type of rel_id column of sep_rel table?
I think it's VARCHAR2, but I would like to know for shure.

If it's VARCHAR2 then try:

select sr.rel_id, sr.ent_id, sr.l_conf, sr.rel_conf, sr.match_id 
from sep_rel sr 
where 
not exists(select 1 from search s where s.ENT_ID = sr.ENT_ID)
and sr.rel_id in (TO_CHAR(?),TO_CHAR(?),TO_CHAR(?))



HTH.
Michael
Re: how to tune query [message #218595 is a reply to message #218522] Thu, 08 February 2007 21:30 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I agree with Michael. There must be a reason why the full index scan is not an INLIST ITERATOR. Casting is a very likely suspect.

Also try gathering statistics on the table and index if you have not done so recently.

Ross Leishman
Re: how to tune query [message #218771 is a reply to message #218282] Sat, 10 February 2007 00:21 Go to previous messageGo to next message
mjschwenger
Messages: 137
Registered: July 2005
Location: US
Senior Member
All ID columns are numbers and the casting did not change anything. The statistics are fresh (I did rebuild the indexes even). And I rerun the server stats under the most recent load. The exec plan is not bad - just this full index scan and the timing that everybody complains.
Thanks a lot, mj
Re: how to tune query [message #218840 is a reply to message #218282] Sun, 11 February 2007 05:03 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Can you post the results of following queries:

SELECT * FROM USER_TABLES WHERE table_name = <your table name>;

SELECT * FROM USER_INDEXES WHERE table_name = <your table name>;

SELECT * FROM USER_TAB_COLUMNS WHERE table_name = <your table name>;

I would like to see the number of distinct values/keys for all relevant objects.
Previous Topic: SQL with high consistent gets
Next Topic: 9i to 10g
Goto Forum:
  


Current Time: Thu Dec 08 10:19:52 CST 2016

Total time taken to generate the page: 0.06768 seconds