[DBI-users] SQL Query Question

Michael,

First, it's unlikely this is on-topic for the list either. There are
at least 2 mailing lists, both apparently
still active, which have had good Oracle experts as members, at least
as of a few years ago.

http://www.freelists.org/webpage/oracle-l
http://www.lazydba.com/

I am no longer a DBA and don't subscribe to either right now, so
can't comment on the current quality.
As of 2003 I was partial to oracle-L.

I suppose you might be tickling a DBI or DBD::Oracle bug somewhere
here, but I think you'll be better off
trying to trace it from the Oracle side first - in which case this is
not the right forum.

Second, if you haven't read it, please google "How to stop
defragmenting and start living".
You may have good reasons to watch your extent growth, but in general
it should just not be an
issue for Oracle 8i or higher. But this is also off-topic.

Good luck,
-Chris
<statement snipped>

Chris:

Thanks for your response. The reason I think this might be a DBI/DBD
question is that the SQL works in under 1 second from locally-connected
SQL*Plus, remote-connected SQL*Plus, and TOAD. Only in Perl is it going
away and never returning. I have tried setting SQL_TRACE; no joy. I've
tried setting TraceLevel => 2 (or 4); no joy (lots of information, but no
joy).

Thanks,
Mike

______________________________________________________________________________

Michael P. Vergara Be good and you will be lonesome
Oracle Database Administrator Mark Twain
(951) 914-2000 (Voice)
(951) 914-2990 (FAX)
www.guidant.com

Any views expressed herein are not necessarily those of Guidant Corporation.

Run a 10046 trace to see where you are waiting.
-----Original Message-----
From: Vergara, Michael (TEM)
Sent: Wednesday, November 09, 2005 2:21 PM
To: dbi-users@perl.org
Subject: SQL Query Question


Hello Everyone:

Thanks to all who have responded to my question about logging. That got
me to the problematic SQL. This SQL,
on just one instance, hangs and does not complete. I have let it run
for hours to no avail. When I run this
query at SQL*Plus I get...

'OKAY'
---------------------------------
Okay

1 row selected.
Elapsed: 00:00:00.84

This query runs on all of the other instances without an issue. This is
a 9.2.0.4 database, running on HP-UX. I am
running perl "v5.8.4 built for PA-RISC2.0-LP64", with DBI v1.46 and
DBD::Oracle v1.16.

Here is my code. Help!
#
------------------------------------------------------------------------
-
# DB Extent growth check
#
------------------------------------------------------------------------
-
$SQLstmt =
qq{ select 'Okay' from dual
where not exists
( select 'x' from sys.dba_segments t1, extents_history t2
where (t1.owner = t2.owner) and
(t1.segment_name = t2.segment_name) and
(t1.segment_type = t2.segment_type) and
(t1.segment_type not like '%TEMP%') and
(t1.segment_type not like '%ROLLBACK%') and
(t1.segment_type not like '%UNDO%') and
((t1.extents - t2.extents) > :Threshold) )
union all
select 'Extent Growth Above Threshold (${DbExtGrowthThreshold})'
from dual
where exists
( select 'x' from sys.dba_segments t1, extents_history t2
where (t1.owner = t2.owner) and
(t1.segment_name = t2.segment_name) and
(t1.segment_type = t2.segment_type) and
(t1.segment_type not like '%TEMP%') and
(t1.segment_type not like '%ROLLBACK%') and
(t1.segment_type not like '%UNDO%') and
((t1.extents - t2.extents) > :Threshold) ) };
$SQLh = $DBh->prepare($SQLstmt,{ora_check_sql => 0 });
$SQLh->bind_param( ":Threshold", $DbExtGrowthThreshold );
$SQLh->execute;
$SQLh->bind_col( 1, \$DbSegments );
$SQLh->fetch;
$SQLh->finish;
print STDOUT "DB Extent Growth....: $DbSegments \n";
print LOGF "DB Extent Growth....: $DbSegments \n";
#

Thanks,
Mike
_____

Michael P. Vergara Be good and you will be lonesome
Oracle Database Administrator Mark Twain
(951) 914-2000 (Voice)
(951) 914-2990 (FAX)
www.guidant.com <http://www.guidant.com/>

Any views expressed herein are not necessarily those of Guidant
Corporation.


This electronic message transmission is a PRIVATE communication which contains
information which may be confidential or privileged. The information is intended
to be for the use of the individual or entity named above. If you are not the
intended recipient, please be aware that any disclosure, copying, distribution
or use of the contents of this information is prohibited. Please notify the
sender of the delivery error by replying to this message, or notify us by
telephone (877-633-2436, ext. 0), and then delete it from your system.