Performance Issue Scenarios and activities.

—--------------------------------------------------------------------------------------

How to find Blocking Sessions

Blocking sessions occur when one sessions holds an exclusive lock on an object
and doesn’t release it before another sessions wants to update the same data.

This will block the second session until the first session has done its work.

1. Simulation

2. Finding Out Who’s Holding a Blocking Lock

3. Solution

1. Simulation

Session 1:

[oracle@node1 ~]$ sqlplus / as sysdba

SQL> create table t (a varchar2(1));

Table created.

SQL> insert into t values ('z');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t where a='z' for update;

A
-
z

SQL>
SQL> select sys_context('USERENV','SID') from dual;

SYS_CONTEXT('USERENV','SID')
----------------------------------------------------
46

SQL>

Session 2:

In second session try to update the rows which you have selected
above.

[oracle@node1 ~]$ sqlplus / as sysdba;

SQL> select sys_context('USERENV','SID') from dual;

SYS_CONTEXT('USERENV','SID')
---------------------------------------------------
39

SQL>
SQL> update t set a='x' where a='z';

-- hanging here --
..
..

2. Finding Out Who’s Holding a Blocking Lock

sqlplus / as sysdba

SELECT s1.username || '@' || s1.machine

|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' )
' AS blocking_status
FROM gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
WHERE s1.sid=l1.sid AND s2.sid=l2.sid
AND l1.BLOCK=1 AND l2.request > 0
AND l1.id1 = l2.id1
AND l1.id2 = l2.id2;

select sid, serial#, username,status from v$session where sid in

('holder','waiter');

-- OR --

select decode(request,0,'Holder: ','Waiter: ')||sid sess,

id1, id2, lmode, request, type
from gv$lock
where (id1, id2, type) in
(select id1, id2, type from gv$lock where request>0)
order by id1, request;

-- OR --

SELECT
s.blocking_session,
s.sid,
s.serial#,
s.seconds_in_wait
FROM
gv$session s
WHERE
blocking_session IS NOT NULL;

-- OR --

SELECT
l1.sid || ' is blocking ' || l2.sid blocking_sessions
FROM
gv$lock l1, gv$lock l2
WHERE
 l1.block = 1 AND
l2.request > 0 AND
l1.id1 = l2.id1 AND
l1.id2 = l2.id2;

-- OR --

SELECT sid, id1 FROM v$lock WHERE TYPE='TM';

SELECT object_name FROM dba_objects WHERE
object_id='&object_id_from_above_output';
select sid,type,lmode,request,ctime,block from v$lock;
select blocking_session, sid, wait_class,
seconds_in_wait
from v$session
where blocking_session is not NULL
order by blocking_session;

Output:

SQL> SELECT s1.username || '@' || s1.machine

2 || ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' )
' AS blocking_status
FROM v$lock l1, v$session s1, v$lock l2, v$session s2
3 4 5 WHERE s1.sid=l1.sid AND s2.sid=l2.sid
6 AND l1.BLOCK=1 AND l2.request > 0
7 AND l1.id1 = l2.id1
8 AND l1.id2 = l2.id2;

BLOCKING_STATUS
---------------------------------------------------------------------
-------------
SH@rac2.rajasekhar.com ( SID=46 ) is blocking SH@rac2.rajasekhar.com
( SID=39 )

SQL> select sid, serial#, username,status from v$session where sid in

(46,39);

SID SERIAL# USERNAME STATUS

---------- ---------- -------------------- --------
 39 77 SH ACTIVE <-- waiter
46 13 SH INACTIVE <-- Holder

SQL>

SQL>

-- OR --

SQL> select decode(request,0,'Holder: ','Waiter: ')||sid sess,

id1, id2, lmode, request, type
from gv$lock
where (id1, id2, type) in
(select id1, id2, type from gv$lock where request>0)
order by id1, request; 2 3 4 5 6

