explain plan for oracle sql query

SQL> create table temp_jp (num int,name char(4));
SQL> DECLARE
 BEGIN
 FOR i in 1..1000 loop
 if mod(i,2)=0 then
 insert into temp_jp values(i,'ram');
 else
 insert into temp_jp values(i,'sita');
 end if;
 end loop;
 end;
 /
SQL> Create index idx_temp_jp on temp_jp(num);
SQL> set timing on
SQL> explain plan for select count(*) from temp_jp where name='sita';

Explained.

Elapsed: 00:00:00.15
SQL> select * from table(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2917205682

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     1 |     6 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |         |     1 |     6 |            |          |
|*  2 |   TABLE ACCESS FULL| TEMP_JP |   500 |  3000 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):

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

   2 - filter("NAME"='sita')

Note
-----
   - dynamic sampling used for this statement

18 rows selected.

Elapsed: 00:00:01.51

SQL> ANALYZE TABLE TEMP_JP COMPUTE STATISTICS;

Table analyzed.

Elapsed: 00:00:00.52

SQL> explain plan for select count(*) from temp_jp where name='sita';

Explained.

Elapsed: 00:00:00.01
SQL> select * from table(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2917205682

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     1 |     4 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |         |     1 |     4 |            |          |
|*  2 |   TABLE ACCESS FULL| TEMP_JP |   500 |  2000 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):

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

   2 - filter("NAME"='sita')

14 rows selected.

Elapsed: 00:00:00.10
SQL>
========================================================================
NOW,
To gather the stats for the entire schema including all tables and all indexes use the following.

SQL> EXEC DBMS_UTILITY.ANALYZE_SCHEMA('AKSHAY','COMPUTE');

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.24
Alternatively, we can use
exec DBMS_UTILITY.ANALYZE_SCHEMA('AKSHAY','ESTIMATE', estimate_rows => 1000);
exec DBMS_UTILITY.ANALYZE_SCHEMA('AKSHAY','ESTIMATE', estimate_percent => 25);
exec DBMS_UTILITY.ANALYZE_SCHEMA('AKSHAY','DELETE');

To Create stats on a table, or index, use the following:
ANALYZE table scott compute statistics;
ANALYZE table scott estimate statistics sample 25 percent;
ANALYZE table scott estimate statistics sample 1000 rows;
analyze index sc_idx compute statistics;
analyze index sc_idx validate structure;

Comments

Popular posts from this blog

Query for Tablespace usage with Autoextend

How to configure multiple Oracle listeners

sheel script - automatic tablespace addition in oracle 11g