Orac-alpha
view release on metacpan or search on metacpan
forum (where you are encouraged to submit suggestions and RFC's) and a
Bug Tracking list.
It is important to check that you are using the latest version before
posting. If you're not then I'm *very* likely to simply say "upgrade to
the latest". You would do yourself a favor by upgrading beforehand.
There is pod documentation for using DDL::Oracle in the Oracle.pm file.
From the .../DDL directory, try 'pod2text Oracle.pm'.
Please remember that I'm (hopefully employed and) busy. Try to help
yourself first, then try to help me help you by following these
guidelines carefully. (Note specifically that I'm unlikely to answer a
question that's answered clearly in the on-line documentation.)
Regards,
Richard Sutherland
__END__
Shell/Meta.pm view on Meta::CPAN
$parent->{dbiwd}->Busy;
my $sth = $parent->{dbh}->table_info;
if ($parent->{dbh}->err) {
warn qq{Table information is not available: } .
$parent->{dbh}->errstr;
} else {
$parent->sth_go( $sth, 1 );
$parent->display_results( $sth );
}
$parent->{dbiwd}->Unbusy;
}
1;
db/orac_Oracle.pm view on Meta::CPAN
$self->selector( \$univ_form_win,
\$screen_type,,
\$screen_title,
\$index_win_cnt,
\@actual_entry,
\$owner,
\$object,
\@ordered_entry,
\@sql_entry,
);
$univ_form_win->Unbusy;
}
)->pack (-side=>'left',
-anchor=>'w');
$balloon->attach($forward_b, -msg => $help_txt );
$self->window_exit_button( \$bb, \$univ_form_win, 1, \$balloon, );
main::iconize( $univ_form_win );
if ($need_focus)
db/orac_Oracle.pm view on Meta::CPAN
-fill=>'both');
main::iconize( $window );
}
$window->{text}->insert('end', @res);
}
$sth->finish;
if($detected == 0){
$self->{Main_window}->Busy(-recurse=>1);
main::mes($self->{Main_window},$main::lg{no_rows_found});
$self->{Main_window}->Unbusy;
} else {
$window->{text}->pack();
$window->{text}->bind(
'<Double-1>',
sub{ $window->Busy(-recurse=>1);
$self->selected_error(
$window->{text}->get('active')
);
$window->Unbusy}
);
}
}
=head2 dbas_orac
Creates DBA Viewer window, for selecting various DBA_XXXX tables,
which can then be selected upon.
=cut
db/orac_Oracle.pm view on Meta::CPAN
)->pack(-expand=>'yes',-fill=>'both');
main::iconize($window);
}
$window->{text}->insert('end', @res);
}
$sth->finish;
if($detected == 0){
$self->{Main_window}->Busy(-recurse=>1);
main::mes($self->{Main_window},$main::lg{no_rows_found});
$self->{Main_window}->Unbusy;
} else {
$window->{text}->pack();
$window->{text}->bind(
'<Double-1>',
sub{
$window->Busy(-recurse=>1);
$self->{Main_window}->Busy(-recurse=>1);
$self->univ_form( 'SYS',
$window->{text}->get('active'),
'form'
);
$self->{Main_window}->Unbusy;
$window->Unbusy;
}
);
}
}
=head2 addr_orac
Produces a list of all the PADDR addresses in the database, to
help a DBA examine what's running. Useful info for deciding
db/orac_Oracle.pm view on Meta::CPAN
main::iconize($window);
}
$window->{text}->insert('end', @res);
}
$sth->finish;
if($detected == 0){
$self->{Main_window}->Busy(-recurse=>1);
main::mes($self->{Main_window},$main::lg{no_rows_found});
$self->{Main_window}->Unbusy;
} else {
$window->{text}->pack();
$window->{text}->bind(
'<Double-1>',
sub{
my $loc_addr = $window->{text}->get('active');
db/orac_Oracle.pm view on Meta::CPAN
)->pack(-expand=>'yes',-fill=>'both');
main::iconize($window);
}
$window->{text}->insert('end', @res);
}
$sth->finish;
if($detected == 0){
$self->{Main_window}->Busy(-recurse=>1);
main::mes($self->{Main_window},$main::lg{no_rows_found});
$self->{Main_window}->Unbusy;
} else {
$window->{text}->pack();
$window->{text}->bind(
'<Double-1>',
sub { $window->Busy(-recurse=>1);
$self->f_clr( $main::v_clr );
# 5 jan 2000, Andre Seesink <Andre.Seesink@CreXX.nl>
# Now we get sid and username
my ($sid, $username) = split(' ',$window->{text}->get('active'));
$self->show_sql( 'sel_sid' , '1',
$main::lg{sel_sid} . ': ' . $sid,
$sid );
$window->Unbusy
}
);
}
}
=head2 gh_roll_name
Produces Rollback report.
=cut
db/orac_Oracle.pm view on Meta::CPAN
my $clr_b = $dmb->Button( -image=>$img,
-command=>sub{
$window->Busy(-recurse=>1);
$w_explain[2]->delete('1.0','end');
$w_holders[0] = $main::v_sys;
$w_holders[1] = $main::lg{explain_help};
$expl_butt->configure(-state=>'normal');
$window->Unbusy;
}
)->pack(side=>'left');
$balloon->attach($clr_b, -msg => $main::lg{clear} );
}
$self->window_exit_button(\$dmb, \$window, 1, \$balloon, );
# Set counter up
my $i;
db/orac_Oracle.pm view on Meta::CPAN
my @v_osuser = split('\:', $first_string[1]);
my @v_username = split('\:', $first_string[0]);
my @v_sid = split('\:', $first_string[2]);
$self->who_what( 1,
$v_osuser[1],
$v_username[1],
$v_sid[1]
);
$self->{Main_window}->Unbusy
}
);
$self->{Text_var}->insert('end', "\n");
}
# And finally, thank goodness, the actual report.
$self->show_sql( 'wait_hold' , '1',
$main::lg{who_hold} );
}
db/orac_Oracle.pm view on Meta::CPAN
$scroll_box->bind(
'<Double-1>',
sub{ $self->{Main_window}->Busy(-recurse=>1);
my @stat_str = split('\:', $scroll_box->get('active') );
$self->who_what( 2,
$stat_str[1],
"${l_stat_title}:$stat_str[1]",
);
$self->{Main_window}->Unbusy
}
);
$self->{Text_var}->insert('end', "\n");
}
$self->show_sql( 'sess_curr_max_mem' , '1',
$main::lg{mts_mem} );
}
db/orac_Oracle.pm view on Meta::CPAN
$final_txt =
$final_txt .
sprintf( "%5d: %s",
$line_counter,
$lines_of_txt[($line_counter - 1)]
);
$line_counter++;
}
$self->see_sql($window,$final_txt,$label_text);
$window->Unbusy;
}
)->pack(-side=>'left');
$balloon->attach($b, -msg => $main::lg{lines} );
if ( ($l_hlst eq 'Tables') || ($l_hlst eq 'Indexes') ){
if ($l_hlst eq 'Tables') {
my $b = $menu_bar->Button(-image=>$b_images{form},
-command=> sub{
$window->Busy(-recurse=>1);
$self->univ_form($owner,
$generic,
'form');
$window->Unbusy
}
)->pack(-side=>'left');
$balloon->attach($b, -msg => $main::lg{form});
$b = $menu_bar->Button( -image=>$b_images{sizeindex},
-command=> sub {
$window->Busy(-recurse=>1);
$self->univ_form($owner,
$generic,
'index'
);
$window->Unbusy
}
)->pack(-side=>'left');
$balloon->attach($b, -msg => $main::lg{sizeindex});
}
my @tablist;
my @tablist_2;
db/orac_Oracle.pm view on Meta::CPAN
} elsif ($l_hlst eq 'Views'){
my $b = $menu_bar->Button(
-image=>$b_images{form},
-command=>sub{ $window->Busy(-recurse=>1);
$self->univ_form( $owner,
$generic,
'form'
);
$window->Unbusy }
)->pack(-side=>'left');
$balloon->attach($b, -msg => $main::lg{form});
}
$self->window_exit_button(\$menu_bar, \$window, 1, \$balloon, );
main::iconize( $window );
$l_mw->Unbusy;
}
=head2 tab_det_orac
Produces simple graphical representations of complex percentage style reports.
=cut
sub tab_det_orac {
db/orac_Oracle.pm view on Meta::CPAN
$eraser = $window->Photo(-file=>"$FindBin::RealBin/img/eraser.gif");
$b = $dev_menu->Button(-image=>$eraser,
-command=>sub{
$window->Busy(-recurse=>1);
$self->{Main_window}->Busy(-recurse=>1);
$text->delete('1.0','end');
$self->{Main_window}->Unbusy;
$window->Unbusy;
}
)->pack(side=>'right');
$balloon->attach($b, -msg => $main::lg{clear});
my $dev_2_menu;
my $balloon2;
$self->create_balloon_bars(\$dev_2_menu, \$balloon2, \$window, );
db/orac_Oracle.pm view on Meta::CPAN
main::iconize($window);
}
$window->{text}->insert('end', @res);
}
$sth->finish;
if($detected == 0){
$self->{Main_window}->Busy(-recurse=>1);
main::mes($self->{Main_window},$main::lg{no_rows_found});
$self->{Main_window}->Unbusy;
} else {
$window->{text}->selectionSet(0);
$window->{text}->pack();
$window->{text}->bind(
'<Double-1>',
sub{
$window->Busy(-recurse=>1);
$self->{Main_window}->Busy(-recurse=>1);
db/orac_Oracle.pm view on Meta::CPAN
# What is the current mark?
$current_index = $text->index('current');
$text->insert('end', $sql . "\n\n");
$self->search_text(\$text, $current_index);
$text->see( q{end linestart});
$self->{Main_window}->Unbusy;
$window->Unbusy;
}
);
}
}
=head2 dev_jpeg_tunen
Creates various tuning pies and inserts them into a pop-up screen.
=cut
db/orac_Oracle.pm view on Meta::CPAN
$flip_switch = 0;
} else {
$flip_switch = 1;
}
}
$sth->finish;
if($detected == 0){
$self->{Main_window}->Busy(-recurse=>1);
main::mes($self->{Main_window},$main::lg{no_rows_found});
$self->{Main_window}->Unbusy;
} else {
$csth->finish;
$rsth = $dbh->prepare(
'SELECT BARCHART FROM bars ' .
'WHERE WIDTH=700 AND HEIGHT=300 ' .
'AND X-AXIS=\'' . $x_axis .
'\' AND Y-AXIS=\'' . $y_axis . '\' AND ' .
'X-ORIENT=\'VERTICAL\' AND ' .
'FORMAT=\'JPEG\' AND ' .
'TITLE = \'' . $title_element .
db/orac_Oracle.pm view on Meta::CPAN
)->pack(-expand=>'yes',-fill=>'both');
main::iconize($window);
}
$window->{text}->insert('end', @res);
}
$sth->finish;
if($detected == 0){
$self->{Main_window}->Busy(-recurse=>1);
main::mes($self->{Main_window},$main::lg{no_rows_found});
$self->{Main_window}->Unbusy;
} else {
$window->{text}->pack();
$window->{text}->bind(
'<Double-1>',
sub{
$window->Busy(-recurse=>1);
$self->{Main_window}->Busy(-recurse=>1);
db/orac_Oracle.pm view on Meta::CPAN
if ( $action eq "totalspace" ){
print "totalspace\n";
}
elsif ( $action eq "tables" ){
print "tables\n";
}
elsif ( $action eq "indexes" ){
print "indexes\n";
}
$self->{Main_window}->Unbusy;
$window->Unbusy;
}
);
}
}
sub search_text {
my ($self, $t, $curr) = @_;
my @blue_bits = ( 'ADD_MONTHS', 'ALTER', 'AND', 'AS', 'ASCII',
'AVG', 'BEGIN', 'BIT_LENGTH', 'BLOCK', 'BODY',
db/orac_Oracle.pm view on Meta::CPAN
$self->dev_jpeg_tabsp(\$canvas,
\$canvas_id,
'EXTENTID',
$datafile
);
$canvas->configure(
-scrollregion=>[ $canvas->bbox("all") ]);
$canvas->pack(-expand=>'yes',-fill=>'both');
$self->{Main_window}->Unbusy;
$window->Unbusy;
}
},
-width=> 80,
-height=> 20,
-font=>$main::font{name},
-foreground=> $main::fc,
-background=> $main::bc,
-command=> sub {
$self->show_or_hide_tabsp( \$text2,
db/orac_Oracle.pm view on Meta::CPAN
# $generic || $owner,
# ]
# ],
# );
#$text_lines = $obj->create ;
# Finally, pump out the monkey
$$text2_ref->insert('end', "$owner $generic");
$l_mw->Unbusy;
}
=head2 post_tabsp
This subroutine is called with the results from show_sql() to allow DB
modules to "post process" the output, if required, before it is analyzed
to be shown.
This is useful for turning numeric flags into words, and other such DB
dependent things.
This generic one does NOTHING!
db/orac_Oracle.pm view on Meta::CPAN
$flip_switch = 0;
} else {
$flip_switch = 1;
}
}
$sth->finish;
if($detected == 0){
$self->{Main_window}->Busy(-recurse=>1);
main::mes($self->{Main_window},$main::lg{no_rows_found});
$self->{Main_window}->Unbusy;
} else {
$csth->finish;
if ($chart_width < 700){
$chart_width = 700;
}
$rsth = $dbh->prepare(
'SELECT BARCHART FROM bars ' .
'WHERE WIDTH=' . $chart_width . ' AND HEIGHT=300 ' .
db/orac_Sybase.pm view on Meta::CPAN
($owner, $generic, $dum) = split("\\$l_gen_sep", $input);
my $loc_g_hlst;
my $cm = $self->f_str($l_hlst ,'99');
if ($l_hlst eq 'Segments' || $l_hlst eq 'All Objects') {
$self->f_clr( $main::v_clr );
$self->{Database_conn}->do("use $owner");
my $reportHeader = ($l_hlst eq 'Segments') ? "Segment Allocation" : "All Objects in $owner";
$self->show_sql($l_hlst, '99', $reportHeader, $generic, $owner);
$l_mw->Unbusy;
return;
} else {
$cm = ($l_hlst eq 'Groups') ? sprintf($cm, $generic, $generic) : sprintf($cm, $generic);
}
my $second_sth = $self->{Database_conn}->prepare( $cm ) ||
die $self->{Database_conn}->errstr;
# Deal with SQL print returns through the global message handler
$main::conn_comm_flag = 999;
$second_sth->execute;
db/orac_Sybase.pm view on Meta::CPAN
print L_TEXT " ";
$i++;
}
print L_TEXT "\n\n ";
$b = $menu_bar->Button(-image=>$b_images{form},
-command=>
sub{$window->Busy(-recurse=>1);
$self->univ_form($window,$owner,$generic,'form');
$window->Unbusy }
)->pack(-side=>'left');
$balloon->attach($b, -msg => $main::lg{form});
$i++;
$b = $menu_bar->Button(-image=>$b_images{sizeindex},
-command=> sub{$window->Busy(-recurse=>1);
$self->univ_form($window,$owner,$generic,'index');
$window->Unbusy }
)->pack(-side=>'left');
$balloon->attach($b, -msg => $main::lg{build_index});
print L_TEXT " ";
} elsif($l_hlst eq 'Procedures' || $l_hlst eq 'Triggers') {
$window->{ed_button} = $menu_bar->Button(-image=>$b_images{form},
-command=>sub{
$window->{text}->configure(-state=>'normal');
$window->{rc_button}->configure(-state=>'normal');
$window->{ed_button}->configure(-state=>'disabled');
}
)->pack(-side=>'left');
$balloon->attach($window->{ed_button}, -msg => 'Edit');
$window->{rc_button} = $menu_bar->Button(-image=>$b_images{sizeindex},
-command=> sub{$window->Busy(-recurse=>1);
$window->{text}->configure(-state=>'disabled');
$window->{rc_button}->configure(-state=>'disabled');
$window->{ed_button}->configure(-state=>'normal');
$self->change_sql($window,$generic, $l_hlst);
$window->Unbusy },
-state=>'disabled'
)->pack(-side=>'left');
$balloon->attach($window->{rc_button}, -msg => 'Recompile');
} elsif ($l_hlst eq 'Views'){
print L_TEXT "\n\n ";
$b = menu_bar->Button(-text=>$main::lg{form},
-command=>sub{$window->Busy(-recurse=>1);
$self->univ_form($window,$owner,$generic,'form');
$window->Unbusy }
)->pack(-side=>'left');
$balloon->attach($b, -msg => $main::lg{form});
}
print L_TEXT "\n\n";
$self->window_exit_button(\$menu_bar, \$window );
$window->{text}->configure(-state=>'disabled');
main::iconize( $window );
$l_mw->Unbusy;
}
sub change_sql {
my $self = shift;
my ($loc_d,$obj,$l_hlst) = @_;
chop $l_hlst;
my $sp_text = $loc_d->{text}->get("1.0", "end");
my $drop_sql = qq{ drop $l_hlst $obj };
db/orac_Sybase.pm view on Meta::CPAN
my $expl_butt = $dmb->Button(-text=>$main::lg{explain},
-command=>sub{ $self->explain_it();}
)->pack(side=>'left');
$dmb->Button(-text=>$main::lg{clear},
-command=>sub{
$window->Busy(-recurse=>1);
$sql_txt->delete('1.0','end');
my $w_user_name = $main::v_sys;
$expl_butt->configure(-state=>'normal');
$window->Unbusy;
}
)->pack(side=>'left');
$dmb->Button(-text=>$main::lg{exit},
-command=> sub{
$window->destroy();
$window->Busy(-recurse=>1);
my $cm = $self->f_str('explain_plan','3');
$self->{Database_conn}->do($cm);
$cm = $self->f_str('explain_plan','4');
$self->{Database_conn}->do($cm);
$window->Unbusy;
undef $main::conn_comm_flag;
}
)->pack(-side=>'left');
$dmb->Label(-text=>" Use ",-borderwidth=>2,-relief=>'flat')->pack(-side=>'left',-anchor=>'w');
# need to get a db list for dropdown
my $sth;
my $cm = "select db_name()";
my @list = ();
db/orac_Sybase.pm view on Meta::CPAN
-foreground=>$main::fc,
-background=>$main::ec)->pack();
my $l3 = $d->Label(-text=>"Seconds Since Last Run:", justify=>"left");
my $ps3 = $d->add("Entry",-cursor=>undef,
-textvariable=>\$data[2],
-state=>'disabled',
-foreground=>$main::fc,
-background=>$main::ec)->pack();
my $l4 = $d->Label(-text=>"CPU busy (sec):", justify=>"right");
my $ps4 = $d->add("Entry",-cursor=>undef,
-textvariable=>\$data[3],
-state=>'disabled',
-foreground=>$main::fc,
-background=>$main::ec)->pack();
my $l5 = $d->Label(-text=>"IO Busy:", justify=>"left");
my $ps5 = $d->add("Entry",-cursor=>undef,
-textvariable=>\$data[4],
-state=>'disabled',
db/orac_Sybase.pm view on Meta::CPAN
if ($uf_type eq 'index'){
$uf_txt = 'Build Index';
} else {
$uf_txt = $main::lg{sel_info};
}
$bb->Button( -text=>$uf_txt,
-command=>sub{ $bd->Busy(-recurse=>1);
$self->selector($bd,$uf_type);
$bd->Unbusy}
)->pack (-side=>'right',
-anchor=>'e');
$bd->Show;
}
sub selector {
my $self = shift;
help/DBATabspace.txt view on Meta::CPAN
datafiles. Select either the tablespace or a datafile and it would open two
panels on the right 2/3 of the window. The top half was divided into two
parts. On the left was a summary of the currently selected segment and
extent. It listed the size, in blocks, of the extent and the segment. On the
right of that summary was a list of segments with segment type, number of
extents and size. Below, in the lower right quandrant was a graphic display.
The graphic used three colors. Each extent had a fine black line border. One
color for unallocated space, one for the currently selected segment and all
its extents, and a third for allocated, but not selected space. The extents
were rectangles arranged in horizontal stripes. The extent rectangles were
scaled to fit the window. As a result a busy tablespace might have so little
space for each extent that the extents ran together to create a mass of
black border stripes. The new version of the tablespace map, now
incorporated with the storage manager, scrolls but I think the scale is
fixed. That makes it difficult to see the big picture. Not sure which I
prefer.
I may be able to install the old version to get you a screen shot of the
earlier tablespace manager. I'll have to remove all of Oracle from my PC to
install the old version. It's from single NT ORACLE_HOME days and won't
install if you have a later installation already on the machine. I'm
orac_Base.pm view on Meta::CPAN
{
$use_img = 0;
}
my $b = $$menu_ref->Button(
-command=> sub{
$$win_ref->Busy(-recurse=>1);
$self->see_sql($$win_ref,$$cm_ref);
$$win_ref->Unbusy;
}
)->pack(-side=>'left');
if ($use_img)
{
my $img;
$self->get_img( $win_ref, \$img, 'sql' );
orac_FileSelect.pm view on Meta::CPAN
# action.
main::mes($self->{window},
qq{Cannot change to the directory } .
$self->{selectPath} . qq{\. \nPermission denied?}
);
return;
}
# Turn on the busy cursor.
$self->{window}->Busy(-recurse=>1);
$self->{window}->idletasks;
$self->{window}->{text}->DeleteAll;
# Make the dir list
my %hasDoneDir;
orac_FileSelect.pm view on Meta::CPAN
}
else
{
$image = $fileImage;
}
$self->{window}->{text}->Add($image, $ffile);
$hasDoneFile{$ffile}++;
}
}
$self->{window}->{text}->Arrange;
$self->{window}->Unbusy;
}
1;
orac_Monitor.pm view on Meta::CPAN
$$mon_text_ref = 'Preparing for Launch...';
$$stop_ref = 1;
# Run some checking Baby!
$self->{mon_win}->Busy(-recurse=>1);
$self->check_the_monitor;
$self->{mon_win}->Unbusy;
while($$stop_ref)
{
select(undef, undef, undef, $countdown_amount);
$$countdown_ref = $$countdown_ref - $countdown_amount;
my $countdown_bit = sprintf("%5.2f", $$countdown_ref);
$$mon_text_ref = 'T-minus ' . $countdown_bit . ' (secs)';
orac_Monitor.pm view on Meta::CPAN
}
if (($$stop_ref) && ($$countdown_ref <= 0.05)) # $countdown_amount/2 ?
{
$$mon_text_ref = 'Initialising...';
# Lock out the screen, then Launch, Launch, Launch!!!
$self->{mon_win}->Busy;
$self->check_the_monitor;
$self->{mon_win}->Unbusy;
if ( Tk::Exists($$prog_bar_ref) )
{
$$prog_bar_ref->update();
}
else
{
last;
}
orac_Shell.pm view on Meta::CPAN
# now execute it;
$self->execute_sql();
# Remove the exec tag
$self->untag_entry( q{Exec} );
# Deal with results.
$self->handle_results;
# Remove the busy pointer.
$self->dbiwd->Unbusy;
return;
}
#
# Display a dialog box to the user. Using this method until
# we create a dialog handler in the core function.
#
sub message_dialog {
my ($self, $title, $msg) = @_;
orac_dba.pl view on Meta::CPAN
main::del_Jareds_tools(\$main::jareds_tool);
main::config_menu();
main::Jareds_tools();
$main::orac_orig_db = $main::orac_curr_db_typ;
}
}
=head2 bz
Makes the main GUI pointer go busy.
=cut
sub bz {
# Make the main GUI pointer go busy
$main::mw->Busy(-recurse=>1);
}
=head2 ubz
Makes the main GUI pointer go Un-busy.
=cut
sub ubz {
# Make the main GUI pointer normalise to unbusy
$main::mw->Unbusy;
}
sub mes {
# Produce the box that contains viewable Error
my $d = $_[0]->DialogBox();
my $displayer;
if (length($_[1]) > 200)
sql/Oracle/dispatch_stuff.1.sql view on Meta::CPAN
select Network,
round((SUM(busy) / (SUM(busy) + SUM(idle))),20) "Busy_Rate"
from v$dispatcher
GROUP by network
sql/Oracle/gh_summ_sess.1.sql view on Meta::CPAN
select 'log file space/switch',sum(time_waited)/100
from v$system_event
where event like 'log file space/switch'
union
select 'latch waits',sum(time_waited)/100
from v$system_event
where event like 'latch free'
union
select 'Buffer waits',sum(time_waited)/100
from v$system_event
where event in ('write complete waits','free buffer waits','buffer busy waits')
union
select 'SQL*Net waits (inc remote SQL)',sum(time_waited)/100
from v$system_event
where event like 'SQL*Net%'
and event !='SQL*Net message from client'
union
select 'lock waits',sum(time_waited)/100
from v$system_event
where event = 'enqueue'
union
sql/Oracle/gh_summ_sess.1.sql view on Meta::CPAN
union
select 'Other waits (non-idle)',sum(time_waited)/100
from v$system_event
where event not in ('Null event','client message','smon timer',
'rdbms ipc message','pmon timer','WMON goes to sleep',
'virtual circuit status','dispatcher timer',
'SQL*Net message from client',
'parallel query dequeue wait','latch free',
'enqueue','write complete waits',
'free buffer waits',
'buffer busy waits','pipe gets',
'Null event','client message','smon timer',
'rdbms ipc message','pmon timer','WMON goes to sleep',
'virtual circuit status','dispatcher timer',
'SQL*Net message from client',
'parallel query dequeue wait','latch free',
'enqueue','write complete waits',
'free buffer waits',
'buffer busy waits','pipe gets','slave wait','PL/SQL lock timer',
'null event','Null event','rdbms ipc reply',
'Parallel Query Idle Wait - Slaves',
'KXFX: Execution Message Dequeue - Slave','slave wait')
and event not like 'db file%'
and event not like 'log file%'
and event not like 'SQL*Net%'
order by 1
sql/Sybase/quickstats.10 view on Meta::CPAN
where cntrltype=0
and status & 64 = 64
and status & 512 != 512)
begin
select @mirror_name='Broken'
end
else select @mirror_name='Ok'
declare @lc float, @li float, @lidle float, @ldate datetime
select @ldate=max(date) from sybsystemprocs..record
select @lc = busy, @li =io, @lidle =idle
from sybsystemprocs..record
where date=@ldate
declare @ms_per_tick float
select @ms_per_tick = convert(int,@@timeticks/1000)
/* numbers here are scaled to give percents */
select
@lc = ( @@cpu_busy * @ms_per_tick) / 1000 - @lc,
@li = ( @@io_busy * @ms_per_tick) / 1000 - @li,
@lidle = ( @@idle * @ms_per_tick) / 1000 - @lidle
declare @sumtimes float
select @sumtimes = @lc + @li +@lidle
if @starttime is not null
exec sp__datediff @starttime,'m',@datestmp output
else select @datestmp=@sumtimes/60.0
if @sumtimes=0
sql/Sybase/quickstats.10 view on Meta::CPAN
if @noconvert is not null
begin
select
blocks=@blk,
conn=@conn,
ctime=datediff(ms,@time1,getdate()),
locks=@locks,
run=@runnable,
tlock=@tlock,
str(convert(float,(100*@lc))/@sumtimes,5,2) "%busy",
str(convert(float,(100*@li))/@sumtimes,5,2) "%io ",
str(convert(float,(100*@lidle))/@sumtimes,5,2) "%idle",
"mirror"=@mirror_name,
mins=@datestmp
end
else
begin
select
blks=convert(char(4),@blk),
sql/Sybase/quickstats.492 view on Meta::CPAN
if @noconvert is not null
begin
select
blocks=@blk,
conn=@conn,
ctime=datediff(ms,@time1,getdate()),
locks=@locks,
run=@runnable,
tlock=@tlock,
busy=@@cpu_busy,
io=@@io_busy,
idle=@@idle,
"mirror"=@mirror_name,
mins=@datestmp
end
else
begin
select
blks=convert(char(4),@blk),
conn=convert(char(4),@conn),
ctime=convert(char(6),datediff(ms,@time1,getdate())),
locks=convert(char(5),@locks),
run=convert(char(4),@runnable),
tlock=convert(char(5),@tlock),
cpu=convert(char(9),@@cpu_busy),
io=convert(char(6),@@io_busy),
idle=convert(char(9),@@idle),
/* "mirror"=@mirror_name, */
minutes=ltrim(str(@datestmp,10,1))
end
go
grant execute on sp__quickstats to public
go
sql/Sybase/record.10 view on Meta::CPAN
where name = "record"
and type = "U")
begin
exec sp_rename record,record_backup
end
else
begin
create table record_backup
(
date datetime,
busy int ,
io int ,
idle int ,
connections int ,
packet_in int ,
packet_out int ,
total_read int ,
total_write int ,
total_errors int ,
description char(30)
)
sql/Sybase/record.10 view on Meta::CPAN
if exists (select * from sysobjects
where name = "sp__show_record"
and type = "P")
drop proc sp__show_record
go
/*---------------------------------------------------------------------------*/
create table record
(
date datetime,
busy int ,
io int ,
idle int ,
connections int ,
packet_in int ,
packet_out int ,
total_read int ,
total_write int ,
total_errors int ,
description char(30)
)
sql/Sybase/record.10 view on Meta::CPAN
AS
BEGIN
declare @dt datetime, @b int, @i int, @id int, @c int
,@pi int,@po int,@tr int,@tw int,@te int
declare @l_dt datetime, @l_b int, @l_i int, @l_id int, @l_c int
,@l_pi int,@l_po int,@l_tr int,@l_tw int,@l_te int
create table #results (
dt datetime,
busy int,
io int,
idle int,
conn int,
pin int ,
pout int ,
rd int ,
wr int ,
err int ,
notes char(10))
sql/Sybase/record.10 view on Meta::CPAN
from record
where description=@desc
and datediff(dd,date,getdate())<@ndays ) <= 1
begin
print "Insufficient Records To Make Report"
return
end
declare record_results cursor for
select date,
busy,
io,
idle,
connections ,
packet_in,packet_out,total_read,total_write,total_errors
from record
where description=@desc
and datediff(dd,date,getdate())<@ndays
order by date
open record_results
sql/Sybase/record.10 view on Meta::CPAN
select @l_b=@b, @l_i=@i, @l_id=@id, @l_c=@c
,@l_pi=@pi , @l_po=@po , @l_tr=@tr , @l_tw=@tw , @l_te=@te
end
close record_results
deallocate cursor record_results
if @print_long is null
select date=convert(char(8),dt,1),
str(convert(float,(100*busy))/(busy+io+idle),5,2) "%busy",
str(convert(float,(100*io)) /(busy+io+idle),5,2) "%io ",
str(convert(float,(100*idle))/(busy+io+idle),5,2) "%idle",
connections=conn ,notes
from #results
order by date
else
select date=convert(char(8),dt,1),
str(convert(float,(100*busy))/(busy+io+idle),5,2) "%busy",
str(convert(float,(100*io)) /(busy+io+idle),5,2) "%io ",
str(convert(float,(100*idle))/(busy+io+idle),5,2) "%idle",
conn ,
pin ,
pout ,
rd ,
wr ,
err ,
notes
from #results
order by date
END
go
/* Inserttype = 1 then 1 row per day */
/* = 2 then 2 rows period */
create proc sp__add_record( @desc char(30)="Daily Record", @inserttype int=1,
@dont_format char(1) = null
)
AS
BEGIN
declare @users int, @runnable int, @busy int, @io int,
@idle int, @connections int, @packet_in int, @packet_out int,
@total_read int, @total_write int, @total_errors int, @now datetime,
@curdate datetime
if @inserttype=1
begin
select @curdate=convert(datetime,convert(char(8),getdate(),1))
delete sybsystemprocs..record
where date = @curdate
sql/Sybase/record.10 view on Meta::CPAN
and date <> @now
end
delete sybsystemprocs..record
where datediff(dd,date,getdate())>90
and description=@desc
exec sp__stat2
@users output,
@runnable output,
@busy output,
@io output,
@idle output,
@connections output,
@packet_in output,
@packet_out output,
@total_read output,
@total_write output,
@total_errors output,
@now output
insert sybsystemprocs..record
select
@curdate,
@busy ,
@io ,
@idle ,
@connections ,
@packet_in ,
@packet_out ,
@total_read ,
@total_write ,
@total_errors ,
@desc
sql/Sybase/record.10 view on Meta::CPAN
END
go
print "THE NEXT PROCEDURE MIGHT PRINT AN ERROR 2007 (sysdepends) - Its Recrusive!"
go
create proc sp__monitor( @desc char(30)="Daily Record",
@dont_format char(1) = null
)
AS
BEGIN
declare @cur_time datetime, @users int, @runnable int, @busy int, @io int,
@idle int, @connections int, @packet_in int, @packet_out int,
@total_read int, @total_write int, @total_errors int, @now datetime
declare @l_time datetime, @l_busy int, @l_io int,
@l_idle int, @l_connections int, @l_packet_in int, @l_packet_out int,
@l_total_read int, @l_total_write int, @l_total_errors int
select
@l_time=date,
@l_busy=busy,
@l_io=io ,
@l_idle=idle,
@l_connections=connections,
@l_packet_in=packet_in,
@l_packet_out=packet_out,
@l_total_read=total_read,
@l_total_write=total_write,
@l_total_errors=total_errors
from record
where date = convert(datetime,convert(char(8),getdate(),1))
sql/Sybase/record.10 view on Meta::CPAN
exec sp__add_record
waitfor delay '00:00:05'
exec sp__monitor
return
end
/* Current Stats */
exec sp__stat2
@users output,
@runnable output,
@busy output,
@io output,
@idle output,
@connections output,
@packet_in output,
@packet_out output,
@total_read output,
@total_write output,
@total_errors output,
@cur_time output
/* print some info */
declare @secs int
select @secs = datediff(ss,@l_time,@cur_time)
create table #r ( text varchar(127) )
insert #r select "Number Of Users ="+convert(varchar(8),@users)
insert #r select "Seconds Since Last Run ="+convert(varchar(8),@secs)
insert #r select "Cpu Busy ="+convert(char(4),@busy-@l_busy)
insert #r select " Io Busy ="+convert(char(4),@io-@l_io)
select * from #r
/* save some stats for next time */
delete sybsystemprocs..record
where date = convert(datetime,convert(char(8),getdate(),1))
and description=@desc
insert sybsystemprocs..record
select
convert(datetime,convert(char(8),getdate(),1)),
@busy ,
@io ,
@idle ,
@connections ,
@packet_in ,
@packet_out ,
@total_read ,
@total_write ,
@total_errors ,
@desc
END
sql/Sybase/record.492 view on Meta::CPAN
where name = "record"
and type = "U")
begin
exec sp_rename record,record_backup
end
else
begin
create table record_backup
(
date datetime,
busy int ,
io int ,
idle int ,
connections int ,
packet_in int ,
packet_out int ,
total_read int ,
total_write int ,
total_errors int ,
description char(30)
)
sql/Sybase/record.492 view on Meta::CPAN
if exists (select * from sysobjects
where name = "sp__show_record"
and type = "P")
drop proc sp__show_record
go
/*---------------------------------------------------------------------------*/
create table record
(
date datetime,
busy int ,
io int ,
idle int ,
connections int ,
packet_in int ,
packet_out int ,
total_read int ,
total_write int ,
total_errors int ,
description char(30)
)
sql/Sybase/record.492 view on Meta::CPAN
AS
BEGIN
declare @dt datetime, @b int, @i int, @id int, @c int
,@pi int,@po int,@tr int,@tw int,@te int
declare @l_dt datetime, @l_b int, @l_i int, @l_id int, @l_c int
,@l_pi int,@l_po int,@l_tr int,@l_tw int,@l_te int
create table #results (
dt datetime,
busy int,
io int,
idle int,
conn int,
pin int ,
pout int ,
rd int ,
wr int ,
err int ,
notes char(10))
sql/Sybase/record.492 view on Meta::CPAN
END
go
/* Inserttype = 1 then 1 row per day */
/* = 2 then 2 rows period */
create proc sp__add_record( @desc char(30)="Daily Record", @inserttype int=1,
@dont_format char(1) = null
)
AS
BEGIN
declare @users int, @runnable int, @busy int, @io int,
@idle int, @connections int, @packet_in int, @packet_out int,
@total_read int, @total_write int, @total_errors int, @now datetime,
@curdate datetime
if @inserttype=1
begin
select @curdate=convert(datetime,convert(char(8),getdate(),1))
delete master..record
where date = @curdate
sql/Sybase/record.492 view on Meta::CPAN
and date <> @now
end
delete master..record
where datediff(dd,date,getdate())>90
and description=@desc
exec sp__stat2
@users output,
@runnable output,
@busy output,
@io output,
@idle output,
@connections output,
@packet_in output,
@packet_out output,
@total_read output,
@total_write output,
@total_errors output,
@now output
insert master..record
select
@curdate,
@busy ,
@io ,
@idle ,
@connections ,
@packet_in ,
@packet_out ,
@total_read ,
@total_write ,
@total_errors ,
@desc
sql/Sybase/record.492 view on Meta::CPAN
END
go
print "THE NEXT PROCEDURE MIGHT PRINT AN ERROR 2007 (sysdepends) - Its Recrusive!"
go
create proc sp__monitor( @desc char(30)="Daily Record",
@dont_format char(1) = null
)
AS
BEGIN
declare @cur_time datetime, @users int, @runnable int, @busy int, @io int,
@idle int, @connections int, @packet_in int, @packet_out int,
@total_read int, @total_write int, @total_errors int, @now datetime
declare @l_time datetime, @l_busy int, @l_io int,
@l_idle int, @l_connections int, @l_packet_in int, @l_packet_out int,
@l_total_read int, @l_total_write int, @l_total_errors int
select
@l_time=date,
@l_busy=busy,
@l_io=io ,
@l_idle=idle,
@l_connections=connections,
@l_packet_in=packet_in,
@l_packet_out=packet_out,
@l_total_read=total_read,
@l_total_write=total_write,
@l_total_errors=total_errors
from record
where date = convert(datetime,convert(char(8),getdate(),1))
sql/Sybase/record.492 view on Meta::CPAN
exec sp__add_record
waitfor delay '00:00:05'
exec sp__monitor
return
end
/* Current Stats */
exec sp__stat2
@users output,
@runnable output,
@busy output,
@io output,
@idle output,
@connections output,
@packet_in output,
@packet_out output,
@total_read output,
@total_write output,
@total_errors output,
@cur_time output
/* print some info */
declare @secs int
select @secs = datediff(ss,@l_time,@cur_time)
create table #r ( text varchar(127) )
insert #r select "Number Of Users ="+convert(varchar(8),@users)
insert #r select "Seconds Since Last Run ="+convert(varchar(8),@secs)
insert #r select "Cpu Busy ="+convert(char(4),@busy-@l_busy)
insert #r select " Io Busy ="+convert(char(4),@io-@l_io)
select * from #r
/* save some stats for next time */
delete master..record
where date = convert(datetime,convert(char(8),getdate(),1))
and description=@desc
insert master..record
select
convert(datetime,convert(char(8),getdate(),1)),
@busy ,
@io ,
@idle ,
@connections ,
@packet_in ,
@packet_out ,
@total_read ,
@total_write ,
@total_errors ,
@desc
END
sql/Sybase/stat.sql view on Meta::CPAN
and type = "P")
drop proc sp__stat
go
if exists (select * from sysobjects
where name = "sp__stat2"
and type = "P")
drop proc sp__stat2
go
/* numbers here are in seconds for io, busy, idle */
create proc sp__stat2 (
@users int output,
@runnable int output,
@busy int output,
@io int output,
@idle int output,
@connections int output,
@pin int output,
@pout int output,
@tread int output,
@twrite int output,
@terr int output,
@now datetime output,
@dont_format char(1) = null
sql/Sybase/stat.sql view on Meta::CPAN
select @users=count(*)
from master..sysprocesses
where suid>1
select @runnable=count(*)
from master..sysprocesses
where cmd!="AWAITING COMMAND"
and suid>1
select
@busy = ( @@cpu_busy * @ms_per_tick) / 1000,
@io = ( @@io_busy * @ms_per_tick) / 1000,
@idle = ( @@idle * @ms_per_tick) / 1000,
@connections = @@connections,
@pin = @@pack_received,
@pout = @@pack_sent,
@tread = @@total_read,
@twrite = @@total_write,
@terr = @@total_errors,
@now = getdate()
END
go
/* If batch=1 then do in a loop, if it =0 then save and print @ once */
create proc sp__stat( @cnt int=10, @dly int=2, @batch char(1)=null,
@dont_format char(1) = null
)
AS
BEGIN
declare @users int, @runnable int, @busy int, @io int,
@idle int, @connections int, @pin int, @pout int,
@tread int, @twrite int, @terr int, @now datetime
declare @last_users int, @last_runnable int, @last_busy int, @last_io int,
@last_idle int, @last_connections int, @last_pin int, @last_pout int,
@last_tread int, @last_twrite int, @last_terr int, @last_now datetime
declare @secs int
/* Process Stats */
set nocount on
/* Initialize */
exec sp__stat2
@last_users output,
@last_runnable output,
@last_busy output,
@last_io output,
@last_idle output,
@last_connections output,
@last_pin output,
@last_pout output,
@last_tread output,
@last_twrite output,
@last_terr output,
@last_now output
sql/Sybase/stat.sql view on Meta::CPAN
waitfor delay '00:01:00'
else
begin
print "Delay must be 1,2,3,4,5,10,30, or 60"
return
end
exec sp__stat2
@users output,
@runnable output,
@busy output,
@io output,
@idle output,
@connections output,
@pin output,
@pout output,
@tread output,
@twrite output,
@terr output,
@now output
select @secs = @busy - @last_busy + @io - @last_io + @idle - @last_idle
if @secs = 0
select @secs=1
if @batch is null
select
"Usrs" = convert(char(4), @users),
"Run" = convert(char(3), @runnable),
"%Cpu" = convert(char(4), (100*(@busy-@last_busy))/@secs),
"%IO" = convert(char(4), (100*(@io-@last_io))/@secs),
"Secs" = convert(char(4), datediff(ss,@last_now,@now)),
"Conn" = convert(char(4), @connections - @last_connections),
"Net in" = convert(char(4), @pin - @last_pin),
"Net out" = convert(char(4), @pout - @last_pout),
"Reads" = convert(char(4), @tread - @last_tread),
"Writes" = convert(char(4), @twrite - @last_twrite),
"Errors" = convert(char(4), @terr - @last_terr)
else
insert #stats
select
Dt = getdate(),
Usrs = convert(char(4), @users),
Run = convert(char(3), @runnable),
Cpu = convert(char(4), (100*(@busy - @last_busy))/@secs),
IO = convert(char(4), (100*(@io - @last_io))/@secs),
Secs = convert(char(4), datediff(ss,@last_now,@now)),
conn = convert(char(4), @connections - @last_connections),
net_in = convert(char(4), @pin - @last_pin),
net_out = convert(char(4), @pout - @last_pout),
reads = convert(char(4), @tread - @last_tread),
writes = convert(char(4), @twrite - @last_twrite),
errors = convert(char(4), @terr - @last_terr)
select
@last_busy = @busy,
@last_io = @io,
@last_idle = @idle,
@last_connections = @connections,
@last_pin = @pin,
@last_pout = @pout,
@last_tread = @tread,
@last_twrite = @twrite,
@last_terr = @terr,
@last_now = @now
txt/Oracle/tune_wait.1.txt view on Meta::CPAN
Notes:
----------------------------------------------------------------
1. Not all waits are shown.
2. Buffer busy waits may be caused by free list contention
. for specific tables (create more free lists if neccesary)
. or by insufficient rollback segments or extents
3. Free buffer waits may be caused by excessive disk sorts
4. Enqueue waits may indicate contention for table or row
. locks (do you have foreign keys defined without indexes?)
5. Examine v$latch (or run latch_sta.sql) if latch waits are
. significant
( run in 0.472 second using v1.01-cache-2.11-cpan-d6f9594c0a5 )