help with indexing Options

[helpme42]

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

[burleson]

Hi, and welcome to the forum!

>> I want to attempt to improve the performance of this query

OK what is your performance goal?

Do you want to optimize for high throughput or fast response time?

http://www.remote-dba.net/oracle_10g_tunin...uning_goals.htm

**********************************************************
>> Elapsed: 00:00:00.01

Your timings are all super-fast!

**********************************************************
>> select * from tblavailability where personid = 248;

In this simple query, there are only two possible plans:

1 - index access via personid
2 - full scan using parallel query (if you have an SMP server with many CPU's)

If you are using the index, the only way to go even faster is to reorg the table to put all related personid's on adjacent data blocks.

You can use this, and probably get the rows with only a few gets:

CODE

create table t1 as select * from tblavailability order by personid:
rename tblavailability to oldtab;
rename t1 to tblavailability;
create index . . .
alter table add primary key constraiont . . .

http://www.remote-dba.net/t_oracle_net_reorganize_tables.htm

**********************************************************

You got 208 rows in 33 gets, that not too bad!

The only way to possible do even better is to examine your WHERE clause and see if you might have a more restrictive consition that you can index on.

Remember, in Oracle, you can index on functions too:

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

[helpme42]

Wow, Thee Donald K Burleson .. Im reading your book currently 'Oracle Solid State Disk Tuning' - Im not using SSD for this by the way (thats not the reason for fast speeds )

Im new to the whole area of oracle performance tuning, I specialise in .Net stuff, so your help is much appriciated.

When you say, ami optimising for performance time or high throughput.. origionally i have been attempting performance time, I am unaware how to look at the stats to determine throughput?

Thanks for the advise.. Maybe you could help me with this one...

================================================================================
===========================
A table called 'tblrequirements' contains 4000 rows, there is a column called 'wardshiftID'

im using the following SQL statement

"select * from tblrequirements where wardshiftid = 110;"

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

I have ran many tests on the table with no indexes, and then many tests on the table with an index on the column used in the where clause 'wardshiftid' , However adding the index seems to make the query perform worse... im baffled... could you have a look at the results and see what you think:

I have posted 2 general results which are representative of all the tests , the first is using no indes, the second is using the index
=========================================================================

239 rows selected.


Execution Plan
----------------------------------------------------------

---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 231 | 11781 | 9 |
| 1 | TABLE ACCESS FULL| TBLREQUIREMENTS | 231 | 11781 | 9 |
---------------------------------------------------------------------

Note
-----
- 'PLAN_TABLE' is old version


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
47 consistent gets
0 physical reads
0 redo size
10519 bytes sent via SQL*Net to client
581 bytes received via SQL*Net from client
17 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
239 rows processed

timing for: timer21
Elapsed: 00:00:00.01


#########################################################create index i_req_ws on tblrequirements(wardshiftid);###########################

begin
dbms_stats.gather_schema_stats(
ownname => 'bsms'
,estimate_percent => null
,cascade => true
,method_opt => 'FOR ALL COLUMNS SIZE 5'
);
end;
/

################################################################################
#########################################################

239 rows selected.


Execution Plan
----------------------------------------------------------

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 22 | 1122 | 7 |
| 1 | TABLE ACCESS BY INDEX ROWID| TBLREQUIREMENTS | 22 | 1122 | 7 |
| 2 | INDEX RANGE SCAN | I_REQ_WS | 22 | | 1 |
-------------------------------------------------------------------------------

Note
-----
- 'PLAN_TABLE' is old version


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
57 consistent gets
0 physical reads
0 redo size
15225 bytes sent via SQL*Net to client
581 bytes received via SQL*Net from client
17 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
239 rows processed

timing for: timer21
Elapsed: 00:00:00.01

[SteveC]

The problem is that since you have run the same query more than once, the blocks are already in memory. You would need to flush the buffer cache - each run - to get a better comparison. You can see that the consistent gets (blocks being read that are in memory) are lower because not all blocks have to be read now.

[helpme42]

The second test with the index on shows more consistent gets though...if what your saying is correct shouldn't this show less consistent gets?

In a production environment, if there were an index on the table, the second time the query is ran it would perform worse... with an index then without an index?

The reason im not flushing the buffer cache, I wouldnt have thought made a difference in terms of optimisation, as im considering the performance of the query beyond the first time its ran,

After the first time its ran, (regardless of clearing the cache or not) the query with an index on it, shows more consistant gets than a query without an index on it... do you see what I mean?

Also, I have been told never to flush the buffer cache, (I figured that it would be the best move also, but i got told off for doing it on a different forum)

[SteveC]

Doing it in production is kind of a no-no, but since you are running tests, one would assume you have a test/dev/QA system to be doing this on.

[helpme42]

I have ran the same test by flushing the buffer cache before each one.

Theres the same problem....

-More consistent gets on the indexed table
-More Physical Reads on the indexed table
- Query takes longer to run

Why is this?

Sample of results are as follows:

================================================================================
======================
No index on where clause
================================================================================
======================

239 rows selected.


Execution Plan
----------------------------------------------------------

---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 22 | 1122 | 9 |
| 1 | TABLE ACCESS FULL| TBLREQUIREMENTS | 22 | 1122 | 9 |
---------------------------------------------------------------------

Note
-----
- 'PLAN_TABLE' is old version


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
47 consistent gets
29 physical reads
0 redo size
10519 bytes sent via SQL*Net to client
581 bytes received via SQL*Net from client
17 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
239 rows processed

timing for: timer21
Elapsed: 00:00:00.04

================================================================================
======================
index ON where clause
================================================================================
======================
239 rows selected.


Execution Plan
----------------------------------------------------------

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 22 | 1122 | 7 |
| 1 | TABLE ACCESS BY INDEX ROWID| TBLREQUIREMENTS | 22 | 1122 | 7 |
| 2 | INDEX RANGE SCAN | I_REQ_WS | 22 | | 1 |
-------------------------------------------------------------------------------

Note
-----
- 'PLAN_TABLE' is old version


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
57 consistent gets
48 physical reads
0 redo size
15225 bytes sent via SQL*Net to client
581 bytes received via SQL*Net from client
17 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
239 rows processed

timing for: timer21
Elapsed: 00:00:00.09

[helpme42]

is it anything to do with the analyzer im using:

begin
dbms_stats.gather_schema_stats(
ownname => 'bsms'
,estimate_percent => null
,cascade => true
,method_opt => 'FOR ALL COLUMNS SIZE 5'
);
end;
/

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

I have tried using AUTO on column size ( and i get the same results for both indexed and non-indexed) :

begin
dbms_stats.gather_schema_stats(
ownname => 'bsms'
,estimate_percent => null
,cascade => true
,method_opt => 'FOR ALL COLUMNS SIZE AUTO'
);
end;
/

[burleson]

Hi,

>> I have ran the same test by flushing the buffer cache before each one.

After you flush, ALL the block reads require a phycical disk read!

Also, YOUR TIMINGS are TOO close to be statisticaly meaningful!

If you want to get some real-world tuimings, make a million row table . . .

***********************************************
>> analyze

No, re-running dbms_stats make no difference since there are only 2 choices, use an index or do a full-scan . .

>> I am unaware how to look at the stats to determine throughput?

It's too much for a forum, but I explain it in great detail in my tuning book:

http://www.rampant-books.com/book_0501_awr...tive_tuning.htm

[helpme42]

O.K ... Aside from the timings , if the column in the where clause is indexed then how:

When flushing the cache: why do i get more physical reads and more consistent reads on the indexed table...?

When not flushing the cache: why do i get more consistent reads on the indexed table..?

I thaught that indexing the where clause in that statement would make it more efficient, why does it appear to make it less efficient?

This doesnt make any sense, in a table with 4000 rows, and a query that returns 239 of them, which is simply selecting * from table where col = xx , then why would indexing that column cause more physical reads (possibly bacause it has to read the index?) but more importantly why more consistant gets?

Sorry if this is a daft question, but I cant get my head around it

[helpme42]

A query with less consistent gets is more efficient is it not? ... so the index is making the query less efficient (which it should do the opposite in this situation?)

If i were to summarise this, then I would have to write in a report that placing an index on the wardshiftid column makes the query less efficient would i not? but why does it make it less efficient?

[helpme42]

After adding half a million record to the table I am now getting the following results: (first with no index, second with index)

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

Execution Plan
----------------------------------------------------------

---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 22 | 1122 | 1947 |
| 1 | TABLE ACCESS FULL| TBLREQUIREMENTS | 22 | 1122 | 1947 |
---------------------------------------------------------------------

Note
-----
- 'PLAN_TABLE' is old version


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7142 consistent gets
0 physical reads
0 redo size
10517 bytes sent via SQL*Net to client
581 bytes received via SQL*Net from client
17 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
239 rows processed

timing for: timer21
Elapsed: 00:00:00.04

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

239 rows selected.


Execution Plan
----------------------------------------------------------

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 704 | 34496 | 11 |
| 1 | TABLE ACCESS BY INDEX ROWID| TBLREQUIREMENTS | 704 | 34496 | 11 |
| 2 | INDEX RANGE SCAN | I_REQ_WS | 704 | | 4 |
-------------------------------------------------------------------------------

Note
-----
- 'PLAN_TABLE' is old version


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
58 consistent gets
0 physical reads
0 redo size
15225 bytes sent via SQL*Net to client
581 bytes received via SQL*Net from client
17 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
239 rows processed

timing for: timer21
Elapsed: 00:00:00.00

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

What i do not understand however, and please please could someone help me...

Now that there are over half a million rows, im getting lower consistant reads in the indexed table ( 52 in the indexed and 7000+ in none indexed)

but....

before I added the extra rows, I was getting higher consistent reads in the indexed table than the none indexed ( 57 indexed / 47 non-indexed)

Why is this .... with lower number of rows why does the indexed table use more consistent gets? ... surely no matter howmany rows are in the table the indexed table should have the same or lower number of consistent gets as the non-indexed???

[helpme42]

Sorry iv figured that one out, the extra consistent gets... im using 2 objects the index and the table, so its going to be more? right?

I have a bigger problem now, and iv put this on a couple of forums but people are stumped.

I have tried running a test on a table which contains a 'sex' field... (table around 500 rows (300ish = F, 150ish = M, 50ish = null ---in the sex column)

When i place a bitmap index on the sex column, it is never used, no idea why?

I have read a blog post (link below and my results) and tried to incorporate this, it still will not use the index, I have also tried to force it using hints, still no luck


Any ideas?

Name Null Type
------------------------------ -------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PERSONID NOT NULL NUMBER(9)
INACTIVEREASONID NUMBER(9)
WORKCATID NUMBER(9)
ORIGINID NUMBER(9)
AGENCYID NUMBER(9)
PAGERNO VARCHAR2(20)
FNAME VARCHAR2(20)
SNAME VARCHAR2(20)
SEX VARCHAR2(1)

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

The data in the sex field is split m/f/null

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

im using the following query:

select fname, sname, personid from tblperson where sex = 'F';

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

adding the following index and running the SQL i get these results:

create bitmap index i_person_sex on tblperson(sex);

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

Execution Plan
----------------------------------------------------------

---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 323 | 6783 | 7 |
| 1 | TABLE ACCESS FULL| TBLPERSON | 323 | 6783 | 7 |
---------------------------------------------------------------

Note
-----
- 'PLAN_TABLE' is old version


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
45 consistent gets
0 physical reads
0 redo size
9458 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.01

================================================== =====
Then changing the index to compensate for null's : create bitmap index i_person_sex on tblperson(sex,0);

this info taken from here:

http://oratips-ddf.blogspot.com/2008...o-indexes.html

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


Execution Plan
----------------------------------------------------------

---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 323 | 6783 | 7 |
| 1 | TABLE ACCESS FULL| TBLPERSON | 323 | 6783 | 7 |
---------------------------------------------------------------

Note
-----
- 'PLAN_TABLE' is old version


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
45 consistent gets
0 physical reads
0 redo size
9458 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

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

after further testing i have noticed....

The two following queries do use the index:

==========================================
select sex from tblperson;
select sex from tblperson where sex = 'F'
==========================================

However these two dont use the index...

==========================================
select fname from tblperson where sex = 'F';
select fname, sex from tblperson where sex = 'F';
==========================================

Crazy?!?!

[SteveC]

It is not a huge table, and you are selecting a data item that is not indexed (fname). When you did the select sex statement, that information can be read directly from the index. When you select the fname, Oracle has to figure what is easier to do: a FTS or use the index to get the table rowid.

[helpme42]

It is not a huge table, and you are selecting a data item that is not indexed (fname). When you did the select sex statement, that information can be read directly from the index. When you select the fname, Oracle has to figure what is easier to do: a FTS or use the index to get the table rowid.

ok, so this might be where my understanding is incorrect..

if i use the statement : slelect fname, sex from tblperson where sex = 'f' .... because i have indexed sex and not fname, then oracle presumes FTS is better way? (due to the table being small)

I thaught that because i had indexed the predicate in the where clause then, it doesnt matter what im selecting (in the same table), as it would just look at the rows that sex = 'F' (via the index) and then pull back whatever columns i have specified in that table that match the where criteria?

ie. 350 columns have sex = 'f' .. so just throw back whatever i have selected where the index has found sex = 'f'

Is this not how it works?

Sorry if this is a stupid question, im really new to this