Re: db cache with IIS application

I have some problems understanding exactly what you are asking, but then your English is a million times better than my italian <G>. However when the Oracle caches become full all that will happen is that the least recently used bit of SQL will be aged out.

Actually a common approach in the web age is to look at the library cache hit ratio, see that it is poor and so increase shared pool size. Whilst this is normally a good thing in the sort of application you describe all that is being done is to increase the amount of parsed queries Oracle has to search through before failing to find a match and then generate a new query plan.

So no you won't get a crash, just poor performance.

--
Niall Litchfield
Oracle DBA
Audit Commission UK
"Jean" <ken_jean_at_hotmail.com> wrote in message
news:9f4tgf$rbl$1_at_serv1.iunet.it...


> Thanks for your reply.

> I say that it's correct what you say.

> I say that using databinding server response is more fast and db is less

> charged.

> But my question is about db cache.

> Is it possible that db crusch when cache become full?

> Using databinding it will take more time but it will also become full and

> the problem will remain.

> Instead I need to say if can be some problems on server that cause db

 crush


> when cache is full.

>

> Thanks in advance.

> Bye

>

> "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message

> news:3b14ed13$0$15030$ed9e5944_at_reading.news.pipex.net...

> > Your DBA is right.

> >

> > If you use

> >

> > strSQL = "SELECT * FROM tblCustomers WHERE ID=" & id

> >

> > then Oracle will have to reparse this query every single time. (assuming

 id


> > has a different value in each case).

> >

> > If you use bind variables , which the second syntax is doing* you will

 only


> > parse once.

> >

> >

> > --

> > Niall Litchfield

> > Oracle DBA

> > Audit Commission UK

> >

> > * although I don't see any population of the bind variable with its

 actual


> > data.

> >

> > "Jean" <ken_jean_at_hotmail.com> wrote in message

> > news:9f2f8a$5cu$1_at_serv1.iunet.it...

> > > Hi to all!

> > > I'm developing a web application based on IIS 5.

> > > I have more asp pages that retrieve data from an Oracle 8i database.

> > >

> > > I developed my ADO code using some recordset and building dinamic

 queries.


> > > For example a query may be

> > > strSQL = "SELECT * FROM tblCustomers WHERE ID=" & id

> > > rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly

> > >

> > > My db administrator told me that this method cause a cache block

 because
 it


> > > must run a new query for every call and can't execute query from

 cache.


> > > So he told me that I must use another method for access data.

> > >

> > > For example I must change my query so:

> > >

> > > strSQL = "SELECT * FROM tblCustomers WHERE ID=?"

> > > set cmd=Server.CreateObject("ADODB.Command")

> > > set cmd.ActiveConnection = cn

> > > cmd.CommandType = adCmdText

> > > cmd.CommandText = strSQL

> > > set rs = cmd.Execute

> > >

> > > Now, I say that this method can improve performance beacuse db server

 can


> > > execute query calling it from cache.

> > > But, is possible that first method can cause cache block?

> > > Normally, db cache queries for a faster call than to execute a new

 query


> > > that there isn't in cache.

> > > But if I call a query no cached, cache may delete last used query and

 insert


> > > new query in cache. If this process work properly cache may be always

 with


> > > same size and db server may haven't problems.

> > > Can anyone tell me what is wright solution for my problem?

> > > Thanks in advance.

> > > Bye

> > >

> > >

> > >

> > >

> > >

> > >

> >

> >

>

>