性能調(diào)優(yōu)解決查詢慢問(wèn)題

1、知也無(wú)涯Oracle RAC 11g r2查詢太慢-Oracle RAC 11g r2查詢太慢Problem Description-Redhat 5 雙機(jī)測(cè)試1：雙實(shí)例，ASM磁盤(pán)組包含3個(gè)磁盤(pán)（SAN）。在其中一個(gè)實(shí)例中執(zhí)行：SELECT c.operaccount | : | c.PASSWORD | | a.PATH,a.dll, a.description, 1.gifFROM hcs2000.dllnames a, hcs2000.operdllnames b, hcs2000.operaccount cWHERE a.dllnameid = b.dllnameidAND b.op

2、erid = c.operidAND upper(c.operaccount) = USERORDER BY a.dllnameid;第一次查詢，25秒。第二次查詢，3秒。第三次查詢，1.6秒。過(guò)10分鐘后查詢，26秒。測(cè)試2：在其中一臺(tái)主機(jī)上創(chuàng)建基于ASM磁盤(pán)組的單個(gè)實(shí)例，第一次查詢，14秒。第二次查詢，3秒。第三次查詢，0.7秒。第四次查詢，3.5秒。測(cè)試3：在其中一臺(tái)主機(jī)上創(chuàng)建基于文件系統(tǒng)的單個(gè)實(shí)例，第一次查詢，5秒。第二次查詢，2.2秒。第三次查詢，2.1秒。測(cè)試4：在PC的VMware虛擬機(jī)里面單實(shí)例查詢，只需0.001秒或0秒。測(cè)試1中的查詢太慢了，請(qǐng)問(wèn)怎么查看問(wèn)題原因，如何調(diào)優(yōu)？

3、Dear customer,請(qǐng)您執(zhí)行以下動(dòng)作：如果可以，請(qǐng)?jiān)谀岬降?個(gè)場(chǎng)景下都生成以下文件，并請(qǐng)?zhí)砑幽恼f(shuō)明后，作為附件更新到SR上：ACTION PLAN-1. Please generate 10046 trace for your sql:SQLconnect username/password SQLalter session set timed_statistics = true;SQLalter session set statistics_level=all;SQLalter session set max_dump_file_size = unlimited;SQLalter

4、 session set events 10046 trace name context forever, level 12;SQL SQLalter session set events 10046 trace name context off;2.Format your 10046 trace file:$tkprof 例如生成的文件應(yīng)該是在您的udump路徑下面。尋找UDUMP路徑，請(qǐng)參考 SQL show parameter user_dump_dest 之后，format您的文件$cd /u01/OracleAPP/oracle/admin/R1020/udump$ls -ltr$t

5、kprof r1020_ora_9638.trc 9638.output3.請(qǐng)?zhí)峤荒?10046 trace 以及 tkprof 輸出文件9638.output Dear customer,目前來(lái)看，您問(wèn)題表中遇到了并行的配置。為了進(jìn)一步診斷，請(qǐng)執(zhí)行以下動(dòng)作，并提供輸出結(jié)果：ACTION PLAN-請(qǐng)分別在測(cè)試2：在其中一臺(tái)主機(jī)上創(chuàng)建基于ASM磁盤(pán)組的實(shí)例以及測(cè)試4：在PC的VMware虛擬機(jī)里面單實(shí)例查詢的測(cè)試環(huán)境中執(zhí)行以下動(dòng)作SQL show parameter parallel_min_serversSQL select table_name,degree from dba_tables

6、 where table_name=dllnames;SQL select table_name,degree from dba_tables where table_name=operdllnames;SQL select table_name,degree from dba_tables where table_name=operaccount;并請(qǐng)?zhí)峁┮陨蠝y(cè)試2， 4環(huán)境的數(shù)據(jù)庫(kù)alert 日志 位于bdump下面SQL show parameter background_dump_destThe alert.log is named as alert_.log.Name-= ODM D

7、ata Collection =SELECT c.operaccount | : | c.PASSWORD | | a.PATH,a.dll, a.description, 1.gifFROM dllnames a, operdllnames b, operaccount cWHERE a.dllnameid = b.dllnameidAND b.operid = c.operidAND upper(c.operaccount) = USERORDER BY a.dllnameidcall count cpu elapsed disk query current rows- - - - - -

8、 - -Parse 1 0.00 0.00 0 0 0 0Execute 1 0.01 11.14 0 3 0 0Fetch 2 0.03 2.24 0 0 0 1- - - - - - - -total 4 0.05 13.39 0 3 0 1Misses in library cache during parse: 1Optimizer mode: ALL_ROWSParsing user id: 5 Rows Row Source Operation- -1 PX COORDINATOR (cr=3 pr=0 pw=0 time=0 us)0 PX SEND QC (ORDER) :TQ

