Help - Search - Members - Calendar
Full Version: Big problem with index performance
Oracle DBA Forums > Oracle > Oracle Forum
Kriptas
Hello all,
this is first my topic in this forum.
I have huge problem regarding oracle indexing, so I need help of real oracle guru, becase I spend 2 weeks on that problem, read lots of articles, I show problem to our DBA, they asked other known DBA and no one can help with that problem.

In our database is few big (for our database) tables. Tables have 200 000 000 - 500 000 000 rows and all of them have the same problem.

in the example table LOG have 260 000 000 records. It is our clients activity log.
table LOG have columns ID (number)- unicue identity, CLIENT_ID (number), ACTIVITY_ID (varchar2(20)), ACTIVITY_DATE (date).

when client do somethins, system in LOG table puts record, where is that clien client_id, activity_id, and date and time of activity.

there is few main queries from that table - one selecting record by date, other selecting records by client_id.
selecting from LOG table by ACTIVITY_ID is fast enougth (it can be faster but it is normal speed)
Selecting from LOG table by client_id is very slow. client_id cardinality is 300 000, and every client_id in that table has about 1000 records.

client_id is foreign key, on that column is b-tree index.

selecting one client_id all records (about 1000 record) takes from 5-8 seconds.

I wrote simple query:

select client_id, activity_id, activity_date
from part_of_clients
inner join LOG on clnt_id=client_id

table part_of_clients has 1500 records.
quey run time - about 2-3 hours.

I pumped table to MS SQL 2005 server, and indexed column client id with non-clustered (b-tree) index
the same query time was 4 minutes.

Original table was on oracle 9.2 HP UX mashine, with 4xItanium procesors, RULE based optimisator. 8KB block size, 24gb ram.
I downloaded table to other mashine 10g r2 oracle, COST based oprimisator, 32k block size, 16gb ram, 8xItanium procesors. gathered statistics, created b-tree index on client_id gathered statistics on that index, gathered histograms on all colums. the result was the same.
CBO chooses full table (LOG) scan, and with full table scan and HASH join, query time was 10 minutes, bus the system was very loaded at that time. I added hint, and using index and nested loops query time was the same 3 hours.

HASH join is not posible on that table because our software selecting rows one by one client ID in a loop and situation is simmilar to nested loops. each loop takes 5 seconds.

I tried other query:


select client_id
from part_of_clients
inner join LOG on clnt_id=client_id

query time was 1 second. That query do not accesed table LOG, it taked cliend id from index.
Then I tried

select client_id, rowid
from part_of_clients
inner join LOG on clnt_id=client_id

query run time 1 second.
But when I tried acces all 1 500 000 by selected rowid's it tooked 2-3 hours again.

I created new table :
create table LOG1 as
select * from LOG
order by client_id

after that I run query

select client_id, activity_id, activity_date
from part_of_clients
inner join LOG1 on clnt_id=client_id

query run time was 25 seconds.

So, oracle ferformance is very slow accesing by rowid randomly distributed rows ?
We cant reorganise that table, because it used 24x7, and after that reorganising other queries (selecting by activity_date) will be slow.

I tried to generate the simmilar table with 2 loops
i:=0;
while i<1000
loop
x:=0;
while x<300000
loop
insert into table LOG2
select i, x, 'aaaa', sysdate from dual;
x:=x+1;
end loop;
i:=i+1;
end loop;

selecting of 1500 distinct client_id from LOG3 taked 5 minutes.
but in LOG3 client_id (x) is distributed not randomly.

I do not know how to boost performance of accesing records int table LOG, please help me.
cromagnon
I am guessing that to return the ~1000 rows (all for a client_id) it is doing roughly 1000 page reads, is that right? (ie, run with set autotrace on). If you run the query again, for the same client_id, is it substantially faster? (ie now those pages are in cache)?

If it is, then the problem is really one of how you want to layout the table on disk to minimize the i/o required to run the queries. The first step is to determine which queries have to run fastest. If for example all that is important is "all for a client_id" as opposed to "all on 7/4/2008" then it is easier to optimize.


