Re: need advice for query time on Oracle

From: Bert Bear <bertbear_at_NOSPAMbertbear.net>
Date: Sat, 14 Dec 2002 22:46:25 GMT
Message-ID: <5xOK9.2125$SP2.910831815@newssvr11.news.prodigy.com>

jyou,

Could you tell me more about the ID field. Is it unique and/or the primary key? If so, my thoughts are in the wrong direction.

If the ID field is NOT the primary key or unique, could you do me a favor and run the following script on the two systems and report about on the results.

cut here/trim down -----

rem $Header: /usr/local/hotsos/RCS/hds.sql,v 1.8 2002/01/07 18:12:27 hotsos Exp $
rem Copyright (c) 2000-2002 by Hotsos Enterprises, Ltd. All rights reserved. rem Author: jeff.holt_at_hotsos.com
rem Notes: Hotsos data selectivity using a full table scan for the row count.

define v_substr7 = 'substr(rowid,15,4)||substr(rowid,1,8)'
define v_substr8 = 'substr(rowid,7,9)'
define v_over = 'substr(''&_O_RELEASE'',1,1)'

col dummy new_value v_substr

set termout off heading on pause off

select decode(&v_over, '7', '&v_substr7', '&v_substr8') dummy from dual;

set termout on verify off feedback off pages 10

accept p_town prompt 'TableOwner : '
accept p_tname prompt 'TableName : '
accept p_clst prompt 'ColumnList : '
accept p_where prompt 'WhereClause: '
accept p_pgs prompt 'PageSize : '

variable fblks number

declare
tblks number;
tbytes number;
ublks number;

ubytes number;
luefid number;
luebid number;

lublk number;
begin
sys.dbms_space.unused_space(
upper('&p_town'), upper('&p_tname'), 'TABLE', tblks, tbytes, ublks, ubytes, luefid, luebid, lublk, null );
:fblks := tblks - ublks;
end;
/

col blks form 9,999,999,999 heading 'Table blocks below hwm|(B)' just c col nrows form 999,999,999,999 heading 'Table rows|(R)' just c new_value v_nrows

select :fblks blks, count(*) nrows
from &p_town..&p_tname;

col bs form a17 heading 'Block selectivity|(pb = b/B)' just c col nblks form 9,999,999,999 heading 'Block count|(b)' just c col rs form a17 heading 'Row selectivity|(pr = r/R)' just c col nrows form 999,999,999,999 heading 'Row count|(r)' just c

set pause on pause 'More: ' pages &p_pgs

select &p_clst,
lpad(to_char(count(distinct &v_substr)/:fblks*100,'990.00')||'%',17) as bs, count(distinct &v_substr) nblks,
lpad(to_char(count(*)/&v_nrows*100,'990.00')||'%',17) rs, count(*) nrows
from &p_town..&p_tname &p_where
group by &p_clst
order by bs desc;

cut here/trim up -----

I find this script helpful when considering I/O problems and building indexes. The idea is blocks (not rows) are the important index and I/O tuning point. The fewer blocks necessary to read from disk the better.

One thought on why the PC is faster than the Sun; the PC is recently loaded data and might require fewer block requests than live data. (Again with the caveat of ID being non-unuique.)

Another request (for grins) on the Sun system, is:

Create a table (e.g. create table T001 as select * from <your_table_name> order by id) built ordering the ID column.
Run the above script on T001
Run your query and statistics on the new table
Create the index for ID
Run your query and statistics on the new table with index

Post the results, I'm not sure what we'll see, but maybe a reduction in I/O wait time necessary to fetch rows access by "where id=xxxxx" Of couse, the T001 table and index might not be good for production as the ID field might not be in the majority of select statements.

In addition to autotrace, you might want to look at using SQL_TRACE at the session level and running TKProf.

Of course, if your 500ms and 140 ms are coming from the end-user/end-program's perspective, you need to look at the ENTIRE data handling path from disk to user!!

Bertram Moshier
Oracle Certified Professional 8i and 9i DBA

http://www.bmoshier.net/bertram

"jyou" <member_at_dbforums.com> wrote in message news:2278938.1039803367_at_dbforums.com...
>
> I am not an Oracle DBA, but a Java programmer.
> We have this Java web application which runs on a Weblogic server and
> it connects to the Oracle 8.1.7 through JDBC, driver is type5 thin.
> The web server is on the same machine with the Oracle server.
> This machine is our production server which has 10 cpus, ram is 10G.
> Supposed to be super good, OS is solaris.
> Both the two servers are mostly only used to serve this application, not
> heavy loaded.
> I test the program, and get the query time through Java, and found
> it takes about 500 ms to execute the query( the query time is the
> time to call java Statement.executeQuery() method).
> This table contains about 5,0000 records, each with about 50 fields.
> The query is like:
> select a, b, c, d, e...etc where id = xxxx
> The id in the table is indexed. I checked the query plan, and it seems
> the query does use the index.
>
> I am really puzzled by this "500 ms to execute such a simple query
> on such a super server with everything we want".
> I am not happy with this performance, but as application programmer, I
> can do nothing on the Oracle side.
>
> To convince my colleague that we need to tune the Oracle server, I
> then installed Oracle server and appliation server in my own PC(P4,
> 1G RAM). And the query time is about 140 ms.
>
> So, my question to is that:
> Is this 500 ms a fair query time?
>
> --
> Posted via http://dbforums.com
Received on Sat Dec 14 2002 - 16:46:25 CST