LOCKS

###########################################################
---  LOCKS
###########################################################
 
Run the following query to construct a query that retrieves the row affected by the locked row.
Obtain the SID of waiting session from the output of the preceding step.
 
col SESSIONS format A20
SELECT DECODE(REQUEST,0,'Holder SID: ','Waiter SID: ') ||
SID SESSIONS, ID1, ID2, LMODE, REQUEST, TYPE
FROM V$LOCK
WHERE (ID1, ID2, TYPE) IN (SELECT ID1, ID2, TYPE FROM V$LOCK WHERE REQUEST > 0)
ORDER BY ID1, REQUEST;
 
 
SESSIONS                    ID1        ID2      LMODE    REQUEST TY
-------------------- ---------- ---------- ---------- ---------- --
Holder SID: 1            393233        947          6          0 TX
Waiter SID: 111          393233        947          0          6 TX
 
Run the following query to display more information about the waiting session.
 
set pagesize 20
col INFO for a200
SELECT
'SID: '|| S.SID || CHR(10) ||
'USERNAME: '|| S.USERNAME || CHR(10) ||
'EVENT: ' || S.EVENT || CHR(10) ||
'DESCRIPTION: ' || T.DESCRIPTION || CHR(10) ||
'CURRENT STATEMENT: ' || Q.SQL_TEXT || CHR(10) ||
'WAITING TIME (s): ' || S.SECONDS_IN_WAIT || CHR(10) ||
'P1TEXT: ' || S.P1TEXT || CHR(10) ||
'P1: ' || S.P1 || CHR(10) ||
'P2TEXT: ' || S.P2TEXT || CHR(10) ||
'P2: ' || S.P2 || CHR(10) ||
'P3TEXT: ' || S.P3TEXT || CHR(10) ||
'P3: ' || S.P3
 AS INFO
FROM V$SESSION S, V$LOCK L, V$LOCK_TYPE T, V$SQL Q
WHERE S.SID = L.SID AND T.TYPE=L.TYPE AND S.SQL_ID = Q.SQL_ID
AND L.REQUEST>0;
 
INFO
--------------------------------------------------------------------------------
SID: 111
USERNAME: SOE
EVENT: enq: TX - row lock contention
DESCRIPTION: Lock held by a transaction to allow other transactions to wait for
it
CURRENT STATEMENT: UPDATE CUSTOMERS SET CUST_EMAIL = CUST_EMAIL || '' WHERE CUST
OMER_ID =100
WAITING TIME (s): 62
P1TEXT: name|mode
P1: 1415053318
P2TEXT: usn<<16 | slot
P2: 393233
P3TEXT: sequence
P3: 947
 
Run the following query to construct a query that retrieves the row affected by the locked row.
Obtain the SID of waiting session from the output of the preceding step.
 
