oracle的监控很多,朋友推荐的ora工具,测试了下,发现确实很好用也很强大
ora使用帮助
[oracle@xifenfei ~]$ ./ora
Syntax Error
Usage: ora [-u user] [-i instance#] <command> [<arguments>]
General
-u user/pass use USER/PASS to log in
-i instance# append # to ORACLE_SID
-sid <sid> set ORACLE_SID to sid
-top # limit some large queries to on # rows
- repeat <interval> <count|forever> <ora command>
Repeat an coomand <count> time
Sleep <interval> between two calls
Command are:
- execute: cursors currently being executed
- longops: run progression monitor
- sessions: currently open sessions
- stack <os_pid> get process stack using oradebug
- cursors [all] <match_str>: [all] parsed cursors
- sharing <sql_id>: print why cursors are not shared
- events [px]: events that someone is waiting for
- ash <minutes_from_now>
[duration]
[-f <file_name>] active session history for specified period
e.g. 'ash 30' to display from [now - 30min]
to [now]
e.g. 'ash 30 10 -f foo.txt' to display a 10
minutes period from [now - 30min]
and store the result in file foo.txt
- ash_wait_graph <minutes_from_now>
[duration]
[-f <file_name>] PQ event wait graph using ASH data
Arguments are the same as for ash
except that the output must be shown
with the mxgraph tool
- ash_sql <sql_id> Show all ash rows group by sampli_time and
event for the specified sql_id
- [-u <user/passwd>] degree degree of objects for a given user
- [-u <user/passwd>] colstats stats for each table, column
- [-u <user/passwd>] tabstats stats for each table
- params [<pattern>]: view all parameters, even hidden ones
- snap: view all snapshots status
- bc: view contents of buffer cache
- temp: view used space in temp tbs
- asm: Show asm space/free space
- space [<tbs>]: view used/free space in a given tbs
- binds <sql_id> : display bind capture information for
specified cursor
- fulltext <sql_id> : display the entire SQL text of the
specified statement
- last_sql_hash [<sid>]: hash value of the last styatement executed
by the specified sid. If no sid speficied,
return the last hash_value of user sessions
- openv <sql_id>
[<pattern>]: display optimizer env parameters for
specified cursor
- plan <sql_id> [<fmt>]: get explain plan of a particular cursor
- pxplan <sql_id> : get explain plan of a particular cursor and
all connected cursor slave SQL
- wplan <sql_id> [<fmt>]: get explain plan with work area information
- pxwplan <sql_id> : get explain plan with work area information
of a particular cursor and all connected
cursor slave SQL
- eplan <sql_id> [<fmt>]: get explain plan with execution statistics
- pxeplan <sql_id> : get explain plan with execution statistics
of a particular cursor and all connected
cursor slave SQL
- gplan <sql_id> : get graphical explain plan of a particular
cursor using dot specification
- webplan <sql_id> get graphical explain plan of a particular
[/<child_number>] cursor using gdl specification
[<decorate>]: optional: child_number, default is zero.
optional: decorate to print further node
information. default is 0,
1 => print further node information such as
cost, filter_predicates etc.
2 => in addition to the above, print
row vector information
sample usage:
# ora webplan 4019453623
print more information (decorate 1)
# ora webplan 4019453623/1 1
more information, overload! (decorate 2)
# ora webplan 4019453623/1 2
using sql_id along with child number
instead of hash value
# ora webplan aca4xvmz0rzup/3 1
- hash_to_sqlid <sql_id> : get the sql_id of the cursor given its hash
value
- sqlid_to_hash <sql_id>: get the hash value of the cursor given its
(unquoted) sql_id
- exptbs: generate export tablespace script
- imptbs: generate import tablespace script
- smm [limited]: SQL memory manager stats for active
workareas
- onepass: Run an ora wplan on all one-pass cursors
- mpass: Run an ora wplan on all multi-pass cursors
- pga: tell how much pga memory is used
- pga_detail <os_pid>|
-mem <size_mb>: Gives details on how PGA memory is consumed
by a process (given its os PID) or by the
set of precesses consuming more than
<size_mb> MB of PGA memory (-mem option)
- pgasnap [<snaptab>] Snapshot the pga advice stats
- pgaadv [-s [<snaptab>]]
[-o graphfile]
[-m min_size]: generate a graph from v
and display it or store it in a
file if the -o option is used.
-s [<snaptab>] to diff with a
previous snapshot (see pgasnap cmd)
-o [graphfile] to store the result
in a file instead of displaying it
-m [min_size] only consider
workareas with a minimum size
- pgaadvhist [-f <f_min>
[<f_max>]] display the advice history for all
factors or for factor between
f_min and f_max
- sga: tell how much sga memory is used
- sga_stats: tell how sga is dynamically used
- sort_usage: tell how temp tablespace is used in detail
- sgasnap [<snaptab>] Snapshot the sga advice stats
- sgaadv [-s [<snaptab>]]
[-o graphfile] generate a graph from v
and v and store it in a
file if the -o option is used.
-s [<snaptab>] to diff with a
previous snapshot (see sgasnap cmd)
-o [graphfile] to store the result
in a file instead of displaying it
- process [<min_mb>]: display process info with pga memory
- version: display Oracle version number
- cur_mem [ <sql_id> ] display the memory used for a
given or all cursors
- shared_mem [ <sql_id> ] detailed dump of cursor shared mem
allocations
- runtime_mem [ <sql_id> ] detailed dump of cursor runtime
memory allocations
- all_mem [ <sql_id> ] do all of the memory dumps
- pstack <pid>|all
[<directory>] run pstack on specified process
(or all if 'all' specified) and store
files in specified dir ( when
not specified)
- idxdesc [username]
<tabName> list all indexes for a given user or
for a given user and table
- segsize [username]
<objName> list size of all objects(segments) for
given user for a given user and object
- tempu <username> list temporary ts usage of all users or
for a given user
- sqlstats [ <sql_id> ] list sql execution stats (like
buffer_gets, phy. reads etc)
for a given sql_id/hash_value of statement
- optstats [username] list optimizer stats for all tables stored
<tabname> in dictionary for a given user or for a
given user and table
- userVs list all user Views (user_tables,
user_indexes etc)
- fixedVs list all V$ Views
- fixedXs list all X$ Views
- px_processes list all px processes (QC and slaves)
- cursor_summary summarize stats about (un)pinned cursors
- rowcache summarizes row cache statistics
- monitor_list lists all the statements that have been
monitored
- monitor : wraps dbms_sqltune.report_sql_monitor().
Directly passe the arguments to the PL/SQL
procedure. Args are:
sql_id, session_id, session_serial,
sql_exec_start, sql_exec_id, inst_id,
instance_id_filter, parallel_filter,
report_level, type.
Examples:
- monitor xml shows XML report
- monitor show last monitored stmt
- monitor sql_id=>'8vz99cy9bydv8',
session_id=>105 will
show monitor info for sql_id
8vz99cy9bydv8 and session_id 105
Use simply ora monitor 8vz99cy9bydv8
to display monitoring information for
sql_id 8vz99cy9bydv8.
Syntax for parallel filters is:
[qc][servers(<svr_grp>[,] <svr_set>[,]
<srv_num>)]
Use /*+ monitor */ to force monitoring.
- monitor_old [ash_all] [<sqlid>]
[qc|<slave_grp#> [<slave_set#> [<slave#>]]]
Old version of SQL monitoring, use a
SQL query versus the report_sql_monitor()
package. Display monitoring info for the
LAST execution of the specified cursor.
Cursor response time needs to be at
least 5s for monitoring to start (use the
monitor hint to force monitoring). Without
any parameter, will display monitoring info
for the last cursor that was monitored
- ash_all will aggregate ash data over all
executions of the cursor (useful for short
queries that are executed many times).
If parallel:
- qc to see only data for qc
- slave_grp# to see only data for one
parallelizer
- slave_grp# + slave_set# to see only data
for one slave set of one parallelizer,
- slave_grp# + slave_set# + slave# to see
data only for the specified slave
- sql_task [progress | interrupt <task_name> | history <#execs> |
report <task_name> <section> <exec_name> ]
progress: progress monitoring for executing
sql tasks
interrupt: interrupt an executing sql task
history: print a history of last n executions
report: get a sql tune report
- sh Run a shell command. E.g.
ora repeat 5 10 sh 'ps -edf | grep DESC'
Memory: The detailed memory dumps need to have events set to work.
The events bellow can be added to the init.ora file
event="10277 trace name context forever, level 10" # mutable mem
event="10235 trace name context forever, level 4" # shared mem
NOTE
====
- Set environment variable ORA_USE_HASH to 1 to get SQL hash values
instead of SQL ids
- Set environment variable DBUSER to change default connect string which
is "/ as sysdba"
- Set environment variable ORA_TMP to the default temp directory (default
if /tmp when not set)
数据库版本
[oracle@xifenfei ~]$ ./ora version Oracle version: 11.2.0.3.0
正在执行sql
[oracle@xifenfei ~]$ ./ora execute
SQL_ID EXEC SQL_TEXT
------------------ ----- ---------------------------------------------------------------------------
g6gu1n3x0h1h4 1 select streams_pool_size_for_estimate s,
streams_pool_size_factor * 100 f, estd_spill_time +
estd_unspill_time, 0 from v$streams_pool_advice
5yv7yvjgjxugg 1 select TIME_WAITED_MICRO from V$SYSTEM_EVENT where event = 'Shared IO
Pool Memory'
0rc4km05kgzb9 1 select 1 from obj$ where name='DBA_QUEUE_SCHEDULES'
当前的会话
[oracle@xifenfei ~]$ ./ora sessions
SID SERIAL# CL_PID PID USERNAME TYPE SERVER PROGRAM SQL_ID
---------- ---------- ---------- ---------- -------------------- ---------- --------- ------------------------------------------------ --------
ACTION
----------------------------------------------------------------
1 1 706 706 BACKGROUND DEDICATED oracle@xifenfei (PMON)
126 1 710 710 BACKGROUND DEDICATED oracle@xifenfei (PSP0)
2 1 714 714 BACKGROUND DEDICATED oracle@xifenfei (VKTM)
127 1 720 720 BACKGROUND DEDICATED oracle@xifenfei (GEN0)
3 1 724 724 BACKGROUND DEDICATED oracle@xifenfei (DIAG)
128 1 728 728 BACKGROUND DEDICATED oracle@xifenfei (DBRM)
4 1 732 732 BACKGROUND DEDICATED oracle@xifenfei (DIA0)
129 1 736 736 BACKGROUND DEDICATED oracle@xifenfei (MMAN)
5 1 740 740 BACKGROUND DEDICATED oracle@xifenfei (DBW0)
130 1 744 744 BACKGROUND DEDICATED oracle@xifenfei (LGWR)
6 1 748 748 BACKGROUND DEDICATED oracle@xifenfei (CKPT)
131 1 752 752 BACKGROUND DEDICATED oracle@xifenfei (SMON)
7 1 756 756 BACKGROUND DEDICATED oracle@xifenfei (RECO)
132 1 760 760 BACKGROUND DEDICATED oracle@xifenfei (MMON)
8 1 764 764 BACKGROUND DEDICATED oracle@xifenfei (MMNL)
125 87 12732 12732 BACKGROUND DEDICATED oracle@xifenfei (W000)
KTSJ Slave
15 141 13132 13136 SYS USER DEDICATED sqlplus@xifenfei (TNS V1-V3) d5zj45xcq95d3
9 5 819 819 BACKGROUND DEDICATED oracle@xifenfei (ARC0)
135 5 823 823 BACKGROUND DEDICATED oracle@xifenfei (ARC1)
10 1 827 827 BACKGROUND DEDICATED oracle@xifenfei (ARC2)
136 3 831 831 BACKGROUND DEDICATED oracle@xifenfei (ARC3)
12 1 835 835 BACKGROUND DEDICATED oracle@xifenfei (QMNC)
QMON Coordinator
133 11 855 855 BACKGROUND DEDICATED oracle@xifenfei (Q000)
QMON Slave
14 7 896 896 BACKGROUND DEDICATED oracle@xifenfei (SMCO)
KTSJ Coordinator
17 3 859 859 BACKGROUND DEDICATED oracle@xifenfei (Q001)
QMON Slave
sql执行情况
[oracle@xifenfei ~]$ ./ora ash_sql d5zj45xcq95d3 SAMPLE_TIME ACTIVITY P1 P2 NAME NB ---------------------------------------- ------------------------------ ------------------------------ -------------------- ---------- ---- 13-JAN-12 03.07.05.299 AM CPU - - 1
完整sql语句
[oracle@xifenfei ~]$ ./ora fulltext d5zj45xcq95d3
SQL_ID SQL_FULLTEXT
------------------ ---------------------------------------------------------------------------
d5zj45xcq95d3 select s.sid, s.serial#, s.PROCESS cl_pid, p.spid pid, s.username,
s.type, s.server, s.PROGRAM, sql_id sql_id, s.action
from v$session s, v$process p
where s.PADDR = p.addr
sql执行计划
[oracle@xifenfei ~]$ ./ora plan d5zj45xcq95d3
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------
SQL_ID d5zj45xcq95d3, child number 0
-------------------------------------
select s.sid, s.serial#, s.PROCESS cl_pid, p.spid pid, s.username,
s.type, s.server, s.PROGRAM, sql_id sql_id, s.action from
v$session s, v$process p where s.PADDR = p.addr
Plan hash value: 1456042965
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)|
| 1 | NESTED LOOPS | | 1 | 264 | 0 (0)|
| 2 | NESTED LOOPS | | 1 | 251 | 0 (0)|
| 3 | MERGE JOIN CARTESIAN | | 5 | 350 | 0 (0)|
|* 4 | FIXED TABLE FULL | X$KSUPR | 1 | 44 | 0 (0)|
| 5 | BUFFER SORT | | 100 | 2600 | 0 (0)|
| 6 | FIXED TABLE FULL | X$KSLWT | 100 | 2600 | 0 (0)|
|* 7 | FIXED TABLE FIXED INDEX| X$KSUSE (ind:1) | 1 | 181 | 0 (0)|
|* 8 | FIXED TABLE FIXED INDEX | X$KSLED (ind:2) | 1 | 13 | 0 (0)|
----------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$573A9BEE
4 - SEL$573A9BEE / X$KSUPR@SEL$5
6 - SEL$573A9BEE / W@SEL$3
7 - SEL$573A9BEE / S@SEL$3
8 - SEL$573A9BEE / E@SEL$3
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter(("INST_ID"=USERENV('INSTANCE') AND BITAND("KSSPAFLG",1)<>0))
7 - filter(("S"."INST_ID"=USERENV('INSTANCE') AND
BITAND("S"."KSSPAFLG",1)<>0 AND BITAND("S"."KSUSEFLG",1)<>0 AND
"S"."KSUSEPRO"="ADDR" AND "S"."INDX"="W"."KSLWTSID"))
8 - filter("W"."KSLWTEVT"="E"."INDX")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "KSUPRPID"[VARCHAR2,24], "S"."INDX"[NUMBER,22],
"S"."KSSPATYP"[NUMBER,22], "S"."KSUUDLNA"[VARCHAR2,30],
"S"."KSUSESER"[NUMBER,22], "S"."KSUSEFLG"[NUMBER,22],
"S"."KSUSEPID"[VARCHAR2,24], "S"."KSUSEPNM"[VARCHAR2,48],
"S"."KSUSESQI"[VARCHAR2,13], "S"."KSUSEACT"[VARCHAR2,64]
2 - "KSUPRPID"[VARCHAR2,24], "W"."KSLWTEVT"[NUMBER,22],
"S"."INDX"[NUMBER,22], "S"."KSSPATYP"[NUMBER,22],
"S"."KSUUDLNA"[VARCHAR2,30], "S"."KSUSESER"[NUMBER,22],
"S"."KSUSEFLG"[NUMBER,22], "S"."KSUSEPID"[VARCHAR2,24],
"S"."KSUSEPNM"[VARCHAR2,48], "S"."KSUSESQI"[VARCHAR2,13],
"S"."KSUSEACT"[VARCHAR2,64]
3 - "ADDR"[RAW,4], "KSUPRPID"[VARCHAR2,24], "W"."KSLWTSID"[NUMBER,22],
"W"."KSLWTEVT"[NUMBER,22]
4 - "ADDR"[RAW,4], "INST_ID"[NUMBER,22], "KSSPAFLG"[NUMBER,22],
"KSUPRPID"[VARCHAR2,24]
5 - (#keys=0) "W"."KSLWTSID"[NUMBER,22], "W"."KSLWTEVT"[NUMBER,22]
6 - "W"."KSLWTSID"[NUMBER,22], "W"."KSLWTEVT"[NUMBER,22]
7 - "S"."INDX"[NUMBER,22], "S"."INST_ID"[NUMBER,22],
"S"."KSSPAFLG"[NUMBER,22], "S"."KSSPATYP"[NUMBER,22],
"S"."KSUUDLNA"[VARCHAR2,30], "S"."KSUSEPRO"[RAW,4],
"S"."KSUSESER"[NUMBER,22], "S"."KSUSEFLG"[NUMBER,22],
"S"."KSUSEPID"[VARCHAR2,24], "S"."KSUSEPNM"[VARCHAR2,48],
"S"."KSUSESQI"[VARCHAR2,13], "S"."KSUSEACT"[VARCHAR2,64]
8 - "E"."INDX"[NUMBER,22]
sga和pga信息
[oracle@xifenfei ~]$ ./ora pga NAME PID USED(KB) ALLOC(KB) MAX_ALLOC(KB) MAP_SIZE(KB) ------------------------------------------------------------------------------------- Total 102M 119M 26173 0M [oracle@xifenfei ~]$ ./ora sga POOL NAME SIZE_KB ------------ -------------------------- ---------- large pool PX msg pool 480 large pool free memory 3616 java pool free memory 4096 large pool (total) 4096 java pool (total) 4096 shared pool free memory 14149 shared pool (total) 14149 Total 22341 8 rows selected. [oracle@xifenfei ~]$ ./ora sga_stats SGA DYNAMIC COMPNENTS COMPONENT CURRENT_SIZE_MB MIN_SIZE_MB MAX_SIZE_MB LAST_OPER_TYP LAST_OPER -------------------------------------------------- --------------- ----------- ----------- ------------- --------- shared pool 84 84 84 STATIC large pool 4 4 4 STATIC java pool 4 4 4 STATIC streams pool 0 0 0 STATIC DEFAULT buffer cache 72 72 72 INITIALIZING KEEP buffer cache 0 0 0 STATIC RECYCLE buffer cache 0 0 0 STATIC DEFAULT 2K buffer cache 0 0 0 STATIC DEFAULT 4K buffer cache 0 0 0 STATIC DEFAULT 8K buffer cache 0 0 0 STATIC DEFAULT 16K buffer cache 0 0 0 STATIC DEFAULT 32K buffer cache 0 0 0 STATIC Shared IO Pool 0 0 0 STATIC ASM Buffer Cache 0 0 0 STATIC
ora的功能非常强大,其实本质都是通过sql语句查询实现,可以通过数据库做10046来捕获相关sql.学习oracle,工具只是提高大家工作的效率,而不是刻意去追求工具本身
