Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

performance Tuning 10g reference poster
 
Oracle training in Linux 

commands
 
Oracle training 

Excel
 
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,100
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
helpme42
post May 12 2010, 02:24 PM
Post #3


Advanced Member
***

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



QUOTE (burleson @ May 12 2010, 02:30 PM) *
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


I understand now.... i think... one last thing bugging me though (and iv read the posts to try to understand this)

The statement : select fname,sname,personid from tblperson where sex = 'f' brings back 350 out of 500 rows

I was under the impression, from everything iv read, that if this were the case a full table scan is faster... (wait for it)

using : create index i_p_s on tblperson(fname, sname, personid, sex); .... this index is used however....

is this simply because the index matches the query used, so like steveC said why go and look for the data when its all ready in the index, so oracle uses INDEX FAST FULL SCAN....

thats all i want to know... because those links state that in an unorganised table (which it is) considering the size of the table, a full table scan is still faster than using the index

CODE
create index i_p_s on tblperson(fname, sname, personid, sex);

exec dbms_stats.gather_table_stats(ownname=>'bsms',tabname=>'TBLPERSON', cascade=>true);

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


323 rows selected.


Execution Plan
----------------------------------------------------------                      
                                                                                
--------------------------------------------------------------                  
| Id  | Operation            | Name  | Rows  | Bytes | Cost  |                  
--------------------------------------------------------------                  
|   0 | SELECT STATEMENT     |       |   323 |  6783 |     3 |                  
|   1 |  INDEX FAST FULL SCAN| I_P_S |   323 |  6783 |     3 |                  
--------------------------------------------------------------                  
                                                                                
Note                                                                            
-----                                                                          
   - 'PLAN_TABLE' is old version                                                


Statistics
----------------------------------------------------------                      
          1  recursive calls                                                    
          0  db block gets                                                      
         28  consistent gets                                                    
          0  physical reads                                                    
          0  redo size                                                          
       8299  bytes sent via SQL*Net to client                                  
        647  bytes received via SQL*Net from client                            
         23  SQL*Net roundtrips to/from client                                  
          0  sorts (memory)                                                    
          0  sorts (disk)                                                      
        323  rows processed                                                    

timing for: timer13
Elapsed: 00:00:00.00


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



Wont ask another question like this I promise... I just dont like taking things for granted until i understand them correctly
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
- - 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: 24th April 2014 - 02:52 AM