Home » RDBMS Server » Performance Tuning » what's operation can cause these wait event?
what's operation can cause these wait event? [message #572109] Thu, 06 December 2012 03:12 Go to next message
lonion
Messages: 97
Registered: August 2011
Location: shenzhen,China
Member
Hi all,
what's operation can cause "library cache lock" and "library cache pin"?
Re: what's operation can cause these wait event? [message #572110 is a reply to message #572109] Thu, 06 December 2012 03:14 Go to previous messageGo to next message
Michel Cadot
Messages: 57607
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
All.

Regards
Michel
Re: what's operation can cause these wait event? [message #573008 is a reply to message #572109] Wed, 19 December 2012 12:45 Go to previous message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
I get these errors parsing sql that are missing bind variables which I find with the following query.
PARSE_CALLS   COUNT(*)    MEG SUBSTR(SQL_TEXT,1,40)
----------- ---------- ------ ----------------------------------------
        145        103      2 select /*+  no_parallel_index(t, "WRH$_S
        296        296      4 select FLAGS from SYS_FBA_TRACKEDTABLES
        247        247      5 select /*+ FIRST_ROWS(1) PARALLEL("WRH$_
       1638        305      6 select count(FA#) from SYS_FBA_TRACKEDTA
        162        155      7 SELECT COUNT(*) FROM (SELECT * FROM DBA_
        235        120      9 WITH a as (SELECT a$.dbid, a$.snap_id, a
        199        124     10  SELECT  count(*) as cnt ,         a.SQL
        241        241     11 create global temporary table sys.ora_te
        592        592     11 SELECT count(*) FROM sys.wri$_adv_execut
        622        369     12 select min(minbkt),maxbkt,substrb(dump(m
        154        148     13 INSERT INTO wri$_adv_inst_fdg (TASK_ID,
        654        157     15 select * from ( select distinct content0
     153538        297     21 select ecmrelatio0_.game_id as col_0_0_
      20684        200     24 select * from ( select playerweek0_.esb_
        998        270     25 select distinct person0_.esb_id as esb1_
      15210        338     30 select playerweek0_.esb_id as esb1_109_,
      27560        250     53 select workoutres0_.combine_person as co
       3811       2445     54 select substrb(dump(val,16,0,32),1,120)
     304150        104     57 select gameschedu2_.game_id as col_0_0_
       1128        609     59 /* SQL Analyze(1) */ select /*+  full(t)
     924880        373     88 select personposi0_.esb_id as esb1_1_, p
      89693        216    122 select * from ( select row_.*, rownum ro
       8560        116    135 select teamgamest0_.game_id as game1_110
     759799        112    162 select * from ( select this_.esb_id as e
      18172        663    186 select persons0_.video_person as video1_
      49341        162    222 select teamstat0_.role as role112_, team
    1455461        719    236 select video0_.id as id134_, video0_.adm
      75284        821    263 select * from ( select this_.workout_res
      10084        183    290 select persongame0_.esb_id as esb1_106_,
     144745        241    301 /*  select new map(max(pts.passingAttemp
      31931       4728    308   declare       cost sys.ODCICost := sys
    1241375        677    465 select personteam0_.esb_id as esb1_1_, p
    1619123        338    542 select * from ( select video0_.id as id1
   16426189       2088    612 /* dynamic native SQL query */ select nv
     101528        431    771 select personteam0_.esb_id as esb1_108_,
                       ------
sum                      5132

35 rows selected.

ENWEBP1P > list
  1  select sum(parse_calls) parse_calls,count(*),
  2  sum(SHARABLE_MEM)/1024/1024 meg,
  3  substr(sql_text,1,40)
  4  from gv$sqlarea
  5  group by substr(sql_text,1,40)
  6  having sum(SHARABLE_MEM)/1024/1024>1
  7  and count(*)>100
  8* order by meg
Previous Topic: Adaptive cursor sharing Doubt!!
Next Topic: Help in tuning the query
Goto Forum:
  


Current Time: Thu Apr 17 22:47:15 CDT 2014

Total time taken to generate the page: 0.09586 seconds