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";
#
# 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 |
| Any views expressed herein are not necessarily those of Guidant Corporation. |
