Tom,

The query you gave in your answer sheds good light on tuning using the bstat/estat utilities.
Wayne

Excelent

I have been trying to understand what is behind my extremely high library cache hit ratio (99.5%), yet relatively poor gethitratio of 60%. From what I read no physical db tuning would improve the gethitratio since there was plenty of free memory for the shared pool.

Hi Tom,

Most of the time when I run remove_constant and the query to generate report, I get :

update t1 set sql_text_wo_constants = remove_constants(sql_text)
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

What could be the reason ?

Regards,
Raman.

Hi tom ,

the solution/work around, you have given above, does it
use the bind variables ?

beacause I don't know yet, after all your explanation in all questions and your book
the difference between the cursor variable and bind variable

I think can we use the bind variable in the following way
will the performance better ?


/********************************************/
declare
l_tmp long;
begin
open x for
'select rowid rid, sql_text from t1' --returns 10000 rows
loop
l_tmp := remove_constants( x.sql_text );
execute immediate
'update t1 set sql_text_wo_constants=:a where rowid :y'
using remove_constants( x.sql_text ),x.rid
end loop;
end;
/
/*********************************************/

could you please explain. Tankans..

Could you clarify further as how to rad the output from query to make out whether bind variables are being used or not?
SQL> /

Function created.

SQL>  update t1 set sql_text_wo_constants = remove_constants(sql_text);

276 rows updated.

SQL> select sql_text_wo_constants, count(*)  from t1 group by sql_text_wo_constants
  2  having count(*) > 100 order by 2;

no rows selected

SQL> /

no rows selected

SQL> 2
  2* having count(*) > 100 order by 2
SQL> c/100/5
  2* having count(*) > 5 order by 2
SQL> /

SQL_TEXT_WO_CONSTANTS
--------------------------------------------------------------------------------
  COUNT(*)
----------
SELECT OBJECT_NAME FROM ALL_OBJECTS WHERE OBJECT_ID = @
        92

What does @ mean here? 

ORA-03113: end-of-file on communication channel

I am sorry that I can't remember the context, but I have
seen that message in relation to some kind of a space
problem. It wasn't an Oracle bug it was my bug. I can't
remember more about it than that, but I remember thinking
that it was "obvious" after I found it. Sorry this is so
vague, but had I called Oracle support, I'm not sure that
message would trigger an answer to the problem I had. At
the time I thought my database had been corrupted by
lightening, and restored from backups, but when I got to
same point again, I got the same error and that was when
the light went on. Anyway, this can just be a warning that
ORA 03113 is not a clear message, so checking your logs
(as Tom suggests) will worth a lot more than that error.

Hi tom,

Thanks, for your answer.
I would like to still ask you one question on this bind variable thing.

Now as you've explained me
1.) if I can do it in one stmt. I should do it.

Ok, my qustion is in your followup answer

/***************************************************/
Followup:
Please contact support -- it will be a bug, you may require a patch. I've never
seen that behaviour myself.

There will be a trace file on the server they will need to diagnose this issue.

meanwhile, you can do something like:

declare
l_tmp long;
begin
for x in ( select rowid rid, sql_text from t1 )
loop
l_tmp := remove_constants( x.sql_text );
update t1 set sql_text_wo_constants = l_tmp where rowid = x.rid;
end loop;
end;
/

instead of the single update.

/********************************************************/


don't we have the same situation where
we are saying

update t set x = l_tmp where y = x.rid

isn't it same as

update t set x = 4 where y = 5

so won't the sql stmt will distinct at run time
it be executed the # of rows time ?
are we using bind varible ? if yes how ?
if not, then in this case, will it be better to use bind
using the execute immedieate ?

Plese explain..
thanks in advence

Hi Tom,

Thanks, for clearing this up for me.
Now I feel comfortable with this bind variable and cursor variables, as you've acknoledged that pl/sql cursor variable/ plsql variable is it self a bind/host variable.

I will further investigate and explore this becacuse I know I have seen some illustration where in the pl/sql block
where we can use the pl/sql varable, dynamic stmt is used.

I think from my point of view almost all statement can be written using pl/sql variable ( DDL stmt excluded). but I will investigate further ...

thanks..

I just ran into this over the weekend while building a new instance on a new server. It was apparently the result of not having enough semaphores.

When I reduced the PROCESSES parameter in the init.ora file to 50, the error went away; when I raised it, it came back. We adjusted the kernel parameters in the /etc/system file, rebooted the server, and everything was fine again.

I also saw this error once, one of my developers was trying to insert a record and have a before insert/update tirgger with a :OLD.Field1 var the is updating part.

create or replace trigger biur_t before insert or update on t for each row
begin
...
if inserting then
...
:OLD.Field1 := sysdate;
else
...
end;

Guess what??? it looks just normal and compile as well but every time they try to insert a record the same messages POP.

We search the web, we look some books and open the traces files and every log and trying to find every string with the offending table or user and That's all folks, we fix it and it's gone away.

I also saw this error once, one of my developers was trying to insert a record and have a before insert/update tirgger with a :OLD.Field1 var the is updating part.

create or replace trigger biur_t before insert or update on t for each row
begin
...
if inserting then
...
:OLD.Field1 := sysdate;
else
...
end;

Guess what??? it looks just normal and compile as well but every time they try to insert a record the same messages POP.

We search the web, we look some books and open the traces files and every log and trying to find every string with the offending table or user and That's all folks, we fix it and it's gone away.

I've run your script on my database and found a few statements that are candidates for bind variables. We have a bunch of Oracle Forms applications running, so I'm not sure which one is executing each statement. How do I determine who submitted the offending SQL?

Hi Tom,

Your remove_constants logic is very useful for me.

I was testing your remove_constants function in one of our devlopment environments and found one Intresting point.

/* ORACLEOEM */ SELECT MIN_VALUE, MAX_VALUE, INCREMENT_BY, LAST_NUMBER, CYCLE_FL
AG, ORDER_FLAG, CACHE_SIZE FROM SYS.DBA_SEQUENCES WHERE SEQUENCE_OWNER = ('#') A
ND SEQUENCE_NAME = ('#')

SQL_TEXT_WO_CONSTANTS
--------------------------------------------------------------------------------
COUNT(*)
----------
113

/* ORACLEOEM */ SELECT TEXT FROM DBA_SOURCE WHERE OWNER = '#' AND NAME = '#' AND
TYPE = '#' ORDER BY LINE
567

/* ORACLEOEM */ SELECT INITCAP(STATUS) FROM DBA_OBJECTS WHERE OWNER = '#' AND OB
JECT_NAME = '#' AND OBJECT_TYPE = '#'
590

So OEM is not using your rule of thumb(it is not using bind variables).

My doubt is why can't OEM use bind variables?

Using this great technique, I found and fixed three minor transgressions in my code that were wasting cache space. However the vast majority of SQL that was duplicated in the cache was generated by Oracle Sales Analyzer or Oracle Discoverer. Don't you have any in-house readers, Tom? ;-)

I can understand why OSA might not be able to use bind variables because of its dynamic nature, but Discoverer builds fairly straight forward queries against static tables. Are there any techniques we can use to shield the database from abuse by query generation tools?

By coincidence, I had read you book up through page 448 last night. I experienced some deja vu when I resumed my reading this evening on page 449 where you cover this very function.

Your book is excellent. I don't know why more technical documentation isn't written in the 1st person. It's the natural way to communicate.

DW = binds if you want to (i cannot believe i said that!)

Are you sure you don't have a fever? Body snatched? Did it feel weird to say it?

Just kidding, of course...

- Mark

Hi Tom ,
Function was pretty useful.. i corrected those queires which was found ........

oiisdba@OIIS8.WORLD> update t1 set sql_text_wo_constants = remove_constants(sql_text);

12867 rows updated.

oiisdba@OIIS8.WORLD> select sql_text_wo_constants, count(*)
2 from t1
3 group by sql_text_wo_constants
4 having count(*) > 100
5 order by 2
6 /

SQL_TEXT_WO_CONSTANTS
--------------------------------------------------------------------------------------------------
COUNT(*)
----------
SELECT * FROM T_FAS_SALE_AWA WHERE DOC_NO=@
115

SELECT ROWID,REAM_ID,PALLET_CODE,BATCH_NO,PART_KEY,QLTY_DEST,USERID,DATESTAMP,PRINT_CNT,RG_VARIETY
D,SMR_FLAG,REAM_STATUS,DEFECT_CD,REAM_WT FROM T_PMS_REAM_DTL WHERE PART_KEY = @ AND REAM_ID > '#'
D REAM_ID < '#' AND (PALLET_CODE=:@) AND (BATCH_NO=:@) ORDER BY REAM_ID
489

Thanks...

"anywhere there is a PLSQL variable -- it is really a host variable, a bind
variable. That is part of the beauty of PLSQL -- it makes binding almost MOOT.
You have to absolutely go out of your way to NOT bind in plsql.
"

well, if you do a simple select as below in pl/sql
wont it violate the always-use-bind-variable rule
(due to the hard coded constant 'FGL'?
....
select a
into b
from t
where t.x = 'FGL';
....

Thanx!

now I understand - in pl/sql if static sql then anyways
the query does not change -
if not then you have to use variables which are
bound - so is there anyway that you could be violating
the "always-bind" rule in pl/sql?

Thanx!

i guess using the dynamic sql you showed is the
way you would violate the "always-gind" rule..'thanx!:)

Tom
You said,
" but in static sql in plsql -- you need not. By definition, in static sql, the
values will NEVER change and you need bind only when the values CHANGE ".

Does this argue for encapsulating all select statements in functions (That could be reused later?) this would ensure that there are no two select stmts that are the same with different values within pl/sql packages?
e.g.
instead of having
select x
from w
where v = &#8216;F&#8217;

at one place
and
select x from w where v = &#8216;G&#8217;

at another you make a function of that select statement and use it.

Do you see any pros/cons of this approach?

Thanx for your wonderful site!:)

lot of times some data has to be there in the
installed tables - usually metadata...
in our case, those dont use bind values.
For exmaple,

insert into table t1 values ( 1, 'metadata 1');
insert into table t1 values ( 2, 'metadata 1');
insert into table t1 values ( 3, 'metadata 1');

There could be many such statements.

Since they are one time they do fill up the library
cache once in the beginning.

Do you think they are harmful (Since their effect
is only in the beginning)

I guess i just wanted to gauge the priority of this work -
from what you say, it seems like it should be done but
is not such a big problem

Regards

Tom,

I used your function remove_constants and have found out that are whole buch of queries that dont use bind variables.

The situation is I dont have acces to the codes so i cannot fix it (3rd party software) and to make matters worse we are running on 8.0.5. To upgarde the systems to 9i will take atleast 3-4 months.Till then can you suggest something that i can do.

Thanks.

Tom,
As the function remove_constants converts all the numbers to @, it may also change the names of columns and tables. e.g., both 'select c1 from t1' and 'select c2 from t2' will be converted as 'SELECT C@ FROM T@'. Is it better to conver only the numbers after the '=' sign? Thanks,

Tom,

Thanks very much for your reply. I agree the situation is much more complicated than I first thought, and it’s hard to list all the possibilities where numbers are used as values. On the other hand, the numbers is used in a name (tables, columns, schemas…) in limited number of ways. By taking this advantage, the following modified function seems working in most cases, though I feel there can be many exceptions (e.g., to_date).

The reason for me to pursue the issue is that I know in some databases, for security raeson, numbers are used intensively in column/table names. If we get 1000 'select * from t@ where c@ = @', it may come from 1 case of 'select * from t1 where c1=@' and 999 of 'select * from t2 where c2=@'.

*****************
create or replace function
remove_constants( p_query in varchar2 ) return varchar2
as
l_query long;
l_char varchar2(1);
l_in_quotes boolean default FALSE;
l_before_number boolean default FALSE;
l_ascii number;
begin
for i in 1 .. length( p_query )
loop
l_char := substr(p_query,i,1);
l_ascii :=ascii(l_char);

