Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
> help with indexing
helpme42
post May 10 2010, 01:49 PM
Post #1


Advanced Member
***

Group: Members
Posts: 31
Joined: 10-May 10
From: UK
Member No.: 43,076



Hi I am new to the whole area of performance and indexing.

im using the following statement:

select * from tblavailability where personid = 248;

This returns 208 rows out of 10,000 in the table

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

I want to attempt to improve the performance of this query, so I have added an index on the 'personid' column

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

The results with no index are as follows (test ran 3 times):

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
82 consistent gets
0 physical reads
0 redo size
8390 bytes sent via SQL*Net to client
559 bytes received via SQL*Net from client
15 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
208 rows processed

timing for: timer18
Elapsed: 00:00:00.01

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
82 consistent gets
0 physical reads
0 redo size
8390 bytes sent via SQL*Net to client
559 bytes received via SQL*Net from client
15 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
208 rows processed

timing for: timer18
Elapsed: 00:00:00.01

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
82 consistent gets
0 physical reads
0 redo size
8390 bytes sent via SQL*Net to client
559 bytes received via SQL*Net from client
15 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
208 rows processed

timing for: timer18
Elapsed: 00:00:00.01

=========================================================================
The results from the same query with the index added are as follows (ran 3 times)

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
33 consistent gets
0 physical reads
0 redo size
12595 bytes sent via SQL*Net to client
559 bytes received via SQL*Net from client
15 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
208 rows processed

timing for: timer18
Elapsed: 00:00:00.01

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
33 consistent gets
0 physical reads
0 redo size
12595 bytes sent via SQL*Net to client
559 bytes received via SQL*Net from client
15 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
208 rows processed

timing for: timer18
Elapsed: 00:00:00.01

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
33 consistent gets
0 physical reads
0 redo size
12595 bytes sent via SQL*Net to client
559 bytes received via SQL*Net from client
15 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
208 rows processed

timing for: timer18
Elapsed: 00:00:00.01

==========================================================================
Im struggeling to see how I can tell if the index has helped improve performance of that query?... as the time is the same!

The only other way I figured I could see a difference would be running the following 2 statements prior to each test..

ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH SHARED_POOL;

this clearly shows less physical reads on the queries that use the index, but the timings seem quite random for queries with and without the index.

also I have been told not to do this (im unsure why)

====================================================================
Can anyone help me, ... for the results i posted (caches not cleared).. how can i tell of a performance increase when using the index?

Any help would be appriciated
Go to the top of the page
 
+Quote Post
 
Start new topic
Replies
burleson
post May 12 2010, 01:29 PM
Post #2


Advanced Member
***

Group: Members
Posts: 11,725
Joined: 26-January 04
Member No.: 13



Hi,

>> I just wanted to know anyones reasoning why the index: create index i_p_s on tblperson(fname,sname,personid,sex) is used on a 500 row table instead of FTS, where the index create index i_p_s on tblperson(sname,personid,sex) is not used...

Before you second-guess the optimizer, have you analyzed all of the tables and indexes with dbms_stats?

http://www.dba-oracle.com/art_builder_dbms_stats.htm

Have you run dbms_stats.gather_system_stats?

http://www.dba-oracle.com/t_dbms_stats_gat...ystem_stats.htm

*************************************************************************
>> For the statement: select fname,sname,personid from tblperson where sex = 'f'

Assuming that you are not European (they have nine genders), this would retrieve half the table rows:

http://www.dba-oracle.com/t_computer_datab...ender_codes.htm

******************************************************
>> where the index create index i_p_s on tblperson(sname,personid,sex) is not used

Because the leading columns IS NOT in the WHERE clause!

This is called an "index skip scan":

http://dba-oracle.com/t_oracle_index_skip_scan_tips.htm





--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post

Posts in this topic
- helpme42   help with indexing   May 10 2010, 01:49 PM
- - burleson   Hi, and welcome to the forum! >> I want...   May 10 2010, 04:12 PM
- - helpme42   Wow, Thee Donald K Burleson .. Im reading your boo...   May 10 2010, 04:42 PM
- - SteveC   The problem is that since you have run the same qu...   May 10 2010, 04:44 PM
- - helpme42   The second test with the index on shows more consi...   May 10 2010, 04:54 PM
- - SteveC   Doing it in production is kind of a no-no, but sin...   May 10 2010, 05:04 PM
- - helpme42   I have ran the same test by flushing the buffer ca...   May 10 2010, 05:12 PM
- - helpme42   is it anything to do with the analyzer im using: ...   May 10 2010, 05:33 PM
- - burleson   Hi, >> I have ran the same test by flushing...   May 10 2010, 05:43 PM
- - helpme42   O.K ... Aside from the timings , if the column in...   May 10 2010, 05:47 PM
- - helpme42   A query with less consistent gets is more efficien...   May 10 2010, 05:55 PM
- - helpme42   After adding half a million record to the table I ...   May 10 2010, 07:03 PM
- - helpme42   Sorry iv figured that one out, the extra consisten...   May 11 2010, 02:08 PM
- - SteveC   It is not a huge table, and you are selecting a da...   May 11 2010, 02:33 PM
|- - helpme42   QUOTE (SteveC @ May 11 2010, 03:34 PM) It...   May 11 2010, 02:45 PM
- - SteveC   Make one record F and the rest M. Then do a select...   May 11 2010, 03:35 PM
|- - helpme42   QUOTE (SteveC @ May 11 2010, 04:36 PM) Ma...   May 11 2010, 03:45 PM
- - SteveC   If Oracle has the data is in the index, then why g...   May 11 2010, 03:58 PM
|- - helpme42   QUOTE (SteveC @ May 11 2010, 04:59 PM) If...   May 11 2010, 04:07 PM
|- - helpme42   sorry what i ment is , lets say the table had 500 ...   May 12 2010, 07:35 AM
- - burleson   Hi, >> I will leave you alone after this I...   May 12 2010, 08:11 AM
|- - helpme42   QUOTE (burleson @ May 12 2010, 09:12 AM) ...   May 12 2010, 08:25 AM
|- - helpme42   QUOTE (burleson @ May 12 2010, 02:30 PM) ...   May 12 2010, 02:24 PM
- - burleson   Hi, >> those links state that in an unorgan...   May 12 2010, 02:38 PM
|- - helpme42   Yes im currently studying for my masters (computer...   May 12 2010, 02:50 PM
- - burleson   Hi, >> im currently studying for my masters...   May 12 2010, 03:16 PM


Closed TopicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 27th November 2014 - 09:09 AM