SESS ID1
ID2 LMODE REQUEST TY
------------------------------------------------ ----------
---------- ---------- ---------- --
Holder: 46 589826
1571 6 0 TX
Waiter: 39 589826
1571 0 6 TX

SQL>

-- OR --

SQL> SELECT
s.blocking_session,
s.sid,
s.serial#,
s.seconds_in_wait
FROM
gv$session s
WHERE
blocking_session IS NOT NULL; 2 3 4 5 6 7 8
9

BLOCKING_SESSION SID SERIAL# SECONDS_IN_WAIT

---------------- ---------- ---------- ---------------
 46 39 77 1626

SQL>

-- OR --

SQL> SELECT
l1.sid || ' is blocking ' || l2.sid blocking_sessions
FROM
gv$lock l1, gv$lock l2
WHERE
l1.block = 1 AND
l2.request > 0 AND
l1.id1 = l2.id1 AND
l1.id2 = l2.id2; 2 3 4 5 6 7 8 9

BLOCKING_SESSIONS
----------------------------------------------------------
46 is blocking 39

SQL>

3. Solution

Inform to the holder to commit/rollback.

--- OR ----

kill the holder session, if it is ok

Syntax: alter system kill session 'SID,SERIAL#,@INST_ID'; (For RAC)

alter system kill session 'SID,SERIAL#';(For Single instance)

SQL> alter system kill session '46,13';

System altered.
SQL>

-- After killing holder session, waiter session got completed

SQL> update t set a='x' where a='z';

1 row updated.

About Oracle Statistics

1. Introduction

2. About *_TAB_MODIFICATIONS
3. Identify STALE STATS

4. Gather STATS

4.1 DATABASE Level

4.2 SCHEMA Level

4.3 TABLE Level

4.4 INDEX Level

5. SYSTEM STATISTICS

6. How lock/unlock statistics on table

Introduction

What are statistics?

Ans: Input to the Cost-Based Optimizer, Provide information on

User Objects

Table, Partition, Subpartition

Columns

Index, Index Partition, Index Subpartition

System

Dictionary

Memory structure (X$)

Statistics on a table are considered stale when more than

STALE_PERCENT (default 10%) of the rows are changed

(total number of inserts, deletes, updates) in the table.

Oracle monitors the DML activity for all tables and records it in the
SGA.

The monitoring information is periodically flushed to disk, and is

exposed in the *_TAB_MODIFICATIONS view.

Why do we care about statistics?

Poor statistics usually lead to poor plans

Collecting good quality stats is not straightforward

Collecting good quality stats may be time consuming

Improving statistics quality improves the chance to find an optimal

plan (usually)

The higher the sample the higher the accuracy

The higher the sample the longer it takes to collect

The longer it takes the less frequent we can collect fresh stats!

If your data changes frequently, then

If you have plenty of resources:

Gather statistics often and with a very large sample size

If your resources are limited:

Use AUTO_SAMPLE_SIZE (11g)

Use a smaller sample size (try to avoid this)

If your data doesn’t change frequently:

Gather statistics less often and with a very large sample size

Recommended syntax

/*
Assuming we want Oracle to determine where to put histograms (instead
of specifying the list manually):

In 10g avoid AUTO_SAMPLE_SIZE

exec dbms_stats.gather_table_stats('owner', 'table_name',

estimate_percent => NNN,granularity => “it depends”);

In 11g use AUTO_SAMPLE_SIZE but keep an eye open.

exec dbms_stats.gather_table_stats('owner', 'table_name');

*/

About *_TAB_MODIFICATIONS

When querying *_TAB_MODIFICATIONS view you should ensure that you run
DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO before doing so in order to
obtain accurate results.

Before
-- exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO; --

SQL> select table_name, inserts, updates, deletes from

dba_tab_modifications where table_name='BIG_TABLE';

no rows selected

SQL>

After

SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

