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 : adiw@peter.petra.ac.id * Private matter : adiwibowo@excite.com ------------------------------------------