###############Grep Ora- error from Alert log:#################
grep 'Ora-' alert_db.log |tail
Only 5 lines with timings
grep -A5 'Ora-' alert_db.log |tail
#####Total Oracle proces spawned####
ps -ef |grep 'ora'|wc -l
####Total process###
ps -ef |wc -l
--OS Process
expr total process - oracle process count
oerr ora 600
---How to find parallel process:
ps -ef |grep 'ora_p0'
======= KILL Sessions Scenerios====================
You are in situation to quickly kill multiple sessions which consume high resource on database which cause performance issues. You can use the below scripts to quickly act based on your scenario
--SQL script to kill all the sessions from the database — Note: Use this script with caution as this will kill all the sessions from the database
SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' IMMEDIATE;' FROM v$session;
--SQL script to kill all the sessions run with username called TEST
SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' IMMEDIATE;' FROM v$session WHERE USERNAME LIKE '%TEST%'
SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' IMMEDIATE;' FROM v$session WHERE USERNAME LIKE '%TEST%' and sql_id='dpgzymcxvwmba';
--SQL script to kill all the sessions which run with user TEST and which are INACTIVE
SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' IMMEDIATE;' FROM v$session WHERE USERNAME LIKE '%TEST%' AND STATUS='INACTIVE'
---SQL script to kill all the sessions which run with user TEST and which are INACTIVE and run for more than 10000 seconds. You can modify the script according to your scenario
SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' IMMEDIATE;' FROM v$session WHERE USERNAME LIKE '%TEST%' AND STATUS='INACTIVE' and LAST_CALL_ET > 10000;
--SQL script to kill all the sessions which run with user TEST and which are INACTIVE and run for more than 10000 seconds and logged on users from last 24 hours
SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' IMMEDIATE;' FROM v$session WHERE USERNAME LIKE '%TEST%' AND STATUS='INACTIVE' and LAST_CALL_ET > 10000 and LOGON_TIME > sysdate - 1 ;
--SQL script to kill all the sessions which run sql queries
SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' IMMEDIATE;' FROM v$session WHERE SQL_ID is NOT NULL;
--SQL script to kill all the sessions which has blockings
SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' IMMEDIATE;' FROM v$session WHERE BLOCKING_SESSION is NOT NULL;
--Script to kill RMAN jobs
SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' IMMEDIATE;' FROM v$session WHERE CLIENT_INFO LIKE '%rman%;
==========================================================================
######SPID, MAPING SPID WITH SID#######
select se.sid,se.serial#,se.sql_id,se.machine,se.terminal,se.module,se.action,pr.spid from v$session se
inner join v$process pr on (pr.addr = se.paddr) where pr.spid ='&spid';
####Shell script to select data####
#!/bin/bash
export ORACLE_SID=db9zx
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
while true
do
$ORACLE_HOME/bin/sqlplus -S '/ as sysdba' <<EOF
select * from kish.xtbl;
exit;
EOF
done
==========Table Lock and Blocking====================================
lock table xtbl in exclusive mode;
select sid as blocked,serial#,machine,program,sql_id,command,blocking_session as blocker from v$session where blocking_session is not null;
Note: Command value 6 means update, 2 means insert, 3 means select,
==========================================================================
Database Version Check:
select * from v$version;
Select * from product_component_version;
or
[oracle@hostname]locate globalvariables.xml
############UNDO TABLESPACE CONCEPT##############
kIsH@Xhydra<>select max(MAXQUERYLEN) from dba_hist_undostat;
MAX(MAXQUERYLEN)
----------------------------------------------------------------------
3408
kIsH@Xhydra<>select max(UNDOBLKS / ((end_time - begin_time) * 60 * 60 * 24)) undo_blk_per_sec from dba_hist_undostat;
UNDO_BLK_PER_SEC
----------------------------------------------------------------------
8.965
Telecom sector - There are different types of customers.
example:
Compare the situation of telecom to undo configuration
1) premium customer (300 mbps speed) - 10 hours query (consume 10gb undo)
2) non premium customer (100 mbps speed) - 5 minutes query (consume 5 mb undo)
We cannot say to premium customer that 100 mbps speed will be provided. Premium customers disagree with that since they pay more.
Similarly, we cannot configure less undo for 10 hours query. 10 hours query needs more undo since it process more data.
That is why we use this formula "max(UNDOBLKS / ((end_time - begin_time) * 60 * 60 * 24))" to calculate undo blocks per second generation
kIsH@Xhydra<>--optimal undo retention
kIsH@Xhydra<>--optimal undo tablespace size
kIsH@Xhydra<>
kIsH@Xhydra<>show parameter undo_retention
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_retention integer 900
kIsH@Xhydra<>select sum(bytes) from dba_data_files where file_name like '%undo%' or file_name like '%UNDO%';
SUM(BYTES)
----------------------------------------------------------------------
6103957504
kIsH@Xhydra<>show parameter db_block_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
kIsH@Xhydra<>select max(UNDOBLKS / ((end_time - begin_time) * 60 * 60 * 24)) undo_blk_per_sec from dba_hist_undostat;
UNDO_BLK_PER_SEC
----------------------------------------------------------------------
8.965
kIsH@Xhydra<>--optimal undo retention = undo tablespace size / (db_block_size * max(undo_block_per_sec)
kIsH@Xhydra<>--optimal undo tablespace = optimal undo retention * db_block_size * max(undo_block_per_sec)
kIsH@Xhydra<>select 6103957504 / ( 8192 * 8.965 ) optimal_retention_undo from dual;
OPTIMAL_RETENTION_UNDO
----------------------------------------------------------------------
83113.4411600669269380925822643614054657
kIsH@Xhydra<>alter system set undo_retention=3408;
System altered.
kIsH@Xhydra<>alter system set undo_retention=900;
System altered.
kIsH@Xhydra<>select 83113 * 8192 * 8.965 optimal_undo_size from dual;
OPTIMAL_UNDO_SIZE
----------------------------------------------------------------------
6103925104.64
kIsH@Xhydra<>select round(6103925104.64/1048576) MB from dual;
MB
----------------------------------------------------------------------
5821
kIsH@Xhydra<>select sum(bytes)/1048576 MB from dba_data_files where file_name like '%undo%' or file_name like '%UNDO%';
MB
----------------------------------------------------------------------
5821.1875
----undostat
set lines 200 pages 1000
col SQL_TEXT for a20
col NOSPACEERRCNT for 999
col UNSC for 999
col ORA1555 for 999
select to_char(us.BEGIN_TIME,'DD-MM-YY HH24:MI') BT,
to_char(us.END_TIME,'DD-MM-YY HH24:MI') ET,
us.MAXQUERYLEN as MQL,
us.MAXQUERYID,
st.SQL_TEXT,
us.TUNED_UNDORETENTION as TR,
us.ACTIVEBLKS as ABLK, --Retention not valid but space is valid
us.UNEXPIREDBLKS as UXBLK, --Commited txn but retention not passed and need for Read consistency
us.EXPIREDBLKS as XBLK, --Txn Commited and no longer required for RC and RB
us.NOSPACEERRCNT as ORA30036,
us.SSOLDERRCNT as ORA1555,
us.UNXPSTEALCNT as UNSC
from v$undostat us
inner join v$sqltext st on (us.MAXQUERYID = st.sql_id)
order by us.MAXQUERYLEN desc;
=======================================================================
--temp usage
Scenario:
temp size - 2gb
TIME 1: A protracted query is launched by Session 1.
TIME 2: At this moment, Session 1 has used up 1.8 GB of the available space on TEMP and Session 2 has begun a query.
TIME 3: Because the tablespace has run out of free space, Sessions 1 and 2 get an ORA-1652.
Both sessions are terminated, and all temporary storage used by them is released (the segments used are marked FREE for reuse)
TIME 4: SMON decommissions the temporary segments utilised by Sessions 1 and 2. (deallocates the storage)
V$SORTSEG USAGE, V$TEMSEG USAGE, and V$SORT SEGMENT.
--Sort usage by amount sort and sqlid
set lines 200 pages 1000
col USERNAME format a10
col TABLESPACE format a15
col SQL_TEXT format a20
col SID format 999999
col EVENT format a15
col PROGRAM format a15
col serial# format 99999
col used format 99999
select su.username,
su.sql_id,
su.tablespace,
sq.sql_text,
se.sid,
se.program,
se.serial#,
se.event,
(su.blocks)*(tb.block_size/1048576) usedMB
from v$sort_usage su
inner join v$sqlarea sq on su.sql_id=sq.sql_id
inner join v$session se on su.session_addr=se.saddr
inner join dba_tablespaces tb on su.tablespace=tb.tablespace_name
order by su.username;
--process id which consume high sort
set lines 200 pages 1000
col USERNAME format a10
col TABLESPACE format a10
col SQL_TEXT format a20
col SID format 999999
col EVENT format a15
col PROGRAM format a15
col serial# format 99999
col used format 99999
col spid format 999999
col sorts format 999
col SORT_CNT for 999
col USEDMB for 999999
select su.username, su.sql_id, su.tablespace, p.spid, count(*) sort_cnt, se.sid, se.program, se.serial#, se.event, sum(su.blocks)*(tb.block_size/1048576) usedMB from v$sort_usage su inner join v$session se on su.session_addr=se.saddr inner join v$process p on p.addr=se.paddr inner join dba_tablespaces tb on su.tablespace=tb.tablespace_name group by su.username,su.sql_id,su.tablespace,p.spid,se.sid,se.program,se.serial#,se.event,tb.block_size/1048576 order by su.username;
select a.* from dba_source a
inner join dba_source1 b on b.name = a.name
order by a.name desc;
select a.* from dba_source a
order by a.name desc;
==============================================
select * from product_component_version;
####### How to check last analysed of tables####
SELECT owner, table_name, last_analyzed FROM dba_tables WHERE last_analyzed IS NOT NULL and owner not in('SYS','SYSTEM','XDB','ORDDATA','APPQOSSYS','GSMADMIN_INTERNAL','DBSNMP','WMSYS','CTXSYS','OUTLN','ORDSYS','MDSYS','LBACSYS','APEX_040200','FLOWS_FILES','DVSYS','OJVMSYS','OLAPSYS','AUDSYS') ORDER BY last_analyzed DESC;