PL/SQL procedure successfully completed.

SQL> select table_name, inserts, updates, deletes from

dba_tab_modifications where table_name='BIG_TABLE';
TABLE_NAME INSERTS UPDATES DELETES

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

BIG_TABLE 100 0 0

SQL>

Identify STALE STATS:

col TABLE_NAME for a30

col PARTITION_NAME for a20

col SUBPARTITION_NAME for a20

select
OWNER,TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,NUM_ROWS,LAST_ANALY
ZED from DBA_TAB_STATISTICS where STALE_STATS='YES';

OR

select OWNER,TABLE_NAME,LAST_ANALYZED,STALE_STATS from

DBA_TAB_STATISTICS where OWNER='&OWNER' AND STALE_STATS='YES';
Gather STATS

CASCADE => TRUE : Gather statistics on the indexes as well. If not used Oracle will
determine whether to collect it or not.

DEGREE => 4: Degree of parallelism.

ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE : (DEFAULT) Auto set

the sample size % for skew(distinct) values (accurate and faster than setting a manual
sample size).

METHOD_OPT=> : For gathering Histograms:

FOR COLUMNS SIZE AUTO : You can specify one column between “” instead of all
columns.

FOR ALL COLUMNS SIZE REPEAT : Prevent deletion of histograms and collect it only
for columns already have histograms.

FOR ALL COLUMNS : Collect histograms on all columns.

FOR ALL COLUMNS SIZE SKEWONLY : Collect histograms for columns have skewed
value should test skewness first

FOR ALL INDEXED COLUMNS : Collect histograms for columns have indexes only.

DATABASE Level
Gathering statistics for all objects in database, cascade will include indexes

exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

select OWNER,TABLE_NAME,LAST_ANALYZED,STALE_STATS from

DBA_TAB_STATISTICS where STALE_STATS='YES';

