oracle ktsj,ora程序简单测试 – 提供7*24专业数据库(Oracle,SQL Server,MySQL等)恢复和Oracle技术服务@Tel:+86 13429648788 - 惜分飞...

oracle的监控很多,朋友推荐的ora工具,测试了下,发现确实很好用也很强大

ora使用帮助

[oracle@xifenfei ~]$ ./ora

Syntax Error

Usage: ora [-u user] [-i instance#] []

General

-u user/pass use USER/PASS to log in

-i instance# append # to ORACLE_SID

-sid set ORACLE_SID to sid

-top # limit some large queries to on # rows

- repeat

Repeat an coomand time

Sleep between two calls

Command are:

- execute: cursors currently being executed

- longops: run progression monitor

- sessions: currently open sessions

- stack get process stack using oradebug

- cursors [all] : [all] parsed cursors

- sharing : print why cursors are not shared

- events [px]: events that someone is waiting for

- ash

[duration]

[-f ] 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

[duration]

[-f ] 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 Show all ash rows group by sampli_time and

event for the specified sql_id

- [-u ] degree degree of objects for a given user

- [-u ] colstats stats for each table, column

- [-u ] tabstats stats for each table

- params []: 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 []: view used/free space in a given tbs

- binds : display bind capture information for

specified cursor

- fulltext : display the entire SQL text of the

specified statement

- last_sql_hash []: hash value of the last styatement executed

by the specified sid. If no sid speficied,

return the last hash_value of user sessions

- openv

[]: display optimizer env parameters for

specified cursor

- plan []: get explain plan of a particular cursor

- pxplan : get explain plan of a particular cursor and

all connected cursor slave SQL

- wplan []: get explain plan with work area information

- pxwplan : get explain plan with work area information

of a particular cursor and all connected

cursor slave SQL

- eplan []: get explain plan with execution statistics

- pxeplan : get explain plan with execution statistics

of a particular cursor and all connected

cursor slave SQL

- gplan : get graphical explain plan of a particular

cursor using dot specification

- webplan get graphical explain plan of a particular

[/] cursor using gdl specification

[]: 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 : get the sql_id of the cursor given its hash

value

- sqlid_to_hash : 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 |

-mem : Gives details on how PGA memory is consumed

by a process (given its os PID) or by the

set of precesses consuming more than

MB of PGA memory (-mem option)

- pgasnap [] Snapshot the pga advice stats

- pgaadv [-s []]

[-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 [] 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

[]] 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 [] Snapshot the sga advice stats

- sgaadv [-s []]

[-o graphfile] generate a graph from v

and v and store it in a

file if the -o option is used.

-s [] to diff with a

previous snapshot (see sgasnap cmd)

-o [graphfile] to store the result

in a file instead of displaying it

- process []: display process info with pga memory

- version: display Oracle version number

- cur_mem [ ] display the memory used for a

given or all cursors

- shared_mem [ ] detailed dump of cursor shared mem

allocations

- runtime_mem [ ] detailed dump of cursor runtime

memory allocations

- all_mem [ ] do all of the memory dumps

- pstack |all

[] run pstack on specified process

(or all if 'all' specified) and store

files in specified dir ( when

not specified)

- idxdesc [username]

list all indexes for a given user or

for a given user and table

- segsize [username]

list size of all objects(segments) for

given user for a given user and object

- tempu list temporary ts usage of all users or

for a given user

- sqlstats [ ] 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

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 1: 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([,] [,]

)]

Use /*+ monitor */ to force monitoring.

- monitor_old [ash_all] []

[qc| [ []]]

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 | history |

report ]

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,工具只是提高大家工作的效率,而不是刻意去追求工具本身