Home » RDBMS Server » Performance Tuning » Unable to interpret number of rows accessed during Full Table scan (Oracle Enterprise Edition 11.2.0.4.0 on Linux)
Unable to interpret number of rows accessed during Full Table scan [message #642949] Wed, 23 September 2015 07:13 Go to next message
orapratap
Messages: 134
Registered: November 2011
Location: Canada
Senior Member
Hello

I have some confusion while interpreting execution plan and would seek your help on the same

Regardless of 'Full Table Scan' or 'Index Access' the execution plan shows same numbers of rows accessed

Am I missing some basic stuff?

Here is the example

Before creating the Index
**************************************************
**************************************************
SQL> create table TS as select * from dba_objects;

SQL> exec dbms_stats.gather_table_stats(user,'TS');

PL/SQL procedure successfully completed.

SQL> select COLUMN_NAME,NUM_DISTINCT,NUM_NULLS from dba_tab_columns where table_name='TS';

COLUMN_NAME                    NUM_DISTINCT  NUM_NULLS
------------------------------ ------------ ----------
M                                         0          0
N                                         0          0
EDITION_NAME                              0      92329
NAMESPACE                                21          1
SECONDARY                                 2          0
GENERATED                                 2          0
TEMPORARY                                 2          0
STATUS                                    2          0
TIMESTAMP                              1912          1
LAST_DDL_TIME                          1806          1
CREATED                                1723          0
OBJECT_TYPE                              46          0
DATA_OBJECT_ID                        12827      79408
OBJECT_ID                             92329          1
SUBOBJECT_NAME                          417      91414
OBJECT_NAME                           54896          0
OWNER                                    88          0

17 rows selected.

SQL> select num_rows from dba_tables where table_name='TS';

  NUM_ROWS
----------
     92329

SQL> explain plan for select * from ts where object_name='EMP';

Explained.

SQL> select * from table(dbms_xplan.display(null,null,'all'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 338975713

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     2 |   196 |   368   (1)| 00:00:05 |
|*  1 |  TABLE ACCESS FULL| TS   |     2 |   196 |   368   (1)| 00:00:05 |
--------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

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

   1 - SEL$1 / TS@SEL$1

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

   1 - filter("OBJECT_NAME"='EMP')

Column Projection Information (identified by operation id):
-----------------------------------------------------------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   1 - "TS"."OWNER"[VARCHAR2,30], "OBJECT_NAME"[VARCHAR2,128],
       "TS"."SUBOBJECT_NAME"[VARCHAR2,30], "TS"."OBJECT_ID"[NUMBER,22],
       "TS"."DATA_OBJECT_ID"[NUMBER,22], "TS"."OBJECT_TYPE"[VARCHAR2,19],
       "TS"."CREATED"[DATE,7], "TS"."LAST_DDL_TIME"[DATE,7],
       "TS"."TIMESTAMP"[VARCHAR2,19], "TS"."STATUS"[VARCHAR2,7],
       "TS"."TEMPORARY"[VARCHAR2,1], "TS"."GENERATED"[VARCHAR2,1],
       "TS"."SECONDARY"[VARCHAR2,1], "TS"."NAMESPACE"[NUMBER,22],
       "TS"."EDITION_NAME"[VARCHAR2,30]

After creating the Index
**************************************************
**************************************************
SQL> create index i_objname on ts(object_name);

Index created.

SQL> exec dbms_stats.gather_index_stats(user,'I_OBJNAME');

PL/SQL procedure successfully completed.

SQL> explain plan for select * from ts where object_name='EMP';

Explained.

SQL> select * from table(dbms_xplan.display(null,null,'all'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3000387531

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     2 |   196 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TS        |     2 |   196 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_OBJNAME |     2 |       |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

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

   1 - SEL$1 / TS@SEL$1
   2 - SEL$1 / TS@SEL$1

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

   2 - access("OBJECT_NAME"='EMP')

Column Projection Information (identified by operation id):

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

   1 - "TS"."OWNER"[VARCHAR2,30], "OBJECT_NAME"[VARCHAR2,128],
       "TS"."SUBOBJECT_NAME"[VARCHAR2,30], "TS"."OBJECT_ID"[NUMBER,22],
       "TS"."DATA_OBJECT_ID"[NUMBER,22], "TS"."OBJECT_TYPE"[VARCHAR2,19],
       "TS"."CREATED"[DATE,7], "TS"."LAST_DDL_TIME"[DATE,7],
       "TS"."TIMESTAMP"[VARCHAR2,19], "TS"."STATUS"[VARCHAR2,7],
       "TS"."TEMPORARY"[VARCHAR2,1], "TS"."GENERATED"[VARCHAR2,1],
       "TS"."SECONDARY"[VARCHAR2,1], "TS"."NAMESPACE"[NUMBER,22],
       "TS"."EDITION_NAME"[VARCHAR2,30]
   2 - "TS".ROWID[ROWID,10], "OBJECT_NAME"[VARCHAR2,128]

SQL> select /*+ gather_plan_statistics */ * from ts where object_name='EMP';

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  10593djpbmayc, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from ts where object_name='EMP'

Plan hash value: 3000387531

---------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |      1 |        |      8 |00:00:00.01 |      12 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TS        |      1 |      2 |      8 |00:00:00.01 |      12 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|*  2 |   INDEX RANGE SCAN          | I_OBJNAME |      1 |      2 |      8 |00:00:00.01 |       4 |
---------------------------------------------------------------------------------------------------

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

   2 - access("OBJECT_NAME"='EMP')


Thus looks like in both above plans, FTS as well as Index scan plan, 2 rows are accessed

Only the Predicate info changed from
1 - filter("OBJECT_NAME"='EMP')
to
2 - access("OBJECT_NAME"='EMP')

But during FTS haven't all rows were accessed to filter out 2 rows?

Why I can't see the number of rows accessed to get 2 rows?

Kind Regards
Orapratap

Report message to a moderator

Re: Unable to interpret number of rows accessed during Full Table scan [message #642950 is a reply to message #642949] Wed, 23 September 2015 07:18 Go to previous messageGo to next message
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
Senior Member
What do you mean by "accessed"? Both plans show that the number of rows expected to be returned after applying the predicate is 2, whether the access method is full table scan or index range scan.

Report message to a moderator

Re: Unable to interpret number of rows accessed during Full Table scan [message #642952 is a reply to message #642950] Wed, 23 September 2015 07:25 Go to previous messageGo to next message
orapratap
Messages: 134
Registered: November 2011
Location: Canada
Senior Member
Hello John

Many thanks for your quick response

Quote:

Both plans show that the number of rows expected to be returned after applying the predicate is 2


My question is where does the execution plan with - "Full Table Scan" shows the information that to return 2 rows it needed to "scan" many rows!
How many?

Kind Regards
Orapratap

Report message to a moderator

Re: Unable to interpret number of rows accessed during Full Table scan [message #642963 is a reply to message #642949] Wed, 23 September 2015 09:08 Go to previous messageGo to next message
bpeasland
Messages: 51
Registered: February 2015
Location: United States
Member
orapratap wrote on Wed, 23 September 2015 07:13



Thus looks like in both above plans, FTS as well as Index scan plan, 2 rows are accessed




This is incorrect. It is not 2 rows accessed. It is 2 rows returned. Obviously, the FTS accesses more rows.

But more importantly than the number of rows that are accessed, is the number of blocks accessed. The CBO cost values partially reflect this, which is why the FTS has a much higher cost.


HTH,
Brian

Report message to a moderator

Re: Unable to interpret number of rows accessed during Full Table scan [message #642966 is a reply to message #642963] Wed, 23 September 2015 09:24 Go to previous messageGo to next message
orapratap
Messages: 134
Registered: November 2011
Location: Canada
Senior Member
Thanks Brian

Question remains "where does the execution plan reflect the number of rows it scanned/traveled/checked through, to get the 2 rows"

Kind Regards
Orapratap

Report message to a moderator

Re: Unable to interpret number of rows accessed during Full Table scan [message #642967 is a reply to message #642966] Wed, 23 September 2015 09:27 Go to previous messageGo to next message
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
Senior Member
orapratap wrote on Wed, 23 September 2015 15:24
Thanks Brian

Question remains "where does the execution plan reflect the number of rows it scanned/traveled/checked through, to get the 2 rows"

Kind Regards
Orapratap

The answer remains: "nowhere".

Report message to a moderator

Re: Unable to interpret number of rows accessed during Full Table scan [message #642969 is a reply to message #642967] Wed, 23 September 2015 10:05 Go to previous messageGo to next message
orapratap
Messages: 134
Registered: November 2011
Location: Canada
Senior Member
Thankfully TKPROF shows the reliable information - cr=1323

Explain Plan did not gave us such indicator even with "Gather_plan_statistics"


select /*+ query15 */ *
from
 ts where object_id=20


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.01          0       1323          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.01          0       1323          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 248
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  TABLE ACCESS FULL TS (cr=1323 pr=0 pw=0 time=54 us cost=368 size=98 card=1)


select /* query14 */ *
from
 ts where object_name='EMP'


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0         18          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.01       0.01          0       1323          0           8
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.02       0.02          0       1341          0           8

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 248
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         8          8          8  TABLE ACCESS FULL TS (cr=1323 pr=0 pw=0 time=16268 us cost=368 size=196 card=2)



Kind Regards
Orapratap

Report message to a moderator

Re: Unable to interpret number of rows accessed during Full Table scan [message #642970 is a reply to message #642966] Wed, 23 September 2015 10:05 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
orapratap wrote on Wed, 23 September 2015 07:24
Thanks Brian

Question remains "where does the execution plan reflect the number of rows it scanned/traveled/checked through, to get the 2 rows"

Kind Regards
Orapratap


one place to see number of rows processed is SQL_TRACE=TRUE & process trace file with tkprof

Report message to a moderator

Re: Unable to interpret number of rows accessed during Full Table scan [message #642971 is a reply to message #642970] Wed, 23 September 2015 10:08 Go to previous messageGo to next message
orapratap
Messages: 134
Registered: November 2011
Location: Canada
Senior Member
Many Thanks BlackSwan!

Our messages crossed just now!

It came as surprise to me that explain plan does not gave any indicator as how many rows / blocks it "might" scan to get the result!

TKPROF rocks!

Kind Regards
Orapratap

Report message to a moderator

Re: Unable to interpret number of rows accessed during Full Table scan [message #642996 is a reply to message #642971] Thu, 24 September 2015 08:58 Go to previous messageGo to next message
bpeasland
Messages: 51
Registered: February 2015
Location: United States
Member
orapratap wrote on Wed, 23 September 2015 10:08


It came as surprise to me that explain plan does not gave any indicator as how many rows / blocks it "might" scan to get the result!



The Explain Plan doesn't tell you how many rows or block, at least not directly. However, the calculated COST for that table access is
a rough indicator of how this.

Cheers,
Brian

Report message to a moderator

Re: Unable to interpret number of rows accessed during Full Table scan [message #643029 is a reply to message #642996] Fri, 25 September 2015 23:38 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
This is one of the reasons why it is still possible for us mere humans to be able to tune better than the computer. We can such deficiencies and go get the extra data we need to stay ahead.

If you want to know, then you have to build your own count and filter queries to find out. This is something I explain in my book on SQL Tuning. I have attached the free materials from the book. These will help you determine if it is something you can benefit from and if so it is on sale on Amazon.

Please find attached.

Chapter #1 of the book (Oracle SQL Performance Tuning and Optimization: It's all about the Cardinalities).
Scripts from the book.
A new organizational tool for doing tuning sessions (SQL Tuning Worksheet)
Brief discussion of what is needed to tune a query.

These will get you started. If you like what you see, go buy the book and learn how to tune SQL.

Kevin

Report message to a moderator

Re: Unable to interpret number of rows accessed during Full Table scan [message #643069 is a reply to message #643029] Mon, 28 September 2015 05:14 Go to previous messageGo to next message
orapratap
Messages: 134
Registered: November 2011
Location: Canada
Senior Member
Hello Kevin

Thank you so much for your kind reply and the sharing the documents & scripts

The document details step by step approach to tune scary big queries and is really helpful

Hope to read the complete book soon

Only thing I would like to mention is - Many times developers sitting at remote locations are given "Execution Plan" for slow running queries on Production and in that case such counts / group by counts won't be available

I guess in that case set_table_stats etc. would be needed based on assumed row count & data distibution

Thank you

Kind Regards
Orapratap

Report message to a moderator

Re: Unable to interpret number of rows accessed during Full Table scan [message #643075 is a reply to message #643069] Mon, 28 September 2015 06:43 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
In my experience, it is near impossible to tune a query without access to the underlying data. This means either access to some version of the database that exhibits the problem be it either the original database or a copy somewhere. If all you have is a query plan, then all you can do is guess. Good luck in such a case.

Thanks for looking at the attachments. Kevin

Report message to a moderator

Previous Topic: Query does not error, but does not come back with a result set (as if it's in an endless loop)
Next Topic: Tuning
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Jan 30 13:25:04 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.