exec dbms_stats.gather_database_stats(cascade=>TRUE,method_opt =>'FOR

ALL COLUMNS SIZE AUTO');

OR

-- For faster execution

EXEC
DBMS_STATS.GATHER_DATABASE_STATS(ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SA
MPLE_SIZE,degree=>6);

OR

EXEC
DBMS_STATS.GATHER_DATABASE_STATS(ESTIMATE_PERCENT=>dbms_stats.auto_sa
mple_size,CASCADE => TRUE,degree => 4);

SCHEMA level

Gathering statistics for all objects in a schema, cascade will

include indexes. If not used Oracle will determine whether to collect
it or not.
exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

select OWNER,TABLE_NAME,LAST_ANALYZED,STALE_STATS from

DBA_TAB_STATISTICS where STALE_STATS='YES' and OWNER='&owner;

set timing on

exec dbms_stats.gather_schema_stats(ownname=>'&schema_name',
CASCADE=>TRUE,ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,degree
=>4);

OR

exec
dbms_stats.gather_schema_stats(ownname=>'&schema_name',ESTIMATE_PERCE
NT=>dbms_stats.auto_sample_size,degree =>4);

-- CASCADE is not included here. Let Oracle will determine whether to

collect statatics on indexes or not.

OR

EXEC DBMS_STATS.GATHER_SCHEMA_STATS ('&schema_name'); Will gather

stats on 100% of schema tables.

TABLE Level
-- The CASCADE parameter determines whether or not statistics are
gathered for the indexes on a table.

exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

SELECT OWNER,TABLE_NAME,LAST_ANALYZED,STALE_STATS from

DBA_TAB_STATISTICS WHERE TABLE_NAME='&TNAME';

exec
dbms_stats.gather_table_stats(ownname=>'&Schema_name',tabname=>'&Tabl
e_name',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,cascade=>TRUE,d
egree =>4);

OR

-- Gather statistics on the table with histograms being automatically

created

exec dbms_stats.gather_table_stats('&SCHEMA_NAME','&Table_name');

Index Statistics

exec DBMS_STATS.GATHER_INDEX_STATS(ownname => '&OWNER',indname

=>'&INDEX_NAME',estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE);
SYSTEM STATISTICS

What is system statistics:

System statistics are statistics about CPU speed and IO performance,

it enables the CBO to

effectively cost each operation in an execution plan. Introduced in

Oracle 9i.

Why gathering system statistics:

Oracle highly recommends gathering system statistics during a

representative workload,

ideally at peak workload time, in order to provide more accurate

CPU/IO cost estimates to the optimizer.

You only have to gather system statistics once.

There are two types of system statistics (NOWORKLOAD statistics &

WORKLOAD statistics):

NOWORKLOAD statistics:
This will simulates a workload -not the real one but a simulation-
and will not collect full statistics, it's less accurate than
"WORKLOAD statistics" but if you can't capture the statistics during
a typical workload you can use noworkload statistics.

To gather noworkload statistics:

SQL> execute dbms_stats.gather_system_stats();

WORKLOAD statistics:

This will gather statistics during the current workload [which

supposed to be representative of actual system I/O and CPU workload
on the DB].

To gather WORKLOAD statistics:

SQL> execute dbms_stats.gather_system_stats('start');

Once the workload window ends after 1,2,3.. hours or whatever, stop
the system statistics gathering:

SQL> execute dbms_stats.gather_system_stats('stop');

You can use time interval (minutes) instead of issuing start/stop

command manually:

SQL> execute dbms_stats.gather_system_stats('interval',60);

Check the system values collected:

col pname format a20

col pval2 format a40

select * from sys.aux_stats$;

cpuspeedNW: Shows the noworkload CPU speed, (average number of CPU

cycles per second).

ioseektim: The sum of seek time, latency time, and OS overhead

time.

iotfrspeed: I/O transfer speed,tells optimizer how fast the DB can

read data in a single read request.

cpuspeed: Stands for CPU speed during a workload statistics

collection.

maxthr: The maximum I/O throughput.

slavethr: Average parallel slave I/O throughput.

sreadtim: The Single Block Read Time statistic shows the average
time for a random single block read.

mreadtim: The average time (seconds) for a sequential multiblock

read.

mbrc: The average multiblock read count in blocks.

Notes:

-When gathering NOWORKLOAD statistics it will gather (cpuspeedNW,

ioseektim, iotfrspeed) system statistics only.

-Above values can be modified manually using

DBMS_STATS.SET_SYSTEM_STATS procedure.

-According to Oracle, collecting workload statistics doesn't impose

an additional overhead on your system.

Delete system statistics:

SQL> execute dbms_stats.delete_system_stats();

How lock/unlock statistics on table

1. Create table and verify

SQL> create table raj ( x number );

Table created.
SQL> SELECT stattype_locked FROM dba_tab_statistics WHERE
table_name='RAJ' and owner='SH';

STATT

-----

<---- Output NULL. Hence table unlocked. It will allow to

gather stats on this table

SQL>

2. Lock stats

SQL> exec dbms_stats.lock_table_stats('SH', 'RAJ');

PL/SQL procedure successfully completed.

SQL>

3. Verify
SQL> SELECT stattype_locked FROM dba_tab_statistics WHERE
table_name='RAJ' and owner='SH';

STATT

-----

ALL <---- Hence table locked. It will not allow to gather stats on
this table

SQL>

Tryied to gather stats, but fails

SQL> exec dbms_stats.gather_table_stats('sh', 'raj');

BEGIN dbms_stats.gather_table_stats('sh', 'raj'); END;

ERROR at line 1:

ORA-20005: object statistics are locked (stattype = ALL) <-- LOCKED

4. Unlock
SQL> exec dbms_stats.unlock_table_stats('SH', 'RAJ');

PL/SQL procedure successfully completed.

SQL> SELECT stattype_locked FROM dba_tab_statistics WHERE

table_name='RAJ' and owner='SH';

STATT

-----

<----it's unlocked, It will allow to gather stats on this table

SQL> exec dbms_stats.gather_table_stats('sh', 'raj');

PL/SQL procedure successfully completed.

SQL>
Locked: ALL

Unlocked: NULL
Generate Explain Plan

Generate/Customize
Explain Plan
0. Creating a Plan Table

1. Generate Explain Plan using SQL Developer

2. Generate Explain Plan using Autotrace in SQL Developer

3. Generate Explain Plan using SQL*Plus

3.1: Running EXPLAIN PLAN

3.2: Using utlxpls.sql

3.3: Using EXPLAIN PLAN with the STATEMENT ID Clause

3.4: Customizing PLAN_TABLE Output

3.5: Using EXPLAIN PLAN with the INTO Clause

3.6: Outline Hit

3.7: Advanced Format

4. Using SQL*Plus Autotrace

5. Using DBMS_XPLAN.DISPLAY_CURSOR

5.1: BASIC Format

5.2: SERIAL Format

5.3: TYPICAL Format

5.4: ALL Format

5.5: ALLSTATS Format

5.6: ALLSTATS LAST Format

5.7: Enabling Extra Output

5.8: Removing Output Sections

5.9: Advanced Format

5.10: TYPICAL Format after immediate sql executed

6: Using DBMS_XPLAN.DISPLAY_AWR (From AWR snaps)

0. Creating a Plan Table

The first thing you will need to do is make sure you have a table
called PLAN_TABLE available in your schema.
The following script will create it for you if you don't have it
already

@$ORACLE_HOME/rdbms/admin/utlxplan.sql

Method 3: Generate Explain Plan using SQL*Plus

Method 3.1: Running EXPLAIN PLAN

— saves the execution plan in the PLAN_TABLE.

SET TIMING ON

set echo on
set linesize 200 pagesize 1000

explain plan for

select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1;

select * from table(dbms_xplan.display);

output

=======

SQL> set echo on

SQL> set linesize 200 pagesize 1000

SQL> explain plan for select count(*) from test t1, test t2 where
t1.c=t2.c and t1.c=1;

Explained.

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 3253233075

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

| Id | Operation | Name | Rows | Bytes | Cost

(%CPU)| Time |

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

| 0 | SELECT STATEMENT | | 1 | 6 | 1928

(99)| 00:00:24 |

| 1 | SORT AGGREGATE | | 1 | 6 |
| |

|* 2 | HASH JOIN | | 400M| 2288M| 1928

(99)| 00:00:24 |

|* 3 | INDEX FAST FULL SCAN| TEST_C_INDX | 20000 | 60000 | 12

(0)| 00:00:01 |

|* 4 | INDEX FAST FULL SCAN| TEST_C_INDX | 20000 | 60000 | 12

(0)| 00:00:01 |

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

Predicate Information (identified by operation id):

---------------------------------------------------
 2 - access("T1"."C"="T2"."C")

3 - filter("T1"."C"=1)

4 - filter("T2"."C"=1)

18 rows selected.

SQL>

Method 3.2: Using utlxpls.sql

SET TIMING ON

set echo on

set linesize 200 pagesize 1000

explain plan for

select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1;

@$ORACLE_HOME/rdbms/admin/utlxpls.sql

output
======

SQL> set echo on

SQL> set linesize 200 pagesize 1000

SQL> explain plan for select count(*) from test t1, test t2 where
t1.c=t2.c and t1.c=1;

Explained.

SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql

..

..

SQL> set markup html preformat on

SQL>

SQL> Rem

SQL> Rem Use the display table function from the dbms_xplan package
to display the last

SQL> Rem explain plan. Force serial option for backward compatibility

SQL> Rem

SQL> select plan_table_output from

table(dbms_xplan.display('plan_table',null,'serial'));
PLAN_TABLE_OUTPUT

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

Plan hash value: 3253233075

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

| Id | Operation | Name | Rows | Bytes | Cost

(%CPU)| Time |

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

| 0 | SELECT STATEMENT | | 1 | 6 | 1928

(99)| 00:00:24 |

| 1 | SORT AGGREGATE | | 1 | 6 |
| |

|* 2 | HASH JOIN | | 400M| 2288M| 1928

(99)| 00:00:24 |

|* 3 | INDEX FAST FULL SCAN| TEST_C_INDX | 20000 | 60000 | 12

(0)| 00:00:01 |

|* 4 | INDEX FAST FULL SCAN| TEST_C_INDX | 20000 | 60000 | 12

(0)| 00:00:01 |
---------------------------------------------------------------------
-----------------

Predicate Information (identified by operation id):

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

2 - access("T1"."C"="T2"."C")

3 - filter("T1"."C"=1)

4 - filter("T2"."C"=1)

18 rows selected.

SQL>

Method 3.4: Customizing PLAN_TABLE Output

SET TIMING ON
set echo on

set linesize 200 pagesize 1000

EXPLAIN PLAN SET STATEMENT_ID = 'MY_PLAN' FOR

select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1;

col Plan for a70

SELECT cardinality "Rows",

lpad(' ',level-1)||operation||' '||options||' '||object_name

"Plan"

FROM PLAN_TABLE

CONNECT BY prior id = parent_id

AND prior statement_id = statement_id

START WITH id = 0

AND statement_id = 'MY_PLAN'

ORDER BY id;

Output

=======
SQL> SELECT cardinality "Rows",

lpad(' ',level-1)||operation||' '||options||' '||object_name

"Plan"

2 3 FROM PLAN_TABLE

4 CONNECT BY prior id = parent_id

5 AND prior statement_id = statement_id

6 START WITH id = 0

7 AND statement_id = 'MY_PLAN'

8 ORDER BY id;

Rows Plan

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

1 SELECT STATEMENT

1 SORT AGGREGATE

400000000 HASH JOIN

20000 INDEX FAST FULL SCAN TEST_C_INDX

20000 INDEX FAST FULL SCAN TEST_C_INDX

SQL>

(OR)

SQL> EXPLAIN PLAN SET STATEMENT_ID = 'MY_PLAN' FOR

select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1;

Explained.

Elapsed: 00:00:00.01

SQL> COL PLAN FORMAT A80

SELECT LPAD( ' ', 2 * ( LEVEL - 1 ) ) ||

SQL> 2 OPERATION || ' ' ||

3 OPTIONS || ' ' ||

4 OBJECT_NAME || ' ' ||

5 OBJECT_TYPE PLAN,

6 COST

7 FROM PLAN_TABLE

8 WHERE STATEMENT_ID = 'MY_PLAN'

 9 CONNECT BY PRIOR ID = PARENT_ID

10 AND PRIOR STATEMENT_ID = 'MY_PLAN'

11 START WITH ID =1;

PLAN COST

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

SORT AGGREGATE

HASH JOIN 1928

INDEX FAST FULL SCAN TEST_C_INDX INDEX 12

INDEX FAST FULL SCAN TEST_C_INDX INDEX 12

Elapsed: 00:00:00.00

SQL>

Method 5.10: TYPICAL Format after immediate sql executed

The typical way to display an execution plan for a SQL statement that was just executed

We could have also been set to NULL to produce the same result

set echo on

set linesize 200 pagesize 1000

select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));

