Oracle Database Performance and Tuning

(1)

ORACLE DATABASE PERFORMANCE AND TUNING

Table and Index Compression

Oracle Tables can compress on block level compression.

This feature will be available from oracle 9i onwards, which is very useful in many data warehouses and read only large tables. Table compression can reduce storage space requirement (drastically some times) and may make the queries run faster (need to read only fewer blocks of data).

You may enable compression on a table at the time of table creation or by altering the table. Remember the existing data in the table is not compressed on uncompressed when you do the “alter”.

CREATE TABLE COMPRESSTABLE (

COMPRESSCOL1 VARCHAR2 (20), COMPRESSCOL2 DATE) TABLESPACE TABLESPACEname NOLOGGING COMPRESS PCTFREE 0;

ALTER TABLE COMPRESSTABLE COMPRESS;

The data compression is transparent to the user. You run queries against the table the same way you use to do before. Oracle compresses data blocks only when the data is loaded in direct path. The statements could be

INSERT with APPEND hint

INSERT with PARALLEL hint (parallel DML)

CREATE TABLE AS

Table Compression is suitable for large tables, where the updates/deletes are close to none. If there are updates/deletes, you may end up using more space – to update, Oracle has to uncompress the row, and insert it again; row deleted will free up some space which may not be sufficient for the next inserted row, because conventional inserts are not compressed, direct load inserts always load above the HWM.

You can either compress the table, or selectively on partitions. It may be a good idea to compress the older data on a partitioned table. To do this, you have to perform a

ALTER TABLE MYPARTTABLE MOVE PARTITION JAN04 TABLESPACE COMP_DATA COMPRESS PCTFREE 0;

After the partition move, you may also have to do:

(2)

MODIFY PARTITION JAN04 REBUILD UNUSABLE LOCAL INDEXES;

Another place to use compression is when you create materialized views, because most of the MVs are read only. If the MV already exist, you may do

ALTER MATERIALIZED VIEW MYMV COMPRESS;

The data will be compressed when the materialized view is refreshed.

Restrictions:

We cannot specify data segment compression for an index-organized table, for any overflow segment or partition of an overflow segment, or for any mapping table segment of an index-organized table.

We cannot specify data segment compression for hash partitions or for either hash or list sub-partitions.

We cannot specify data segment compression for an external table

The dictionary views DBA_TABLES, DBA_TAB_PARTITIONS have a column named COMPRESSION, which will be either DISABLED or ENABLED.

Index Key Compression

Oracle Index key compression : You compress the leading columns of an index (or index organized table) to save space. Oracle compresses only non-partitioned indexes that are non-unique or unique indexes of at least two columns. Bitmap indexes cannot be compressed.

Usually, keys in an index have two pieces, a grouping piece and a unique piece. If the key is not defined to have a unique piece, Oracle provides one in the form of a rowid appended to the grouping piece. Key compression is a method of breaking off the grouping piece and storing it so it can be shared by multiple unique pieces.

The Key compression is achieved by breaking the index entry into two pieces – a prefix entry (or the grouping piece) and the suffix entry (the unique piece). Key compression is done within an index block but not across multiple index blocks. Suffix entries form the compressed version of index rows. Each suffix entry references a prefix entry, which is stored in the same index block as the suffix entry.

Although key compression reduces the storage requirements of an index, it can increase the CPU time required to reconstruct the key column values during an index scan. It also incurs some additional storage overhead, because every prefix entry has an overhead of 4 bytes associated with it.

Example creating a compressed index-organized table:

CREATE TABLE INDEXKEYCOM

