Archive for the ‘Schema Objects’ Category

Directory Privileges

Creating Directory

SQL> create or replace directory DW_DMP as ‘/u02/expdp’;

SQL> GRANT READ, WRITE ON DIRECTORY DW_DMP to expdpadmin;

To check on which directories user have privilege to read & write:
#################################################################

SQL> SELECT grantee,privilege, directory_name,directory_path
FROM dba_tab_privs t, all_directories d
WHERE t.table_name(+)=d.directory_name
and directory_name like ‘DW%’
ORDER BY 3;

Table and Index Partitioning

Oracle Partitioning Quick Primer

Partitioned Tables and Indexes

When to Rebuild Indexes

How can you determine if an index needs to be dropped and rebuilt?

Expected answer: Run the ANALYZE INDEX command on the index to validate its structure and then calculate the ratio of LF_BLK_LEN/LF_BLK_LEN+BR_BLK_LEN and if it isn?t near 1.0 (i.e. greater than 0.7 or so) then the index should be rebuilt. Or if the ratio BR_BLK_LEN/ LF_BLK_LEN+BR_BLK_LEN is nearing 0.3.

 

analyze index i1 validate structure;
select lf_blks from index_stats;

 

 

One opinion:

index rebuilds are justified only if there are regular deletes and inserts of more than 20-30% of the records in the table.

 

select ‘analyze index ‘||owner||’.’||index_name||’ validate structure;’
from dba_indexes
where owner = ‘HR’;

 

CREATE OR REPLACE PROCEDURE analyze_index (owner IN VARCHAR2)
IS
   index_nam         VARCHAR2 (50);
   stmt              VARCHAR (100);
   index_ratio       NUMBER (3, 2);
   idx_rebuild_cnt   NUMBER        := 0;
   idx_valid_cnt     NUMBER        := 0;

   CURSOR dyn_cur
   IS
      SELECT index_name
        FROM dba_indexes
       WHERE owner = ‘HR’;
BEGIN
   FOR rec IN dyn_cur
   LOOP
      index_nam := rec.index_name;
      stmt :=
         ‘analyze index ‘ || owner || ‘.’ || index_nam
         || ‘ validate structure’;
      DBMS_OUTPUT.put_line (stmt);

      EXECUTE IMMEDIATE stmt;

      EXECUTE IMMEDIATE ‘insert into index_stats_log select * from index_stats’;

      SELECT lf_blk_len / (lf_blk_len + br_blk_len)
        INTO index_ratio
        FROM index_stats;

      IF (index_ratio < 0.7)
      THEN
         DBMS_OUTPUT.put_line (   ‘Index ratio of’
                               || index_nam
                               || ‘ is greater than 0.7: = ‘
                               || index_ratio
                              );
         idx_rebuild_cnt := idx_rebuild_cnt + 1;
      ELSE
         DBMS_OUTPUT.put_line (   ‘Index ratio of’
                               || index_nam
                               || ‘ is less than 0.7: = ‘
                               || index_ratio
                              );
         idx_valid_cnt := idx_valid_cnt + 1;
      END IF;
   END LOOP;

   DBMS_OUTPUT.put_line (‘Index Rebuild Count is ‘ || idx_rebuild_cnt);
   DBMS_OUTPUT.put_line (‘Index Without Rebuild Count is ‘ || idx_valid_cnt);
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      NULL;
   WHEN OTHERS
   THEN
      — Consider logging the error and then re-raise
      RAISE;
END analyze_index;

 

select s.name,s.del_lf_rows*100/decode(s.lf_rows, 0, 1, s.lf_rows) pct_deleted, (s.lf_rows-s.distinct_keys)*100/ decode(s.lf_rows,0,1,s.lf_rows) distinctiveness, i.blevel blevel

from index_stats s, dba_indexes i

where i.index_name = s.name ;

 

One More Approach

