> Dear Ari,

> I have visted you Site, It was very much relavent to the people who are
> involving as Oracle DBA. You are extending full co-operation. Earlier I had
> asked few questions and most of them you have clarified in good way. I am very
> much thankful to you. I have some more questions and I hope you can clarify
> them?

> 1) In order to keep the Package or PL/SQL in shared pool permanently , so
> that the kept package can not be flushed out from the Shared pool and hence
> it causes I/O improvement. I did it with SYS an other User accounts as below:

> SQL> sqlplus sys/sys
> SQL> exec dbms_shared_pool.keep('sys.standard'); 

> If I run with SYS user account, I am getting the following message

> begin dbms_shared_pool.keep('sys.standard'); end;

>       *
> ERROR at line 1:
> ORA-06550: line 1, column 7:
> PLS-00201: identifier 'DBMS_SHARED_POOL.KEEP' must be declared
> ORA-06550: line 1, column 7:
> PL/SQL: Statement ignored
> ----------------------------------------------------------------------------
> SQL> sqlplus user/user
> SQL> exec dbms_shared_pool.keep('sys.standard'); 
> If I run with Other user account, I am getting the following message
> begin dbms_shared_pool.keep('sys.standard'); end;
> *
> ERROR at line 1:
> ORA-06509: PL/SQL: ICD vector missing for this package
> ORA-06512: at "OPS$BHAS.DBMS_SHARED_POOL", line 27
> ORA-06512: at "OPS$BHAS.DBMS_SHARED_POOL", line 32
> ORA-06512: at line 1
> I am unable locate the problem, Can you suggest me how to resolve this problem?
I do not have a database to answer your question. Try:
oerr ora 6509
oerr 6512

at the UNIX prompt. Then call Oracle Support for more help.

> 2) As Oracle Corporation recomended, the value of the DB_BLOCK_SIZE parameter 
> should be multiples of Operating System Block Size. Why is it so?
> How it affects in case if use both File System Files as well as Raw device
> Files for datafile?
> What is the Operating System Block Size? Will it differ from one system to
> other system? How to know the value?

It should be a multiple so that when the database writer reads/writes from
the datafiles it can do so in the most effective manner. If there is no even
multiple, then one of the "hits" will retrieve excess information to "round
up" the amount of data retrieved to the nearest block size.
Unix typically has 512-bytes for a block size. This can differ from system
to system. Consult your OS manual for the commands and syntax for finding
the OS block size.

> 3) To execute any SQL Statement, we can either user ";" or "/" symbol.
> But as I have seen one of the Book published by Oracle Corporation, by using
> "/" instead of ";", the SQL Statement can be executed faster than using ";"
> symobol.
> What is the reason for this?
> Where are the functional differences between ";" and "/" symbol?

The semi-colon is used on the same line as the code.
The / is used on the first and subsequent executions.
There is no difference in how Oracle dals with the SQL. This is just how
Oracle's cheesy line-editor works.

> 4) Can you provide me query to see the user name and lock details which
> includes object name, Type of Statement, Type of lock aquired and some other
> details?

The presentation "sql_notools99.ppt" is available at www.arikaplan.com. There
are also scripts in $ORACLE_HOME/rdbms/admin to do this (utllock*.sql).

> 5) How the lock contention is tracked and resolved?

Use $ORACLE_HOME/rdbms/admin/utllockt.sql to view who holds locks and who is
blocking with locks. To resolve, kill the user that is blocking others.

> 6) Suppose you have switch over from One type of system (say HP-UX) to other
>    type (Sun Solaris), What are the factors I have to consider?

Make sure that your cron jobs (if you have any) and OS scripts (if you have
any) still work. Aside from that, all procedures/tables/privileges/etc will
work exactly the same.
> Thank you,

You are welcome.

-Ari Kaplan
Independent Oracle DBA Consultant

<-> For 315+ Oracle tips, visit my Web Page:                      <->
<->                                                               <->
<->             http://www.arikaplan.com                          <->
<->                                                               <->
<->             email: akaplan@interaccess.com                    <->

Back to Ari Kaplan's Home Page > Back to Ari Kaplan's Home Page