ora程序简单测试

联系:手机(+86 13429648788) QQ(107644445)QQ咨询惜分飞

标题:ora程序简单测试

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

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

此条目发表在 Oracle 分类目录。将固定链接加入收藏夹。

ora程序简单测试》有 4 条评论

  1. snail 说:

    请问下你这个工具能发一份给我吗?谢谢了

      [引用]  [回复]

  2. garydba 说:

    请问这个工具哪里可以下载?谢谢!!

      [引用]  [回复]

  3. 不了峰 说:

    请问哪儿下载ora

      [引用]  [回复]

  4. McDull 说:

    Ora这个工具是自己开发的吗,哪里可以下载啊?

      [引用]  [回复]

发表评论

电子邮件地址不会被公开。 必填项已用 * 标注

您可以使用这些 HTML 标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>