Re: Keeping stored procedures in the shared pool ?

Dave Marcus (dmarcus_at_notes.cc.bellcore.com) wrote:

: Try this:  grant execute on the package to sys, logon as sys and at the 
: command line:
: begin
: dbms_shared_pool.keep ('owner.package_name');
: end
: it should work

Here is an oraperl script that I wrote a while ago to keep large stored packages in shared memory :-

It is invoked by

keepem [-d] [-n] <sys password> <minsize>

------------------------Cut here---------------------------

$mode = "keep";
$cmp = ">";
$dummy = 0;

while(( $_ = $ARGV[0]) =~ /^-/ ) {

	if( $_ eq "-d" ) {
		$mode = "unkeep";
		$cmp = "<";
	}
	elsif( $_ eq "-n" ) {
		$dummy = 1;
	}
	else {
		print STDERR "Bad option $_\n";
		exit(1);
	}

$syspwd = $ARGV[0];
$minsize = $ARGV[1];

$syslda = &ora_login($twospec, "sys", $syspwd)

                        || &oradie("Failed to create sys session");

$bigobjcsr = &ora_open($syslda, "select owner, name, totsize

				  from dba_keepsizes 
				  where totsize $cmp $minsize")
			|| &oradie("Failed to open cursor on large objects");



$keepcsr = &ora_open($syslda, "begin dbms_shared_pool.$mode(:1); end;")


			|| &oradie("Failed to open cursor on large object keeper");



$keptcsr = &ora_open($syslda, "select kept


				from v\$db_object_cache
				where owner = :1
				and   name = :2
				and   type in ('PACKAGE', 'PROCEDURE','FUNCTION','PACKAGE BODY')" )
			|| &oradie("Failed to open cursor on object cache");



$accessiblecsr = &ora_open($syslda, "select count(*)


					from all_tab_privs
					where table_schema = :1
					and   table_name = :2" )
			|| &oradie("Failed to open cursor on accessability");

while( ($owner, $name, $totsize) = &ora_fetch($bigobjcsr) ) {
	print STDERR "Inspecting $owner.$name (size $totsize) - ";

	&ora_bind($keptcsr, $owner, $name)
			|| &oradie("Failed to bind to kept cursor");

	if( ($kept) = &ora_fetch($keptcsr) ) {
		if( $kept eq "YES" ) {
			print STDERR "Already kept\n";
		}
	}
	if( $kept ne "YES" ) {
		if( $owner ne "SYS" ) {
			&ora_bind($accessiblecsr, $owner, $name )
				|| &oradie("Failed to bind to accesibility cursor");
 ($accessible) = &ora_fetch($accessiblecsr);
		}
		else {
			$accessible = 1;
		}
		if( !$accessible ) {
			if( !$ldas{$owner} ) {
				print STDERR "Connecting - ";
				$ldas{$owner} = &connectasuser($owner);
			}
			print STDERR "Granting - ";
			&ora_do($ldas{$owner}, "grant execute on $name to sys") ||
					&oradie("Failed to grant execute access to sys");
		}
		print STDERR "Keeping - ";
		if( !$dummy ) {
			if( &ora_bind($keepcsr, $owner.".".$name) ) {
				print STDERR "OK\n";
			}
			else {
				print STDERR "Failed to keep $owner.$name - $ora_errstr\n";
			}
		}
		else {
			print "Suppressed\n";
		}
		if( !$accessible ) {	# i.e. did a grant
			print STDERR "Revoking";
			&ora_do($ldas{$owner}, "revoke execute on $name from sys") ||
					&oradie("Failed to revoke execute access from sys");
		}
 print STDERR "\n";
	}

&oradie("Failed to fetch big object") if ( $ora_errno );
&ora_close($keepcsr);
&ora_close($keptcsr);
&ora_close($bigobjcsr);
&ora_close($accessiblecsr);
foreach $lda ( values(%ldas) ) {
	&ora_logoff($lda);

&ora_logoff($syslda);

exit(0);

sub connectasuser {

	local($uname) = _at__;
	local($pwd);
	local($usrlda);

	$dbausrcsr = &ora_open($syslda, "select password from dba_users where username = :1")
			|| &oradie('Failed to open dba user cursor');
	&ora_bind($dbausrcsr, $uname);
	($pwd) = &ora_fetch($dbausrcsr);
	&oradie("Failed to find user $uname") if( !$pwd );
	&ora_close($dbausrcsr);

	&ora_do($syslda, "alter user $uname identified by temporary_dummy_password_")||
			&oradie("Failed to change password");
	$usrlda = &ora_login($twospec, $uname, 'temporary_dummy_password_' );

	if( !$usrlda ) {
		&ora_do($syslda, "alter user $uname identified by values '$pwd'")||
			&oradie("Failed to change password back!");
		&oradie("Failed to connect as $uname");
	}
	&ora_do($syslda, "alter user $uname identified by values '$pwd'")||
			&oradie("Failed to change password back!");
	$usrlda;

sub oradie {

	local($msg,$line) = _at__;
	local($package, $filename, $line) = caller;

	print STDERR "Oracle Error - $ora_errstr ($ora_errno) : $msg at line $line of $filename\n";
	die "Exiting due to Oracle error";

------------------------Cut here---------------------------

--
Chris Selwyn
Senior Consultant
Perihelion Software
email: chris_at_perihelion.co.uk tel: +44 1749 344203