SQLT aids in diagnosing SQL statement performance problems by collecting and analyzing detailed trace data.
Sharing knowledge is the ultimate key to gaining knowledge…
The only two things that stay with you for life are you & your knowledge...
Live while you can! Teach & inspire while you could & Smile while you have the teeth.
-
ASM
- Creating ASM Instance and diskgroups manually without DBCA
- 20 ASM Realtime | Interview Questions
- How to make a cold backup for database running on ASM ( using RMAN )
- Moving a Single Datafile from File System to ASM System (METHOD 1)
- Moving a Single Datafile from File System to ASM System (METHOD 2)
- Things to consider while adding new disk/ Rebalancing existing Disk Group
-
Dataguard
- Dataguard Modes
- Dataguard Modes - II
- Oracle DataGuard Setup
- invalid archived log deletion policy
- recover from the Loss of a Datafile on a Standby Database ?
- MRP terminated with ORA-00600: internal error code, arguments
- Recover archive gaps in standby database - using 2 methodss
- Gap Resolution in Dataguard
- Convert Physical Standby to Snapshot Standby and Vice Versa
- Re-create Redo-logs on Standby/ Primary
- Pre-check for DataGuard switchover Process
- ORA-19815: WARNING: db_recovery_file_dest_size
- How do you purge old archivelogs which are applied on Standby DB
- Pre-check For SWITCHOVER using DG Broker
- https://oracle-dba-help.blogspot.com/2016/03/data-guard-modes.html
- ORA-01274 : RENAME THE UNKNOW FILE IN THE STANDBY DATABASE
- Database Creation in Easy 6 Steps
- Oracle Networking
- Tablespace management
- Housekeep : Quick Tips for DBA
- Health check for DB
- ORA-12537 While Connecting To Database Via Listener
- How to identify the applied patche-set of your Oracle Home
- Accessing a schema without knowing the password
- ORA-12505: TNS:listener does not currently know of SID given in connect descriptor
- Session Related Scripts
- How to check ORA Error on DB Server – Beginner Tip
- Working with Redo Logs
- Decommission of Oracle Database
- Roles in Oracle - Managing Users
- Oracle Release Number Format
- Troubleshooting Handy-Guide
- Understanding the Upgradation Process
- Query to find the session creating more redo
- Audit in Oracle
- Troubleshooting Internal Errors and Error-Look-up Tool on MOS
- Troubleshooting Issues with Undo Tablespace
- User Management
- User Management - II
- Data Pump Gets Better
- MEMORY_TARGET Parameter
- Installing PostgreSQL Server on CentOS, RHEL 6 / 7 Systems
- Creating a database in Postgres
- Initialize multiple Postgres instance on the same RHEL server
- How to stop starting of clusters while starting up services
- Setup a streaming replication
- Switchover- master server and warm standby (with streaming replication)
- All About Physical Replication and Log shipping
- PostgreSQL Upgrade and troubleshooting
- Timeline Switch issue
- Query to identify the lag in replication
- Drop table : ERROR: must be owner of relation table_name
- PgBadger
- PgCenter
- PgTune
- Internals of Toast Table
- Influencing Query Execution Plan
- Create Extension ERROR
- Find Uptime in PostgreSQL
- Set DB Timezone
- Clear RAM Cache, Buffer
- Read-Only User in Postgres
- Everything you need to know about Streaming Replication
- Ora2Pg Migrate Oracle to Postgres
- Total Page Cost
- PostgreSQL 13 - Everything you need to know
- Startup Failure - .conf file
- All About Postgres Upgrade
- Basic Guide
- Session Related Scripts
- AWR :: Beginners Guide
- All About ASH - Active Session History
- Wait Events : checkpoint busy waits or archiver busy waits
- Find sessions performing sort operations : TEMP Tablespace occupants
- Generate ADDM task and generate its report for Tuning
- Database Performance Tuning Scripts -Keep Handy
- Buffer Busy Wait
- Simulating the Deadlock
- Latches
- Steps to Schedule any RMAN Jobs In Windows Server
- ORA-01194: file 1 needs more recovery to be consistent
- Loss of Current Online Redo-Log
- RMAN "baby Steps"
- Recovery of ControlFiles
- Loss of Datafile
- Recovery from complete loss of all online redo log files
- Block Change Tracking
- RMAN Questions
- Instance Recovery
- Redundancy and Recovery window in RMAN
- Cold Backup
- Database Cloning
- Contact
- Drop Database using RMAN
- PITR to older Incarnation
Showing posts with label Tuning. Show all posts
Showing posts with label Tuning. Show all posts
Sunday 14 April 2024
Saturday 6 November 2021
Resmgr:Cpu Quantum | Oracle wait event
SCOPE
Application job are running slow and DBA finds out the wait event as "Resmgr:Cpu Quantum" for multiple sessions running in database.
Friday 15 January 2021
Is it possible to view database performance information without DBA privilege in Oracle 11g?
The DBA can grant you SELECT privileges to the V$ performance views. It's good for instance tuning, but not as effective as SQL Trace or ADVISOR privilege for query tuning.
Saturday 19 December 2020
Monday 26 March 2018
Wait Events : checkpoint busy waits or archiver busy waits
While
such wait events occur in AlertLog file, one must consider to proceed with ARCHIVER
TUNING
1.
Check the number of Online Redolog Members and size of the online redo logs.
Excessive size and the number of online redo log groups will give archiver more
time to catch up. Hence Adding more online logs does not help a situation where
the archiver cannot keep up with LGWR process.
It can help if there are bursts of redo
generation since it gives ARCH more time to average its processing rate over
time.
2.
In such cases you can add multiple archiver (ARCh) processes
Create
'alter system archive log all'. This will spawn archive processes at some fixed
interval may be required. These processes once spawned will assist archiver in
archiving any un-archived log in that thread of redo. Once it has been
completed, the temporary processes will go away.
3.
Evaluate checkpoint interval and frequency
There
are several possible actions include adding DBWR processes, increasing db_block_checkpoint_batch, reducing db_block_buffers. Turning on or allowing
async IO capabilities definitely helps alleviate most DBWR inefficiencies.
4.
Check OS supportability of asynchronous I/Os
Async
reads should help tremendously. Async writes may help if OS supports
asynchronous I/Os on file systems.
You
can check with your vendor if the current version of your operating system supports
async IO to file systems (ufs).
5.
Check for system or IO contention.
Check
CPU waits and usage, disk level
bottlenecks. Also check operating system manuals for the appropriate commands
to monitor system performance.
For
example, you can use UNIX commands such
as "sar 5 5 5" “sar –d ”or "iostat" to identify
disk bottlenecks.
Friday 26 January 2018
Find sessions performing sort operations : TEMP Tablespace occupants
Most of the time, user will ask you to check long
running sessions in database and you will figure out the query which the
session is performing sort/join operation – reading the query you can say the
query is related to sorting. But you will also see the high usage of
Temporary segments or will see wait events such as “Direct Path Temp Read” Or
“Direct Path Temp Write”.
To rectify this you can use views such as
V$SORT_USAGE and V$SORT_SEGMENT to identify the other sessions performing sort
operation
Oracle provides two basic dynamic performance views
for online monitoring of temporary tablespace usage.
These are v$sort_usage to
query online sessions using temporary segments and v$sort_segment to query the
size of the temporary segments in the database.
V$SORT_USAGE has below main columns which can be useful here:
USERNAME
–
Session User name.
SQL_ID –
unique identifier of the SQL that requires the sort.
EXTENTS –
number
of extents in the temporary segment being used by particular sessions.
BLOCKS –
number of blocks in the temporary segment being used by particular
sessions.
SESSION_ADDR – address of the session, can be used to
identify the session in V$SESSION according to the SADDR column.
--Use below query to find the sessions consuming temp
segments
SELECT S.sid || ‘,’ || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module, P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace, COUNT(*) statements FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P WHERE T.session_addr = S.saddr AND S.paddr = P.addr AND T.tablespace = TBS.tablespace_name GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module, P.program, TBS.block_size, T.tablespace ORDER BY sid_serial;
This will give the sid and serial# which be used to
find current query that is taking temp segment using view v$sqltext and passing
sql_id of the sid and serial#
using V$SORT_SEGMENT
SELECT A.tablespace_name tablespace, D.mb_total, SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used, D.mb_total-SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free FROM v$sort_segment A, ( SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total FROM v$tablespace B, v$tempfile C WHERE B.ts#= C.ts# GROUP BY B.name, C.block_size ) D WHERE A.tablespace_name = D.name GROUP by A.tablespace_name, D.mb_total;
Thursday 10 August 2017
How to generate ADDM task and generate its report for Tuning Purpose
In
this post, I will explain How to create ADDM task and check its report
We are
using begin snapshot as 500 And end snapshots as 550
BEGIN
--------At
First, we need to Create an ADDM task using the below procedure---------.
DBMS_ADVISOR.create_task
(
advisor_name => ‘ADDM’,
task_name => ‘500_550_AWR_SNAPSHOT’,
task_desc => ‘ADDM for snapshots 500 to 550.’);
advisor_name => ‘ADDM’,
task_name => ‘500_550_AWR_SNAPSHOT’,
task_desc => ‘ADDM for snapshots 500 to 550.’);
--------—
Now, Set the start and end snapshots as parameters to display. ------------.
DBMS_ADVISOR.set_task_parameter
(
task_name => ‘500_550_AWR_SNAPSHOT’,
parameter => ‘START_SNAPSHOT’,
value => 500);
DBMS_ADVISOR.set_task_parameter (
task_name => ‘500_550_AWR_SNAPSHOT’,
parameter => ‘END_SNAPSHOT’,
value => 550);
task_name => ‘500_550_AWR_SNAPSHOT’,
parameter => ‘START_SNAPSHOT’,
value => 500);
DBMS_ADVISOR.set_task_parameter (
task_name => ‘500_550_AWR_SNAPSHOT’,
parameter => ‘END_SNAPSHOT’,
value => 550);
—------ Now, Execute the task to display the report as parsing the task name.
--------.
DBMS_ADVISOR.execute_task(task_name
=> ‘500_550_AWR_SNAPSHOT’,);
END;
/
END;
/
—-------Finally, execute the following proc to display the report on console.
-----------.
SET LONG 100000
SET PAGESIZE 50000
SELECT DBMS_ADVISOR.get_task_report(‘500_550_AWR_SNAPSHOT’) AS report
FROM dual;
SET PAGESIZE 24
SET PAGESIZE 50000
SELECT DBMS_ADVISOR.get_task_report(‘500_550_AWR_SNAPSHOT’) AS report
FROM dual;
SET PAGESIZE 24
ADDM Related Views for DBA
DBA_ADVISOR_TASKS
– Basic information about existing tasks.
DBA_ADVISOR_LOG – Status information about existing tasks.
DBA_ADVISOR_FINDINGS – Findings identified for an existing task.
DBA_ADVISOR_RECOMMENDATIONS – Recommendations for the problems identified by an existing task.
DBA_ADVISOR_LOG – Status information about existing tasks.
DBA_ADVISOR_FINDINGS – Findings identified for an existing task.
DBA_ADVISOR_RECOMMENDATIONS – Recommendations for the problems identified by an existing task.
See Also : Performace Tuning Scripts
20 ASM Real-time Interview Questions
Sunday 30 July 2017
Database Performance Tuning Scripts -Keep Handy
****************************************************************************
Database Performance Tuning Scripts
****************************************************************************
---------------------------------Measure
the Buffer Cache Hit Ratio------------------------------------
Increase
DB_BLOCK_BUFFER if cache hit ratio < 90%
-------------------------------------------------------------------------------select
1-(phy.value / (cur.value + con.value)) "Cache Hit Ratio",
round((1-(phy.value
/ (cur.value + con.value)))*100,2) "% Ratio"
from
v$sysstat cur, v$sysstat con, v$sysstat phy
where
cur.name = 'db block gets' and
con.name
= 'consistent gets' and
phy.name
= 'physical reads'
/
select
(d.value+c.value-p.value)/(d.value+c.value)*100 "hit ratio"
from
v$sysstat d,v$sysstat c,v$sysstat p
where
d.name='db block gets' and
c.name='consistent
gets' and
p.name
='physical reads'
/
col
name format a20 heading "Buffer Pool Name
select
name,free_buffer_wait,write_complete_wait,
buffer_busy_wait,db_block_gets,
consistent_gets,physical_reads,
physical_writes
from
v$buffer_pool_statistics;
----------------Sessions
with bad Buffer Cache Hit Ratio in %---------------
select substr(a.sid,1,5)
"Sid",
substr(a.username,1,10)
"User",
substr(a.osuser,1,23)
USER_NM,
--substr(a.machine,1,23)
MACHINE_NM,
substr(a.program,1,17)
program,
b.consistent_gets
"ConsGets",
b.block_gets
"BlockGets",
b.physical_reads
"PhysReads",
100
* round((b.consistent_gets + b.block_gets - b.physical_reads) /
(b.consistent_gets
+ b.block_gets),3) HitRatio
from
v$session a, v$sess_io b
where
a.sid = b.sid
and
(b.consistent_gets + b.block_gets) > 0
and
a.username is not null
and
a.username not in ('SYS','SYSTEM')
order
by HitRatio asc
/
-------------response
time per transaction---------------------------
select ((WAIT + val) / TRXN_CNT)
as TRXN_SPEED
from
(select sum(TOTAL_WAITS) as WAIT from v$system_event) ,
(select
value as VAL from v$sysstat where name='CPU used by this session'),
(select
sum(value) as TRXN_CNT from v$sysstat where name in ('user rollbacks','user
commits'))
/
------------sql
text executing morethan 25 times(SQL Hard Parsing )--------------
select
substr(sql_text,1,50), count(*) from v$sql
group
by substr(sql_text,1,50) having count(*) > 25
/
**---------------------Latch
Contention----------------**
col
name format a40 heading "Latch Name"
SELECT
name, gets, misses,
round((gets-misses)/decode(gets,0,1,gets),3)
hit_ratio,
sleeps,
round(sleeps/decode(misses,0,1,misses),3)
"sleeps/misses"
from
v$latch
where
gets != 0
order
by name;
select
name,immediate_gets,immediate_misses,(immediate_gets)/(immediate_gets+immediate_misses)
Hit_Ratio
from
v$latch where immediate_gets != 0;
**---------------Shared
Pool Statistics-------------------------------**
col
namespace format a22
select
namespace,gets,gethits,round(gethitratio,2)
gethitratio,pins,pinhits,round(pinhitratio,2)
pinhitratio,reloads,invalidations
from v$librarycache;
----------------------------------------------------------------------Display
database SGA statistics
----------------------------------------------------------------------DECLARE
libcac
number(10,2);
rowcac
number(10,2);
bufcac
number(10,2);
redlog
number(10,2);
spsize
number;
blkbuf
number;
logbuf
number;
BEGIN
select
value into redlog from v$sysstat
where
name = 'redo log space requests';
select
100*(sum(pins)-sum(reloads))/sum(pins) into libcac from v$librarycache;
select
100*(sum(gets)-sum(getmisses))/sum(gets) into rowcac from v$rowcache;
select
100*(cur.value + con.value - phys.value)/(cur.value + con.value) into bufcac
from
v$sysstat cur,v$sysstat con,v$sysstat phys,v$statname ncu,v$statname
nco,v$statname nph
where
cur.statistic# = ncu.statistic#
and
ncu.name = 'db block gets'
and
con.statistic# = nco.statistic#
and
nco.name = 'consistent gets'
and
phys.statistic# = nph.statistic#
and
nph.name = 'physical reads';
select
value into spsize from v$parameter where name = 'shared_pool_size';
select
value into blkbuf from v$parameter where name = 'db_block_buffers';
select
value into logbuf from v$parameter where name = 'log_buffer';
dbms_output.put_line('>
SGA
CACHE STATISTICS');
dbms_output.put_line('>
********************');
dbms_output.put_line('>
SQL
Cache Hit rate = '||libcac);
dbms_output.put_line('>
Dict
Cache Hit rate = '||rowcac);
dbms_output.put_line('>
Buffer
Cache Hit rate = '||bufcac);
dbms_output.put_line('>
Redo
Log space requests = '||redlog);
dbms_output.put_line('>
');
dbms_output.put_line('>
INIT.ORA
SETTING');
dbms_output.put_line('>
****************');
dbms_output.put_line('>
Shared
Pool Size = '||spsize||' Bytes');
dbms_output.put_line('>
DB
Block Buffer = '||blkbuf||' Blocks');
dbms_output.put_line('>
Log
Buffer = '||logbuf||' Bytes');
dbms_output.put_line('>
');
if
libcac
< 99 then dbms_output.put_line('*** HINT: Library Cache too low! Increase
the Shared Pool Size.');
END
IF;
if
rowcac
< 85 then dbms_output.put_line('*** HINT: Row Cache too low! Increase the
Shared Pool Size.');
END
IF;
if
bufcac
< 90 then dbms_output.put_line('*** HINT: Buffer Cache too low! Increase the
DB Block Buffer value.');
END
IF;
if
redlog
> 100 then dbms_output.put_line('*** HINT: Log Buffer value is rather
low!');
END
IF;
END;
/
---------------List
all supported INIT.ORA parameters----------------------------
select a.ksppinm
name, b.ksppstvl value, b.ksppstdf isdefault,
decode(a.ksppity,
1, 'boolean', 2, 'string', 3, 'number', 4, 'file',
a.ksppity)
type,
a.ksppdesc
description
from
sys.x$ksppi a, sys.x$ksppcv b
where
a.indx = b.indx
and
a.ksppinm not like '\_%' escape '\'
order
by name
/
--------------List
all un-supported INIT.ORA parameters-------------------------
select a.ksppinm
name, b.ksppstvl value, b.ksppstdf isdefault,
decode(a.ksppity,
1, 'boolean', 2, 'string', 3, 'number', 4, 'file',
a.ksppity)
type,
a.ksppdesc
description
from
sys.x$ksppi a, sys.x$ksppcv b
where
a.indx = b.indx
and
a.ksppinm like '\_%' escape '\'
order
by name
/
---------------active
(in progress) transactionsROLLBACK SEG-----------------
col name format a8
col
username format a8
col
osuser format a8
col
start_time format a17
col
status format a12
tti
'Active transactions'
select
username, terminal, osuser,
t.start_time,
r.name, t.used_ublk "ROLLB BLKS",
decode(t.space,
'YES', 'SPACE TX',
decode(t.recursive,
'YES', 'RECURSIVE TX',
decode(t.noundo,
'YES', 'NO UNDO TX', t.status)
))
status
from
sys.v_$transaction t, sys.v_$rollname r, sys.v_$session s
where
t.xidusn = r.usn
and
t.ses_addr = s.saddr
/
-------------------- Display rollback segment statistics---------------------------
column
"Rollback Segment"
format
a16
column
"Size (Kb)"
format
9,999,999
column
"Gets"
format
999,999,990
column
"Waits"
format
9,999,990
column
"% Waits"
format
90.00
column
"# Shrinks"
format
999,990
column
"# Extends"
format
999,990
Prompt
Prompt
Rollback Segment Statistics...
Select
rn.Name "Rollback Segment", rs.RSSize/1024 "Size (KB)",
rs.Gets "Gets",
rs.waits
"Waits", (rs.Waits/rs.Gets)*100 "% Waits",
rs.Shrinks
"# Shrinks", rs.Extends "# Extends"
from
sys.v_$RollName rn, sys.v_$RollStat rs
where
rn.usn = rs.usn
/
show
sga
show
parameters area_size
set
pages 999;
column
pga_size format 999,999,999
select
2048576+a.value+b.value
pga_size
from
v$parameter a,v$parameter b
where
a.name = 'sort_area_size'
and
b.name = 'hash_area_size';
column
pga_size format 999,999,999
accept
hwm number prompt 'Enter the high-water mark of connected users: '
select
150*(2048576+a.value+b.value)
pga_size
from
v$parameter a, v$parameter b
where
a.name = 'sort_area_size'
and
b.name = 'hash_area_size';
****************************************************************************************************
================================
Database Health monitoring script.
================================
REM
Segments Max extents & Current extent comparison
set
line 180
set
pagesize 10000
set
feedback off
prompt
col
Time format a50 heading "System Time"
select
to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS') Time from dual;
prompt
prompt
prompt
Rem**-------------Objects
Reaching Max extents-----------**
col
segment_name format a40 heading "Object Name"
col
max_extents format 9999999999 heading "Max Extents"
col
curr_extents format 99999999999 heading "Curent Extents"
select
a.segment_name,a.max_extents,b.curr_extents from dba_segments a,(select
segment_name,max(extent_id)
curr_extents from dba_extents group by segment_name) b where a.segment_name =
b.segment_name
and
(a.max_extents - b.curr_extents) <= 10;
Rem**--------------User
Session Information-----------------**
col
sid format 9999 heading "SID"
col
serial# format 9999999 heading "SERIAL NO"
col
logon_time format 9999999 heading "Login Time"
col
username format a12
col
osuser format a24
col
program format a38
select
s.sid,s.serial#,p.spid,s.username,s.osuser,s.program,to_char(s.LOGON_TIME,'DD-MON-YY:HH24:MI:SS')
"Log on
Time",round((s.LAST_CALL_ET/(60*60*24)),2)"Wait
in Days" from v$session s,v$process p where s.paddr = p.addr and
s.username
is not null order by 8 desc;
Rem**----------------------File
Information-------------------------**
col
file_name format a55 heading "File Name"
col
SizeInMB format 99999999 heading "Total Size (MB)"
col
MAXSIZE format 99999999 heading "Maximum Size (MB)"
compute
sum of SizeInMB on report
select
substr(file_name,1,60)
FIL_NAME,
BYTES/(1024*1024)
SizeInMB,
AUTOEXTENSIBLE,
MAXBYTES/(1024*1024)
MAXSIZE
from
dba_data_files
union
all
select
substr(file_name,1,60)
FIL_NAME,
BYTES/(1024*1024)
SizeInMB,
AUTOEXTENSIBLE,
MAXBYTES/(1024*1024)
MAXSIZE
from
dba_temp_files;
Rem**-----------------Tablespace
Information--------------------------*
col
tablespace_name format a25 heading "Tablespace Name"
col
logging format a10
col
status format a12
col
extent_management format a30 heading "Local/Dict"
select
tablespace_name,status,contents,decode(logging,LOGGING,'YES','NO')
Logging,extent_management from dba_tablespaces;
SELECT
Total.name "Tablespace Name",
Free_space,
(total_space-Free_space) Used_space, total_space
FROM
(select
tablespace_name, sum(bytes/1024/1024) Free_Space
from
sys.dba_free_space
group
by tablespace_name
)
Free,
(select
b.name, sum(bytes/1024/1024) TOTAL_SPACE
from
sys.v_$datafile a, sys.v_$tablespace B
where
a.ts# = b.ts#
group
by b.name
)
Total
WHERE
Free.Tablespace_name = Total.name;
Rem**-----------Tablespace
Fragmentation Status---------------------**
col
TABLESPACE_NAME format a25 heading "Tablespace Name"
select
substr(TABLESPACE_NAME,1,12)
Name,
TOTAL_EXTENTS
"Total Extents",
EXTENTS_COALESCED,
decode(PERCENT_EXTENTS_COALESCED,100,'NO','YES')
"Frag"
from
dba_free_space_coalesced;
Rem**---------------Top
20 Events and System Statistics-------------------------------**
col
event format a40 heading "Event Name"
col
Stat format a40 heading "Stat Name"
select
* from ( select name "Stat",value from v$sysstat order by value desc
) where rownum <= 20 ;
select
* from ( select event,total_waits from v$system_event order by total_waits desc
) where rownum <=20;
Rem**---------------Buffer
Cache statistics-------------------------------**
select
(1-(a.value/(b.value+c.value)))*100 "Buffer Cache Hit ratio"
from
v$sysstat a,v$sysstat b,v$sysstat c
where
a.name= 'physical reads'
and
b.name = 'consistent gets'
and
c.name = 'db block gets';
Rem**---------------File I/O statistics-------------------------------**
col
file# format 99 heading "File No"
select
file#,PHYRDS "Physical Reads",
PHYWRTS
"Physical Writes",
READTIM
"Read Time",
WRITETIM
"Write Time",
AVGIOTIM
"Avg Time" from v$filestat;
select
f.file#,d.file_name,f.PHYRDS "Physical Reads",
f.PHYWRTS
"Physical Writes",
f.READTIM
"Read Time",
f.WRITETIM
"Write Time",
f.AVGIOTIM
"Avg Time"
from
v$filestat f,dba_data_files d
where
d.file_id=f.file#
/
--------------------------------------getting
sql statement -------------------------------------
select sql_text
from
v$sql
where
address in ( select address
from
v$open_cursor
where
sid = (select sid
from
v$mystat
where
rownum=1) )
and
upper(sql_text) like '%
------------------------------------idle
connections-------------------------------------------------
select
s.sid||','||s.serial# session,
s.USERNAME,
s.last_call_et
seconds_since_active,
s.status,
s.sql_address,
s.program
from
v$session s
where
s.sid = nvl(to_number('&sid'),s.sid);
Subscribe to:
Posts (Atom)