Dbms_report.get_report() Performance Very Slow as Non-sys User (Doc ID 2467173.1)

Last updated on APRIL 17, 2023

Applies to:

Symptoms

• As a non-sys user,Oracle Cloud Control SQL Monitoring Page is very slow, appearing to be a hang
• As a sys(or system) user,Oracle Cloud Control SQL Monitoring Page is normal not slow
• The slow operations are dbms_report.get_report:
  

  select dbms_report.get_report('/orarep/sqlmonitor/main?report_level=<report_level>&instId=1&sql_id=<sql_id>&sw2con_id=<con_id>&sql_exec_start=<sql_exec_start>&session_serial=<session_serial>&inst_id=<inst_id>&session_id=<session_id>&sql_exec_id=<sql_exec_id>') from dual;

  select dbms_report.get_report('/orarep/sqlmonitor/list?instId=<inst_id>&top_n_count=<top_n_count>&max_sqltext_length=<max_sqltext_length>&inst_id=<inst_id>&top_n_rankby=last_active_time') from dual;

• From SQL trace, FIXED TABLE FULL X$ASH of the Recursive SQL(SQL ID: 8mdz49zkajhw3) costs most of time:
  

  Example:

  ...

   NESTED LOOPS  (cr=0 pr=0 pw=0 time=1 us cost=13 size=287 card=1)

   VIEW  X$KEWASH (cr=0 pr=0 pw=0 time=1 us cost=0 size=27680 card=1384)

    FIXED TABLE FULL X$KEWASH (cr=0 pr=0 pw=0 time=1 us cost=0 size=30448 card=1384)

   VIEW  X$ASH (cr=0 pr=0 pw=0 time=4337 us cost=0 size=267 card=1)

    FIXED TABLE FULL X$ASH (cr=0 pr=0 pw=0 time=4337 us cost=0 size=91 card=1)

  ...

• EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS not fixed the issue

Changes

 None

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.