Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!postnews.google.com!g49g2000cwa.googlegroups.com!not-for-mail From: "crs_stat" Newsgroups: comp.databases.oracle.server Subject: SQL Performance Problem Date: 2 Feb 2006 09:57:43 -0800 Organization: http://groups.google.com Lines: 56 Message-ID: <1138903063.138112.75440@g49g2000cwa.googlegroups.com> NNTP-Posting-Host: 204.174.64.34 Mime-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" X-Trace: posting.google.com 1138903068 14125 127.0.0.1 (2 Feb 2006 17:57:48 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Thu, 2 Feb 2006 17:57:48 +0000 (UTC) User-Agent: G2/0.2 X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.0.1) Gecko/20060111 Firefox/1.5.0.1,gzip(gfe),gzip(gfe) X-HTTP-Via: 1.1 S1PS Complaints-To: groups-abuse@google.com Injection-Info: g49g2000cwa.googlegroups.com; posting-host=204.174.64.34; posting-account=CDcMPQ0AAACGcl1nX6IdoIhZorIFnxMs Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:260575 I have 2 sql statements S1 and S2. S1 never actually finishes at least it exceeds our time slot so I never wiat for it to finish which is 30 minutes. S2 runs in under a second usually in the area of .2 ish. So the problem has been overcome but I was wondering why. Why was it a problem in the first place. Stats were gathered on the objects in question. I just can't seem to understand why Oracle chose to access the table via xif26NC_PARAMS when the XIF12 index was far superior. The costs are very similar which is also a mystery since one is very good and one is obvisouly very bad. Anyone have any ideas? Thanks S1S1S1S1S1S1S1S1S1S1S1S1S1S1S1 select count(*) from EI$NC_PARAMS tab where exists (select object_id from nc_params where object_id = tab.object_id and attr_id = tab.attr_id ) Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=44) 1 0 SORT (AGGREGATE) 2 1 NESTED LOOPS (SEMI) (Cost=2 Card=1 Bytes=44) 3 2 INDEX (FULL SCAN) OF 'XIF12NC_PARAMS_EI' (NON-UNIQUE) 4 2 TABLE ACCESS (BY INDEX ROWID) OF 'NC_PARAMS' (Cost=1 C ard=6049584 Bytes=108892512) 5 4 INDEX (RANGE SCAN) OF 'XIF26NC_PARAMS' (NON-UNIQUE) S2S2S2S2S2S2S2S2S2S2S2S2S2S2S2S2S2S2S2S2 select count(*) from EI$NC_PARAMS tab where exists (select /*+ index(nc_params XIF12NC_PARAMS) */ object_id from nc_params where object_id = tab.object_id and attr_id = tab.attr_id ) Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=19) 1 0 SORT (AGGREGATE) 2 1 NESTED LOOPS (SEMI) (Cost=2 Card=1 Bytes=19) 3 2 INDEX (FULL SCAN) OF 'XIF12NC_PARAMS_EI' (NON-UNIQUE) 4 2 INDEX (RANGE SCAN) OF 'XIF26NC_PARAMS' (NON-UNIQUE) Stats Gathering Script exec dbms_stats.gather_schema_stats( - ownname => 'psdata1', - options => 'GATHER AUTO', - estimate_percent => dbms_stats.auto_sample_size, - method_opt => 'for all columns size auto', - degree => 15 - )