– Find out indexes having height(blevel+1) > 4 i.e. Indexes having BLEVEL > 3
How:
SQL> select owner,index_name,table_name,blevel from dba_indexes where BLEVEL>3
– Analyze indexes and find out ratio of (DEL_LF_ROWS/LF_ROWS*100) is > 20
How:
First "Analyze the index with validate structure option" and then,

SQL> SELECT name,height,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 as ratio FROM INDEX_STATS;

 

 

Rebuilding Indexes

Avoids writing to redo logs and speeds up the index rebuild.

Alter index <index_name> rebuild unrecoverable;

Managing Indexes

BTree Index Creation

CREATE INDEX hr.employees_last_name_idx
ON hr.employees(last_name)
PCTFREE 30
STORAGE(INITIAL 200K NEXT 200K
PCTINCREASE 0 MAXEXTENTS 50)
TABLESPACE indx;

 

Bitmap Index Creation

CREATE BITMAP INDEX orders_region_id_idx
ON orders(region_id)
PCTFREE 30
STORAGE(INITIAL 200K NEXT 200K
PCTINCREASE 0 MAXEXTENTS 50)
TABLESPACE indx;

Index Guidelines

Balance query and DML needs
• Place in separate tablespace
• Use uniform extent sizes: Multiples of five blocks or MINIMUM EXTENT size for  tablespace
• Consider NOLOGGING for large indexes
• INITRANS should generally be higher on indexes than on the corresponding tables.

Altering Index

ALTER INDEX employees_last_name_idx
STORAGE(NEXT 400K  MAXEXTENTS 100);

ALTER INDEX orders_region_id_idx
ALLOCATE EXTENT (SIZE 200K DATAFILE ‘/DISK6/indx01.dbf’);

ALTER INDEX orders_id_idx    DEALLOCATE UNUSED;

ALTER INDEX orders_region_id_idx REBUILD
TABLESPACE indx02;

     ALTER INDEX orders_region_id_idx REBUILD ONLINE;

ALTER INDEX orders_id_idx COALESCE;

Use the ALTER INDEX command to:
• Move an index to a different tablespace
• Improve space utilization by removing deleted entries
• Change a reverse key index to a normal B-tree index and vice versa

 

Analyzing and Gathering Index STATS

analyze index HR.LOC_CITY_IX validate structure

After running this command, query INDEX_STATS to obtain information about the index as shown in the following example:

SELECT blocks, pct_used, distinct_keys,
lf_rows, del_lf_rows
FROM index_stats

EXEC DBMS_STATS.gather_index_stats(‘SCOTT’, ‘EMPLOYEES_PK’);EXEC DBMS_STATS.gather_index_stats(‘SCOTT’, ‘EMPLOYEES_PK’, estimate_percent => 15);

Dictionary Views

DBA_INDEXES
DBA_IND_COLUMNS
DBA_OBJECTS
IND$
INDEX_STATS

 

LINKS

CBO Stats

DBASUPPORT CBO

Bitmap and BTree Indexes

According to conventional wisdom, Bitmap index is a preferred indexing technique for cases where the indexed attributes have few distinct values (i.e., low cardinality). The query response time is expected to degrade as the cardinality of indexed columns increase due to a larger index size. On the other hand, B-tree index is good if the column values are of high cardinality due to its indexing and retrieving mechanisms.

In summary, bitmap indexes are best suited for DSS regardless of cardinality for these reasons:

  • With bitmap indexes, the optimizer can efficiently answer queries that include AND, OR, or XOR. (Oracle supports dynamic B-tree-to-bitmap conversion, but it can be inefficient.)
  • With bitmaps, the optimizer can answer queries when searching or counting for nulls. Null values are also indexed in bitmap indexes (unlike B-tree indexes).
  • Most important, bitmap indexes in DSS systems support ad hoc queries, whereas B-tree indexes do not. More specifically, if you have a table with 50 columns and users frequently query on 10 of them—either the combination of all 10 columns or sometimes a single column—creating a B-tree index will be very difficult. If you create 10 bitmap indexes on all these columns, all the queries can be answered by these indexes, whether they are queries on all 10 columns, on 4 or 6 columns out of the 10, or on a single column. This limit is not imposed with bitmap indexes.

In contrast, B-tree indexes are well suited for OLTP applications in which users’ queries are relatively routine (and well tuned before deployment in production), as opposed to ad hoc queries, which are much less frequent and executed during nonpeak business hours. Because data is frequently updated in and deleted from OLTP applications, bitmap indexes can cause a serious locking problem in these situations.

Structure of a B-Tree Index

At the top of the index is the root, which contains entries that point to the next level in the index. At the next level are branch blocks, which in turn point to blocks at the next level in the index. At the lowest level are the leaf nodes, which contain the index entries that point to rows in the table. The leaf blocks are doubly linked to facilitate scanning the index in an ascending as well as descending order of key values.

Format of Index Leaf Entries
An index entry is made up of the following components:

• An entry header, which stores number of columns and locking information
• Key values consisting of length and value pairs for each key column
• ROWID of a row, which contains the key values

Index Leaf Entry Characteristics
In a B-tree index on a nonpartitioned table:
• Key values are repeated if there are multiple rows that have same key value.
• There is no index entry corresponding to a row that has all key columns that are NULL. Therefore a WHERE clause specifying NULL will always result in a full table scan.
• Restricted ROWID is used to point to the rows of the table, since all rows belong to the same segment.

Effect of DML Operations on an Index
The Oracle server maintains all the indexes when DML operations are carried out on the table. Here is an explanation of the effect of a DML command on an index:
• Insert operations result in the insertion of index entry in appropriate block.
• Deleting a row results only in a logical deletion of the index entry. The space used by the deleted row is not available for new entries until all the entries in the block are deleted.
• Updates to the key columns result in a logical delete and an insert to the index. The PCTFREE setting has no effect on the index except at the time of creation. A new entry may be added to an index block even if it has less space than that specified by PCTFREE.

Structure of a Bitmap Index

A bitmap index is also organized as a B-tree, but the leaf node stores a bitmap for each key value instead of a list of ROWIDs. Each bit in the bitmap corresponds to a possible ROWID, and if the bit is set, it means that the row with the corresponding ROWID contains the key value.

The leaf node of a bitmap index contains the following:
• An entry header, containing the number of columns and lock information
• Key values consisting of length and value pairs for each key column
• Start ROWID and End ROWID

• A bitmap segment consisting of a string of bits (The bit is set when the corresponding row contains the key value and is unset when the row does not contain the key value. The Oracle server uses a patented compression technique to store bitmap segments.)

The start ROWID is the ROWID of the first row pointed to by the bitmap segment of the bitmap—that is, the first bit of the bitmap corresponds to that ROWID, the second bit of the bitmap corresponds to the next row in the block, and the end ROWID is a pointer to the last row in the table covered by the bitmap segment. Bitmap indexes use restricted ROWIDs.

Using a Bitmap Index
The B-tree is used to locate the leaf nodes that contain bitmap segments for a given value of the key. Start ROWID and the bitmap segments are used to locate the rows that contain the key value.
When changes are made to the key column in the table, bitmaps must be modified. This results in locking of the relevant bitmap segments. Because locks are acquired on the whole bitmap segment, a row that is covered by the bitmap cannot be updated by other transactions until the first transaction ends.

B-Tree Index

Bitmap Index

Suitable for high-cardinality columns Suitable for low-cardinality columns
Updates on Keys relatively easy Updates on Key columns very expensive
Inefficient for queries using OR predicates Efficient for queries using OR predicates
Useful for OLTP Useful for datawarehousing

Create Statistics

Analyze command

The ANALYZE command is available for all versions of Oracle, however to obtain faster and better statistics use the procedures supplied in DBMS_STATS.GATHER_SCHEMA_STATS.

