Re: [PATCH] Binding sql variables in dbpool

Hi Robert,

patch commited to cvs.
Thanks a lot!

On Thursday 11 March 2004 21:26, Robert Galach wrote:
> hi Alex,
>
> > why in hell are you trying to append NULL pointers to the list?
>
> No reason of course, this is a garbage I forgot. It is ugly, but does not
> imply any problems.
> Sorry, I made a mistake and diff'ed  dbpool_oracle. c which was working but
> not clean version of file,
> there is no need to keep bind_list at all, as there is no explicit calls to
> any bindhp now.
>
> Clean patch attached.
>
> > I believe that this patch will get only very very small performance
>
> advantage,
>
> > because binds will get performance advantage only with prepared
> > statements that is not a case in your patch.
>
> Please run this query:
>
> SELECT SQL_TEXT, PARSE_CALLS, EXECUTIONS
>   FROM V$SQLAREA
>   WHERE SQL_TEXT LIKE '%dlr%'
>
> where dlr is the table-field from your config file.
> Notice that DLR queries with bound variables are cached very well and
> parsed _only_once_.
> In my test system it looks like:
>
> DELETE FROM dlr WHERE smsc=:1 AND timestamp=:2 AND destination=:3 AND
> ROWNUM < 2
>   4256    4256
>
> INSERT INTO dlr (smsc, timestamp, src, destination, service, url, mask,
> boxc_id, status)
>   VALUES (:1, :2, :3, :4, :5, :6, :7, :8, 0)
>   4275    4275
>
> SELECT mask, service, url, src, destination, boxc_id FROM dlr
>   WHERE smsc=:1 AND timestamp=:2 AND destination=:3 AND ROWNUM < 2
>   4547    4547
>
> Before this patch, V$SQLAREA was filled with thousands of rows with similar
> statements
> with differences only in literal parameters :( It has no matter in low
> load, dedicated system.
> But it is critical on production systems. Clean sql history is only way to
> trace and optimize
> inefficient queries. It also improve performance because Oracle is able to
> effective use
> cache mechanism and manage cache. You can use smaller SHARED_POOL.
> Less number of unique objects in SHARED_POOL = faster SQL hashing and
> finding.
>
> Regards
> Robert

-- 
Best regards / Mit besten Grüßen aus Düsseldorf

Dipl.-Ing.
Alexander Malysh
___________________________________________

Centrium GmbH
Vogelsanger Weg 80
40470 Düsseldorf

Fon: +49 (0211) 74 84 51 80
Fax: +49 (0211) 277 49 109

email: a.malysh (at) centrium.de
web: www.centrium.de
msn: a.malysh (at) centrium.de
icq: 98063111
___________________________________________

Please avoid sending me Word, Excel or PowerPoint attachments.
See http://www.fsf.org/philosophy/no-word-attachments.html

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (GNU/Linux)

iD8DBQBAXKAknX3e5W+uJ0ERAvDsAJ9028ffi/7pF8EKBmtFDwNC197wRgCg4qZO
k393B1GmjbQcHMXMWE+s0rA=
=ef/q
-----END PGP SIGNATURE-----