if ( l_in_quotes )
then
if (l_char ='''')
then
l_query := l_query || '''';
l_in_quotes := FALSE;
else
l_query := l_query || '#';
end if;
elsif ( NOT l_in_quotes and l_char = '''' )
then
l_in_quotes := TRUE;
l_query := l_query || '''';
elsif ( (NOT l_in_quotes )
and l_before_number
and (l_ascii between 48 and 57))
then
l_query := l_query || '@';
else
l_query := l_query || l_char;
end if;

if ( l_ascii = 36 -- 36 => $
or l_ascii = 95 -- 95 => _
or l_ascii between 65 and 90 -- 65-90 => A-Z
or l_ascii between 97 and 122 -- 97-122 => a-z
)
then
l_before_number := FALSE;
elsif (not l_before_number
and l_ascii between 48 and 57 -- 48-57 => 0-9
)
then
l_before_number := FALSE;
elsif ( NOT l_in_quotes )
then
l_before_number := TRUE;
end if;

end loop;

for i in 0 .. 8 loop
l_query := replace( l_query, lpad('@',10-i,'@'), '@' );
l_query := replace( l_query, lpad(' ',10-i,' '), ' ' );
l_query := replace( l_query, lpad('#',10-i,'#'), '#' );
end loop;
dbms_output.put_line(l_query);
return upper(l_query);

end;
/
************

Hi Tom,

Could you let me know which "ADDRESS" column of v$session relates to the address in v$sqlarea view on which your above query is operating on.

Put it simply, I need to find out the program or the process which is NOT using Binds and need to advice the development team accordingly.

Thanks,

Hi Tom
It is very nice tool to catch the culprit SQLs. Here Iam using Oracle JDeveloper. I have query like this

SELECT XYZ.TADIBANK_ID, XYZ.TADIBRANCH_ID, XYZ.TADVCHALLAN_ID, XYZ.TADDCHALLAN_DATE, XYZ.CTDILOGIN_ID, XYZ.CTDDMODIFIED_DATE, XYZ.CTDIOFFICE_ID, XYZ.TADVREGN_ID, XYZ.TADVCST_REGN_ID, XYZ.TADICIRCLE_OFFICE_ID, XYZ.TADIASSMT_YEAR, XYZ.TADVACT_ID, XYZ.TADIPERIOD, XYZ.TADRCHALLAN_AMT, XYZ.TADVSCROLL_ID, XYZ.TADVERROR_ID, XYZ.TADVRECONCILIATION_FLAG, XYZ.TADIRECONCILIATION_COUNT, XYZ.TADVREC_CONFIRM_FLAG_PCR_RCPT, FUNC1('#',XYZ.TADVACT_ID,'#') AS ACT, XYZ.TADDFROM_DATE, XYZ.TADDTO_DATE, FUNC1('#','# .........................
FROM XYZ
WHERE...............

in where clause I have used the bind variable. But in the FUNC1 I have used the literals and it should be same for all the users, still it is coming in that query(which u have given) for more than 100 times. Whether I have to use bind for those literal also?

Regards
Sudipt

1. The literal values for the queries are the SAME (for all users)

2. Release of the database -- Oracle 9.2.0.3

3. The queries are executed directly using JDBC( in built in Oracle JDeveloper). FUNC1() is a stored function in Oracle DB.

peek into v$sql_shared_cursor for that query and see which columns are flagged with "Y"s -- eg, this is a short excerpt from my book "Efficient Oracle by design" showing what I mean:

...
We simply set up the four users A, B, C, and D. We grant CREATE SESSION and CREATE TABLE privileges to users A, B, and D, and grant just CREATE SESSION to C. Then we run the following script:

create public synonym emp for d.emp;
alter system flush shared_pool;

connect a/a
create table emp ( x int );
select * from emp;

connect b/b
create table emp ( x int );
select * from emp;

connect d/d
create table emp ( x int );
grant select on emp to c;
select * from emp;

connect c/c
select * from emp;

Now, let's take a quick peek at V$SQL, which shows all of the currently cached SQL statements.

ops$tkyte@ORA920> select address, executions, sql_text
  2    from v$sql
  3   where upper(sql_text) like 'SELECT * FROM EMP%';

ADDRESS  EXECUTIONS SQL_TEXT
-------- ---------- ------------------------------
58DBD9CC          1 select * from emp
58DBD9CC          1 select * from emp
58DBD9CC          2 select * from emp

We can see that there are three versions of the query in the shared pool and that one of the versions was used two times-that is the query submitted by users C and D. 

We can also query V$SQL_SHARED_CURSOR to determine exactly why Oracle developed three query plans, to discover the root cause for the mismatch here. This view has many columns that can be used to determine why a plan in the shared pool was not shared. For example, the ROW_LEVEL_SEC_MISMATCH column would be flagged for two queries that appear to be the same but differed at runtime based on the security predicate returned by Oracle's Virtual Private Database (VPD) feature. The OUTLINE_MISMATCH column would be flagged if one query used a query outline and the other did not. Other columns in this view provide similar mismatch information. In this example, we can see this information regarding our queries:


ops$tkyte@ORA920> select kglhdpar, address,
  2         auth_check_mismatch, translation_mismatch
  3    from v$sql_shared_cursor
  4   where kglhdpar in
  5   ( select address
  6       from v$sql
  7      where upper(sql_text) like 'SELECT * FROM EMP%' )
  8  /

KGLHDPAR ADDRESS  AUTH_CHECK_MISMATCH TRANSLATION_MISMATCH
-------- -------- ------------------- --------------------
58DBD9CC 59D4C9C8 N                   N
58DBD9CC 59E54EB0 Y                   Y
58DBD9CC 5898D42C Y                   Y

In this case, Oracle flagged two columns: AUTH_CHECK_MISMATCH, meaning the authorization/translation check failed for the existing cursor (query plan), and TRANSLATION_MISMATCH, meaning the base objects of the child cursor (query plan) do not match. This shows us exactly why there are three queries plans (child cursors): The base objects referenced were different. 
 

I am sorry that the user I am talking about are the browing user not the Database user.
In my database I am using only one schema(one DB user). The application is accessed through Oracle 9iAS. Still I am getting so many count using your query.

Regards
Sudipt

Tom,

Wow, I think this is the best website for interesting and complicated oracle issues!!!

If I am reading my statspack report correctly, I have a library latch cache contention. So I ran the "remove_constants" procedure Tom recommended at the top of this thread and no SQL with > 100 counts turned up. I have set cursor_sharing=FORCE in the 8.1.6 db. What are other possible reasons for library cache latch contention? Below are snippets from the statspack report... Thanks.

Snap Length
Start Id End Id Start Time End Time (Minutes)
-------- -------- -------------------- -------------------- -----------
190 191 26-Oct-03 01:07:56 26-Oct-03 01:16:00 8.07


Cache Sizes
~~~~~~~~~~~
db_block_buffers: 131072
db_block_size: 8192
log_buffer: 524288
shared_pool_size: 100000000


Load Profile
~~~~~~~~~~~~
Per Second Per Transaction
--------------- ---------------
Redo size: 151,257.45 2,187.62
Logical reads: 6,142.08 88.83
Block changes: 946.70 13.69
Physical reads: 778.34 11.26
Physical writes: 77.79 1.13
User calls: 1,609.01 23.27
Parses: 1,047.90 15.16
Hard parses: 0.01 0.00
Sorts: 1.05 0.02
Transactions: 69.14

Rows per Sort: 117.75
Pct Blocks changed / Read: 15.41
Recursive Call Pct: 60.36
Rollback / transaction Pct: 0.30


Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait Ratio: 99.94
Buffer Hit Ratio: 87.33
Library Hit Ratio: 100.04
Redo NoWait Ratio: 100.00
In-memory Sort Ratio: 97.84
Soft Parse Ratio: 100.00
Latch Hit Ratio: 89.24


Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait % Total
Event Waits Time (cs) Wt Time
-------------------------------------------- ------------ ------------ -------
latch free 2,182,681 251,374 42.78
slave wait 30,702 137,440 23.39
db file sequential read 127,626 74,321 12.65
log file sync 43,839 41,690 7.09
io done 30,386 40,844 6.95
-------------------------------------------------------------



Pct Avg Pct
Get Get Sleeps Nowait Nowait
Latch Name Requests Miss /Miss Requests Miss
------------------------------- ------------ ------ ------ ----------- ------
Active checkpoint queue latch 924 0.0 0
Checkpoint queue latch 121,876 0.0 0.1 0
Token Manager 164 0.0 0
archive control 2 0.0 0
archive process latch 2 0.0 0
cache buffer handles 138 0.0 0
cache buffers chains 5,518,468 0.2 0.1 666,964 0.0
cache buffers lru chain 475,650 0.2 0.0 382,391 0.2
channel handle pool latch 170 0.0 170 0.0
channel operations parent latch 340 0.0 170 0.0
constraint object allocation 243 0.0 0
direct msg latch 93,078 0.0 0.0 0
dml lock allocation 247,685 0.1 0.0 0
done queue latch 29,564 0.0 29,567 0.0
enqueue hash chains 330,628 0.2 0.1 0
enqueues 394,860 0.2 0.1 0
event group latch 170 0.0 0
global transaction 7,431 0.0 0
global tx free list 531 0.0 0
global tx hash mapping 2,090 0.0 0
job_queue_processes parameter l 154 0.0 0
ksfv messages 122,630 0.0 0
ktm global data 2 0.0 0
latch wait list 1,409,267 2.1 0.1 1,653,622 4.0
library cache 11,937,161 22.3 0.8 0
library cache load lock 56 0.0 0
list of block allocation 71,525 0.0 0.0 0

Tom, I'm wondering about you said:
"You need binds when you are parsing X statements / second.
When are you parsing 1 statement every Y seconds, it is not nearly as important."

How can I get such ratio? -- x statements / second.

thanks,
Marcio

Thanks Tom for your earlier response to this issue.

Our DB Server is 8 CPUs. It is an OLTP system where the majority of the processing is this: There is A LOT (26,000 per second) of inserts into a view. An "instead of" trigger fires (and discards about 23,000 inserts because of some application logic). The rest (3,000 inserts) is processed and updates/deletes/inserts into a table (IPMAP) that keeps track of what cable modem owns what ip address. This triggers also inserts into a "history" table to store this same information.

If this is a soft parse issue, how do I go about improving this?

Below is the costly SQL statements (in terms of buffer gets) in this 8 minute window. The first statement is the insert into the VIEW.

Buffer Gets Executes per Exec Total Hash Value
-------------- ------------ ------------ ------ ------------
SQL statement
------------------------------------------------------------------------------
1,848,346 214,866 8.6 62.2 2948872974
insert into LEASEVIEW (IPADDR,REGIONID,DEVICEID,RAREMOTEID,DEVICETYPE,LEASEDAT

859,372 214,946 4.0 28.9 837720088
SELECT COUNT(*) FROM IPMAP WHERE IPADDR = :b1 AND REGIONID = :b2 AND DEVI

216,003 27,029 8.0 7.3 1919955916
SELECT COUNT(*) FROM IPMAP WHERE ((IPADDR = :b1 AND REGIONID = :b2 ) OR (D

205,496 27,019 7.6 6.9 146615416
UPDATE IPMAP SET DEVICEID=:b1,RAREMOTEID=:b2,DEVICETYPE=:b3,LEASEDATE=:b4,EXPD

198,669 27,040 7.3 6.7 1492083554
SELECT AD.ACCTTYPE,DA.DATAACCOUNTID ACCOUNTID FROM ACCTDEVICE AD,DATAACCOUNT

I would not have done it that way -- for such intensive processing -- I would have built a stored procedure that would reject 23 out of 26k records -- inserting the other 3k.

what is the calling application and is it parsing the insert before each and every insert?

also -- the trigger, does it contain the code "in it" or is the trigger a single line -- a call to a stored procedure.  If not, each execute is going to cause a soft parse of all of the trigger SQL.  Consider:

ops$tkyte@ORA920PC> create table t ( x int );
 
Table created.
 
ops$tkyte@ORA920PC> create or replace view v as select * from t;
 
View created.
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> create or replace procedure p
  2  as
  3  begin
  4          for x in ( select * from dual IN_PROCEDURE )
  5          loop
  6                  null;
  7          end loop;
  8  end;
  9  /
 
Procedure created.
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> create or replace trigger v_trigger
  2  instead of insert on v
  3  begin
  4          for x in ( select * from dual IN_TRIGGER )
  5          loop
  6                  null;
  7          end loop;
  8          p;
  9  end;
 10  /
 
Trigger created.
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> alter session set sql_trace=true;
 
Session altered.
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> insert into v values ( 1 );
 
1 row created.
 
ops$tkyte@ORA920PC> insert into v values ( 1 );
 
1 row created.
 
ops$tkyte@ORA920PC> insert into v values ( 1 );
 
1 row created.
 
ops$tkyte@ORA920PC> insert into v values ( 1 );
 
1 row created.
 
ops$tkyte@ORA920PC> insert into v values ( 1 );
 
1 row created.
 
ops$tkyte@ORA920PC>

tkprof shows us:

select * from dual IN_TRIGGER
                                                                                                  
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        5      0.00       0.00          0          0          0           0
Execute      5      0.00       0.00          0          0          0           0
Fetch       10      0.00       0.00          0         15          0           5
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       20      0.00       0.00          0         15          0           5
********************************************************************************
select * from dual IN_PROCEDURE
                                                                                                  
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      5      0.00       0.00          0          0          0           0
Fetch       10      0.00       0.00          0         15          0           5
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       16      0.00       0.00          0         15          0           5
                                                                                                  


See how the trigger sql was parsed 5 times for 5 executes, but plsql stored procedures nicely "cache" the sql.

So, my advice:

a) don't use the view
b) use a stored procedure
c) make DARN sure the caller parses the begin p(....); end; block ONCE and executes it over and over and over again.


your parsing will go way way way down.


 

Oops...I meant 26,000 inserts per minute (not per second)

Tom,
When I use the way you provided to us to find the statements
not using BIND variable, I see something in our production database like:

SQL_TEXT_WO_CONSTANTS
--------------------------------------------------------------------------------
COUNT(*)
----------
SELECT DISTINCT ADDRESS_TO_PTY_ID FROM PARTY_ACCOUNT_ADDRESSES PAA WHERE PAA.ACCT_ID =@ AND PAA.PTY_ID = ( SELECT PTY_ID FROM PARTY_ACCOUNTS PTYA, PRIMARY_ACCOUNTS PRIMA WHERE PTYA.ACCT_ID =@ AND PTYA.ACCT_ID = PRIMA.ACCT_ID AND PTYA.PTYACCTROLE_ID = @ AND NVL(PTYA.DELETED_FLG,'#') <> '#' AND ROWNUM < @ GROUP BY PTYA.PTY_ID ) AND NVL(DELETED_FLG,'#') <> '#' AND PAA.ADDRESS_ID = @ AND PAA.ACCTADDR_USETYP_ID = @
292

Then I try to do:
select sql_text from v$sql
where upper(sql_text) like 'SELECT DISTINCT ADDRESS_TO_PTY_ID FROM PARTY_ACCOUNT_ADDRESSES PAA WHERE PAA.ACCT_ID%';

I see no rows selected.

If I do :

select sql_text from v$sql
where upper(sql_text) like 'SELECT DISTINCT ADDRESS_TO_PTY_ID%';

Then I see 292 rows selected.

Why that upper(sql_text) not working for the longer string?
Is there any limitation for using upper function?

Thanks.

<quote>
So, my advice:

a) don't use the view
b) use a stored procedure
c) make DARN sure the caller parses the begin p(....); end; block ONCE and
executes it over and over and over again.
</quote>
About a) In this context -- why do I not use views?

Sorry, I was talking about VIEWs, because I use it
to manage insted of TABLES directly -- I keep tables under my own control
and give grants on views... more or less like this:
ops$t_mp00@MRP920>
ops$t_mp00@MRP920> create sequence s;

Sequence created.

ops$t_mp00@MRP920> create table p$ ( id int primary key,
2 name varchar2(10),
3 qty_c int default 0 );

Table created.

ops$t_mp00@MRP920>
ops$t_mp00@MRP920> create or replace trigger p$_bifer
2 before insert on p$ for each row
3 begin
4 select s.nextval into :new.id from dual;
5 end;
6 /

Trigger created.

ops$t_mp00@MRP920>
ops$t_mp00@MRP920> create or replace view p
2 as
3 select id p_id, name, qty_c q from p$;

View created.

ops$t_mp00@MRP920>
ops$t_mp00@MRP920> create table c$ ( id int primary key, name varchar2(10), p_id references p$ );

Table created.

ops$t_mp00@MRP920>
ops$t_mp00@MRP920> create or replace trigger c$_bifer
2 before insert on c$ for each row
3 begin
4 select s.nextval into :new.id from dual;
5 add_p(:new.p_id);
6 end;
7 /

Warning: Trigger created with compilation errors.

ops$t_mp00@MRP920>
ops$t_mp00@MRP920> create or replace view c
2 as
3 select id c_id, p_id, name child_name from c$
4 /

View created.

ops$t_mp00@MRP920>
ops$t_mp00@MRP920> create or replace procedure add_p ( i in number )
2 as
3 begin
4 update p
5 set q = q + 1
6 where p_id = i;
7 end;
8 /

Procedure created.

ops$t_mp00@MRP920>
ops$t_mp00@MRP920> show error
No errors.
ops$t_mp00@MRP920>
ops$t_mp00@MRP920> insert into p(name) values ( 'Marcio');

1 row created.

ops$t_mp00@MRP920>
ops$t_mp00@MRP920> insert into c(p_id, child_name) values (1, 'Alicia');

1 row created.

ops$t_mp00@MRP920> insert into c(p_id, child_name) values (1, 'Jack');

1 row created.

ops$t_mp00@MRP920> insert into c(p_id, child_name) values (1, 'Nicole');

1 row created.

ops$t_mp00@MRP920>
ops$t_mp00@MRP920> select * from p;

P_ID NAME Q
---------- ---------- ----------
1 Marcio 3

1 row selected.

ops$t_mp00@MRP920> select * from c;

C_ID P_ID CHILD_NAME
---------- ---------- ----------
2 1 Alicia
3 1 Jack
4 1 Nicole

3 rows selected.

ops$t_mp00@MRP920> select * from p$;

ID NAME QTY_C
---------- ---------- ----------
1 Marcio 3

1 row selected.

I use this to maybe rename column, change order of columns, whatever... up until now thought this is good way to manage schemas. What do you think?

thanks,

Sorry my misunderstood, when I saw your comment about views, I just forgot context and put my follow up.

I work for that way (demonstrated above) following your advice found here on this site (about views).

Thanks.

This was almost as good as the Thia soup they've named after you.... ;)

Hi Tom,

This is a very useful tool for catching the not-binded sqls.

One thing that puzzles me is that always when we run this, it turns out that the biggest offenders are internal sqls of Oracle Text.

Could you tell me if there are any legitimate reasons why Oracle Text is not using binded parameters?

Thanks.





DECLARE SEL NUMBER; BEGIN :@ := "CTXSYS"."TEXTOPTSTATS".ODCISTATSSELECTIVITY( S
YS.ODCIPREDINFO('#', '#', '#', @), SEL, SYS.ODCIARGDESCLIST(SYS.ODCIARGDESC(@, N

SQL_TEXT_WO_CONSTANTS
--------------------------------------------------------------------------------
COUNT(*)
----------
ULL, NULL, NULL, NULL, NULL), SYS.ODCIARGDESC(@, NULL, NULL, NULL, NULL, NULL),
SYS.ODCIARGDESC(@, '#', '#', '#', NULL, NULL), SYS.ODCIARGDESC(@, NULL, NULL, NU
LL, NULL, NULL)), @, NULL , NULL, '#', SYS.ODCIENV(@, @)); IF SEL IS NULL THEN :
@ := -@; ELSE :@ := SEL; END IF; EXCEPTION WHEN OTHERS THEN RAISE; END;
725

