E-rows e A-rows

gcomenale · Post by **gcomenale** » Fri Oct 30, 2015 10:09 am

Por que alguns planos me mostram o valor estimado e o valor atual, outros me mostram apenas o estimado e alguns não mostram nada?

SQL> ALTER SESSION SET statistics_level='ALL';

Session altered.

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(('dr6cphyffhjtk'),0, 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID dr6cphyffhjtk, child number 0
-------------------------------------
SELECT SQL_ID, CHILD_NUMBER FROM V$SQL WHERE SQL_TEXT LIKE '%GABRIEL_3%'

Plan hash value: 903671040

--------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 |00:00:00.01 |
|* 1 | FIXED TABLE FULL| X$KGLCURSOR_CHILD | 1 | 1 | 2 |00:00:00.01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(("KGLNAOBJ" IS NOT NULL AND "KGLNAOBJ" LIKE '%GABRIEL_3%' AND
"INST_ID"=USERENV('INSTANCE')))

19 rows selected.

SQL> SELECT SQL_ID, CHILD_NUMBER FROM V$SQL WHERE SQL_TEXT LIKE '%GABRIEL_3%';

SQL_ID CHILD_NUMBER
------------- ------------
dr6cphyffhjtk 0
030490ay7nsy7 0

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(('030490ay7nsy7'),0, 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 030490ay7nsy7, child number 0
-------------------------------------
select /* GABRIEL_3 */ owner, object_name from t2 where owner='SYSTEM'

Plan hash value: 2572602246

---------------------------------------------------------------------
| Id | Operation | Name | E-Rows |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 589 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 2 | INDEX RANGE SCAN | IDX_T2_OWNER | 589 |
---------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OWNER"='SYSTEM')

Note
-----
- Warning: basic plan statistics not available. These are only collected when:

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level

25 rows selected.

SQL> alter session set statistics_level=ALL;

Session altered.

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(('030490ay7nsy7'),0, 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 030490ay7nsy7, child number 0
-------------------------------------
select /* GABRIEL_3 */ owner, object_name from t2 where owner='SYSTEM'

Plan hash value: 2572602246

---------------------------------------------------------------------
| Id | Operation | Name | E-Rows |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 589 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 2 | INDEX RANGE SCAN | IDX_T2_OWNER | 589 |
---------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OWNER"='SYSTEM')

Note
-----
- Warning: basic plan statistics not available. These are only collected when:

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level

25 rows selected.

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(('030490ay7nsy7'),0, 'TYPICAL'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 030490ay7nsy7, child number 0
-------------------------------------
select /* GABRIEL_3 */ owner, object_name from t2 where owner='SYSTEM'

Plan hash value: 2572602246

----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 20 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 589 | 18259 | 20 (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 2 | INDEX RANGE SCAN | IDX_T2_OWNER | 589 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OWNER"='SYSTEM')

19 rows selected.

Post by **portilho** » Wed Nov 04, 2015 10:41 am

Hum, veja que ele reclama exatamente que não coletou as estatísticas de utilização ("basic plan statistics not available"), mesmo você mostrando o ALTER SESSION.
Vou tentar fazer um teste aqui e ver se tenho o mesmo resultado.

Dica: quando colocr plano de execução aqui, utilize a Tag code, fica bem legal.

gcomenale · Post by **gcomenale** » Wed Nov 04, 2015 10:44 am

O que seria a tag code?

Post by **portilho** » Wed Nov 04, 2015 4:10 pm

Eu executei vários aqui, e com ALTER SESSION aparece o E-Rows e A-Rows sim.
Você pode me passar uma sequência toda de testes que fez para chegar neste resultado? Se possível, não utilize SYSTEM, use um usuário "normal".

Quando você cria uma resposta aqui no Fórum, tem um botão aí em cima "Code", clique nele, e coloque o código entre as Tags que aparecerão.

gcomenale · Post by **gcomenale** » Wed Nov 04, 2015 4:44 pm

Beleza, vou reproduzir e te mando porque eu não salvei o puuty que usei nesse teste.

Post by **portilho** » Wed Nov 04, 2015 5:37 pm

Reproduzindo mais aqui, acho que entendi o que aconteceu com seu teste.
O parâmetro STATISTICS_LEVEL=ALL tem que ser alterado antes da execução do SQL, e não antes do DISPLAY_CURSOR.

Code: Select all

17:32:29 SQL> CONN SCOTT/TIGER
Connected.
17:32:34 SQL> CREATE TABLE T AS SELECT * FROM ALL_OBJECTS;

Table created.

Elapsed: 00:00:05.97
17:32:46 SQL> INSERT INTO T SELECT * FROM T;

73610 rows created.

Elapsed: 00:00:00.54
17:32:53 SQL> INSERT INTO T SELECT * FROM T;

147220 rows created.

Elapsed: 00:00:01.27
17:32:56 SQL> INSERT INTO T SELECT * FROM T;

294440 rows created.

Elapsed: 00:00:02.44
17:32:59 SQL> INSERT INTO T SELECT * FROM T;

588880 rows created.

Elapsed: 00:00:04.99
17:33:05 SQL> INSERT INTO T SELECT * FROM T;

1177760 rows created.

Elapsed: 00:00:09.55
17:33:16 SQL> INSERT INTO T SELECT * FROM T;

2355520 rows created.

Elapsed: 00:00:17.81
17:33:36 SQL> INSERT INTO T SELECT * FROM T;

4711040 rows created.

Elapsed: 00:00:46.52
17:34:26 SQL> COMMIT;

Commit complete.

Elapsed: 00:00:00.03
17:34:31 SQL> SELECT COUNT(*) FROM T;

  COUNT(*)
----------
   9422080

Elapsed: 00:00:06.65
17:34:42 SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID   48k1njhd4ras3, child number 0
-------------------------------------
SELECT COUNT(*) FROM T

Plan hash value: 2966233522

--------------------------------------------
| Id  | Operation      | Name | E-Rows |
--------------------------------------------
|   0 | SELECT STATEMENT   |     |      |
|   1 |  SORT AGGREGATE    |     |    1 |
|   2 |   TABLE ACCESS FULL| T     |  73610 |
--------------------------------------------

Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level


20 rows selected.

Elapsed: 00:00:00.78
17:34:50 SQL> ALTER SESSION SET STATISTICS_LEVEL=ALL;

Session altered.

Elapsed: 00:00:00.02
17:34:54 SQL> SELECT COUNT(*) FROM T;

  COUNT(*)
----------
   9422080

Elapsed: 00:00:05.68
17:35:02 SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID   48k1njhd4ras3, child number 1
-------------------------------------
SELECT COUNT(*) FROM T

Plan hash value: 2966233522

----------------------------------------------------------------------------------------------
| Id  | Operation      | Name | Starts | E-Rows | A-Rows |    A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |     |    1 |       |      1 |00:00:05.67 |   318K|    160K|
|   1 |  SORT AGGREGATE    |     |    1 |     1 |      1 |00:00:05.67 |   318K|    160K|
|   2 |   TABLE ACCESS FULL| T     |    1 |  73610 |   9422K|00:00:05.57 |   318K|    160K|
----------------------------------------------------------------------------------------------


14 rows selected.

Elapsed: 00:00:00.24
17:35:07 SQL>

gcomenale · Post by **gcomenale** » Thu Nov 05, 2015 11:12 am

hum

Veja só, ele gerou um novo SQL ID e eu não consegui pegar um novo plano para o mesmo SQL ID

Code: Select all

SQL> select /* GABRIEL */ count(*) from t1 where owner='SYS';

  COUNT(*)
----------
     37300

SQL> select /* GABRIEL */ count(*) from t1 where owner='SYS';

  COUNT(*)
----------
     37300

SQL> select SQL_ID, PLAN_HASH_VALUE, CHILD_NUMBER from gv$sql where SQL_FULLTEXT like '%/* GABRIEL */%';

SQL_ID        PLAN_HASH_VALUE CHILD_NUMBER
------------- --------------- ------------
15488hg4tst10      3724264953            0
8crfnuafn9r3p      3617692013            0
c3jnsjjw1a7fz       903671040            0

SQL> select SQL_FULLTEXT, PLAN_HASH_VALUE, CHILD_NUMBER from gv$sql where SQL_ID='c3jnsjjw1a7fz';

SQL_FULLTEXT                                                                     PLAN_HASH_VALUE CHILD_NUMBER
-------------------------------------------------------------------------------- --------------- ------------
select SQL_ID, PLAN_HASH_VALUE, CHILD_NUMBER from gv$sql where SQL_FULLTEXT like       903671040            0
 '%/* GABRIEL */%'


SQL> select SQL_FULLTEXT, PLAN_HASH_VALUE, CHILD_NUMBER from gv$sql where SQL_ID='8crfnuafn9r3p';

SQL_FULLTEXT                                                                     PLAN_HASH_VALUE CHILD_NUMBER
-------------------------------------------------------------------------------- --------------- ------------
select /* GABRIEL */ owner  from t1 where owner='SYS'                                 3617692013            0

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(('8crfnuafn9r3p'),0, 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  8crfnuafn9r3p, child number 0
-------------------------------------
select /* GABRIEL */ owner  from t1 where owner='SYS'

Plan hash value: 3617692013

-------------------------------------------
| Id  | Operation         | Name | E-Rows |
-------------------------------------------
|   0 | SELECT STATEMENT  |      |        |
|*  1 |  TABLE ACCESS FULL| T1   |  33440 |
-------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OWNER"='SYS')

Note
-----
   - dynamic sampling used for this statement (level=2)
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level


25 rows selected.

SQL> alter session set statistics_level='ALL';

Session altered.

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(('8crfnuafn9r3p'),0, 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  8crfnuafn9r3p, child number 0
-------------------------------------
select /* GABRIEL */ owner  from t1 where owner='SYS'

Plan hash value: 3617692013

-------------------------------------------
| Id  | Operation         | Name | E-Rows |
-------------------------------------------
|   0 | SELECT STATEMENT  |      |        |
|*  1 |  TABLE ACCESS FULL| T1   |  33440 |
-------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OWNER"='SYS')

Note
-----
   - dynamic sampling used for this statement (level=2)
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level


25 rows selected.

SQL> select /* GABRIEL */ count(*) from t1 where owner='SYS';

  COUNT(*)
----------
     37300

SQL> /

  COUNT(*)
----------
     37300

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(('8crfnuafn9r3p'),0, 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  8crfnuafn9r3p, child number 0
-------------------------------------
select /* GABRIEL */ owner  from t1 where owner='SYS'

Plan hash value: 3617692013

-------------------------------------------
| Id  | Operation         | Name | E-Rows |
-------------------------------------------
|   0 | SELECT STATEMENT  |      |        |
|*  1 |  TABLE ACCESS FULL| T1   |  33440 |
-------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OWNER"='SYS')

Note
-----
   - dynamic sampling used for this statement (level=2)
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level


25 rows selected.



SQL> EXEC DBMS_STATS.gather_table_stats('T', 'T1', estimate_percent => 100);

PL/SQL procedure successfully completed.

SQL> conn t/t
Connected.
SQL> conn / as sysdba
Connected.
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(('8crfnuafn9r3p'),0, 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  8crfnuafn9r3p, child number 0
-------------------------------------
select /* GABRIEL */ owner  from t1 where owner='SYS'

Plan hash value: 3617692013

-------------------------------------------
| Id  | Operation         | Name | E-Rows |
-------------------------------------------
|   0 | SELECT STATEMENT  |      |        |
|*  1 |  TABLE ACCESS FULL| T1   |  33440 |
-------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OWNER"='SYS')

Note
-----
   - dynamic sampling used for this statement (level=2)
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level


25 rows selected.

SQL> conn t/t
Connected.
SQL> select /* GABRIEL */ count(*) from t1 where owner='SYS';

  COUNT(*)
----------
     37300

SQL> select SQL_ID, PLAN_HASH_VALUE, CHILD_NUMBER from gv$sql where SQL_FULLTEXT like '%/* GABRIEL */%';

SQL_ID        PLAN_HASH_VALUE CHILD_NUMBER
------------- --------------- ------------
15488hg4tst10      3724264953            0
15488hg4tst10      3724264953            1
8crfnuafn9r3p      3617692013            0
c3jnsjjw1a7fz       903671040            0

SQL> select SQL_FULLTEXT, PLAN_HASH_VALUE, CHILD_NUMBER from gv$sql where SQL_ID='8crfnuafn9r3p';

SQL_FULLTEXT                                                                     PLAN_HASH_VALUE CHILD_NUMBER
-------------------------------------------------------------------------------- --------------- ------------
select /* GABRIEL */ owner  from t1 where owner='SYS'                                 3617692013            0

SQL> select SQL_FULLTEXT, PLAN_HASH_VALUE, CHILD_NUMBER from gv$sql where SQL_ID='15488hg4tst10';

SQL_FULLTEXT                                                                     PLAN_HASH_VALUE CHILD_NUMBER
-------------------------------------------------------------------------------- --------------- ------------
select /* GABRIEL */ count(*) from t1 where owner='SYS'                               3724264953            0
select /* GABRIEL */ count(*) from t1 where owner='SYS'                               3724264953            1




SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(('15488hg4tst10'),1, 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  15488hg4tst10, child number 1
-------------------------------------
select /* GABRIEL */ count(*) from t1 where owner='SYS'

Plan hash value: 3724264953

-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.02 |    1209 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.02 |    1209 |
|*  2 |   TABLE ACCESS FULL| T1   |      1 |  33440 |  37300 |00:00:00.02 |    1209 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OWNER"='SYS')

Note
-----
   - dynamic sampling used for this statement (level=2)


23 rows selected.

Post by **portilho** » Mon Nov 09, 2015 10:56 am

Está correto.
O SQL_ID 8crfnuafn9r3p com CHILD_NUMBER 0 foi compilado antes do STATISTICS_LEVEL = ALL, portanto não tem A-Rows.
O SQL_ID 15488hg4tst10 com CHILD_NUMBER 1 foi compilado depois do STATISTICS_LEVEL = ALL, portanto tem A-Rows.

gcomenale · Post by **gcomenale** » Mon Nov 09, 2015 11:25 am

Interessante!

Então, em caso de precisar ver um plano com E-rows e A-rows eu preciso alterar a minha sessão e solicitar que o usário/aplicação execute o a query novamente até gerar um novo child_number ou ele recompilar a query novamente?

Post by **portilho** » Tue Nov 10, 2015 7:40 am

Para gerar o A-Rows, o SQL tem que ser executado na mesma sessão em que ocorreu o ALTER SESSION, após este.
Se o cliente não tiver como alterar o código, pode ser via TRIGGER de LOGON, ou então ALTER SYSTEM.

Fórum Nerv Informática Ltda.

E-rows e A-rows

E-rows e A-rows

Re: E-rows e A-rows

Re: E-rows e A-rows

Re: E-rows e A-rows

Re: E-rows e A-rows

Re: E-rows e A-rows

Re: E-rows e A-rows

Re: E-rows e A-rows

Re: E-rows e A-rows

Re: E-rows e A-rows