Axton:
I'm talking about archiving to a historical record form rather than using partitioning within the data table for one form. Makes searches run faster/better when your users forget to select 'status' <= "Resolved".
James McKenzie
L-3 GSI
-----Original Message-----
From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of Axton
Sent: Thursday, May 04, 2006 8:27 AM
To: [email protected]
Subject: Re: Oracle 9.2.0.5 CBO Not Using Index
The beauty of partitioning is that is can be a transparent method of archiving, esp. since status is typically the method used to archive tickets/requests.
Axton Grams
On 5/4/06, McKenzie, James J C-E LCMC HQISEC/L3 <[EMAIL PROTECTED]> wrote:
> **
>
> Axton:
>
> Partitioning is a fantastic feature and should be employed where it can.
> However, BMC did not decide to use this feature out of the box. I
> would help greatly if you could partition a high use form, such as the
> Help Desk Case, by status. That and a good archiving system is
> advised. When you have almost 90% of your actions in a form as closed
> or no longer worked, that is a good time to think about installing or
> implementing a good archiving system with a good solutions database.
>
>
> James McKenzie
> L-3 GSI
>
>
> -----Original Message-----
> From: Action Request System discussion list(ARSList)
> [mailto:[email protected]] On Behalf Of Axton
> Sent: Thursday, May 04, 2006 7:55 AM
> To: [email protected]
> Subject: Re: Oracle 9.2.0.5 CBO Not Using Index
>
>
>
>
> I can find nothing on the net to confirm your statements. The closest
> thing I can find that relates to this is the list of the "20 golden
> rules" of the oracle RBO.
>
> http://www.oreilly.com/catalog/orsqltunpr/chapter/excerpt.html
>
> In this list, bound range and unbound range searches still rank higher
> than a table scan.
>
> I ran into a similar problem as this with the email messages form,
> where 2m records were sent and < 3000 were error/received/sent. When
> the email engine executes its query to poll for messages to send, it
> will not use an index on the status field (using an equal comparison),
> even though that index narrowed the query to < 50 records out of > 2m
> records. Don't ask me why, but partitioning the table was the only
> resolution I could come up with to get the query the email engine
> issues to use an index. Hopefully the folks at BMC tweaked the next
> release of their product to use a more structured query that is capable of using indexes on the db's they support.
>
> Axton Grams
>
> On 5/4/06, McKenzie, James J C-E LCMC HQISEC/L3
> <[EMAIL PROTECTED]> wrote:
> > **
> >
> > Axton:
> >
> > That was directly from the PT&T class and was enforced by some basic
> > ORACLE training that I received.
> >
> > Here is gist of the training:
> >
> > If the query can be closed end (i.e. equals) then the optimizer will
> > look to see if the index can be used. This happens at the point
> > where about eight percent or less of the index entries are pulled back.
> >
> > If the query is not closed end (i.e not equals) then the optimizer
> > will do a full table scan.
> > So the key here is what is and is not closed end. If the entry has
> > an equals sign in it like the following:
> >
> > 'Status' <= "Pending" the optimizer SHOULD look at the index and see
> > approximately how many entries would be pulled back.
> >
> > 'Status' < "Resolved" SHOULD result in a full table scan.
> >
> > The key here is that the data in the field must be highly variable
> > OR the data is bunched around a single value. In this case, over
> > 280,000 of about 300,000 have a single value with the remaining
> > 20,000 (less than 8%) are scattered around the remaining values.
> > So, if the training I received is correct, using the <= value should
> > result in an index scan IF you do not use the value with 280,000
> > entries, which is the
> case here.
> >
> > I don't have a form with 1,000 let alone 100,000 entries. Does
> > anyone want to confirm/deny that this is the case?
> >
> > James McKenzie
> > L-3 GSI
> >
> >
> > -----Original Message-----
> > From: Action Request System discussion list(ARSList)
> > [mailto:[email protected]] On Behalf Of Axton
> > Sent: Wednesday, May 03, 2006 4:54 PM
> > To: [email protected]
> > Subject: Re: Oracle 9.2.0.5 CBO Not Using Index
> >
> >
> >
> >
> > I don't think using > vs. >= will make a difference, but the stats
> > could prove me wrong.
> >
> > Consider partitioning the table on the allowed status values. You
> > can accomplish this using the ardb.cfg file for new tables or using
> > the ardb.conf file plus your dba for existing tables; though this
> > will require some offline time to convert existing tables, esp.
> > those with large amounts of data (read worklog and audit trail diary fields).
> >
> > If you partition the table into 3 sections based on range (keyword
> > "range
> > partition"):
> >
> > PartitionA: 0-4
> > PartitionB: 5
> > PartitionC: 6
> >
> > Then also include in the ardb.cfg to create the index on the status
> > field using the same partition ranges.
> >
> > There is a way in 9i and higher to allow row relocation between
> > partitions that you will have to investigate. The keywords escape
> > me at
> the moment.
> >
> > See these links for more specifics:
> >
> http://www.oracle.com/technology/products/oracle9i/datasheets/partitio
> > ning.html
> >
> http://www.lc.leidenuniv.nl/awcourse/oracle/server.920/a96521/partiti.
> > htm
> >
> > One little gem I will throw out there is that the ardb.cfg file only
> > interprets the first 314 or so characters of the clause you provide,
> > so don't overstep that limit or the table/index creation will fail.
> >
> > This method will ensure your server will use the index provided for
> > the status column.
> >
> > Axton Grams
> >
> > On 5/3/06, McKenzie, James J C-E LCMC HQISEC/L3
> > <[EMAIL PROTECTED]> wrote:
> > > **
> > >
> > > Jon:
> > >
> > > Your problem is using the < sign. This forces Oracle to check
> > > EACH and EVERY entry in the table. Try this:
> > >
> > > 'Status' <= "Pending"
> > >
> > > This should use the index as you are now looking for a value that
> > > has an end.
> > >
> > > James McKenzie
> > >
> > >
> > >
> > >
> > > -----Original Message-----
> > > From: Action Request System discussion list(ARSList)
> > > [mailto:[email protected]] On Behalf Of Jon Chau
> > > Sent: Wednesday, May 03, 2006 2:30 PM
> > > To: [email protected]
> > > Subject: Oracle 9.2.0.5 CBO Not Using Index
> > >
> > > Hello Listers,
> > >
> > > I've read some past threads on Oracle CBO optimization and
> > > database tuning and have been having a constant battle with my DBA
> > > on how to resolve the issue we are having. We have a HelpDesk
> > > form with an index against the Status field and it is not being
> > > used when we are using a range query. Example - 'Status' <
> > > "Resolved". Our optimization mode is set to choose and due to the
> > > distribution it wants/does a full table scan and causes several
> > > queries against the Status field to timeout:
> > >
> > > COUNT(C7) C7
> > > ---------- ----------
> > > 3 0 - New
> > > 597 1 - Assigned
> > > 192 2 - Work In Progress
> > > 133 3 - Pending
> > > 2083 4 - Resolved
> > > 283007 5 - Closed
> > > 34 6 - Recovered
> > >
> > > As you can see there are under 300k rows on this form/table.
> > >
> > > I looked into changing optimizer_index_cost_adj parameter so maybe
> > > the CBO would use the index but it didn't have an effect. We
> > > tried changing it to 10 on an session basis and ran our query. We
> > > checked to see the suggested parameter value from a script that
> > > was given
> > > here:
> http://www.dba-oracle.com/oracle_tips_cost_adj.htm
> > > and that
> > > wanted us to use 0, which is almost like using rule based
> > > optimization mode (my DBA said). Rule based does hit the index,
> > > but we want to stay on choose. We also update statistics on a
> > > weekly basis. Does anyone have any suggestions? The only thing I
> > > can think of is archiving, but it sounds like there are higher
> > > volume systems out there that don't have this problem.
> > >
> > > Environment
> > > Oracle 9.2.0.5 on HP-UX instance shared with another application
> > > ARS
> > > 6.3 p13 Windows 2003 HelpDesk 6.0
> > >
> > > TIA,
> > > Jon
> > >
> > >
> >
> ______________________________________________________________________
> > > _________ UNSUBSCRIBE or access ARSlist Archives at
> > > http://www.wwrug.org
> > __20060125_______________________This posting
> > > was submitted with HTML in it___
> >
> >
> >
> ______________________________________________________________________
> > _________
> >
> > UNSUBSCRIBE or access ARSlist Archives at http://www.wwrug.org
> >
> >
> > __20060125_______________________This posting was
> submitted with HTML
> > in it___
>
>
> ______________________________________________________________________
> _________
>
> UNSUBSCRIBE or access ARSlist Archives at http://www.wwrug.org
>
>
> __20060125_______________________This posting was submitted with HTML
> in it___
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at http://www.wwrug.org
