目录
调优排故方法论
调优排故的一般步骤
分析dump和trace文件
查看trace文件位置
SQL> show parameter dump;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_core_dump string partial
background_dump_dest string /u01/app/oracle/product/19/dbh
ome_1/rdbms/log
core_dump_dest string /u01/app/oracle/diag/rdbms/orc
l/orcl/cdump
max_dump_file_size string unlimited
shadow_core_dump string partial
user_dump_dest string /u01/app/oracle/product/19/dbh
ome_1/rdbms/log
分析等待事件
通过gdb工具深入调查等待事件
1、打开会话窗口，查看SPID
SQL> select c.sid,spid,pid,a.SERIAL# from (select sid from v$mystat where rownum<=1) c,v$session a,v$process b where c.sid=a.sid and a.paddr=b.addr;
SID SPID PID SERIAL#
---------- ------------------------ ---------- ----------
14 11829 33 231382、打开gdb调试窗口
[root@11rac1 ~]# gdb /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle 11829
GNU gdb (GDB) Red Hat Enterprise Linux (7.2-92.el6)
Copyright (C) 2010 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html>
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law. Type "show copying"
and "show warranty" for details.
This GDB was configured as "x86_64-redhat-linux-gnu".
For bug reporting instructions, please see:
<http://www.gnu.org/software/gdb/bugs/>...
Reading symbols from /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle...(no debugging symbols found)...done.
Attaching to program: /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle, process 11829
Reading symbols from /u01/app/oracle/product/11.2.0.4/dbhome_1/lib/libodm11.so...(no debugging symbols found)...done.
Loaded symbols for /u01/app/oracle/product/11.2.0.4/dbhome_1/lib/libodm11.so
Reading symbols from /u01/app/oracle/product/11.2.0.4/dbhome_1/lib/libcell11.so...done.
Loaded symbols for /u01/app/oracle/product/11.2.0.4/dbhome_1/lib/libcell11.so
Reading symbols from /u01/app/oracle/product/11.2.0.4/dbhome_1/lib/libskgxp11.so...(no debugging symbols found)...done.
Loaded symbols for /u01/app/oracle/product/11.2.0.4/dbhome_1/lib/libskgxp11.so
Reading symbols from /lib64/librt.so.1...(no debugging symbols found)...done.
Loaded symbols for /lib64/librt.so.1
Reading symbols from /u01/app/oracle/product/11.2.0.4/dbhome_1/lib/libnnz11.so...(no debugging symbols found)...done.
Loaded symbols for /u01/app/oracle/product/11.2.0.4/dbhome_1/lib/libnnz11.so
Reading symbols from /u01/app/oracle/product/11.2.0.4/dbhome_1/lib/libclsra11.so...done.
Loaded symbols for /u01/app/oracle/product/11.2.0.4/dbhome_1/lib/libclsra11.so
Reading symbols from /u01/app/oracle/product/11.2.0.4/dbhome_1/lib/libdbcfg11.so...(no debugging symbols found)...done.
Loaded symbols for /u01/app/oracle/product/11.2.0.4/dbhome_1/lib/libdbcfg11.so
Reading symbols from /u01/app/oracle/product/11.2.0.4/dbhome_1/lib/libhasgen11.so...done.
Loaded symbols for /u01/app/oracle/product/11.2.0.4/dbhome_1/lib/libhasgen11.so
Reading symbols from /u01/app/oracle/product/11.2.0.4/dbhome_1/lib/libskgxn2.so...(no debugging symbols found)...done.
Loaded symbols for /u01/app/oracle/product/11.2.0.4/dbhome_1/lib/libskgxn2.so
Reading symbols from /u01/app/oracle/product/11.2.0.4/dbhome_1/lib/libocr11.so...done.
Loaded symbols for /u01/app/oracle/product/11.2.0.4/dbhome_1/lib/libocr11.so
Reading symbols from /u01/app/oracle/product/11.2.0.4/dbhome_1/lib/libocrb11.so...done.
Loaded symbols for /u01/app/oracle/product/11.2.0.4/dbhome_1/lib/libocrb11.so
Reading symbols from /u01/app/oracle/product/11.2.0.4/dbhome_1/lib/libocrutl11.so...done.
Loaded symbols for /u01/app/oracle/product/11.2.0.4/dbhome_1/lib/libocrutl11.so
Reading symbols from /lib64/libaio.so.1...(no debugging symbols found)...done.
Loaded symbols for /lib64/libaio.so.1
Reading symbols from /lib64/libdl.so.2...(no debugging symbols found)...done.
Loaded symbols for /lib64/libdl.so.2
Reading symbols from /lib64/libm.so.6...(no debugging symbols found)...done.
Loaded symbols for /lib64/libm.so.6
Reading symbols from /lib64/libpthread.so.0...(no debugging symbols found)...done.
[Thread debugging using libthread_db enabled]
Loaded symbols for /lib64/libpthread.so.0
Reading symbols from /lib64/libnsl.so.1...(no debugging symbols found)...done.
Loaded symbols for /lib64/libnsl.so.1
Reading symbols from /lib64/libc.so.6...(no debugging symbols found)...done.
Loaded symbols for /lib64/libc.so.6
Reading symbols from /lib64/ld-linux-x86-64.so.2...(no debugging symbols found)...done.
Loaded symbols for /lib64/ld-linux-x86-64.so.2
Reading symbols from /usr/lib64/libnuma.so.1...(no debugging symbols found)...done.
Loaded symbols for /usr/lib64/libnuma.so.1
Reading symbols from /lib64/libnss_files.so.2...(no debugging symbols found)...done.
Loaded symbols for /lib64/libnss_files.so.2
Reading symbols from /u01/app/oracle/product/11.2.0.4/dbhome_1/lib/libnque11.so...(no debugging symbols found)...done.
Loaded symbols for /u01/app/oracle/product/11.2.0.4/dbhome_1/lib/libnque11.so
0x000000364720e810 in __read_nocancel () from /lib64/libpthread.so.0
Missing separate debuginfos, use: debuginfo-install glibc-2.12-1.209.el6.x86_64 libaio-0.3.107-10.el6.x86_64 numactl-2.0.9-2.el6.x86_64
(gdb)3、在kslwtbctx函数出设置断点
kslwtbctx函数是等待事件的起始函数，在它的入口处设置断点"b" (break)后，使用“c”（continue）继续处理指令
(gdb) b kslwtbctx
Breakpoint 1 at 0x95bcf32
调制工具中按c继续处理信息流
(gdb) c
Continuing.
3、查询表tb_02
SQL> select * from tb_02;
会话被gdb hang住，说明断点已触发，查看gdb中情况
5、info register 显示寄存器值
6、查看rdi指向的数据 x/32 0x7ffd73e64f90
7、出现4列数值，研究了一下，每个数值均为十进制（书中显示为16进制）每个数值代表4个字节，其中第104字节处数值为十进制的11，该值为等待事件的event号
Breakpoint 1, 0x00000000095bcf32 in kslwtbctx ()
(gdb) info register
rax 0x4 4
rbx 0xee6098e0 3999308000
rcx 0x2 2
rdx 0x0 0
rsi 0x7a59 31321
rdi 0x7ffd73e64f90 140726547926928
rbp 0x7ffd73e64f80 0x7ffd73e64f80
rsp 0x7ffd73e64f80 0x7ffd73e64f80
r8 0xee5cb9b8 3999054264
r9 0x0 0
r10 0xf352b4d8 4082283736
r11 0x0 0
r12 0x0 0
r13 0x4 4
r14 0x0 0
r15 0x0 0
rip 0x95bcf32 0x95bcf32 <kslwtbctx+4>
eflags 0x246 [ PF ZF IF ]
cs 0x33 51
ss 0x2b 43
ds 0x0 0
es 0x0 0
fs 0x0 0
gs 0x0 0
(gdb) x/32 0x7ffd73e64f90
0x7ffd73e64f90: 31321 0 526670485 1764394442
0x7ffd73e64fa0: 0 0 4 0
0x7ffd73e64fb0: 0 0 4 0
0x7ffd73e64fc0: 610556565 1764394442 581196437 -1764724545
0x7ffd73e64fd0: 0 0 1 0
0x7ffd73e64fe0: -274678488 0 1 0
0x7ffd73e64ff0: 177455104 0 11 0
0x7ffd73e65000: 2147483647 32765 2 08、查询该事件可得知，等待事件为Disk file operations I/O，并不是书中说提到的 db file sequential read，不知道是什么回事
SQL> select event# ,name from v$event_name where event#=11;
EVENT# NAME
---------- ------------------------------
11 Disk file operations I/O
SQL> select sid,event,total_waits from v$session_event where sid in(14);
SID EVENT TOTAL_WAITS
---------- ------------------------------ -----------
14 Disk file operations I/O 1
14 SQL*Net message to client 21
14 SQL*Net message from client 209、按c继续，查询新rdi对应的数值，此时该位置变为147
(gdb) c
Continuing.
Breakpoint 1, 0x00000000095bcf32 in kslwtbctx ()
(gdb) info register
rax 0x0 0
rbx 0x1c0 448
rcx 0x29fb20 2751264
rdx 0xc354334 204817204
rsi 0x7fac604138e8 140378325989608
rdi 0x7fac604138e8 140378325989608
rbp 0x7ffe23c544b0 0x7ffe23c544b0
rsp 0x7ffe23c544b0 0x7ffe23c544b0
r8 0xee60dd38 3999325496
r9 0x8 8
r10 0x0 0
r11 0x7ffe23c544f0 140729498551536
r12 0xc353a60 204814944
r13 0x7fac5ff7f000 140378321186816
r14 0x1 1
r15 0x7fac604138e8 140378325989608
rip 0x95bcf32 0x95bcf32 <kslwtbctx+4>
eflags 0x202 [ IF ]
cs 0x33 51
ss 0x2b 43
ds 0x0 0
es 0x0 0
fs 0x0 0
gs 0x0 0
(gdb) x/32 0x7fac604138e8
0x7fac604138e8: 600144473 32766 3 0
0x7fac604138f8: 201526659 -1812864422 1914506627 1812836035
0x7fac60413908: 0 0 -295659296 0
0x7fac60413918: 600136880 32766 37217702 0
0x7fac60413928: 59683 44 -1757511141 384272
0x7fac60413938: 0 0 1 384272
0x7fac60413948: 166391500 0 147 0
0x7fac60413958: 2147483647 -1812864422 4 010、查询该事件名可知书中的db file sequential read 等待事件出现了，同时查询14号session的所有事件，发现Disk file operations I/O次数增加了1，貌似在调试工具中可以提前发现该等待事件
SQL> select event# ,name from v$event_name where event#=147;
EVENT# NAME
---------- ------------------------------
147 db file sequential read
SQL> select sid,event,total_waits from v$session_event where sid in(14);
SID EVENT TOTAL_WAITS
---------- ------------------------------ -----------
14 Disk file operations I/O 2
14 SQL*Net message to client 21
14 SQL*Net message from client 2011、继续书中的实验按c处理信息流发现值变为148，对应等待事件为db file scattered read，而视图中也如期的出现了db file sequential read等待事件
(gdb) c
Continuing.
Breakpoint 1, 0x00000000095bcf32 in kslwtbctx ()
(gdb) info register
rax 0x0 0
rbx 0x1c0 448
rcx 0x29fb30 2751280
rdx 0xc354334 204817204
rsi 0x7fac604138e8 140378325989608
rdi 0x7fac604138e8 140378325989608
rbp 0x7ffe23c4e0a0 0x7ffe23c4e0a0
rsp 0x7ffe23c4e0a0 0x7ffe23c4e0a0
r8 0xee60dd38 3999325496
r9 0xa 10
r10 0x0 0
r11 0x7ffe23c4e0e0 140729498525920
r12 0xc353a60 204814944
r13 0x7fac5ff7f000 140378321186816
r14 0x1 1
r15 0x7fac604138e8 140378325989608
rip 0x95bcf32 0x95bcf32 <kslwtbctx+4>
eflags 0x202 [ IF ]
cs 0x33 51
ss 0x2b 43
ds 0x0 0
es 0x0 0
fs 0x0 0
gs 0x0 0
(gdb) x/32 0x7fac604138e8
0x7fac604138e8: 600078937 32766 158102433 0
0x7fac604138f8: 600111120 32766 -253032952 0
0x7fac60413908: -274678088 0 62 0
0x7fac60413918: -274679344 0 0 0
0x7fac60413928: 0 0 -940912048 0
0x7fac60413938: -274541624 0 1 0
0x7fac60413948: 166391500 0 148 0
0x7fac60413958: 2147483647 0 4 0
SQL> select event# ,name from v$event_name where event#=148;
EVENT# NAME
---------- ------------------------------
148 db file scattered read
SQL> select sid,event,total_waits from v$session_event where sid in(14);
SID EVENT TOTAL_WAITS
---------- ------------------------------ -----------
14 Disk file operations I/O 2
14 db file sequential read 1
14 SQL*Net message to client 21
14 SQL*Net message from client 20
各种视图介绍
前面学习gdb花了好久时间，为了得出书中实验结果也查阅了很多资料，后面就偷个懒，就直接把官方文档的信息贴上来了
V$SYSSTAT
V$SYSSTAT displays system statistics. To find the name of the statistic associated with each statistic number (STATISTIC#), query the V$STATNAME view.
| Column | Datatype | Description |
| STATISTIC# | NUMBER | Statistic number Note: Statistics numbers are not guaranteed to remain constant from one release to another. Therefore, you should rely on the statistics name rather than its number in your applications. |
| NAME | VARCHAR2(64) | Statistic name. You can get a complete listing of statistic names by querying the V$STATNAME view. |
| CLASS | NUMBER | A number representing one or more statistics class. The following class numbers are additive:
|
| VALUE | NUMBER | Statistic value |
| STAT_ID | NUMBER | Identifier of the statistic |
| CON_ID | NUMBER | The ID of the container to which the data pertains. Possible values include:
|
V$SESSTAT
V$SESSTAT displays user session statistics. To find the name of the statistic associated with each statistic number (STATISTIC#), query the V$STATNAME view.
| Column | Datatype | Description |
| SID | NUMBER | Session identifier |
| STATISTIC# | NUMBER | Statistic number Note: Statistics numbers are not guaranteed to remain constant from one release to another. Therefore, you should rely on the statistics name rather than its number in your applications. |
| VALUE | NUMBER | Statistic value |
| CON_ID | NUMBER | The ID of the container to which the data pertains. Possible values include:
|
V$STATNAME
V$STATNAME displays decoded statistic names for the statistics shown in the V$SESSTAT and V$SYSSTAT tables.
On some platforms, the NAME and CLASS columns contain additional operating system-specific statistics.
| Column | Datatype | Description |
| STATISTIC# | NUMBER | Statistic number Note: Statistics numbers are not guaranteed to remain constant from one release to another. Therefore, you should rely on the statistics name rather than its number in your applications. |
| NAME | VARCHAR2(64) | Statistic name. Names that appear in this column remain stable across Oracle Database releases, and they can be relied on by customer scripts. |
| CLASS | NUMBER | A number representing one or more statistics classes. The following class numbers are additive:
|
| STAT_ID | NUMBER | Identifier of the statistic |
| DISPLAY_NAME | VARCHAR2(64) | A clearer and more descriptive name for the statistic that appears in the NAME column. Names that appear in the DISPLAY_NAME column can change across Oracle Database releases, therefore customer scripts should not rely on names that appear in the DISPLAY_NAME column across releases. |
| CON_ID | NUMBER | The ID of the container to which the data pertains. Possible values include:
|
V$UNDOSTAT
V$UNDOSTAT displays a histogram of statistical data to show how well the system is working. The available statistics include undo space consumption, transaction concurrency, and length of queries executed in the instance. You can use this view to estimate the amount of undo space required for the current workload. Oracle uses this view to tune undo usage in the system. The view returns NULL values if the system is in manual undo management mode.
Each row in the view keeps statistics collected in the instance for a 10-minute interval. The rows are in descending order by the BEGIN_TIME column value. Each row belongs to the time interval marked by (BEGIN_TIME, END_TIME). Each column represents the data collected for the particular statistic in that time interval. The first row of the view contains statistics for the (partial) current time period. The view contains a total of 576 rows, spanning a 4 day cycle.
| Column | Datatype | Description |
| BEGIN_TIME | DATE | Identifies the beginning of the time interval |
| END_TIME | DATE | Identifies the end of the time interval |
| UNDOTSN | NUMBER | Represents the last active undo tablespace in the duration of time. The tablespace ID of the active undo tablespace is returned in this column. If more than one undo tablespace was active in that period, the active undo tablespace that was active at the end of the period is reported. |
| UNDOBLKS | NUMBER | Represents the total number of undo blocks consumed. You can use this column to obtain the consumption rate of undo blocks, and thereby estimate the size of the undo tablespace needed to handle the workload on your system. |
| TXNCOUNT | NUMBER | Identifies the total number of transactions executed within the period |
| MAXQUERYLEN | NUMBER | Identifies the length of the longest query (in seconds) executed in the instance during the period. You can use this statistic to estimate the proper setting of the UNDO_RETENTION initialization parameter. The length of a query is measured from the cursor open time to the last fetch/execute time of the cursor. Only the length of those cursors that have been fetched/executed during the period are reflected in the view. |
| MAXQUERYID | VARCHAR2(13) | SQL identifier of the longest running SQL statement in the period |
| MAXCONCURRENCY | NUMBER | Identifies the highest number of transactions executed concurrently within the period |
| UNXPSTEALCNT | NUMBER | Number of attempts to obtain undo space by stealing unexpired extents from other transactions |
| UNXPBLKRELCNT | NUMBER | Number of unexpired blocks removed from certain undo segments so they can be used by other transactions |
| UNXPBLKREUCNT | NUMBER | Number of unexpired undo blocks reused by transactions |
| EXPSTEALCNT | NUMBER | Number of attempts to steal expired undo blocks from other undo segments |
| EXPBLKRELCNT | NUMBER | Number of expired undo blocks stolen from other undo segments |
| EXPBLKREUCNT | NUMBER | Number of expired undo blocks reused within the same undo segments |
| SSOLDERRCNT | NUMBER | Identifies the number of times the error ORA-01555 occurred. You can use this statistic to decide whether or not the UNDO_RETENTION initialization parameter is set properly given the size of the undo tablespace. Increasing the value of UNDO_RETENTION can reduce the occurrence of this error. |
| NOSPACEERRCNT | NUMBER | Identifies the number of times space was requested in the undo tablespace and there was no free space available. That is, all of the space in the undo tablespace was in use by active transactions. The corrective action is to add more space to the undo tablespace. |
| ACTIVEBLKS | NUMBER | Total number of blocks in the active extents of the undo tablespace for the instance at the sampled time in the period |
| UNEXPIREDBLKS | NUMBER | Total number of blocks in the unexpired extents of the undo tablespace for the instance at the sampled time in the period |
| EXPIREDBLKS | NUMBER | Total number of blocks in the expired extents of the undo tablespace for the instance at the sampled time in the period |
| TUNED_UNDORETENTION | NUMBER | Amount of time (in seconds) for which undo will not be recycled from the time it was committed. At any point in time, the latest value of TUNED_UNDORETENTION is used to determine whether data committed at a particular time in the past can be recycled. The value of this column is not meaningful on an Oracle Active Data Guard standby database instance, because the system does not tune this value on such instances. |
| CON_ID | NUMBER | The ID of the container to which the data pertains. Possible values include:
|
835
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
