RE: Question about Dual

> The app server would be calling this query many thousands
> of times each day.

Scary. What I expect for you to see is much network bandwidth consumed = by
what a performance analyst will later call "wasted workload." If you do = the
X$DUAL thing that Ryan suggested, it'll be an incrementally wiser thing = than
querying from DUAL, but something about this scenario you've painted = tells
me that the right advice is to keep trying to find a smarter way to = "clear
your memory" than by making several database calls.

I chose the word "several" very carefully, by the way. The way I see it, = no
matter how you do what you're describing, you're going to be putting = PARSE,
EXEC, and FETCH calls onto your network. There /must/ be a better way to accomplish your functional goal than to do this...

As you try things while you're trying to figure this out, trace them = with
extended SQL trace with DBMS_SUPPORT.START_TRACE(TRUE,TRUE) so that you = can
see the impact that your application development decisions will be inflicting upon your DBMS.

Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
* Nullius in verba *

Upcoming events:
- Performance Diagnosis 101: 9/14 San Francisco, 10/5 Charlotte, 10/26 Toronto

- SQL Optimization 101: 9/20 Hartford, 10/18 New Orleans
- Hotsos Symposium 2005: March 6-10 Dallas
- Visit www.hotsos.com for schedule details...

-----Original Message-----
From: oracle-l-bounce_at_freelists.org =
[mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Mark Moynahan
Sent: Friday, September 10, 2004 11:09 AM To: oracle-l_at_freelists.org
Subject: Question about Dual

Is there any performance benefit selecting from dual in sqlplus as = compared
to selecting dual from a packaged procedure? The reason I ask is that = we
have a cluster of BEA app servers that are having memory issues. The = only
way to clear the memory is to call a very small query. Thus, we came up = with
select 'x' from dual;. We're leaning towards putting the select = statement in
a packaged procedure so we can pin it the shared pool. The app server = would
be calling this query many thousands of times each day.

Thanks,

Mark

--
To unsubscribe - =
mailto:oracle-l-request_at_freelists.org&subject=3Dunsubscribe=20
To search the archives - http://www.freelists.org/archives/oracle-l/

--
To unsubscribe - mailto:oracle-l-request_at_freelists.org&subject=unsubscribe 
To search the archives - http://www.freelists.org/archives/oracle-l/