Oracle hints

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Peter Teoh - 30 Jan 2008 02:05 GMT

For a one time query, and if I were to make many of these, caching in
the V$SQLAREA is not necessary.

Is there any Oracle hint to allow me to do that?

Thanks.

Reply to this Message

sybrandb - 30 Jan 2008 09:10 GMT

> For a one time query, and if I were to make many of these, caching in
> the V$SQLAREA is not necessary.
>
> Is there any Oracle hint to allow me to do that?
>
> Thanks.

There is no such hint.
Oracle is merciless for people building unscalable applications.
Oracle is also merciless for people not using bind variables.
Please try to learn Oracle.

--
Sybrand Bakker
Senior Oracle DBA

Reply to this Message

Mladen Gogala - 30 Jan 2008 10:43 GMT

> Oracle is also merciless for people not using bind variables.

No it isn't. CURSOR_SHARING=FORCE

Oracle is merciful. Halelujah!

--
Mladen Gogala
http://mgogala.freehostia.com

Reply to this Message

sybrandb - 30 Jan 2008 12:01 GMT

> > Oracle is also merciless for people not using bind variables.
>
[quoted text clipped - 4 lines]
> --
> Mladen Gogalahttp://mgogala.freehostia.com

CURSOR_SHARING=FORCE is a KLUDGE
which can have very negative side effects.

consider the following statement
select substr(name, 1,10) from emp
/

becomes
select substr(name, :b1, :b2) from emp
/

Oracle can no longer determine the length of the resultstring.
I know of at least one application which had problems because of this.

--
Sybrand Bakker
Senior Oracle DBA

Reply to this Message

joel garry - 30 Jan 2008 18:46 GMT

> For a one time query, and if I were to make many of these, caching in
> the V$SQLAREA is not necessary.
>
> Is there any Oracle hint to allow me to do that?
>
> Thanks.

v$sqlarea is just a view. Oracle has to hard parse the new query
anyways. There really isn't anything sensible one could do to avoid
that.

If there are many singular queries, they'll just get aged out fast.
If you are reusing other sql just after it is getting aged out, you
need a bigger shared area anyways.

Sybrand probably understates the issues with cursor_sharing.
http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/memory.htm#sthref648
Look at the bulletins and bugs found when searching for cursor_sharing
on metalink.

I can't always tell if Mladen is joking.

Can't help but think it is a valid wish to be able to tell Oracle
"hey, don't even bother trying to keep this query around. And no
peeking!"

jg
--
@home.com is bogus.
". . . Some day I hope to meet you. When that happens, you'll need a
new nose, a lot of beefsteak for black eyes, and perhaps a supporter
below." - President Harry S Truman

Reply to this Message

bdbafh - 30 Jan 2008 19:01 GMT

> > For a one time query, and if I were to make many of these, caching in
> > the V$SQLAREA is not necessary.
[quoted text clipped - 21 lines]
> "hey, don't even bother trying to keep this query around. And no
> peeking!"

Joel,

do you mean that in addition to the

/*+ cursor_sharing_exact */ hint,

to have a hint along the lines of:

/*+ cursor_sharing_exact_then_flush */

-bdbafh

Reply to this Message

joel garry - 30 Jan 2008 19:18 GMT

> > > For a one time query, and if I were to make many of these, caching in
> > > the V$SQLAREA is not necessary.
[quoted text clipped - 33 lines]
>
> -bdbafh

Not really, since the idea is not to share at all because it is
unique. Hopefully, this could help Oracle not have to go through all
it's checking. I have no idea if this is realistic, on the surface it
seems simple enough.

jg
--
@home.com is bogus
If wishes were horses
beggars would ride
If turnips were watches
I'd wear one by my side.

Reply to this Message

Peter Teoh - 31 Jan 2008 01:52 GMT

Thanks to all for the amazing, refreshing discussion.....got to find
out more....!!!! Thanks.

Reply to this Message

Peter Teoh - 31 Jan 2008 01:56 GMT

Got another idea, instead of completely not storing in V$SQLAREA
cache, is there a way to store it such that it will timeout in the
soonest time possible?

Eg, in a LRU algorithm, if u explicitly queue it at the back, it is
expected to be reused soon, and will take the longest to get flushed
out. But what if u queue it in FRONT? Any HINTs for doing that?

Reply to this Message