OracleRAC11gr2性能调优解决查询慢问题教学内容.docx
《OracleRAC11gr2性能调优解决查询慢问题教学内容.docx》由会员分享，可在线阅读，更多相关《OracleRAC11gr2性能调优解决查询慢问题教学内容.docx（19页珍藏版）》请在冰豆网上搜索。
OracleRAC11gr2性能调优解决查询慢问题教学内容
知也无涯
OracleRAC11gr2查询太慢
---------------------------------------------------
OracleRAC11gr2查询太慢
ProblemDescription
---------------------------------------------------
Redhat5双机
测试1：
双实例，ASM磁盘组包含3个磁盘（SAN）。
在其中一个实例中执行：
SELECTc.operaccount||':
'||c.PASSWORD||'@'||a.PATH,
a.dll,a.description,'1.gif'
FROMhcs2000.dllnamesa,hcs2000.operdllnamesb,hcs2000.operaccountc
WHEREa.dllnameid=b.dllnameid
ANDb.operid=c.operid
ANDupper（c.operaccount）=USER
ORDERBYa.dllnameid;
第一次查询，25秒。
第二次查询，3秒。
第三次查询，1.6秒。
过10分钟后查询，26秒。
测试2：
在其中一台主机上创建基于ASM磁盘组的单个实例，
第一次查询，14秒。
第二次查询，3秒。
第三次查询，0.7秒。
第四次查询，3.5秒。
测试3：
在其中一台主机上创建基于文件系统的单个实例，
第一次查询，5秒。
第二次查询，2.2秒。
第三次查询，2.1秒。
测试4：
在PC的VMware虚拟机里面单实例查询，只需0.001秒或0秒。
测试1中的查询太慢了，请问怎么查看问题原因，如何调优？
1、现代文化对大学生饰品消费的影响
据介绍，经常光顾“碧芝”的都是些希望得到世界上“独一无二”饰品的年轻人，他们在琳琅满目的货架上挑选，然后亲手串连，他们就是偏爱这种ＤＩＹ的方式，完全自助。
Dearcustomer,
请您执行以下动作：
如果可以，请在您提到的4个场景下都生成以下文件，并请添加您的说明后，作为附件更新到SR上：
ACTIONPLAN
-----------------------
1.Pleasegenerate10046traceforyoursql:
SQL>connectusername/password
SQL>altersessionsettimed_statistics=true;
SQL>altersessionsetstatistics_level=all;
SQL>altersessionsetmax_dump_file_size=unlimited;
SQL>altersessionsetevents'10046tracenamecontextforever,level12';
SQL>
SQL>altersessionsetevents'10046tracenamecontextoff';
2.Formatyour10046tracefile:
$tkprof
例如
生成的文件应该是在您的udump路径下面。
寻找UDUMP路径，请参考
SQL>showparameteruser_dump_dest
之后，format您的文件
$cd/u01/OracleAPP/oracle/admin/R1020/udump
$ls-ltr
$tkprofr1020_ora_9638.trc9638.output
3.请提交您10046trace以及tkprof输出文件9638.output
但这些困难并非能够否定我们创业项目的可行性。
盖茨是由一个普通退学学生变成了世界首富，李嘉诚是由一个穷人变成了华人富豪第一人，他们的成功表述一个简单的道理：
如果你有能力，你可以从身无分文变成超级富豪；如果你无能，你也可以从超级富豪变成穷光蛋。
§8-2购物环境与消费行为2004年3月20日
3、消费“多样化”
二、资料网址：
Dearcustomer,
目前来看，您问题表中遇到了并行的配置。
为了进一步诊断，请执行以下动作，并提供输出结果：
ACTIONPLAN
-----------------------
请分别在测试2：
在其中一台主机上创建基于ASM磁盘组的实例
以及
测试4：
在PC的VMware虚拟机里面单实例查询
的测试环境中执行以下动作
SQL>showparameterparallel_min_servers
SQL>selecttable_name,degreefromdba_tableswheretable_name='dllnames';
SQL>selecttable_name,degreefromdba_tableswheretable_name='operdllnames';
SQL>selecttable_name,degreefromdba_tableswheretable_name='operaccount';
并请提供以上测试2，4环境的数据库alert日志位于bdump下面
SQL>showparameterbackground_dump_dest
Thealert.logisnamedasalert_.log.
手工艺品，它运用不同的材料，通过不同的方式，经过自己亲手动手制作。
看着自己亲自完成的作品时，感觉很不同哦。
不论是01年的丝带编织风铃，02年的管织幸运星，03年的十字绣，04年的星座手链，还是今年风靡一时的针织围巾等这些手工艺品都是陪伴女生长大的象征。
为此，这些多样化的作品制作对我们这一创业项目的今后的操作具有很大的启发作用。
此次调查以女生为主，男生只占很少比例，调查发现58％的学生月生活费基本在400元左右，其具体分布如（图1-1）
3、你是否购买过DIY手工艺制品？
（1）专业知识限制
Name
--------
===ODMDataCollection===
SELECTc.operaccount||':
'||c.PASSWORD||'@'||a.PATH,
a.dll,a.description,'1.gif'
FROMdllnamesa,operdllnamesb,operaccountc
WHEREa.dllnameid=b.dllnameid
ANDb.operid=c.operid
ANDupper（c.operaccount）=USER
ORDERBYa.dllnameid
callcountcpuelapseddiskquerycurrentrows
-----------------------------------------------------------------------
Parse10.000.000000
Execute10.0111.140300
Fetch20.032.240001
-----------------------------------------------------------------------
total40.0513.390301
Missesinlibrarycacheduringparse:
1
Optimizermode:
ALL_ROWS
Parsinguserid:
5
RowsRowSourceOperation
----------------------------------------------------------
1PXCOORDINATOR（cr=3pr=0pw=0time=0us）
0PXSENDQC（ORDER）:
TQ10001（cr=0pr=0pw=0time=0uscost=3size=68card=1）
0SORTORDERBY（cr=0pr=0pw=0time=0uscost=3size=68card=1）
0PXRECEIVE（cr=0pr=0pw=0time=0us）
0PXSENDRANGE:
TQ10000（cr=0pr=0pw=0time=0us）
0NESTEDLOOPS（cr=0pr=0pw=0time=0us）
0NESTEDLOOPS（cr=0pr=0pw=0time=0uscost=2size=68card=1）
0NESTEDLOOPS（cr=0pr=0pw=0time=0uscost=2size=19card=1）
0PXBLOCKITERATOR（cr=0pr=0pw=0time=0us）
0TABLEACCESSFULLOPERACCOUNT（cr=0pr=0pw=0time=0uscost=2size=11card=1）
0INDEXFULLSCANOPERDLLNAMESINDEX（cr=0pr=0pw=0time=0uscost=1size=16card=2）（objectid73471）
0INDEXUNIQUESCANPK_DLLNAMEID（cr=0pr=0pw=0time=0uscost=0size=0card=1）（objectid73465）
0TABLEACCESSBYINDEXROWIDDLLNAMES（cr=0pr=0pw=0time=0uscost=1size=49card=1）
Elapsedtimesincludewaitingonfollowingevents:
EventwaitedonTimesMax.WaitTotalWaited
----------------------------------------Waited----------------------
rdbmsipcreply20.000.00
osthreadstartup960.1510.46
PXDeq:
JoinACK780.250.39
latchfree100.000.01
latch:
parallelqueryallocbuffer10.000.00
PXDeq:
ParseReply660.040.21
SQL*Netmessagetoclient20.000.00
PXDeq:
ExecuteReply1320.010.15
PXDeqCredit:
sendblkd151.982.03
SQL*Netmessagefromclient20.000.00
PXDeq:
SignalACKRSG700.000.01
latch:
callallocation40.000.01
PXDeq:
SlaveSessionStats20.000.00
enq:
PS-contention20.000.00
********************************************************************************
Elapsedtimesincludewaitingonfollowingevents:
EventwaitedonTimesMax.WaitTotalWaited
----------------------------------------Waited----------------------
SQL*Netmessagetoclient30.000.00
SQL*Netmessagefromclient359.9059.90
rdbmsipcreply20.000.00
osthreadstartup960.1510.46
PXDeq:
JoinACK780.250.39
latchfree100.000.01
latch:
parallelqueryallocbuffer10.000.00
PXDeq:
ParseReply660.040.21
PXDeq:
ExecuteReply1320.010.15
PXDeqCredit:
sendblkd151.982.03
PXDeq:
SignalACKRSG700.000.01
latch:
callallocation40.000.01
PXDeq:
SlaveSessionStats20.000.00
enq:
PS-contention20.000.00
FileName
----------------
mytestas1_ora_4262.trc.output
FileComment
----------------------
Dearcustomer,
感谢您的配合。
目前来看，您问题表中遇到了并行的配置。
为了进一步诊断，请执行以下动作，并提供输出结果：
ACTIONPLAN
-----------------------
请分别在测试2：
在其中一台主机上创建基于ASM磁盘组的实例
以及
测试4：
在PC的VMware虚拟机里面单实例查询
的测试环境中执行以下动作
SQL>showparameterparallel_min_servers
SQL>selecttable_name,degreefromdba_tableswheretable_name='dllnames';
SQL>selecttable_name,degreefromdba_tableswheretable_name='operdllnames';
SQL>selecttable_name,degreefromdba_tableswheretable_name='operaccount';
并请提供以上测试2，4环境的数据库alert日志位于bdump下面
SQL>showparameterbackground_dump_dest
Thealert.logisnamedasalert_.log.
测试2实例的输出：
SQL>showparameterparallel_min_servers
NAMETYPEVALUE
-----------------------------------------------------------------------------
parallel_min_serversinteger0
SQL>selecttable_name,degreefromdba_tableswheretable_name='DLLNAMES';
TABLE_NAMEDEGREE
--------------------------------------------------
DLLNAMES1
SQL>selecttable_name,degreefromdba_tableswheretable_name='OPERDLLNAMES';
TABLE_NAMEDEGREE
--------------------------------------------------
OPERDLLNAMESDEFAULT
SQL>selecttable_name,degreefromdba_tableswheretable_name='OPERACCOUNT';
TABLE_NAMEDEGREE
--------------------------------------------------
OPERACCOUNTDEFAULT
测试4实例的输出：
SQL>showparameterparallel_min_servers
SQL>
NAMETYPEVALUE
-----------------------------------------------------------------------------
parallel_min_serversinteger0
SQL>selecttable_name,degreefromdba_tableswheretable_name='DLLNAMES'andowner='HCS2000';
TABLE_NAMEDEGREE
--------------------------------------------------
DLLNAMES1
SQL>selecttable_name,degreefromdba_tableswheretable_name='OPERDLLNAMES'andowner='HCS2000';
TABLE_NAMEDEGREE
--------------------------------------------------
OPERDLLNAMESDEFAULT
SQL>selecttable_name,degreefromdba_tableswheretable_name='OPERACCOUNT'andowner='HCS2000';
TABLE_NAMEDEGREE
--------------------------------------------------
OPERACCOUNTDEFAULT
Dearcustomer,
感谢您的更新。
从您当前的设置来看，应该很大可能与您当前RAC服务器的多颗CPU数量有关
TABLE_NAMEDEGREE
--------------------------------------------------
OPERDLLNAMESDEFAULT
您的DEGREE是默认值，该默认值的算法为
假设CPU数目为16，一般
showparameterPARALLEL_THREADS_PER_CPU
NAMETYPEVALUE
-----------------------------------------------------------------------------
parallel_threads_per_cpuinteger2
showparametercpu
NAMETYPEVALUE
-----------------------------------------------------------------------------
cpu_countinteger16
那么以下对于并行的默认值设置即为：
Threads/CPU=3（"parallel_threads_per_cpu"）
defaultDOP=（#CPU*Threads/CPU）
加入之前CPU=16,parallel_threads_per_cpu=2,
defaultDOP=3x2x16=96
================
ACTIONPLAN
------------------------
请您提供您当前两个环境的
showparameterPARALLEL_THREADS_PER_CPU
showparametercpu
或者您可以直接执行
对于单机
ALTERSYSTEMSETparallel_min_servers=96SCOPE=BOTH;
对于RAC，执行
ALTERSYSTEMSETparallel_min_servers=96SCOPE=BOTHSID='ORCL1';
ALTERSYSTEMSETparallel_min_servers=96SCOPE=BOTHSID='ORCL2';
之后重新测试您的SQL。
测试环境2：
SQL>showparametercpu
NAMETYPEVALUE
-----------------------------------------------------------------------------
cpu_countinteger24
parallel_threads_per_cpuinteger2
resource_manager_cpu_allocationinteger24
SQL>
SQL>ALTERSYSTEMSETparallel_min_servers=144SCOPE=BOTH;
ALTERSYSTEMSETparallel_min_servers=144SCOPE=BOTH
*
ERRORatline1:
ORA-02097:
parametercannotbemodifiedbecausespecifiedvalueisinvalid
ORA-12811:
PARALLEL_MIN_SERVERSmustbelessthanorequalto
PARALLEL_MAX_SERVERS,135
SQL>showparameterparallel_min_servers
NAMETYPEVALUE
-----------------------------------------------------------------------------
parallel_min_serve
升级会员 