Output

=======

SQL> set echo on

SQL> set linesize 200 pagesize 1000

SQL> select count(*) from test t1, test t2 where t1.c=t2.c and
t1.c=1;

COUNT(*)

----------

400000000
SQL> SELECT * FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));

PLAN_TABLE_OUTPUT

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

SQL_ID dkz7v96ym42c6, child number 1

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

select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1

Plan hash value: 3253233075

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

| Id | Operation | Name | Rows | Bytes | Cost

(%CPU)| Time |

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

| 0 | SELECT STATEMENT | | | | 1928

(100)| |

| 1 | SORT AGGREGATE | | 1 | 6 |
| |
|* 2 | HASH JOIN | | 400M| 2288M| 1928
(99)| 00:00:24 |

|* 3 | INDEX FAST FULL SCAN| TEST_C_INDX | 20000 | 60000 | 12

(0)| 00:00:01 |

|* 4 | INDEX FAST FULL SCAN| TEST_C_INDX | 20000 | 60000 | 12

(0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("T1"."C"="T2"."C")

3 - filter("T1"."C"=1)

4 - filter("T2"."C"=1)

23 rows selected.

SQL>
-----

set echo on

set linesize 200 pagesize 1000

ALTER SESSION SET STATISTICS_LEVEL='ALL';

select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS

LAST'));

select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'Advanced'));

