Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Question about Dual

RE: Question about Dual

From: Cary Millsap <>
Date: Fri, 10 Sep 2004 14:22:52 -0500
Message-ID: <005c01c4976b$914835a0$6601a8c0@CVMLAP02>

> 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.
* 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 for schedule details...

-----Original Message-----
From: =
On Behalf Of Mark Moynahan
Sent: Friday, September 10, 2004 11:09 AM To:
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.



To unsubscribe - =
To search the archives -

To unsubscribe - 
To search the archives -
Received on Fri Sep 10 2004 - 14:19:31 CDT

Original text of this message