发布新日志

  • Oracle诊断--Spotlight On Oracle

    2016-04-16 23:31:18

    http://blog.csdn.net/jim110/article/details/6947989

    Spotlight on Oracle 能让你迅速发现任何性能瓶颈，无论是实时还是历史查询。Spotlight 能鉴别和诊断几千种性能问题，无论是特定用户问题、集中资源SQL事务、 I/O瓶颈、锁定等待或者其它源码问题。Spotlight for Oracle 能自动为每个实例建立正常活动的底线，当检测到性能瓶颈时自动发出警告.

    一、首先建立Oracle的连接

    第一步要新建connection,这样能够使用spotlight连接到要监测的数据库。

    一、系统主界面

    系统主界面反映了系统的整体运行情况，如果系统哪方面出现问题，会报相应的警告，不同级别显示不同的颜色，最严重为红色警告。然后据此警告可下钻到相应的子窗口，查看相应情况。下面介绍各子窗口。

    1.Session面板:

    (1)Response<10,系统的响应时间

    (2)Total Users:总用户SESSION数

    (3)Active Users:当前正在执行的用户SESSION数

    2.Host面板:

    (1)CPU利用率

    (2)内存的使用情况

    3. Server Processes面板：显示服务器进程的信息

    (1)PGA Target/Used显示PGA目标总数及当前使用数

    (2)dedicated显示专用服务器进程的个数

    (3)Shared显示共享服务器进程的个数。

    (4)Dispatchers显示dispathers的个数.

    (5)JobQueue显示作业进程的个数

    4. SGA面板：显示SGA中各组件的内存使用情况

    (1)CurrentSize显示当前sga使用M数

    (2)BufferCache,KeepPool,RecyclePool显示数据缓冲区的内存情况

    (3)SharedPool:共享池的使用情况

    (4)RedoLog：重作日志的使用情况

    (5)LargePool：大池的使用情况

    (6)JavaPool：java池的使用情况

    5.Background process面板-后台进程面板：显示与磁盘I/O相关的后台进程。

    (1)DBWR 数据写入进程

    (2)LGWR 日志进程

    (3)ARCH  规档进程式

    6. 磁盘存储面板：显示主要数据库文件的情况（控制文件除外）

    (1）DatabaseFiles:显示数据文件使用情况。

    (2）联机日志文件情况。包括组数及大小。

    (3）归档日志情况。

    三、TopSessions

        通过topSession面板可以查看当前哪个session当前占用了大量的资源，以此定位数据库问题。单击上部列表，会在session Information中显示该会话的所有详细信息。

    下面是几个应注意的地方：

    most recent sql:可以用来确定当前占用资源最大的sql语句

    Session waits:可以找出与该session相关的等待事件。

    Session locks:显示相关的锁信息。

    四、top sql:根据条件来查看libraryCache中相应的sql.

    主要的选项为sorting

    可以据此来找出影响大的sql

    点击Fetch SQL按钮，可以查看到相应的SQL语句

    五、Activity:这个窗口，主要提供了等待事件，锁等待，闫锁等待，当前事务等。

    六、配置和内存：主要显示SGA的使用情况及参数配置情况

    七、磁盘存储情况:显示表空间利用率和在线日志的使用情况。

    查看(701) 评论(0) 收藏 分享 管理

  • AWR报告详解（二）

    2016-04-16 23:05:52

    SQL Statistics v$sqlarea

    本节按各种资源分别列出对资源消耗最严重的SQL语句，并显示它们所占统计期内全部资源的比例，这给出我们调优指南。例如在一个系统中，CPU资源是系统性能瓶颈所在，那么优化buffer gets最多的SQL语句将获得最大效果。在一个I/O等待是最严重事件的系统中，调优的目标应该是physical IOs最多的SQL语句。

    在STATSPACK报告中，没有完整的SQL语句，可使用报告中的Hash Value通过下面语句从数据库中查到：

     

    SELECT sql_text

        FROM stats$sqltext

       WHERE hash_value = &hash_value

    ORDER BY piece;

     

    Back to Top

    SQL ordered by Elapsed Time

    • Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
    • % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100

    SQL ordered by CPU Time

    • Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
    • % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100

    SQL ordered by Gets

    • Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
    • Total Buffer Gets: 16,648,792
    • Captured SQL account for 97.9% of Total

    这一部分，通过Buffer Gets对SQL语句进行排序，即通过它执行了多少个逻辑I/O来排序。顶端的注释表明一个PL/SQL单元的缓存获得(Buffer Gets)包括被这个代码块执行的所有SQL语句的Buffer Gets。因此将经常在这个列表的顶端看到PL/SQL过程，因为存储过程执行的单独的语句的数目被总计出来。在这里的Buffer Gets是一个累积值，所以这个值大并不一定意味着这条语句的性能存在问题。通常我们可以通过对比该条语句的Buffer Gets和physical reads值，如果这两个比较接近，肯定这条语句是存在问题的，我们可以通过执行计划来分析，为什么physical reads的值如此之高。另外，我们在这里也可以关注gets per exec的值，这个值如果太大，表明这条语句可能使用了一个比较差的索引或者使用了不当的表连接。

    另外说明一点：大量的逻辑读往往伴随着较高的CPU消耗。所以很多时候我们看到的系统CPU将近100％的时候，很多时候就是SQL语句造成的，这时候我们可以分析一下这里逻辑读大的SQL。

    SELECT *

      FROM (  SELECT SUBSTR (sql_text, 1, 40) sql,

                     buffer_gets,

                     executions,

                     buffer_gets / executions "Gets/Exec",

                     hash_value,

                     address

                FROM v$sqlarea

               WHERE buffer_gets > 0 AND executions > 0

            ORDER BY buffer_gets DESC)

     WHERE ROWNUM <= 10;

     

    AWR 是 Oracle  10g 版本 推出的新特性， 全称叫Automatic Workload Repository-自动负载信息库, AWR 是通过对比两次快照(snapshot)收集到的统计信息，来生成报表数据，生成的报表包括多个部分

     

    WORKLOAD REPOSITORY report for

    DB Name

    DB Id

    Instance

    Inst num

    Release

    RAC

    Host

    ICCI

    1314098396

    ICCI1

    1

    10.2.0.3.0

    YES

    HPGICCI1

     

     

     

    Snap Id

    Snap Time

    Sessions

    Cursors/Session

    Begin Snap:

    2678

    25-Dec-08 14:04:50

    24

    1.5

    End Snap:

    2680

    25-Dec-08 15:23:37

    26

    1.5

    Elapsed:

     

    78.79 (mins)

     

     

    DB Time:

     

    11.05 (mins)

     

     

    DB Time不包括Oracle后台进程消耗的时间。如果DB Time远远小于Elapsed时间，说明数据库比较空闲。

    db time= cpu time + wait time（不包含空闲等待） （非后台进程）说白了就是db time就是记录的服务器花在数据库运算(非后台进程)和等待(非空闲等待)上的时间DB time = cpu time + all of nonidle wait event time

    在79分钟里（其间收集了3次快照数据），数据库耗时11分钟，RDA数据中显示系统有8个逻辑CPU（4个物理CPU），平均每个CPU耗时1.4分钟，CPU利用率只有大约2%（1.4/79）。说明系统压力非常小。

     

    列出下面这两个来做解释：
    Report A:
    Snap Id Snap Time Sessions Curs/Sess
    --------- ------------------- -------- ---------
    Begin Snap: 4610 24-Jul-08 22:00:54 68 19.1
    End Snap: 4612 24-Jul-08 23:00:25 17 1.7
    Elapsed: 59.51 (mins)
    DB Time: 466.37 (mins)

    Report B:
    Snap Id Snap Time Sessions Curs/Sess
    --------- ------------------- -------- ---------
    Begin Snap: 3098 13-Nov-07 21:00:37 39 13.6
    End Snap: 3102 13-Nov-07 22:00:15 40 16.4
    Elapsed: 59.63 (mins)
    DB Time: 19.49 (mins)
    服务器是AIX的系统，4个双核cpu,共8个核:

    /sbin> bindprocessor -q
    The available processors are: 0 1 2 3 4 5 6 7

    先说Report A,在snapshot间隔中，总共约60分钟，cpu就共有60*8=480分钟，DB time为466.37分钟，则：
    cpu    花费了466.37分钟在处理Oralce非空闲等待和运算上(比方逻辑读)
    也就是说cpu有 466.37/480*100% 花费在处理Oracle的操作上，这还不包括后台进程
    看Report B，总共约60分钟，cpu有 19.49/480*100% 花费在处理Oracle的操作上
    很显然，2中服务器的平均负载很低。
    从awr report的Elapsed time和DB Time就能大概了解db的负载。

    可是对于批量系统，数据库的工作负载总是集中在一段时间内。如果快照周期不在这一段时间内，或者快照周期跨度太长而包含了大量的数据库空闲时间，所得出的分析结果是没有意义的。这也说明选择分析时间段很关键，要选择能够代表性能问题的时间段。

     

    Report Summary

    Cache Sizes

     

    Begin

    End

     

     

    Buffer Cache:

    3,344M

    3,344M

    Std Block Size:

    8K

    Shared Pool Size:

    704M

    704M

    Log Buffer:

    14,352K

    显示SGA中每个区域的大小（在AMM改变它们之后），可用来与初始参数值比较。

    shared pool主要包括library cache和dictionary cache。library cache用来存储最近解析（或编译）后SQL、PL/SQL和Java classes等。library cache用来存储最近引用的数据字典。发生在library cache或dictionary cache的cache miss代价要比发生在buffer cache的代价高得多。因此shared pool的设置要确保最近使用的数据都能被cache。

     

    Load Profile

    查看(2223)评论(0)收藏分享管理

  • Oracle日常性能查看

    2016-04-16 22:41:08

    1、查看锁（lock）情况
    SELECT /* RULE */ Ls.Osuser Os_User_Name, Ls.Username User_Name,Decode(Ls.TYPE,
    'RW', 'Row wait enqueue lock', 'TM', 'DML enqueue lock','TX', 'Transaction enqueue lock', 'UL', 'User supplied lock') Lock_Type,o.Object_Name OBJECT,Decode(Ls.Lmode,1, NULL, 2, 'Row Share', 3, 'Row Exclusive',
    2、查询耗资源的进程（top session）
    SELECT s.Schemaname Schema_Name,Decode(Sign(48 - Command),
    1, To_Char(Command), 'Action Code #' || To_Char(Command)) Action,Status Session_Status, s.Osuser Os_User_Name, s.Sid, p.Spid,s.Serial# Serial_Num, Nvl(s.Username, '[Oracle process]') User_Name,
    s.Terminal Terminal, s.Program Program, St.VALUE Criteria_Value
    FROM V$sesstat St, V$session s, V$process p
    WHERE St.Sid = s.Sid
    AND St.Statistic# = To_Number('38')
    AND ('ALL' = 'ALL' OR s.Status = 'ALL')
    AND p.Addr = s.Paddr
    ORDER BY St.VALUE DESC, p.Spid ASC, s.Username ASC, s.Osuser ASC
    3、捕捉运行很久的SQL
    column username format a12
    column opname format a16
    column progress format a8 select username,sid,opname,
    round(sofar*100 / totalwork,0) || '%' as progress,
    time_remaining,sql_text
    from v$session_longops , v$sql
    where time_remaining <> 0
    and sql_address = address
    and sql_hash_value = hash_value
    /
    4、求当前会话的SID，SERIAL#
    SELECT Sid, Serial# FROM V$session
    WHERE Audsid = Sys_Context('USERENV', 'SESSIONID');
    5、查询session的OS进程ID
    SELECT p.Spid "OS Thread", b.NAME "Name-User", s.Program, s.Sid, s.Serial#,s.Osuser, s.Machine
    FROM V$process p, V$session s, V$bgprocess b
    WHERE p.Addr = s.Paddr
    AND p.Addr = b.Paddr And (s.sid=&1 or p.spid=&1)
    UNION ALL
    SELECT p.Spid "OS Thread", s.Username "Name-User", s.Program, s.Sid,s.Serial#, s.Osuser, s.Machine
    FROM V$process p, V$session s
    WHERE p.Addr = s.Paddr
    And (s.sid=&1 or p.spid=&1)
    AND s.Username IS NOT NULL;
    6、根据sid查看对应连接正在运行的sql
    SELECT /* PUSH_SUBQ */ Command_Type, Sql_Text, Sharable_Mem, Persistent_Mem, Runtime_Mem, Sorts,
    Version_Count, Loaded_Versions, Open_Versions, Users_Opening, Executions,
    Users_Executing, Loads, First_Load_Time, Invalidations, Parse_Calls,
    Disk_Reads, Buffer_Gets, Rows_Processed, SYSDATE Start_Time,
    SYSDATE Finish_Time, '>' || Address Sql_Address, 'N' Status
    FROM V$sqlarea WHERE Address = (SELECT Sql_Address
    FROM V$session WHERE Sid = &sid );
    7、查看有哪些用户连接
    SELECT s.Osuser Os_User_Name,Decode(Sign(48 - Command),1,To_Char(Command),
    'Action Code #' || To_Char(Command)) Action,
    p.Program Oracle_Process, Status Session_Status, s.Terminal Terminal,
    s.Program Program, s.Username User_Name,
    s.Fixed_Table_Sequence Activity_Meter, '' Query, 0 Memory,
    0 Max_Memory, 0 Cpu_Usage, s.Sid, s.Serial# Serial_Num
    FROM V$session s, V$process p
    WHERE s.Paddr = p.Addr
    AND s.TYPE = 'USER'
    ORDER BY s.Username, s.Osuser

    查看(1121) 评论(0) 收藏 分享 管理

  • AWR的生成

    2016-04-16 22:28:35

    AWR（Automatic Workload Repository）报告是我们进行日常数据库性能评定、问题SQL发现的重要手段。熟练掌握AWR报告，是做好开发、运维DBA工作的重要基本功。

     

     

    AWR报告的原理是基于Oracle数据库的定时镜像功能。默认情况下，Oracle数据库后台进程会以一定间隔（一小时）收集系统当前状态镜像，并且保存在数据库中。生成AWR报告时，只需要指定进行分析的时间段（开始镜像编号和结束镜像编号），就可以生成该时间段的性能分析情况。AWR镜像保存在数据库中的时间为一个月左右。

     

     

    目前Oracle10g之后，AWR报告取代了原先的Statspack报告成为一个主流性能分析报告。通常可以从OEM（Oracle Enterprise Manager Console）平台上生成查看AWR报告。在OEM中，使用图形化方法更加容易。本篇中介绍使用手工脚本方式生成AWR的方法，脱离OEM的限制。

     

     

    1、  运行脚本

     

    首先，准备一个目录作为AWR生成报告的路径。

     

     

    [oracle@bspdev /]$ ls -l | grep test

    drwxr-xr-x.   2 oracle oinstall  4096 Jun 21 13:01 test

     

    [oracle@bspdev /]$ cd test

     

     

    启动sqlplus等开发工具，调用生成脚本。程序脚本一般保存在$ORACLE_HOME下的rdbms/admin中，名称为awrrpt.sql。

     

     

    [oracle@bspdev test]$ sqlplus /nolog

     

    SQL*Plus: Release 11.2.0.1.0 Production on Tue Jun 21 13:04:44 2011

     

    Copyright (c) 1982, 2009, Oracle.  All rights reserved.

     

    SQL> conn / as sysdba

    Connected.

     

    --调用脚本，生成文件

    SQL> @?/rdbms/admin/awrrpt.sql

     

     

    之后进入报告参数输入模块。

     

    2、输入报告参数

     

    之后，要持续输入一系列的报告参数。

     

    ü        输入生成报告类型，目前AWR提供txt和html两种格式。需要确认生成格式，默认是html格式。

     

     

    Current Instance

    ~~~~~~~~~~~~~~~~

     

       DB Id    DB Name      Inst Num Instance

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

     4143510747 ORA11G              1 ora11g

     

     

    Specify the Report Type

    ~~~~~~~~~~~~~~~~~~~~~~~

    Would you like an HTML report, or a plain text report?

    Enter 'html' for an HTML report, or 'text' for plain text

    Defaults to 'html'

     

     

    ü        报告涉及天数范围

     

    启动报告后，会显示生成实例的名称等基本信息。

     

    默认情况下，AWR会将镜像信息保留一个月。手工生成的时候，需要确认生成AWR报告的时间范围。一般情况下，特别是生产环境下，我们通常设置1-7天也就够用了。

     

     

    Instances in this Workload Repository schema

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

     

       DB Id     Inst Num DB Name      Instance     Host

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

    * 4143510747        1 ORA11G       ora11g       bspdev.local

                                                    domain

     

    Using 4143510747 for database Id

    Using          1 for instance number

     

     

    Specify the number of days of snapshots to choose from

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    Entering the number of days (n) will result in the most recent

    (n) days of snapshots being listed.  Pressing without

    specifying a number lists all completed snapshots.

     

    Enter value for num_days: 3 

     

     

    ü        输入开始和结束的snapshot编号

     

    输入天数信息后，AWR生成代码会将天数范围内的snapshot镜像点列出，供输入选择。

     

     

    Listing the last 3 days of Completed Snapshots

     

                                                            Snap

    Instance     DB Name        Snap Id    Snap Started    Level

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

    ora11g       ORA11G            1789 20 Jun 2011 13:01      1

                                   1790 20 Jun 2011 14:00      1

                                   1791 20 Jun 2011 15:00      1

                                   1792 20 Jun 2011 16:00      1

                                   （篇幅原因，有省略……）

                                   1811 21 Jun 2011 11:00      1

                                   1812 21 Jun 2011 12:00      1

                                   1813 21 Jun 2011 13:00      1

     

     

     

    Specify the Begin and End Snapshot Ids

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

     

     

    之后，我们需要根据列出的时间范围，输入开始和结束的snap编号。

     

     

    Specify the Begin and End Snapshot Ids

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    Enter value for begin_snap: 1796

    Begin Snapshot Id specified: 1796

     

    Enter value for end_snap: 1813  

     

     

    ü        确定报告名称

     

    最后就是确定生成报告的名称。一般采用默认的名称就可以了。

     

     

    Specify the Report Name

    ~~~~~~~~~~~~~~~~~~~~~~~

    The default report file name is awrrpt_1_1796_1813.html.  To use this name,

    press to continue, otherwise enter an alternative.

     

    Enter value for report_name:

     

     

    之后输出内容很多，此处不加以累述。最后提示报告生成成功。

     

     

    Report written to awrrpt_1_1796_1813.html

     

     

    于是，指定目录上可以看到相应的报告文件。

     

     

    [oracle@bspdev test]$ ls -l

    total 508

    -rw-r--r--. 1 oracle oinstall 515262 Jun 21 13:10 awrrpt_1_1796_1813.html

     

     

     

    3、说明两个问题

     

    首先，此处生成的html格式的报表。如果要求生成txt格式，就在生成过程中选择text格式报表。

     

     

    Specify the Report Type

    ~~~~~~~~~~~~~~~~~~~~~~~

    Would you like an HTML report, or a plain text report?

    Enter 'html' for an HTML report, or 'text' for plain text

    Defaults to 'html'

    Enter value for report_type: text

     

    Type Specified:  text

     

    End of Report

    Report written to awrrpt_1_1789_1800.txt

     

    [oracle@bspdev test]$ ls -l

    total 692

    -rw-r--r--. 1 oracle oinstall 180601 Jun 21 13:27 awrrpt_1_1789_1800.txt

    -rw-r--r--. 1 oracle oinstall 515262 Jun 21 13:10 awrrpt_1_1796_1813.html

     

     

     

    第二个就是调用脚本的方式问题。调用时使用的sqlplus客户端可以在Oracle服务器本机上（远程登录），也可以在客户端机器本机上。笔者建议是在客户端本机上进行生成，这样可以避免报告文件来回拷贝的工作。但是最好要保证客户端版本与服务器版本相匹配。

     

     

    4、结论

     

    手工生成AWR报告，可以避免受到OEM的限制约束，而且灵活度高。本篇记录，权当备忘。

    查看(384) 评论(0) 收藏 分享 管理

  • mysql数据库使用存储过程造1000条数据

    2012-04-06 14:03:04

    数据库mytest
    表hotel
    表的设计如附图
     
    DELIMITER $$
    USE `mytest`$$
    DROP PROCEDURE IF EXISTS `insert_hotel_mapping_pro`$$
    CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_hotel_mapping_pro`()
    BEGIN
    DECLARE i INT;
     SET i=0;
       WHILE i<= 1000 DO
           INSERT INTO hotel(propertyid,NAME,passport,STATUS)
              VALUES(80000+i,CONCAT('lowcost_test_', i),CONCAT('AAAAAAAAAAAAAAAAAAAAAAAAAFFFFFF',i),'ENABLED');
           SET i=i + 1;
        END WHILE;
     END$$
    DELIMITER ;

    查看(5914) 评论(0) 收藏 分享 管理

    •  

    Per Second

    Per Transaction

    Redo size:

    918,805.72

    775,912.72

    Logical reads:

    3,521.77

    2,974.06

    Block changes:

    1,817.95

    1,535.22

    Physical reads:

    68.26

    57.64

    Physical writes:

    362.59

    306.20

    User calls:

    326.69

    275.88

    Parses:

    38.66

    32.65

    Hard parses:

    0.03

    0.03

    Sorts:

    0.61

    0.51

    Logons:

    0.01

    0.01