orale 10 RAC升级失败，升级后性能超慢

棉花糖ONE · 发表于 2009-7-14 09:37

truss -f -d sqlplus / as sysdba看看时间花哪了

棉花糖ONE · 发表于 2009-7-14 09:38

应用连接上以后执行的慢吗,还是只是在建立连接的时候特别慢

lifewise · 发表于 2009-7-14 11:28

[oracle@rac1 ~]$ strace sqlplus "/ as sysdba"
execve("/home/oracle/10.2.0.4/db/bin/sqlplus", ["sqlplus", "/ as sysdba"], [/* 33 vars */]) = 0
brk(0)                               = 0x18dba000
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x2b2c62655000
uname({sys="Linux", node="rac1", ...})  = 0
access("/etc/ld.so.preload", R_OK)    = -1 ENOENT (No such file or directory)
open("/home/oracle/10.2.0.4/db/lib/tls/x86_64/libsqlplus.so", O_RDONLY) = -1 ENOENT (No such file or directory)
stat("/home/oracle/10.2.0.4/db/lib/tls/x86_64", 0x7fff48451080) = -1 ENOENT (No such file or directory)
open("/home/oracle/10.2.0.4/db/lib/tls/libsqlplus.so", O_RDONLY) = -1 ENOENT (No such file or directory)
stat("/home/oracle/10.2.0.4/db/lib/tls", 0x7fff48451080) = -1 ENOENT (No such file or directory)
open("/home/oracle/10.2.0.4/db/lib/x86_64/libsqlplus.so", O_RDONLY) = -1 ENOENT (No such file or directory)
stat("/home/oracle/10.2.0.4/db/lib/x86_64", 0x7fff48451080) = -1 ENOENT (No such file or directory)
open("/home/oracle/10.2.0.4/db/lib/libsqlplus.so", O_RDONLY) = 3
read(3, "\177ELF\2\1\1\0\0\0\0\0\0\0\0\0\3\0>\0\1\0\0\0\0\252\2\0\0\0\0\0"..., 832) = 832
fstat(3, {st_mode=S_IFREG|0644, st_size=997409, ...}) = 0
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x2b2c62656000
mmap(NULL, 2043464, PROT_READ|PROT_EXEC, MAP_PRIVATE|MAP_DENYWRITE, 3, 0) = 0x2b2c62657000
mprotect(0x2b2c6273d000, 1048576, PROT_NONE) = 0
mmap(0x2b2c6283d000, 53248, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED|MAP_DENYWRITE, 3, 0xe6000) = 0x2b2c6283d000
close(3)                               = 0
open("/home/oracle/10.2.0.4/db/lib/libclntsh.so.10.1", O_RDONLY) = 3
read(3, "\177ELF\2\1\1\0\0\0\0\0\0\0\0\0\3\0>\0\1\0\0\0\340F&\0\0\0\0\0"..., 832) = 832
fstat(3, {st_mode=S_IFREG|0750, st_size=21188349, ...}) = 0
mmap(NULL, 21949888, PROT_READ|PROT_EXEC, MAP_PRIVATE|MAP_DENYWRITE, 3, 0) = 0x2b2c6284a000
mprotect(0x2b2c63a7a000, 2093056, PROT_NONE) = 0
mmap(0x2b2c63c79000, 675840, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED|MAP_DENYWRITE, 3, 0x122f000) = 0x2b2c63c79000
mmap(0x2b2c63d1e000, 110016, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED|MAP_ANONYMOUS, -1, 0) = 0x2b2c63d1e000
close(3)                               = 0
open("/home/oracle/10.2.0.4/db/lib/libnnz10.so", O_RDONLY) = 3
read(3, "\177ELF\2\1\1\0\0\0\0\0\0\0\0\0\3\0>\0\1\0\0\0\200\275\f\0\0\0\0\0"..., 832) = 832
fstat(3, {st_mode=S_IFREG|0644, st_size=3796601, ...}) = 0
mmap(NULL, 4848040, PROT_READ|PROT_EXEC, MAP_PRIVATE|MAP_DENYWRITE, 3, 0) = 0x2b2c63d39000
mprotect(0x2b2c64028000, 1044480, PROT_NONE) = 0
mmap(0x2b2c64127000, 724992, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED|MAP_DENYWRITE, 3, 0x2ee000) = 0x2b2c64127000
mmap(0x2b2c641d8000, 2472, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED|MAP_ANONYMOUS, -1, 0) = 0x2b2c641d8000
close(3)                               = 0
open("/home/oracle/10.2.0.4/db/lib/libdl.so.2", O_RDONLY) = -1 ENOENT (No such file or directory)
open("/home/oracle/10.2.0.4/db/lib/libdl.so.2", O_RDONLY) = -1 ENOENT (No such file or directory)
open("/lib/tls/x86_64/libdl.so.2", O_RDONLY) = -1 ENOENT (No such file or directory)
stat("/lib/tls/x86_64", 0x7fff48450ff0) = -1 ENOENT (No such file or directory)
open("/lib/tls/libdl.so.2", O_RDONLY) = -1 ENOENT (No such file or directory)
stat("/lib/tls", 0x7fff48450ff0)       = -1 ENOENT (No such file or directory)
open("/lib/x86_64/libdl.so.2", O_RDONLY) = -1 ENOENT (No such file or directory)
stat("/lib/x86_64", 0x7fff48450ff0)    = -1 ENOENT (No such file or directory)
open("/lib/libdl.so.2", O_RDONLY)    = 3
read(3, "\177ELF\1\1\1\0\0\0\0\0\0\0\0\0\3\0\3\0\1\0\0\0P\332`\0004\0\0\0"..., 832) = 832
close(3)                               = 0
stat("/lib", {st_mode=S_IFDIR|0755, st_size=4096, ...}) = 0
open("/usr/lib/tls/x86_64/libdl.so.2", O_RDONLY) = -1 ENOENT (No such file or directory)
stat("/usr/lib/tls/x86_64", 0x7fff48450ff0) = -1 ENOENT (No such file or directory)
open("/usr/lib/tls/libdl.so.2", O_RDONLY) = -1 ENOENT (No such file or directory)
stat("/usr/lib/tls", 0x7fff48450ff0) = -1 ENOENT (No such file or directory)
open("/usr/lib/x86_64/libdl.so.2", O_RDONLY) = -1 ENOENT (No such file or directory)
stat("/usr/lib/x86_64", 0x7fff48450ff0) = -1 ENOENT (No such file or directory)
open("/usr/lib/libdl.so.2", O_RDONLY) = -1 ENOENT (No such file or directory)
stat("/usr/lib", {st_mode=S_IFDIR|0755, st_size=65536, ...}) = 0
open("/usr/local/lib/tls/x86_64/libdl.so.2", O_RDONLY) = -1 ENOENT (No such file or directory)
stat("/usr/local/lib/tls/x86_64", 0x7fff48450ff0) = -1 ENOENT (No such file or directory)
open("/usr/local/lib/tls/libdl.so.2", O_RDONLY) = -1 ENOENT (No such file or directory)
stat("/usr/local/lib/tls", 0x7fff48450ff0) = -1 ENOENT (No such file or directory)
open("/usr/local/lib/x86_64/libdl.so.2", O_RDONLY) = -1 ENOENT (No such file or directory)
stat("/usr/local/lib/x86_64", 0x7fff48450ff0) = -1 ENOENT (No such file or directory)
open("/usr/local/lib/libdl.so.2", O_RDONLY) = -1 ENOENT (No such file or directory)
stat("/usr/local/lib", {st_mode=S_IFDIR|0755, st_size=4096, ...}) = 0
open("/etc/ld.so.cache", O_RDONLY)    = 3
fstat(3, {st_mode=S_IFREG|0644, st_size=145661, ...}) = 0
mmap(NULL, 145661, PROT_READ, MAP_PRIVATE, 3, 0) = 0x2b2c641d9000
close(3)                               = 0
open("/lib64/libdl.so.2", O_RDONLY)    = 3
read(3, "\177ELF\2\1\1\0\0\0\0\0\0\0\0\0\3\0>\0\1\0\0\0\20\16@b9\0\0\0"..., 832) = 832
fstat(3, {st_mode=S_IFREG|0755, st_size=23360, ...}) = 0
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x2b2c641fd000
mmap(0x3962400000, 2109696, PROT_READ|PROT_EXEC, MAP_PRIVATE|MAP_DENYWRITE, 3, 0) = 0x3962400000
mprotect(0x3962402000, 2097152, PROT_NONE) = 0
mmap(0x3962602000, 8192, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED|MAP_DENYWRITE, 3, 0x2000) = 0x3962602000
close(3)                               = 0
open("/home/oracle/10.2.0.4/db/lib/libm.so.6", O_RDONLY) = -1 ENOENT (No such file or directory)
open("/home/oracle/10.2.0.4/db/lib/libm.so.6", O_RDONLY) = -1 ENOENT (No such file or directory)
open("/lib/libm.so.6", O_RDONLY)       = 3
read(3, "\177ELF\1\1\1\0\0\0\0\0\0\0\0\0\3\0\3\0\1\0\0\0\20\344\\\0004\0\0\0"..., 832) = 832
close(3)                               = 0
open("/usr/lib/libm.so.6", O_RDONLY) = -1 ENOENT (No such file or directory)
open("/usr/local/lib/libm.so.6", O_RDONLY) = -1 ENOENT (No such file or directory)
open("/lib64/libm.so.6", O_RDONLY)    = 3
read(3, "\177ELF\2\1\1\0\0\0\0\0\0\0\0\0\3\0>\0\1\0\0\0`>\200b9\0\0\0"..., 832) = 832
fstat(3, {st_mode=S_IFREG|0755, st_size=615136, ...}) = 0
mmap(0x3962800000, 2629848, PROT_READ|PROT_EXEC, MAP_PRIVATE|MAP_DENYWRITE, 3, 0) = 0x3962800000
mprotect(0x3962882000, 2093056, PROT_NONE) = 0
mmap(0x3962a81000, 8192, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED|MAP_DENYWRITE, 3, 0x81000) = 0x3962a81000
close(3)                               = 0
open("/home/oracle/10.2.0.4/db/lib/libpthread.so.0", O_RDONLY) = -1 ENOENT (No such file or directory)
open("/home/oracle/10.2.0.4/db/lib/libpthread.so.0", O_RDONLY) = -1 ENOENT (No such file or directory)
open("/lib/libpthread.so.0", O_RDONLY)  = 3
read(3, "\177ELF\1\1\1\0\0\0\0\0\0\0\0\0\3\0\3\0\1\0\0\0000\210_\0004\0\0\0"..., 832) = 832
close(3)                               = 0
open("/usr/lib/libpthread.so.0", O_RDONLY) = -1 ENOENT (No such file or directory)
open("/usr/local/lib/libpthread.so.0", O_RDONLY) = -1 ENOENT (No such file or directory)
open("/lib64/libpthread.so.0", O_RDONLY) = 3
read(3, "\177ELF\2\1\1\0\0\0\0\0\0\0\0\0\3\0>\0\1\0\0\0\200W\300b9\0\0\0"..., 832) = 832
fstat(3, {st_mode=S_IFREG|0755, st_size=145592, ...}) = 0
mmap(0x3962c00000, 2204528, PROT_READ|PROT_EXEC, MAP_PRIVATE|MAP_DENYWRITE, 3, 0) = 0x3962c00000
mprotect(0x3962c16000, 2093056, PROT_NONE) = 0
mmap(0x3962e15000, 8192, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED|MAP_DENYWRITE, 3, 0x15000) = 0x3962e15000
mmap(0x3962e17000, 13168, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED|MAP_ANONYMOUS, -1, 0) = 0x3962e17000
close(3)                               = 0
open("/home/oracle/10.2.0.4/db/lib/libnsl.so.1", O_RDONLY) = -1 ENOENT (No such file or directory)
open("/home/oracle/10.2.0.4/db/lib/libnsl.so.1", O_RDONLY) = -1 ENOENT (No such file or directory)
open("/lib/libnsl.so.1", O_RDONLY)    = 3
read(3, "\177ELF\1\1\1\0\0\0\0\0\0\0\0\0\3\0\3\0\1\0\0\0 \301\227\0004\0\0\0"..., 832) = 832
close(3)                               = 0
open("/usr/lib/libnsl.so.1", O_RDONLY)  = -1 ENOENT (No such file or directory)
open("/usr/local/lib/libnsl.so.1", O_RDONLY) = -1 ENOENT (No such file or directory)
open("/lib64/libnsl.so.1", O_RDONLY) = 3
read(3, "\177ELF\2\1\1\0\0\0\0\0\0\0\0\0\3\0>\0\1\0\0\0\240@\200i9\0\0\0"..., 832) = 832
fstat(3, {st_mode=S_IFREG|0755, st_size=114352, ...}) = 0
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x2b2c641fe000
mmap(0x3969800000, 2194096, PROT_READ|PROT_EXEC, MAP_PRIVATE|MAP_DENYWRITE, 3, 0) = 0x3969800000
mprotect(0x3969815000, 2093056, PROT_NONE) = 0
mmap(0x3969a14000, 8192, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED|MAP_DENYWRITE, 3, 0x14000) = 0x3969a14000
mmap(0x3969a16000, 6832, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED|MAP_ANONYMOUS, -1, 0) = 0x3969a16000
close(3)                               = 0
open("/home/oracle/10.2.0.4/db/lib/libc.so.6", O_RDONLY) = -1 ENOENT (No such file or directory)
open("/home/oracle/10.2.0.4/db/lib/libc.so.6", O_RDONLY) = -1 ENOENT (No such file or directory)
open("/lib/libc.so.6", O_RDONLY)       = 3
read(3, "\177ELF\1\1\1\0\0\0\0\0\0\0\0\0\3\0\3\0\1\0\0\0\320\257I\0004\0\0\0"..., 832) = 832
close(3)                               = 0
open("/usr/lib/libc.so.6", O_RDONLY) = -1 ENOENT (No such file or directory)
open("/usr/local/lib/libc.so.6", O_RDONLY) = -1 ENOENT (No such file or directory)
open("/lib64/libc.so.6", O_RDONLY)    = 3
read(3, "\177ELF\2\1\1\0\0\0\0\0\0\0\0\0\3\0>\0\1\0\0\0p\332\1b9\0\0\0"..., 832) = 832
fstat(3, {st_mode=S_IFREG|0755, st_size=1713088, ...}) = 0
mmap(0x3962000000, 3494168, PROT_READ|PROT_EXEC, MAP_PRIVATE|MAP_DENYWRITE, 3, 0) = 0x3962000000
mprotect(0x396214c000, 2097152, PROT_NONE) = 0
mmap(0x396234c000, 20480, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED|MAP_DENYWRITE, 3, 0x14c000) = 0x396234c000
mmap(0x3962351000, 16664, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED|MAP_ANONYMOUS, -1, 0) = 0x3962351000
close(3)                               = 0