DECLARE COST SYS.ODCICOST := SYS.ODCICOST(NULL, NULL, NULL, NULL); BEGIN :@ :=
"CTXSYS"."TEXTOPTSTATS".ODCISTATSINDEXCOST( SYS.ODCIINDEXINFO('#', '#', SYS.ODCI
COLINFOLIST(SYS.ODCICOLINFO('#', '#', '#', '#', NULL, NULL)), NULL, @, @), @.@,

SQL_TEXT_WO_CONSTANTS
--------------------------------------------------------------------------------
COUNT(*)
----------
COST, SYS.ODCIQUERYINFO(@, SYS.ODCIOBJECTLIST(SYS.ODCIOBJECT('#', '#'))), SYS.OD
CIPREDINFO('#', '#', NULL, @), SYS.ODCIARGDESCLIST(SYS.ODCIARGDESC(@, NULL, NULL
, NULL, NULL, NULL), SYS.ODCIARGDESC(@, NULL, NULL, NULL, NULL, NULL), SYS.ODCIA
RGDESC(@, '#
816

Hi Tom,

It's Oracle9i Release 9.2.0.3.0 for Windows.

I went to
</code> http://otn.oracle.com/software/products/oracle9i/index.html <code>
to check what was the latest release of 9i and I found that 9.2.0.3 is not available to download (9.2.0.2 is the latest for Windows)

This is strange, because I am sure it was there before. Do you know why it has been removed?

Please tell me if there is something I got wrong. Thanks,

Tom,

I reviewed your book (Expert One-on-One Oracle and Oracle Perf. By design, Cary Millsap's Optimizing Oracle Performance. Based on these, I have come up with a little logic of how the Oracle kernel's latch acquisition algorithm will look like at a very high level. Please confirm if my understanding is correct. The code snippet is shown below :

Code snippet for latch that sorround an Oracle kernel code
==========================================================
if get_latch(latch_name,Mode) {
/*
.....
.....
Oracle kernel code associated for the latch
.....
.....
*/
release (latch_name)
}

Latch acquisition algorithm
===========================
Boolean get_latch (char *latch_name, char Mode) {
Latch_Acquired := 0;

If (latch for 'latch_name' available) {
Latch_Acquired := 1; /* Mark latch as acquired */
}

If (Latch_Acquired) {
If (Mode = 'I') {
Increment Gets count for Immediate mode
}
else {
Increment Gets count for Willing-to-Wait mode
}
}
else {
If (Mode = 'W') {
Increment MISSES count for Willing To Wait mode
/*
The while loop below consumes CPU time as the process consumes CPU doing nothing but
spiining for the latch
*/
While (NOT Latch_Acquired) {
If ( Request_Count++ <= LATCH_SPIN_COUNT) {
get_latch (latch_name,Mode);
}
sleep (x); /* This will post a Latch Free WAIT event and causes */
elapsed time exceed CPU time */
Request_Count = 0;
}
}
else {
Increment MISSES count for Immediate mode
}
}
return (Latch_Acquired);
}

Please confirm if my understanding is correct.

Tom,

In the above feedback, you mentioned that recursion would be logically wrong.
1. Can you please eloborate on this please
2. Also, when the process is pre-empted because of busy CPU, it will not show as WAIT line but will be represented by "Time spent not using the CPU" (which per Cary Millsap's response time analysis would be counted as N ==> time spent not utilizing the CPU) and will not appear in any of the wait interfaces since the wait interfaces do not show the time waiting for CPU.

Is my understanding of 2 correct?

As always, thanks for your very clear explanation

Tom,

In the above feedback, you mentioned that recursion would be logically wrong.
1. Can you please eloborate on this please
2. Also, when the process is pre-empted because of busy CPU, it will not show as WAIT line but will be represented by "Time spent not using the CPU" (which per Cary Millsap's response time analysis would be counted as N ==> time spent not utilizing the CPU) and will not appear in any of the wait interfaces since the wait interfaces do not show the time waiting for CPU.

Is my understanding of 2 correct?

As always, thanks for your very clear explanation

Tom,

The remove_constants function is proving to be very
helpful. It can tell you right away how many copies of
the same SQL statement are there in the SQLAREA. Is there
a query which can show the amount how much memory is
being wasted per SQL statement as compared to the total
memory allocated by shared_pool_size parameter..?

Thanks Always for your response.
Neeraj

Dear Tom,

I have a Document listed on orapub.com. The Document Titled "Conquering Oracle Latch Contention" is written by Craig A. Shallahamer. In the document on page no 12, the author states about the Library Cache Latch Contention that

"Very generally and incomplete, the library cache kernel code is used to check if a SQL Statement is cached in the library cache".

I think that the author means that the Library Cache Latch Contention occurs to check the parsed version of a SQL Statement.

As per my understanding, the author is wrong in his statement. I regularly read your articles and visit this site and based on this I have understood that

Library Cache Latch contention can occur if there are too many parses in the Library Cache probably due to any of these reasons

1. Not using Bind variables
2. Shared Pool is too small
etc.

whereas Shared Pool Latch is used for the purpose what the author of above said article states for Library Cache Latch. i.e. Shared Pool Latch is used to scan the Shared Pool (library Cache) to check if a SQL Statement is cached in the library cache. In this case, if the sql is found, then the parsing is avoided whereas if the statement is not in the Library cache then the library cache latch is acquired to parse the Statement. Am I correct ?

Regards
Vivek

Dear Tom,

Thanks for your reply.

You said that Shared Pool Latch is used to protect Memory Allocation. What does it mean ? Can you explain me one scenario when the memory allocation is to be protected so that it is clear for my understanding.

What I have understood so far was

When a user fires a query then

1. Shared Pool Latch is acquired to check for the parsed verion of this SQL.
2. If the Parsed Version is available then Library Cache Latch is acquired to protect the parsed version of that SQL for any modification. (SOFT Parse).
3. If the parsed version is not available, even then the Library Cache latch is acquired to parse the query and generate the execution plan (HARD Parse)

Are my assumptions wrong. If yes, then which all latch will be acquired for each steps. Please let me know for better understanding.

Thanks and Regards
Vivek

Dear Tom,

Thanks for your reply. I understood the concept of Library Cache Latch and Shared Pool Latch now. Would like to know what all things to be taken care off if the database is experiencing heavy Shared Pool Latch Contention ? What should be tuned ?

Thanks and Regards
Vivek

Dear Tom,

In the Statspack report "Latch Activity Section" it is written that PCT misses should be nearly 0 whereas the the pct misses on Library Cache and Shared Pool Latch are 1.8 and 2.1 respectively.

If the shared pool latch is used to allocate and protect memory allocation in the shared pool, then will the parameter parallel_automatic_tuning and configuring large_pool help eliminate the contention for this latch so that the memory for DBWR, LGWR and other processes are allocated in the large_pool.

Thanks and Regards
Vivek

there are some queries not using bind variables, I came to the developers for the issues. They claim becuase of using dynamic sql and there is no way to make the shared/bind work.
How do you think of their claims?

Hi Tom,
We are using Siebel CRM application and I want to use above technique to find sql statements which are not using bind variable. Many of the sql generated by siebel is more then 1000 character long..In that case how could use remove_constants procedure and stuff..

Please help

Thanks

Hi Tom,

I have a following code in Oracle Forms:

GO_BLOCK('BLOCK1') ;
SET_BLOCK_PROPERTY ('BLOCK1',default_where,'ref_key = ' || out_ref_key);
EXECUTE_QUERY;

This code always gets hard-parsed. Please suggest a way to Bind this query.

Thanks
Deepak

Thank you tom. The Oracle Forms solution works.

I have a select statement on a lookup table which is called from 100's of different forms, Stored Procedures, Oracle Reports, Triggers etc...

Is it possible to FORCE cursor sharing for 1 single statement without changing the database parameter, so as not to affect cursor_sharing for other statements in the Database?

Deepak

Because it is called from 1000's of different places. I mean,

Form1
Program Unit 1
select select_list from lu_table
from type = 'TYPE1' and code = 'CODE1' ;


Form2
Program Unit 1
select select_list from lu_table
from type = 'TYPE2' and code = 'CODE2' ;
...
...
Form n
Program Unit X
select select_list from lu_table
from type = 'TYPEX' and code = 'CODEX' ;


same goes with Stored Procedures, Oracle Reports, Table Triggers.

In such a case, is it OK to go with CURSOR_SHARING=FORCE ?

Tom,

After issuing
select sql_text_wo_constants, count(*)
from t1
group by sql_text_wo_constants
having count(*) > 50
order by 2

i noticed i have lot of entries like for different procedures

BEGIN MY_PROC_P1('#',:@,:RETCODE); END; 120
BEGIN MY_PROC-P2( '#', '#', '#', :@, 150
NULL, NULL, TO_DATE('#','#') );END;

As per my understanding procedures are autobinded, so why do i see entries like these.

Thanks.

Thanks for your prompt reply.

"but the CALLER of such procedures STILL NEEDS TO BIND THE CALL TO THE PROCEDURE
itself! "

does it mean bind variable has to be declared and then be passed to those procedures..

Much clearer now .. Thanks

I attended a seminar (NOT from oracle corporation) on database maintenance. Session was ended with following conclusions.

1. Rollback segments to be defined as n/4 ("n" # of users and NOT exceeding 50)
2. Ideally database should have 4 control files and also should be in seperate hdd.
3. Extents calculation, make it as single extend etc. (Myself know that this is myth, since I have gone through your forum - so please ignore this point)
4. DB_BLOCK_BUFFERS is low, users will NOT have enought memory to operate efficently
DB_BLOCK_BUFFERS is high, your system may begin to swap and may come to halt
5. Read Hit ratio below 90 are usually a sign of poor indexing (But distortion of hit ratio number is possible)
6.Data dictionary cache miss ratio 90-95 % good
7. Library cache hit ratio > 95% good.
8. Increasing sort_area_size to reduce I/O and determine the PER USER space allocated in main memory for each process to sort.

5) "read hit ratio"?
select (1-(sum(decode(name,'physical reads',value,0))/
(sum(decode(name,'db block gets',value,0))+
(sum(decode(name,'consistent gets',value,0))))))*100 Read_Hit_ratio
from v$sysstat;

6) you want to miss 90/95% of the time?

ohh. it is buffer miss followed by "hit ratio"

select sum(gets) gets, sum(getmisses) misses,
(1 - (sum(getmisses) /(sum(gets)+sum(getmisses))))*100 hit_ratio
from v$rowcache;

Point no. 5: Read hit ratio below 90-95 % means poor indexing. Do you agree? please answer.

I have checked our production server using your procedure remove_constants
results are:
select count(1) from t1;
resut --> 10990

select count(1) from
(select sql_text_wo_constants, count(*) cnt from t1
group by sql_text_wo_constants
having count(*) > 1
resut --> 481

select sum(cnt) from
(select sql_text_wo_constants, count(*) cnt from t1
group by sql_text_wo_constants
having count(*) > 1

result --> 8157

total parsing sqls - 10,990
sqls having repeated parsing - 481
sql repeated parsing with no. of times - 8157
(ie instead of 481, 8157 times parsed)

ratio = ( 481 / (10990- (8157-481)) )*100
= 14.51 %
Hence our production server sqls approx 85 % bind enabled and 15 % of places, still to be changed to make it as "bind enabled".
(So I have to take list sqls repeated in table t1 (arrived using remove constants) and I have to take it up with development team for changing the same.)

Please confirm my calculation above.

Exactly it is only tells how much % friendly bind sqls are there.
Thanks.

Then how to find out ratio.

Hi Tom,

We have severe problems revolving around Library Cache latch waits / sleeps. I wonder if you know of any way of finding the amount of time a Library Cache child latch is held,(which we have identified as #106), and to identify the respective SQL statements, in version 8.1.7.4.

I do appreciate that we are dealing with microseconds, but this child is using approx 97% of the waits on the system and we would hope therefore that it will repeat and / or be holding the child latch for a longer time.

We have already reduced the Library Cache Latch requests by over 40%, by code optimisation (less executions/more soft parses), but the sleeps have not decreased at all.

Tom,
We have very complicated pl/sql package in which the developer use the dynamic sql to insert data into the global temp table. This is used all day in our OLTP enviornment where application users search for the person with different input. When we had checked the statement
exececute and counts are same around 6000 meaning that many different versions of the sql in the cache instead of 1 or 2.

I had used the following query,

SELECT substr(sql_text,1,40) "SQL", count(*) ,
sum(executions) "TotExecs"
FROM v$sqlarea
WHERE executions < 5
GROUP BY substr(sql_text,1,40)
HAVING count(*) > 30
ORDER BY 2;

The code in PL/SQL package, is there any way we can tune this??

v_InsertStmt := 'INSERT INTO temp_person_search
(sequence_id, ' || 'membership_number, ' || 'account_individuals, ' || 'date_of_births, ' ||
'ages, ' ||
'address, ' ||
'phone_numbers, ' ||
'household_id, ' ||
'sort_individual_id) ' ||
'(SELECT ' || v_SequenceID || ', ' ||
'pkg_person_search.fn_get_current_member_num(h1.household_id), ' ||
'pkg_person_search.fn_get_household_members(h1.household_id), ' || 'pkg_person_search.fn_get_hhld_mem_birth_date(h1.household_id), ' ||
'pkg_person_search.fn_get_hhld_mem_age(h1.household_id), ' ||
'pkg_person_search.fn_get_household_address(h1.household_id), ' || 'pkg_person_search.fn_get_household_phone_nums(h1.household_id), ' || 'h1.household_id, ' ||
'pkg_person_search.fn_get_result_order_indv_id(h1.household_id) ' ||
'FROM household h1 ' ||
'WHERE h1.household_id IN (' || v_SQLStmt || '))';

EXECUTE IMMEDIATE v_InsertStmt;

Tom,
Thanks for the reply, but in our case the function and casting won't work as we build the where condition based on the customer input that input is different everytime


WHERE h1.household_id IN
( SELECT household_id FROM ( SELECT DISTINCT(h.household_Id) FROM household h WHERE (h.daytime_phone_num = '8454571465' OR h.evening_phone_num = '8454571465') ) WHERE ROWNUM < 253 ))

Another example

WHERE h1.household_id IN ( SELECT household_id FROM ( SELECT DISTINCT(h.household_Id) FROM household h WHERE h.current_bank_acct_num = '03173219' ) WHERE ROWNUM < 253 ))

I am geeting error while using casting with sys_context.
VAR1
-----
1,2,3,4

SELECT count(*)
FROM work_queue_item wqi, work_queue_status wqs
WHERE wqi.status_id = wqs.status_id
AND wqi.status_id IN (select * from table ( CAST( str2tbl ( sys_context( 'WQ_CTX', 's_status_id' ) ) as str2tableType )))

The wqi.status_id is Number data type.

The context was set in same session tho' pl/sql procedure. I was getting ORA-00902: invalid datatype error both way. Could you please show me where I am doing wrong?

Thanks,

As continue for above question why I need to cast?
Why the following statement not work for above conext value.

SELECT count(*) FROM work_queue_item wqi,work_queue_status wqs WHERE wqi.status_id = wqs.status_id
AND wqi.status_id IN ( to_number(sys_context( 'WQ_CTX', 's_status_id' )) )

Hi Tom,

I am running this query :
select w.sid,
c.name || ' (child #' || c.child# || ')' latch,
s.program,
s.machine || ', ' || s.process process,
p.spid ospid,
a.sql_text,
a.hash_value,
a.parse_calls,
a.executions
from v$session_wait w,
v$latch_children c,
v$session s,
v$process p,
v$sqlarea a
where w.event = 'latch free'
and c.addr = w.p1raw
and s.sid = w.sid
and p.addr = s.paddr
and a.address (+) = s.sql_address
/


and the output is

397 library iwserver@r3d3 r3d3, 13201 Begin 1055490975 6902 13560
cache (TNS V1-V3) 25409 Ptsm_Vas_General_Services3_Pkg
(child #7) .Ptsm_GeneralServices_Validate
(:txt0, :txt1,:txt2, :a0, :a1,
:a2, :a3, :a4,:b, :r, :s);
End;

567 library iwserver@r3d3 r3d3, 3032 Begin 1055490975 6902 13560
cache (TNS V1-V3) 2681 Ptsm_Vas_General_Services3_Pkg
(child #1) .Ptsm_GeneralServices_Validate
(:txt0, :txt1,:txt2, :a0, :a1,
:a2, :a3, :a4,:b, :r, :s);
End;
same statement / same hash_value different childs?

How come?

Here is the version:
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - 64bi
PL/SQL Release 10.1.0.3.0 - Production
CORE    10.1.0.3.0      Production
TNS for Solaris: Version 10.1.0.3.0 - Production
NLSRTL Version 10.1.0.3.0 - Production


Here is the trace file:

=====================
PARSING IN CURSOR #1 len=65 dep=0 uid=220 oct=6 lid=220 tim=387747479508 hv=4170283050 ad='9ae98b90'
update cust set lastLogin = sysdate where customer_id = :cid
END OF STMT
PARSE #1:c=0,e=93,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=387747479495
*** 2005-07-08 14:18:01.567
<b>WAIT #1: nam='latch: library cache' ela= 22360910 </b>p1=24488193544 p2=199 p3=0
BINDS #1:
 bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=00 oacfl2=0000 size=24 offset=0
   bfp=ffffffff7bb6d2a0 bln=22 avl=06 flg=05
   value=103313413
WAIT #1: nam='buffer busy waits' ela= 77925 p1=159 p2=392206 p3=1
WAIT #1: nam='buffer busy waits' ela= 12791 p1=547 p2=710649 p3=1
WAIT #1: nam='enq: TX - index contention' ela= 11388 p1=1415053316 p2=4587522 p3=301972
WAIT #1: nam='latch: enqueue hash chains' ela= 7739 p1=24840189800 p2=20 p3=0
EXEC #1:c=20000,e=22479366,p=0,cr=11,cu=17,mis=0,r=1,dep=0,og=1,tim=387769959129
WAIT #1: nam='SQL*Net message to client' ela= 9 p1=1413697536 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 453 p1=1413697536 p2=1 p3=0
STAT #2 id=1 cnt=0 pid=0 pos=1 obj=0 op='COUNT STOPKEY (cr=31 pr=0 pw=0 time=1043 us)'
STAT #2 id=2 cnt=0 pid=1 pos=1 obj=22585 op='TABLE ACCESS FULL WG2K_PLAYER_LOCKOUT (cr=31 pr=0 pw=0 time=1034 us)'
=====================
PARSING IN CURSOR #2 len=105 dep=0 uid=220 oct=3 lid=220 tim=387769961679 hv=1751757214 ad='9c679790'
select work, round_id, version, revision, variation, flags from work_in_progress where customer_id = :1
END OF STMT
PARSE #2:c=0,e=200,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=387769961661
BINDS #2:
 bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=00 oacfl2=0000 size=24 offset=0
   bfp=ffffffff7bb5ff78 bln=22 avl=06 flg=05
   value=103313413
EXEC #2:c=0,e=555,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=387769962607
WAIT #2: nam='SQL*Net message to client' ela= 4 p1=1413697536 p2=1 p3=0
FETCH #2:c=0,e=123,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=1,tim=387769963173
WAIT #2: nam='SQL*Net message from client' ela= 449 p1=1413697536 p2=1 p3=0
XCTEND rlbk=0, rd_only=0
WAIT #0: nam='log file sync' ela= 7090 p1=3563 p2=0 p3=0
WAIT #0: nam='SQL*Net message to client' ela= 5 p1=1413697536 p2=1 p3=0
WAIT #0: nam='SQL*Net message from client' ela= 418 p1=1413697536 p2=1 p3=0
XCTEND rlbk=1, rd_only=1
WAIT #0: nam='SQL*Net message to client' ela= 9 p1=1413697536 p2=1 p3=0
WAIT #0: nam='SQL*Net message from client' ela= 486 p1=1413697536 p2=1 p3=0
XCTEND rlbk=1, rd_only=1
WAIT #0: nam='SQL*Net message to client' ela= 3 p1=1413697536 p2=1 p3=0
WAIT #0: nam='SQL*Net message from client' ela= 768 p1=1413697536 p2=1 p3=0
STAT #1 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE  (cr=11 pr=0 pw=0 time=114758 us)'
STAT #1 id=2 cnt=1 pid=1 pos=1 obj=16837 op='TABLE ACCESS BY INDEX ROWID cust (cr=5 pr=0 pw=0 time=529 us)'
STAT #1 id=3 cnt=1 pid=2 pos=1 obj=52162 op='INDEX RANGE SCAN cust_IDX001 (cr=3 pr=0 pw=0 time=149 us)'
=====================

Any idea why Oracle spent so long on library cache latch considering that we're already using bind variables?

and why Oracle emited '*** 2005-07-08 14:18:01.567' before that waits?

Thanks a lot in advance 

Dear Tom
I was trying to find out the Sql statements not using bind variables with the procedure you had given. When I execute the function it returns with error ORA-06502. It is because of using multibyte characters(Japanese) in the query like table names or column names. Could you please help me with a solution for this.
For example:
select remove_constants('&#65324;') from dual;
gives error ORA-06502.

Thanks & Regards

we are seeing a lots of 'latch free' waits. Its the #1 wait. The cause is library cache latch contention. Its not practicle to change the code at the moment. The system is very complex. Any way to reduce the library cache latch waits?

There is no hard parsing at all.
We have cursor_sharing=SIMILAR

We don't have a test system where we can practically test anything. We'll hv to do it in production.

Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 58,660.73 1,936.94
Logical reads: 10,185.88 336.33
Block changes: 343.58 11.34
Physical reads: 429.88 14.19
Physical writes: 40.21 1.33
User calls: 3,393.25 112.04
Parses: 910.57 30.07
Hard parses: 0.02 0.00
Sorts: 51.59 1.70
Logons: 0.12 0.00
Executes: 1,643.58 54.27
Transactions: 30.29

% Blocks changed per Read: 3.37 Recursive Call %: 34.87
Rollback per transaction %: 1.90 Rows per Sort: 23.03

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.71 Redo NoWait %: 99.97
Buffer Hit %: 95.78 In-memory Sort %: 100.00
Library Hit %: 100.47 Soft Parse %: 100.00
Execute to Parse %: 44.60 Latch Hit %: 98.76
Parse CPU to Parse Elapsd %: 2.10 % Non-Parse CPU: 94.78

Shared Pool Statistics Begin End
------ ------
Memory Usage %: 71.22 71.42
% SQL with executions>1: 77.44 77.36
% Memory for SQL w/exec>1: 76.06 75.95

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
latch free 1,008,928 109,850 33.27
enqueue 126,613 62,231 18.84
buffer busy waits 51,496 38,175 11.56
db file sequential read 738,505 32,484 9.84
free buffer waits 31,495 30,784 9.32
-------------------------------------------------------------

SID SEQ# EVENT P1TEXT P1 P1RAW P2TEXT
---------- ---------- -------------------- ---------- ---------- ---------------- ------------------
1 58267 pmon timer duration 300 000000000000012C
2 39979 rdbms ipc message timeout 300 000000000000012C
3 22189 rdbms ipc message timeout 69 0000000000000045
6 528 rdbms ipc message timeout 180000 000000000002BF20
4 42730 rdbms ipc message timeout 300 000000000000012C
9 47573 db file sequential r file# 245 00000000000000F5 block#
ead

18 31212 db file sequential r file# 277 0000000000000115 block#
ead

13 270 PX Deq: Txn Recovery sleeptime/ 200 00000000000000C8 passes
Start senderid

14 270 PX Deq: Txn Recovery sleeptime/ 200 00000000000000C8 passes
Start senderid

22 270 PX Deq: Txn Recovery sleeptime/ 200 00000000000000C8 passes
Start senderid

5 8597 wait for stopper eve 0 00
nt to be increased

23 328 SQL*Net message to c driver id 1413697536 0000000054435000 #bytes
lient

7 2677 SQL*Net message from driver id 675562835 0000000028444553 #bytes
client

10 3009 SQL*Net message from driver id 1413697536 0000000054435000 #bytes
client

16 14370 SQL*Net message from driver id 1413697536 0000000054435000 #bytes
client

20 1448 SQL*Net message from driver id 1413697536 0000000054435000 #bytes
client

17 8526 SQL*Net message from driver id 1413697536 0000000054435000 #bytes
client

11 4241 SQL*Net message from driver id 675562835 0000000028444553 #bytes
client

12 3078 SQL*Net message from driver id 1413697536 0000000054435000 #bytes
client

Tom ,Please give your comments on above

<Tom>
Parses: 910.57 30.07

ouch, that hurts doesn't it.

what do you have session cached cursors set to? what is the session cursor
cache hits|count statistic showing?
</Tom>

session cached cursors is not set. We tried to set it to 100 , but doesn't look like it helped.

what is the session cursor cache hits|count statistic showing? --- Where do i see it?

Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
CPU used by this session 980,970 546.5 18.1
CPU used when call started 980,949 546.5 18.0
CR blocks created 5,850 3.3 0.1
Cached Commit SCN referenced 1,218 0.7 0.0
Commit SCN cached 89 0.1 0.0
DBWR buffers scanned 0 0.0 0.0
DBWR checkpoint buffers written 33,798 18.8 0.6
DBWR checkpoints 2 0.0 0.0
DBWR cross instance writes 25 0.0 0.0
DBWR free buffers found 0 0.0 0.0
DBWR fusion writes 10,635 5.9 0.2
DBWR lru scans 18 0.0 0.0
DBWR make free requests 72 0.0 0.0
DBWR revisited being-written buff 1 0.0 0.0
DBWR summed scan depth 0 0.0 0.0
DBWR transaction table writes 230 0.1 0.0
DBWR undo block writes 4,017 2.2 0.1
DFO trees parallelized 19 0.0 0.0
PX local messages recv'd 86 0.1 0.0
PX local messages sent 86 0.1 0.0
PX remote messages recv'd 92 0.1 0.0
PX remote messages sent 69 0.0 0.0
Parallel operations downgraded to 0 0.0 0.0
Parallel operations not downgrade 19 0.0 0.0
RowCR - row contention 25 0.0 0.0
RowCR attempts 1,009 0.6 0.0
RowCR hits 952 0.5 0.0
SQL*Net roundtrips to/from client 5,984,253 3,333.9 110.1
active txn count during cleanout 10,243 5.7 0.2
background checkpoints completed 1 0.0 0.0
background checkpoints started 2 0.0 0.0
background timeouts 2,148 1.2 0.0
branch node splits 5 0.0 0.0
buffer is not pinned count 9,944,004 5,539.8 182.9
buffer is pinned count 4,560,050 2,540.4 83.9
bytes received via SQL*Net from c 530,065,085 295,300.9 9,750.7
bytes sent via SQL*Net to client 841,800,537 468,969.7 15,485.1
calls to get snapshot scn: kcmgss 3,457,742 1,926.3 63.6
calls to kcmgas 252,079 140.4 4.6
calls to kcmgcs 4,396 2.5 0.1
change write time 32,627 18.2 0.6
cleanout - number of ktugct calls 9,568 5.3 0.2
cleanouts and rollbacks - consist 5,216 2.9 0.1
cleanouts only - consistent read 342 0.2 0.0
cluster key scan block gets 260,623 145.2 4.8
cluster key scans 151,531 84.4 2.8
commit cleanout failures: block l 54 0.0 0.0
commit cleanout failures: buffer 25 0.0 0.0
commit cleanout failures: callbac 21 0.0 0.0
commit cleanout failures: cannot 159 0.1 0.0
commit cleanouts 141,756 79.0 2.6
commit cleanouts successfully com 141,497 78.8 2.6
commit txn count during cleanout 2,120 1.2 0.0
consistent changes 10,281 5.7 0.2
consistent gets 17,630,040 9,821.8 324.3
consistent gets - examination 8,444,648 4,704.5 155.3
Instance Activity Stats for DB: ECA Instance: eca1 Snaps: 22323 -22324

Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
current blocks converted for CR 1 0.0 0.0
cursor authentications 14 0.0 0.0
data blocks consistent reads - un 10,047 5.6 0.2
db block changes 616,730 343.6 11.3
db block gets 670,863 373.7 12.3
deferred (CURRENT) block cleanout 66,423 37.0 1.2
dirty buffers inspected 108,718 60.6 2.0
enqueue conversions 405 0.2 0.0
enqueue deadlocks 5 0.0 0.0
enqueue releases 232,629 129.6 4.3
enqueue requests 231,908 129.2 4.3
enqueue timeouts 3 0.0 0.0
enqueue waits 17,058 9.5 0.3
exchange deadlocks 17,159 9.6 0.3
execute count 2,950,234 1,643.6 54.3
free buffer inspected 1,527,907 851.2 28.1
free buffer requested 1,049,648 584.8 19.3
gcs messages sent 956,297 532.8 17.6
ges messages sent 56,557 31.5 1.0
global cache blocks lost 65 0.0 0.0
global cache convert time 658,050 366.6 12.1
global cache converts 73,128 40.7 1.4
global cache cr block build time 6,703 3.7 0.1
global cache cr block flush time 24,781 13.8 0.5
global cache cr block receive tim 65,111 36.3 1.2
global cache cr block send time 11,616 6.5 0.2
global cache cr blocks received 41,016 22.9 0.8
global cache cr blocks served 74,157 41.3 1.4
global cache current block flush 32,034 17.9 0.6
global cache current block pin ti 1,903,565 1,060.5 35.0
global cache current block receiv 354,068 197.3 6.5
global cache current block send t 7,540 4.2 0.1
global cache current blocks recei 103,002 57.4 1.9
global cache current blocks serve 43,077 24.0 0.8
global cache defers 313 0.2 0.0
global cache freelist waits 144 0.1 0.0
global cache get time 1,082,145 602.9 19.9
global cache gets 840,060 468.0 15.5
global cache prepare failures 0 0.0 0.0
global cache skip prepare failure 25,111 14.0 0.5
global lock async converts 733 0.4 0.0
global lock async gets 483 0.3 0.0
global lock convert time 1,712 1.0 0.0
global lock get time 5,553,240 3,093.7 102.2
global lock releases 740,602 412.6 13.6
global lock sync converts 1,311 0.7 0.0
global lock sync gets 740,244 412.4 13.6
hot buffers moved to head of LRU 365,778 203.8 6.7
immediate (CR) block cleanout app 5,577 3.1 0.1
immediate (CURRENT) block cleanou 16,050 8.9 0.3
index fast full scans (full) 1 0.0 0.0
index fetch by key 2,043,240 1,138.3 37.6
index scans kdiixs1 1,258,729 701.2 23.2
leaf node 90-10 splits 22 0.0 0.0
leaf node splits 105 0.1 0.0
logons cumulative 221 0.1 0.0
Instance Activity Stats for DB: ECA Instance: eca1 Snaps: 22323 -22324

Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
messages received 17,597 9.8 0.3
messages sent 17,597 9.8 0.3
no buffer to keep pinned count 0 0.0 0.0
no work - consistent read gets 6,807,056 3,792.2 125.2
opened cursors cumulative 1,411,846 786.5 26.0
parse count (failures) 6 0.0 0.0
parse count (hard) 30 0.0 0.0
parse count (total) 1,634,474 910.6 30.1
parse time cpu 51,185 28.5 0.9
parse time elapsed 2,435,356 1,356.7 44.8
physical reads 771,637 429.9 14.2
physical reads direct 95 0.1 0.0
physical writes 72,171 40.2 1.3
physical writes direct 65 0.0 0.0
physical writes non checkpoint 70,309 39.2 1.3
pinned buffers inspected 418,098 232.9 7.7
prefetch clients - default 6 0.0 0.0
prefetched blocks 19,697 11.0 0.4
prefetched blocks aged out before 37 0.0 0.0
process last non-idle time 252,581,568,265 140,713,965.6 4,646,289.1
queries parallelized 19 0.0 0.0
recursive calls 3,261,006 1,816.7 60.0
recursive cpu usage 448,622 249.9 8.3
redo blocks written 220,573 122.9 4.1
redo buffer allocation retries 52 0.0 0.0
redo entries 326,144 181.7 6.0
redo log space requests 89 0.1 0.0
redo log space wait time 1,341 0.8 0.0
redo ordering marks 4,253 2.4 0.1
redo size 105,296,012 58,660.7 1,936.9
redo synch time 1,693,641 943.5 31.2
redo synch writes 54,058 30.1 1.0
redo wastage 4,083,300 2,274.8 75.1
redo write time 140,164 78.1 2.6
redo writer latching time 3,892 2.2 0.1
redo writes 15,688 8.7 0.3
remote instance undo block writes 25 0.0 0.0
rollback changes - undo records a 9,615 5.4 0.2
rollbacks only - consistent read 605 0.3 0.0
rows fetched via callback 1,296,280 722.2 23.9
session connect time 278,351,372,041 155,070,402.3 5,120,329.9
session logical reads 18,283,663 10,185.9 336.3
session pga memory max 52,871,856 29,455.1 972.6
session uga memory 26,238,760 14,617.7 482.7
session uga memory max 107,014,712 59,618.2 1,968.6
shared hash latch upgrades - no w 1,775,368 989.1 32.7
shared hash latch upgrades - wait 635 0.4 0.0
sorts (disk) 0 0.0 0.0
sorts (memory) 92,599 51.6 1.7
sorts (rows) 2,133,009 1,188.3 39.2
summed dirty queue length 916,778 510.7 16.9
switch current to new buffer 83,353 46.4 1.5
table fetch by rowid 5,184,007 2,888.0 95.4
table fetch continued row 88,547 49.3 1.6
table scan blocks gotten 2,853,247 1,589.6 52.5
table scan rows gotten 2,460,559 1,370.8 45.3
Instance Activity Stats for DB: ECA Instance: eca1 Snaps: 22323 -22324

Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
table scans (long tables) 0 0.0 0.0
table scans (short tables) 354,522 197.5 6.5
transaction lock background get t 1 0.0 0.0
transaction lock background gets 136 0.1 0.0
transaction lock foreground reque 136 0.1 0.0
transaction lock foreground wait 1,029 0.6 0.0
transaction rollbacks 729 0.4 0.0
user calls 6,090,875 3,393.3 112.0
user commits 53,328 29.7 1.0
user rollbacks 1,034 0.6 0.0
workarea executions - onepass 0 0.0 0.0
workarea executions - optimal 184,874 103.0 3.4
write clones created in backgroun 1 0.0 0.0
write clones created in foregroun 1,542 0.9 0.0
-------------------------------------------------------------


STATISTIC# NAME CLASS VALUE
---------- ---------------------------------------------------------------- ---------- ----------
208 session cursor cache hits 64 0
209 session cursor cache count 64 0

<do you have session_caches_cursors *SET* or not.>
No.
However, we tried it setting to a value of 100 in QA Stress region, but did not find any difference.

Can someone who is running 9i RAC on Solaris share his statspack report with me. I just want to compare ours to get an idea whether our values are similar or way-off. Also, it'll be helpful if you can mention how many CPUs you have and how much is your cpu utilization?

Thanks in advance.

<do you have session_caches_cursors *SET* or not.>
No.
However, we tried it setting to a value of 100 in QA Stress region, but did not
find any difference.


Followup:

define "did not find any difference"

-- Test was done by my colleague and he said he did not find any difference in terms of (i) perses/second (ii) library cache latch free waits and (iii) CPU utilization.

Hi Tom, when trying to run your example at the top I get this 9.2.0.5 Linux Redhat

SQL> create table t1 as select sql_text from v$sqlarea;

Table created.

SQL> alter table t1 add sql_text_wo_constants varchar2(1000);

Table altered.

SQL> create or replace function
  2  remove_constants( p_query in varchar2 ) return varchar2
  3  as
  4      l_query long;
  5      l_char  varchar2(1);
  6      l_in_quotes boolean default FALSE;
  7  begin
  8      for i in 1 .. length( p_query )
  9      loop
 10          l_char := substr(p_query,i,1);
 11          if ( l_char = '''' and l_in_quotes )
 12          then
 13              l_in_quotes := FALSE;
 14          elsif ( l_char = '''' and NOT l_in_quotes )
 15          then
 16              l_in_quotes := TRUE;
 17              l_query := l_query || '''#';
 18          end if;
 19          if ( NOT l_in_quotes ) then
 20              l_query := l_query || l_char;
 21          end if;
 22      end loop;
 23      l_query := translate( l_query, '0123456789', '@@@@@@@@@@' );
 24      for i in 0 .. 8 loop
 25          l_query := replace( l_query, lpad('@',10-i,'@'), '@' );
 26          l_query := replace( l_query, lpad(' ',10-i,' '), ' ' );
 27      end loop;
 28      return upper(l_query);
 29  end;
 30  /

Function created.

SQL>
SQL> update t1 set sql_text_wo_constants = remove_constants(sql_text);
update t1 set sql_text_wo_constants = remove_constants(sql_text)
                                      *
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "PERFSTAT.REMOVE_CONSTANTS", line 10


If i change
 
l_char  varchar2(1);

to l_char  varchar2(10);

it works but I am unsure if I have 'broken' anything

Thanks
 

you have a multibyte character set.... your fix was good - or you could have

ops$tkyte@ORA10GR2> declare l_char varchar2(1 CHAR ); begin null; end;
  2  /
 
PL/SQL procedure successfully completed.


varchar2(1 CHAR) 

I have library cache contention but no bind variable issues, or should I say it doesn't look like any serious bind variable issues. Can it be something else? I ran your script, the max number of duplicate statements was 4 closely followed by some 2's, and there were only about 80 statments, yet everything is sitting on a latch free library cache event. in just 9 sessions. Shared pool is 150MB.

Tom,
In Ch.9 of your book Effective Oracle By Design, you mention that native dynamic sql is parsed every time. I've noticed an increase in latch free wait events when a program that uses excessive NDS is run even though the sql uses binds.

Could that be the source of the latch contention?

I really understood the concept and I was able to find
out the sql which is causing high parsing
Thanks....TOM

Hi Tom,
my database had this wait spike on the top latch# wait report, i searched metalink and have not found useful information about the detail of this latch, could you give me some highlight about this latch, what is it used, what activities in applications can cause this latch? Or maybe you have URL of it somewhere?
thanks a lot!

hi tom,
I am almost get out oh the hard parses. But i still lot of soft parses.

What would you recommend to start my war on the soft parses?

Hi Tom, confused over the output I am getting from the original script

SQL> select sql_text_wo_constants, count(*)
  from t1
 group by sql_text_wo_constants
having count(*) > 100
 order by 2


SQL_TEXT_WO_CONSTANTS
------------------------------------------------------------------------------------------------------------------------------
  COUNT(*)
----------
GRANT SELECT,INSERT
       226

TRUNCATE TABLE SYS.ORA_TEMP_@_DS_@
       251

But when I try to see what that tanle is..

SQL> select object_name from dba_objects where object_name like 'ORA_TE%';

no rows selected


Do you happen to know what that ORA_TEMP_@_DS_@ is?

It is also in a few other queries which come up as not using binds
 

Hi,
I did not find any better related thread for posting my question, that's why I am posting here.

We are facing library cache lock very frequently. We have used the following sqlto track which user is waiting on which object due to library cache lock or pin.

SELECT /*+ ordered */ w1.sid waiting_session, h1.sid holding_session,
w.kgllktype lock_or_pin,
w.kgllkhdl address, DECODE(h.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3,
'Exclusive', 'Unknown') mode_held,
DECODE(w.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
'Unknown') mode_requested,h1.sql_hash_value ,lock_id1 object_waited_on
FROM dba_kgllock w, dba_kgllock h, v$session w1, v$session h1 , dba_lock_internal dl
WHERE (((h.kgllkmod != 0) AND (h.kgllkmod != 1) AND ((h.kgllkreq = 0)
OR (h.kgllkreq = 1)))
AND (((w.kgllkmod = 0) OR (w.kgllkmod= 1)) AND ((w.kgllkreq != 0)
AND (w.kgllkreq != 1))))
AND w.kgllktype = h.kgllktype AND w.kgllkhdl = h.kgllkhdl
AND w.kgllkuse=w1.saddr AND h.kgllkuse=h1.saddr
and dl.mode_requested<>'None'
and dl.mode_requested<>dl.mode_held
and w1.sid=dl.SESSION_ID

WAIT_SES HOLD_SES LOCK_OR_PIN ADDRESS MODE_HELD MODE_REQUESTED SQL_HASH_VALUE OBJECT_WAITED_ON
110 80 Lock E54B622C Exclusive Share 1057805097 UPMARKET_EI_MV
112 110 Lock E56289D8 Share Exclusive 4103562029 SOLD_INFL_COMP_CPT_MV
76 110 Lock E857A16C Share Exclusive 4103562029 FACT_FORECAST_SUMMARY_MV
76 52 Lock E857A16C Share Exclusive 2349705177 FACT_FORECAST_SUMMARY_MV
52 80 Lock E54B622C Exclusive Share 1057805097 UPMARKET_EI_MV

In the above output you can see that session 110 is holding share lock on two different MVIEWS at same time - one is FACT_FORECAST_SUMMARY_MV and another one is SOLD_INFL_COMP_CPT_MV. This is not clear to me . At the same
time how a session can held share lock on two different mviews ? Under what circumstances share lock on mview is used? Can programmer cotrol this lock manually means can we keep this type of lock for sometimes or it is maintained by Orcale only and programmer does not have any control over this lock? Could you explain this shared lock on MVIEWS with example please ? How share lock on two diffrent MVIEWS at same time by single session get maintained by oracle? please give one example.

Thanks
Deba

Dave from UK:

Are you using DBMS_STATS? I regularly see ORA_TEMP_@_DS_@ tables in my v$session_longops table when I run dbms_stats.gather_table_stats on large tables.

Hi,
We refresh soem views at specific time interval using dbms_mviews.refresh. The mviews are not in refresh group. This is ongoing process. Some process capture data from OLTP ( CDC is configured ) thruogh database link, some process refresh mviews and some process calculate data and store them in different tables.

What we want to know when shar lock on mview gets generated ? Accordingly we can see the codes. Is there any issue while refreshing mviews. We tried to simulate this but failed. We don't know when share lock on mview gets generated.

Thanks
Deba

Hi,

The use of MVIEWS is all follows :

1) We refresh them at regular intervals
2) We select data from MVIEWS ( either using SELECT only or INSER INTO ... SELECT ... )

We can see lot of sessions are waiting on 2 or 3 sessions. These 2 or 3 sessions generally held shared lock on MVIEWS. Now I want to know when Oracle acquire share lock on MVIEWS. When we select data there should not be any share lock on MVIEW. There will be exclusive lock on MVIEWS when those get refreshed. So when blocking sessions held share lock on MVIEWS then it means that there is no refresh for MVIEWS. It puzled me why session held share lock on MVIEWS though it is not doing any refresh.So I want to know

a) Under which circumstances share lock on MVIEWS get generated ? Is it possible to simulate ? If so then how ?

b) Some MVIEWS are based on other other MVIEWS also.Under any situations share lock on MVIEWS can be generated due to change in underlying MVIEWS or underlying tables ? Changes mean any changes ( either data or structure ).

Deba

Hi,

There should be not be any share lock on MVIEW if I read from any MVIEW. Could you tell me when share lock on MVIEW generates ?

What should we look (and also where should we look) when there is a library cache lock when running a dbms_stats package on a half a million row table?

The table is a non-partitioned and has one primary key on that. The library cache lock happens even if I use "analyze table" instead of DBMS_STATS. Also, when I tried to change the degree value by using alter table, again it hangs with the same wait event.

When it is waiting on library cache lock, even a simple select statement on that table just hangs.

We have found out the work around by creating a temp table, create a primary key on that, drop the existing table and rename the temp table back to the existing one. The analyze or dbms_stats on that temp table worked just fine.


The database is 10.0.1 and running on a 5 node RAC.

Interestingly, the library cache happens on only that particular table and when I tried analyzing other big tables it just analyzes fine.

Hi Tom,

Could you please explain the difference between the following latch.

LATCH.library cache pin
LATCH.shared pool
LATCH.library cache pin allocation
LATCH.library cache

It is very confusing when each type of latch is acquired.

Thanks and Regards,

Raj

This has been an intersestion thread to me. I would like to share a problem which I encountered recently

We have a a view which does quite a number of PL/SQL function calls (should not do. trying to get rid of the function calls). A couple of function calls uses execute immediate statements. These functions are quite bulky in nature. The packages containing these functions are bulky themselves

Whenever a SQL is issued against this view it holds the library cache lock for a long time. I have learned from Steve Adams' site that ' a library cache lock is also held when a referenced object is not pinned'.

My questions are:
1. Does a PL/SQL call from a SQL statement result in library cache lock owing to context switch?
2.Could it be that the package (in chunks) gets paged out and the session holds the lock and waits for the package to be pinned again?
3. Is it the 'execute immediate' statements causing the problem?
4. Is 'execute immediate' statement condidered a DML (even if it does no DML operation)? If so, then it might break the exisisting parse locks (again from Steve Adams)(occuring many times over for as many number of rows) requiring a reparse and hence the lock?

Yor views and clarification would resolve my state of confusion. Apologies, if I have misinterpreted Steve Adams in appropriately and used it in the wrong context of my problem.

Thanks in advance for your valuable time and your unconditional service to the Oracle community.

Regards
Ray

The code provided to check if there is a Lbrary cache latch contention was very useful.

Is this paramater has any effect on the library cache latch contention?
If yes, Could you please explain me how this effects?

Thanks

Hi Tom,

I'm trying to run your script to determine whether bind variables are being used or not, and I get ORA-01401. We are running 11i , rdbms 9.2.0.6 , Red Hat Linuz 2.1.  Could you please help me out.  Thanks.

SQL> create table t1 as select sql_text from v$sqlarea;

Table created.

SQL> 
SQL> alter table t1 add sql_text_wo_constants varchar2(1000);

Table altered.

SQL> create or replace function 
  2  remove_constants( p_query in varchar2 ) return varchar2
  3  as
  4      l_query long;
  5      l_char  varchar2(1);
  6      l_in_quotes boolean default FALSE;
  7  begin
  8      for i in 1 .. length( p_query )
  9      loop
 10          l_char := substr(p_query,i,1);
 11          if ( l_char = '''' and l_in_quotes )
 12          then
 13              l_in_quotes := FALSE;
 14          elsif ( l_char = '''' and NOT l_in_quotes )
 15          then
 16              l_in_quotes := TRUE;
 17              l_query := l_query || '''#';
 18          end if;
 19          if ( NOT l_in_quotes ) then
 20              l_query := l_query || l_char;
 21          end if;
 22      end loop;
 23      l_query := translate( l_query, '0123456789', '@@@@@@@@@@' );
 24      for i in 0 .. 8 loop
 25          l_query := replace( l_query, lpad('@',10-i,'@'), '@' );
 26          l_query := replace( l_query, lpad(' ',10-i,' '), ' ' );
 27      end loop;
 28      return upper(l_query);
 29  end;
 30  /

Function created.

SQL> update t1 set sql_text_wo_constants = remove_constants(sql_text);
update t1 set sql_text_wo_constants = remove_constants(sql_text)
       *
ERROR at line 1:
ORA-01401: inserted value too large for column



Thanks. 

Thanks Tom. I increased it and it worked of course! But I'm curious why I had to increase it. I had read your explanation of the code : "My remove constants turns any character string literal into '#' and any number into @" - so after the translation the length of t1.sql_text_wo_constants and v$sqlarea.sql_text should be the same, right ?  Obviously there is something I am missing here - could you please help explain.

The script revealed :


SQL_TEXT_WO_CONSTANTS
------------------------------
  COUNT(*)
----------
SAVEPOINT ORACLE_SVPT_@
       197


SQL> select sql_text from v$sql
  2  where upper(sql_text) like 'SAVEPOINT ORACLE_SVPT_%';

SQL_TEXT
------------------------------------------------------------------
SAVEPOINT ORACLE_SVPT_111
SAVEPOINT ORACLE_SVPT_34
SAVEPOINT ORACLE_SVPT_198
SAVEPOINT ORACLE_SVPT_125
SAVEPOINT ORACLE_SVPT_189
SAVEPOINT ORACLE_SVPT_200
SAVEPOINT ORACLE_SVPT_136
........

This goes on as SAVEPOINT ORACLE_SVPT_<nnnnn>....  197 rows.

From reading your excellent info on binding, I think the reason this is coming up is because this must be a dynamic statement where the savepoint name is NOT a bind parameter. Please correct me if I am wrong. 
This is a 11i application, so I am not sure if I can "fix" the app - is there any other way to fix this ?

As always thanks so much for the wealth of info on binding - great eye opener! 

Yes, I see now - I hadn't thought of the empty string, and, neither did I think about the effects of changing the name of the savepoint!! Thanks for the prompt response!

Instead of
"create table t1 as select sql_text from v$sqlarea;" do you think we should have
"create table t1 as select sql_text from v$sqlarea where users_executing > 0;" ?

SQLs in v$sqlarea not currently executing should have no impact on the performance at this time right?

The following is the output of v$parameter.

108 shared_pool_size 6 0
110 large_pool_size 6 0
112 java_pool_size 6 0
114 streams_pool_size 6 0
116 shared_pool_reserved_size 6 7759462
404 buffer_pool_keep 2

In one of the databases, it was revelation, that shared pool size is 0. according to me it could cause lots of contention in library cache.
pls advice

or you could be using automatic sga sizing....


ops$tkyte%ORA10GR2> show parameter pool

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_keep                     string
buffer_pool_recycle                  string
global_context_pool_size             string
java_pool_size                       big integer 0
large_pool_size                      big integer 0
olap_page_pool_size                  big integer 0
shared_pool_reserved_size            big integer 7969177
shared_pool_size                     big integer 0
streams_pool_size                    big integer 0
ops$tkyte%ORA10GR2> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 580M
sga_target                           big integer 580M
ops$tkyte%ORA10GR2>


I have a 580m sga, Oracle is figuring out how much to put into each pool and can/will change its mind over time. 

version is 10Gr2 on linux Redhat

Tom,

I recently read a paper by Cary Millsap about how works the Buffer Cache and how a Hr > 95 (more or less) could point a bad sql and a very cpu usage. It is a very good article. Then I read that Hit ratios very very high is not always good. Mi question is why is too bad a very high hit ratio in the Shared Pool? In Buffer Cache now I can understand why but not in the Shared Pool. Could you please clarify me this point?

Thanks in advance Tom.

Sorry, I would refer to the library cache. When Cary Millsap talk about that hit ratios, in general, are not useful to lead a tuning, the case of study was the Buffer Cache. I don´t know if in any other areas like Shared Pool -> Library Cache, PGA ... etc this issue is similar, i.e. imagine mi H.r. in my library cache is close to 100%. Could I assert that there is no problem with my library cache?

Thank you Tom.

Thank you Tom. Your example is simple and very good. As always.

Tom,
When memory is needed oracle allocates from shared pool. If it does not find it then it removes unused sql. LRU
Question : Apart from packages/procedures PINNED/KEPT in memory what else cannot be removed when memory is needed?
I am not sure about session memory as decided by param session_cached_cursors can be removed.

About pinning objects : It was for oracle7 and it was supposed to improve with 8i/9i since memory in pieces can be allocated. Something like if total memory needed is 40K then it could get 4 chunks of 10K that are not contiguous and then satisfy the request. Correct me if I am wrong. So the question is in that case why do we keep getting this error in 8i and even on 9i. Must be some explanation. I think there is a minimum allocation it does. For example if I give a short sql like : 'select * from dual' which does not need much memory then I think it still allocates a bigger minimum amount. How much is this minimum amount of memory. Is it 1K. Is it determined by some params.
Since memory used by old sql which is not used can be reused. LRU algorithm or whatever : And at any given time a small number of sql in memory is being actively used : So it can always remove the old sql and use the memory : so less chance of ORA-4031 unless there are lot of active processes. However we have seen serious ORA-4031 errors even in case of very less active processes. Sometimes needing bounce.
Other than PINNED objects and session_cached_cursors I think shared pool sql can be removed and yet we get this ORA-4031 error.We do see a lot of inactive processes like over 400 inactive and very less active like less than 30. So are the inactive ones holding on to session_cached_cursors. No, there is no way to reduce the inactive processes.

Tom,

1. Which is worse, parsing a 5000-line SQL 2 times or parsing a 1 line SQL 500 times?
2. To parse 2 5000-line SQL, is it posible to say which is worse than the other? What makes one worse than the other in terms of negatively impacting the system?

John

A handy script and an interesting thread

Just came other this on metalink forum:

BEGIN PKG_NAME(:DBP_AABEG,:DBP_AABEH,:DBP_AABEI); END;
BEGIN PKG_NAME(:DBP_AABGQ,:DBP_AABGR,:DBP_AABGS); END;
BEGIN PKG_NAME(:DBP_AABMJ,:DBP_AABMK,:DBP_AABML); END;

So - as you can see, just using bind variables is sometimes makes no difference from not using them at all :)

eh-heh, but you'll get same hard parsing on every statement regardless...

my point was - "just use bind variables" sometimes doesn't solve hard parsing issues :)

"there are many reasons why "you bind but it is not shareable" - from optimizer related settings, to bind mismatches, to nls settings, to security settings, to ....."

ok, i suppose you was not looking at my previous review...

here:

BEGIN PKG_NAME(:DBP_AABEG,:DBP_AABEH,:DBP_AABEI); END;
BEGIN PKG_NAME(:DBP_AABGQ,:DBP_AABGR,:DBP_AABGS); END;
BEGIN PKG_NAME(:DBP_AABMJ,:DBP_AABMK,:DBP_AABML); END;

they have *unique* bind variable names for each statement - seems that they just auto-generate them.

no matter what - you'll get hard parse each time here. That was my point.

The bind variable names matter? I thought you could call your bind variable anything and the difference was just that you were using one vs no binds.

Alexander, the proof is very easy : remember, Oracle SQL processing IS based on text-recognizing, and different bind variables IMPLIES in differente texts, thus DIFFERENT hashes... See, in the SAME machine, SAME user, same everything :

scott@BDDEV:SQL>variable x number;
scott@BDDEV:SQL>variable y number;
scott@BDDEV:SQL>select /*+ TEST_01 */ loc, deptno, deptno + 10 result from dept where deptno = :x;

scott@BDDEV:SQL>select /*+ TEST_01 */ loc, deptno, deptno + 10 result from dept where deptno = :y;


system@BDDEV:SQL>select * from v$sqlarea where sql_text like 'select /*+ TEST_0%';

SQL_TEXT
------------------------------------------------------------      SHARABLE_MEM     PERSISTENT_MEM        RUNTIME_MEM              SORTS      VERSION_COUNT    LOADED_VERSIONS      OPEN_VERSIONS      USERS_OPENING            FETCHES         EXECUTIONS    USERS_EXECUTING              LOADS FIRST_LOAD_TIME          INVALIDATIONS        PARSE_CALLS         DISK_READS        BUFFER_GETS     ROWS_PROCESSED       COMMAND_TYPE OPTIMIZER_MODE               PARSING_USER_ID  PARSING_SCHEMA_ID      KEPT_VERSIONS ADDRESS                  HASH_VALUE MODULE                                                                  MODULE_HASH ACTION                                                                  ACTION_HASH SERIALIZABLE_ABORTS           CPU_TIME       ELAPSED_TIME I        CHILD_LATCH
------------------ ------------------ ------------------ ------------------ ------------------ ------------------ ------------------ ------------------ ------------------ ------------------ ------------------ ------------------ ------------------- ------------------ ------------------ ---------------------
select /*+ TEST_01 */ loc, deptno, deptno + 10 result from dept where deptno = :x
             17187               1208               2464                  0                  1                  1                  0                  0                  1                  1                  0                  1 2006-11-17/14:51:16                  0                  1                  0                  0                  0                  3 CHOOSE                                    22                 22                  0 C000000079996BC0         3582669877 SQL*Plus                                                                 -625018272                                                                          -265190056                   0                  0                925 N                  2

select /*+ TEST_01 */ loc, deptno, deptno + 10 result from dept where deptno = :y
             17595               1208               2464                  0                  1                  1                  1                  1                  1                  1                  0                  1 2006-11-17/14:51:16                  0                  1                  0                  0                  0                  3 CHOOSE                                    22                 22                  0 C000000079AA02E8         4254623821 SQL*Plus                                                                 -625018272                                                                          -265190056                   0                  0                889 N                  2


==> See ? Two DIFFERENT statements, two hard parses, only due DIFFERENT bind names...

 Regards,
 
   Chiappa 

Thanks for all the information provided.

I suspect that the application that is running against the databases that I look after is not using bind variables.

I have tried running your script remove_constants but I get
the following errors

Function created.

update t1 set sql_text_wo_constants = remove_constants(sql_text)
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SYSTEM.REMOVE_CONSTANTS", line 10



SQL_TEXT_WO_CONSTANTS COUNT(*)
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------
2798

1 row selected.

I have looked in the table T1, below is an example of the SQL returned

select u.EMP_NO, g.GROUP_ID,g.ACCESS_OWN_DATA,g.CONDITION_NAME,g.COND_SUCCESS_ACTION,g.COND_FAILURE_ACTION, g.REPORTEE_SEC, u.USER_PWD, u.SECURITY_OFFICER, u.ADMIN_OPTIONS, u.MODULE_OPTIONS from PS_USERS u, PS_GROUPS g where u.USER_ID='LWPSE9' and u.GROUP_ID=g.GROUP_ID


select u.EMP_NO, g.GROUP_ID,g.ACCESS_OWN_DATA,g.CONDITION_NAME,g.COND_SUCCESS_ACTION,g.COND_FAILURE_ACTION, g.REPORTEE_SEC, u.USER_PWD, u.SECURITY_OFFICER, u.ADMIN_OPTIONS, u.MODULE_OPTIONS from PS_USERS u, PS_GROUPS g where u.USER_ID='PACE04' and u.GROUP_ID=g.GROUP_ID


select u.EMP_NO, g.GROUP_ID,g.ACCESS_OWN_DATA,g.CONDITION_NAME,g.COND_SUCCESS_ACTION,g.COND_FAILURE_ACTION, g.REPORTEE_SEC, u.USER_PWD, u.SECURITY_OFFICER, u.ADMIN_OPTIONS, u.MODULE_OPTIONS from PS_USERS u, PS_GROUPS g where u.USER_ID='PACE17' and u.GROUP_ID=g.GROUP_ID

The SQL is identical apart from the following

u.USER_ID='LWPSE9'
u.USER_ID='PACE17'
u.USER_ID='PACE04'

If the application was using bind variables would the results in the table T1 show a single entry for this query instead of three and should I expect to see

u.USER_ID='LWPSE9'
u.USER_ID='PACE17'
u.USER_ID='PACE04'

replaced with something like

u.USER_ID=:user

Tom,

The gethitratios in library cache are extremely low.. Instead of being around 90% they are less than 1%. Please shortly explain what is the main reason. Thanks.

NAMESPACE GETHITRATIO

SQL AREA 0.96008153
TABLE/PROCEDURE 0.998642242
BODY 0.998955199
TRIGGER 0.999912234
INDEX 0.46969697
CLUSTER 0.993840985
OBJECT 1
PIPE 1
JAVA SOURCE 1
JAVA RESOURCE 1
JAVA DATA 1

Tom,

When I multiply by 100, all other gethitratios are ok but the index is 46.96%. Is that an indication latche contention is occuring when indexes are used? Any means to correct it? The schema has tables with unindexed foreign keys. On the average, a deadlock occurs once every month in the database. Thanks ...

Tom,

Last week there was Library cache lock on a table because of which we were not able to even select that table.We thought that the table was dropped by some one but after careful investigation we found it as library cache lock.

1)I am wondering about How can library cache lock will disable select/describe on table?

2)What all possible reasons to get library cache lock on table and disable readonly also on that table

I used the following steps to find the SID which is causing block but interestingly this user has only read only access on that table.It can not DML/DDL on that table



step1:

select /*+ ordered use_nl(lob pn ses) */
decode(lob.kglobtyp, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
11, 'PACKAGE BODY', 12, 'TRIGGER',
13, 'TYPE', 14, 'TYPE BODY',
19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',
22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
32, 'INDEXTYPE', 33, 'OPERATOR',
34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
42, 'MATERIALIZED VIEW',
43, 'DIMENSION',
44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',
48, 'CONSUMER GROUP',
51, 'SUBSCRIPTION', 52, 'LOCATION',
55, 'XML SCHEMA', 56, 'JAVA DATA',
57, 'SECURITY PROFILE', 59, 'RULE',
62, 'EVALUATION CONTEXT',
'UNDEFINED') object_type,
lob.kglnaobj object_name,
pn.kglpnmod lock_mode_held,
pn.kglpnreq lock_mode_requested,
ses.sid,
ses.serial#,
ses.username
from v$session_wait vsw,
x$kglob lob,
x$kglpn pn,
v$session ses
where vsw.event = 'library cache lock'
and vsw.p1raw = lob.kglhdadr
and lob.kglhdadr = pn.kglpnhdl
and pn.kglpnmod != 0
and pn.kglpnuse = ses.saddr

Step 2:
select spid as thread, sid, osuser, s.program
from sys.v_$process p, sys.v_$session s
where p.addr = s.paddr
and s.sid=<sid>


Step 3:
kill -9 <pid>

Thanks

Tom,
i was asked to improve the performance on the materialized-views -MVs build. the MVs have rebuilt every night (do not ask why). they do the one after other. i thought if build them in parallel would be much fast. so i have been testing the parallel MVs build. and found most of the build sessions wait on "library cache lock". i used query to find what are the objs being locked:
select oracle_username, os_user_name, session_id, process,
locked_mode, o.owner, o.object_type, o.object_name
from sys.dba_objects o,
v$locked_object l
where l.object_id = o.object_id
order by o.owner, o.object_name, session_id

they are the sys owned:
SNAP_LOADERTIME$
SNAP_LOGDEP$
SUMAGG$
SUMDELTA$
MLOG$
OBJ$
SNAP$
SUMINLINE$
SUMJOIN$
SUMKEY$
SUMPARTLOG$
SUMPRED$

What could MVs build parallel avoid the library cache lock?

Thanks In Avance.

Hi Tom,
I have find this script at http://www.dba-village.com/village/dvp_scripts.ScriptDetails?ScriptIdA=1842 link.
-------------------------- begin --------------------------
ttitle "Count of executions, grouped by minutes cached."
break on exec skip 2
select
exec ,
substr(Min_Cached,5) Min_Cached,
COUNT(*),
ROUND(AVG(Executions),2) Avg_Exec,
sum(executions) Executions,
min(executions) Min_Exec,
max(executions) Max_Exec,
round(sum(OPTIMIZER_COST),0) Cost
,substr(to_char(100 * ratio_to_report(sum(OPTIMIZER_COST)) over (), '999.00'), 2) || '%' pct_Cost
from (
select decode(executions,0,0,1,1,2,2) exec,
DECODE(Min_Cached,
0,' 1) 00-01min',
1,' 2) 01-02min',
2,' 2) 01-02min',
DECODE(SIGN(Min_Cached- 6),-1,' 3) 03-05min',
DECODE(SIGN(Min_Cached- 16),-1,' 4) 06-15min',
DECODE(SIGN(Min_Cached- 31),-1,' 5) 16-30min',
DECODE(SIGN(Min_Cached- 61),-1,' 6) 31-60min',
DECODE(SIGN(Min_Cached- 121),-1,' 7) 1- 2hr',
DECODE(SIGN(Min_Cached- 180),-1,' 8) - 3hr',
DECODE(SIGN(Min_Cached- 240),-1,' 9) - 4hr',
DECODE(SIGN(Min_Cached-12*60),-1,'10) -12hr',
'11) 12hr+ '))))))))) Min_Cached,
Executions,OPTIMIZER_COST
from (
select ROUND((SysDate-
TO_DATE(First_Load_Time,
'yyyy-mm-dd/hh24:mi:ss'))*24*60) Min_Cached,
Executions,OPTIMIZER_COST
from V$SQL
where Parsing_User_ID != 0
and executions>0
)
)
group by exec,Min_Cached;
-------------------------- end --------------------------
Scope of this sql script is to obtain Statistical information about the number of executions of a statements, and time in cache, to identify the profile of the statements by the applications on a database.
It is a good start to identify not using bind s variable?

Tom,

Just wondering: Does the following statement from a 3rd party vendor make you laugh or cry?

XXXX does not use bind variables because of the unique way in which users can build their own queries within the application.

Seriously, the unique way the vendor refers to is a search screen with about 50 columns that users can plug-in various variables to one or more column. (The table by the way is about 300 columns, 40M rows)

Haven't provided any specifics because I'm just curious about your general feeling of the vendors statement. In your experience have you found applications with legitimate reasons for not using bind?

Tom,

I have been using your query/method at the top of the page with great success.
I have shown some of the Developers a snapshot/summary of library cache of all similar statements not using bind variables where count(*) > 100 and it seems to have gotten their attention.
Now we want to have an ongoing report that shows offending SQL ranked in order of severity ('duplicate' statements).

Here is my question: Your script takes a snapshot of a single moment in time but V$SQL can change from hour to hour... and I want to get a summary of ALL major offenders for an entire week. How can I collect those hard parse statement stats/queries for an entire week?

What about creating a table with primary key of first_load_time, hash_value, and substr(sql_text), then every hour, say, take a snapshot of v$sql based on your query above... but capture ALL statements which match your criteria... the first_load_time would insure I didn't double up on sql that stayed in v$sql between snapshots.

Tben for the weekly report I can summarize them all up again with counts.

What do you think?

Thanks,

Robert.

Tom,

Thank you for the insight into my question on tracking hard parses.

What stats can I look at, either Statspack report, v$ stats, etc. that would indicate or suggest lots of hard parses going on at a given point, a given hour, etc.?

Thank you,

Robert.

Tom, I have ran the script you provided to find Lbrary cache latch contention. So as result I can see many queries with the count is high, example count for the last query is 2158, please look at the last section for the script results. And the hit ratio's from the statspack is very good:
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 94.80 In-memory Sort %: 100.00
Library Hit %: 99.99 Soft Parse %: 99.70
Execute to Parse %: 96.60 Latch Hit %: 99.58
Parse CPU to Parse Elapsd %: 92.98 % Non-Parse CPU: 99.63

I don't realy understand the purpose of it, in the statspack report I do see many latch sleeps:

Latch Activity for DB: SABO Instance: SABO Snaps: 28176 -28178
->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
willing-to-wait latch get requests
->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
->"Pct Misses" for both should be very close to 0.0

Pct Avg Wait Pct
Get Get Slps Time NoWait NoWait
Latch Requests Miss /Miss (s) Requests Miss
------------------------ -------------- ------ ------ ------ ------------ ------
Consistent RBA 108,273 0.0 0 0
FAL request queue 4 0.0 0 0
FIB s.o chain latch 132 0.0 0 0
FOB s.o list latch 2,265 0.0 0 0
SQL memory manager latch 2 0.0 0 1,191 0.0
SQL memory manager worka 1,158,591 0.0 0.0 0 0
X$KSFQP 28 0.0 0 0
active checkpoint queue 12,053 0.0 0.0 0 0
alert log latch 14 0.0 0 0
archive control 424 0.0 0 0
archive process latch 80 0.0 0 0
begin backup scn array 1 0.0 0 0
cache buffer handles 1,395,954 0.0 0.0 0 0
cache buffers chains 837,999,907 0.1 0.0 4 46,634,519 0.0
cache buffers lru chain 343,842 0.5 0.0 0 67,019,084 0.2
cas latch 32,205,261 0.1 0.0 0 0
channel handle pool latc 217 0.0 0 0
channel operations paren 2,800 0.0 0 0
checkpoint queue latch 2,201,940 0.0 0.0 0 57,609 0.0
child cursor hash table 29,824 0.0 0 0
commit callback allocati 12,781 0.0 0 0
dictionary lookup 9 0.0 0 0
dml lock allocation 493,698 0.0 0.0 0 0
dummy allocation 195 0.0 0 0
enqueue hash chains 779,023 0.0 0.0 0 0
enqueues 363,025 0.0 0.0 0 0
event group latch 95 0.0 0 0
global tx hash mapping 292 0.0 0 0
hash table column usage 141 0.0 0 1,706,189 0.0
job_queue_processes para 59 0.0 0 0
ktm global data 23 0.0 0 0
lgwr LWN SCN 108,276 0.0 0.0 0 0
library cache 99,847,263 1.0 0.0 15 11,817 4.6
library cache load lock 1,542 0.0 0 0
library cache pin 91,085,186 0.2 0.0 2 0
library cache pin alloca 7,150,475 0.0 0.0 0 0
list of block allocation 4,247 0.0 0 0
loader state object free 194 0.0 0 0
longop free list parent 1,271 0.0 0 59 0.0
message pool operations 14 0.0 0 0
messages 369,323 0.0 0.0 0 0
mostly latch-free SCN 491,595 0.3 0.0 0 0
multiblock read objects 18,762,943 0.1 0.0 0 0
ncodef allocation latch 58 0.0 0 0
object stats modificatio 12 0.0 0 0
post/wait queue 202,484 0.0 0.0 0 105,514 0.0
process allocation 95 0.0 0 95 0.0
process group creation 195 0.0 0 0
redo allocation 957,456 0.0 0.0 0 0
redo copy 0 0 741,027 0.0
Latch Activity for DB: SABO Instance: SABO Snaps: 28176 -28178
->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for
willing-to-wait latch get requests
->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
->"Pct Misses" for both should be very close to 0.0

Pct Avg Wait Pct
Get Get Slps Time NoWait NoWait
Latch Requests Miss /Miss (s) Requests Miss
------------------------ -------------- ------ ------ ------ ------------ ------
redo on-disk SCN 343,716 0.0 0 0
redo writing 339,347 0.0 0.0 0 0
row cache enqueue latch 62,201,242 0.2 0.0 0 0
row cache objects 62,209,555 4.2 0.0 0 190 0.0
sequence cache 101,827 0.0 0.0 0 0
session allocation 685,027 0.0 0.0 0 0
session idle bit 1,563,084 0.0 0.0 0 0
session switching 58 0.0 0 0
session timer 1,212 0.0 0 0
shared pool 41,912,797 1.0 0.0 1 0
sim partition latch 0 0 3,138 0.0
simulator hash latch 33,381,090 0.0 0.0 0 0
simulator lru latch 629,084 0.0 0.4 0 1,672,383 0.1
sort extent pool 77 0.0 0 0
transaction allocation 747,057 0.0 0 0
transaction branch alloc 316 0.0 0 0
undo global data 434,038 0.0 0.0 0 0
user lock 282 0.0 0 0
-------------------------------------------------------------
Latch Sleep breakdown for DB: SABO Instance: SABO Snaps: 28176 -28178
-> ordered by misses desc

Get Spin &
Latch Name Requests Misses Sleeps Sleeps 1->4
-------------------------- -------------- ----------- ----------- ------------
row cache objects 62,209,555 2,636,047 1,501 2634552/1489/6/0/0
cache buffers chains 837,999,907 1,054,910 1,255 1053666/1233/11/0/0
library cache 99,847,263 960,617 6,712 954009/6505/102/1/0
shared pool 41,912,797 401,849 2,742 399208/2543/95/3/0
library cache pin 91,085,186 215,764 317 215450/311/3/0/0
row cache enqueue latch 62,201,242 112,183 77 112106/77/0/0/0
cas latch 32,205,261 23,737 21 23716/21/0/0/0
multiblock read objects 18,762,943 22,925 48 22877/48/0/0/0
cache buffers lru chain 343,842 1,682 43 1640/41/1/0/0
mostly latch-free SCN 491,595 1,558 3 1555/3/0/0/0
library cache pin allocati 7,150,475 546 5 541/5/0/0/0
redo allocation 957,456 394 2 392/2/0/0/0
session allocation 685,027 259 8 251/8/0/0/0
simulator lru latch 629,084 179 68 111/68/0/0/0
dml lock allocation 493,698 170 2 168/2/0/0/0
-------------------------------------------------------------

Here is result of the script I run on our dabase:
-------------------------------------------------
SQL_TEXT_WO_CONSTANTS COUNT(*)
------------------------------------------------------------ ----------
R_PACKAGE_BALANCES PKGBAL ,ER_RESOURCES RES WHERE PKGBAL.SUB
SCRIPTION_OBJ_ID IN (@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@
,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@
,@,@

SELECT PURSRV.PURCHASED_SERVICE_OBJ_ID ,PURSRV.SUBSCRIPTION 121
_OBJ_ID ,PURSRV.STATUS_OBJ_ID ,PURSRV.PROV_STATUS_OBJ_ID ,PU
RSRV.SERVICE_ID ,PURSRV.PROVISIONING_TAG ,PURSRV.TIME_STAMP
,PURSRV.NON_REFUND_DESCRIPTION FROM ER_PURCHASED_SERVICES PU
RSRV WHERE PURSRV.SUBSCRIPTION_OBJ_ID IN (@,@,@,@,@,@,@,@,@,
@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,

SQL_TEXT_WO_CONSTANTS COUNT(*)
------------------------------------------------------------ ----------
@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@,@

SELECT OPTS.SUBSCRIPTION_OBJ_ID ,OPTS.DURATION ,OPTS.PROMO_ 122
CODE ,OPTS.USER_GROUP ,OPTS.PAYMENT_TYPE ,OPTS.SUPPLIER_ID ,
OPTS.PREMIUM_LEVEL ,OPTS.CHANNEL ,OPTS.CHARGING_METHOD ,OPTS
.ACCESS_DEVICE ,OPTS.OPTION_OBJ_ID ,OPTS.OPTS_LOCK_ID ,MAX(O
PTS.TIME_STAMP) FROM ER_OPTIONS OPTS WHERE OPTS.SUBSCRIPTION
_OBJ_ID IN (@) GROUP BY OPTS.SUBSCRIPTION_OBJ_ID ,OPTS.DURAT
ION ,OPTS.PROMO_CODE ,OPTS.USER_GROUP ,OPTS.PAYMENT_TYPE ,OP
TS.SUPPLIER_ID ,OPTS.PREMIUM_LEVEL ,OPTS.CHANNEL ,OPTS.CHARG
ING_METHOD ,OPTS.ACCESS_DEVICE ,OPTS.OPTION_OBJ_ID ,OPTS.OPT

SQL_TEXT_WO_CONSTANTS COUNT(*)
------------------------------------------------------------ ----------
S_LOCK_ID

SELECT PKGBAL.AMOUNT ,PKGBAL.THRESHOLD ,PKGBAL.SUBSCRIPTION 122
_OBJ_ID ,RES.DESCRIPTION ,RES.RESOURCE_CODE ,RES.RESOURCE_TY
PE ,PKGBAL.BALANCE_OBJ_ID ,PKGBAL.PKG_BALANCE_LOCK_ID FROM E
R_PACKAGE_BALANCES PKGBAL ,ER_RESOURCES RES WHERE PKGBAL.SUB
SCRIPTION_OBJ_ID IN (@) AND PKGBAL.RESOURCE_OBJ_ID = RES.RES
OURCE_OBJ_ID

SELECT DISTINCT SUBS.SUBSCRIPTION_OBJ_ID , SUBS.ACCOUNT_OBJ 238
_ID , SUBS.STATUS_OBJ_ID , SUBS.PACKAGE_ID , SUBS.START_DATE

SQL_TEXT_WO_CONSTANTS COUNT(*)
------------------------------------------------------------ ----------
, SUBS.END_DATE , SUBS.TIME_STAMP , SUBS.NEXT_CYCLE_DISCOUN
T , SUBS.IS_REFUNDABLE , SUBS.NON_REFUND_DESCRIPTION, SUBS.S
UBS_LOCK_ID , SUBS.SUPER_CREDIT_ID , SUBS.SUPER_CREDIT_BALAN
CE , DRM_DETAILS.DRM_DESCRIPTION, DRM_DETAILS.DRM_TYPE , DRM
_DETAILS.DRM_ID , SUBS.INTERACTIVE_USAGE_FLAG , COUNTRY.COUN
TRY_OBJ_ID, SUBS.BATCH_PROCESSOR

SELECT DISTINCT SUBS.SUBSCRIPTION_OBJ_ID , SUBS.ACCOUNT_OBJ 243
_ID , SUBS.STATUS_OBJ_ID , SUBS.PACKAGE_ID , SUBS.START_DATE
, SUBS.END_DATE , SUBS.TIME_STAMP , SUBS.NEXT_CYCLE_DISCOUN
T , SUBS.IS_REFUNDABLE , SUBS.NON_REFUND_DESCRIPTION , SUBS.

SQL_TEXT_WO_CONSTANTS COUNT(*)
------------------------------------------------------------ ----------
SUBS_LOCK_ID , SUBS.SUPER_CREDIT_ID , SUBS.SUPER_CREDIT_BALA
NCE , PUR_SRV.SERVICE_ID , DRM_DETAILS.DRM_DESCRIPTION , DRM
_DETAILS.DRM_TYPE , DRM_DETAILS.DRM_ID , SUBS.INTERACTIVE_US
AGE_FLAG , SUBS.PROMOTIONAL , SUBS.BATCH_PROCESSOR , SUBS.PR
OMOTIONAL_EXPIRY_DATE ,

SELECT DISTINCT SUBS.SUBSCRIPTION_OBJ_ID , SUBS.TIME_STAMP 2158
FROM ER_ACCOUNTS ACC, ER_SUBSCRIPTIONS SUBS, ER_OPTIONS OPTS
, ER_COUNTRY COUNTRY WHERE ACC.ACCOUNT_OBJ_ID = SUBS.ACCOUNT
_OBJ_ID AND SUBS.SUBSCRIPTION_OBJ_ID = OPTS.SUBSCRIPTION_OBJ
_ID(+) AND ACC.COUNTRY_OBJ_ID = COUNTRY.COUNTRY_OBJ_ID AND C

SQL_TEXT_WO_CONSTANTS COUNT(*)
------------------------------------------------------------ ----------
OUNTRY.COUNTRY_CODE ='#' AND SUBS.STATUS_OBJ_ID=@ AND SUBS.S
UBSCRIPTION_OBJ_ID > @ AND SUBS.BATCH_RETRY_DATE IS NOT NULL
AND SUBS.BATCH_RETRY_DATE <= TO_DATE('#','#') AND OPTS.CHAR
GING_METHOD=@ ORDER BY SUBS.SUBSCRIPTION_OBJ_ID ASC

8 rows selected.

Thank you for your help
Sam

Sorry, I did not include the hall report, because it's so big and we are limited to 1000 words maximum.
I will try again to send it.

Sam

Here is the first report section...

STATSPACK report for

DB Name DB Id Instance Inst Num Release Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
SABO 310423053 SABO 1 9.2.0.6.0 NO su1162

Snap Id Snap Time Sessions Curs/Sess Comment
--------- ------------------ -------- --------- -------------------
Begin Snap: 28176 05-Jun-08 13:00:03 489 28.1
End Snap: 28178 05-Jun-08 14:00:03 484 27.8
Elapsed: 60.00 (mins)

Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 7,008M Std Block Size: 8K
Shared Pool Size: 304M Log Buffer: 1,024K

Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 68,109.68 1,936.06
Logical reads: 126,505.31 3,596.00
Block changes: 403.43 11.47
Physical reads: 6,577.85 186.98
Physical writes: 17.19 0.49
User calls: 167.84 4.77
Parses: 365.27 10.38
Hard parses: 1.09 0.03
Sorts: 515.41 14.65
Logons: 0.03 0.00
Executes: 10,744.48 305.42
Transactions: 35.18

% Blocks changed per Read: 0.32 Recursive Call %: 99.48
Rollback per transaction %: 1.30 Rows per Sort: 5.69

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 94.80 In-memory Sort %: 100.00
Library Hit %: 99.99 Soft Parse %: 99.70
Execute to Parse %: 96.60 Latch Hit %: 99.58
Parse CPU to Parse Elapsd %: 92.98 % Non-Parse CPU: 99.63

Shared Pool Statistics Begin End
------ ------
Memory Usage %: 95.36 97.92
% SQL with executions>1: 69.27 69.89
% Memory for SQL w/exec>1: 64.22 64.56

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
CPU time 27,990 76.37
db file sequential read 5,853,053 6,502 17.74
log file sync 105,494 793 2.16
log file parallel write 108,260 698 1.90
db file scattered read 4,206,900 538 1.47
-------------------------------------------------------------
Wait Events for DB: SABO Instance: SABO Snaps: 28176 -28178
-> s - second
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc (idle events last)

Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
db file sequential read 5,853,053 0 6,502 1 46.2
log file sync 105,494 0 793 8 0.8
log file parallel write 108,260 0 698 6 0.9
db file scattered read 4,206,900 0 538 0 33.2
buffer busy waits 12,756 0 52 4 0.1
latch free 12,800 0 22 2 0.1
log file sequential read 300 0 17 57 0.0
sbtwrite2 1,191 0 11 10 0.0
control file parallel write 1,504 0 9 6 0.0
sbtbackup 4 0 5 1307 0.0
SQL*Net more data to client 33,777 0 4 0 0.3
control file sequential read 4,669 0 2 0 0.0
sbtclose2 4 0 1 327 0.0
sbtinfo2 4 0 1 273 0.0
control file single write 32 0 1 26 0.0
SQL*Net break/reset to clien 19 0 1 27 0.0
enqueue 26 0 0 18 0.0
sbtremove2 2 0 0 220 0.0
log file switch completion 6 0 0 55 0.0
SQL*Net message from dblink 365 0 0 1 0.0
switch logfile command 5 0 0 46 0.0
library cache pin 24 0 0 6 0.0
sbtinit 8 0 0 14 0.0
local write wait 9 0 0 11 0.0
log file single write 8 0 0 11 0.0
single-task message 1 0 0 40 0.0
LGWR wait for redo copy 157 0 0 0 0.0
wait list latch free 1 0 0 25 0.0
log buffer space 3 0 0 8 0.0
direct path read 1,048 0 0 0 0.0
direct path write 307 0 0 0 0.0
sbtend 8 0 0 0 0.0
SQL*Net message to dblink 365 0 0 0 0.0
async disk IO 116 0 0 0 0.0
sbtinit2 8 0 0 0 0.0
buffer deadlock 1 1 0 0 0.0
SQL*Net message from client 782,399 0 1,434,920 1834 6.2
queue messages 4,039 1,431 21,057 5213 0.0
virtual circuit status 120 120 3,489 29074 0.0
pipe get 21 8 30 1441 0.0
SQL*Net message to client 782,394 0 1 0 6.2
SQL*Net more data from clien 1,132 0 0 0 0.0
-------------------------------------------------------------
Background Wait Events for DB: SABO Instance: SABO Snaps: 28176 -28178
-> ordered by wait time desc, waits desc (idle events last)

Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
log file parallel write 108,260 0 698 6 0.9
control file parallel write 1,269 0 8 6 0.0
log file single write 8 0 0 11 0.0
db file sequential read 44 0 0 1 0.0
rdbms ipc reply 12 0 0 3 0.0
control file sequential read 796 0 0 0 0.0
LGWR wait for redo copy 157 0 0 0 0.0
enqueue 4 0 0 1 0.0
direct path read 1,048 0 0 0 0.0
latch free 4 0 0 0 0.0
direct path write 306 0 0 0 0.0
log file sequential read 8 0 0 0 0.0
buffer busy waits 1 0 0 0 0.0
rdbms ipc message 110,090 4,210 22,846 208 0.9
pmon timer 1,212 1,212 3,510 2896 0.0
smon timer 19 10 3,465 ###### 0.0
-------------------------------------------------------------

Here is another important section of the report..
Thank you

Latch Miss Sources for DB: SABO Instance: SABO Snaps: 28176 -28178
-> only latches with sleeps are shown
-> ordered by name, sleeps desc

NoWait Waiter
Latch Name Where Misses Sleeps Sleeps
------------------------ -------------------------- ------- ---------- --------
cache buffers chains kcbrls: kslbegin 0 517 334
cache buffers chains kcbgtcr: kslbegin excl 0 381 591
cache buffers chains kcbgtcr: fast path 0 270 311
cache buffers chains kcbchg: kslbegin: bufs not 0 19 9
cache buffers chains kcbgcur: kslbegin 0 17 3
cache buffers chains kcbzsc 0 16 0
cache buffers chains kcbzgb: scan from tail. no 0 12 0
cache buffers chains kcbzib: multi-block read: 0 9 0
cache buffers chains kcbchg: kslbegin: call CR 0 6 3
cache buffers chains kcbgtcr: kslbegin shared 0 5 0
cache buffers chains kcbcge 0 1 0
cache buffers chains kcbzib: finish free bufs 0 1 3
cache buffers chains kcbzwb 0 1 0
cache buffers lru chain kcbzgb: multiple sets nowa 44,700 23 0
cache buffers lru chain kcbbiop: lru scan 374 13 0
cache buffers lru chain kcbzar: KSLNBEGIN 15,164 3 0
cache buffers lru chain kcbzgm 3,866 3 0
cache buffers lru chain kcbzgb: posted for free bu 2,732 1 41
cas latch kgsloupd1 0 21 21
dml lock allocation ktaiam 0 2 2
library cache kglic 0 2,932 66
library cache kglupc: child 0 1,396 2,324
library cache kglpnc: child 0 1,101 3,183
library cache kglpndl: child: before pro 0 247 341
library cache kglpnp: child 0 125 321
library cache kgllkdl: child: cleanup 0 113 62
library cache kglpin: child: heap proces 0 107 27
library cache kglhdgc: child: 0 56 41
library cache kglobpn: child: 0 49 8
library cache kglpndl: child: after proc 0 47 10
library cache kglhdgn: child: 0 36 78
library cache kglget: child: KGLDSBRD 0 30 126
library cache kgldti: 2child 0 16 2
library cache kgldte: child 0 0 7 27
library cache kglobld 0 2 1
library cache kglpin 0 1 1
library cache pin kglpnc: child 0 131 136
library cache pin kglupc 0 105 116
library cache pin kglpndl 0 47 24
library cache pin kglpnp: child 0 28 29
library cache pin kglpnal: child: alloc spac 0 6 12
library cache pin alloca kgllkdl 0 3 4
library cache pin alloca kglpnal 0 2 1
mostly latch-free SCN kcs01 0 2 3
mostly latch-free SCN kcs02 0 1 0
multiblock read objects kcbzib: mbr get 0 27 29
multiblock read objects kcbzib: normal mbr free 0 21 19
redo allocation kcrfwr 0 2 0
row cache enqueue latch kqreqd 0 58 22
row cache enqueue latch kqreqa 0 19 55
row cache objects kqrpfl: not dirty 0 1,273 372
row cache objects kqrpre: find obj 0 228 1,129
session allocation ksufap: active sessions 0 3 0
Latch Miss Sources for DB: SABO Instance: SABO Snaps: 28176 -28178
-> only latches with sleeps are shown
-> ordered by name, sleeps desc

NoWait Waiter
Latch Name Where Misses Sleeps Sleeps
------------------------ -------------------------- ------- ---------- --------
session allocation ksucri 0 2 6
session allocation ksuxds: not user session 0 2 2
session allocation ksuxds: KSUSFCLC not set 0 1 0
shared pool kghalo 0 1,351 4
shared pool kghupr1 0 1,103 2,737
shared pool kghfrunp: clatch: nowait 0 289 0
shared pool kghfrunp: alloc: wait 0 269 0
shared pool kghfrunp: clatch: wait 0 158 94
shared pool kghalp 0 13 0
shared pool kghfre 0 4 1
shared pool kghfen: not perm alloc cla 0 2 0
simulator lru latch kcbs_simulate: simulate se 0 68 68
-------------------------------------------------------------
Dictionary Cache Stats for DB: SABO Instance: SABO Snaps: 28176 -28178
->"Pct Misses" should be very low (< 2% in most cases)
->"Cache Usage" is the number of cache entries being used
->"Pct SGA" is the ratio of usage to allocated size for that cache

Get Pct Scan Pct Mod Final
Cache Requests Miss Reqs Miss Reqs Usage
------------------------- ------------ ------ ------- ----- -------- ----------
dc_database_links 192 0.0 0 0 1
dc_files 7,600 0.0 0 0 152
dc_free_extents 40 0.0 0 0 4
dc_global_oids 330,886 0.0 0 0 87
dc_histogram_defs 174,807 0.1 0 0 1,231
dc_object_ids 495,926 0.0 0 0 466
dc_objects 30,967 0.3 0 8 951
dc_profiles 81 0.0 0 0 1
dc_rollback_segments 5,134 0.0 0 0 130
dc_segments 53,259 0.2 0 14 423
dc_sequences 1,642 0.1 0 1,642 11
dc_tablespace_quotas 26 11.5 0 26 10
dc_tablespaces 706,661 0.0 0 0 42
dc_user_grants 900 0.0 0 0 26
dc_usernames 132,405 0.0 0 0 14
dc_users 29,161,512 0.0 0 0 29
-------------------------------------------------------------


Library Cache Activity for DB: SABO Instance: SABO Snaps: 28176 -28178
->"Pct Misses" should be very low

Get Pct Pin Pct Invali-
Namespace Requests Miss Requests Miss Reloads dations
--------------- ------------ ------ -------------- ------ ---------- --------
BODY 736 0.7 856 0.6 0 0
CLUSTER 382 0.5 302 1.3 0 0
INDEX 130 2.3 67 4.5 0 0
PIPE 52 23.1 65 18.5 0 0
SQL AREA 112,152 3.0 42,045,433 0.0 577 8
TABLE/PROCEDURE 478,013 0.0 3,765,359 0.0 382 0
TRIGGER 248 0.0 248 0.0 0 0
-------------------------------------------------------------
Shared Pool Advisory for DB: SABO Instance: SABO End Snap: 28178
-> Note there is often a 1:Many correlation between a single logical object
in the Library Cache, and the physical number of memory objects associated
with it. Therefore comparing the number of Lib Cache objects (e.g. in
v$librarycache), with the number of Lib Cache Memory Objects is invalid

Estd
Shared Pool SP Estd Estd Estd Lib LC Time
Size for Size Lib Cache Lib Cache Cache Time Saved Estd Lib Cache
Estim (M) Factr Size (M) Mem Obj Saved (s) Factr Mem Obj Hits
----------- ----- ---------- ------------ ------------ ------- ---------------
176 .6 161 41,309 485,708 1.0 181,573,120
208 .7 192 50,285 485,711 1.0 181,574,848
240 .8 223 59,446 485,714 1.0 181,575,905
272 .9 255 68,780 485,720 1.0 181,579,173
304 1.0 286 74,811 485,741 1.0 181,618,438
336 1.1 318 78,529 485,763 1.0 181,687,097
368 1.2 346 84,684 485,770 1.0 181,724,048
400 1.3 362 89,476 485,771 1.0 181,724,742
432 1.4 362 89,633 485,771 1.0 181,724,768
464 1.5 362 89,633 485,771 1.0 181,724,768
496 1.6 362 89,633 485,771 1.0 181,724,768
528 1.7 362 89,633 485,771 1.0 181,724,768
560 1.8 362 89,633 485,771 1.0 181,724,768
592 1.9 362 89,633 485,771 1.0 181,724,768
624 2.1 362 89,633 485,771 1.0 181,724,768
-------------------------------------------------------------
SGA Memory Summary for DB: SABO Instance: SABO Snaps: 28176 -28178

SGA regions Size in Bytes
------------------------------ ----------------
Database Buffers 7,348,420,608
Fixed Size 752,080
Redo Buffers 1,323,008
Variable Size 3,137,339,392
----------------
sum 10,487,835,088
-------------------------------------------------------------


SGA breakdown difference for DB: SABO Instance: SABO Snaps: 28176 -28178

Pool Name Begin value End value % Diff
------ ------------------------------ ---------------- ---------------- -------
large free memory 33,554,432 33,554,432 0.00
shared 1M buffer 2,099,200 2,099,200 0.00
shared Checkpoint queue 4,106,240 4,106,240 0.00
shared FileOpenBlock 6,773,240 6,773,240 0.00
shared KGK heap 7,000 7,000 0.00
shared KGLS heap 1,606,168 1,131,072 -29.58
shared KQR L PO 1,561,320 1,562,344 0.07
shared KQR M PO 1,179,624 1,196,008 1.39
shared KQR S SO 7,424 7,424 0.00
shared KQR X PO 28,368 28,368 0.00
shared KSXR pending messages que 853,952 853,952 0.00
shared KSXR receive buffers 1,034,000 1,034,000 0.00
shared MTTR advisory 298,328 298,328 0.00
shared PL/SQL DIANA 1,769,864 1,371,744 -22.49
shared PL/SQL MPCODE 5,543,416 5,633,280 1.62
shared PLS non-lib hp 3,216 3,216 0.00
shared VIRTUAL CIRCUITS 2,073,680 2,073,680 0.00
shared branch 355,512 355,512 0.00
shared db_block_hash_buckets 30,574,480 30,574,480 0.00
shared db_handles 1,044,000 1,044,000 0.00
shared dictionary cache 3,229,952 3,229,952 0.00
shared enqueue 1,654,896 1,654,896 0.00
shared errors 52,504 400,880 663.52
shared event statistics per sess 11,255,440 11,255,440 0.00
shared fixed allocation callback 1,168 1,168 0.00
shared free memory 19,458,928 8,740,712 -55.08
shared joxs heap init 4,240 4,240 0.00
shared krvxrr 253,056 253,056 0.00
shared ksm_file2sga region 370,496 370,496 0.00
shared ktlbk state objects 586,384 586,384 0.00
shared library cache 70,989,544 73,428,504 3.44
shared miscellaneous 60,497,992 60,358,832 -0.23
shared parameters 157,784 164,384 4.18
shared pl/sql source 640 584 -8.75
shared processes 1,180,800 1,180,800 0.00
shared sessions 2,690,480 2,690,480 0.00
shared sim memory hea 7,301,888 7,301,888 0.00
shared sim trace entries 393,216 393,216 0.00
shared sql area 177,103,016 185,982,000 5.01
shared table definiti 6,552 1,344 -79.49
shared transaction 954,448 954,448 0.00
shared trigger defini 544 544 0.00
shared trigger inform 1,808 1,680 -7.08
shared trigger source 168 168 0.00
shared type object de 322,360 321,216 -0.35
buffer_cache 7,348,420,608 7,348,420,608 0.00
fixed_sga 752,080 752,080 0.00
log_buffer 1,312,768 1,312,768 0.00
-------------------------------------------------------------
init.ora Parameters for DB: SABO Instance: SABO Snaps: 28176 -28178

End value
Parameter Name Begin value (if different)
----------------------------- --------------------------------- --------------
aq_tm_processes 0
background_dump_dest /product/oracle/sgbd/SABO/bdump
compatible 9.2.0.6
control_files /database/oracle/SABO/CTL_SABO_00
core_dump_dest /product/oracle/sgbd/SABO/cdump
db_block_size 8192
db_cache_size 7348420608
db_domain
db_file_multiblock_read_count 16
db_name SABO
dispatchers (PROTOCOL=TCP) (SERVICE=SABOXDB)
fast_start_mttr_target 300
hash_join_enabled TRUE
instance_name SABO
java_pool_size 0
job_queue_processes 10
large_pool_size 33554432
local_listener LSNR_SABO_01_LOC
log_archive_dest /product/oracle/sgbd/SABO/arch
log_archive_format arc_SABO_%s.arc
log_archive_start TRUE
log_buffer 1048576
open_cursors 5000
optimizer_mode CHOOSE
os_authent_prefix ops$
pga_aggregate_target 7340032000
processes 900
query_rewrite_enabled FALSE
remote_login_passwordfile EXCLUSIVE
service_names su1162_SABO, SABO
sga_max_size 10487835088
shared_pool_size 318767104
star_transformation_enabled FALSE
timed_statistics TRUE
undo_management AUTO
undo_retention 50000
undo_tablespace UNDO_001_SABO
user_dump_dest /product/oracle/sgbd/SABO/udump
utl_file_dir /varsoft/sabo/trace/tmp, /varsoft
workarea_size_policy auto

Tom, I thought soft parse is good and we should avoid only hard parse. Parses: 365.27 and 10.38 per transaction.
1. Do you mean we parse a lot per transaction.
2. Are you saying if I increasing the shared pool nothing will change.

About session_cached_cursors I read so many articles that it does not improve respond time. it might improve parsing a little but not respond time.

Can I set the session_cached_cursors at database level I mean in the spfile or init.ora file.

Also you said at the begining that (a parse is a parse - soft or hard - both require lots of latching, hard is just worse, soft is very bad, hard is very very bad) so how we can avoid both parse hard and soft then.

Thank you again
Sam

parse sql
bind (hopefully this is there!!!!)
execute sql
close sql

if (first_time)
then
   parse sql
   first_time gets false
end
bind sql
execute sql
/* do not close it */

I am sorry Tom, but I didn't realy get what you are saying about not closing sql after execute sql!!! how can you close or open sql statement?

if (first_time)
then
parse sql
first_time gets false
end
bind sql
execute sql
/* do not close it */

All sql's statements is in plsql procedures... and there is so many procedures in this application. many procedures that is calling many other procedures with loops and conditions and SELECT COUNT(*)... and so on,

I am trying to improve performance of the procedure that of course calls other procedures to do the job. we are reaching TIME OUT "over a 1 minutes and 10 seconds".

Thank you again,
Sam

Tom,
I have a 10.2.0.3 database that I believe is experiencing library latch contention (among other things). In the Top SQL Statements of the ASH report I have 51.67% for the PL/SQL block generated by a web server request. That 51.67% breaks down as follows in the %Event column):

18.97 cursor: pin S wait on X
16.11 CPU + Wait for CPU
12.54 kksfbc child completion

I thought I read the "cursor: pin S wait on X" was a new term for library latches?

Two other very concerning entries come from the AWR. The application is an OLTP type app that does tens of millions of transactions over a several hour time period. When I compare stats from the new code to benchmarked code (for a 10 or 15 minute period) I see the following:

Parse CPU to Parse Elapsd %: went from 62.13 to 4.74
Soft Parse %: went from 99.99 to 81.65
% Non-Parse CPU: went from 99.61 to 93.38

I have used your code to strip literals and numbers from v$sqlarea and look for bad SQL (without binds) and found nothing (which I sort of expected since the application is 99% PL/SQL and the binding should be done for me and there is very little dynamic SQL).

The noticeable difference is that machine CPU is now being pegged whereas it used to run much more efficiently. Looking for any suggestions on where to look and am I even on the right track.

I will note that with the new code that has been added there are a lot of selects from PL/SQL tables using CAST(TABLE with CARDINALITY hints. I have no idea if that should affect anything and have given thought to recoding using global temp tables but would be a ton of programming effort (man months) I don't want to go through if it's not even the right track.

I know you love details and this may sound somewhat vague, I'm just at a loss as to what details to provide because I'm not sure where to go with this. The system is a shop floor type app with all action coming through thousands of simultaneous web server connections from plant floor devices so tracing individual sessions isn't very practical, but I could do it at the system level for a couple minutes if I knew what to then go look for in the monstrous trace file I'd get.

Thoughts on how to proceed?

If you're hitting this:

18.97 cursor: pin S wait on X
16.11 CPU + Wait for CPU
12.54 kksfbc child completion


I'd say you're hitting Oracle Bug: 6477136 - but it appears to be a Real Application Clusters issue. We get this problem when the shared pool has been flushed or the DB bounced, and I think it's related to Oracle's ASMM, where the various memory structures are resized. Possibly also related to cursor_sharing setting.

I'd look into this bug, sadly the base bug isn't visible so an SR may be required.

How to simulate library cache latch in our 10gR2 environment?

Also, I once tried executing SQL quering on v$ views (pretty big SQL with more than 400 characters) and that was waiting for library cache latch. In the RAW trace file, it showed it is waiting on different object (user defined index) which I am not using my query at all.

How is this possible?

At that time, there was heavy parsing by user application and perhaps one of their SQL was waiting for library cache latch (not sure).

In this situation will my big query also end up in library cache latch? I tried small query something like "select * from v$session where sid=nnn" and that worked without any delay.

Does library cache latch happens if there is no enough room (enough chunks of memory) in the shared pool?
Does library cache latch happen if the alloted latches at the database level are exhausted by latching other cursors?

How does CURSOR_SHARING parameter values(all the 3 values) affect the library cache latch?

Appreciate your response.

Thanks,

I don't think oracle support 40,000 characters long sql string as a single SQL statement.

"sure it does, there is actually no documented limit. "

My application submitted a 744,265 character long SQL query via JDBC. It's been hanging for over 20 hours. V$SQL doesn't show any activity, no executions, disk reads, buffer gets, nothing. V$SESSION shows the query waiting on "SQL*Net more data from client".

One of Millsap's case studies featured in his book indicates the follwing regarding the more data from client event:

"The long SQL text strings wouldn¿t fit into a single SQL*Net packet, so the Oracle kernel spent a considerable amount of time awaiting second and subsequent SQL*Net packets during parse calls."

Any chance this is what's holding up my query from running?

Thanks.

The 750K query has an IN list of 43,000 items. We are going to change the query to avoid having a large IN list but I'd like to know if such a large query could be "hanging" due to a more data from client wait event.

That's exactly what we did, use a global temp table and it works great. The session was waiting for "more data from client" back when it was hanging.

A few more questions regarding the long SQL statement I mentioned above.

The scenario I described had the application "hanging" and the database waiting for "more data from client". I recently discovered the application's Java code builds the SQL statement as a single string of characters with no embedded new lines. If we were to print the SQL it would show as one very long line.

Does a very long SQL string (with no new lines) cause the database to wait on "more data from client"?

Can I assume the entire SQL was received by the database because I found it in V$SQL?

We already fixed this issue by using a global temp table. My questions are more curiosity than anything else.

Thanks.

Try as I might, I cannot get remove_constants to work in 10g )10.2.0.3) Linux.

The problem lies with:
update t1 set sql_text_wo_constants = remove_constants(sql_text);

Here's the error:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SYSTEM.REMOVE_CONSTANTS", line 10

Works on 9i.

ops$tkyte%ORA10GR2> create or replace function
  2  remove_constants( p_query in varchar2 ) return varchar2
  3  as
  4      l_query long;
  5      l_char  varchar2(1);
  6      l_in_quotes boolean default FALSE;
  7  begin
  8      for i in 1 .. length( p_query )
  9      loop
 10          l_char := substr(p_query,i,1);
 11          if ( l_char = '''' and l_in_quotes )
 12          then

  1  create or replace function
  2  remove_constants( p_query in varchar2 ) return varchar2
  3  as
  4      l_query long;<b>
  5      l_char  varchar2(1 CHAR);</b>
  6      l_in_quotes boolean default FALSE;
  7  begin

Worked! Thanks!

I love the Am I Using Binds query, and have used it to great effect on many occasions.
I use it to prove that a shop is not being as efficient as it could be, and it is usually well-received... BUT... what I'd also like to be able to do based (I expect)
on the script is to show how much Shared Pool, in megabytes/whatever, is being wasted.

Is there a way to do that?

This is a new question, but, I feel, is a natural corollary to the replies thus far given

ops$tkyte%ORA11GR1> create table t ( x int primary key );

Table created.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> select count(*) from v$sql where sql_text NOT LIKE 'select /* nobind */ * from t where x = %';

  COUNT(*)
----------
       684

ops$tkyte%ORA11GR1> pause

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> set timing on
ops$tkyte%ORA11GR1> declare
  2      l_cursor sys_refcursor;
  3  begin
  4      for i in 1 .. 10000
  5      loop
  6          open l_cursor for 'select /* nobind */ * from t where x = ' || i;
  7          close l_cursor;
  8      end loop;
  9  end;
 10  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:13.70
ops$tkyte%ORA11GR1> declare
  2      l_cursor sys_refcursor;
  3  begin
  4      for i in 1 .. 10000
  5      loop
  6          open l_cursor for 'select /* bind */ * from t where x = :x' using i;
  7          close l_cursor;
  8      end loop;
  9  end;
 10  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.85
ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> set linesize 121
ops$tkyte%ORA11GR1> select count(*), to_char(sum(sharable_mem),'999,999,999') sm,
  2         to_char(sum(sharable_mem)/ decode( count(*), 0, to_number(null), count(*) ), '999,999,999' ) per_stmt
  3    from v$sql
  4   where sql_text like 'select /* nobind */ * from t where x = %';

  COUNT(*) SM           PER_STMT
---------- ------------ ------------
       353    3,107,841        8,804

Elapsed: 00:00:00.19
ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> select count(*), to_char(sum(sharable_mem),'999,999,999') sm
  2    from v$sql
  3   where sql_text = 'select /* bind */ * from t where x = :x';

  COUNT(*) SM
---------- ------------
         1       12,892

Elapsed: 00:00:00.03
ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> select count(*) from v$sql where sql_text NOT LIKE 'select /* nobind */ * from t where x = %';

  COUNT(*)
----------
       433

SQL> SELECT event, state, p1, p2, p3
  2  FROM   v$session_wait
  3  WHERE  sid = 2807;

EVENT                                    STATE                       P1         P2         P3
---------------------------------------- ------------------- ---------- ---------- ----------
free global transaction table entry      WAITING                  47497          0          0


SQL> SELECT event#, event_id, name, parameter1, wait_class_id, wait_class#, wait_class 
  2  FROM v$event_name WHERE name = 'free global transaction table entry';

    EVENT#   EVENT_ID NAME                                   PARAMETER1   WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- ----------  -------------------------------------- ------------ ------------- -----------  -----
       629 1932123989 free global transaction table entry    tries           1893977003           0 Other

How can we get rid of wait event "free global transaction table entry"?

Hi tom, We faced database hung on 3 node 11i erp rac
database.We saw the libracy cache load lock timed out events reported in alert log. Then few ora-600 and later ROW CACHE ENQUEUE LOCK timed out event. Eventually database was hung and we had to bouce services .
we created support sr 7845542.992 for RCA.
The support says to increase shared pool size to avoid shared pool fragmentation and avoid reload ,additionaly to upgrade to 10g database.

I am covinced adding additional pool size would solve this or upgrade to 10 .furthermore even 10g has such issues reported.
I saw couple of bugs mentioned such issue can happen due deadlock of session holding latches .kindly let me know your view on issue

. Regards

Hi Tom,

Hereby a regular expression version of the query to find statements that are identical in the shared pool:

SELECT COUNT (*),
   sql_text_wo_constants
FROM
   (SELECT  regexp_replace (
               regexp_replace (
                  regexp_replace (
                     sql_text,
                     '([^'']+'')[^'']+''', -- find quoted strings
                     '\1#''' -- replace them with '#'
                  ),
                  '([^[:alnum:]])[[:digit:]]+([^[:alnum:]])', -- find numbers
                  '\1@\2' -- replace them with @
               ),
               ' +', -- find blank spaces
               ' ' -- replace them with single space
            ) AS sql_text_wo_constants
   FROM v$sqlarea
   )
GROUP BY sql_text_wo_constants
HAVING COUNT (*) > 100
ORDER BY 1 DESC;

Notes:
1. The sub-query is only there to avoid typing the regular expression statement twice.
2. This query avoids the issue with variables having digits in their name. I.E. 'select value11, value12, (...)' is not handled correctly by the remove_constant function
3. Your version is much, much faster... I know!

Donat

HI Tom,

select sql_text_wo_constants, count(*)
from t1
group by sql_text_wo_constants
having count(*) > 100
order by 2
/


SQL_TEXT_WO_CONSTANTS COUNT(*)
---------------------------------------------------------------------------------------------------- ----------
BEGIN :L_RET_VALUE := ABC.P_PROVISIONING_ABC.ABC_ANRAX(@ ,@ ,'#' ,'#'); END; 2880
BEGIN :L_RET_VALUE := ABC.P_PROVISIONING_ABC.ABC_LAC(@ ,@ ,'#' ,'#'); END; 4156
BEGIN :L_RET_VALUE := ABC.P_PROVISIONING_ABC.ABC_DIRECTORY_NUMBER(@ ,@ ,'#' ,'#'); END; 8813
BEGIN P_PROVISIONINGMANAGER_API.UPDATE_SOP_OBJECTS ('#','#','#'); END; 13464


i got the above output, As the count is in 5 digits. I really need to work on it to stabilize my database. The above procedures are called by the application.

1)How to pass bind variables while calling the procedure?

2)If it is not possible,is there any other work around. Please suggest me as i am very new to DBA and got lot of issues on my database.

Hi Tom

I have been trying to figure out why some vendor sql at times takes 2 minutes to run and at other time takes hours.
The sql does not use bind variables, so i know that is an issue. I ran your sql from above and i do not see the sql listed that is taking a long time to run, so I am missing something.

Here is the OEM Wait History for the sql when it does not complete properly. Is there any other reason other than not using bind variables that would cause cache buffer chains ?

What do you suggest I do next ?

Thanks
Jim


Wait Class Wait Event P1 Test P1 P2 Text P2 P3 Text P3 Wait Time
Concurrency latch: cache buffers chains address number 124 tries 0
Concurrency latch: cache buffers chains address number 124 tries 0
Concurrency latch: cache buffers chains address number 124 tries 1
Concurrency latch: cache buffers chains address number 124 tries 2
Concurrency latch: cache buffers chains address number 124 tries 4
Concurrency latch: cache buffers chains address number 124 tries 1
Concurrency latch: cache buffers chains address number 124 tries 1
Concurrency latch: cache buffers chains address number 124 tries 1
Concurrency latch: cache buffers chains address number 124 tries 1
Concurrency latch: cache buffers chains address number 124 tries 0

Not really much sql being run Tom and no batch jobs

I broke out the sql and ran it on a test database

Both servers and databases have the same issues with the same sql

I entered a follow up question under Performance Tuning
since you were assisting me last year on similar issue

Hope you can assist me

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3083446555294#4298956900346743544

Thanks
Jim

Hi Tom,

1)What is the between "library cache lock " and "library cahe pin".

2)Can you give us some examples on,when there will be a "lock" and "pin"

HI tom,

I am still not clear with licrary cache pin. Your link says that,

"an underlying objects should not be modified when a session is executing or accessing a dependent object (SQL). So, before parse locks on a library cache object can be broken, library cache pins must be acquired in Exclusive mode and then only library cache objects can be dropped. If a session is executing an SQL, then library cache pins will not be available and there will be waits for library cache pins. Typically, this happens for long running SQL statement.
"
If i am running "select * from emp" from session 1,where emp table is a pretty huge table and it takes around 1 hr to complete.

If i run the same query from session 2, will session 2 wait on "library cache pin" till the session 1 gets completed?


Thanks a lot

Dear tom ,

o/s : linux
database : 11gr2

i have taken awr report for one hour time duration

Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 5236 06-Feb-12 18:00:33 126 4.2
End Snap: 5237 06-Feb-12 19:00:53 93 3.2
Elapsed: 60.33 (mins)
DB Time:



Event Waits Time(s) (ms) time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
cursor: pin S 51,214 32,594 636 41.9 Concurrenc
latch: shared pool 23,610 17,529 742 22.6 Concurrenc
cursor: mutex S 23,827 13,235 555 17.0 Concurrenc
DB CPU 10,688 13.8
log file sync 877,048 4,089 5 5.3 Commit

found that cursor: pin and latch: shared pool are top waits in the database.

i tried to understand about latch: shared pool wait and its fixes from google and metalink. but i could not anything usefully.

can you please help me to understand the above said wait event and fix for that wait

Thanks
Aliyar

dear Tom,

thanks for your immediate reply for the previous question about " latch : shared pool wait event " . i have taken awr report from good time and bad time . result as follows


Good time report taken 01 hour interval

Host Name        Platform                         CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
xxxxxxxxxx Linux x86 64-bit                    4     4       2      31.28


              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:      5403 13-Feb-12 17:00:15       163       4.8
  End Snap:      5404 13-Feb-12 18:00:36       149       5.1
   Elapsed:               60.34 (mins)
   DB Time:              363.79 (mins)




Load Profile              Per Second    Per Transaction   Per Exec   Per Call
~~~~~~~~~~~~         ---------------    --------------- ---------- ----------
      DB Time(s):                6.0                0.0       0.01       0.01
       DB CPU(s):                1.4                0.0       0.00       0.00
       Redo size:        2,787,450.0            6,975.8
   Logical reads:           33,314.5               83.4
   Block changes:           10,532.5               26.4
  Physical reads:            4,662.5               11.7
 Physical writes:              654.3                1.6
      User calls:              509.0                1.3
          Parses:               85.2                0.2
     Hard parses:                0.9                0.0
W/A MB processed:                1.0                0.0
          Logons:                0.5                0.0
        Executes:              445.5                1.1
       Rollbacks:                0.0                0.0
    Transactions:              399.6



Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                                           Avg
                                                          wait   % DB
Event                                 Waits     Time(s)   (ms)   time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
db file sequential read           4,013,902       6,481      2   29.7 User I/O
DB CPU                                            4,874          22.3
log file sync                     1,442,402       4,405      3   20.2 Commit
direct path read                    284,571       1,595      6    7.3 User I/O
latch: shared pool                   19,107       1,239     65    5.7 Concurrenc
Host CPU (CPUs:    4 Cores:     4 Sockets:    2)
~~~~~~~~         Load Average
               Begin       End     %User   %System      %WIO     %Idle
           --------- --------- --------- --------- --------- ---------
                5.09      6.00      30.1       7.6      26.5      60.1




=======================================================
=========================================================

Bad Time report 01 hour interval

              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:      5236 06-Feb-12 18:00:33       126       4.2
  End Snap:      5237 06-Feb-12 19:00:53        93       3.2
   Elapsed:               60.33 (mins)
   DB Time:            1,295.40 (mins)



Load Profile              Per Second    Per Transaction   Per Exec   Per Call
~~~~~~~~~~~~         ---------------    --------------- ---------- ----------
      DB Time(s):               21.5                0.1       0.09       0.07
       DB CPU(s):                3.0                0.0       0.01       0.01
       Redo size:        1,879,295.1            7,753.1
   Logical reads:           10,841.6               44.7
   Block changes:            6,955.0               28.7
  Physical reads:              104.3                0.4
 Physical writes:              388.2                1.6
      User calls:              294.2                1.2
          Parses:               47.4                0.2
     Hard parses:                0.3                0.0
W/A MB processed:                0.2                0.0
          Logons:                0.0                0.0
        Executes:              249.2                1.0
       Rollbacks:                0.0                0.0
    Transactions:              242.4


Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                                           Avg
                                                          wait   % DB
Event                                 Waits     Time(s)   (ms)   time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
cursor: pin S                        51,214      32,594    636   41.9 Concurrenc
latch: shared pool                   23,610      17,529    742   22.6 Concurrenc
cursor: mutex S                      23,827      13,235    555   17.0 Concurrenc
DB CPU                                           10,688          13.8
log file sync                       877,048       4,089      5    5.3 Commit


Host CPU (CPUs:    4 Cores:    4 Sockets:    2)
~~~~~~~~         Load Average
               Begin       End     %User   %System      %WIO     %Idle
           --------- --------- --------- --------- --------- ---------
               25.17      6.48      54.4      23.8       2.3      20.8



SQL> show parameter cursor_sharing

NAME_COL_PLUS_SHOW_P TYPE                             VALUE_COL_
-------------------- -------------------------------- ----------
cursor_sharing       string                           force
SQL>


concurrency user count is less in bad time while comparing with good time. i dont find any hard parsing issue from the above report.

could you please give me some more guidence on this issue

Thanks
Aliyar