9、10001 (cr=0 pr=0 pw=0 time=0 us cost=3 size=68 card=1)0 SORT ORDER BY (cr=0 pr=0 pw=0 time=0 us cost=3 size=68 card=1)0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us)0 PX SEND RANGE :TQ10000 (cr=0 pr=0 pw=0 time=0 us)0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us)0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=2 siz

10、e=68 card=1)0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=2 size=19 card=1)0 PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us)0 TABLE ACCESS FULL OPERACCOUNT (cr=0 pr=0 pw=0 time=0 us cost=2 size=11 card=1)0 INDEX FULL SCAN OPERDLLNAMESINDEX (cr=0 pr=0 pw=0 time=0 us cost=1 size=16 card=2)(object id 7347

11、1)0 INDEX UNIQUE SCAN PK_DLLNAMEID (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 73465)0 TABLE ACCESS BY INDEX ROWID DLLNAMES (cr=0 pr=0 pw=0 time=0 us cost=1 size=49 card=1)Elapsed times include waiting on following events:Event waited on Times Max. Wait Total Waited- Waited - -rdbms ip

12、c reply 2 0.00 0.00os thread startup 96 0.15 10.46PX Deq: Join ACK 78 0.25 0.39latch free 10 0.00 0.01latch: parallel query alloc buffer 1 0.00 0.00PX Deq: Parse Reply 66 0.04 0.21SQL*Net message to client 2 0.00 0.00PX Deq: Execute Reply 132 0.01 0.15PX Deq Credit: send blkd 15 1.98 2.03SQL*Net mes

13、sage from client 2 0.00 0.00PX Deq: Signal ACK RSG 70 0.00 0.01latch: call allocation 4 0.00 0.01PX Deq: Slave Session Stats 2 0.00 0.00enq: PS - contention 2 0.00 0.00*Elapsed times include waiting on following events:Event waited on Times Max. Wait Total Waited- Waited - -SQL*Net message to client

14、 3 0.00 0.00SQL*Net message from client 3 59.90 59.90rdbms ipc reply 2 0.00 0.00os thread startup 96 0.15 10.46PX Deq: Join ACK 78 0.25 0.39latch free 10 0.00 0.01latch: parallel query alloc buffer 1 0.00 0.00PX Deq: Parse Reply 66 0.04 0.21PX Deq: Execute Reply 132 0.01 0.15PX Deq Credit: send blkd

15、 15 1.98 2.03PX Deq: Signal ACK RSG 70 0.00 0.01latch: call allocation 4 0.00 0.01PX Deq: Slave Session Stats 2 0.00 0.00enq: PS - contention 2 0.00 0.00FileName-mytestas1_ora_4262.trc.outputFileComment-Dear customer,感謝您的配合。目前來(lái)看，您問(wèn)題表中遇到了并行的配置。為了進(jìn)一步診斷，請(qǐng)執(zhí)行以下動(dòng)作，并提供輸出結(jié)果：ACTION PLAN-請(qǐng)分別在測(cè)試2：在其中一臺(tái)主機(jī)上創(chuàng)建基于A

16、SM磁盤(pán)組的實(shí)例以及測(cè)試4：在PC的VMware虛擬機(jī)里面單實(shí)例查詢的測(cè)試環(huán)境中執(zhí)行以下動(dòng)作SQL show parameter parallel_min_serversSQL select table_name,degree from dba_tables where table_name=dllnames;SQL select table_name,degree from dba_tables where table_name=operdllnames;SQL select table_name,degree from dba_tables where table_name=operacc

17、ount;并請(qǐng)?zhí)峁┮陨蠝y(cè)試2， 4環(huán)境的數(shù)據(jù)庫(kù)alert 日志 位于bdump下面SQL show parameter background_dump_destThe alert.log is named as alert_.log.測(cè)試2實(shí)例的輸出：SQL show parameter parallel_min_serversNAME TYPE VALUE- - -parallel_min_servers integer 0SQL select table_name,degree from dba_tables where table_name=DLLNAMES;TABLE_NAME DEG

18、REE- -DLLNAMES 1SQL select table_name,degree from dba_tables where table_name=OPERDLLNAMES;TABLE_NAME DEGREE- -OPERDLLNAMES DEFAULTSQL select table_name,degree from dba_tables where table_name=OPERACCOUNT;TABLE_NAME DEGREE- -OPERACCOUNT DEFAULT測(cè)試4實(shí)例的輸出：SQL show parameter parallel_min_serversSQLNAME

19、TYPE VALUE- - -parallel_min_servers integer 0SQL select table_name,degree from dba_tables where table_name=DLLNAMES and owner=HCS2000;TABLE_NAME DEGREE- -DLLNAMES 1SQL select table_name,degree from dba_tables where table_name=OPERDLLNAMES and owner=HCS2000;TABLE_NAME DEGREE- -OPERDLLNAMES DEFAULTSQL

