Re: Fwd: Re: [PHP-DB] OCIFetch performance problem with more rows
On Wednesday 23 May 2001 13:13, Adi Wibowo wrote: > On Wed, 23 May 2001, Thies C. Arntzen wrote: > > > > > I have performance problem with OCIFetch (and OCIFetchInto, too) > > > > > when query return more rows.Same query from sqlplus is more fastest > > > > > (more rows is worse then less rows) > > > > > > Indexes aren't problem.I use php and sqlplus with same tables. > > > Sqlplus is faster. > > Of course SQLPlus execution *seems* tob faster. Once again ... seems ... > > This is caused by different optimation approach executed by Oracle. > There are two kind of optimation : > - Rule based optimation : faster first row > - Cost based optimation : faster all row > > Rule Based Optimation will give you faster FIRST ROW display. First row > will be displayed as fast as it can eventhough all necesarry process/steps > are not done yet. This is good for access via tools like SQLPlus, where > operator doesn't want to wait for a long time just to see the first row. > > But this doesn't suit for some kind of operation like OLTP (Online > Transaction Processing like accessing oracle using PHP. > > This is where cost based optimation take action. > CBO gives best througput using the least amount of resourcess necessary to > process all rows accessed by sql statement. It means faster time for > returning ALL rows compared with RBO. > > So you see that SQLPlus give you faster result .. BUT .. only for the > first row. So it seems to be faster. But it doesn't. > > Important : Eventhough oracle will use CBO as a default, but it need some > conditions to be met. Oracle Data dictionary need to have statistics about > all schema object on your database. We should collect statistics by using > DBMS_STATS package, or ANALIZYE statement. Otherwise oracle will use RBO > which is slower to return data to our PHP. > > It will be better if we use document from oracle site : > "Oracle 8i : Designing and Tuning for Performance". > WRONG. I use sqlplus script (not interactive) : Script is same in PHP.Number of rows is same (4000).Optimizer is same (from init.ora) and sqlplus do array processing and PHP not (this is only different) ANALYZE isn't important (I do , but this is for PHP and sqlplus) regards Haris Peco [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: Fwd: Re: [PHP-DB] OCIFetch performance problem with more rows
On Wednesday 23 May 2001 12:21, you wrote: > > > > >Hello, > > > > > I have performance problem with OCIFetch (and OCIFetchInto, > > > > > too) when query return more rows.Same query from sqlplus is more > > > > > fastest (more rows is worse then less rows) > > > > > > > >Are the columns you're fetching included in an Oracle index? > > > > Without an appropriate index, your SQL statement is probably doing a > > > > full table scan, which can be *very* time-consuming... > > > > > > Hello, > > > Indexes aren't problem.I use php and sqlplus with same tables. > > > Sqlplus is faster. > > > > by what amount? > > May be You could try to use OCISetPrefetch - it is not documented, but > there is short note at www.php.net from somebody about it. > I can say, it speeds things up very much (if You wanna more than 1 row) > Btw. anybody knows why it is not documented (I mean "hidden" in some > way) I try, but it is same. OCISetPrefetch increase 'fetch memory' , but I think that is problem with array.Function oci_fetch have parameter 'nrows', but this is always 1 (possible that this is TODO). -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: Fwd: Re: [PHP-DB] OCIFetch performance problem with more rows
On Wed, 23 May 2001, Thies C. Arntzen wrote: > > > > I have performance problem with OCIFetch (and OCIFetchInto, too) > > > > when query return more rows.Same query from sqlplus is more fastest (more > > > > rows is worse then less rows) > > Indexes aren't problem.I use php and sqlplus with same tables. > > Sqlplus is faster. Of course SQLPlus execution *seems* tob faster. Once again ... seems ... This is caused by different optimation approach executed by Oracle. There are two kind of optimation : - Rule based optimation : faster first row - Cost based optimation : faster all row Rule Based Optimation will give you faster FIRST ROW display. First row will be displayed as fast as it can eventhough all necesarry process/steps are not done yet. This is good for access via tools like SQLPlus, where operator doesn't want to wait for a long time just to see the first row. But this doesn't suit for some kind of operation like OLTP (Online Transaction Processing like accessing oracle using PHP. This is where cost based optimation take action. CBO gives best througput using the least amount of resourcess necessary to process all rows accessed by sql statement. It means faster time for returning ALL rows compared with RBO. So you see that SQLPlus give you faster result .. BUT .. only for the first row. So it seems to be faster. But it doesn't. Important : Eventhough oracle will use CBO as a default, but it need some conditions to be met. Oracle Data dictionary need to have statistics about all schema object on your database. We should collect statistics by using DBMS_STATS package, or ANALIZYE statement. Otherwise oracle will use RBO which is slower to return data to our PHP. It will be better if we use document from oracle site : "Oracle 8i : Designing and Tuning for Performance". Adi Wibowo --- * Work matter: [EMAIL PROTECTED] * Private matter : [EMAIL PROTECTED] -- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: Fwd: Re: [PHP-DB] OCIFetch performance problem with more rows
On Wed, May 23, 2001 at 12:21:08PM +0200, Remigiusz Sokolowski wrote: > > > > >Hello, > > > > > I have performance problem with OCIFetch (and OCIFetchInto, too) > > > > > when query return more rows.Same query from sqlplus is more fastest (more > > > > > rows is worse then less rows) > > > > > > > >Are the columns you're fetching included in an Oracle index? Without an > > > > appropriate index, your SQL statement is probably doing a full table scan, > > > > which can be *very* time-consuming... > > > > > > Hello, > > > Indexes aren't problem.I use php and sqlplus with same tables. > > > Sqlplus is faster. > > > > by what amount? > > May be You could try to use OCISetPrefetch - it is not documented, but > there is short note at www.php.net from somebody about it. > I can say, it speeds things up very much (if You wanna more than 1 row) > Btw. anybody knows why it is not documented (I mean "hidden" in some > way) nobody has gotten around documenting it. tc -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: Fwd: Re: [PHP-DB] OCIFetch performance problem with more rows
> > > >Hello, > > > > I have performance problem with OCIFetch (and OCIFetchInto, too) > > > > when query return more rows.Same query from sqlplus is more fastest (more > > > > rows is worse then less rows) > > > > > >Are the columns you're fetching included in an Oracle index? Without an > > > appropriate index, your SQL statement is probably doing a full table scan, > > > which can be *very* time-consuming... > > > > Hello, > > Indexes aren't problem.I use php and sqlplus with same tables. > > Sqlplus is faster. > > by what amount? May be You could try to use OCISetPrefetch - it is not documented, but there is short note at www.php.net from somebody about it. I can say, it speeds things up very much (if You wanna more than 1 row) Btw. anybody knows why it is not documented (I mean "hidden" in some way) -- ---/\-- Remigiusz Sokolowski e-mail: [EMAIL PROTECTED]/ \ -/\- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: Fwd: Re: [PHP-DB] OCIFetch performance problem with more rows
On Tue, May 22, 2001 at 09:03:45PM +0200, snpe wrote: > > > -- Forwarded Message -- > Subject: Re: [PHP-DB] OCIFetch performance problem with more rows > Date: Tue, 22 May 2001 21:02:42 +0200 > From: snpe <[EMAIL PROTECTED]> > To: "Brian S. Dunworth" <[EMAIL PROTECTED]> > > > On Tuesday 22 May 2001 20:59, you wrote: > > At 08:49 PM 5/22/01 +0200, snpe wrote: > > >Hello, > > > I have performance problem with OCIFetch (and OCIFetchInto, too) > > > when query return more rows.Same query from sqlplus is more fastest (more > > > rows is worse then less rows) > > > >Are the columns you're fetching included in an Oracle index? Without an > > appropriate index, your SQL statement is probably doing a full table scan, > > which can be *very* time-consuming... > > Hello, > Indexes aren't problem.I use php and sqlplus with same tables. > Sqlplus is faster. by what amount? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Fwd: Re: [PHP-DB] OCIFetch performance problem with more rows
-- Forwarded Message -- Subject: Re: [PHP-DB] OCIFetch performance problem with more rows Date: Tue, 22 May 2001 21:02:42 +0200 From: snpe <[EMAIL PROTECTED]> To: "Brian S. Dunworth" <[EMAIL PROTECTED]> On Tuesday 22 May 2001 20:59, you wrote: > At 08:49 PM 5/22/01 +0200, snpe wrote: > >Hello, > > I have performance problem with OCIFetch (and OCIFetchInto, too) > > when query return more rows.Same query from sqlplus is more fastest (more > > rows is worse then less rows) > >Are the columns you're fetching included in an Oracle index? Without an > appropriate index, your SQL statement is probably doing a full table scan, > which can be *very* time-consuming... Hello, Indexes aren't problem.I use php and sqlplus with same tables. Sqlplus is faster. regards --- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
