Home » SQL & PL/SQL » SQL & PL/SQL » flush buffer cache within stored procedure (9i)
flush buffer cache within stored procedure [message #391577] Thu, 12 March 2009 11:18 Go to next message
firefly
Messages: 53
Registered: March 2009
Location: Europe
Member
Hi,

I'm trying to create a stored procedure that can be used by a developer to flush the buffer cache on a test database so I don't have to grant him alter system directly.

My proc is below - does anyone know if this is possible...?

Thanks in advance!

SQL> CREATE OR REPLACE PROCEDURE flush_buffer_cache
2 AS
3 BEGIN
4 alter system flush buffer cache;
5 -- NULL;
6 END;
7 /

Warning: Procedure created with compilation errors.

SQL>
SQL> SHOW ERRORS
Errors for PROCEDURE FLUSH_BUFFER_CACHE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/1 PLS-00103: Encountered the symbol "ALTER" when expecting one of
the following:
begin case declare exit for goto if loop mod null pragma
raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe

SQL>


Re: flush buffer cache within stored procedure [message #391579 is a reply to message #391577] Thu, 12 March 2009 11:22 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Direct DDL is not allowed within PL/SQL procedure.
must abuse EXECUTE IMMEDIATE.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Re: flush buffer cache within stored procedure [message #391580 is a reply to message #391579] Thu, 12 March 2009 11:25 Go to previous messageGo to next message
firefly
Messages: 53
Registered: March 2009
Location: Europe
Member
Thanks for the quick reply - how do I use EXECUTE IMMEDIATE in my scenario?

Re: flush buffer cache within stored procedure [message #391581 is a reply to message #391580] Thu, 12 March 2009 11:29 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
The usual way.
Re: flush buffer cache within stored procedure [message #391582 is a reply to message #391577] Thu, 12 March 2009 11:29 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Which is it?
Are you unwilling or incapable to Read The Fine Manual?
http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96624/11_dynam.htm#8074
Re: flush buffer cache within stored procedure [message #391583 is a reply to message #391580] Thu, 12 March 2009 11:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I recommend you to read and search in:
PL/SQL User's Guide and Reference

Regards
Michel
Re: flush buffer cache within stored procedure [message #391588 is a reply to message #391577] Thu, 12 March 2009 11:39 Go to previous messageGo to next message
firefly
Messages: 53
Registered: March 2009
Location: Europe
Member
Thanks for the link. I have the procedure compiled but am getting an error with insufficient privs - I am logged in as SYSTEM . Any ideas welcome!


SQL> show user
USER is "SYSTEM"
SQL>
SQL> CREATE OR REPLACE PROCEDURE flush_buffer_cache
2 is
3 sql_stmt varchar(200);
4
5 BEGIN
6 sql_stmt := 'alter system flush buffer_cache';
7 execute immediate sql_stmt;
8 -- NULL;
9 END;
10 /

Procedure created.

SQL>
SQL>
SQL> show errors
No errors.
SQL>
SQL>
SQL>
SQL> execute flush_buffer_cache
BEGIN flush_buffer_cache; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYSTEM.FLUSH_BUFFER_CACHE", line 7
ORA-06512: at line 1
Re: flush buffer cache within stored procedure [message #391590 is a reply to message #391588] Thu, 12 March 2009 11:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://asktom.oracle.com/tkyte/Misc/RolesAndProcedures.html

Regards
Michel
Re: flush buffer cache within stored procedure [message #391592 is a reply to message #391590] Thu, 12 March 2009 11:47 Go to previous messageGo to next message
firefly
Messages: 53
Registered: March 2009
Location: Europe
Member
Thanks Michel, but I am running this as SYSTEM - from which I can ALTER SYSTEM outside of the procedure...
Re: flush buffer cache within stored procedure [message #391595 is a reply to message #391577] Thu, 12 March 2009 11:49 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Privileges acquired via ROLE do NOT apply within PL/SQL procedures.

Explicit GRANT is necessary.
Re: flush buffer cache within stored procedure [message #391596 is a reply to message #391595] Thu, 12 March 2009 11:51 Go to previous messageGo to next message
firefly
Messages: 53
Registered: March 2009
Location: Europe
Member
Sorry don't get this - do I need to grant system execute on this procedure even though the procedure was created by system?
Re: flush buffer cache within stored procedure [message #391598 is a reply to message #391592] Thu, 12 March 2009 11:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please carefully read the article it explains why you have this error and how to verify if it really applies to you.

Regards
Michel
Re: flush buffer cache within stored procedure [message #391599 is a reply to message #391598] Thu, 12 March 2009 12:02 Go to previous messageGo to next message
firefly
Messages: 53
Registered: March 2009
Location: Europe
Member

I ran the test "set role none" and then tried the ALTER SYSTEM and it failed (so am I right in saying that SYSTEM gets the ALTER SYSTEM privilege from a role then?).
So am I correct in saying that the developer I am trying to grant this procedure to will actually require ALTER SYSTEM privilege granted to them explicitly before they can run this proc? If so this is not what I am looking for at all.
Re: flush buffer cache within stored procedure [message #391600 is a reply to message #391599] Thu, 12 March 2009 12:09 Go to previous messageGo to next message
firefly
Messages: 53
Registered: March 2009
Location: Europe
Member
Just to add - how would you then grant the user the ability to flush the buffer cache without having to grant the ALTER SYSTEM priv which will them the perform a number of other tasks?
Thanks!
Re: flush buffer cache within stored procedure [message #391602 is a reply to message #391577] Thu, 12 March 2009 12:16 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
The procedure should be owned by a user with sufficient privs granted to it.

GRANT EXECUTE ON flush_buffer_cache to DEV_A
Re: flush buffer cache within stored procedure [message #391603 is a reply to message #391599] Thu, 12 March 2009 12:23 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
firefly wrote on Thu, 12 March 2009 18:02

I ran the test "set role none" and then tried the ALTER SYSTEM and it failed (so am I right in saying that SYSTEM gets the ALTER SYSTEM privilege from a role then?).

Yes.
firefly wrote on Thu, 12 March 2009 18:02
So am I correct in saying that the developer I am trying to grant this procedure to will actually require ALTER SYSTEM privilege granted to them explicitly before they can run this proc? If so this is not what I am looking for at all.

No, the ALTER SYSTEM privilege is (by default) required to the owner of that procedure (SYSTEM in your case).
It may be changed by specifying AUTHID CURRENT_USER clause (as documented for CREATE PROCEDURE statement); but from your post it seems you do not want this.
Re: flush buffer cache within stored procedure [message #391604 is a reply to message #391602] Thu, 12 March 2009 12:24 Go to previous messageGo to next message
firefly
Messages: 53
Registered: March 2009
Location: Europe
Member
Got it! Created the procedure in SYS and granted execute to the Dev. I'll look at setting up a DBA account and grant ALTER SYSTEM directly to it from SYS and then re-create the proc there as I don't want any non "Oracle" objects in SYS. Thanks again for your help and links. Appreciate it!

Firefly.
Re: flush buffer cache within stored procedure [message #391605 is a reply to message #391577] Thu, 12 March 2009 12:28 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>Created the procedure in SYS
NO, No, no!
Only use SYS for patching (upgrades).
NEVER EVER use SYS for anything other than Oracle maintenance!
Re: flush buffer cache within stored procedure [message #391607 is a reply to message #391604] Thu, 12 March 2009 12:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition, why do you need to flush the buffer cache? I hope this is just a development or test database.

Regards
Michel

[Updated on: Thu, 12 March 2009 13:00]

Report message to a moderator

Re: flush buffer cache within stored procedure [message #391727 is a reply to message #391577] Fri, 13 March 2009 04:06 Go to previous messageGo to next message
firefly
Messages: 53
Registered: March 2009
Location: Europe
Member
I agree re objects in SYS. Here's what I have done to get this working:

Dropped the procedure from SYS

Logged in as SYS and created a user with DBA privs (and ALTER SYSTEM). This user will not be known by the developer

Created the procedure in this DBA user

Granted execute on the procedure to the developer and it works

Does seem OK?

By the way, this is a dev environment (warehouse) where the developer wants to simulate the performace of SQL statements following the clearing out of the buffer cache following a server re-boot / instance crash etc

Thanks again for your valuable input. Learn something new...
Re: flush buffer cache within stored procedure [message #391729 is a reply to message #391727] Fri, 13 March 2009 04:10 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
The user does not need DBA privs if it has alter system.

Just out of curiosity: why are you interested in the performance of a system after a crash? That should be something that doesn't happen too often, and besides, there isn't an awful lot you can do to improve it. After all, all data has to be gotten through disk reads.
Re: flush buffer cache within stored procedure [message #391730 is a reply to message #391729] Fri, 13 March 2009 04:13 Go to previous messageGo to next message
firefly
Messages: 53
Registered: March 2009
Location: Europe
Member
The developer needs the timings for the SLA - slowest & normal timings. Thanks
Re: flush buffer cache within stored procedure [message #391818 is a reply to message #391730] Fri, 13 March 2009 13:47 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
I've used a dbms_job to warm up the caches, to overcome slow response after a DB restart. It simply makes a few automated PL/SQL selects to mimic what a user does from the application. The objective is to get some data & SQL cached after a restart, before a user access the system. From what I recall, you need to change the tablespaces to read-only then back to normal to flush the buffer cache (flushing the shared pool isn't enough). This may be outdated approach now...

To me this automated warmup makes sense at it avoids the worst case scenario your developer is testing for SLA compliance...
Re: flush buffer cache within stored procedure [message #391853 is a reply to message #391818] Sat, 14 March 2009 00:52 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I was thinking along the same lines, except that I thought a database trigger might do it (after startup trigger, if something like that exists).
It would be (or might be) a waste to spend time or adjust code to account for the odd occasion of an empty buffer cache. Better to fire up some of the key queries to mimic end user behaviour after restarting the database.
Previous Topic: Procedure needed without creating arrays
Next Topic: Transaction log
Goto Forum:
  


Current Time: Sun Dec 04 10:33:29 CST 2016

Total time taken to generate the page: 0.04889 seconds