cannot see a-rows while querying dbms_xplan.display_cursor [message #553340] |
Thu, 03 May 2012 09:12 |
Andrey_R
Messages: 441 Registered: January 2012 Location: Israel
|
Senior Member |
|
|
Hi All,
I am trying to investigate a slow query.
1. I've ran the query with a hint of /*+ gather_plan_statistics */
2. got its sql_id,
3. and executed:
select * from table (dbms_xplan.display_cursor ('sql_id_number', 0, 'all allstats advanced'));
The output doen't show me A-ROWS to compare to E-ROWS and i have no clue why.
Does anyone know why, and how can i compare actual cardinality on stages of executing this query e.t.c?
Many thanks in advance,
Andrey
|
|
|
|
|
|
Re: cannot see a-rows while querying dbms_xplan.display_cursor [message #553526 is a reply to message #553345] |
Sun, 06 May 2012 06:33 |
Andrey_R
Messages: 441 Registered: January 2012 Location: Israel
|
Senior Member |
|
|
Hi All,
Blackswan - I understand it's easier to answer once you have a test case. Unfortunately - I do not have a RAC-configured database to create test case scripts on. In addition, I assume I am not the only one encountering this behavior and there should be a simple explanation why the A-ROWS column does not show up.
LNossov - I've tried it as you suggested - provides same output:
" ------------------------------------------------------------------------------------------------------------------------------------- -- "
"| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem |"
" ------------------------------------------------------------------------------------------------------------------------------------- -- "
Anybody has an idea why could this happen, and how to fix it, so I can see Actual Rows and such too, to compare to my estimate?
Thanks,
Andrey
|
|
|
|
Re: cannot see a-rows while querying dbms_xplan.display_cursor [message #553628 is a reply to message #553577] |
Mon, 07 May 2012 06:22 |
Andrey_R
Messages: 441 Registered: January 2012 Location: Israel
|
Senior Member |
|
|
Thank you for trying to help.
however, problem persists...
I have Prepared the following testcase on:
database version: Oracle Database 11g Release 11.1.0.7.0 - 64bit Production
OS version : Windows Server 2003
--create user, grant permissions, connect with the user:
create user ANDREY identified by whatever;
grant dba to ANDREY;
conn andrey/password@connstring;
--create the table for the testcase
create table testcase
(id number, name varchar(15));
--fill it with data
begin
for i in 1..50000 loop
insert into testcase values
(i, 'name' || i);
end loop;
end;
-commit:
commit;
--set statistics level to all:
alter session
set statistics_level = all;
--run the query:
select *
from testcase;
--then run the query to detect its sql_id:
select sql_id,prev_sql_id
from v$session v
where
lower(program) like '%plus%'
and upper(username) like '%ANDREY%'
order by logon_time desc
--make sure that this is my query:
select sql_text
from v$sql
where sql_id = '22qf4xxf1v5za';
--then query the plan table to try and see A-ROWS,
--in the same session, or another one:
select * from table(dbms_xplan.display_cursor('22qf4xxf1v5za', 0 , 'all allstats advanced'))
--also tried
--select * from table(dbms_xplan.display_cursor('22qf4xxf1v5za', 0 , 'ADVANCED ALLSTATS LAST'))
Unfortunately, it gives out the same output....
can anyone help please?
Thank you all in advance.
regards,
Andrey
[Updated on: Mon, 07 May 2012 06:27] Report message to a moderator
|
|
|
Re: cannot see a-rows while querying dbms_xplan.display_cursor [message #553633 is a reply to message #553628] |
Mon, 07 May 2012 06:53 |
|
LNossov
Messages: 318 Registered: July 2011 Location: Germany
|
Senior Member |
|
|
1. you wasn't careful enough with your testcase. So I had to correct it,
2. because of long output I changed your sql,
3. it is my result:
SQL> @andrey
SQL> set echo on
SQL>
SQL> set linesize 1000
SQL> set pagesize 1000
SQL>
SQL> drop table testcase;
drop table testcase
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL>
SQL> --create the table for the testcase
SQL>
SQL> create table testcase
2 (id number, name varchar(15));
Table created.
SQL>
SQL> --fill it with data
SQL>
SQL> begin
2 for i in 1..50000 loop
3 insert into testcase values
4 (i, 'name' || i);
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> --set statistics level to all:
SQL>
SQL> alter session
2 set statistics_level = all;
Session altered.
SQL>
SQL> --run the query:
SQL>
SQL> select count(*) from testcase;
COUNT(*)
----------
50000
SQL>
SQL> select * from table(dbms_xplan.display_cursor('', '' , 'all allstats advanced'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID f4wzjckg705zs, child number 0
-------------------------------------
select count(*) from testcase
Plan hash value: 3221245523
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 40 (100)| | 1 |00:00:00.01 | 141 |
| 1 | SORT AGGREGATE | | 1 | 1 | | | 1 |00:00:00.01 | 141 |
| 2 | TABLE ACCESS FULL| TESTCASE | 1 | 53169 | 40 (3)| 00:00:01 | 50000 |00:00:00.01 | 141 |
-----------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / TESTCASE@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "TESTCASE"@"SEL$1")
END_OUTLINE_DATA
*/
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]
Note
-----
- dynamic sampling used for this statement (level=2)
43 rows selected.
SQL>
SQL> select * from table(dbms_xplan.display_cursor('f4wzjckg705zs', 0 , 'all allstats advanced'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID f4wzjckg705zs, child number 0
-------------------------------------
select count(*) from testcase
Plan hash value: 3221245523
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 40 (100)| | 1 |00:00:00.01 | 141 |
| 1 | SORT AGGREGATE | | 1 | 1 | | | 1 |00:00:00.01 | 141 |
| 2 | TABLE ACCESS FULL| TESTCASE | 1 | 53169 | 40 (3)| 00:00:01 | 50000 |00:00:00.01 | 141 |
-----------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / TESTCASE@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "TESTCASE"@"SEL$1")
END_OUTLINE_DATA
*/
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]
Note
-----
- dynamic sampling used for this statement (level=2)
43 rows selected.
SQL>
SQL> drop table testcase;
Table dropped.
SQL>
SQL> exit
I don't see any problem. You too ?
[Updated on: Mon, 07 May 2012 06:54] Report message to a moderator
|
|
|
Re: cannot see a-rows while querying dbms_xplan.display_cursor [message #553635 is a reply to message #553628] |
Mon, 07 May 2012 06:57 |
Andrey_R
Messages: 441 Registered: January 2012 Location: Israel
|
Senior Member |
|
|
Hi all,
I think I've found the problem for this particular scenario.
I didn't see A-ROWS because it wasn't inputted in the plan table in the first place.
if you want A-ROWS to be inputted in the plan table - alter session set statistics_level = all is not enough.
Only after running the query with a hint of /*+ gather_plan_statistics */ - I got the A-ROWS in the plan table.
------------------------------------------------------------------------------------------------------------
the problem is with a very heavy query. it runs for about a minute or so.
when i run it - it probably doesn't yet have data for A-ROWS, which makes sense.
however, as it finishes - the plan table for this sql_id is somehow emptied of data.
I observed what appears to be a short window of time between running the query,
Identifying it's sql_id and querying the plan table for it.
1. Does anyone have an explanation for this?
2. Anyone knows how to somehow "retain" the data of a plan table for a particular sql_id?
Regards,
Andrey
|
|
|
|
Re: cannot see a-rows while querying dbms_xplan.display_cursor [message #553647 is a reply to message #553645] |
Mon, 07 May 2012 09:25 |
Andrey_R
Messages: 441 Registered: January 2012 Location: Israel
|
Senior Member |
|
|
Dear LNossov
1).
first of all,
Thank you for your replies and wanting to help.
you are right:
alter session set statistics_level = all is enough to see A-ROWS, my mistake.
2).
The reason i didn't see A-ROWS was because first i queried the plan table while it was still executing,
and then i DID see something - estimated rows, but not yet ACTUAL rows.
that is what got me confused.
then, when i queried the plan table again AFTER query has finished running -
- query got an error, because data in plan table was no longer valid.
3).
what i observed is that there is a time window of a few seconds between finishing of the query run and losing the data in the explain table.
I asked whether i can control it to retain the data in the plan table for some more time.
Does anybody know the answer to this question?
Regards,
Andrey
[Updated on: Mon, 07 May 2012 09:28] Report message to a moderator
|
|
|
|
|
|
|
|
|
Re: cannot see a-rows while querying dbms_xplan.display_cursor [message #662870 is a reply to message #662863] |
Sun, 14 May 2017 07:18 |
|
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
firoj_m wrote on Sun, 14 May 2017 02:41HI- How did you get elapsed time in mil sec.
I am using oracle 11g for me its displaying in seconds.
I need to perform micro tuning where mil sec matter.
any idea how to change this time format of explain plan's A-Row
Can't be done since DATE datatype is used & only has granularity to whole seconds.
IMO, micro tuning is an exercise in futility.
|
|
|