Home » RDBMS Server » Performance Tuning » Oracle Optimizer cost (merged) (Oracle11g)
Oracle Optimizer cost (merged) [message #639061] Tue, 30 June 2015 13:34 Go to next message
shrinika
Messages: 298
Registered: April 2008
Senior Member
Team, Optimizer is showing different cost when run the below query.

select * from xxxx.audit_events;


Here is the execution plan. Oracle think, there is only 100 rows...

SQL> EXPLAIN PLAN SET STATEMENT_ID='GT' FOR
  2  select * from xxxx.audit_events;

Explained.

SQL>
SQL> select plan_table_output from table(dbms_xplan.display('plan_table','GT','SERIAL'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
Plan hash value: 3209023837

----------------------------------------------------------------------------------
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |              |   100 | 15100 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| AUDIT_EVENTS |   100 | 15100 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------

8 rows selected.

SQL>


Oracle optimizer thinks different way when I we run below query.
1308M rows is accurate info. But above query shows only 100 rows.


SQL> EXPLAIN PLAN SET STATEMENT_ID='GT' FOR
  2  select count(*) from xxxx.audit_events;

Explained.

SQL>
SQL> select plan_table_output from table(dbms_xplan.display('plan_table','GT','SERIAL'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 188268779

-------------------------------------------------------------------------------------------
| Id  | Operation             | Name                      | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                           |     1 |   297K (14)| 01:16:48 |
|   1 |  SORT AGGREGATE       |                           |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| AUDIT_OCCURENCE_EVENT_IDX |  1308M|   297K (14)| 01:16:48 |
-------------------------------------------------------------------------------------------

9 rows selected.

SQL>



Can any one help to understand why it is different?
Oracle Optimizer cost [message #639062 is a reply to message #639061] Tue, 30 June 2015 13:34 Go to previous messageGo to next message
shrinika
Messages: 298
Registered: April 2008
Senior Member
Team, Optimizer is showing different cost when run the below query.

select * from xxxx.audit_events;


Here is the execution plan. Oracle think, there is only 100 rows...

SQL> EXPLAIN PLAN SET STATEMENT_ID='GT' FOR
  2  select * from xxxx.audit_events;

Explained.

SQL>
SQL> select plan_table_output from table(dbms_xplan.display('plan_table','GT','SERIAL'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
Plan hash value: 3209023837

----------------------------------------------------------------------------------
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |              |   100 | 15100 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| AUDIT_EVENTS |   100 | 15100 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------

8 rows selected.

SQL>


Oracle optimizer thinks different way when I we run below query.
1308M rows is accurate info. But above query shows only 100 rows.


SQL> EXPLAIN PLAN SET STATEMENT_ID='GT' FOR
  2  select count(*) from xxxx.audit_events;

Explained.

SQL>
SQL> select plan_table_output from table(dbms_xplan.display('plan_table','GT','SERIAL'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 188268779

-------------------------------------------------------------------------------------------
| Id  | Operation             | Name                      | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                           |     1 |   297K (14)| 01:16:48 |
|   1 |  SORT AGGREGATE       |                           |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| AUDIT_OCCURENCE_EVENT_IDX |  1308M|   297K (14)| 01:16:48 |
-------------------------------------------------------------------------------------------

9 rows selected.

SQL>



Can any one help to understand why it is different?
Re: Oracle Optimizer cost [message #639063 is a reply to message #639062] Tue, 30 June 2015 13:37 Go to previous messageGo to next message
Michel Cadot
Messages: 65850
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Because:
ROWS=100
ROWS=1306M

Re: Oracle Optimizer cost [message #639064 is a reply to message #639063] Tue, 30 June 2015 13:48 Go to previous messageGo to next message
BlackSwan
Messages: 26208
Registered: January 2009
Location: SoCal
Senior Member
>Can any one help to understand why it is different?
Table & index statistics are not current or correct.
Re: Oracle Optimizer cost [message #639065 is a reply to message #639062] Tue, 30 June 2015 13:49 Go to previous messageGo to next message
John Watson
Messages: 7624
Registered: January 2010
Location: Global Village
Senior Member
Perhaps the statistics for the index and the table are out of sync. Look at the figures for NUM_ROWS in dba_tables and dba_indexes. And gather stats.
Re: Oracle Optimizer cost [message #639082 is a reply to message #639065] Wed, 01 July 2015 06:48 Go to previous messageGo to next message
shrinika
Messages: 298
Registered: April 2008
Senior Member
The table has 1308 million rows. My question is, why optimizer shows 100 rows on the first query.

SQL> select num_rows,num_rows/1000000 from dba_tables where table_name='AUDIT_EVENTS';

  NUM_ROWS NUM_ROWS/1000000
---------- ----------------
1308422936       1308.42294

SQL>


Re: Oracle Optimizer cost [message #639083 is a reply to message #639082] Wed, 01 July 2015 06:51 Go to previous messageGo to next message
shrinika
Messages: 298
Registered: April 2008
Senior Member
Also first query takes full table scan and second one takes index scan...

It makes sense to use index scan. Why optimizer thinks that there is only 100 rows?

Any input is highly appreciated.
Re: Oracle Optimizer cost [message #639086 is a reply to message #639083] Wed, 01 July 2015 07:20 Go to previous messageGo to next message
John Watson
Messages: 7624
Registered: January 2010
Location: Global Village
Senior Member
Because you have not gathered statistics. You have been told this already. Twice. You need to read up on using the dbms_stats.gather_table_stats procedure.

[Updated on: Wed, 01 July 2015 07:21]

Report message to a moderator

Re: Oracle Optimizer cost [message #639099 is a reply to message #639083] Wed, 01 July 2015 10:16 Go to previous message
Lalit Kumar B
Messages: 3131
Registered: May 2013
Location: World Wide on the Web
Senior Member
A nice article https://oracle-base.com/articles/misc/cost-based-optimizer-and-database-statistics
Previous Topic: SQL Tuning Advise
Next Topic: 'Configuration' wait class in AWR report
Goto Forum:
  


Current Time: Tue Oct 23 13:44:38 CDT 2018