RE: Index performance
What is the selectivity of the columns emp_st, emp_status and match?
If there are just 3-4 distinct values in the column, do not use b-tree index
on them. Just drop the Indexes on those columns and try again. If still slow
then try bitmap indexes on those low selectivity columns.
Sanjay
-Original Message-
Sent: 23 July, 2002 11:24 PM
To: Multiple recipients of list ORACLE-L
Hi
I am executing following query adn this query hits a number of indices on
this table.let me know what is wrong please.all in where clause are having
indexes.
select name,last_access, reg_date from empmaster where emp_id<100
and reg_date>to_date('2001-01-01','-MM-DD') and
emp_st='valid' and last_access>to_date ('2001-01-01','-MM-DD')
and emp_status='S' and match='FIRST'
Here all conditions in where clause are having indexes.
How to rewrite this query.
The primary key is emp_id.
Thanks
-Seema
_
Chat with friends online, try MSN Messenger: http://messenger.msn.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Seema Singh
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: G Sanjay
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: Index performance
The SQL appears to be fine. If it uses "too many indexes" then perhaps all the indexes are on a single column. Perhaps you need to create a concatenated index. At any rate, if it runs too slowly, post the SQL again with an explain plan. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Index performance
Title: RE: Index performance
Seema,
It would really help to see your index scripts also. If you have an index that includes emp_id, reg_date, emp_st, last_access, emp_status, and match, that index should work best. Use a hint on that index.
You say that this query hits a number of indices on this table. Do you have the explain plan that shows which and how the indexes are hit? You can't assume that just because you have an index that it is being used. If you have a bunch of indexes each on a different column or two, the indexes are probably not doing this SQL any good and surely making inserts and updates take a lot longer.
Also how big is the table? How big is it expected to become? When's the last time it was analyzed?
Jerry Whittle
ACIFICS DBA
NCI Information Systems Inc.
[EMAIL PROTECTED]
618-622-4145
-Original Message-
From: Seema Singh [SMTP:[EMAIL PROTECTED]]
Hi
I am executing following query adn this query hits a number of indices on
this table.let me know what is wrong please.all in where clause are having
indexes.
select name,last_access, reg_date from empmaster where emp_id<100
and reg_date>to_date('2001-01-01','-MM-DD') and
emp_st='valid' and last_access>to_date ('2001-01-01','-MM-DD')
and emp_status='S' and match='FIRST'
Here all conditions in where clause are having indexes.
How to rewrite this query.
The primary key is emp_id.
Thanks
-Seema
RE: Index performance
Also do you know what is the most selective part of the query. If you know
that one index will bring back the fewest rows then try hinting to use it.
-Original Message-
Sent: Tuesday, July 23, 2002 5:59 PM
To: Multiple recipients of list ORACLE-L
why do you think hitting the indexes is a bad thing? what is the
performance of this query? What's the explain plan?
--- Seema Singh <[EMAIL PROTECTED]> wrote:
> Hi
> I am executing following query adn this query hits a number of
> indices on
> this table.let me know what is wrong please.all in where clause are
> having
> indexes.
> select name,last_access, reg_date from empmaster where emp_id<100
> and reg_date>to_date('2001-01-01','-MM-DD') and
> emp_st='valid' and last_access>to_date ('2001-01-01','-MM-DD')
> and emp_status='S' and match='FIRST'
> Here all conditions in where clause are having indexes.
> How to rewrite this query.
> The primary key is emp_id.
> Thanks
> -Seema
>
>
>
>
>
> _
> Chat with friends online, try MSN Messenger: http://messenger.msn.com
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Seema Singh
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing
> Lists
>
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
__
Do You Yahoo!?
Yahoo! Health - Feel better, live better
http://health.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Rachel Carmichael
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Nicoll, Iain (Calanais)
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: Index performance
why do you think hitting the indexes is a bad thing? what is the
performance of this query? What's the explain plan?
--- Seema Singh <[EMAIL PROTECTED]> wrote:
> Hi
> I am executing following query adn this query hits a number of
> indices on
> this table.let me know what is wrong please.all in where clause are
> having
> indexes.
> select name,last_access, reg_date from empmaster where emp_id<100
> and reg_date>to_date('2001-01-01','-MM-DD') and
> emp_st='valid' and last_access>to_date ('2001-01-01','-MM-DD')
> and emp_status='S' and match='FIRST'
> Here all conditions in where clause are having indexes.
> How to rewrite this query.
> The primary key is emp_id.
> Thanks
> -Seema
>
>
>
>
>
> _
> Chat with friends online, try MSN Messenger: http://messenger.msn.com
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Seema Singh
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing
> Lists
>
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
__
Do You Yahoo!?
Yahoo! Health - Feel better, live better
http://health.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Rachel Carmichael
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Index performance
Hi
I am executing following query adn this query hits a number of indices on
this table.let me know what is wrong please.all in where clause are having
indexes.
select name,last_access, reg_date from empmaster where emp_id<100
and reg_date>to_date('2001-01-01','-MM-DD') and
emp_st='valid' and last_access>to_date ('2001-01-01','-MM-DD')
and emp_status='S' and match='FIRST'
Here all conditions in where clause are having indexes.
How to rewrite this query.
The primary key is emp_id.
Thanks
-Seema
_
Chat with friends online, try MSN Messenger: http://messenger.msn.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Seema Singh
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Reverse Key Index Performance
I have never used RKI's nor have I read up on them before this posting; this is pure conjecture. As I recall this was happening in a correlated subquery. Is it possible that using a normal forward-key index the indexed could be stepped through sequentially , whereas using the reverse key meant that each index lookup required a different block to be read. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Monday, May 06, 2002 5:08 PM To: Multiple recipients of list ORACLE-L Waleed, Thanks for doing a test. There could be cases where we don't benefit from buffering, and, where we will have to visit a lot more blocks, but surely not enough to account for the difference I was seeing. As far as the plans, the raw 10046 traces show them as being the same, and, there are no parallel query operations. You might have picked this up from one of the other emails on this topic, and particularly where I noted the severe waits on db file sequential reads when using the reverse key. Would tend to indicate I/O issues (or why am I requiring so many I/O's). Anyway, switching back and forth between the two in the same tablespace and seeing the difference could simply have been pure luck in the way things got laid down physically each time. And that's the big question since with the striping that is used, and currently nothing mapping things out back to or looking inside the EMC's, things could have been very different without my knowing. Thanks for taking the time to run a test. FWIW, the original intent for RKI's from what I have been told is that at one time their intention was to have 8 simultaneous processes operating on different rows from the staging table using the MOD function and different divisors. That was scrapped some time ago and only a single process is used. So what they were trying to avoid with the RKI's is no longer an issue (and there are other approaches to RKI's to avoid the problem that RKI's are intended to cure). So, the RKI's on the two tables have been permanently converted to b-tree. Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Khedr, > Waleed > Sent: Monday, May 06, 2002 8:13 AM > To: Multiple recipients of list ORACLE-L > Subject: RE: Reverse Key Index Performance > > > Hi Larry, > > I did some testing on RKI after seeing your post. It's not any different > that normal indexes for unique lookups. > > I'm sure you have some other issue like change in execution plan or even a > small difference like using/not using Oracle PQO. > > Regards, > > Waleed -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Larry Elkins INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: MacGregor, Ian A. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Reverse Key Index Performance
Waleed, Thanks for doing a test. There could be cases where we don't benefit from buffering, and, where we will have to visit a lot more blocks, but surely not enough to account for the difference I was seeing. As far as the plans, the raw 10046 traces show them as being the same, and, there are no parallel query operations. You might have picked this up from one of the other emails on this topic, and particularly where I noted the severe waits on db file sequential reads when using the reverse key. Would tend to indicate I/O issues (or why am I requiring so many I/O's). Anyway, switching back and forth between the two in the same tablespace and seeing the difference could simply have been pure luck in the way things got laid down physically each time. And that's the big question since with the striping that is used, and currently nothing mapping things out back to or looking inside the EMC's, things could have been very different without my knowing. Thanks for taking the time to run a test. FWIW, the original intent for RKI's from what I have been told is that at one time their intention was to have 8 simultaneous processes operating on different rows from the staging table using the MOD function and different divisors. That was scrapped some time ago and only a single process is used. So what they were trying to avoid with the RKI's is no longer an issue (and there are other approaches to RKI's to avoid the problem that RKI's are intended to cure). So, the RKI's on the two tables have been permanently converted to b-tree. Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Khedr, > Waleed > Sent: Monday, May 06, 2002 8:13 AM > To: Multiple recipients of list ORACLE-L > Subject: RE: Reverse Key Index Performance > > > Hi Larry, > > I did some testing on RKI after seeing your post. It's not any different > that normal indexes for unique lookups. > > I'm sure you have some other issue like change in execution plan or even a > small difference like using/not using Oracle PQO. > > Regards, > > Waleed -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Larry Elkins INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Reverse Key Index Performance
Hi Larry, I did some testing on RKI after seeing your post. It's not any different that normal indexes for unique lookups. I'm sure you have some other issue like change in execution plan or even a small difference like using/not using Oracle PQO. Regards, Waleed -Original Message- Sent: Saturday, May 04, 2002 10:23 AM To: Multiple recipients of list ORACLE-L Listers, Has anyone done extensive benchmarking of unique key index lookups comparing reverse key and b-tree? For the sake of brevity, I am leaving out a lot of details at this point. Just simply had a case where doing a million unique key lookups using a reverse key index would run for hour(s). Change to b-tree, 6 minutes. Build again as reverse key, same TS, "fresh" index, runs for hours again. I've got lots of ideas but I want to keep this short. Query was correlated sub-query (replication query for the <> "I"). Sure, there are issues with RKI's not packing as much in and being bigger, you lose any benefit of optimal clustering, the range scan issue, etc. And for rows physically located together, I know I will have to access more index blocks to get those rows versus a b-tree with good clustering of data. And how much overhead is needed for reversing the value used to do the lookup? And my test case was hardly a controlled environment where I could rule out or control all other factors. And I know of some things that could very well have skewed the testing. Anyway, I'm curious if anyone else has done some benchmarking on this. I would be curious about the results and comparing notes. Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Larry Elkins INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Reverse Key Index Performance
> > Sometimes it's a pity that a problem can be resolved > without being understood, but that's the real world. No kidding -- if some things "appear" to work, it would help to understand the details to make sure a valid conclusion is being drawn. The test of reverse vs. b-tree was simply performed because of the situation described earlier where the only two issues where on those with RKI's. I had the test going on in the background while focusing on more critical work that needed to be done. I didn't expect the big difference or else I would have tried to be a little more controlled to really pinpoint all possible factors. And so now I will still be wondering what other factors were involved. And it's hard to duplicate on my Win2k box, and not sure that you could draw a correlation anyway due to such massive differences in size, HW, and OS's. > > A couple of thoughts (for next time). > > It would be useful to see the execution plans (particularly > to see the plan dumped in the trace files just in case > the theoretical plan was not the same as the > actual plan). The plan in the raw trace file was the same as what explain plan was giving. > Also the full EXPLAIN PLAN output to see > if the estimated index access costs on the subquery varied. No can do -- was lucky to be able to temporarily "borrow" the space. I hardly ever pay attention to the calculated cost when dealing with problem queries, but, it would have been a good idea here to note the differences in the calculated costs when using the reverse key vs. b-tree. Had this been my primary focus, I would have taken the time to do a 10053 trace as well just to see inside the CBO's head and how it might calculate things differently between the RKI and the b-tree. Maybe there would have been differences, but the bottom line is the same access path was used either way, so I don't know how much we would gain from seeing the numbers. > > One thought that could explain the discrepancy, which > would be controlled by the type of query and the size of > the table. > > If Oracle optiimises the query by doing the DISTINCT > before doing the subquery (and this is nominally a valid > optimisation, depending on scale and statistics) then > the EMPNOs being checked would be in empno order. Would this show up differently in the plan? I don't guess that I have seen that. Typically I have seen the sort phase for the distinct operation as the last step. Or, are you implying that even if the sort phase shows up last, that internally it could have selected the distinct values before doing the correlation? I could see where that could be a valid optimization -- reduce the number of correlated UK index lookups. On the other hand, if most were unique, and the correlated sub-query eliminated many rows, the cost of sorting could be much less when done *after* the correlation, at the expense of more unique index lookups. And which one benefits the most? > > With a standard index, you would get 100% buffering > of index blocks when doing the subquery - with the > reverse key, you COULD get 0% buffering on the > leaf blocks. It tallies with the timing - does it tally > with the execution path ? And especially in my test case. My update was simply a "where rownum < 101" -- yeah, I should have done something random. So, with this being a "fresh" table just inserted into, I know there's a high probability that I was updating physically adjacent rows that would correspond very nicely with the index, minimizing the number of blocks to visit and then benefiting from the buffering. And with the FTS on the MLOGS$ table, and the way I did the updates, and the MLOG$ table being "new", I would stand a good chance of reading those updated values in order. So this was like a best case scenario. That's why I then used the MLOG$ table from the "real" table for additional testing. Those updates would have been random, and not updating 1 million rows residing in the fewest blocks possible, thus being a little bit better test than using my MLOG$ table and it's built in advantage due to the way I did the updates. On a table of this size, and if the updates were really random, though, you could conceivably see cases where the difference in the number of index blocks visited could be nearly the same between the two types of indexes. But things could flush out with the RKI requiring physically reading a block again. So, buffer size could play a role. And I also wonder what type of overhead is needed when reversing the value to do the index lookup. Similar to compressed indexes -- we know there can be a big benefit, but we also hear from some people where, in specific cases, the decompression overhead offset any advantage of a smaller index. Anyway, thanks for throwing some things out there to think about. At some point in time, I would like to dig deeper, but just can't justify it now. > > > > Jonathan Lewis > http://www.jlcomp.demon.co.uk > > Author of: > Practical Oracle 8i: Build
Re: Reverse Key Index Performance
Larry,
|control all other factors. And I will not have the chance to do so.
As far
|as they are concerned, the production problem is resolved. So,
there's no
|need to more thoroughly investigate this -- let's move on to other
pressing
|matters. I'd like to have more details, but it's hard to justify
spending
|more time on it.
Sometimes it's a pity that a problem can be resolved
without being understood, but that's the real world.
A couple of thoughts (for next time).
It would be useful to see the execution plans (particularly
to see the plan dumped in the trace files just in case
the theoretical plan was not the same as the
actual plan). Also the full EXPLAIN PLAN output to see
if the estimated index access costs on the subquery varied.
One thought that could explain the discrepancy, which
would be controlled by the type of query and the size of
the table.
If Oracle optiimises the query by doing the DISTINCT
before doing the subquery (and this is nominally a valid
optimisation, depending on scale and statistics) then
the EMPNOs being checked would be in empno order.
With a standard index, you would get 100% buffering
of index blocks when doing the subquery - with the
reverse key, you COULD get 0% buffering on the
leaf blocks. It tallies with the timing - does it tally
with the execution path ?
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Author of:
Practical Oracle 8i: Building Efficient Databases
Next Seminar - Australia - July/August
http://www.jlcomp.demon.co.uk/seminar.html
Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
-Original Message-
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: 04 May 2002 23:52
|Jonathan,
|
|Absolutely, the index was being used whether reverse key or not.
Surrogate
|key defined as RKI using direct inserts and a sequence for populating
the
|key (no caching on the sequence). Environment, 64 bit 8.1.7.3 Solaris
2.7
|EMC Symmetrix (raw).
|
|The query was a correlated NOT IN generated by Oracle's replication
process
|for a primary key fast refresh, just like the following except with
real
|table names instead of EMP:
|
|SELECT
| DISTINCT LOG$."EMPNO"
|FROM
| (SELECT MLOG$."EMPNO"
| FROM "SCOTT"."MLOG$_EMP" MLOG$
| WHERE "SNAPTIME$$" > :1 AND
| ("DMLTYPE$$" != 'I')) LOG$
|WHERE (LOG$."EMPNO") NOT IN (SELECT MAS_TAB$."EMPNO"
| FROM "EMP" "MAS_TAB$"
| WHERE LOG$."EMPNO" = MAS_TAB$."EMPNO")
|
|So, for every row in the MLOG$ table, a unique index lookup would be
|performed on the PK of the table being replicated, EMP_PK in the
example
|above. And this was verified by tracing the session and examining the
plan.
|And in my testing of just the query, I would also verify the plan. In
the
|real world case, MLOG$ will vary between 500,000 and 5 million rows a
day,
|just depends on the loads done that day. The table on which the
snapshot is
|created is around 250 - 275 million rows, I'm thinking 30-40 gig
total size
|(I'm not at work, can't verify) with the reverse key PK a few gig.
|
|So, when doing a 10046 trace with waits, saw big time waits on db
file
|sequential reads. Ok, so possible I/O contention, maybe a hot disk,
|saturated switch, whatever. But, they don't have the tools to dig
into the
|black box called EMC to see if we had hot disks. And the SA's don't
have
|anything (they are working on it) that map things out. With the
striping
|that was done, who knows what else might reside on those same disks
that
|could be causing contention. But from a fiber and switch standpoint,
they
|have never seen any saturation issues with everything working well
below
|peak capacity.
|
|But, I did note that of all the tables being replicated, and many
pushing 1
|to 5 million rows a day, sometimes much more, the only two that were
|experiencing performance issues in the past were those with reverse
key
|PK's. So, decided to test RKI's against regular B-Trees. I created a
copy of
|the 250 million row table, and created the snapshot log. And it's
hard to
|say exactly how it ends up getting laid out on the disks -- working
with the
|SA's and production DBA's on that. I then created the reverse key
index. I
|generated 1 million inserts and 1 million updates, giving me 2
million rows
|in my personal MLOG$ table.
|
|Ran the query, it ran for a while (killed it after 1.5 hours). Once
again
|seeing severe waits on db file sequential reads. Dropped the RKI and
|created, using the same TS, as a B-TREE. Query finished in 6 minutes.
And
|this is what I was talking about earlier and having an
"un-controlled"
|environment. Just because I create the RKI, and then the b-tree, in
the same
|TS, the way the data actually got placed on the physical disks could
vary
|greatly and at this time they don't have the tools to investigate it.
So I
|repeated the process, going back to a RKI, ran the query, killed it
after
|1.5 hours, once again seeing waits on sequential reads
RE: Reverse Key Index Performance
, OS, DB, and activity, I don't think I can safely draw that conclusion. You know, like telling a frog with no legs to jump and then writing in your journal that frogs with no legs are deaf ;-) I don't want to be that guy ;-) So, that's why I ask what other people have seen and about their experiences and testing. And sorry for the length. Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Jonathan > Lewis > Sent: Saturday, May 04, 2002 4:23 PM > To: Multiple recipients of list ORACLE-L > Subject: Re: Reverse Key Index Performance > > > > If this is Oracle 8.1, it is possible for the optimizer > to reject even a primary key index as too expensive > once it has been reversed. Did you check the execution > path (and I/O characteristics if necessary) to see if the > index was still being used. > > I haven't been able to emulate the problem in 9.0 yet. > > > Jonathan Lewis > http://www.jlcomp.demon.co.uk > > Author of: > Practical Oracle 8i: Building Efficient Databases > > Next Seminar - Australia - July/August > http://www.jlcomp.demon.co.uk/seminar.html > > Host to The Co-Operative Oracle Users' FAQ > http://www.jlcomp.demon.co.uk/faq/ind_faq.html > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Larry Elkins INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Reverse Key Index Performance
If this is Oracle 8.1, it is possible for the optimizer to reject even a primary key index as too expensive once it has been reversed. Did you check the execution path (and I/O characteristics if necessary) to see if the index was still being used. I haven't been able to emulate the problem in 9.0 yet. Jonathan Lewis http://www.jlcomp.demon.co.uk Author of: Practical Oracle 8i: Building Efficient Databases Next Seminar - Australia - July/August http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: 04 May 2002 15:03 |Listers, | |Has anyone done extensive benchmarking of unique key index lookups comparing |reverse key and b-tree? For the sake of brevity, I am leaving out a lot of |details at this point. Just simply had a case where doing a million unique |key lookups using a reverse key index would run for hour(s). Change to |b-tree, 6 minutes. Build again as reverse key, same TS, "fresh" index, runs |for hours again. I've got lots of ideas but I want to keep this short. | |Query was correlated sub-query (replication query for the <> "I"). Sure, |there are issues with RKI's not packing as much in and being bigger, you |lose any benefit of optimal clustering, the range scan issue, etc. And for |rows physically located together, I know I will have to access more index |blocks to get those rows versus a b-tree with good clustering of data. And |how much overhead is needed for reversing the value used to do the lookup? |And my test case was hardly a controlled environment where I could rule out |or control all other factors. And I know of some things that could very well |have skewed the testing. | |Anyway, I'm curious if anyone else has done some benchmarking on this. I |would be curious about the results and comparing notes. | |Regards, | |Larry G. Elkins |[EMAIL PROTECTED] |214.954.1781 | -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Reverse Key Index Performance
Listers, Has anyone done extensive benchmarking of unique key index lookups comparing reverse key and b-tree? For the sake of brevity, I am leaving out a lot of details at this point. Just simply had a case where doing a million unique key lookups using a reverse key index would run for hour(s). Change to b-tree, 6 minutes. Build again as reverse key, same TS, "fresh" index, runs for hours again. I've got lots of ideas but I want to keep this short. Query was correlated sub-query (replication query for the <> "I"). Sure, there are issues with RKI's not packing as much in and being bigger, you lose any benefit of optimal clustering, the range scan issue, etc. And for rows physically located together, I know I will have to access more index blocks to get those rows versus a b-tree with good clustering of data. And how much overhead is needed for reversing the value used to do the lookup? And my test case was hardly a controlled environment where I could rule out or control all other factors. And I know of some things that could very well have skewed the testing. Anyway, I'm curious if anyone else has done some benchmarking on this. I would be curious about the results and comparing notes. Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Larry Elkins INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