Can you post some autotrace sessions?
Kriptas
CODE
| Id  | Operation                   | Name               | Rows  | Bytes | Cost | (CPU%)
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |   815 | 69275 |   610|   (1)|
|   1 |  TABLE ACCESS BY INDEX ROWID| LOG                |   815 | 69275 |   610|   (1)|
|   2 |   INDEX RANGE SCAN          | GT_IDX_1           |   973 |       |     6|   (0)|
----------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
702 consistent gets
562 physical reads
0 redo size
97596 bytes sent via SQL*Net to client
1206 bytes received via SQL*Net from client
77 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1130 rows processed

this is select for 1 client_id
The big problem is, that it is not possible to rebuild table in ordered way,
table is very big, it is used 24x7, and after rebuilding i need to rebuild it after ~1 month again and again ...
Kriptas
and if i run the query for 1 client_id second time it takes 0.01 seconds, because it in cache.

other thinfg is,
that FULL table scan (LOG) anh hach join takes only 10 minutes.

but is wrong to full scan every time 30gb table...

there is 10 big table, where you can feel that problem
ant lots of small ones... where you cant feel that problem, query on them takes let say 1 second.. but it can be only 0.1 s. the small tables is accesed more often and every access genertes extra (not needable) load to server.

Like I said, MS SQL server somehow deals with that data distribution, and runs query in normal speed (4 minutes only, and oracle 3 hours)
HAL9000
Kriptas,

"CBO chooses full table (LOG) scan, and with full table scan and HASH join, query time was 10 minutes, bus the system was very loaded at that time. I added hint, and using index and nested loops query time was the same 3 hours."

When joining two tables, there are really only two options, a "nested loop" join or a hash join.

Are the log inserts continuous? Could you drop the indexes (to make the inserts run faster), and then build the indexes only when needed for reports?

"with 4xItanium procesors"

So, you could use parallel query (degree 3) to speed up the full-scans for the hash join.

http://www.dba-oracle.com/concepts/parallel_query_option.htm

"I do not know how to boost performance of accesing records int table LOG"

The easiest solution is to cache the log table into the KEEP pool. Before, you did 562 disk reads for 1,130 rows. In cache, there will be no disk reads.

http://www.dba-oracle.com/oracle_tips_cache_small_fts.htm
Kriptas
I can't use hash join on that table, because where is lots of queries, that uses that table, some times in views.
Lots of stored procedures, and software which uses that table selects records by 1 client_id at the time and do calculations. so stored procedures and sotware forses nested loop by the quering..
USE_HASH to get one client_id from the LOG table is very expensive and will run lots slower then nested loops.

LOG table size is 35GB, so I cant put it to the KEEP buffer. there is about 10 tables, with the same problems, and those tables sizes is 10-50 GB.
HAL9000
"slower then nested loops."

If you have verified that a nested loop join is fastest and that you are using the best indexes, then that may be all that you can to.

For evaluating SQL performance, compare the rows returned with the number of gets. Below you do 702 gets for 1130 rows, not bad:

CODE
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
702 consistent gets
562 physical reads
0 redo size
97596 bytes sent via SQL*Net to client
1206 bytes received via SQL*Net from client
77 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1130 rows processed


"table part_of_clients has 1500 records. quey run time - about 2-3 hours. I pumped table to MS SQL 2005 server, and indexed column client id with non-clustered (b-tree) index the same query time was 4 minutes."

Can you elaborate on this? Was SQL Server far faster than Oracle?
Kriptas
yes, ms sql server 2005 was lot of faster than oracle on this query and with same data.. compare 4 minutes, and 3 hours..
I do not know why, it can be such big difference.
Ms sql server used nested loops to join the tables. and ms sql server was 2 times weaker, only 4xXeon and 4gb of ram.
I pumped data to ms sql server as is, do not performed any ordering or other tuning.
data distribution must be the same. but ms sql table size was abaut 20% larger, because I do not pick up right culumn types for number colums.

