Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: time performance SQLPLUS VS SQL Worksheet

Re: time performance SQLPLUS VS SQL Worksheet

From: Winnie Liu <poohland_at_hotmail.com>
Date: Wed, 29 Jul 1998 20:04:10 -0700
Message-ID: <6ponj6$1to@dfw-ixnews10.ix.netcom.com>


Do you run the procedure in SQLPLUS first and then run it in the SQL Worksheet? If so, since the SQL statement is exactly the same, Oracle will use the parse tree that resign in the Shared_SQL_area the next time you run the same query (provided that the parse tree in that SQL statement is still in that Shared_SQL_area). That mean, the first time you run that query in SQLPLUS, oracle try to find the best way to run that query and store it as a 'parse tree' in the Shared SQL area. The next time you run that exact same query in SQL worksheet, Oracle will try to see if there is any 'parse tree' for that query by applying the hash function on your SQL statment. If so, they don't have to parse the statement again. That means you will get a faster response the next time you run the exact statement.

Also, it may also be that the first time you run that statement. The data that you are looking for is not the the database buffer. So DBWR has to physically read them into the database buffer. This will take some time for the DBWR to do so. But the next time you run the exact same query. The data is already in the buffer, so that the server process this time has a cache hit. It will definitely be a lot faster than.

As far as I know, no matter what kind of tools you use to access the database. Oracle is using the same optimizer. It actually depends on your parameter in the init.ora file (optimizer_mode.. or something similar). So, I don't think SQL PLUS or SQL worksheet use different optimizer.

Hope it can help

Winnie Liu

poohland_at_hotmail.com

orsano wrote in message <35BEE78D.EC43EE92_at_inf.uniroma3.it>...
>Hi.
>Is there someone who can tell me why running sqllpus queries into SQL
>Worksheet I've
>faster time response than using a stored procedure (with the same query)
>
>called into SQLPLUS?
>May be SQLPLUS and SQL Worksheet use a different default Oracle
>optimizer.
>
>My Oracle version is 7.3.3.
>
>Thanks
>
>
><><><><><><><><><><><><><><><>
>
>Orsano Alessandro
>e-mail: orsano_at_dia.uniroma3.it
>
><><><><><><><><><><><><><><><>
>
>
Received on Wed Jul 29 1998 - 22:04:10 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US