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

Efficient SQL (an example of functional index optimization VIEW)

Original Oracle author: lovehewenyu time: 2016-07-12 14:57:43 242

Efficient SQL (an example of functional index optimization VIEW)

The business staff reported that the execution of the system was extremely slow, and looking at the system resources, they found that the CPU load was close to 100%. The challenge of CASE is coming, very excited. Ha ha ha.

1. When you encounter performance problems, we first analyze the system resources and find that the CPU load lasts about 100%. 11.2.0.4 2 nodes RAC architecture with high CPU load on each node

System: bmcdb1 Tue Jun 28 17:17:06 2016

Load averages: 21.06, 17.79, 13.17

687 processes: 417 sleeping, 270 running

Cpu states:

CPU LOAD USER NICE SYS IDLE BLOCK SWAIT INTR SSYS

0 20.37 95.8% 0.02% 4.2% 0.02% 0.00% 0.00% 0.00% 0.02%

2 22.15 95.8% 0. 0% 4.2% 0. 0% 0. 0% 0. 0% 0. 0% 0.

4 20.79 90.3% 0.07% 9.7% 0.07% 0.07% 0.07% 0.07% 0.07% 0.07% 0.07% 0.07% 0.07% 0.07% 0.07% 0.07% 0.07% 0.07% 0.07% 0.07% 0.07% 0.07% 0.07% 0.07% 0.07% 0.07% 0.07% 0.07% 0.07% 0.07% 0.07% 0.07% 0.07% 0.07% 0.07% 0.07% 0.07% 0.07% 0.07% 0.07% 0.07% 0.07% 0.07% 0.07% 0.07% 0.07% 0.07% 0.07% 0.07% 0.07% 0.07% 0.07% 0.07% 0.07% 0.07% 0.07% 0.07% 0.07% 0.07% 0.07% 0.07% 0.07% 0.07% 0.07% 0.07% 0.07% 0.07

6 19.88 91.1% 0.09% 8.9% 0.00% 0.00% 0.00% 0.00%

8 20.54 97.0% 0.0% 3.0% 0.00% 0.00% 0.00% 0.00%

10 21.11 98.0% 0.0% 2.0% 0.00% 0.00% 0.00% 0.00%

12 19.15 99.0% 0.0% 1.0% 0.00% 0.00% 0.00% 0.00%

14 24.51 95.7% 0.03% 4.3% 0.03% 0.07% 0.07% 0.07% 0.03% 0.03% 0.07% 0.07% 0.03% 0.03% 0.03% 0.03% 0.03% 0.07% 0.03% 0.03% 0.03% 0.03% 0.03% 0.03% 0.03% 0.03% 0.03% 0.03% 0.03% 0.03% 0.03% 0.03% 0.03% 0.03% 0.03% 0.03% 0.03% 0.03% 0.03% 0.03% 0.07% 0.07% 0.07% 0.03% 0.03% 0.07% 0.07% 0.07% 0.07% 0.03% 0.03% 0.03% 0.07% 0.03% 0.03% 0.03% 0.03% 0.03% 0.03% 0.03% 0.03% 0.03% 0.03% 0.03% 0.03% 0.03% 0.03%

Avg 21.06 95.4% 0. 0% 4. 6% 0. 0% 0. 0% 0. 0% 0. 0%

System: bmcdb2 Tue Jun 28 17:17:26 2016

Load averages: 22.63, 18.72, 13.23

695 processes: 450 sleeping, 244 running, 1 zombie

Cpu states:

CPU LOAD USER NICE SYS IDLE BLOCK SWAIT INTR SSYS

0 21.69 98.2% 0.0% 1.8% 0.08% 0.00% 0.00% 0.00% 0.00%

2 21.87 99.0% 0. 0% 1.0% 0. 0% 0. 0% 0. 0%

4 23.55 96.2% 0.08% 0.08% 0.02% 0.02% 0.02% 0.08% 0.08% 0.02% 0.02% 0.08% 0.08% 0.02% 0.08% 0.08% 0.02% 0.02% 0.02% 3.8% 0.08% 0.02% 0.02% 0.08% 0.08% 0.02% 0.02% 0.08% 0.08% 0.02% 0.02% 0.08% 0.08% 0.02% 0.08% 0.08% 0.02% 0.08% 0.08% 0.02% 0.08% 0.08% 0.02% 0.08% 0.08% 0.08% 0.08% 0.08% 0.08% 0.08% 0.02% 0.08% 0.08% 0.08% 0.02% 0.08% 0.08% 0.08% 0.02% 0.02% 0.08% 0.08% 0.08% 0.08% 0.08% 0.08% 0.08

6 22.04 98.0% 0.00% 2.0% 0.00% 0.00% 0.00% 0.00%

8 21.89 94.9% 0.01% 5.1% 0.01% 0.00% 0.00% 0.00% 0.02%

10 22.55 97.8% 0.02% 2.2% 0.00% 0.00% 0.00% 0.00%

12 24.17 96.0% 0.0% 4.0% 0.00% 0.00% 0.00% 0.00%

14 23.27 96.4% 0.0% 3.6% 0.06% 0.00% 0.00% 0.00% 0.00%

Avg 22.63 97.0% 0.0% 3.0% 0.00% 0.00% 0.00% 0.00%

two。 Analyze AWR report

Node 1

Snap Id Snap Time Sessions Cursors/Session Instances

Begin Snap: 17049 28-Jun-16 15:00:15 241 6.7 2

End Snap: 17050 28-Jun-16 16:00:19 282 6.2 2

Elapsed: 60.08 (mins)

DB Time: 1710.37 (mins)

Top 10 Foreground Events by Total Wait Time

Event Waits Total Wait Time (sec) Wait Avg (ms)% DB time Wait Class

DB CPU 24.2K 23.6

Latch: cache buffers chains 54799 9199.7 168 9.0 Concurrency

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.