Home » RDBMS Server » Performance Tuning » SQL tuning
SQL tuning [message #511146] Thu, 09 June 2011 13:43 Go to next message
hannah00
Messages: 37
Registered: March 2005
Member
Hi all,
select start, medication, id_event, JOB_status from client_sim
where id_even='ATC' and JOB_status in ('ACTIVE', NACTIVE', 'RUNNING')

above the query, oracle will do FTS on the table and the table itself had 40 million row.

I already did two trial tests:
1. create on index for both columns: id_even and job_status
2. on different test I created two indexes: one for id_even and one for job_status.

run both scenerios but neither of them picked up the indexes. In other word, it still do FTS.

Any thoughts or advises.
Thanks,
Re: SQL tuning [message #511147 is a reply to message #511146] Thu, 09 June 2011 13:45 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
After creating any index, did you collect statistics so CBO might decide to use them?

WHY MY INDEX IS NOT BEING USED
http://communities.bmc.com/communities/docs/DOC-10031

http://searchoracle.techtarget.com/tip/Why-isn-t-my-index-getting-used

http://www.orafaq.com/tuningguide/not%20using%20index.html
Re: SQL tuning [message #511150 is a reply to message #511146] Thu, 09 June 2011 15:17 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
hannah00 wrote on Thu, 09 June 2011 14:43
Hi all,
select start, medication, id_event, JOB_status from client_sim
where id_even='ATC' and JOB_status in ('ACTIVE', NACTIVE', 'RUNNING')

above the query, oracle will do FTS on the table and the table itself had 40 million row.

I already did two trial tests:
1. create on index for both columns: id_even and job_status
2. on different test I created two indexes: one for id_even and one for job_status.

run both scenerios but neither of them picked up the indexes. In other word, it still do FTS.


Sometimes a FTS is more efficient than using the index. How many rows out of all 40 million have those 3 values for job_status? How many rows have that value for id_even?
Read the links by BlackSwan. They will help to understand.

[edit] typo

[Updated on: Thu, 09 June 2011 15:17]

Report message to a moderator

Re: SQL tuning [message #511160 is a reply to message #511146] Thu, 09 June 2011 22:30 Go to previous messageGo to next message
hannah00
Messages: 37
Registered: March 2005
Member
there are around 150,000 rows on two values and less then 20,000 rows on one value of job_status out out 40 mil. There are only less than 100 rows on id_even.

I did two tests: the first test I created two indexes: one index for each column and the second test I created one index for both columns. The index NEVER get picked up from both tests. When I added the INDEX hint on both tests, then the indexes got picked up; however, I noticed the cost is even higher then FTS. From these two tests, I can possibly conclude that the index is being used in a FULL SCAN and and it's better to do FTS.

1. is it correct analysis?
2. I am thiking about partition the table, what's best method to deal with this table?
3. Any other advises that can optimize the query?

Thanks all and have a great weekend everyone.
Re: SQL tuning [message #511161 is a reply to message #511160] Thu, 09 June 2011 22:32 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof

[Updated on: Thu, 09 June 2011 22:33]

Report message to a moderator

Re: SQL tuning [message #511249 is a reply to message #511146] Fri, 10 June 2011 07:33 Go to previous messageGo to next message
Yasir Hashmi
Messages: 304
Registered: April 2006
Senior Member
The usage of indexes not only depend on the cardinality of the rows alone. It also depends on the cardinality of the blocks those rows are contained in.
It could be that the rows returned by the query are scattered throughout the blocks.As goal of tuning is to minimize block read ,It might be a possibility that though the rows are few but distributed throughout the blocks.So index access would mean single I/o for many blocks resulting in larger block visit hence the cost is high.With full table scans,oracle would do multiple I/Os hence might be efficient.
The defining factor for using the index is the clustering factor.
If the clustering factor is closer to the number of rows,oracle won't use the index.
Re: SQL tuning [message #511415 is a reply to message #511160] Sun, 12 June 2011 21:33 Go to previous messageGo to next message
msol25
Messages: 396
Registered: June 2011
Senior Member
hi,

Yes,if we are selecting rows more than 80% then we should use full table scan.If we are selecting less rows like our where clause is returning only 20-30% rows then we should create index for columns i.e mentioned in where clause and then analyze table using

Alter table emp compute statistics

then your index will automatically use,You no need to mention index hint.

Thanks
Re: SQL tuning [message #511498 is a reply to message #511146] Mon, 13 June 2011 08:34 Go to previous messageGo to next message
hannah00
Messages: 37
Registered: March 2005
Member
I read many places and "atler table table_name compute statistics" is NOT a proper way to update stat. should use DBMS_STATS.

In any case, my index still didn't get pick up, any thoughts?
Re: SQL tuning [message #511499 is a reply to message #511498] Mon, 13 June 2011 08:39 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/mv/msg/171967/511161/136107/#msg_511161
Re: SQL tuning [message #511522 is a reply to message #511499] Mon, 13 June 2011 13:31 Go to previous messageGo to next message
msol25
Messages: 396
Registered: June 2011
Senior Member
Hi Friend please follow given steps :

Step 1 : create 3 indexes one for id_even and one for job_status and one is composite
index for id_even,job_status.

Assuming you r using emp table:

like: create index idx_id on emp(id_even);

create bitmap index idx_stat on emp(job_status);

create index idx_id_stat on emp(id_even,job_status);


Step 2: Anayze for compute statistics

begin
dbms_stats.gather_table_stats(ownname=> USER, tabname=> emp,
estimate_percent=> 25, cascade=> TRUE);
end;

Analyze table emp compute statistics;

Step 3: check explain plan :

select * from table(dbms_xplan.display);

Step 4: You should get index range scan for idx_even and job_status column;

Re: SQL tuning [message #511556 is a reply to message #511146] Mon, 13 June 2011 21:46 Go to previous messageGo to next message
hannah00
Messages: 37
Registered: March 2005
Member
Thanks so much for your input......forgot to mention, unfortunately, this is standard edition so we can't use bitmap index.

Can I just create all three indexes?

Re: SQL tuning [message #511557 is a reply to message #511556] Mon, 13 June 2011 21:53 Go to previous messageGo to next message
msol25
Messages: 396
Registered: June 2011
Senior Member
Oracle version 9.2.1.0

Yes create all these 3 indexes.So optimizer is having choice to use correct index.

But we shd use bitmap index where cardinality is high.

Regards
Saurabh Mittal
Re: SQL tuning [message #511558 is a reply to message #511160] Mon, 13 June 2011 21:54 Go to previous messageGo to next message
hkchital
Messages: 128
Registered: September 2008
Location: Singapore
Senior Member
>there are around 150,000 rows on two values and less then 20,000 rows on one value of job_status out out 40 mil. There are only less than 100 rows on id_even

An Explain Plan will show how many rows the Optimizer *expects* to have to delete. Get an Explain Plan and compare that with your known counts -- particularly if you know that the id_even predicate would qualify only 100 rows for deletion.


Hemant K Chitale
Re: SQL tuning [message #511559 is a reply to message #511557] Mon, 13 June 2011 21:55 Go to previous messageGo to next message
msol25
Messages: 396
Registered: June 2011
Senior Member
If you can't create bitmap index then ceate like :

Oracle9.2.1.0> create index idx_stat on emp(job_status);

Regards
Saurabh Mittal
Re: SQL tuning [message #511564 is a reply to message #511558] Tue, 14 June 2011 00:15 Go to previous messageGo to next message
msol25
Messages: 396
Registered: June 2011
Senior Member
hi,

Please give this decision to Optimizer and you never bother about this, only you check index is working perfect or not.

If you want faster access than given decision by Otimizer,So then you hav to create partition table.


Regards
Saurabh Mittal

Re: SQL tuning [message #511621 is a reply to message #511564] Tue, 14 June 2011 05:22 Go to previous messageGo to next message
cookiemonster
Messages: 13926
Registered: September 2008
Location: Rainy Manchester
Senior Member
@msol25 - based on the available information there is no way you can be sure adding those indexes will solve anything, especially since the OP has already tried adding indexes.

@hannah00 - If you want help with this post the explain plan, it'll help us see what is happening.
Re: SQL tuning [message #511661 is a reply to message #511557] Tue, 14 June 2011 08:02 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
msol25 wrote on Tue, 14 June 2011 03:53

But we shd use bitmap index where cardinality is high.


I assume that was meant to be SHOULDN'T use a bitmap on high cardinality?
Re: SQL tuning [message #511670 is a reply to message #511661] Tue, 14 June 2011 09:07 Go to previous messageGo to next message
msol25
Messages: 396
Registered: June 2011
Senior Member
Hi Roachcoach,

We should have very few values for job status as per assumtion so i told regarding bitmap index creation.We should always give choice to optimizer,so we need to create composite index and column based index.

begin
 dbms_stats.gather_table_stats( 
      ownname          => 'schema_name',   --Give schema name
      tabname          => 'emp',
      estimate_percent => dbms_stats.auto_sample_size, 
      method_opt       => 'for all columns size skewonly', 
      cascade          => true,
      degree           => 7
     );
end;


Surely index will use in right way.

* < code tags > added by BlackSwan please do so yourself in the future

[Updated on: Tue, 14 June 2011 09:12] by Moderator

Report message to a moderator

Re: SQL tuning [message #511671 is a reply to message #511670] Tue, 14 June 2011 09:09 Go to previous messageGo to next message
msol25
Messages: 396
Registered: June 2011
Senior Member
Please suggest how to indent query before posting because i m giving indentation but query is not going to be indented buring posting.
Re: SQL tuning [message #511673 is a reply to message #511671] Tue, 14 June 2011 09:20 Go to previous messageGo to next message
cookiemonster
Messages: 13926
Registered: September 2008
Location: Rainy Manchester
Senior Member
Use [code] tags to maintain indentation.
Re: SQL tuning [message #511682 is a reply to message #511673] Tue, 14 June 2011 09:38 Go to previous messageGo to next message
msol25
Messages: 396
Registered: June 2011
Senior Member
Thanks Cookiemonster
Re: SQL tuning [message #511688 is a reply to message #511671] Tue, 14 June 2011 10:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68667
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
msol25 wrote on Tue, 14 June 2011 16:09
Please suggest how to indent query before posting because i m giving indentation but query is not going to be indented buring posting.


Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel

Re: SQL tuning [message #514172 is a reply to message #511688] Sat, 02 July 2011 01:25 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Forget bitmap indexes. Everybody thinks they know what they are for but in reality few do. You don't need them here. Your issue is likely one of the following (which others have already touched upon):

1) your stats have not been collected correctly
2) you are looking at way more rows that you think
3) your indexes do not align well to the needs of your query

Please post the following for us to review:

1) the exact command you use to collect statistics
2) table create statement
3) index create statements
4) query you want to go fast
5) select count(*) from <table>
6) select count(*) from (<your query goes here>)

Once we have this we can offer some relevant advice.

It seems these days that there is a group espousing the advice to create lots of single column indexes. This is usually stupid and this trend annoys me. I do not mean to suggest that msol25 is stupid, only that he is premature is suggesting these indexes before we have the kind of information I have asked for. Indeed, to his credit, msol25 has given you an example DBMS_STATS that you can use to collect statistics which should be helpful to you.

Kevin
Previous Topic: Performance Standard Edition without partitioning
Next Topic: How to measure the sql run time?
Goto Forum:
  


Current Time: Sat Jul 20 22:24:01 CDT 2024