20、 select table_name,degree from dba_tables where table_name=OPERACCOUNT and owner=HCS2000;TABLE_NAME DEGREE- -OPERACCOUNT DEFAULTDear customer,感謝您的更新。從您當(dāng)前的設(shè)置來(lái)看，應(yīng)該很大可能與您當(dāng)前RAC 服務(wù)器的多顆CPU數(shù)量有關(guān)TABLE_NAME DEGREE- -OPERDLLNAMES DEFAULT您的DEGREE 是默認(rèn)值，該默認(rèn)值的算法為假設(shè) CPU 數(shù)目為16，一般show parameter PARALLEL_THREADS_PER_C

21、PUNAME TYPE VALUE- - -parallel_threads_per_cpu integer 2show parameter cpuNAME TYPE VALUE- - -cpu_count integer 16那么 以下對(duì)于并行的默認(rèn)值設(shè)置即為：Threads/CPU = 3 (parallel_threads_per_cpu) default DOP = (# CPU * Threads/CPU)加入之前CPU=16, parallel_threads_per_cpu =2, default DOP =3x 2 x 16 = 96=ACTION PLAN-請(qǐng)您提供您當(dāng)前兩個(gè)

22、環(huán)境的show parameter PARALLEL_THREADS_PER_CPUshow parameter cpu或者您可以直接執(zhí)行對(duì)于單機(jī)ALTER SYSTEM SET parallel_min_servers = 96 SCOPE=BOTH;對(duì)于RAC，執(zhí)行ALTER SYSTEM SET parallel_min_servers = 96 SCOPE=BOTH SID=ORCL1;ALTER SYSTEM SET parallel_min_servers = 96 SCOPE=BOTH SID=ORCL2;之后重新測(cè)試您的SQL。測(cè)試環(huán)境2：SQL show parameter

23、cpuNAME TYPE VALUE- - -cpu_count integer 24parallel_threads_per_cpu integer 2resource_manager_cpu_allocation integer 24SQL SQL ALTER SYSTEM SET parallel_min_servers = 144 SCOPE=BOTH;ALTER SYSTEM SET parallel_min_servers = 144 SCOPE=BOTH*ERROR at line 1:ORA-02097: parameter cannot be modified because

24、 specified value is invalidORA-12811: PARALLEL_MIN_SERVERS must be less than or equal toPARALLEL_MAX_SERVERS, 135SQL show parameter parallel_min_serversNAME TYPE VALUE- - -parallel_min_servers integer 0SQL show parameter PARALLEL_MAX_SERVERSNAME TYPE VALUE- - -parallel_max_servers integer 135SQL ALT

25、ER SYSTEM SET parallel_min_servers = 135 SCOPE=BOTH;System altered.SQL show parameter parallel_min_serversNAME TYPE VALUE- - -parallel_min_servers integer 135設(shè)置完之后，連續(xù)測(cè)試5次，分別用時(shí)3.7s，3.7s，0.4s, 0.4s, 0.7s。過(guò)5分鐘再測(cè)，用時(shí)3.4s。還是比較慢。測(cè)試環(huán)境4：SQL show parameter cpuNAME TYPE VALUE- - -cpu_count integer 1parallel_th

26、reads_per_cpu integer 2測(cè)試環(huán)境2：SQL show parameter cpuNAME TYPE VALUE- - -cpu_count integer 24parallel_threads_per_cpu integer 2resource_manager_cpu_allocation integer 24SQL SQL ALTER SYSTEM SET parallel_min_servers = 144 SCOPE=BOTH;ALTER SYSTEM SET parallel_min_servers = 144 SCOPE=BOTH*ERROR at line 1

27、:ORA-02097: parameter cannot be modified because specified value is invalidORA-12811: PARALLEL_MIN_SERVERS must be less than or equal toPARALLEL_MAX_SERVERS, 135SQL show parameter parallel_min_serversNAME TYPE VALUE- - -parallel_min_servers integer 0SQL show parameter PARALLEL_MAX_SERVERSNAME TYPE V

28、ALUE- - -parallel_max_servers integer 135SQL ALTER SYSTEM SET parallel_min_servers = 135 SCOPE=BOTH;System altered.SQL show parameter parallel_min_serversNAME TYPE VALUE- - -parallel_min_servers integer 135設(shè)置完之后，連續(xù)測(cè)試5次，分別用時(shí)3.7s，3.7s，0.4s, 0.4s, 0.7s。過(guò)5分鐘再測(cè)，用時(shí)3.4s。還是比較慢。測(cè)試環(huán)境4：SQL show parameter cpuNAME TYPE VALUE- - -cpu_count integer 1parallel_threads_per_cpu integer 2Dear customer,我們從您提供的信息中發(fā)現(xiàn)，您的比較是基于9I的單機(jī)環(huán)境，是沒(méi)有使用并行的。如果您的業(yè)務(wù)都是基于9I單機(jī)開(kāi)發(fā)，建議您將parallel_max_servers 設(shè)置為0 之后再次測(cè)試SQL ALTER SYSTEM SET parallel_min_servers =