SELECT 'SELECT * FROM "' || O.OWNER || '"."' || O.OBJECT_NAME || '"
WHERE ROWID = DBMS_ROWID.ROWID_CREATE(1, ' || S.ROW_WAIT_OBJ# || ', ' ||
 S.ROW_WAIT_FILE# || ', ' || ROW_WAIT_BLOCK# || ', ' ||
 ROW_WAIT_ROW# || ');'
FROM DBA_OBJECTS O, V$SESSION S
WHERE S.ROW_WAIT_OBJ# = O.OBJECT_ID AND S.SID = &V_WSID;
 
 
'SELECT*FROM"'||O.OWNER||'"."'||O.OBJECT_NAME||'"WHEREROWID=DBMS_ROWID.ROWID_CRE
--------------------------------------------------------------------------------
SELECT * FROM "SOE"."CUSTOMERS"
WHERE ROWID = DBMS_ROWID.ROWID_CREATE(1, 73366, 5, 1858, 38);
 
==============================================
Generate the ASH report from 10 mins back
==============================================
define dbid = '';
define inst_num = '';
define report_type = 'html';
define begin time
define begin_time ='-10'
define duration = 10;
define report_name = '/u01/ash_enqtx.html'; -- location of the ash report
define slot_width = '';
define target_session_id = '';
define target_sql_id = '';
define target_wait_class = '';
define target_service_hash = '';
define target_module_name = '';
define target_action_name = '';
define target_client_id = '';
define target_plsql_entry = '';
define target_container = '';
@ $ORACLE_HOME/rdbms/admin/ashrpti.sql
 
Run the following query to retrieve the enqueue wait events ever recorded in the database since
the last time the instance was started.
 
col EVENT format a40
col WAIT_CLASS format a11
SELECT EVENT, AVERAGE_WAIT,
TO_CHAR(ROUND(TIME_WAITED/100),'999,999,999') TIME_SECONDS, WAIT_CLASS
FROM V$SYSTEM_EVENT
WHERE EVENT LIKE 'enq%'
ORDER BY TIME_WAITED;
 
EVENT                                    AVERAGE_WAIT TIME_SECONDS WAIT_CLASS
---------------------------------------- ------------ ------------ -----------
enq: CR - block range reuse ckpt                  .27            0 Other
enq: PV - syncstart                             18.34            0 Other
enq: PR - contention                             3.41            0 Other
enq: JG - queue lock                            12.22            0 Other
enq: TX - row lock contention                26001.31          260 Application
 
Run the following query to return the objects that were waited for their locks to be released.
 
col OBJECT_NAME for a10
col stats for a20
SELECT OBJECT_NAME, SUBSTR(STATISTIC_NAME, 1, 30) STATS, VALUE
FROM V$SEGMENT_STATISTICS
WHERE ( STATISTIC_NAME IN( 'ITL waits' , 'row lock waits' ))
 AND VALUE>0 AND OBJECT_NAME NOT LIKE 'BIN$%'
ORDER BY VALUE DESC;
 
OBJECT_NAM STATS                     VALUE
---------- -------------------- ----------
CUSTOMERS  row lock waits                1
 
 
Retrieve the SQL statements that waited the most for wait events of type "Application".
 
col SQL_TEXT for a30
SELECT ROUND(APPLICATION_WAIT_TIME / 1000000) WAIT_TIME_S,
 SQL_ID, SUBSTR(SQL_TEXT,1,30) SQL_TEXT
FROM V$SQLSTATS
WHERE APPLICATION_WAIT_TIME >0
ORDER BY APPLICATION_WAIT_TIME DESC FETCH FIRST 10 ROW ONLY;
 
WAIT_TIME_S SQL_ID        SQL_TEXT
----------- ------------- ------------------------------
        260 1d0m79rpx5h4j UPDATE CUSTOMERS SET CUST_EMAI


Comments

Post a Comment

Popular posts from this blog

To change the data directory location for PostgreSQL after installation

 To change the data directory location for PostgreSQL after installation, you'll need to move the existing data to the new location and update PostgreSQL's configuration to use the new directory. Here’s a step-by-step guide to achieve that: 1. Stop PostgreSQL service : First, stop the PostgreSQL service to ensure no data is being accessed or written during the move. sudo systemctl stop postgresql-15 2. Create the new data directory : Create the new directory where you want to move your PostgreSQL data, and set the appropriate permissions for the postgres user. sudo mkdir -p /new/postgres/pg_data sudo chown postgres:postgres /new/postgres/pg_data sudo chmod 700 /new/postgres/pg_data 3. Move the existing data : Move your existing data from the old directory to the new directory. This step will ensure all your current databases and configurations are preserved. sudo rsync -av --progress /var/lib/pgsql/15/data/ /new/postgres/pg_data/ The rsync command helps preserve permission...
Read more

AWR

Image
  ****** AWR ******  Run the following query to display the AWR settings.  By default, the INTERVAL equals to 1 hour and the RETENTION equals to 8 days.    col SNAP_INTERVAL format a20  col RETENTION format a20  col TOPNSQL format a10  SELECT SNAP_INTERVAL, RETENTION, TOPNSQL FROM DBA_HIST_WR_CONTROL;    SNAP_INTERVAL        RETENTION            TOPNSQL -------------------- -------------------- ---------- +00000 01:00:00.0    +00008 00:00:00.0    DEFAULT     Modify the INTERVAL setting to 30 minutes.    BEGIN   DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(INTERVAL => 30);  END;  /    PL/SQL procedure successfully completed.   SELECT SNAP_INTERVAL, RETENTION, TOPNSQL FROM DBA_HIST_WR_CONTROL;   SNAP_INTERVAL        RETENTION  ...
Read more

GIT

Image
GIT Download : https://git-scm.com/downloads Click the one above link to down load the Git software. Installation: Click on the machine OS supported, for windows click on the download for windows. Click on the OS bit version as per your PC or laptop supported, it will download. Checking the GIT version. GIT -HUB account Creation: Click on this link :   https://github.com/ Open Gmail account check mail from GitHub for code and enter the code.  GIT HUB  Account creation is completed :---- Account creation  END -----: Create a Repository:  (or) :----C reate Repository  END -----: GIT ARCHITECTURE: GIT configuration:      git config --global user.name "Sainath Kota"      git config --global user.email "sainath.kota@gmail.com" Remove an entry in global configuration:     git config --global --unset user.name      git config --global --uns...
Read more