The “oratop” displays relevant database activity information presented in four sections.
Section 1 - HEADER: Global database information ◦
Section 2 - DATABASS: Database instance Activity ◦
Section 3 - DB EVENTS: AWR like “Top 5 Timed Events“ ◦
Section 4 – PROCESS/SQL: Processes/SQL information Specific detail of the various sections data can be obtained using the interactive help (see Runtime usage Sections Detail below)
[oracle@tnc2 ~]$ tfactl print status
.-------------------------------------------------------------------------------------------.
| Host | Status of TFA | PID | Port | Version | Build ID | Inventory Status |
+------+---------------+------+------+------------+----------------------+------------------+
| tnc2 | RUNNING | 4280 | 5000 | 12.1.2.8.0 | 12128020160623222219 | COMPLETE |
'------+---------------+------+------+------------+----------------------+------------------'
[oracle@tnc2 ~]$ tfactl
tfactl> db12c
Usage : /u01/app/12.1.0.2/grid/bin/tfactl <command> [options]
<command> =
print Print requested details
purge Delete collections from TFA repository
analyze List events summary and search strings in alert logs.
diagcollect Collect logs from across nodes in cluster
collection Manage TFA collections
directory Add or Remove or Modify directory in TFA
toolstatus Prints the status of TFA Support Tools
run <tool> Run the desired support tool
start <tool> Starts the desired support tool
stop <tool> Stops the desired support tool
For help with a command: /u01/app/12.1.0.2/grid/bin/tfactl <command> -help
tfactl> db db12c
Set db to DB12C
DB12C tfactl> oratop -i 10 -bn10
Cycle 1 - oratop: Release 14.1.2 Production on Wed Sep 14 11:00:16 2016
Oracle 12c - db1 11:00:08 up: 0.4h, 2 ins, 0 sn, 0 us, 1.8G mt, 1.9% db
ID %CPU LOAD %DCU AAS ASC ASI ASW AST IOPS %FR PGA UTPS UCPS SSRT %DBT
-------------------------------------------------------------------------------
1 21 1 0 1 0 0 0 0 4 6 368M 0 2 975u 84.3
2 11 1 0 1 0 0 0 0 3 4 323M 0 2 462u 15.7
EVENT (C) TOT WAITS TIME(s) AVG_MS PCT WAIT_CLASS
----------------------------------------------------------------------------------------------
PX Deq: Slave Session Stats 2760 453 179.6 36 Other
PX Deq: reap credit 47402 320 6.7 25 Other
DB CPU 188 15
oracle thread bootstrap 319 183 580.5 15 Other
reliable message 890 118 168.9 9 Other
ID SID SPID USR PROG S PGA SQLID/BLOCKER OPN E/T STA STE EVENT/*LA W/T
-------------------------------------------------------------------------------
From
Section 4 – PROCESS/SQL: Processes/SQL information Specific detail of the various sections data
we will get SQL_ID involved in waits
check execution plan of SQL statement using dbms_xplan.display_cursor & SQL_TEXT as follows
sql> select * from table(dbms_xplan.display_cursor(‘&sql_id’,’&child_no’,’typical’));
SQL> col SQL_TEXT format a10000
SQL> select SQL_TEXT from v$sqltext where SQL_ID='worldoforacle';
SQL> select SQL_TEXT from v$sqltext where SQL_ID='worldoforacle';
--Nikhil Tatineni--
--RAC--
