| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Re: cardinality in query plans?
Ryan,
With all due respect - you speak about guaranteeing performance (as per an SLA) for 30,000 concurrent users on a machine that you expect to have no access to. Whoever signed such an agreement wasn't thinking very well at the time - that's a crazy risk which is pretty much setting yourselves up for failure. I think the easiest way to tune this system would be to renegotiate the SLA - just sign a non-disclosure agreement and get access to the production environment. It seems that you have so many variables (including not knowing real data values and distributions) to contend with.
What sort of data could be made available to 30,000 concurrent users yet also be so secret that one additional DBA cannot see the data? That's exactly what non-disclosure agreements are for.
Is your test case (where "BILLY%" matches one quater of the result set) even typical of the query that will run in production? Perhaps no literal will represent more than 1 per cent of results and the query will resolve itself? I don't mean to sound harsh but it just seems like your doing things the hard way.
<ryan.gaffuri_at_cox.
net> To: oracle-l_at_freelists.org, <oracle-l_at_freelists.org>
Sent by: cc:
oracle-l-bounce_at_fr Subject: Re: Re: cardinality in query plans?
eelists.org
16/03/2004 04:05
Please respond to
oracle-l
response in line
>
> From: "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk>
> Date: 2004/03/15 Mon AM 11:07:10 EST
> To: <oracle-l_at_freelists.org>
> Subject: Re: Re: cardinality in query plans?
>
>
> There are a couple of articles of mine on
> www.dbazine.com/oracle.shtml which may
> explain why Oracle chooses the tablescan
> for "billy%" Note, though, that based on the
> "billy" value, Oracle is reporting the right
> cardinality, and doing the right thing. Oracle
> is doing the slow path when it gets the right
> answer for the cardinality.
>
> Before you move to hinting, have you examined
> system statistics. If you can calibrate the system
> so that it has a realistic single-block read time compared
> to multiblock read time, the paths may correct themselves
> automatically. You also need to get the apparent CPU
> speed into the system, so that Oracle can check the
> CPU cost of scanning all those rows.
system stats may not be an option. We will not have access to the
production system, yet have to guarantee performance. so when do i know to
run the gather_system_stats?
>
> However - before you get to that - are you sure that
> the sample query is correct. Oracle defaults to using
> a RANGE scan on TAB1_PK according to the plan,
> but there appear to be no columns it could be using
> to avoid a FULL scan.
>
ahhhh... I totally botched this. I have the alias backwards. All columns
are present in the index. Sorry about this.
select /*+ ordered index(a TAB2_IND) */ COL3, COL4, COL5
from TAB2 a, TAB1 b
WHERE b.col1 = a.col1
AND b.col2 = a.col2
AND b.col3 = a.col3
and col6 = 'hello'
and a.searchCol like upper('billy%')
order by a.hh_nm, a.hh_id, a.acct_no
correct plan
CASE 2 Now with hints or bind variables:
Execution Plan
5 4 WINDOW (SORT) (Cost=346 Card=1 Bytes=89) 6 5 HASH JOIN (Cost=344 Card=1 Bytes=89) 7 6 INDEX (RANGE SCAN) OF 'TAB2_IND'(UNIQUE) (Cost=11 Card=2250 Bytes=36000) 8 6 TABLE ACCESS (BY INDEX ROWID) OF 'TAB' (Cost=337 Card=43539 Bytes=3178347) 9 8 INDEX (RANGE SCAN) OF 'TAB1_PK' (NON-UNIQUE) (Cost=44 Card=1)
> Is it possible that there is some other predicate on
> TAB1 that is causing the problem (it seems to be
> returning only 2250 rows out of 16M in the default
> plan) or are the statistics on the TAB1 index totally
> misleading ?
I just generated the statistics. This is an oltp query and there should not be many records returned.
>
> (I have no comment to make on TAB2 and the other
> table at the moment).
>
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
> April 2004 Iceland
> June 2004 UK - Optimising Oracle Seminar
>
>
> ----- Original Message -----
> From: <ryan.gaffuri_at_cox.net>
> To: <oracle-l_at_freelists.org>
> Sent: Monday, March 15, 2004 3:36 PM
> Subject: Re: Re: cardinality in query plans?
>
>
> : yes billy in this case uses a large fraction of the results. 262,000
> records out of 870,000. I really would prefer not to hint. I do not have
> production data yet, so big and small in production may not be the same.
> however, we are currently in acceptance testing so when the client sees
the
> query we must not have a problem.
> :
> : is there anyway to provide oracle with better information? The odd
thing
> was that in the past on a different data set, I had to remove bind
variables
> to make this same query work. This is troubling.
> :
> : I'll attempt to analyze the 10053 trace and I have a TAR open.
> :
> : Why would Oracle cost a full table scan lower than an index search even
in
> spite of the large number of records? I can clearly tell with
runstats_pkg
> that the index scan is better.
> :
> : Here is another oddity. If I use hard coded values. I then ONLY hint
the
> index, oracle uses the index, but the incorrect join order. This is
> happening in a join between TAB2 and another table also.
> :
> : I am fearful of hints since I cannot predict what the data will look
like
> in production. I may not see the production data at all due to security
> reasons(not government, its private sector), yet I have to guarantee
> performance. Per our SLA this has to support 30,000 concurrent users. Now
> trimming off 9,000 LIOs on a frequently used query is very important to
> scaling this application.
> :
>
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such a case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (03) 9612-6999 or (61) 3 9612-6999. Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of Transurban Infrastructure Developments Limited and CityLink Melbourne Limited shall be understood as neither given nor endorsed by them. <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Mon Mar 15 2004 - 17:31:02 CST
 |
 |