Search This Blog

Total Pageviews

Tuesday 3 May 2011

Oracle Datafiles Disk I/O

Datafiles Disk IO


set pagesize 100
set linesize 200
col PERC_READS format a10
col name format a40
col PERC_WRITES like PERC_READS
SELECT df.NAME, phyrds Physical_READS,
ROUND((RATIO_TO_REPORT(phyrds) OVER ())*100, 2)|| '%' PERC_READS,
phywrts Physical_WRITES,
ROUND((RATIO_TO_REPORT(phywrts) OVER ())*100, 2)|| '%' PERC_WRITES,
phyrds + phywrts total
FROM v$datafile df, v$filestat fs, ts$ t
WHERE df.FILE# = fs.FILE#
AND df.ts# = t.ts#
-- AND t.NAME = ' xxxx '
ORDER BY phyrds DESC;

Who is using TEMP tablespace?

SQL Running out of Temporary tablespace

TEMP tablespace currently using


1.

set linesize 200
col sid format a12
col sql_text format a50
select s.sid || ',' || s.serial# sid,s.username,u.tablespace,
substr(a.sql_text, 1, (instr(a.sql_text, ' ')-1)) sql_text,
round(((u.blocks*p.value)/1024/1024),2) size_mb,s.sql_id
from v$sort_usage u, v$session s, v$sqlarea a,v$parameter p
where s.saddr = u.session_addr
and a.address (+) = s.sql_address
and a.hash_value (+) = s.sql_hash_value
and p.name = 'db_block_size'
group by s.sid || ',' || s.serial#, s.username,
substr(a.sql_text, 1, (instr (a.sql_text, ' ')-1)),u.tablespace,round(((u.blocks*p.value)/1024/1024),2),s.sql_id;



2

select
s.INST_ID node,
segfile#,
round(sum (((u.blocks*par.value)/1024/1024)),2) size_mb,s.sql_id
from
gv$sort_usage u,
gv$session s,
gv$process p,
v$parameter par
where
s.inst_id=u.inst_id AND
s.saddr = u.session_addr AND
s.paddr = p.addr AND
par.name='db_block_size'
group by
s.INST_ID,
segfile#,s.sql_id
order by 1,2
;


3.

set linesize 140
set pagesize 400
set head on
col iid for 990
col sid_serial for a11
column username format a10
col module for a13
col action for a15
col state for a10
column segfile# format 9,999 heading 'File|ID'
col orapid for 999999 Heading "orapid||Oracle|pid"
col dbpid for a6 HEADING "spid|Unix|pid"
col apppid for a10;
column program format a55
column segblk# format 999,999,999 heading 'Block|ID'

set timi off;
CLEAR COMPUTES;
break on iid

