RE: Question about Dual
- From: "Cary Millsap" <cary.millsap@xxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 10 Sep 2004 14:22:52 -0500
> 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 - //www.freelists.org/archives/oracle-l/
--
To unsubscribe - mailto:oracle-l-request@xxxxxxxxxxxxx&subject=unsubscribe
To search the archives - //www.freelists.org/archives/oracle-l/
Other related posts:
