适用范围
ORACLE 所有版本
问题概述
海外客户ORACLE 19.11 出现了跑批性能问题，需要协助分析，但是在分析过程中，AWR，ASH均无数据。 v$active_session_history 视图也数据。
SQL> select count(*) from v$active_session_history ;
COUNT(*)
----------
0
问题原因
通过分析可能是未购买ORACLE诊断包的许可，未开启相关功能。
#查看数据库版本
SQL> select * from v$version ;
BANNER
--------------------------------------------------------------------------------
BANNER_FULL
----------------------------------------------------------------------------------------------------------------------------------------------------------------
BANNER_LEGACY CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.11.0.0.0
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production 0
#查看是否开启了ash功能
SQL> SELECT a.ksppinm "parameter",b.ksppstvl "session value",c.ksppstvl "instance value",a.KSPPDESC FROM x$ksppi a, x$ksppcv b, x$ksppsv c WHERE a.indx = b.indx AND a.indx = c.indx AND a.ksppinm LIKE '%_ash_enable%'
parameter session value instance value KSPPDESC
----------------------------------- --------------- --------------- --------------------------------------------------
_right_outer_hash_enable TRUE TRUE Right Outer/Semi/Anti Hash Enabled
_ash_enable TRUE TRUE To enable or disable Active Session sampling and f
lushing
#查看被禁用的option选件
SQL> select * from v$option where value='FALSE';
PARAMETER VALUE CON_ID
----------------------------------- ---------------------------------------------------------------- ----------
Partitioning FALSE 0
Real Application Clusters FALSE 0
Automatic Storage Management FALSE 0
Oracle Label Security FALSE 0
OLAP FALSE 0
Advanced Analytics FALSE 0
Oracle Database Vault FALSE 0
Real Application Testing FALSE 0
Unified Auditing FALSE 0
Management Database FALSE 0
I/O Server FALSE 0
ASM Proxy Instance FALSE 0
Data Mining FALSE 0
13 rows selected.
#查看是否使用过 Tuning 包
SQL> @options_packs_usage_statistics.sql
OVERALL INFORMATION
HOST_NAME |INSTANCE_NAME |DATABASE_NAME |OPEN_MODE |DATABASE_ROLE |CREATED | DBID|VERSION |BANNER
----------------------------------------|----------------|--------------|----------------|----------------|-------------------|----------|-----------|--------------------------------------------------------------------------------
hgcoracledb |orcl12c |ORCL12C |READ WRITE |PRIMARY |2022.05.12_14.01.37| 934992385|12.1.0.2.0 |Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PARAMETER |VALUE
------------------------------|--------------------
control_management_pack_access|NONE
enable_ddl_logging |FALSE
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
PRODUCT USAGE
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
PRODUCT |USAGE |LAST_SAMPLE_DATE |FIRST_USAGE_DATE |LAST_USAGE_DATE
---------------------------------------------------|------------------------|-------------------|-------------------|-------------------
Active Data Guard |NO_USAGE |2023.03.30_11.54.46| |
Advanced Analytics |NO_USAGE |2023.03.30_11.54.46| |
Advanced Compression |NO_USAGE |2023.03.30_11.54.46| |
Advanced Security |NO_USAGE |2023.03.30_11.54.46| |
Database In-Memory |NO_USAGE |2023.03.30_11.54.46| |
Database Vault |NO_USAGE |2023.03.30_11.54.46| |
Diagnostics Pack |NO_USAGE |2023.03.30_11.54.46| |
Label Security |NO_USAGE |2023.03.30_11.54.46| |
OLAP |NO_USAGE |2023.03.30_11.54.46| |
Partitioning |NO_USAGE |2023.03.30_11.54.46| |
RAC or RAC One Node |NO_USAGE |2023.03.30_11.54.46| |
Real Application Clusters |NO_USAGE |2023.03.30_11.54.46| |
Real Application Clusters One Node |NO_USAGE |2023.03.30_11.54.46| |
Real Application Testing |NO_USAGE |2023.03.30_11.54.46| |
Spatial and Graph |NO_USAGE |2023.03.30_11.54.46| |
Tuning Pack |NO_USAGE |2023.03.30_11.54.46| |
.Database Gateway |NO_USAGE |2023.03.30_11.54.46| |
.Exadata |NO_USAGE |2023.03.30_11.54.46| |
.GoldenGate |NO_USAGE |2023.03.30_11.54.46| |
.HW |NO_USAGE |2023.03.30_11.54.46| |
.Pillar Storage |NO_USAGE |2023.03.30_11.54.46| |
#查看 control_management_pack_access
SQL> show parameter control_management_pack_access ;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_management_pack_access string NONE
control_management_pack_access 值为NONE会禁用 DIAGNOSTIC+TUNING。其中：
. 该DIAGNOSTIC包包括 AWR、ADDM 等。
. 该TUNING包包括 SQL Tuning Advisor、SQLAccess Advisor 等。
DIAGNOSTIC启用需要TUNING的许可证。
解决方案
1.向客户确认是否有有TUNING的 license
2.修改 control_management_pack_access 参数
#修改参数
alter system set control_management_pack_access='DIAGNOSTIC+TUNING' scope=both ;
#重启ASH
alter system set "_ash_enable"=false ;
alter system set "_ash_enable"=true ;
#调整_ash_size大小建议在1G以内
alter ssytem set "_ash_size"=275M;