STATISTICS_LEVEL A-ROWS

STATISTICS_LEVEL specifies the level of collection for database and operating system statistics. The Oracle Database collects these statistics for a variety of purposes, including making self-management decisions.

Property	Description
Parameter type	String
Syntax	STATISTICS_LEVEL = { ALL | TYPICAL | BASIC }
Default value	TYPICAL
Modifiable	ALTER SESSION, ALTER SYSTEM
Modifiable in a PDB	Yes
Basic	No

The default setting of TYPICAL ensures collection of all major statistics required for database self-management functionality and provides best overall performance. The default value should be adequate for most environments.

When the STATISTICS_LEVEL parameter is set to ALL, additional statistics are added to the set of statistics collected with the TYPICAL setting. The additional statistics are timed operating system statistics and plan execution statistics.

SQL> alter session set statistics_level=all

SQL> show parameter  STATISTICS_LEVEL


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
statistics_level                     string      TYPICAL
SQL> show parameter  control_management


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_management_pack_access       string      DIAGNOSTIC+TUNING

alter session set statistics_level=basic; 

alter session set statistics_level=typical; 

alter session set statistics_level=all; 

SQL> show parameter  statis

The V$STATISTICS_LEVEL view displays information about the status of the statistics or advisories controlled by the STATISTICS_LEVEL parameter. See "V$STATISTICS_LEVEL".

using  alter session set statistics_level=all;  to get A-ROWS in sql Xplan

1. get SQL_ID for a particular SQL

select sql_id from v$sql where sql_text like '%KEY WORLD TO IDDENTIFY THE SQL WHICH HAS BEEN EXECUTED%';

2. get SQL XPLAN

select * from table (dbms_xplan.display_cursor('203tkg7xhs0c4' ,NULL, 'runstats_last' ));  

Plan hash value: 215547808
 
-------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                      | Name                  | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |

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

screenshot for XPLAN:

if you want to have more information fromt the plan, using query below:

select * from table(dbms_xplan.display_cursor('203tkg7xhs0c4',null,'all allstats last'));

3. 另一种方法　可以加hint  :   /*+ gather_plan_statistics */   在查询语句中，也可以实现同样的效果