OracleRAC11gr2性能调优解决查询慢问题教学内容.docx
- 文档编号:28519710
- 上传时间:2023-07-18
- 格式:DOCX
- 页数:13
- 大小:44.50KB
OracleRAC11gr2性能调优解决查询慢问题教学内容.docx
《OracleRAC11gr2性能调优解决查询慢问题教学内容.docx》由会员分享,可在线阅读,更多相关《OracleRAC11gr2性能调优解决查询慢问题教学内容.docx(13页珍藏版)》请在冰豆网上搜索。
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中的查询太慢了,请问怎么查看问题原因,如何调优?
Dearcustomer,
的说明后,作为附件更新到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>vRunyourSQLhere;>
SQL>altersessionsetevents'10046tracenamecontextoff;
2.Formatyour10046tracefile:
$tkprof
例如
生成的文件应该是在您的udump路径下面。
寻找UDUM路径,请参考
SQL>showparameteruser_dump_dest
之后,format您的文件
$cd/u01/OracleAPP/oracle/admin/R1020/udump
$ls-ltr
$tkprofr1020_ora_9638.trc9638.output
3.
请提交您10046trace以及tkprof输出文件9638.output
Dearcustomer,
目前来看,您问题表中遇到了并行的配置。
为了进一步诊断,请执行以下动作,并提供输出结果:
ACTIONPLAN
请分别在测试2:
在其中一台主机上创建基于ASM磁盘组的实例以及
测试4:
在PC的VMware虚拟机里面单实例查询的测试环境中执行以下动作
SQL>showparameterparallel』in_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_
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.240001total40.0513.390301
Missesinlibrarycacheduringparse:
1
Optimizermode:
ALL_ROWS
Parsinguserid:
5
RowsRowSourceOperation1PXCOORDINATOR(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)
0TABLEACCESSFULLOPERACCOU(NcrT=0pr=0pw=0time=0uscost=2size=11card=1)
0INDEXFULLSCANOPERDLLNAMESINDEX(cr=0pr=0pw=0time=0uscost=1size=16card=2)(objectid73471)
0INDEXUNIQUESCANPK_DLLNAMEI(Dcr=0pr=0pw=0time=0uscost=0size=0card=1)(objectid73465)
0TABLEACCESSBYINDEXROWIDDLLNAMES(cr=0pr=0pw=0time=0uscost=1size=49card=1)
Elapsedtimesincludewaitingonfollowingevents:
EventwaitedonTimesMax.WaitTotalWaitedWaitedrdbmsipcreply20.000.00osthreadstartup960.1510.46
PXDeq:
JoinACK780.250.39latchfree100.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.01latch:
callallocation40.000.01
PXDeq:
SlaveSessionStats20.000.00enq:
PS-contention20.000.00
*********************************************************************
***********
Elapsedtimesineludewaitingonfollowingevents:
EventwaitedonTimesMax.WaitTotalWaited
Waited
SQL*Netmessagetoclient30.000.00
SQL*Netmessagefromclient359.9059.90rdbmsipcreply20.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.01latch:
callallocation40.000.01
PXDeq:
SlaveSessionStats20.000.00enq:
PS-contention20.000.00
FileNamemytestas1_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_
测试2实例的输出:
SQL>showparameterparallel_min_servers
NAMETYPEVALUEparallel_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』in_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服务器的多颗CPL数量有关
TABLE_NAMEDEGREE
OPERDLLNAMESDEFAULT
您的DEGREE!
默认值,该默认值的算法为假设CPU数目为16,—般
showparameterPARALLEL_THREADS_PER_CPUNAMETYPEVALUE
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_cpuinteger2resource_manager_cpu_allocationinteger24SQL>
SQL>ALTERSYSTEMSETparallel_min_servers=144SCOPE=BOTH;
ALTERSYSTEMSETparallel_min_servers=144SCOPE=BOTH
*
ERRORatline1:
ORA-02097:
parametercannotbemodifiedbecausespecifiedvalueisinvalid
ORA-12811:
PARALLEL_MIN_SERVERSmustbelessthanorequaltoPARALLEL_MAX_SERVERS,135
SQL>showparameterparallel_min_servers
NAMETYPEVALUE
parallel_min_serversinteger0
SQL>showparameterPARALLEL_MAX_SERVERS
NAMETYPEVALUE
parallel_max_serversinteger135
SQL>ALTERSYSTEMSETparallel_min_servers=135SCOPE=BOTH;Systemaltered.
SQL>showparameterparallel_min_servers
NAMETYPEVALUE
parallel_min_serversinteger135
设置完之后,连续测试5次,分别用时3.7s,3.7s,0.4s,0.4s,0.7s过5分钟再测,用时3.4s。
还是比较慢。
测试环境4:
SQL>showparametercpu
NAMETYPEVALUEcpu_countinteger1
parallel_threads_per_cpuinteger2
测试环境2:
SQL>showparametercpu
NAMETYPEVALUEcpu_countinteger24
parallel_threads_per_cpuinteger2
resource_manager_cpu_allocationinteger24
SQL>
SQL>ALTERSYSTEMSETparallel_min_servers=144SCOPE=BOTH;
ALTERSYSTEMSETparallel_min_servers=144SCOPE=BOTH
*
ERRORatline1:
ORA-O2O97:
parametercannotbemodifiedbecausespecified
valueisinvalid
ORA-12811:
PARALLEL_MIN_SERVERSmustbelessthanorequalto
PARALLEL_MAX_SERVERS,135
SQL>showparameterparallel_min_servers
NAMETYPEVALUEparallel_min_serversinteger0
SQL>showparameterPARALLEL_MAX_SERVERS
NAMETYPEVALUEparallel_max_serversinteger135
SQL>ALTERSYSTEMSETparallel_min_servers=135SCOPE=BOTH;
Systemaltered.
SQL>showparameterparallel』in_servers
NAMETYPEVALUE
parallel』in_serversinteger135
设置完之后,连续测试5次,分别用时3.7s,3.7s,0.4s,0.4s,0.7s
过5分钟再测,用时3.4s。
还是比较慢。
测试环境4:
SQL>showparametercpu
NAMETYPEVALUEcpu_countinteger1
parallel_threads_per_cpuinteger2
Dearcustomer,
我们从您提供的信息中发现,您的比较是基于91的单机环境,是没有使用并行的。
如果您的业务都是基于9I单机开发,建议您将parallel_max_servers设置为0之后再次测试
SQL>ALTERSYSTEMSETparallel_min_servers=0SCOPE=BOTH;
ALTERSYSTEMSETparallel_max_servers=0SCOPE=BOTH;
之后,请将您新测试的10046结果更新到SR上。
已经执行了
ALTERSYSTEMSETparallel_min_servers=0SCOPE=BOTH;
ALTERSYSTEMSETparallel_max_servers=0SCOPE=BOTH;
再次测试,查询用时为0.01秒,可以接受。
trace文件就不上传了。
请问parallel_max_servers设置为0后,系统的24个CPL是不是
同时只能有1个们该查询操作(而且只有一个线程)?
抛开应用其它部分,单就这个select语句而言,如何修改该select语句或做其它设置,从而充分利用多个cpu多线程查询(如果表中数
据很多的话,肯定是多个cpu并行查询速度更快)?
Dearcustomer,
感谢您的配合。
目前从您应用的等待来看,您是遇到了并发高的负影响您的SQL在不启用并发的情况下应该会执行的很好。
如果您希望在打开并发设置前提下,单独调整问题表,您可以在问题表上执行
打开并发
ALTERSYSTEMSETparallel_min_servers=96SCOPE=BOTH;
ALTERSYSTEMSETparallel_max_servers=135SCOPE=BOTH;
ALTERTABLEdllname
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- OracleRAC11gr2 性能 解决 查询 问题 教学内容