select distinct
s.INST_ID iid,
lpad(s.sid,5)||','|| Lpad(s.serial#,5) sid_serial,
p.pid orapid,
p.spid dbpid,
s.USERNAME,
round(sum (((u.blocks*par.value)/1024/1024)),2) size_mb,
s.status,
s.state,
u.SQL_ID,
segfile#,
s.module,
s.action
from
gv$sort_usage u,
gv$session s,
gv$process p,
v$parameter par
where
s.inst_id=u.inst_id AND
s.saddr = u.session_addr AND
s.paddr = p.addr AND
par.name='db_block_size'
group by
s.INST_ID,
lpad(s.sid,5)||','|| Lpad(s.serial#,5),
p.pid,
p.spid,
s.USERNAME,
s.status,
s.state,
u.SQL_ID,
segfile#,
s.module,
s.action
order by 1,2
;



4

SELECT S.sid || ',' || S.serial# sid_serial, S.username,
T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
T.sqladdr address, Q.hash_value, Q.sql_text
FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE T.session_addr = S.saddr
AND T.sqladdr = Q.address (+)
AND T.tablespace = TBS.tablespace_name
ORDER BY S.sid;

5.


PROMPT consuption by instances, tempfiles:

select
s.INST_ID iid,
segfile#,
round(sum (((u.blocks*par.value)/1024/1024)),2) size_mb
from
gv$sort_usage u,
gv$session s,
gv$process p,
v$parameter par
where
s.inst_id=u.inst_id AND
s.saddr = u.session_addr AND
s.paddr = p.addr AND
par.name='db_block_size'
group by
s.INST_ID,
segfile#
order by 1,2
;

sqlplus Command Line History

sqlplus Command Line History Doskey functionality in sqlplus on linux platform sqlplus History sql doskey ( like old days on dos ) sqlplus doskey

rlwrap is a Command Line History and Editing in SQL*Plus and RMAN on Linux


http://utopia.knoware.nl/~hlub/uck/rlwrap/


wget http://utopia.knoware.nl/~hlub/uck/rlwrap/rlwrap-0.37.tar.gz

apt-amd-02:/home/anujs/Downloads/rlwrap-0.37 # wget http://utopia.knoware.nl/~hlub/uck/rlwrap/rlwrap-0.37.tar.gz
--2011-05-03 12:44:25--  http://utopia.knoware.nl/~hlub/uck/rlwrap/rlwrap-0.37.tar.gz
Resolving utopia.knoware.nl... 213.197.30.29
Connecting to utopia.knoware.nl|213.197.30.29|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 251438 (246K) [application/x-gzip]
Saving to: `rlwrap-0.37.tar.gz'

100%[==========================================================================================================================================>] 251,438     1.12M/s   in 0.2s    

2011-05-03 12:44:25 (1.12 MB/s) - `rlwrap-0.37.tar.gz' saved [251438/251438]


or


rpm 


Download your RPM according to your OS

apt-amd-02:/home/anujs/Downloads # cat /etc/issue
Welcome to openSUSE 11.3 "Teal" - Kernel \r (\l).


rlwrap-0.30-56.1.x86_64.rpm


Download software from  this site as per your OS

http://rpm.pbone.net/index.php3?stat=3&search=rlwrap&srodzaj=3


OpenSuSE  ftp.opensuse.org/distribution/11.2/repo/oss/suse/x86_64/rlwrap-0.30-56.1.x86_64.rpm


install this RPM 


apt-amd-02:/home/anujs/Downloads # rpm -Uvh rlwrap-0.30-56.1.x86_64.rpm
Preparing...                ########################################### [100%]
        package rlwrap-0.30-56.1.x86_64 is already installed


go to oracle user

su - oracle

edit your .bash_profile with following line


alias hsqlplus='rlwrap sqlplus'
alias hman='rlwrap rman'
alias hsql='rlwrap sqlplus'
alias sql="rlwrap -i -f ~/.sqlplus_history -H ~/.sqlplus_history -s 30000 sqlplus"
touch ~/.sqlplus_history



this will store 30000 line in .sqlplus_history file 

or

export RLWRAP_EDITOR="vim +%L -c 'syntax on' -c 'set filetype=sql'"
alias sysdba='rlwrap -m -s5000 sqlplus / as sysdba'




oracle@apt-amd-02:~> hsqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Tue May 3 11:54:26 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Enter user-name: scott/tiger

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BONUS                          TABLE
DEPT                           TABLE
EMP                            TABLE
SALGRADE                       TABLE
T                              TABLE

sql> 

now if you press arrow key, you will able to see previous commands !!!!!!!!!!!!!!!!!!!!!!!!


=====================================================================================================================================


download !!

 wget ftp://ftp.pbone.net/mirror/archive.fedoraproject.org/epel/6/x86_64/Packages/r/rlwrap-0.42-1.el6.x86_64.rpm
 
--2022-03-10 07:51:15--  ftp://ftp.pbone.net/mirror/archive.fedoraproject.org/epel/6/x86_64/Packages/r/rlwrap-0.42-1.el6.x86_64.rpm
           => ‘rlwrap-0.42-1.el6.x86_64.rpm’
Resolving ftp.pbone.net (ftp.pbone.net)... 93.179.225.212
Connecting to ftp.pbone.net (ftp.pbone.net)|93.179.225.212|:21... connected.
Logging in as anonymous ... Logged in!
==> SYST ... done.    ==> PWD ... done.
==> TYPE I ... done.  ==> CWD (1) /mirror/archive.fedoraproject.org/epel/6/x86_64/Packages/r ... done.
==> SIZE rlwrap-0.42-1.el6.x86_64.rpm ... 95240
==> PASV ... done.    ==> RETR rlwrap-0.42-1.el6.x86_64.rpm ... done.
Length: 95240 (93K) (unauthoritative)

100%[===================================================================================================================================================>] 95,240       166KB/s   in 0.6s

2022-03-10 07:51:17 (166 KB/s) - ‘rlwrap-0.42-1.el6.x86_64.rpm’ saved [95240]


rpm -ivh rlwrap-0.42-1.el6.x86_64.rpm
warning: rlwrap-0.42-1.el6.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID 0608b895: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:rlwrap-0.42-1.el6                ################################# [100%]




su - oracle
 touch /home/oracle/.oracle_keywords
 
 
 alias sqlplus='/usr/bin/rlwrap -if $HOME/.oracle_keywords $ORACLE_HOME/bin/sqlplus'
 
 rlwrap sqlplus / as sysdba
 
 
  rlwrap sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Mar 10 07:54:51 2022

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select * from dual;

D
-
X

 

Oracle DBA

anuj blog Archive