OraFAQ Forum: Performance Tuning » Regarding Autotrace execution Plan

Home » RDBMS Server » Performance Tuning » Regarding Autotrace execution Plan

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Regarding Autotrace execution Plan [message #203107]

Mon, 13 November 2006 22:38

reena_ch30
Messages: 100
Registered: December 2005

Senior Member

A sample output of the autotrace:-

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=7 Bytes=42)
1 0 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=7 Bytes=42)

My question is:-
1) What does card=7 signify?
2) How is cost=2 arrived?

Thanks
reena

Report message to a moderator

Re: Regarding Autotrace execution Plan [message #203115 is a reply to message #203107]

Mon, 13 November 2006 23:16

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

hi reena

Cardinality

Cardinality represents the number of rows in a row set. Here, the row set can be a base table, a view, or the result of a join or GROUP BY operator.

Cost

The cost represents units of work or resource used. The CBO uses disk I/O, CPU usage, and memory usage as units of work. So, the cost used by the CBO represents an estimate of the number of disk I/Os and the amount of CPU and memory used in performing an operation. The operation can be scanning a table, accessing rows from a table by using an index, joining two tables together, or sorting a row set. The cost of a query plan is the number of work units that are expected to be incurred when the query is executed and its result produced.

go through for more info.
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/optimops.htm#76553

How does Oracle access data?
Logically Oracle finds the data to read by using the following methods:

1.Full Table Scan (FTS)

2.Index Lookup (unique & non-unique)

3.Rowid

cost= part of the explain plan as well. For example the following query indicates that the CBO has been used because there is a cost in the cost field:

SELECT STATEMENT [CHOOSE] Cost=1234

However the explain plan below indicates the use of the RBO because the cost field is blank:

SELECT STATEMENT [CHOOSE] Cost=

http://www.akadia.com/services/ora_interpreting_explain_plan.html

Understanding Access Paths for the CBO

This section describes the data access paths that can be used to locate and retrieve any row in any table.

Full Table Scans
Rowid Scans
Index Scans
Cluster Scans
Hash Scans
Sample Table Scans
How the CBO Chooses an Access Path

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/optimops.htm#76553

hope this helps
MOhammad Taj.

Report message to a moderator

Re: Regarding Autotrace execution Plan [message #203117 is a reply to message #203107]

Mon, 13 November 2006 23:25

reena_ch30
Messages: 100
Registered: December 2005

Senior Member

Hi,

I am not very clear as to how the cardinality would arrive.
suppose i have a table "emp" with 15 records and i issue "select * from emp". In that case, what should be the cardinality and how would it change?

Also, can we see the breakup of the value arrived for "cost"??

Thanks
reena

[Updated on: Mon, 13 November 2006 23:25]

Report message to a moderator

Re: Regarding Autotrace execution Plan [message #203134 is a reply to message #203117]

Tue, 14 November 2006 00:49

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

hi

Note> i am not experienced in PT just trying to learn.

Cardinality depand on some factors. factors are below.
1.Base Cardinality.

i have a table "emp" with 15 records and i issue "select * from emp". In that case, what should be the cardinality
let check .

SQL> SELECT COUNT(*) FROM EMP;

  COUNT(*)
----------
        14
SQL> exec dbms_stats.gather_table_stats('SCOTT','EMP');

PL/SQL procedure successfully completed.

SQL> explain plan for select * from emp;

Explained.

SQL> select * from table(dbms_xplan.display);

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

Plan hash value: 4080710170

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |   518 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

8 rows selected.

In above case cardinality is 14 rows.

2.Effective cardinality
When query use Predicates. ( and , or , not etc).

SQL> explain plan for select * from emp
  2  where sal between 1000 and 2000
  3  and comm is not null;

Explained.

SQL> select * from table(dbms_xplan.display);

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

Plan hash value: 4080710170

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     2 |    74 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     2 |    74 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

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


   1 - filter("COMM" IS NOT NULL AND "SAL"<=2000 AND "SAL">=1000)

13 rows selected.

Again Cardinality ( rows) is changed.

3.Join cardinality joins with predicates.

SQL> explain plan for select e.empno, e.ename,e.job,d.dname,d.deptno
  2  from emp e, dept d
  3  where e.deptno = d.deptno
  4  and sal between 1000 and 1500
  5  and comm is not null;

Explained.

SQL> select * from table(dbms_xplan.display);

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

Plan hash value: 1863486531

--------------------------------------------------------------------------------

--------

| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Ti

me     |

--------------------------------------------------------------------------------

--------


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

|   0 | SELECT STATEMENT             |         |     1 |    40 |     4   (0)| 00

:00:01 |

|   1 |  NESTED LOOPS                |         |     1 |    40 |     4   (0)| 00

:00:01 |

|*  2 |   TABLE ACCESS FULL          | EMP     |     1 |    27 |     3   (0)| 00

:00:01 |

|   3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    13 |     1   (0)| 00

:00:01 |

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


|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00

:00:01 |

--------------------------------------------------------------------------------

--------


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


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

   2 - filter("COMM" IS NOT NULL AND "SAL"<=1500 AND "SAL">=1000)
   4 - access("E"."DEPTNO"="D"."DEPTNO")

17 rows selected.

4.Distinct cardinality ( using distinct values)

SQL> explain plan for select distinct comm from emp;

Explained.

SQL> select * from table(dbms_xplan.display);

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

Plan hash value: 253844996

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     4 |     8 |     4  (25)| 00:00:01 |
|   1 |  SORT UNIQUE       |      |     4 |     8 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |    28 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

9 rows selected.

5.Group cardinality ( using Group by clause )

SQL> explain plan for select count(j.employee_id) employees, d.department_name
  2  from job_history j, departments d
  3  where d.department_id = j.department_id
  4  group by d.department_name;

Explained.

SQL> select * from table(dbms_xplan.display);

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

Plan hash value: 57058609

--------------------------------------------------------------------------------

-----------

| Id  | Operation           | Name                | Rows  | Bytes | Cost (%CPU)|

 Time     |

--------------------------------------------------------------------------------

-----------


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

|   0 | SELECT STATEMENT    |                     |    10 |   200 |     4  (25)|

 00:00:01 |

|   1 |  SORT GROUP BY      |                     |    10 |   200 |     4  (25)|

 00:00:01 |

|   2 |   NESTED LOOPS      |                     |    10 |   200 |     3   (0)|

 00:00:01 |

|   3 |    TABLE ACCESS FULL| DEPARTMENTS         |    27 |   432 |     3   (0)|

 00:00:01 |

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


|*  4 |    INDEX RANGE SCAN | JHIST_DEPARTMENT_IX |     1 |     4 |     0   (0)|

 00:00:01 |

--------------------------------------------------------------------------------

-----------


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


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

   4 - access("D"."DEPARTMENT_ID"="J"."DEPARTMENT_ID")

16 rows selected.

hope this helps
Mohammad Taj.

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/optimops.htm#76553

[Updated on: Tue, 14 November 2006 00:51]

Report message to a moderator

Previous Topic:	Difference between SORT_MULTIBLOCK_READ_COUNT and DB_MULTIBLOCK_READ_COUNT
Next Topic:	Analyze tables

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Apr 29 14:09:48 CDT 2024