nested loops must be the fastest way, i accesing only 0.5% of all rows in LOG table...
but I think that in this case with nested loops I accesing avout 60% af all table LOG blocks.
cromagnon
I bet the reason why sql server shows faster was that when you loaded the data there you inadvertantly reclustered the data so that there is much less physical i/o required than in oracle. Or, your physical i/o subsystem on your oracle box is not as good as that on sqlserver. In the end, both databases cant outperform their underlying i/o system...

Your autotrace confirms my suspicion; the data is spread out all over. 3 suggestions:


1) If there are a known set of queries that dont require a lot of columns back (ie <4) create a covered index for the queries so the table will not be looked at at all. For example, if you want "select a,b where d=12" create an index (d,a,cool.gif. Make sure that the hit to your insert speed is okay.

2) Use compressed tablespaces. We get higher net tablescan throughput using compressed tablespaces, as there is generally plenty of underused CPU resources but i/o is often maxed out. If this is also true in your environment I bet this will help.

3) For some of our big tables that have multi-way queries against them we use paritioned IOT tables. In your example, partitioning by activity_date (*not* client_id) couple help a lot. The idea is that you will be better able to utilize parallel query. Just make sure there is a local index on each partition by client_id. Additionally, using IOT tables can keep data together (so your query would use say 10 reads instead of 500), but there is a hit to these.

hope it helps
HAL9000
"you inadvertantly reclustered the data "

Yes, that must be it.

With such a small SGA, disk reads are the major time factor.
Kriptas
I tried to use partitionin by activity_date, for each month i created partition. in one partition there was about 5 milions rows. created local bitmam, and b-tree (in different tests) indexes on client_id. the result was the same.. no performance boos at all, in some cases even slower, because oracle scanns in parallel al partiotion indexes and if data exist only in one partition it takes longer to get it comparing to global index.

regarding oracle IOT, it is not very usefull thing, because in IOT arrange table by PK, and some adidional columns. If I create IOT by log_id and client_id, the client_id will be spread out.

I tried to create compressed table, table size was 20% less, but no performance boost, only about 5-10%, but it is still too slow.

in MS SQL server, i put row by row like it was in oracle. so data spread is the same like in oracle.
and I exported data to txt files from ms sql and importet data from tha txt file to my home PC.
the result the same on my home PC.
I tried to create clustered index on client_id in MS SQL server (it is like IOT, but you do not need PK in that index, only client_id) query run time was 14s.

and another question
why generated table was faster
generated with loops:
i:=0;
while i<1000
loop
x:=0;
while x<300000
loop
insert into table LOG2
select i, x, 'aaaa','BBBBBbb', sysdate from dual;
x:=x+1;
end loop;
i:=i+1;
end loop;

row lenght was 21B, my PC block size is 8K,
so every the same X vill be in the different block. the same X inserted after every 3000 000 rows.. so it can't be in the same block.
cromagnon
Regarding IOT yes, you do end up compromising the "right" logical key which is something that bothers me, but oh well. a logical PK of {log_id,client_id} is the same as one of {client_id,log_id}, so switch the IOT order and your locality will go up.

It sounds to me like your i/o system is hosed up. Can we try another test? Run the i/o calibration as described here:
http://download.oracle.com/docs/cd/B28359_...74/iodesign.htm
Kriptas
sorry, but I cant run calibration or server, our senior dba on vocation, and junior dba do not want perform calibration ;/
any other ideas?
or it is really that oracle working with indexes slower in some cases then ms sql server.. i can't believe...
HAL9000
"i can't believe..."

Me neither. . . .

Oracle has over 400 initialization parms and other settings, and it likely that the instance has not been properly optimized.

Oracle is light years ahead of SQL Server.
Kriptas
I realy want to see oracle working... because at the moment, at work there are 4 oracle servers, and 1 ms sql server 2005. ms sql server machine is 2-3 times weaker then oracle servers... but ms sql server do all tasks 2-20 times faster. ms sql oprimiser schooses better plans without any hints, any statistics histograms gathering (it do that by him self), ms sql server works unsupported, dba do nothing with him.. installed and forgot for evar...but it works...and what nicest, that is local temporary tables and smart parallel processing ;/

if someone can look at the data of the table which has slow index, i can share it.. data size ~25GB..;(
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.