Oracle Optimizer cost (merged) [message #639061] |
Tue, 30 June 2015 13:34 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
shrinika
Messages: 306 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
shrinika
Messages: 306 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 #639065 is a reply to message #639062] |
Tue, 30 June 2015 13:49 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8944 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
shrinika
Messages: 306 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
shrinika
Messages: 306 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.
|
|
|
|
|