Home » RDBMS Server » Performance Tuning » cannot see a-rows while querying dbms_xplan.display_cursor (Oracle Database 10g Enterprise (RAC) Edition Release 10.2.0.4.0 - 64bit, LINUX)
cannot see a-rows while querying dbms_xplan.display_cursor [message #553340] Thu, 03 May 2012 09:12 Go to next message
Andrey_R
Messages: 188
Registered: January 2012
Location: Euro-Asia
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 #553341 is a reply to message #553340] Thu, 03 May 2012 09:13 Go to previous messageGo to next message
BlackSwan
Messages: 22845
Registered: January 2009
Senior Member
Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: cannot see a-rows while querying dbms_xplan.display_cursor [message #553342 is a reply to message #553341] Thu, 03 May 2012 09:14 Go to previous messageGo to next message
cookiemonster
Messages: 11000
Registered: September 2008
Location: Rainy Manchester
Senior Member
@Blackswan - that wasn't the question.
Re: cannot see a-rows while querying dbms_xplan.display_cursor [message #553345 is a reply to message #553340] Thu, 03 May 2012 10:15 Go to previous messageGo to next message
LNossov
Messages: 288
Registered: July 2011
Location: Germany
Senior Member
Try with 'ADVANCED ALLSTATS LAST'.
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 Go to previous messageGo to next message
Andrey_R
Messages: 188
Registered: January 2012
Location: Euro-Asia
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 #553577 is a reply to message #553526] Mon, 07 May 2012 00:51 Go to previous messageGo to next message
LNossov
Messages: 288
Registered: July 2011
Location: Germany
Senior Member
It has to work. Perhaps you have an unvisible character in your hint. Try to retype the hint. Additionally you can try with

alter session set statistics_level=all;


Could you please upload a complete protocol.
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 Go to previous messageGo to next message
Andrey_R
Messages: 188
Registered: January 2012
Location: Euro-Asia
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 Go to previous messageGo to next message
LNossov
Messages: 288
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 Wink ?

[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 Go to previous messageGo to next message
Andrey_R
Messages: 188
Registered: January 2012
Location: Euro-Asia
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 #553645 is a reply to message #553635] Mon, 07 May 2012 08:48 Go to previous messageGo to next message
LNossov
Messages: 288
Registered: July 2011
Location: Germany
Senior Member
Quote:
if you want A-ROWS to be inputted in the plan table - alter session set statistics_level = all is not enough


It is absolutely enough!

Quote:
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.


You make something wrong. For running query you should get 0 for all sql plan stats. After finishing you will get correct stats.

If you start your sql manually, then you can do the following:

1. make the following settings


set linesize 1000
set pagesize 1000



2. then run


alter session set statistics_level=all;



3. then run your sql,
4. after that run the following select:


select plan_table_output from table ( sys.dbms_xplan.display_cursor( '','','ADVANCED ALLSTATS LAST'));
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 Go to previous messageGo to next message
Andrey_R
Messages: 188
Registered: January 2012
Location: Euro-Asia
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 #553650 is a reply to message #553647] Mon, 07 May 2012 10:08 Go to previous messageGo to next message
LNossov
Messages: 288
Registered: July 2011
Location: Germany
Senior Member
You didn't answer, if you start your sql manually.
Re: cannot see a-rows while querying dbms_xplan.display_cursor [message #553651 is a reply to message #553650] Mon, 07 May 2012 10:14 Go to previous messageGo to next message
Andrey_R
Messages: 188
Registered: January 2012
Location: Euro-Asia
Senior Member

I assume it will work, but this is not longer the question, as i stated previously.

the question is how to retain data of plan table.

I don't want to wait 5 minutes(heavy query) with my eyes on the screen,
so I can catch the plan_table content before it disappears.

Regards,
Andrey
Re: cannot see a-rows while querying dbms_xplan.display_cursor [message #553652 is a reply to message #553650] Mon, 07 May 2012 10:21 Go to previous messageGo to next message
LNossov
Messages: 288
Registered: July 2011
Location: Germany
Senior Member
I see, you start the sql manually in SQL*Plus. In this case you don't need to search the cursor in v$sql. Instead of that you can get your execution plan by calling

select plan_table_output from table ( sys.dbms_xplan.display_cursor( '','','ADVANCED ALLSTATS LAST'));


as I described for you.

I suppose, you make a mistake at searching in v$sql.

Take in account my suggestion please. Or you will run into problems further.
Re: cannot see a-rows while querying dbms_xplan.display_cursor [message #553653 is a reply to message #553651] Mon, 07 May 2012 10:28 Go to previous messageGo to next message
LNossov
Messages: 288
Registered: July 2011
Location: Germany
Senior Member
You will lose quickly execution plan in v$sql_plan, if your shared pool is too small and / or the traffic in shared pool is too big. But I don't think, that you have this case. Rather your search in v$sql isn't correct.
Re: cannot see a-rows while querying dbms_xplan.display_cursor [message #553655 is a reply to message #553653] Mon, 07 May 2012 10:48 Go to previous message
Andrey_R
Messages: 188
Registered: January 2012
Location: Euro-Asia
Senior Member

No, I am not making a mistake - I can see my query clearly in the plan table,
and seconds after i do not see it anymore.

Your explanation about the shared pool might be the answer.

Thank you.

[Updated on: Mon, 07 May 2012 10:49]

Report message to a moderator

Previous Topic: unexplained performance degradation of PL/SQL procedures
Next Topic: Help required in analyzing AWR report
Goto Forum:
  


Current Time: Thu Oct 02 08:48:31 CDT 2014

Total time taken to generate the page: 0.05798 seconds