Home » RDBMS Server » Performance Tuning » SQL performance tuning
SQL performance tuning [message #214795] Thu, 18 January 2007 02:14 Go to next message
rawat_me
Messages: 45
Registered: September 2005
Member
Hi Gurus,

I Have the following query which i want to tune.

a.add,
a.stamp_updt,
b.cur_typ_c
from record_master a,
Detail_record b
where a.id = p_id
and a.unit_id in (select unit_id from list_home)
and a.unit_c in (select unit_c from list_copy)
and a.id_c = b.id_c


Where record_master is the driving table containing 20 Million
reocrd and detail_record has around 5 Million record.
Also list_home and list_copy contains around 1 Million record.

Where p_id is the input from the screen.

There is a index in the table record_master whose order is
( id, unit_id , unit_c, id_c ) .

I am unable to see any improvement in the query because of IN clause in the Query.

Any suggestion is most useful.

DR


Re: SQL performance tuning [message #214818 is a reply to message #214795] Thu, 18 January 2007 03:35 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Both of those IN clauses are going to cause Full Table scans on million row tables, which isn't going to make anything any quicker.

If there is in index on Unit_id on table List_Home, and on Unit_c on table List_Copy, then you could use Exists :
a.add, 
a.stamp_updt, 
b.cur_typ_c 
from record_master a, 
Detail_record b 
where a.id = p_id
and exists (select null from list_home l where l.unit_id = a.unit_id) 
and exists (select null from list_copy c where c.unit_c a.unit_c) 
and a.id_c = b.id_c
Re: SQL performance tuning [message #214992 is a reply to message #214818] Thu, 18 January 2007 20:53 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
JRowbottom wrote on Thu, 18 January 2007 20:35
Both of those IN clauses are going to cause Full Table scans on million row tables,...


Not necessarily these days. The optimizer will often rewrite such a query to be the equivalent of an EXISTS. It's unlikely in this case though - more probably it will produce a HASH SEMI-JOIN, which may actually be the best solution depending on the number of distinct unit_id and unit_c values.

Ross Leishman
Re: SQL performance tuning [message #214993 is a reply to message #214818] Thu, 18 January 2007 20:55 Go to previous messageGo to next message
rawat_me
Messages: 45
Registered: September 2005
Member
Hi,
But in your query the index on the master table record_master
will not be picked.

Also what if list_home and list_copy has only few thousand records.In that case i think IN is fine and the query will pick the index as well.

Thanks
Re: SQL performance tuning [message #214994 is a reply to message #214993] Thu, 18 January 2007 20:56 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Post your Explain Plan
Re: SQL performance tuning [message #214998 is a reply to message #214795] Thu, 18 January 2007 21:23 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
With no join criteria between list_home and list_copy doesn't a cartesian product result?
Re: SQL performance tuning [message #215023 is a reply to message #214998] Fri, 19 January 2007 00:23 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Confused Umm, no. They're in separate sub-queries. They're not joined.

[Updated on: Sat, 20 January 2007 19:15]

Report message to a moderator

Re: SQL performance tuning [message #215289 is a reply to message #215023] Sat, 20 January 2007 10:11 Go to previous messageGo to next message
rawat_me
Messages: 45
Registered: September 2005
Member
Hi All
I am attaching the Explain plan for the Test Query .
=========================================================
SELECT STATEMENT Cost = 11
SORT AGGREGATE
HASH JOIN SEMI
HASH JOIN SEMI
HASH JOIN
TABLE ACCESS FULL record_master
TABLE ACCESS FULL Detail_record
TABLE ACCESS FULL list_copy
TABLE ACCESS FULL list_home

=======================================================

The record_master has 1 Million record and detail_record has
5000 records.
The list_copy and list_home have only 1000 records.

Any help is most welcome.

rawat
Re: SQL performance tuning [message #215309 is a reply to message #214795] Sat, 20 January 2007 19:25 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
rawat_me wrote on Thu, 18 January 2007 19:14
a.add,
a.stamp_updt,
b.cur_typ_c
from record_master a,
Detail_record b
where a.id = p_id
and a.unit_id in (select unit_id from list_home)
and a.unit_c in (select unit_c from list_copy)
and a.id_c = b.id_c

You didn't explain the right query. I was willing to overlook the missing SELECT keyword in the original query, but your EXPLAIN PLAN shows a SORT AGGREGATE step, which means it included a SELECT COUNT(*) or similar.

  • Post the actual query
  • Post the actual Explain Plan of that query
  • Enclose both of them in [code] and [/code] tags so that we can read them properly in our browser.
  • Tell us what p_id is in the original query. A column? A PL/SQL variable?
  • How many of the 1M rows in the master do you expect to be needed in the query.
  • Double-check! Confirm you have done ALL of the above before posting back.


Have you tried analyzing your tables? If p_id is a PL/SQL variable, I'm surprised Oracle is not using an index.

Ross Leishman
Re: SQL performance tuning [message #215313 is a reply to message #215309] Sat, 20 January 2007 22:09 Go to previous messageGo to next message
rawat_me
Messages: 45
Registered: September 2005
Member
Hi

This is the code
=======================================================
select a.add,
a.stamp_updt,
b.cur_typ_c
from record_master a , Detail_record b
where a.id = p_id
and a.unit_id in (select unit_id from list_home)
and a.unit_c in (select unit_c from list_copy)
and a.id_c = b.id_c
=======================================================

Explain plan i am attaching in the file.

p_id is the input parameter ( Variable ) .

Average 2-3K records are coming for the query but taking around 14-15 Min.

Thanks.

Re: SQL performance tuning [message #215341 is a reply to message #214795] Sun, 21 January 2007 11:40 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
please post
SQL> DESC record_master
SQL> DESC Detail_record
Place index on each individual column a.id, p_id, a.unit_id, unit_c, a.id_c, & b.id_c

[Updated on: Sun, 21 January 2007 13:23] by Moderator

Report message to a moderator

Re: SQL performance tuning [message #215356 is a reply to message #215341] Sun, 21 January 2007 19:48 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
That COST of 11 tells me that Oracle thinks the tables are very small. There's no way a full scan of a 1M row table would cost that little.

Gather statistics on all tables like I suggested earlier.

If there are only 2-3K rows with a.id = p_id, then you will need an index on record_master.id

If Detail_record contains only 5000 rows, you won't need an index. But you seem to be confused about this, because earlier you told us it had 5M rows. If that is the case, you'll need an index on Detail_record.id_c

If list_home and list_copy contain only 1000 rows each, they will not need an index either. However, if - as you indicated in your earlier post - they have 1M rows, you will need indexes on list_home.unit_id and list_copy.unit_c

Work out from this which indexes you will need, build them, gather statistics on all tables, and then re-explain your plan.
It should look something like:

NESTED LOOPS (SEMI)
  NESTED LOOPS (SEMI)
    NESTED LOOPS
      TABLE ACCESS (BY INDEX ROWID) record_master
        INDEX RANGE SCAN record_master(id)
      TABLE ACCESS (BY INDEX ROWID) Detail_record
        INDEX RANGE SCAN Detail_record(id_c)
    TABLE ACCESS (BY INDEX ROWID) list_home
      INDEX RANGE SCAN list_home(unit_id)
  TABLE ACCESS (BY INDEX ROWID) list_copy
    INDEX RANGE SCAN list_home(unit_c)
if the tables are very large as you first indicated, or

HASH JOIN (SEMI)
  HASH JOIN (SEMI)
    HASH JOIN
      TABLE ACCESS (BY INDEX ROWID) record_master
        INDEX RANGE SCAN record_master(id)
      TABLE ACCESS (FULL) Detail_record
    TABLE ACCESS (FULL) list_home
  TABLE ACCESS (FULL) list_copy
if the tables (other than master) are small, as you later indicated.

Do not index record_master.unit_id or record_master.unit_c - it will just confuse the optimizer.

Ross Leishman
Re: SQL performance tuning [message #215357 is a reply to message #215356] Sun, 21 January 2007 20:17 Go to previous messageGo to next message
rawat_me
Messages: 45
Registered: September 2005
Member
Hi Ross,
I will do as per your suggestion and see if it works.

Can you please clarify is there any need of the index on all four columns because at present there is an index
on the table record_master whose order is
( id, unit_id , unit_c, id_c ) .

Thanks
DR
Re: SQL performance tuning [message #215361 is a reply to message #215357] Sun, 21 January 2007 21:27 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
It wont hurt. If the number of rows to be returned from the master was greater, it would actually help. With your volumes I doubt you'll notice.

Ross Leishman
Previous Topic: How to see the constraints in the table ?
Next Topic: Veiw synonym performs differently in diferent users
Goto Forum:
  


Current Time: Tue Dec 06 04:10:53 CST 2016

Total time taken to generate the page: 0.09102 seconds