其他方面

3.其他方面
①根据实例来查看进程id。

SQL code

   
   

    
    
    
    

    
    select
    
     spid
  
    
    from
    
     v$process
 
    
    where
    
     addr 
    
    in
    
     (
    
    select
    
     paddr 
    
    from
    
     v$session 
    
    where
    
     sid 
    
    =
    
     $sid)

   
   

②根据进程id来查看实例。

SQL code

   
   

    
    
    
    

    
    select
    
     sid
  
    
    from
    
     v$session
 
    
    where
    
     paddr 
    
    in
    
     (
    
    select
    
     addr 
    
    from
    
     v$process 
    
    where
    
     spid 
    
    =
    
     $pid)

   
   

③查看当前在session中的sql文。

SQL code

   
   

    
    
    
    

    
    select
    
     SQL_TEXT
  
    
    from
    
     V$SQLTEXT
 
    
    where
    
     HASH_VALUE 
    
    =
    
     (
    
    select
    
     SQL_HASH_VALUE 
    
    from
    
     v$session 
    
    where
    
     sid 
    
    =
    
     
    
    &
    
    sid)
 
    
    order
    
     
    
    by
    
     PIECE

   
   

④查看v$session_wait。

SQL code

   
   

    
    
    
    

    
    select
    
     
    
    *
    
    
  
    
    from
    
     v$session_wait
 
    
    where
    
     event 
    
    not
    
     
    
    like
    
     
    
    '
    
    rdbms%
    
    '
    
    
   
    
    and
    
     event 
    
    not
    
     
    
    like
    
     
    
    '
    
    SQL*N%
    
    '
    
    
   
    
    and
    
     event 
    
    not
    
     
    
    like
    
     
    
    '
    
    %timer
    
    '
    
    ;

   
   

⑤Dictionary缓存的命中率。

SQL code

   
   

    
    
    
    

    
    /*
    
    It should be about 15%, otherwise add share_pool_size
    
    */
    
    

    
    SELECT
    
     
    
    sum
    
    (getmisses) 
    
    /
    
     
    
    sum
    
    (gets) 
    
    FROM
    
     v$rowcache;

   
   

⑥利用文件号和数据块来查看DB中的各个对象。

SQL code

   
   

    
    
    
    

    
    select
    
     owner,segment_name,segment_type  
  
    
    from
    
     dba_extents 

    
    where
    
     
    
    file_id
    
     
    
    =
    
     
    
    [
    
    $fno and &dno between block_id and block_id + blocks - 1 
    
    ]
    
    

   
   

⑦寻找hot block。

SQL code

   
   

    
    
    
    

    
    select
    
     
    
    /*
    
    + ordered 
    
    */
    
     
       e.owner 
    
    ||
    
     
    
    '
    
    .
    
    '
    
     
    
    ||
    
     e.segment_name segment_name,
       e.extent_id extent#,
       x.dbablk 
    
    -
    
     e.block_id 
    
    +
    
     
    
    1
    
     block#,
       x.tch,
       l.child#
  
    
    from
    
     sys.v$latch_children l, sys.x$bh x, sys.dba_extents e
 
    
    where
    
     l.name 
    
    =
    
     
    
    '
    
    cache buffers chains
    
    '
    
    
   
    
    and
    
     l.sleeps 
    
    >
    
     
    
    &
    
    sleep_count
   
    
    and
    
     x.hladdr 
    
    =
    
     l.addr
   
    
    and
    
     e.
    
    file_id
    
     
    
    =
    
     x.
    
    file
    
    #
   
    
    and
    
     x.dbablk 
    
    between
    
     e.block_id 
    
    and
    
     e.block_id 
    
    +
    
     e.blocks 
    
    -
    
     
    
    1
    
    ;

   
   

⑧找出每个文件上的等待事件。

SQL code

   
   

    
    
    
    

    
    select
    
     df.name, kf.
    
    count
    
    
  
    
    from
    
     v$datafile df, x$kcbfwait kf
 
    
    where
    
     (kf.indx 
    
    +
    
     
    
    1
    
    ) 
    
    =
    
     df.
    
    file
    
    #;

   
   

⑨找出引起等待事件的SQL语句。

SQL code

   
   

    
    
    
    

    
    select
    
     sql_text, c.event
  
    
    from
    
     v$sqlarea a, v$session b, v$session_wait c
 
    
    where
    
     a.address 
    
    =
    
     b.sql_address
   
    
    and
    
     b.sid 
    
    =
    
     c.sid;

   
   

⑩判断你是从pfile启动还是spfile启动。

SQL code

   
   

    
    
    
    
SQL
    
    >
    
     
    
    select
    
     decode(
    
    count
    
    (
    
    *
    
    ), 
    
    1
    
    , 
    
    '
    
    spfile
    
    '
    
    , 
    
    '
    
    pfile
    
    '
    
     ) 
    
    as
    
     DECODE
  
    
    2
    
      
    
    from
    
     v$spparameter
  
    
    3
    
      
    
    where
    
     rownum
    
    =
    
    1
    
    
  
    
    4
    
      
    
    and
    
     isspecified
    
    =
    
    '
    
    TRUE
    
    '
    
    ;
DECODE

    
    --
    
    ----
    
    

    
    spfile
SQL
    
    >