The analyze table can be used to create statistics for 1 table, index or cluster.
Syntax:
ANALYZE table tableName {compute|estimate|delete) statistics options
ANALYZE table indexName {compute|estimate|delete) statistics options
ANALYZE cluster clusterName {compute|estimate|delete) statistics options

Code examples 

ANALYZE table scott compute statistics;
ANALYZE table scott estimate statistics sample 25 percent;
ANALYZE table scott estimate statistics sample 1000 rows;
analyze index sc_idx compute statistics;
analyze index sc_idx validate structure;
DBMS_STATS.GATHER_SCHEMA_STATS

Starting with the introduction of the dbms_stats package, Oracle provides a simple way for the Oracle professional to collect statistics for the CBO. The old-fashioned analyze table and dbms_utility methods for generating CBO statistics are obsolete and somewhat dangerous to SQL performance because they don’t always capture high-quality information about tables and indexes. The CBO uses object statistics to choose the best execution plan for all SQL statements.

The dbms_stats utility does a far better job in estimating statistics, especially for large partitioned tables, and the better stats result in faster SQL execution plans.

 

Syntax:

exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname,estimate_percent, block_sample , method_opt,degree,granularity,cascade,stattab, statid,options,statown ,no_invalidate, gather_temp,gather_fixed);

Code examples:

exec  DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT',DBMS_STATS.AUTO_SAMPLE_SIZE);
exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'SCOTT'
                              ,estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);
EXEC DBMS_STATS.gather_schema_stats(ownname => 'SCOTT', estimate_percent => 25);
EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES');
EXEC DBMS_STATS.gather_index_stats('SCOTT', 'EMPLOYEES_PK');
exec DBMS_STATS.DELETE_SCHEMA_STATS('SCOTT');
For reference check ORADEV link

Using for gather schema level stats

exec DBMS_STATS.gather_schema_stats(

ownname          => ‘FZAFAR’,

options          => ‘GATHER AUTO’,

estimate_percent => dbms_stats.auto_sample_size,

method_opt       => ‘for all columns size repeat’,

cascade          => true )

There are several values for the OPTIONS parameter that we need to know about:

  • GATHER_ reanalyzes the whole schema
  • GATHER EMPTY_ only analyzes tables that have no existing statistics
  • GATHER STALE_ only reanalyzes tables with more than 10 percent modifications (inserts, updates,   deletes)
  • GATHER AUTO_ will reanalyze objects that currently have no statistics and objects with stale statistics.  Using GATHER AUTO is like combining GATHER STALE and GATHER EMPTY.

More Details read Art Builder DBMS_STATS

Oracle link for DBMS_STATS

Implications of Compute Statistics Option

Oracle provides estimate statistics option which bases the object’s statistics on a review of a portion of the data. Usually analyzing 20 percent of the table is sufficient.

Analyzing data can require large amounts of sort space. It may include full table scans as well so its better to change session settings prior to starting the analysis.The session settings to change are

SORT_AREA_SIZE

DB_FILE_MULTIBLOCK_READ_COUNT

The larger the sort area size is, the less likely you are to need to use the temporary tablespace for sort segments.

Managing Tables

Creating a Table

The CREATE TABLE command is used to create relational tables or object tables.

Relational table: This is the basic structure to hold user data.

Object table: Is a table that uses an object type for a column definition. An object table is a table that is explicitly defined to hold the object instance of a particular type.

 

The example below creates a DEPARTMENTS table in a data dictionary managed tablespace.

SQL> CREATE TABLE hr.departments( department_id NUMBER(4),

department_name VARCHAR2(30), manager_id NUMBER(6), location_id NUMBER(4))

STORAGE(INITIAL 200K NEXT 200K PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 5)

TABLESPACE data;

STORAGE clause:

The STORAGE clause specifies storage characteristics for the table. The storage allocated for the first extent is 200 KB. When a second extent is required it will be created at 200 KB also defined by the NEXT value. When a third extent is required, it will be created at 200 KB because the PCTINCREASE has been set to zero. The maximum amount of extents that can be used is set at five, with the minimum set to one.

