After trialling EM Express 12.2 for use on a team dashboard screen, my monitoring picked up a session burning a lot of CPU cycles overnight.

A shared server session was running a single execution of:

SELECT COUNT(*) FROM DBA_ADVISOR_OBJECTS, DBA_ADVISOR_OBJECTS;

DBA_ADVISOR_OBJECTS had 2.5 million records in it, so it isn’t surprising that it ran all night without completing.

Execution plan:

 

  2 - ACCESS: "A"."TASK_ID"="B"."ID"
  4 - ACCESS: "D"."INDX"="A"."TYPE"
  7 - ACCESS: "A"."TASK_ID"="B"."ID"
  9 - ACCESS: "D"."INDX"="A"."TYPE"
OPTIMIZER: ALL_ROWS
| Operation                                                            | Name                            |  Rows | Bytes|  Cost  | I/O|PQ Dist| Pstart| Pstop | Est Secs
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|  0 SELECT STATEMENT                                                  |                                 |       |      |     15M|    |       |       |       |
|  1  SORT AGGREGATE                                                   |                                 |     1 |   28 |        |    |       |       |       |
|  2   HASH JOIN                                                       |                                 |  4778G|124602|     15M|    |       |       |       |      606
|  3    INDEX FAST FULL SCAN                                           |WRI$_ADV_TASKS_IDX_02            |   741 |    2K|      2 |    |       |       |       |        1
|  4    HASH JOIN                                                      |                                 |  4778G|106802|7785244 |    |       |       |       |      305
|  5     TABLE ACCESS FULL                                             |WRI$_ADV_OBJECTS                 |     2M|   14M|   5561 |    |       |       |       |        1
|  6     MERGE JOIN CARTESIAN                                          |                                 |    56M|  921M|   8786 |    |       |       |       |        1
|  7      HASH JOIN                                                    |                                 |     2M|   29M|   5570 |    |       |       |       |        1
|  8       INDEX FAST FULL SCAN                                        |WRI$_ADV_TASKS_IDX_02            |   741 |    2K|      2 |    |       |       |       |        1
|  9       HASH JOIN                                                   |                                 |     2M|   20M|   5565 |    |       |       |       |        1
| 10        FIXED TABLE FULL                                           |X$KEAOBJT                        |    26 |   78 |      0 |    |       |       |       |
| 11        TABLE ACCESS FULL                                          |WRI$_ADV_OBJECTS                 |     2M|   14M|   5561 |    |       |       |       |        1
| 12      BUFFER SORT                                                  |                                 |    26 |   78 |   8784 |    |       |       |       |        1
| 13       FIXED TABLE FULL                                            |X$KEAOBJT                        |    26 |   78 |      0 |    |       |       |       |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------

I tracked it down to line 417 in WRI$_REPT_SQLPI :

  413       WHEN REPORT_NAME = PRVT_SQLPA.REPT_NAME_DEFAULTQCPARAMS THEN
  414 
  415         
  416         DBMS_ADVISOR.CHECK_PRIVS;
  417         SELECT COUNT(*) INTO OBJ_ID FROM DBA_ADVISOR_OBJECTS, DBA_ADVISOR_OBJECTS; 
  418         
  419         REPORT_XML := PRVTEMX_SQL.REPORT_QUICK_CHECK_DFLTS_XML;
  420

Which may be called by:

begin :rept := dbms_report.get_report(:report_ref, :content, :comp); end;

 

Following the Oracle documentation below seems to trigger this issue:

On any page in EM Express, from the Performance menu, select SQL Performance Analyzer.
The SQL Performance Analyzer page appears.
On the Quick Check Tasks tab, click Quick Check Default Setup.
The SPA Quick Check Default Values setup dialog box appears.

I have logged a service request with Oracle Support.

 

Update 1

Looks like growth in SYS.WRI$_ADV_OBJECTS is due to the new 12.2  AUTO_STATS_ADVISOR_TASK.

SQL> col task_name format a30
SQL> select TASK_NAME, count(1) from DBA_ADVISOR_OBJECTS group by TASK_NAME order by 2 desc;

TASK_NAME                        COUNT(1)
------------------------------ ----------
AUTO_STATS_ADVISOR_TASK           2545756
SYS_AUTO_SQL_TUNING_TASK               27
ADDM:1001930401_1_6370                  5
ADDM:1001930401_1_6922                  5
ADDM:1001930401_1_6330                  4
ADDM:1001930401_1_6322                  4
ADDM:1001930401_1_6250                  4

 

AUTO_STATS_ADVISOR_TASK can be disabled until the bug is fixed:

DECLARE  filter1 CLOB;  BEGIN  filter1 := DBMS_STATS.CONFIGURE_ADVISOR_OBJ_FILTER(  task_name => 'AUTO_STATS_ADVISOR_TASK',  stats_adv_opr_type => 'EXECUTE',  rule_name => NULL,  ownname => NULL,  tabname => NULL,  action => 'DISABLE' );  END;  /

Then remove the spam:

delete from wri$_adv_objects where task_id in (select task_id from dba_advisor_objects where task_name='AUTO_STATS_ADVISOR_TASK');
commit;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s