接上一篇常用的数据库管理SQL语句（一）

    九.统计联机前10名用户所使用CPU时间

    SQL> SELECT * FROM

    2  (SELECT \'P\', S.SID,S.STATUS,T.VALUE / 100 / 60 "CPU TIME",

    3  FLOOR (LAST_CALL_ET / 60 ) "LAST CALL ET",TO_CHAR(S.LOGON_TIME,\'MM/DD HH24:MI\') "LOGON TIME",

    4 S.USERNAME,S.PROCESS,P.SPID,S.MODULE,S.MACHINE,S.SQL_HASH_VALUE

    5  FROM V$SESSTAT T,V$SESSION S,V$PROCESS P

    6  WHERE T.STATISTIC# = 12 AND S.SID = T.SID AND S.PADDR = P.ADDR AND S.TYPE = \'USER\'

    7  AND S.SQL_HASH_VALUE != 1425819161 UNION SELECT \'N\',

    8  S.SID,S.STATUS,T.VALUE * -1 / 100 / 60 "CPU TIME",

    9  FLOOR (LAST_CALL_ET / 60 ) "LAST CALL ET",TO_CHAR(S.LOGON_TIME,\'MM/DD HH24:MI\') "LOGON TIME",

    10  S.USERNAME,S.PROCESS,P.SPID,S.MODULE,S.MACHINE,S.SQL_HASH_VALUE

    11  FROM V$SESSTAT T,V$SESSION S,V$PROCESS P

    12  WHERE T.STATISTIC# = 12 AND S.SID = T.SID AND S.PADDR = P.ADDR AND S.TYPE = \'USER\' 

    13  AND S.SQL_HASH_VALUE != 1425819161

    14  AND T.VALUE < 0

    15  ORDER BY 4 DESC)

    16  WHERE ROWNUM < 11;

    \'P\'        SID STATUS     CPU TIME LAST CALL ET LOGON TIME  USERNAME                       PROCESS      SPID         MODULE                                           MACHINE                                                          SQL_HASH_VALUE

    --- ---------- -------- ---------- ------------ ----------- ------------------------------ ------------ ------------ ------------------------------------------------ ---------------------------------------------------------------- --------------

    P         1533 INACTIVE 25.3363333            0 08/17 15:28 SYSMAN                         1234         1822         OEM.SystemPool                                   db1                                                                           0

    P         1498 INACTIVE 20.5493333           13 10/26 16:23 SOLEILFUZHOU                   8968         11046        java@WEBSERVER2 (TNS V1-V3)                      WEBSERVER2                                                           4283697136

    P         1493 ACTIVE   18.8611666            0 08/17 15:29 DBSNMP                         3688         3866         emagent@db1 (TNS V1-V3)                          db1                                                                           0

    P         1465 INACTIVE 12.3526666          121 10/26 20:43 SOLEILFUZHOU                   32512        32638        java@WEBSERVER3 (TNS V1-V3)                      WEBSERVER3                                                                    0

    P         1500 INACTIVE    12.3035            0 10/26 16:23 SOLEILFUZHOU                   8968         11048        java@WEBSERVER2 (TNS V1-V3)                      WEBSERVER2                                                           2314313480

    P         1538 INACTIVE 11.9511666          227 10/26 20:43 SOLEILFUZHOU                   32512        32640        java@WEBSERVER3 (TNS V1-V3)                      WEBSERVER3                                                           1912375278

    P         1545 INACTIVE 5.44716666          119 10/26 16:23 SOLEILFUZHOU                   8968         11044        java@WEBSERVER2 (TNS V1-V3)                      WEBSERVER2                                                           1912375278

    P         1511 INACTIVE      4.597        89558 08/20 14:56 SOLEILFUZHOU                   23615        22500        java@WEBSERVER2 (TNS V1-V3)                      WEBSERVER2                                                                    0

    P         1540 INACTIVE 2.36633333         1810 08/15 14:16 SOLEILFUZHOU                   23615        30341        java@WEBSERVER2 (TNS V1-V3)                      WEBSERVER2                                                           2715598771

    P         1630 ACTIVE   1.55233333            0 08/15 12:21 SYS                            9190         10029        racgimon@db1 (TNS V1-V3)                         db1                                                                  2346103937

    10 rows selected

    十：统计前10名排序量最大的使用者

    SQL> SELECT * FROM (SELECT S.SID,S.STATUS,B.SPID,S.SQL_HASH_VALUE SESSHASH,

    2  U.SQLHASH SORTHASH,S.USERNAME,U.TABLESPACE,SUM(U.BLOCKS * P.VALUE / 1024 / 1024)

    3  MBUSED,SUM(U.EXTENTS) NOEXTS,U.SEGTYPE,S.MODULE || \'-\' || S.PROGRAM PROGINFO

    4  FROM V$SORT_USAGE U,V$SESSION S,V$PARAMETER P,V$PROCESS B

    5  WHERE U.SESSION_ADDR = S.SADDR AND P.NAME = \'DB_BLOCK_SIZE\'

    6  AND B.ADDR = S.PADDR GROUP BY S.SID,S.STATUS,B.SPID,S.SQL_HASH_VALUE,

    7  U.SQLHASH,S.USERNAME,U.TABLESPACE,U.SEGTYPE,S.MODULE || \'-\' || S.PROGRAM

    8  ORDER BY 8 DESC,4)

    9  WHERE ROWNUM < 11;

     SID STATUS   SPID           SESSHASH   SORTHASH USERNAME                       TABLESPACE                          MBUSED     NOEXTS SEGTYPE   PROGINFO

    ---------- -------- ------------ ---------- ---------- ------------------------------ ------------------------------- ---------- ---------- --------- --------------------------------------------------------------------------------

    十一：统计前10名数据等待事件

    SQL> SELECT * FROM (SELECT EVENT,TOTAL_WAITS,TIME_WAITED,AVERAGE_WAIT

     2  FROM V$SYSTEM_EVENT WHERE EVENT NOT LIKE \'%IPC%\'

     3  AND EVENT NOT LIKE \'SQL*NET%\'

     4  AND EVENT NOT LIKE \'PIPE%\'

     5  AND EVENT NOT LIKE \'NULL%\'

     6  AND EVENT NOT LIKE \'WAKEUP%\'

     7  AND EVENT NOT LIKE \'PX%\'

     8  AND EVENT NOT LIKE \'%TIMER%\'

     9  ORDER BY 3 DESC)

    10  WHERE ROWNUM < 101;

    EVENT                                                            TOTAL_WAITS TIME_WAITED AVERAGE_WAIT

    ---------------------------------------------------------------- ----------- ----------- ------------

    rdbms ipc message                                                  119829424  9484828486        79.15

    jobq slave wait                                                     22321120  5517001961       247.17

    SQL*Net message from client                                        355319260  5165327887        14.54

    gcs remote message                                                 485381465  1368716151         2.82

    Streams AQ: waiting for messages in the queue                        2159832  1350292859       625.18

    class slave wait                                                      799866   824348163      1030.61

    Streams AQ: qmn slave idle wait                                       276580   721687184      2609.33

    DIAG idle wait                                                      34794348   684920856        19.68

    ges remote message                                                 115824798   684714731         5.91

    pmon timer                                                           2419561   684648008       282.96

    ASM background timer                                                 2897175   684597236        236.3

    smon timer                                                           1460456   684164267       468.46

    virtual circuit status                                                233798   682660364      2919.87

    dispatcher timer                                                      116900   680302241      5819.52

    Streams AQ: qmn coordinator idle wait                                 505458   679457024      1344.24

    Streams AQ: waiting for time management or cleanup tasks               41394   662178271     15996.96

    log file sequential read                                             2433424    31321584        12.87

    direct path read temp                                              136113170    24235677         0.18

    db file parallel write                                               7259212     8125206         1.12

    SQL*Net message from dblink                                        203115263     7331295         0.04

    十二：统计前10名使用CURSOR最多

    SQL> SELECT * FROM ( SELECT S.SID,V0.VALUE CUM_OPEN_CURSORS,

     2  V1.VALUE CUR_OPEN_CURSORS,

     3  V2.VALUE CACHE_HITS,

     4  V3.VALUE CACHE_COUNT,

     5  V4.VALUE HARD_PARSE,

     6  V5.VALUE - V4.VALUE SOFT_PARSE,

     7  S.OSUSER,S.MACHINE,

     8  NVL(S.MODULE,S.PROGRAM) PROGINFO FROM V$SESSTAT V0,V$SESSTAT V1,V$SESSTAT V2,V$SESSTAT V3,

     9  V$SESSTAT V4,V$SESSTAT V5,V$SESSION S WHERE V0.STATISTIC# = 2

    10  AND V0.SID = S.SID AND V1.STATISTIC# = 3 AND V1.SID = S.SID

    11  AND V2.STATISTIC# = 191 AND V2.SID = S.SID

    12  AND V3.STATISTIC# = 192 AND V3.SID = S.SID

    13  AND V4.STATISTIC# = 180 AND V4.SID = S.SID

    14  AND V5.STATISTIC# = 179 AND V5.SID = S.SID

    15  ORDER BY 3 DESC, 7 DESC)

    16  WHERE ROWNUM < 11;

     SID CUM_OPEN_CURSORS CUR_OPEN_CURSORS CACHE_HITS CACHE_COUNT HARD_PARSE SOFT_PARSE OSUSER                         MACHINE                                                          PROGINFO

    ---------- ---------------- ---------------- ---------- ----------- ---------- ---------- ------------------------------ ---------------------------------------------------------------- ------------------------------------------------

     1533            20448               47          1      114062      55172     -55172 oracle                         db1                                                              OEM.SystemPool

     1641           628681               28        192      174896     607373    -582053 oracle                         db1                                                              oracle@db1 (MMON)

     1493           118371               22      11914       51293  512581656 -512568710 oracle                         db1                                                              emagent@db1 (TNS V1-V3)

     1606             4824                9        122        4787       8294      -8262 Administrator                  WORKGROUP\\NUAGE-TEST1                                            PL/SQL Developer

     1488            53050                8          3       22829     394397    -394397 oracle                         db1                                                              OEM.Loader

     1531              365                6          0           4        809       -809 oracle                         db1                                                              emagent@db1 (TNS V1-V3)

     1490             1403                6          0           4   11628610  -11628610 Administrator                  WORKGROUP\\NUAGE-TEST1                                            PL/SQL Developer

     1630             4429                5          0           0       7629      -7629 oracle                         db1                                                              racgimon@db1 (TNS V1-V3)

     1576               34                4          0           1         77        -77 oracle                         db1                                                              racgimon@db1 (TNS V1-V3)

     1468               31                4          0           0        676       -676 root                           rdb1                                                             oracle@rdb1 (TNS V1-V3)

    10 rows selected

    十三：查询最常用的表

    SQL> SELECT OWNER ,SUBSTR(SEGMENT_NAME,1,30) SEGMENT_NAME,OBJECT_TYPE,TOTAL_BUFF_BUSY_WAITS

     2  FROM ( SELECT OWNER,OBJECT_NAME AS SEGMENT_NAME,OBJECT_TYPE,VALUE AS TOTAL_BUFF_BUSY_WAITS

     3  FROM V$SEGMENT_STATISTICS WHERE STATISTIC_NAME IN (\'BUFFER BUSY WATIS\')

     4  ORDER BY TOTAL_BUFF_BUSY_WAITS DESC)

     5  WHERE ROWNUM <=30;

    OWNER                          SEGMENT_NAME                                                 OBJECT_TYPE        TOTAL_BUFF_BUSY_WAITS

    ------------------------------ ------------------------------------------------------------ ------------------ ---------------------

    十四：查询SID对应到操作系统的PID

    SQL> SELECT SS.SID,P.SPID FROM V$SESSION SS,V$PROCESS P,V$SESSTAT ST,V$STATNAME SN

     2  WHERE ST.STATISTIC# = SN.STATISTIC#

     3  AND SS.SID=\'111\' AND SS.PADDR=P.ADDR

     4  AND SS.SID=ST.SID;

     SID SPID

    ---------- ------------

    十五：查询数据库运行的天数

    SQL> SELECT SYSDATE - LOGON_TIME "DAYS",(SYSDATE - LOGON_TIME) * 24

     2  "HOURS" FROM SYS.V_$SESSION WHERE SID = 1;

     DAYS      HOURS

    ---------- ----------

    十六：监控目前正在使用的SQL

    SQL> SELECT C.SPID B1,B.OSUSER C1,B.USERNAME C2,

     2  B.SID B2,B.SERIAL# B3,A.SQL_TEXT FROM

     3  V$SQLTEXT A,V$SESSION B,V$PROCESS C

     4  WHERE A.ADDRESS = B.SQL_ADDRESS AND B.STATUS = \'ACTIVE\'

     5  AND B.PADDR = C.ADDR AND A.HASH_VALUE = B.SQL_HASH_VALUE

     6  ORDER BY C.SPID,A.HASH_VALUE,A.PIECE;

    B1           C1                             C2                                     B2         B3 SQL_TEXT

    ------------ ------------------------------ ------------------------------ ---------- ---------- ----------------------------------------------------------------

    31329        Administrator                  SOLEILFUZHOU                         1487      17233  SELECT C.SPID B1,B.OSUSER C1,B.USERNAME C2, B.SID B2,B.SERIAL#

    31329        Administrator                  SOLEILFUZHOU                         1487      17233 B3,A.SQL_TEXT FROM V$SQLTEXT A,V$SESSION B,V$PROCESS C WHERE A.A

    31329        Administrator                  SOLEILFUZHOU                         1487      17233 DDRESS = B.SQL_ADDRESS AND B.STATUS = \'ACTIVE\' AND B.PADDR = C.A

    31329        Administrator                  SOLEILFUZHOU                         1487      17233 DDR AND A.HASH_VALUE = B.SQL_HASH_VALUE ORDER BY C.SPID,A.HASH_V

    31329        Administrator                  SOLEILFUZHOU                         1487      17233 ALUE,A.PIECE

    --END--

建议继续学习：

1. 常用的数据库管理SQL语句（一）    (阅读：3140)