FAQ
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.

Search Discussions

  • Chris Sarnowski at Nov 9, 2005 at 9:54 pm
    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
    On Nov 9, 2005, at 4:21 PM, Vergara, Michael ((TEM)) wrote:
    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...
    <statement snipped>
  • Vergara, Michael \(TEM\) at Nov 9, 2005 at 10:04 pm
    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.
  • Reidy, Ron at Nov 10, 2005 at 3:37 pm
    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.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupdbi-users @
categoriesperl
postedNov 9, '05 at 9:21p
activeNov 10, '05 at 3:37p
posts4
users3
websitedbi.perl.org

People

Translate

site design / logo © 2022 Grokbase