Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Why producing multiple versions of execution plan for this SQL?

Quanwen ZhaoJun 28 2018 — edited Sep 12 2018

Hello, experts

Today I found a SQL is running and consuming a lot of CPU resource on my Oracle Database Server 11.2.0.4.0 for Linux x86_64. Next I observed some historical ASH data.

The following are my 4 number of screenshots,

1.png

2.png

3.png

4.png

At the same time I had also queried the view "v$active_session_history",

set linesize 200

set pagesize 200

col event for a40

select *

from

( select sql_id

    , event

    , count(\*)

from v$active_session_history

where sample_time between to_date('2018-06-28 06:00:00','yyyy-mm-dd hh24:mi:ss')

and to_date('2018-06-28 11:10:00','yyyy-mm-dd hh24:mi:ss')

group by sql_id

       , event

order by count(*) desc

       , event

       , sql\_id

)

where rownum <= 20

;

SQL_ID EVENT COUNT(*)

-------------------------- ---------------------------------------- ----------

bg8pk89nc71x1 97131 <<==

g72nq3v9j149v 43149

70u5bvgsqwpgq 13501

                       log file sync                                 11754

                       db file async I/O submit                       7057

04ugnv5psmjju 5023

496q1h8vgnt7r 3962

dh7prjfsdmg6a 3467

                                                                      3181

                       log file parallel write                        2909

3k649st25y0b1 1932

67g5x47y9xv0s 1505

0mbyvszuyd6s2 1383

ad3zd5kf54j6p 1294

2y820c9w3vrsh 1291

520mkxqpf15q8 1238

dkx64pv90vtx9 1079

dktwkccxpm7u4 778

d8z4mmrtbj6aq 741

7kmr0ddt2pd0h 684

20 rows selected.

Afterwards I used SQLT tool to export this SQL_ID ("bg8pk89nc71x1")'s SQLT reports, there are some screenshots as follows,

(1) SQLT main page,

5.png

(2) when I click "STA Report" on the part of "SQL Tuning Advisor" based on the first picture above, then appear the second picture below,

6.png

(3) according to the first picture on SQLT, I click the "Performance History (total)" on the part of "Plans", then show the third picture as follows,

7.png

(4) when I click the "Execution Plans" on the part of "Plans", then present the fourth screenshot below,

8.png

(5) based on the fourth picture above, I click the hyper link for the first plan hash value - "293581336 [****W]", then jump to the fifth image as follows,

9.png

Next, how to continuously analyze this issue?

Please giving me some good advice, very appreciate you if any help.

Best Regards

Quanwen Zhao

This post has been answered by AndrewSayer on Aug 8 2018
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 6 2018
Added on Jun 28 2018
11 comments
1,486 views