E-rows e A-rows
E-rows e A-rows
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.
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.
Re: E-rows e A-rows
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.
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.
Re: E-rows e A-rows
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.
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.
Re: E-rows e A-rows
Beleza, vou reproduzir e te mando porque eu não salvei o puuty que usei nesse teste.
Re: E-rows e A-rows
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.
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>
Re: E-rows e A-rows
hum
Veja só, ele gerou um novo SQL ID e eu não consegui pegar um novo plano para o mesmo SQL ID
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.
Re: E-rows e A-rows
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.
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.
Re: E-rows e A-rows
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?
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?
Re: E-rows e A-rows
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.
Se o cliente não tiver como alterar o código, pode ser via TRIGGER de LOGON, ou então ALTER SYSTEM.