•MINEXTENTS: This is the minimum number of extents that is to be allocated.

•MAXEXTENTS: This is the maximum number of extents to be allocated. If MINEXTENTS is specified with a value greater than one and the tablespace contains more than one datafile, the extents will be spread across the different datafiles.

•PCTINCREASE: This is the percent of increase in extent size after NEXT extent and thereafter.

 

PCTFREE must be a value from zero to ninety-nine. A value of zero means that the entire block can be filled by inserts of new rows. The default value is 10. This value reserves 10% of each block for updates to existing rows and allows inserts of new rows to fill a maximum of 90% of each block.

•PCTUSED: Specifies the minimum percentage of used space that is maintained for each data block of the table. A block becomes a candidate for row insertion when its used space falls below PCTUSED. PCTUSED is specified as integer from zero to ninety-nine and defaults to 40.

The combination of PCTFREE and PCTUSED determines whether new rows will be inserted into existing data blocks or into new bocks. The sum of these two must be equal to or less than 100. These parameters are used to utilize space within a table more efficiently.

Note: PCTUSED, FREELISTS, and FREELIST GROUPS are deprecated with the Oracle9i feature Automatic Segment-Space Management. Refer to the “Storage Structures and Relationship” lesson for details regarding this feature.

•INITRANS: Specifies the initial number of transaction entries allocated within each data block allocated to the table. This value can range from 1-255 and default to one INITRANS: Ensures that a minimum number of concurrent transactions can update the block. In general, this value should not be changed from its default.

•MAXTRANS: Specifies the maximum number of concurrent transaction that can update a data block allocated to the table. This limit does not apply to queries. The value can range from 1-255 and the default is a function of the data block size.

 

Creating Temporary Tables

Temporary tables can be created to hold session-private data that exists only for the duration of a transaction or session.

The CREATE GLOBAL TEMPORARY TABLE command creates a temporary table that can be transaction-specific or session-specific. For transaction-specific temporary tables, data exists for the duration of the transaction, while for session-specific temporary tables, data exists for the duration of the session. Data in a session is private to the session. Each session can only see and modify its own data. DML locks are not acquired on the data of the temporary tables. The clauses that control the duration of the rows are:

  • ON COMMIT DELETE ROWS: To specify that rows are only visible within the transaction
  • ON COMMIT PRESERVE ROWS: To specify that rows are visible for the entire session

You can create indexes, views, and triggers on temporary tables and you can also use the Export and Import utilities to export and import the definition of a temporary table. However, no data is exported, even if you use the ROWS option. The definition of a temporary table is visible to all sessions.

Setting PCTFREE

A higher PCTFREE affords more room for updates within a database block. Set a higher value if the table contains:

  • Columns that are initially NULL and later updated with a value
  • Columns that are likely to increase in size as a result of an update

A higher PCTFREE will result in lower block density—each block can accommodate fewer rows. The formula specified above ensures that there is enough free space in the block for row growth.

Setting PCTUSED

Set PCTUSED to ensure that the block is returned to the free list only when there is sufficient space to accommodate an average row. If a block on the free list does not contain sufficient space for inserting a row, the Oracle server looks up the next block on the free list. This linear scan continues until either a block with sufficient space is found or the end of the list is reached. Using the formula given reduces the time taken to scan the free list by increasing the probability of finding a block with the required free space.

Changing Storage and Block Utilization Parameters

Some of the storage parameters and any of the block utilization parameters can be modified by using the ALTER TABLE command.

Syntax:

ALTER TABLE hr.employees

PCTFREE 20

PCTUSED 60

STORAGE NEXT (500k MINEXTENTS 2 MAXEXTENTS 100);

ALLOCATING EXTENTS MANUALLY

Extents may need to be allocated manually:

  • To control the distribution of extents of a table across files
  • Before loading data in bulk to avoid dynamic extension of tables