(OWNER VARCHAR2(30),

TABLE_NAME VARCHAR2(30),

TABLESPACE_NAME VARCHAR2 (30),

(3)

ORGANIZATION INDEX

COMPRESS;

Example creating a compressed index:

CREATE INDEX pidx_INDEXKEYCOM

ON INDEXKEYCOM (COUNTRY, STATE, SEX)

TABLESPACE IKEYCOMPRESS_TS

COMPRESS;

We can specify an integer along with the COMPRESS clause, which specifies the number of prefix columns to compress. For unique indexes, the valid range of prefix length values is from 1 to the number of key columns minus 1. The default is the number of key columns minus 1. For non-unique indexes, the valid range is from 1 to the number of key columns. The default is the number of key columns.

Oracle Resumable Space Allocation

Ora-0165* is a common to all and it is always need a special attention, otherwise the entire work may get impacted.

If you often have issues with batch jobs running out of space producing unable to extent errors in the database, now Oracle can suspend the session in error until you add more space and resume the session from where it left….

Resumable space allocation solution can be used for the following errors:

ORA-1653 unable to extend table ... in tablespace ...

ORA-1654 unable to extend index ... in tablespace ...

ORA-1650 unable to extend rollback segment ... in tablespace ...

ORA-1628 max # extents ... reached for rollback segment ...

ORA-1654 max # extents ... reached in index ...

ORA-1631 max # extents ... reached in table ...

(4)

ALTER SESSION ENABLE RESUMABLE;

Oracle Dictionary views can be queried to obtain information about the status of resumable statements:

V$SESSION_WAIT

Statement is suspended the session invoking the statement is put into a wait state. Row is inserted into this view for the session with the EVENT column containing "statement suspended, wait error to be cleared".

DBA_RESUMABLE and USER_RESUMABLE

Views contain rows for all currently executing or suspended resumable statements. Can be used by a dba, AFTER SUSPEND trigger, or another session to monitor the progress of, or obtain specific information about, resumable statements.

Invisible Indexes in Oracle 11g

Introduction

11g allows indexes to be marked as invisible. Invisible indexes are maintained or structured like any other index, but they are ignored by the optimizer unless the OPTIMIZER_USE_INVISIBLE_INDEXES parameter is set to TRUE at the instance or session level.

It can be created as invisible by using the INVISIBLE keyword, and their visibility can be toggled using the ALTER INDEX command.

CREATE INDEX index_name ON table_name(column_name) INVISIBLE; ALTER INDEX index_name INVISIBLE;

ALTER INDEX index_name VISIBLE;

A query using the indexes column in the WHERE clause ignores the index and does a full table scan. Create a table and execute select commands

SET AUTOTRACE ON

SELECT * FROM invisible_table WHERE id = 9999;

---| Id ---| Operation ---| Name ---| Rows ---| Bytes ---| Cost (%CPU)---| Time ---| ---| 0 ---| SELECT STATEMENT ---| ---| 1 ---| 3 ---| 7 (0)---| 00:00:01 ---|

|* 1 | TABLE ACCESS FULL| INVISIBLE_TABLE | 1 | 3 | 7 (0)| 00:00:01 |

Change the OPTIMIZER_USE_INVISIBLE_INDEXES parameter makes the index available to the optimizer. ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE;

SELECT * FROM invisible_table WHERE id = 9999;

|* 1 | INDEX RANGE SCAN| INVISIBLE_TABLE_ID | 1 | 3 | 1 (0)| 00:00:01 |

The index visible means it is still available to the optimizer when the OPTIMIZER_USE_INVISIBLE_INDEXES parameter is reset.

(5)

ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=FALSE; ALTER INDEX invisible_table_id VISIBLE;

|* 1 | INDEX RANGE SCAN| INVISIBLE_TABLE_ID | 1 | 3 | 1 (0)| 00:00:01 |

II (Invisible Indexes) can be useful for processes with specific indexing needs, where the presence of the indexes may adversely affect other functional areas. They are also useful for testing the impact of dropping an index. The visibility status of an index is indicated by the VISIBILITY column of the [DBA|ALL|USER]_INDEXES views. Oracle Virtual Indexes

Introduction

VI (Virtual Indexes) is another undocumented feature used by Oracle. Virtual indexes, as the name suggests are pseudo-indexes that will not behave the same way that normal indexes behave, and are meant for a very specific purpose.

A virtual index is created in a slightly different manner than the normal indexes. A virtual index has no segment pegged to it, i.e., the DBA_SEGMENTS view will not show an entry for this. Oracle handles such indexes internally and few required dictionary tables are updated so that the optimizer can be made aware of its presence and generate an execution plan considering such indexes.

This functionality is not intended for standalone usage. It is part of the Oracle Enterprise Manger Tuning Pack (Virtual Index Wizard). The virtual index wizard functionality allows the user to test a potential new index prior to actually building the new index in the database. It allows the CBO to evaluate the /7potential new index for a selected SQL statement by building an explain plan that is aware of the potential new index. This allows the user to determine if the optimizer would use the index, once implemented.

This feature is here to be supported from Enterprise Manager and not for standalone usage. I went a bit further and actually tested it using SQL*Plus, basically, trying to use the same feature but without the enterprise manager. On a developer angle, I could not see much use of Virtual Indexes, where we can create and drop indexes while testing. However, this feature could prove handy if a query or group of queries have to be tested in production (for want of simulation or urgency!), to determine if a new index will improve the performance, without impacting existing or new sessions.

Some attributes of the Virtual Indexes.

a. Permanent and continue to exist unless we drop them.

b. Creation will not affect existing and new sessions. Only sessions marked for Virtual Index usage will become aware of their existence.

c. VI indexes will be used only when the hidden parameter "_use_nosegment_indexes" is set to true.

d. Rule based optimizer did not recognize Virtual Indexes, however, CBO recognizes them. Anyway, RBO is obsolute in Oracle 10g onwards.

e. Dictionary view DBA_SEGMENTS will not show an entry for Virtual Indexes. The table DBA_INDEXES and DBA_OBJECTS will have an entry for them in Oracle 8i; in Oracle 9i onwards, DBA_INDEXES no longer show Virtual Indexes.

f. Virtual Indexes cannot be altered and throw a "fake index" error!

g. Virtual Indexes can be analyzed, using the ANALYZE command or DBMS_STATS package, but the statistics cannot be viewed (in Oracle 8i, DBA_INDEXES will not show this either). Oracle may be generating artificial statistics and storing it somewhere for referring it later.

Creating Virtual Index

create unique index am304_u1 on am304(col2) nosegment;

(6)

This is a hidden/internal parameter and therefore undocumented. Such parameters should not be altered for Oracle databases unless Oracle Support either advises or recommends that you do so. In our case, we make an exception (!), but only to be set at session level. Do not set it for the complete instance.

Setting the "_use_nosegment_indexes" parameter enables the optimizer to use virtual indexes.

Examples:

Creating the virtual index:

create index vinew on tb(a1) nosegment; Checking some dictionary tables:

01. select segment_name, segment_type, bytes from dba_segments where segment_name = 'VINEW'; You will get a message no records found.

02. select object_name, object_type, status from dba_objects where object_name = 'VINEW'; OBJECT_NAME |OBJECT_TYPE |STATUS

--- -- |---VINEW |INDEX |VALID

03. select index_name, index_type, status from dba_indexes where index_name = 'VINEW'; INDEX_NAME |INDEX_TYPE |STATUS

--- |--- |---VINEW |NORMAL |VALID

Virtual Index will not prevent the creation of an index with the same column(s).

How to find out virtual index from database?

Virtual index can be created in oracle database which doesn't has any physical body and location. It can create with NOSEGMENT clause for testing purpose.

SQL> create table test11 (a number,b number); Table created.

SQL> create index v_test11 on test11(a) nosegment; Index created.

SQL> select index_name,owner from dba_indexes where index_name='V_TEST11' and owner='SYS'; no rows selected

SQL> select index_owner,index_name,column_name,table_name from dba_ind_columns 2 where index_owner='SYS' and index_name='V_TEST11';

INDEX_OWNER INDEX_NAME COLUMN_NAME TABLE_NAME SYS V_TEST11 A TEST11

SQL> select index_name from dba_ind_columns 2 minus

(7)

INDEX_NAME ---AAA_V V_T1_A V_TEST11 V_TEST1_A SQL> select owner,object_id 2 from dba_objects 3 where object_name='V_TEST11'; OWNER OBJECT_ID ---SYS 7718

SQL> select owner,object_id,object_type,status,temporary from dba_objects 2 where object_name='V_TEST11';

OWNER OBJECT_ID OBJECT_TYPE STATUS T

-SYS 7718 INDEX VALID N

SQL> create index test11_b on test11(b); Index created.

SQL>select object_name,object_id,object_type from dba_objects where object_type='INDEX' and owner='SYS' and object_name like '%TEST%'

OBJECT_NAM OBJECT_ID OBJECT_TYPE --- --- ---TEST11_B 7730 INDEX

V_TEST11 7718 INDEX

SQL> select obj#,ts#,file#,block#,type#,flags,property from ind$ where obj# in (7718,7730); OBJ# TS# FILE# BLOCK# TYPE# FLAGS PROPERTY

7730 0 1 15832 1 0 0 7718 0 0 0 1 4096 0

Above query shows that in the IND$ flags of virtual index is 4096 and for other index is 0. That means we can find out the VIRTUAL INDEX FROM following queries ONLY.

SQL> select index_name from dba_ind_columns 2 minus

3 select index_name from dba_indexes; AND

SQL> select obj#,ts#,file#,block#,type#,flags,property from ind$ where flags=4096

STATSPACK Statistics Level

"DBAs can change the amount of information or detail of statistics Statspack gathers by specifying a snapshot level. The level you choose dictates how much data Statspack collects. Level 5 is the default.

(8)

Level 0: Statspack collects general performance statistics such as wait statistics, system events, system statistics, rollback-segment data, row cache, SGA, background events, session events, lock statistics, buffer-pool statistics, and parent latch data.

Level 5: Statspack collects all the statistics it gathers at level 0 plus performance data about high-resource-usage SQL statements.

Level 10: Statspack collects all the statistics from level 0 and level 5 as well as child-latch information. At level 10, the snapshot can sometimes take longer to gather data because level 10 can be resource-intensive. You should use it only on the advice of Oracle personnel.

Levels 5 and 10 capture high-resource SQL statements that exceed any of the following four threshold parameters: the number of executions of the SQL statement (default = 100)

the number of disk reads the SQL statement performs (default = 1,000) the number of parse calls the SQL statement performs (default = 1,000) the number of buffer gets the SQL statement performs (default = 10,000)

If a SQL statement's resource usage exceeds any one of these threshold values, Statspack captures the statement when it takes a snapshot.\"

To specify the statistics level for a particular statspack snapshot, use the command; SQL> execute statspack.snap -

(i_snap_level=> statistics_level);

To change the default value for this and all future snapshots, use the command; SQL> execute statspack.snap -

(i_snap_level=> statistics_level, i_modify_parameter=> 'true');

Bear in mind though, that the default statistics level is actually 5 - which is usually enough to capture all the information you need (long running SQL queries, in my case). With Oracle 9i, level 6 stores the explain plans for these SQL statements, whilst with 9.2 level 7 gathers segment statistics. As the article says, only use 10 if you're asked to by Oracle Support.

Setting the Statistics Levels

In order for Oracle to collect those statistics, you must have proper initialization parameters set in the instance. The parameter is STATISTICS_LEVEL and is set in the init.ora. The good news is that this is modifiable via ALTER SYSTEM command and some underlying parameters are even modifiable via ALTER SESSION. This parameter can take three values: 1. BASIC: At this setting Oracle des not collect any stats. Although this is not recommended, you may decide to set this in a fine-tuned production system to save some overhead.

2. TYPICAL: This is the default value. In this setting, Oracle collects the following statistics.

 Buffer Cache ? These statistics advise the DBA how to tune the multiple buffer pools. The statistics can also be collected by setting another parameter DB_CACHE_ADVICE independently using initialization file, stored parameter file, ALTER SYSTEM or ALTER SESSION. If it's independently set, that setting takes preference over the statistics level setting.

 Mean Time to Recover ? These statistics help the DBA set an acceptable Mean Time to Recover (MTTR) setting, sometimes due to the requirements from Service Level Agreements with the users.

 Shared Pool Sizing ? Oracle can provide valuable clues to size the shared pool effectively based on the the usage and these statistics provide information on that.

(9)

occurring at each segment. We are interested in these statistics.

 PGA Target ? These statistics help tune the Program Global Area effectively based on the usage.

 Timed Statistics ? This is an old concept. The timed statistics were enabled in earlier versions with the initialization parameter timed_statistics. However, the statistic was so useful that Oracle made it default with the setting of statistic_level. It can be set independently, too; and if set, overrides the statistics_level setting.

3. ALL: In this setting, all of the above statistics are collected as well as an additional two.

Row Source Execution Stats ?These statistics help tune the sql statements by storing the execution statistics with the parser. This can provide an extremely useful tool in the development stages.

Timed OS Statistics ? Along with the timed statistics, if the operating system permits it, Oracle can also collect timed stats from the host. Certain operating systems like Unix allow it. It too can be set independently; and if set, overrides the statistics_level setting.

If you set these via any of the three methods, Initialization File, ALTER SYSTEM or ALTER SESSION, you can find out the current setting by querying the view V$STATISTICS_LEVEL as follows:

SELECT ACTIVATION_LEVEL, STATISTICS_NAME, SYSTEM_STATUS, SESSION_STATUS FROM V$STATISTICS_LEVEL ORDER BY ACTIVATION_LEVEL, STATISTICS_NAME;

So, set the STATISTICS_LEVEL to TYPICAL either by ALTER SYSTEM or by an initialization parameter file. Do not forget to restart the database if you choose the latter.

Table Partition Performance analysis

Collection of Statistics for Cost-Based Optimization/DBMS_STATS vs. ANALYZE

The cost-based approach relies on statistics and if the cost-based Approach is used , then statistics should be generated for all tables, clusters, and all types of indexes accessed by your SQL statements. If the size and data distribution of your tables change frequently, then generate statistics regularly to ensure the statistics accurately represent the data in the tables.

SELECT * FROM TEST PARTITION; This uses global statistics but no predicate

SELECT * FROM TEST S WHERE S.AMOUNT_OF_SHIFT > 1000;

This uses a predicate for more than one partition and may use global statistics SELECT * FROM TEST PARTITION (SEP2009) S WHERE S.AMOUNT_OF_SHIFT > 1000; This uses global statistics and predicate to one partition.

Gathering global statistics with the DBMS_STATS package is more useful because ANALYZE always runs serially. DBMS_STATS can run in serial or parallel. Whenever possible, DBMS_STATS calls a parallel query to gather statistics with the specified degree of parallelism; otherwise, it calls a serial query or the ANALYZE statement. Index statistics are not gathered in parallel.

ANALYZE gathers statistics for the individual partitions and then calculates the global statistics from the partition statistics. DBMS_STATS can gather separate statistics for each partition as well as global statistics for the entire table or index. Depending on the SQL statement being optimized, the optimizer may choose to use either the partition (or subpartition) statistics or the global statistics.

(10)

CREATE TABLE PARTTAB( ordid NUMBER, PARTCOL DATE, DETAILS NUMBER, AMOUNT NUMBER) PARTITION BY RANGE(PARTCOL)

SUBPARTITION BY HASH(DETAILS) SUBPARTITIONS 2

(PARTITION q1 VALUES LESS THAN(TO_DATE('01-04-2009','DD-MM-YYYY')) TABLESPACE TBLSPCE1, PARTITION q2 VALUES LESS THAN(TO_DATE('01-07-2009','DD-MM-YYYY')) TABLESPACE TBLSPCE2, PARTITION q3 VALUES LESS THAN(TO_DATE('01-10-2009','DD-MM-YYYY')) TABLESPACE TBLSPCE3, PARTITION q4 VALUES LESS THAN(TO_DATE('01-12-2009','DD-MM-YYYY')) TABLESPACE TBLSPCE4 );

A local non prefixed index will be associated with it: CREATE INDEX IDX_PARTTAB ON PARTTAB (ordid) LOCAL;

The PARTTAB table has been populated before to start the following examples.

GATHER_TABLE_STATS

Collects table, column, and index statistics.

Compute, serial mode, without histograms, Default granularity. SQL> execute

dbms_stats.gather_table_stats(->ownname => 'test',->tabname =>

'PARTTAB',->partname => null,- --> Gather stats on all partitions. >estimate_percent => null,- --> Compute mode

>block_sample => false,- --> Default value. No Sense in Compute mode

>method_opt => 'FOR ALL COLUMNS SIZE 1',- --> Table and columns statistics. No histogram generated >degree => null,- --> default parallel degree based on DOP set on PARTTAB.

>granularity => 'default',- --> Gather Global and Partition statistics >cascade => true ,- --> with index stats generated

>stattab => null,- --> The statistics will be stored in the dictionary. >statid =>

null,->statown => null);

PL/SQL procedure successfully completed.

Index Statistics won't be calculated by default if CASCADE=>TRUE .

SQL> select table_name, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, AVG_ROW_LEN , GLOBAL_STATS, USER_STATS, sample_size from user_tables

where table_name = 'PARTTAB';

TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN GLOBAL_STATS USER_STATS SAMPLE_SIZE

--- --- --- - --- - --- ---PARTTAB 400 8 0 0 11 YES NO 400

(11)

Now that the statistics have been updated.

The column GLOBAL_STATS has been also initialized.

SQL> select partition_name "Partition", NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, AVG_ROW_LEN, SAMPLE_SIZE, global_stats, user_stats

from user_tab_partitions where table_name = 'PARTTAB' order by partition_position /

Partition NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN GLOBAL_STATS USER_STATS SAMPLE_SIZE --- --- --- - --- - --- ---Q1 100 2 0 0 11 YES NO 100 Q2 100 2 0 0 11 YES NO 100 Q3 100 2 0 0 11 YES NO 100 Q4 100 2 0 0 11 YES NO 100

The statistics are again obtained at the table level with the GLOBAL_STATS .

SQL> select partition_name \"Partition\", subpartition_name \"Subpartition\", NUM_ROWS, BLOCKS, EMPTY_BLOCKS

SAMPLE_SIZE, global_stats, user_stats from user_tab_subpartitions

where table_name = 'PARTTAB'

order by partition_name, subpartition_position /

Partition Subpartition NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN SAMPLE_SIZE GLOBAL_STATS USER_STATS --- --- --- -- --- - - -- ---Q1 SYS_SUBP10365 NO NO Q1 SYS_SUBP10366 NO NO Q2 SYS_SUBP10367 NO NO Q2 SYS_SUBP10368 NO NO Q3 SYS_SUBP10369 NO NO Q3 SYS_SUBP10370 NO NO Q4 SYS_SUBP10371 NO NO Q4 SYS_SUBP10372 NO NO

The statistics aren't computed at the subpartition level which is in phase with the 'DEFAULT' granularity.

SQL>select COLUMN_NAME, NUM_DISTINCT, DENSITY, NUM_NULLS, NUM_BUCKETS, LAST_ANALYZED from user_tab_col_statistics where table_name = 'PARTTAB'

/

COLUMN_NAME NUM_DISTINCT DENSITY NUM_NULLS NUM_BUCKETS LAST_ANALYZED -- --- --- --- ---

---ORDID 0 0 400 1 12-DEC-02 PARTCOL 4 .25 0 1 12-DEC-02 DETAILS 100 .01 0 1 12-DEC-02 AMOUNT 0 0 400 1 12-DEC-02

(12)

statistics are well initialized

The same result is showed below on each partition columns:

SQL>select partition_name, COLUMN_NAME, NUM_DISTINCT, DENSITY, NUM_NULLS, NUM_BUCKETS, LAST_ANALYZED from user_part_col_statistics

where table_name = 'PARTTAB' /

PARTITION_ COLUMN_NAME NUM_DISTINCT DENSITY NUM_NULLS NUM_BUCKETS LAST_ANALYZED --- -- --- --- --- --- ---Q1 ORDID 0 0 100 1 12-DEC-07 Q1 PARTCOL 1 1 0 1 12-DEC-07 Q1 DETAILS 100 .01 0 1 12-DEC-07 Q1 AMOUNT 0 0 100 1 12-DEC-07 Q2 ORDID 0 0 100 1 12-DEC-07 Q2 PARTCOL 1 1 0 1 12-DEC-07 Q2 DETAILS 100 .01 0 1 12-DEC-07 Q2 AMOUNT 0 0 100 1 12-DEC-07 Q3 ORDID 0 0 100 1 12-DEC-07 Q3 PARTCOL 1 1 0 1 12-DEC-07 Q3 DETAILS 100 .01 0 1 12-DEC-07 Q3 AMOUNT 0 0 100 1 12-DEC-07 Q4 ORDID 0 0 100 1 12-DEC-07 Q4 PARTCOL 1 1 0 1 12-DEC-07 Q4 DETAILS 100 .01 0 1 12-DEC-07 Q4 AMOUNT 0 0 100 1 12-DEC-07

the statistics loaded for subpartitions of the PARTTAB table are displayed below:

SQL> select subpartition_name \"Subpartition\", COLUMN_NAME, NUM_DISTINCT, DENSITY, NUM_NULLS, NUM_BUCKETS from dba_subpart_col_statistics where table_name = 'PARTTAB'

order by column_name /

Subpartition COLUMN_NAME NUM_DISTINCT DENSITY NUM_NULLS NUM_BUCKETS ---- ---- - --- --- ---SYS_SUBP10365 PARTCOL SYS_SUBP10365 ORDID SYS_SUBP10365 DETAILS SYS_SUBP10365 AMOUNT SYS_SUBP10366 PARTCOL SYS_SUBP10366 ORDID SYS_SUBP10366 DETAILS SYS_SUBP10366 AMOUNT SYS_SUBP10367 PARTCOL SYS_SUBP10367 ORDID SYS_SUBP10367 DETAILS SYS_SUBP10367 AMOUNT SYS_SUBP10368 PARTCOL SYS_SUBP10368 ORDID SYS_SUBP10368 DETAILS SYS_SUBP10368 AMOUNT SYS_SUBP10369 PARTCOL SYS_SUBP10369 ORDID SYS_SUBP10369 DETAILS

(13)

SYS_SUBP10369 AMOUNT SYS_SUBP10370 PARTCOL SYS_SUBP10370 ORDID SYS_SUBP10370 DETAILS SYS_SUBP10370 AMOUNT SYS_SUBP10371 PARTCOL SYS_SUBP10371 ORDID SYS_SUBP10371 DETAILS SYS_SUBP10371 AMOUNT SYS_SUBP10372 PARTCOL SYS_SUBP10372 ORDID SYS_SUBP10372 DETAILS SYS_SUBP10372 AMOUNT

No statistics were loaded on subpartition's columns.

Here partitioned objects contain more than one sets of statistics. This is because statistics can be generated for the entire object, partition, or subpartition.

A Roadmap To Query Tuning

For each SQL statement, there are different approaches that could be used to retrieve the required data. Optimization is the process of choosing the most efficient way to retrieve this data based upon the evaluation of a number of different criteria. The CBO bases optimization choices on pre-gathered table and index statistics while the RBO makes it's decisions based on a set of rules and does not rely on any statistical information. CBO's reliance on statistics makes it vastly more flexible than the RBO since as long as up to date statistics are maintained, it will accurately reflect real data volumes. The RBO is de-supported in Oracle10g.

To gather 10046 trace at the session level: alter session set timed_statistics = true; alter session set statistics_level=all;

alter session set max_dump_file_size = unlimited;

alter session set events '10046 trace name context forever,level 12'; Features of DBMS_SUPPORT Package

In this article I have described undocumented feature within Oracle there is no guarantee that the results will be exactly as described for all releases .

Installing DBMS Package

---[oracle@localhost admin]$ ls -ltr *supp*

-rw-r--- 1 oracle oracle 1546 Feb 27 2001 dbmssupp.sql -rw-r--- 1 oracle oracle 1198 Sep 19 2005 prvtsupp.plb SQL> @$ORACLE_HOME/rdbms/admin/dbmssupp

- run your select(s) -

--SQL> exec DBMS_SUPPORT.START_TRACE; PL/SQL procedure successfully completed. SQL> /* Execute your query */

SQL> exec DBMS_SUPPORT.STOP_TRACE; PL/SQL procedure successfully completed.

(14)

Trace Output: System name: Linux

Node name: localhost.localdomain Release: 2.6.18-53.el5xen

Version: #1 SMP Sat Nov 10 19:46:12 EST 2007 Machine: x86_64

Instance name: orcl

Redo thread mounted by this instance: 1 Oracle process number: 16

Unix process pid: 4947, image: [email protected] (TNS V1-V3) *** 2008-01-21 12:00:25.204

*** SERVICE NAME:(SYS$USERS) 2008-01-21 12:00:25.204 *** SESSION ID:(158.3) 2008-01-21 12:00:25.204

=====================

PARSING IN CURSOR #6 len=198 dep=1 uid=0 oct=3 lid=0 tim=1172745727738352 hv=4125641360 ad='6c2b8cc0' select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null

and linkname is null and subname is null END OF STMT

PARSE #6:c=0,e=620,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1172745727738344 =====================

PARSING IN CURSOR #3 len=121 dep=2 uid=0 oct=3 lid=0 tim=1172745727740552 hv=3150898423 ad='6c1ddee0' select /*+ rule */ bucket, endpoint, col#, epvalue from histgrm$ where obj#=:1 and intcol#=:2 and row#=:3 order by bucket END OF STMT PARSE #3:c=0,e=587,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=3,tim=1172745727740544 EXEC #3:c=0,e=2148,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=3,tim=1172745727742876 ===================== --More--(80%)

==---How to interpret the internal trace output

STAT Lines report explain plan statistics for the numbered <CURSOR>. PARSE Parse a statement

EXEC Execute a pre-parsed statement. FETCH Fetch rows from a cursor.

-This is a very short brief explanation for Interpreting and investigating query parsing ,wait events.

Understanding the SCN

In order to understand how Oracle performs recovery, it’s first necessary to understand Oracle’s SCN in terms of the various places where it can be stored and how it’s used for instance and media recovery.

The SCN is an internal number maintained by the database management system (DBMS) to log changes made to a database. The SCN increases over time as changes are made to the database by Structured Query Language (SQL). By understanding how the SCN is used, you can understand how Oracle recovery works. Oracle9i enables you to examine the current SCN using the following SQL:

SQL> select dbms_flashback.get_system_change_number from dual;

Whenever an application commits a transaction, the log writer process (LGWR) writes records from the redo log buffers in the System Global Area (SGA) to the online redo logs on disk. LGWR also writes the transaction’s SCN to

(15)

the online redo log file. The success of this atomic write event determines whether your transaction succeeds, and it requires a synchronous (wait-until-completed) write to disk.

Note: The need for a synchronous write upon commit is one of the reasons why the online redo log can become a bottleneck for applications and why you should commit as infrequently as is practical. In general, Oracle writes asynchronously to the database datafiles for performance reasons, but commits require a synchronous write because they must be guaranteed at the time they occur.

SCN and Checkpoints:

A checkpoint occurs when all modified database buffers in the Oracle SGA are written out to datafiles by the database writer (DBWn) process. The checkpoint process (CKPT) updates all datafiles and control files with the SCN at the time of the checkpoint and signals DBWn to write out the blocks. A successful checkpoint guarantees that all database changes up to the checkpoint SCN have been recorded in the datafiles. As a result, only those changes made after the checkpoint need to be applied during recovery. Checkpoints occur automatically as follows:

 Whenever a redo log switch takes place

 Whenever the time set by the LOG_CHECKPOINT_TIMEOUT initialization parameter is reached

 Whenever the amount of redo written reaches the number of bytes associated with the LOG_CHECKPOINT_INTERVAL

Typically, LOG_CHECKPOINT_INTERVAL is chosen so that checkpoints only occur on log switches. Oracle stores the SCN associated with the checkpoint in four places: three of them in the control file and one in the datafile header for each datafile.

The System Checkpoint SCN:

After a checkpoint completes, Oracle stores the system checkpoint SCN in the control file. You can access the checkpoint SCN using the following SQL:

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE# 292767

The Datafile Checkpoint SCN:

After a checkpoint completes, Oracle stores the SCN individually in the control file for each datafile. The following SQL shows the datafile checkpoint SCN for a single datafile in the control file:

SQL> select name,checkpoint_change# from v$datafile where name like '%users01%'; NAME CHECKPOINT_CHANGE#

--- ---/u02/oradata/OMFD1/users01.dbf 292767 The Start SCN:

Oracle stores the checkpoint SCN value in the header of each datafile. This is referred to as the start SCN because it is used at instance startup time to check if recovery is required. The following SQL shows the checkpoint SCN in the datafile header for a single datafile:

SQL> select name,checkpoint_change# from v$datafile_header where name like '%users01%'; NAME CHECKPOINT_CHANGE#

(16)

---/u02/oradata/OMFD1/users01.dbf 292767

The Stop SCN:

The stop SCN is held in the control file for each datafile. The following SQL shows the stop SCN for a single datafile when the database is open for normal use:

SQL> select name,last_change# from v$datafile where name like '%users01%';

NAME LAST_CHANGE# --- ---/u02/oradata/OMFD1/users01.dbf

During normal database operation, the stop SCN is NULL for all datafiles that are online in read-write mode. SCN Values while the Database Is Up Following a checkpoint while the database is up and open for use, the system checkpoint in the control file, the datafile checkpoint SCN in the control file, and the start SCN in each datafile header all match. The stop SCN for each datafile in the control file is NULL. SCN after a Clean Shutdown After a clean database shutdown resulting from a SHUTDOWN IMMEDIATE or SHUTDOWN NORMAL of the database, followed by STARTUP MOUNT, the previous queries on v$database and v$datafile return the following:

CHECKPOINT_CHANGE# 293184

SQL> select name,checkpoint_change#,last_change# from v$datafile where name like '%user%';

NAME CHECKPOINT_CHANGE# LAST_CHANGE# --- ---

---/u02/oradata/OMFD1/users01.dbf 293184 293184

--- ---/u02/oradata/OMFD1/users01.dbf 293184

During a clean shutdown, a checkpoint is performed and the stop SCN for each datafile is set to the start SCN from the datafile header. Upon startup, Oracle checks the start SCN in the file header with the datafile checkpoint SCN. If they match, Oracle checks the start SCN in the datafile header with the datafile stop SCN in the control file. If they match, the database can be opened because all block changes have been applied, no changes were lost on shutdown, and therefore no recovery is required on startup. After the database is opened, the datafile stop SCN in the control file once again changes to NULL to indicate that the datafile is open for normal use.

SCN after an Instance Crash

The previous example showed the behavior of the SCN after a clean shutdown. To demonstrate the behavior of the checkpoints after an instance crash, the following SQL creates a table (which performs an implicit commit) and inserts a row of data into it without a commit:

create table x(x number) tablespace users; insert into x values(100);

If the instance is crashed by using SHUTDOWN ABORT, the previous queries on v$database and v$datafile return the following after the database is started up in mount mode:

SQL> select checkpoint_change# from v$database; CHECKPOINT_CHANGE#

(17)

293185

SQL> select name,checkpoint_change#,last_change# from v$datafile where name like '%users01%'; NAME CHECKPOINT_CHANGE# LAST_CHANGE#

--- --- ---/u02/oradata/OMFD1/users01.dbf 293185

--- ---/u02/oradata/OMFD1/users01.dbf 293185

In this case, the stop SCN is not set, which is indicated by the NULL value in the LAST_CHANGE# column. This information enables Oracle, at the time of the next startup, to determine that the instance crashed because the checkpoint on shutdown was not performed. If it had been performed, the LAST_CHANGE# and

CHECKPOINT_CHANGE# values would match for each datafile as they did during a clean shutdown. If an instance crashes at shutdown, then instance crash recovery is required the next time the instance starts up.

Recovery from an Instance Crash

Upon the next instance startup that takes place after SHUTDOWN ABORT or a DBMS crash, the Oracle DBMS detects that the stop SCN for datafiles is not set in the control file during startup. Oracle then performs crash recovery. During crash recovery, Oracle applies redo log records from the online redo logs in a process referred to as roll forward to ensure that all transactions committed before the crash are applied to the datafiles. Following roll forward, active transactions that did not commit are identified from the rollback segments and are undone before the blocks involved in the active transactions can be accessed. This process is referred to as roll back. In our example, the following transaction was active but not committed at the time of the SHUTDOWN ABORT, so it needs to be rolled back:

SQL> insert into x values(100);

After instance startup, the X table exists, but remains empty. Instance recovery happens automatically at database startup without database administrator (DBA) intervention. It may take a while because of the need to apply large amounts of outstanding redo changes to data blocks for transactions that completed and those that didn’t complete and require roll back.

Recovery from a Media Failure

Up until this point, the checkpoint start SCN in the datafile header has always matched the datafile checkpoint SCN number held in the control file. This is reasonable because during a checkpoint, the datafile checkpoint SCN in the control file and the start SCN in the datafile header are both updated, along with the system checkpoint SCN. The following SQL shows the start SCN from the datafile header and datafile checkpoint SCN from the control file for the same file:

SQL> select 'controlfile' "SCN location",name,checkpoint_change# from v$datafile where name like '%users01%' union

select 'file header',name,checkpoint_change# from v$datafile_header where name like '%users01%'; SCN location NAME CHECKPOINT_CHANGE#

--- --- ---controlfile /u02/oradata/OMFD1/users01.dbf 293188 file header /u02/oradata/OMFD1/users01.dbf 293188

Unlike the v$datafile view, there is no stop SCN column in the v$datafile_header view because v$datafile_header is not used at instance startup time to indicate that an instance crash occurred. However, the v$datafile_header does provide the Oracle DBMS with the information it requires to perform media recovery. At instance startup, the datafile checkpoint SCN in the control file and the start SCN in the datafile header are checked for equality. If they don’t match, it is a signal that media recovery is

(18)

required.

For example, media recovery is required if a media failure has occurred and the original datafile has been replaced with a backup copy. In this case, the start SCN in the backup copy is less than the checkpoint SCN value in the control file, and Oracle requests archived redo logs—generated at the time of previous log switches—in order to reapply the changes required to bring the datafile up to the current point in time.

In order to recover the database from a media failure, you must run the database in ARCHIVELOG mode to ensure that all database changes from the online redo logs are stored permanently in archived redo log files. In order to enable ARCHIVELOG mode, you must run the command ALTERDATABASE ARCHIVELOG when the database is in a mounted state.

You can identify files that need recovery after you have replaced a datafile with an older version by starting the instance in mount mode and running the following SQL:

SQL> select file#,change# from v$recover_file; FILE# CHANGE#

4 313401

In this example, file 4 is the datafile in the USERS tablespace. By reexecuting the previous SQL to display the datafile checkpoint SCN in the control file and the start SCN in the datafile header, you can see that the start SCN is older due to the restore of the backup datafile that has taken place:

SQL> select 'controlfile' "SCN location",name,checkpoint_change# from v$datafile where name like '%users01%'

union

select 'file header',name,checkpoint_change#

from v$datafile_header where name like '%users01%';

SCN location NAME CHECKPOINT_CHANGE# --- --- ---controlfile /u02/oradata/OMFD1/users01.dbf 313551 file header /u02/oradata/OMFD1/users01.dbf 313401

If you were to attempt to open the database, you would receive errors like the following: ORA-01113: file 4 needs media recovery

ORA-01110: datafile 4: '/u02/oradata/OMFD1/users01.dbf'

You can recover the database by issuing RECOVER DATABASE from SQL*Plus while the database is in a mounted state. If the changes needed to recover the database to the point in time before the crash are in an archived redo log, then you will be prompted to accept the suggested name:

ORA-00279: change 313401 generated at 11/10/2001 18:50:23 needed for thread ORA-00289: suggestion : /u02/oradata/OMFD1/arch/T0001S0000000072.ARC ORA-00280: change 313401 for thread 1 is in sequence #72

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

If you respond to the prompt using AUTO, Oracle applies any archived redo logs it needs, followed by any

necessary changes in the online redo logs, to bring the database right up to the last committed transaction before the media failure that caused the requirement for the restore.

So far, we’ve considered recovery scenarios where the goal is to recover the database to the most recent

transaction. This is known as complete recovery. The RECOVER DATABASE command has several other options that enable you to recover from a backup to a point in time before the most recent transaction by rolling forward and then stopping the application of the redo log changes at a specified point. This is known as incomplete recovery. You can specify a time or an SCN as the recovery point. For example,

recover database until time '2001-11-10:18:52:00'; recover database until change 313459;

Before you perform incomplete recovery, it’s recommended that you restore a complete database backup first. After incomplete recovery, you must open the mounted database with ALTER DATABASE OPEN RESETLOGS. This creates a new incarnation of the database and clears the contents of the existing redo logs to make sure they can’t be applied.

(19)

In the previous example, we had access to a current control file at the time of the media failure. This means that none of the start SCN values in the datafile headers exceeded the system checkpoint SCN number in the control file. To recap, the system checkpoint number is given by the following:

You might be wondering why Oracle needs to maintain the last system checkpoint value in the control file as well as checkpoint SCNs in the control file for each datafile (as used in the previous example). There are two reasons for this. The first is that you might have read-only tablespaces in your database. In this case, the database checkpoint SCN increases, and the checkpoint SCN for the datafiles in the read-only tablespace remains frozen in the control file.

The following SQL report output shows a database with a read-write tablespace (USERS) and read-only tablespace (TEST). The start SCN in the file header and the checkpoint SCN in the control file for TEST are less than the system checkpoint value. Once a tablespace is read only, checkpoints have no effect on the files in it. The other read-write tablespace has checkpoint values that match the system checkpoint:

SCN location NAME CHECKPOINT_CHANGE# ---- --

---controlfile SYSTEM checkpoint 355390

file header /u02/oradata/OD2/users01.dbf 355390

file in controlfile /u02/oradata/OD2/users01.dbf 355390

file header /u02/oradata/OD2/test01.dbf 355383

file in controlfile /u02/oradata/OD2/test01.dbf 355383

The second reason for the maintenance of multiple checkpoint SCNs in the control file is that you might not have a current control file available at recovery time. In this case, you need to restore an earlier control file before you can perform a recovery. The system checkpoint in the control file may indicate an earlier change than the start SCN in the datafile headers. The following SQL shows an example where the system checkpoint SCN and datafile checkpoint SCN indicate an earlier change than the start SCN in the datafile header: SQL> select 'controlfile' "SCN location",'SYSTEM checkpoint' name,checkpoint_change# from v$database union select 'file in controlfile',name,checkpoint_change# from v$datafile where name like 'users01%' union select 'file header',name,checkpoint_change# from v$datafile_header where name like '%users01%'; SCN location NAME CHECKPOINT_CHANGE# - --- ---controlfile SYSTEM checkpoint 333765

file header /u02/oradata/OD2/users01.dbf 355253

file in controlfile /u02/oradata/OD2/users01.dbf 333765

If try you to recover a database in the usual way in this situation, Oracle detects that the control file is older than some of the datafiles, as indicated by the checkpoint SCN values in the datafile headers, and reports the following message:

SQL> recover database

ORA-00283: recovery session canceled due to errors

ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

If you want to proceed with recovery in this situation, you need to indicate to Oracle that a noncurrent control file— possibly containing mismatches in the SCN values identified by the previous error messages—is about to be specified for recovery by using the following command:

recover database using BACKUP CONTROLFILE;

Overview : Table’s Fragmentation Audience : DBA’s

(20)

What is Table fragmentation?

When rows are not stored contiguously, or if rows are split onto more than one page, performance decreases because these rows require additional page accesses. Table fragmentation is distinct from file fragmentation. When lots of DML operation apply on tables then tables is fragmented.

because DML is not release free space from table below HWM.

Hint: HWM is indicator for USED BLOCKS in database. Blocks below the high water mark (used blocks) have at least once contained data. This data might have been deleted.

Since Oracle knows that blocks beyond the high water mark don't have data, it only reads blocks up to the high water mark in a full table scan.

DDL statement always reset HWM. How to find table fragmentation? SQL> select count(*) from big1; 1000000 rows selected.

SQL> delete from big1 where rownum <= 300000; 300000 rows deleted.

SQL> commit; Commit complete.

SQL> update big1 set object_id = 0 where rownum <=350000; 342226 rows updated.

SQL> exec dbms_stats.gather_table_stats('SCOTT','BIG1'); PL/SQL procedure successfully completed.

Table Size ( with fragmented)

SQL> select table_name,round((blocks*8),2)||'kb' "size" 2 from user_tables

3 where table_name = 'BIG1'; TABLE_NAME size

--- ---BIG1 72952kb

Actual data in table

SQL> select table_name,round((num_rows*avg_row_len/1024),2)||'kb' "size" 2 from user_tables

3 where table_name = 'BIG1'; TABLE_NAME size

--- ---BIG1 30604.2kb

(21)

The difference between two values is 60% and Pctfree 10% (default) so table is 50% extra space which is wasted because there is no data.

How to reset HWM / remove fragemenation? For that we need to reorganize fragmented table We have four options to reorganize fragmented tables 1. alter table … move + rebuild indexes

2. export / truncate / import 3. create table as select ( CTAS) 4. dbms_redefinition

Option: 1 “alter table … move + rebuild indexes” SQL> alter table BIG1 move;

Table altered.

SQL> select status,index_name from user_indexes 2 where table_name = 'BIG1';

STATUS INDEX_NAME

--- ---UNUSABLE BIGIDX

SQL> alter index bigidx rebuild; Index altered.

SQL> select status,index_name from user_indexes 2 where table_name = 'BIG1';

STATUS INDEX_NAME

--- ---VALID BIGIDX

SQL> exec dbms_stats.gather_table_stats('SCOTT','BIG1'); PL/SQL procedure successfully completed.

SQL> select table_name,round((blocks*8),2)||'kb' "size" 2 from user_tables

3 where table_name = 'BIG1'; TABLE_NAME size

--- ---BIG1 38224kb

(22)

2 from user_tables

--- ---BIG1 30727.37kb

Option: 2 “Create table as select” SQL> create table big2 as select * from big1; Table created.

SQL> drop table big1 purge; Table dropped.

SQL> rename big2 to big1; Table renamed.

SQL> select table_name,round((blocks*8),2)||'kb' "size" 2 from user_tables

--- ---BIG1 85536kb

SQL> select table_name,round((num_rows*avg_row_len/1024),2)||'kb' "size" 2 from user_tables

--- ---BIG1 68986.97kb

SQL> select status from user_indexes 2 where table_name = 'BIG1'; no rows selected

SQL> -- Note we need to create all indexes. Option: 3 “export / truncate / import”

SQL> select table_name, round((blocks*8),2)||'kb' "size" 2 from user_tables

(23)

--- ---BIG1 85536kb

SQL> select table_name, round((num_rows*avg_row_len/1024),2)||'kb' "size" 2 from user_tables

--- ---BIG1 42535.54kb

SQL> select status from user_indexes where table_name = 'BIG1'; STATUS

---VALID SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Pr oduction

With the Partitioning, OLAP and Data Mining options C:>exp scott/tiger@Orcl file=c:big1.dmp tables=big1

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Produc tion

With the Partitioning, OLAP and Data Mining options

Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set About to export specified tables via Conventional Path ...

. . exporting table BIG1 468904 rows exported Export terminated successfully without warnings. C:>sqlplus scott/tiger@orcl

Connected to:

(24)

With the Partitioning, OLAP and Data Mining options SQL> truncate table big1;

Table truncated. SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Pr oduction

With the Partitioning, OLAP and Data Mining options C:>imp scott/tiger@Orcl file=c:big1.dmp ignore=y

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Produc tion

With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.01.00 via conventional path

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set . importing SCOTT's objects into SCOTT

. . importing table "BIG1" 468904 rows imported Import terminated successfully without warnings. C:>sqlplus scott/tiger@orcl

Connected to:

Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production With the Partitioning, OLAP and Data Mining options

--- ---BIG1 85536kb

(25)

TABLE_NAME size

--- ---BIG1 42535.54kb

--- ---BIG1 51840kb

--- ---BIG1 42542.27kb

SQL> select status from user_indexes where table_name = 'BIG1'; STATUS

---VALID

SQL> exec dbms_redefinition.can_redef_table('SCOTT','BIG1',-> dbms_redefinition.cons_use_pk);

PL/SQL procedure successfully completed. Option: 4 “dbms_redefinition”

SQL> create table TABLE1 ( 2 no number,

3 name varchar2(20) default 'NONE', 4 ddate date default SYSDATE); Table created.

SQL> alter table table1 add constraint pk_no primary key(no); Table altered.

(26)

2 for x in 1..100000 loop

3 insert into table1 ( no , name, ddate) 4 values ( x , default, default);

5 end loop; 6 end; 7 /

PL/SQL procedure successfully completed. SQL> create or replace trigger tri_table1 2 after insert on table1

3 begin 4 null; 5 end; 6 /

Trigger created.

SQL> select count(*) from table1; COUNT(*)

---100000

SQL> delete table1 where rownum <= 50000; 50000 rows deleted.

SQL> exec dbms_stats.gather_table_stats('SCOTT','TABLE1'); PL/SQL procedure successfully completed.

3 where table_name = 'TABLE1'; TABLE_NAME size

--- ---TABLE1 2960kb

(27)

TABLE_NAME size

--- ---TABLE1 822.69kb

SQL> --Minimum Privs required "DBA" role or "SELECT" on dbms_redefinition pkg SQL> --First check table is condidate for redefinition.

SQL>

SQL> exec sys.dbms_redefinition.can_redef_table('SCOTT',->

'TABLE1',-> sys.dbms_redefinition.cons_use_pk); PL/SQL procedure successfully completed.

SQL> --After verifying that the table can be redefined online, you manually crea te an empty interim table (in the same schema as the table to be redefined) SQL>

SQL> create table TABLE2 as select * from table1 WHERE 1 = 2; Table created.

SQL> exec sys.dbms_redefinition.start_redef_table ( 'SCOTT',->

'TABLE1',-> 'TABLE2');

SQL> --This procedure keeps the interim table synchronized with the original tab le.

SQL>

SQL> exec sys.dbms_redefinition.sync_interim_table ('SCOTT',->

SQL> --Create PRIMARY KEY on interim table(TABLE2) SQL> alter table TABLE2

2 add constraint pk_no1 primary key (no); Table altered.

SQL> create trigger tri_table2 2 after insert on table2 3 begin

(28)

4 null; 5 end; 6 /

Trigger created.

SQL> --Disable foreign key on original table if exists before finish this proces s.

SQL>

SQL> exec sys.dbms_redefinition.finish_redef_table ( 'SCOTT',->

SQL> exec dbms_stats.gather_table_stats('SCOTT','TABLE1'); PL/SQL procedure successfully completed.

--- ---TABLE1 1376kb

--- ---TABLE1 841.4kb

SQL> select status,constraint_name 2 from user_constraints

3 where table_name = 'TABLE1'; STATUS CONSTRAINT_NAME --- ---ENABLED PK_NO1

SQL> select status ,trigger_name 2 from user_triggers

(29)

3 where table_name = 'TABLE1'; STATUS TRIGGER_NAME

--- ---ENABLED TRI_TABLE2

SQL> drop table TABLE2 PURGE; Table dropped.

Slow Running SQL results in Oracle performance degradation ChunPei Feng & R. Wang

Environment

Oracle 9iR2 and Unix, production database and standby database Circumstance

In the morning, routine daily database checking shows that the database has an unusual heavy load. As DBA, definitely, the first checking step is to monitor the top OS processes with command TOP or PRSTAT, which offer an ongoing look at processor activity in real time. In this case, however, a list of the most CPU-intensive processes on the system does not tell us anything special which might particularly cause the database performance degradation. Next, information fetching about TOP SQL and long-running SQL also fail to figure out the possible reason of this performance problem.

Also, the team of application development confirms that no change has been made at the application level. And, application log doesn¡¯t show exception on heavy jobs and excessive user logon.

According to the information above, it can be concluded that the corrupt database performance is caused by issues relating to the database server.

Steps to diagnose:

1. Check and Compare Historical Statspack Reports

So far, no one is able to tell which job attributes to performance degradation because hundreds of processes, which reside on tens of Unix servers, make DBAs difficult to track process by process. Here, the more feasible action is to recur to Statspack, which provides a great deal of performance information about an Oracle database. By keeping historical Statspack reports, it makes possible to compare current Statspack report to the one in last week. The report, generated at peak period (9:00AM - 10:00AM), is sampled to compare to one of report created in last week at same period.

Upon comparison, the instant finding is that CPU time is increased by 1,200 (2341 vs. 1175) seconds. Usually, the significant increase on CPU time very likely attribute to the following two scenarios:

 More jobs loaded

 The execution plan of SQLs is changed Top 5 Timed Events in Statspack Reports ~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 Current Stataspack Report

(30)

Time(s)

--- --- --- ---CPU time 2,341 42.60 db file sequential read 387,534 2,255 41.04 global cache cr request 745,170 231 4.21 log file sync 98,041 229 4.17 log file parallel write 96,264 158 2.88

 Statspack Report in Last Week

Event Waits

% Total Time(s)

Ela Time

--- --- --- ---db file sequential read 346,851 1,606 47.60 CPU time 1,175 34.83 global cache cr request 731,368 206 6.10 log file sync 90,556 91 2.71 db file scattered read 37,746 90 2.66

2. Narrow down by examining SQL Part of Statspack Reports

Next, we examine the SQL part of Statspack report and find the following SQL statement (Query 1) is listed at the very beginning of ¡°Buffer Gets part. It tells us that this SQL statement is the consumer of 1161.27 seconds¡¯ CPU Time. In last week¡¯s report, no information about this SQL statement has been reported at the very beginning part. And, it only took 7.39 seconds to be finished. It¡¯s obvious that this SQL statement must be one of the attributors of performance degradation.

SELECT login_id, to_char(gmt_create, 'YYYY-MM-DD HH24:MI:SS') from IM_BlackList where black_id = :b1

Query 1: Query on table IM_BlackList with bind variable

 SQL Part of Statspack Report ~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 Current Stataspack Report

Buffer Gets Executions Gets per Exec %Total CPU Times (s) Elapsd Times (s) Hash Value --- --- --- --- --- --- ---17,899,606 47,667 375.5 55.6 1161.27 1170.22 3481369999 Module: /home/oracle/AlitalkSrv/config/../../AlitalkSrv/

(31)

from IM_BlackList where black_id = :b1  Statspack Report in Last Week

Buffer Gets Executions Gets per Exec %Total CPU Times (s)

Elapsd

Times (s) Hash Value --- --- --- --- --- --- ---107,937 47,128 2.3 0.8 7.39 6.94 3481369999 Module: /home/oracle/AlitalkSrv/config/../../AlitalkSrv/

SELECT login_id, to_char(gmt_create, 'YYYY-MM-DD HH24:MI:SS') from IM_BlackList where black_id = :b1

Now, our investigation has been significantly narrowed down to single SQL statement. That is

SELECT login_id, to_char(gmt_create, 'YYYY-MM-DD HH24:MI:SS') from IM_BlackList where black_id = :b1

This is a typical SQL query with binding variable and it should benefit from b-tree index created. But, the statistics show that it seems conduct full table scan rather than using proper index.

The following checking on index of field black_id in table IM_BlackList clearly demonstrates the availability of the index.

SQL> select index_name,column_name from user_ind_columns where table_name = 'IM_BLACKLIST'; IM_BLACKLIST_PK LOGIN_ID

IM_BLACKLIST_PK BLACK_ID IM_BLACKLIST_LID_IND BLACK_ID

The question now is, how come full table scan replace usage of index for this SQL statement? In order to testify our supposition, we simply execute this SQL statement against production database and it¡¯s clear that full table scan is conducted rather than index access.

3. Go Check Histograms generated by Objects Analyzing

To figure out the problem, we then check histograms on the field of BLACK_ID against standby database. That¡¯s also a comparison practice between production database and standby database. Because the activity of gathering statistics does happen on production database, but not on standby database, we are hoping to find some difference between the histograms on the filed BLACK_ID and then to measure the impact of statistics collecting. We select histograms as criteria because histograms is cost-based optimizer (CBO) feature that allows Oracle to see the possible number of values of a particular column, which is known as data skewing, and histograms can track the number of occurrences of a particular data values when CBO decide on what type of index to use or even whether to use an index.

To gather histograms information against standby database, we run:

SQL> select COLUMN_NAME ,ENDPOINT_NUMBER, ENDPOINT_VALUE , from dba_histograms where table_name = 'IM_BLACKLIST' and column_name = 'BLACK_ID';

Query 2:gather histograms information from dba_hisrograms Then, we get:

COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE

(32)

---BLACK_ID 0 2.5031E+35 BLACK_ID 1 2.5558E+35 BLACK_ID 2 2.8661E+35 BLACK_ID 3 5.0579E+35 BLACK_ID 4 5.0585E+35 BLACK_ID 5 5.0585E+35 BLACK_ID 6 5.0589E+35 BLACK_ID 7 5.0601E+35 BLACK_ID 8 5.1082E+35 BLACK_ID 9 5.1119E+35 BLACK_ID 10 5.1615E+35 BLACK_ID 11 5.1616E+35 BLACK_ID 12 5.1628E+35 BLACK_ID 13 5.1646E+35 BLACK_ID 14 5.2121E+35 BLACK_ID 15 5.2133E+35 BLACK_ID 16 5.2155E+35 BLACK_ID 17 5.2662E+35 BLACK_ID 18 5.3169E+35 BLACK_ID 19 5.3193E+35 BLACK_ID 20 5.3686E+35 BLACK_ID 21 5.3719E+35 BLACK_ID 22 5.4198E+35 BLACK_ID 23 5.4206E+35 BLACK_ID 24 5.4214E+35 BLACK_ID 25 5.4224E+35 BLACK_ID 26 5.4238E+35 BLACK_ID 27 5.4246E+35 BLACK_ID 28 5.4743E+35 BLACK_ID 29 5.5244E+35 BLACK_ID 30 5.5252E+35 BLACK_ID 31 5.5252E+35

(33)

BLACK_ID 32 5.5272E+35 BLACK_ID 33 5.5277E+35 BLACK_ID 34 5.5285E+35 BLACK_ID 35 5.5763E+35 BLACK_ID 36 5.6274E+35 BLACK_ID 37 5.6291E+35 BLACK_ID 38 5.6291E+35 BLACK_ID 39 5.6291E+35 BLACK_ID 40 5.6291E+35 BLACK_ID 41 5.6305E+35 BLACK_ID 42 5.6311E+35 BLACK_ID 43 5.6794E+35 BLACK_ID 44 5.6810E+35 BLACK_ID 45 5.6842E+35 BLACK_ID 46 5.7351E+35 BLACK_ID 47 5.8359E+35 BLACK_ID 48 5.8887E+35 BLACK_ID 49 5.8921E+35 BLACK_ID 50 5.9430E+35 BLACK_ID 51 5.9913E+35 BLACK_ID 52 5.9923E+35 BLACK_ID 53 5.9923E+35 BLACK_ID 54 5.9931E+35 BLACK_ID 55 5.9947E+35 BLACK_ID 56 5.9959E+35 BLACK_ID 57 6.0428E+35 BLACK_ID 58 6.0457E+35 BLACK_ID 59 6.0477E+35 BLACK_ID 60 6.0479E+35 BLACK_ID 61 6.1986E+35 BLACK_ID 62 6.1986E+35 BLACK_ID 63 6.1994E+35

(34)

BLACK_ID 64 6.2024E+35 BLACK_ID 65 6.2037E+35 BLACK_ID 66 6.2521E+35 BLACK_ID 67 6.2546E+35 BLACK_ID 68 6.3033E+35 BLACK_ID 69 6.3053E+35 BLACK_ID 70 6.3069E+35 BLACK_ID 71 6.3553E+35 BLACK_ID 72 6.3558E+35 BLACK_ID 73 6.3562E+35 BLACK_ID 74 6.3580E+35 BLACK_ID 75 1.1051E+36

Output 1: Histograms data on standby database

Subsequently, then same command has been executed against production database. The output looks like followings:

COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE

--- --- ---BLACK_ID 0 1.6715E+35 BLACK_ID 1 2.5558E+35 BLACK_ID 2 2.7619E+35 BLACK_ID 3 2.9185E+35 BLACK_ID 4 5.0579E+35 BLACK_ID 5 5.0589E+35 BLACK_ID 6 5.0601E+35 BLACK_ID 7 5.1100E+35 BLACK_ID 8 5.1601E+35 BLACK_ID 9 5.1615E+35 BLACK_ID 10 5.1624E+35 BLACK_ID 11 5.1628E+35 BLACK_ID 12 5.1642E+35 BLACK_ID 13 5.2121E+35 BLACK_ID 14 5.2131E+35

(35)

BLACK_ID 15 5.2155E+35 BLACK_ID 16 5.2676E+35 BLACK_ID 17 5.3175E+35 BLACK_ID 18 5.3684E+35 BLACK_ID 19 5.3727E+35 BLACK_ID 20 5.4197E+35 BLACK_ID 21 5.4200E+35 BLACK_ID 22 5.4217E+35 BLACK_ID 23 5.4238E+35 BLACK_ID 24 5.4244E+35 BLACK_ID 25 5.4755E+35 BLACK_ID 26 5.5252E+35 BLACK_ID 27 5.5252E+35 BLACK_ID 28 5.5252E+35 BLACK_ID 29 5.5283E+35 BLACK_ID 30 5.5771E+35 BLACK_ID 31 5.6282E+35 BLACK_ID 32 5.6291E+35 BLACK_ID 33 5.6291E+35 BLACK_ID 34 5.6291E+35 BLACK_ID 35 5.6299E+35 BLACK_ID 36 5.6315E+35 BLACK_ID 37 5.6794E+35 BLACK_ID 38 5.6798E+35 BLACK_ID 39 5.6816E+35 BLACK_ID 40 5.6842E+35 BLACK_ID 41 5.7838E+35 BLACK_ID 42 5.8877E+35 BLACK_ID 43 5.8917E+35 BLACK_ID 44 5.9406E+35 BLACK_ID 45 5.9909E+35 BLACK_ID 46 5.9923E+35

(36)

BLACK_ID 47 5.9923E+35 BLACK_ID 48 5.9946E+35 BLACK_ID 49 5.9950E+35 BLACK_ID 50 5.9960E+35 BLACK_ID 51 5.9960E+35 BLACK_ID 52 5.9960E+35 BLACK_ID 53 5.9960E+35 BLACK_ID 54 5.9960E+35 BLACK_ID 55 5.9960E+35 BLACK_ID 56 5.9960E+35 BLACK_ID 57 6.0436E+35 BLACK_ID 58 6.0451E+35 BLACK_ID 59 6.0471E+35 BLACK_ID 60 6.1986E+35 BLACK_ID 61 6.1998E+35 BLACK_ID 62 6.2014E+35 BLACK_ID 63 6.2037E+35 BLACK_ID 64 6.2521E+35 BLACK_ID 65 6.2544E+35 BLACK_ID 66 6.3024E+35 BLACK_ID 67 6.3041E+35 BLACK_ID 68 6.3053E+35 BLACK_ID 69 6.3073E+35 BLACK_ID 70 6.3558E+35 BLACK_ID 71 6.3558E+35 BLACK_ID 72 6.3558E+35 BLACK_ID 73 6.3558E+35 BLACK_ID 74 6.3580E+35 BLACK_ID 75 1.1160E+36

Output 2: Histograms data on production database

Comparing to the value of histograms derived from standby database, we find that the histograms values on production database is not distributed evenly as that on standby database. The exception occurred in range of line

(37)

50 -56 and line 70-73. That¡¯s important finding because histograms are used to predict cardinality and cardinality is the key measure in using B-tree index or bitmap index. The difference of histograms may be the most direct cause for this performance problem we¡¯re facing.

4. Trace with event 10053

We then analyze the ¡°10053 event and try to get more information to figure out this problem. And, this operation is also done against both standby database and production database.

To enable trace with event 10053, we run:

alter session set events '10053 trace name context forever'; And then, we rerun Query 1.

The comparison of these two 10053 trace files, as shown in color red in Output 3 and Output 4, shows that the cost of full table scan are all 38. The difference is that index access cost is jumped from 4 to 65 after conducting optimizer statistics. So far, it¡¯s very clear that this SQL statement is executed via path of full table scan rather than index access.

Event 10053 Trace files

~~~~~~~~~~~~~~~~~~~~~~~~~~~~  Against Standby Database

Table stats Table: IM_BLACKLIST Alias: IM_BLACKLIST TOTAL :: CDN: 57477 NBLKS: 374 AVG_ROW_LEN: 38 -- Index stats

INDEX NAME: IM_BLACKLIST_LID_IND COL#: 2

TOTAL :: LVLS: 1 #LB: 219 #DK: 17181 LB/K: 1 DB/K: 2 CLUF: 44331 INDEX NAME: IM_BLACKLIST_PK COL#: 1 2

TOTAL :: LVLS: 1 #LB: 304 #DK: 57477 LB/K: 1 DB/K: 1 CLUF: 55141 _OPTIMIZER_PERCENT_PARALLEL = 0

*************************************** SINGLE TABLE ACCESS PATH

Column: BLACK_ID Col#: 2 Table: IM_BLACKLIST Alias: IM_BLACKLIST NDV: 17181 NULLS: 0 DENS: 5.8204e-05

NO HISTOGRAM: #BKT: 1 #VAL: 2

TABLE: IM_BLACKLIST ORIG CDN: 57477 ROUNDED CDN: 3 CMPTD CDN: 3 Access path: tsc Resc: 38 Resp: 38

Access path: index (equal) Index: IM_BLACKLIST_LID_IND TABLE: IM_BLACKLIST

RSC_CPU: 0 RSC_IO: 4

IX_SEL: 0.0000e+00 TB_SEL: 5.8204e-05 Skip scan: ss-sel 0 andv 27259

ss cost 27259 table io scan cost 38

Access path: index (no sta/stp keys) Index: IM_BLACKLIST_PK

TABLE: IM_BLACKLIST RSC_CPU: 0 RSC_IO: 309

IX_SEL: 1.0000e+00 TB_SEL: 5.8204e-05 BEST_CST: 4.00 PATH: 4 Degree: 1

*************************************** OPTIMIZER STATISTICS AND COMPUTATIONS ***************************************