lifewise · 发表于 2009-7-14 11:29

[oracle@rac1 ~]$ find ./ -name libdl.so.2
./10.2.0.4/crs/lib32/stubs/libdl.so.2
./10.2.0.4/db/lib/stubs/libdl.so.2
./10.2.0.4/db/lib32/stubs/libdl.so.2

lib路径有问题？

lifewise · 发表于 2009-7-14 11:31

现象就是连接慢，连sqlplus都比其他慢几倍，但单独执行sql都不慢，但应用一打开，负载就明显飙升

cc59 · 发表于 2009-7-14 23:11

我觉得问题应该还是出在查询数据字典慢这个问题上面.

cc59 · 发表于 2009-7-14 23:12

这种情况不是hang, truss也没多大的意义.

Yong Huang · 发表于 2009-7-15 01:17

lifewise, is there any logon trigger? When you run strace sqlplus / as sysdba, is there step in the middle that hangs? Or it hangs at the last line close(3)=0? We can't tell from your strace output.

Yong Huang

lifewise · 发表于 2009-7-15 12:19

28365 10:53:13 shmget(0xdae1b58e, 0, 0) = -1 ENOENT (No such file or directory)
28365 10:53:13 shmget(0xdae1b58f, 4096, 0) = -1 ENOENT (No such file or directory)
28365 10:53:13 shmget(0xdae1b58f, 0, 0) = -1 ENOENT (No such file or directory)
28365 10:53:13 times(NULL)             = 474712795
28365 10:53:13 brk(0xf7d3000)          = 0xf7d3000
28365 10:53:13 mmap(NULL, 143360, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x2b0cc9dc7000
28365 10:53:13 mmap(NULL, 143360, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x2b0cc9dea000
28365 10:53:13 mmap(NULL, 143360, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x2b0cc9e0d000
28365 10:53:13 open("/dev/null", O_RDONLY) = 5
28365 10:53:13 dup(5)                = 6
28365 10:53:13 stat("/home/oracle/admin/rac1/bdump", 0x7fffe29ed390) = -1 ENOENT (No such file or directory)
28365 10:53:13 stat("/home/oracle/admin/rac1/udump", 0x7fffe29ed390) = -1 ENOENT (No such file or directory)
28365 10:53:13 setsid()                = -1 EPERM (Operation not permitted)
28365 10:53:13 chdir("/home/oracle/10.2.0.4/db/dbs") = 0
28365 10:53:13 shmget(0x639d243c, 4096, 0) = 425985
28365 10:53:13 open("/dev/shm/ora_rac1_425985", O_RDONLY) = -1 ENOENT (No such file or directory)
28365 10:53:13 shmget(0x639d243c, 0, 0) = 425985
28365 10:53:13 open("/dev/shm/ora_rac1_425985", O_RDONLY) = -1 ENOENT (No such file or directory)
28365 10:53:13 shmctl(425985, IPC_STAT, 0x7fffe29ecdd0) = 0
28365 10:53:13 open("/dev/shm/ora_rac1_425985", O_RDONLY) = -1 ENOENT (No such file or directory)
28365 10:53:13 shmat(425985, 0, 0)    = ?
28365 10:53:13 open("/dev/shm/ora_rac1_425985", O_RDONLY) = -1 ENOENT (No such file or directory)
28365 10:53:13 shmdt(0x2b0cc9e30000) = 0
28365 10:53:13 shmget(0x639d243d, 4096, 0) = -1 ENOENT (No such file or directory)
28365 10:53:13 shmget(0x639d243d, 0, 0) = -1 ENOENT (No such file or directory)
28365 10:53:13 shmget(0x639d243e, 4096, 0) = -1 ENOENT (No such file or directory)
28365 10:53:13 shmget(0x639d243e, 0, 0) = -1 ENOENT (No such file or directory)
28365 10:53:13 shmget(0x639d243f, 4096, 0) = -1 ENOENT (No such file or directory)
28365 10:53:13 shmget(0x639d243f, 0, 0) = -1 ENOENT (No such file or directory)
28365 10:53:13 open("/dev/shm/ora_rac1_425985", O_RDONLY) = -1 ENOENT (No such file or directory)
28365 10:53:13 shmat(425985, 0x60000000, 0) = ?
28365 10:53:15 getcwd("/home/oracle/10.2.0.4/db/dbs"..., 256) = 29
28365 10:53:15 access("/etc/intchg.ora", F_OK) = -1 ENOENT (No such file or directory)
28365 10:53:15 access("/home/oracle/10.2.0.4/db/network/admin/intchg.ora", F_OK) = -1 ENOENT (No such file or directory)
28365 10:53:15 access("/etc/tnsnav.ora", F_OK) = -1 ENOENT (No such file or directory)
28365 10:53:15 access("/home/oracle/10.2.0.4/db/network/admin/tnsnav.ora", F_OK) = -1 ENOENT (No such file or directory)
28365 10:53:15 getrlimit(RLIMIT_NOFILE, {rlim_cur=64*1024, rlim_max=64*1024}) = 0
28365 10:53:15 getppid()             = 28364
28365 10:53:15 fcntl(8, F_SETFD, FD_CLOEXEC) = 0
28365 10:53:15 fcntl(11, F_SETFD, FD_CLOEXEC) = 0
28365 10:53:15 write(11, "NTP0 28365\n", 11) = 11
28365 10:53:15 fcntl(8, F_SETFD, FD_CLOEXEC <unfinished ...>
28364 10:53:15 <... read resumed> "NTP0 28365\n", 64) = 11
28365 10:53:15 <... fcntl resumed> ) = 0

lifewise · 发表于 2009-7-15 12:21

没有triggle，基本确定问题在
28365 10:53:13 shmat(425985, 0x60000000, 0) = ?
28365 10:53:15 getcwd("/home/oracle/10.2.0.4/db/dbs"..., 256) = 29

谢谢棉花的帮忙

通过应用的小测试程序，慢应该在连接的地方，去数据库取一个小表，升级前30ms，升级后3000ms

[讨论] orale 10 RAC升级失败，升级后性能超慢

回复 #11 棉花糖ONE 的帖子

浏览过的版块