Saturday, 15 October 2022

Find SID from Process ID (PID) in Oracle

 How to find SID from PID in Oracle Database?

The below query helps in finding the Session ID using Process ID within Oracle Database,

SELECT s.SID,

       s.serial#,

       s.machine,

       s.osuser,

       s.terminal,

       s.username

  FROM v$process P LEFT OUTER JOIN v$session s ON P.addr = s.paddr

 WHERE P.spid = 12345;

Components of Oracle DB BUFFER CACHE

What are the components of DB BUFFER CACHE?

Below mentioned are the components of db buffer cache,

  • Default pool (where blocks are normally buffered)
  • Big table cache (optional part of the default pool; uses a temperature based algorithm instead of the LRU based one)
  • Keep pool - data buffers for frequently running queries
  • Recycle pool - data buffers for least running queries
  • Non-default buffer pools (used for non-standard block sizes of 2, 4, 16 or 32 KB, when you have different tablespaces with different db block sizing)
  • Database Smart Flash Cache (used in flash cache)
  • Least Recently Used list (LRU)
  • Checkpoint queue
  • Flash Buffer Area

Cache Hit vs Cache Miss in Oracle Database

 Difference between Cache Hit and Cache Miss

Cache Hit: When running a SELECT statement within Oracle database, if the relevant data is already found in the db buffer cache, that signifies its a Cache Hit. 

Cache Miss: When any transaction is not able to find data in the db buffer cache and it has to go to datafile to fetch that data, it means cache miss. Which means it would take more time to process that transaction.

The more Cache hit ratio you have, the better performance you have within your database. It is always beneficial to keep data buffers available for frequently running queries within db buffer cache and hence database memory components should be tuned accordingly.

Important Parameters related to DB_BUFFER_CACHE

 Important Parameters related to DB BUFFER CACHE in Oracle Database

What are the parameters related to Oracle Database Buffer Cache? How to modify/tune db buffer cache within Oracle Database?

The below mentioned parameters are useful when you want to configure DB BUFFER CACHE as per your need,

Saturday, 16 July 2022

Oracle Query to check OS PID from SID

Oracle Query to check OS PID from SID

How to find OS Process ID using Session ID in oracle?

Below query will help you extract the PID on OS using SID. Normally in OS, different processes takes care of different operations and the same goes for Oracle as well. In random scenarios, you could be interested in finding PID for some operation running within database for example, a background job for which you want to track the resource consumption. The below query will help in such scenarios.

Set lines 200
col sid format 99999
col username format a15
col osuser format a15
select a.sid, a.serial#,a.username, a.osuser, b.spid
from v$session a, v$process b
where a.paddr= b.addr
and a.sid='&sid'
order by a.sid;
/

How to find SQL Text From SID?

How to find SQL Text From Session ID?

The below queries would help you find SQL Text running behind some Oracle SID.


col sql_text form a80
set lines 120
select sql_text from gv$sqltext where hash_value=
(select sql_hash_value from gv$session where sid=&1)
order by piece
/

It will prompt you to enter SID for which you want to find running SQL Query, (here i am entering my own SID so it will show you the same query with provided details)

SQL> SQL> 2 3 4
Enter value for 1: 175 old 2: (select sql_hash_value from gv$session where sid=&1)
new 2: (select sql_hash_value from gv$session where sid=175)

SQL_TEXT
--------------------------------------------------------------------------------
select sql_text from gv$sqltext where hash_value= (select sql_ha
sh_value from gv$session where sid=175) order by piece

2 rows selected.

Thursday, 17 February 2022

ORA-15080 ORA-27070 ORA-63999 ORA-01114 ORA-15081 in alert log

ORA-15080: synchronous I/O operation to a disk failed | ORA-27070: async read/write failed | ORA-15081: failed to submit an I/O operation to a disk

These Oracle errors would lead Oracle instance to crash and won't let it start till you fix the issue. The below IO errors shows the issue with Oracle Storage Management when there's some issue while performing write operation on Oracle file.