----

Method 6: Using DBMS_XPLAN.DISPLAY_AWR (From AWR snaps)

set echo on

set linesize 200 pagesize 1000

select * from table(dbms_xplan.display_awr('dkz7v96ym42c6'));

Output
=======

SQL> select * from table(dbms_xplan.display_awr('dkz7v96ym42c6'));

PLAN_TABLE_OUTPUT

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

SQL_ID dkz7v96ym42c6

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

select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1

Plan hash value: 3253233075

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

| Id | Operation | Name | Rows | Bytes | Cost

(%CPU)| Time |

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

| 0 | SELECT STATEMENT | | | | 1928

(100)| |
| 1 | SORT AGGREGATE | | 1 | 6 |
| |

| 2 | HASH JOIN | | 400M| 2288M| 1928

(99)| 00:00:24 |

| 3 | INDEX FAST FULL SCAN| TEST_C_INDX | 20000 | 60000 | 12

(0)| 00:00:01 |

| 4 | INDEX FAST FULL SCAN| TEST_C_INDX | 20000 | 60000 | 12

(0)| 00:00:01 |

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

16 rows selected.
Rebuild Index Script
racle provides a fast index rebuild capability that allows you to re-create an index
without having to drop the existing index. The currently available index is used as the
data source for the index, instead of the table’s being used as the data source. During
the index rebuild, you can change its STORAGE parameters and TABLESPACE
assignment.

