World of Oracle DBA

All about Statistics Intruduction on Statistics The CBO makes its explain-plan decisions based on statistics. Statistics provide critical input in order for CBO to work properly; these includes information such as the number of rows in a table, distinct values in a column, number of leaf blocks in an index, etc. The more accurate the statistics, the more efficient the results provided by Optimizer. Statistics may be exact or estimated: Statistics generated with a COMPUTE clause analyzes all of the data in the object. This gives the optimizer accurate information to work on and arrive at a good execution plan. Statistics generated with an ESTIMATE clause analyzes data in the object to the extent of sample size mentioned. Sample size may be specified as number of rows or percentage of rows that should be randomly analyzed to generate the statistics. Optionally block sampling may also be specified. Statistics are stored in a data dictionary tables owned by SYS user. The following views d...

It is sometimes a time consuming task if you like to create index with much number of rows. For example you are asked to created an index over 1 billion of data. It may take over 6 hours on your computer and you want to make it faster. With providing several options while creating index you can improve index creation speed dramatically. 1)PARALLEL Option: While creating index oracle must first collect the symbolic key/ROWID pairs with a full-table scan. With Parallel option supplied to the create index script oracle will scan full table parallel based on the number of CPUs, table partitioning and disk configuration and thus do the tasks faster. On a server that have 6 CPUs you may give parallel 5 as below. create index table_1_I on table_1(id,code) parallel 5; 2)NOLOGGING Option: With the NOLOGGING option provided while creating index you can restrict database to generate a large redo log. NOLOGGING option generates minimal redo. So you will achieve higher performance. create index ...

Below is the output of an execution plan statistics. SQL> set autot trace statistics SQL> select * from tab; 107 rows selected. Statistics ---------------------------------------------------------- 8 recursive calls 0 db block gets 898 consistent gets 0 physical reads 0 redo size 3459 bytes sent via SQL*Net to client 458 bytes received via SQL*Net from client 9 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 107 rows processed You sometimes want to know what these fields indicates. Below is the details of these fields. 1)recursive calls: For processing of sql statements oracle database maintain internal tables. Sometimes for processing sql statements change is needed in the internal tables and sometimes not. When Oracle Database needs to make a change to these tables, it internally generates an internal SQL statement, which in turn generates a recursive call. 2)...

On the partitioned table, if we don't do anything with the index then the index against the partitioned table remain global Non partitioned indexes and thus whenever we perform any DDL operation against the table partition like drop a partition or truncate a partition or add a partition or merge a partition or split a partition then the associated global indexes become invalid. Thus after doing DDL we need to rebuild the indexes. This may be very cumbersome and it may need several hours if the table is big enough. With an example I am demonstrating how global indexes against a table become invalid if I do any DDL against the table. 1)Create a partitioned table. CREATE TABLE test_partition ( id number, created_date date, col3 varchar2(20) ) PARTITION BY RANGE (created_date) ( PARTITION part1 VALUES LESS THAN (to_date('11-oct-2008','dd-mon-yyyy')) , PARTITION part2 VALUES LESS THAN (to_date('12-oct-2008','dd-mon-yyyy')) , PARTITION part...

Index on the partitioned table can be of three types. 1)Global Non-partitioned Index. 2)Global Partitioned Index. 3)Local Partitioned Index. With an example I will make you clear of these three different types of indexes on the partitioned table. 1)Create a partitioned table. CREATE TABLE test_partition ( id number, created_date date, col3 varchar2(20) ) PARTITION BY RANGE (created_date) ( PARTITION part1 VALUES LESS THAN (to_date('11-oct-2008','dd-mon-yyyy')) , PARTITION part2 VALUES LESS THAN (to_date('12-oct-2008','dd-mon-yyyy')) , PARTITION part3 VALUES LESS THAN (to_date('13-oct-2008','dd-mon-yyyy')) , PARTITION part4 VALUES LESS THAN (to_date('14-oct-2008','dd-mon-yyyy')) , PARTITION junk VALUES LESS THAN (MAXVALUE) ) / Table created. Global Non Partitioned Index This index span all over the table. Hence it is global and index is not partitioned. SQL> create index tpid_i on test_partition(id); I...

Whenever you create index on multiple columns in a table then that index is called composite index. Advantage of Using Composite Index: ---------------------------------------------------------------- Improved selectivity: As we all know index is created for the column that has higher selectivity means there are a few duplicate column values of the indexing columns. Sometimes two or more columns each with poor selectivity, can be combined to form a composite index with higher selectivity. Reduced I/O: If composite index is created with all the columns selected by the query, then oracle can return the values from the index itself instead of accessing values from the table using ROWID scan unlike single key index. To know more about ROWID scan and explain plan search within my blog. Composite Key Usage in a Query ---------------------------------------------------------- It is important to maintain order of the index while creating composite key index. For example create index on colum...

Whenever you create index on multiple columns in a table then that index is called composite index. Advantage of Using Composite Index: ---------------------------------------------------------------- Improved selectivity: As we all know index is created for the column that has higher selectivity means there are a few duplicate column values of the indexing columns. Sometimes two or more columns each with poor selectivity, can be combined to form a composite index with higher selectivity. Reduced I/O: If composite index is created with all the columns selected by the query, then oracle can return the values from the index itself instead of accessing values from the table using ROWID scan unlike single key index. To know more about ROWID scan and explain plan search within my blog. Composite Key Usage in a Query ---------------------------------------------------------- It is important to maintain order of the index while creating composite key index. For example create index on colum...

If I create an index on column (a,b,c) then a, ab, abc combination of columns in where clause will use the index. bc, b, and c combinations of columns in where clause will not use the index. The example is given below. SQL> create table comp_tab(a number, b varchar2(10), c varchar2(10)); Table created. SQL> begin for i in 1 .. 10000 loop insert into comp_tab values(i,'pc-'||round(dbms_random.value(1,20000),0),'pc-'||round(dbms_random.value(1,20000),0)); end loop; end; / PL/SQL procedure successfully completed. SQL> create index comp_tab_I on comp_tab(a,b,c); Index created. SQL> analyze table comp_tab estimate statistics; Table analyzed. SQL> set autot trace a in where cluase will use the index as a is leading portion of index. SQL> select a,c,b from comp_tab where a=565; Execution Plan ---------------------------------------------------------- Plan hash value: 753992557 -------------------------------------------------------------------------------...