ALTER TABLE hr.employess

ALLOCATE EXTENT (SIZE 500K  DATAFILE ‘C:\ORACLE\DATA01.DBF’);

Moving Table To Different Tablespace

A nonpartitioned table can be moved without having to run the Export or Import utility. In addition, it allows the storage parameters to be changed. This is useful when:

  • Moving a table from one tablespace to another
  • Reorganizing the table to eliminate row migration

ALTER TABLE hr.employees move tablespace data1;

 

Adding a Table Column and Check Constraint: Example

The following statement adds a column named duty_pct of datatype NUMBER and a column named visa_needed of datatype VARCHAR2 with a size of 3 and a CHECK integrity constraint:

 ALTER TABLE countries 
   ADD (duty_pct     NUMBER(2,2)  CHECK (duty_pct < 10.5),
        visa_needed  VARCHAR2(3)); 

 

Renaming a Column: Example

ALTER TABLE customers

   RENAME COLUMN credit_limit TO credit_amount;

TRUNCATE TABLE

TRUNCATE TABLE hr.employees;

  • The effects of using this command are as follows:
  • All rows in the table are deleted.
  • No undo data is generated and the command commits implicitly because TRUNCATE TABLE is a DDL command.
  • Corresponding indexes are also truncated.
  • A table that is being referenced by a foreign key cannot be truncated.
  • The delete triggers do not fire when this command is used.

DROP TABLE

DROP TABLE hr.employees CASCADE CONSTRAINT;

When a table is dropped, the extents used by the table are released. If they are contiguous, they may be coalesced either automatically or manually at a later stage. The CASCADE CONSTRAINTS option is necessary if the table is the parent table in a foreign key relationship.

 

Dropping a Column

Dropping a column can take a significant amount of time because all the data for the column is deleted from the table.

Dropping a column can be time consuming and requires a large amount of undo space. While dropping columns from large tables, checkpoints can be specified to minimize the use of undo space. In the example in the slide, a checkpoint occurs every 1,000 rows. The table is marked INVALID until the operation completes.

ALTER TABLE hr.employees DROP COLUMN comments CASCADE constraints CHECKPOINT 1000;

If the instance fails during the operation, the table remains INVALID on start up, and the operation will have to be completed.

SQL> ALTER TABLE hr.employees DROP COLUMNS CONTINUE;

Using the UNUSED Option

Instead of removing a column from a table, the column can be marked as unused and then removed later. This has the advantage of being relatively quick, as it does not reclaim the disk space because the data is not removed. Columns that are marked as unused can be removed at a later time from the table when there is less activity on the system.

Unused columns act as if they are not part of the table. Queries cannot see data from unused columns. In addition, the names and data types of those columns are not displayed when a DESCRIBE command is executed. A user can add a new column with the same name as an unused column. Marking column COMMENTS as unused:

ALTER TABLE hr.employees SET UNUSED COLUMN comments CASCADE CONSTRAINTS;

Dropping UNUSED column

ALTER TABLE hr.employees DROP UNUSED COLUMNS CHECKPOINT 1000;

The following query shows that the table EMPLOYEES owned by HR has one unused column:

SQL > SELECT * FROM dba_unused_col_tabs;

OWNER TABLE_NAME COUNT

—– ————– ——

HR EMPLOYEES 1

To identify tables that have partially completed DROP COLUMN operations the DBA_PARTIAL_DROP_TABS view can be queried.

SQL > SELECT * FROM dba_partial_drop_tabs;

OWNER TABLE_NAME COUNT

—– ————– ——

no rows selected

Obtaining Table Information

Information about tables can be obtained from the data dictionary.

  • DBA_OBJECTS
  • DBA_TABLES

To obtain the data object number and the location of the table header for all tables owned by HR, use the following query:

SQL > SELECT table_name FROM dba_tables WHERE owner = ‘HR’;

 

 
 
For details see Alter table link.