Shulou(Shulou.com)06/01 Report--

The application of feedback ticket system is slow, log on to the host to query the number of LOCAL=NO and v$session, and find that the number of connections is indeed more than 50% higher than usual. Later, I consulted the business and found that this was a normal business phenomenon at the end of the month.

1. Check the SunOS operating system:

1) memory:

Click (here) to collapse or open

Root@abdit # prtconf | grep MemoryMemory size: 32768 Megabytesroot@abdit # echo ":: memstat" | mdb-kPage Summary Pages MB% Tot--Kernel 364339 2846 9%Anon 2158855 16866 52%Exec and libs 74791 2%Page cache 62215 486 2%Free (cachelist) 1391728 10872 34%Free (freelist) 65421 511 2%Total 4117349 32166Physical 4095711 31997 visible The host has 32 gigabytes of memory, 11 gigabytes free, uses 21 gigabytes, and the utilization rate is 65%.

Check that the database sga is 10G and the dgyt is 1G, so the sga configuration is too low, so it is recommended to adjust it.

2) cpu

Mpstat has 16 logical cpu

Sar-u 3 20 cpu utilization 15%

The database already uses all 16 cpu of the host.

Cpu_count integer 16

3) IO

The io of each disk viewed by iostat-xd 2 is busy, and it is initially suspected that IO is causing the database to be slow.

2), check the database (10.2.0.5 single instance of rac based on asm)

1) View the wait events of the database:

Select event#,event,count (*) from v$session group by event#,event order by 3

Click (here) to collapse or open

EVENT# EVENT COUNT (*)-150 db file parallel write 1135 log file parallel write 1199 direct path read temp 1350 SQL*Net message to client 1200 direct path write 1201 direct path write temp 2144 log file sync 10148 db file scattered read 11147 db file sequential read 1498 read by other session 205198 direct path read 342 The problems are all caused by direct path read and read by other session waiting events.

2) check the sql that caused the direct path read.

Select sql_id,username,count (*) from gv$session where event#=198 group by sql_id,username order by 3

Know that it is basically caused by the following two sql of * users: d7d83k6fzn7db and bbqffj0cd01xm

3) check that the corresponding sql content has been executed.

SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR ('bbqffj0cd0qxm',NULL))

I learned that the contents are as follows:

Click (here) to collapse or open

Select title, itemid,formId,predictFinishTime

From (select mw.title,mw.itemid,mw.itemtype as formId,ia.predictFinishTime

From ta_* tp,form_* fw,mw_workitem mw,Item_* ia

Where tp.****='waiting'

And (tp.defName='??t?? Or tp.defName='???? Or tp.defName='???)

And tp.rootinstid = fw.processid

And fw.itemid = mw.itemid

And mw.relatingRoom in ('2mm?-2mm?

Click (here) to collapse or open

-| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | |-| 0 | SELECT STATEMENT | 17302 | | | 1 | COUNT STOPKEY | | 2 | HASH JOIN | | 1 | 17302 | 17302 (1) | 00:03:28 | 3 | NESTED LOOPS | 4 | 10380 (1) | 00:02:05 | | 4 | NESTED LOOPS | 2752 | 10380 (1) | 00:02:05 | 5 | HASH JOIN | 52632 | 8660 (1) | 00:01:44 | 6 | TABLE ACCESS FULL | MW_* | 37962 | 8006 (1) | 00:01:37 | | | 7 | TABLE ACCESS FULL | FORM_* | 290K | 11m | 652 (1) | 00:00:08 | 8 | INDEX RANGE SCAN | TAI_*_ROOTID | 8 | 2 (0) | 00:00:01 | 9 | TABLE ACCESS BY INDEX ROWID | TA_* | 1 | 50 | 5 (0) | 00:00:01 | 10 | TABLE ACCESS FULL | ITEM_* | 601K | 35m | 6918 (1) | 00:01:24 |- -you can see that part of the table is stored in the full table scan. Although the amount of data in the table is small (about 170w rows), it may have a performance impact in the case of high concurrency. Therefore, consider creating an index as follows:

Click (here) to collapse or open

Create index * .idx_*_****_**** on * .Item_* (* *, * *, *) online parallel 8 tablespace TBS_*;create index * .idx_* on * .form_**** (*) online tablespace TBS_* Create index * .idx_*_* on * .MW_**** (*, *) online tablespace TBS_*;4) adjust the sga parameters:

Alter system set sga_max_target=20g scope=spfile sid='*'

Restart the library.

5) because when analyzing the problem, the business has been restored. Therefore, it is impossible to verify whether the changes are enabled, and it will be followed up to see whether the business failure will recur.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.