In the following example, the BA_PK index is rebuilt (via the REBUILD clause). Its
storage parameters are changed to use an initial extent size of 8MB and a next extent
size of 4MB in the BA_INDEXES tablespace.

NOTE

When the BA_PK index is rebuilt, there must be enough space for both the old index
and the new index to exist simultaneously. After the new index has been created, the
old index will be dropped.When you create an index that is based on previously indexed
columns, Oracle may be able to use the existing indexes as data sources for the new
index. The Oracle Optimizer may use parts of existing composite indexes as needed for
queries, so you may not need to create many indexes to support the most common
queries.

You can rebuild indexes while they are being accessed via the REBUILD ONLINE
clause of the ALTER INDEX command.

Use dba_indexes or dba_tables views

-- Rebuild index

Select 'alter index '||owner||'.'||index_name||' rebuild ONLINE;' from

dba_indexes d where D.TABLE_NAME ='PROVIDE_TABLE_NAME' and
D.OWNER='PROVIDE_OWNER';

-- Rebuild Partition index

Select 'alter index '||index_owner||'.'||index_name||' rebuild partition

'||partition_name||' ONLINE;' from dba_ind_partitions where
INDEX_NAME='PROVIDE_INDEX_NAME';

-- Rebuild Sub Partition index

Select 'alter index '||index_owner||'.'||index_name||' rebuild subpartition

'||subpartition_name||' ONLINE;' from dba_ind_subpartitions where
INDEX_NAME='PROVIDE_INDEX_NAME';