RE: Question about Dual

It has been determined that there is no need to make a call from the app
server to the DB. The temporary solution is to create an empty ejb and call
that so it clears the memory in the app server. We are also working with BEA
for a patch.

Thanks,

Mark

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Cary Millsap
Sent: Friday, September 10, 2004 12:23 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: 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@xxxxxxxxxxxxx =
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Mark Moynahan
Sent: Friday, September 10, 2004 11:09 AM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx&subject=3Dunsubscribe=20
To search the archives - http://www.freelists.org/archives/oracle-l/

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

Other related posts: