Return-Path: Delivered-To: 2-oracle-l@orafaq.com Received: (qmail 15910 invoked from network); 2 Oct 2007 12:04:03 -0500 Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180) by 69.64.49.119 with SMTP; 2 Oct 2007 12:04:00 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 901DB78AA51; Tue, 2 Oct 2007 13:03:55 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 07701-05; Tue, 2 Oct 2007 13:03:55 -0400 (EDT) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 06A1278A0C8; Tue, 2 Oct 2007 13:03:54 -0400 (EDT) Received: with ECARTIS (v1.0.0; list oracle-l); Tue, 02 Oct 2007 12:18:22 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 8BBE578B7AE for ; Tue, 2 Oct 2007 12:18:22 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 28932-06 for ; Tue, 2 Oct 2007 12:18:22 -0400 (EDT) Received: from trinity.uptime.be (trinity.uptime.be [193.121.44.112]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 5BD8E78B7B2 for ; Tue, 2 Oct 2007 12:18:19 -0400 (EDT) X-ASG-Debug-ID: 1191340691-1e42002e0004-JbYmij X-Barracuda-URL: http://193.121.44.112:8000/cgi-bin/mark.cgi Received: from ws03-exchange.iconos.be (localhost [127.0.0.1]) by trinity.uptime.be (Spam Firewall) with ESMTP id 12D241123C1 for ; Tue, 2 Oct 2007 17:58:12 +0200 (CEST) Received: from ws03-exchange.iconos.be ([10.0.10.5]) by trinity.uptime.be with ESMTP id UYXT6uko49caIpgI for ; Tue, 02 Oct 2007 17:58:12 +0200 (CEST) X-MimeOLE: Produced By Microsoft Exchange V6.5 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C8050C.E6F85A59" X-ASG-Orig-Subj: RE: Very Strange Query Access Plan Subject: RE: Very Strange Query Access Plan Date: Tue, 2 Oct 2007 17:55:21 +0200 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Very Strange Query Access Plan References: From: "D'Hooge Freek" To: , X-Barracuda-Connect: UNKNOWN[10.0.10.5] X-Barracuda-Start-Time: 1191340692 X-Barracuda-Virus-Scanned: by Uptime Spam Firewall at uptime.be X-archive-position: 2070 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: Freek.DHooge@uptime.be Precedence: normal Reply-to: Freek.DHooge@uptime.be List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l X-Virus-Scanned: Debian amavisd-new at localhost.localdomain ------_=_NextPart_001_01C8050C.E6F85A59 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Do you use a bind variable for the ssn column in the query? If so, it could be that because of bind variable peeking you are now = using a different access plan. =20 regards, =20 Freek D'Hooge Uptime Oracle Database Administrator e-mail: freek.dhooge@uptime.be =20 http://www.uptime.be =20 disclaimer =20 =20 ________________________________ Van: oracle-l-bounce@freelists.org namens Mercadante, Thomas F (LABOR) Verzonden: di 2/10/2007 17:12 Aan: oracle-l@freelists.org Onderwerp: Very Strange Query Access Plan All, =20 Running on 9.2.0.7 on Aix. =20 I have a database table with 18 million rows in it. There is an SSN = column in the table. Only 625,000 rows have an SSN populated. I also = have an index on this column. Statistics on both the table and index = are gathered nightly (using dbms_stats with the estimated and default = sample options). =20 Yesterday, queries against this table used the index. Today, they do = not. There was no large data load overnight. We add about 10,000 = records per day. This behavior has happened before where the index gets = ignored for a day and then used the next day. I really need to use an index here. Queries via ssn are executed all = day long and take 10 minutes to complete. CPU is pegged at 99%. What am I missing? Maybe histograms? Haven't used them before, so any = suggestions would be helpful. Thanks Tom ------_=_NextPart_001_01C8050C.E6F85A59 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable =0A= =0A= =0A= =0A= =0A= =0A=
=0A=
Do you use a = bind variable for the ssn column in the query?
=0A=
If so, it could be that = because of bind variable peeking you are now using a different = access plan.
=0A=
 
=0A=
regards,
=0A=
 
=0A=
=0A=
Freek D'Hooge
=0A=
Uptime
=0A=
Oracle Database = Administrator
=0A= =0A= =0A= =0A=
 
=0A=

=0A=
=0A= Van: oracle-l-bounce@freelists.org = namens Mercadante, Thomas F (LABOR)
Verzonden: di 2/10/2007 = 17:12
Aan: oracle-l@freelists.org
Onderwerp: Very = Strange Query Access Plan

=0A=
=0A=
=0A=

All,

=0A=

 

=0A=

Running on 9.2.0.7 on = Aix.

=0A=

 

=0A=

I have a database table = with 18 million rows in it.  There is an SSN column in the table. =  Only 625,000 rows have an SSN populated.  I also have an = index on this column.  Statistics on both the table and index are = gathered nightly (using dbms_stats with the estimated and default sample = options).

=0A=

 

=0A=

Yesterday, queries against = this table used the index.  Today, they do not.  There was no = large data load overnight.  We add about 10,000 records per = day.  This behavior has happened before where the index gets = ignored for a day and then used the next day.

=0A=

I really need to use an = index here.  Queries via ssn are executed all day long and take 10 = minutes to complete.  CPU is pegged at 99%.

=0A=


What am I = missing?  Maybe histograms?  Haven’t used them before, = so any suggestions would be helpful.

=0A=


Thanks

=0A=


Tom

------_=_NextPart_001_01C8050C.E6F85A59-- -- http://www.freelists.org/webpage/oracle-l