Feeds:
Posts
Comments

Posts Tagged ‘Performance’

A)Index Skip Scan
—————————————-

As the name suggest index skip scan does not scan complete index. But it scan of the subindexes.

Index skip scan lets a composite index be split logically into smaller subindexes. In skip scanning, the initial column of the composite index is not specified in the query. In other words, it is skipped.

The number of logical subindexes is determined by the number of distinct values in the initial column. Skip scanning is advantageous if there are few distinct values in the leading column of the composite index and many distinct values in the nonleading key of the index.

Suppose if I make a make a composite index with two columns sex and id. The leading column sex contains only two distinct columns. Now if I query with non-leading column that is with id column then index skip scan will be used.

Example:
——————

SQL> create table test_skip_scan (sex varchar2(1), id number, address varchar2(20));
Table created.

SQL> create index test_skip_scan_I on test_skip_scan(sex,id);

Index created.

SQL> begin
for i in 1 .. 10000
loop
insert into test_skip_scan values(decode(remainder(abs(round(dbms_random.value(2,20),0)),2),0,’M’,’F’),i,null);
end loop;
end;
/

PL/SQL procedure successfully completed.

SQL> analyze table test_skip_scan estimate statistics;
Table analyzed.

SQL> select * from test_skip_scan where id=1;

Execution Plan
———————————————————-
Plan hash value: 2410156502

————————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————————————
| 0 | SELECT STATEMENT | | 1 | 9 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_SKIP_SCAN | 1 | 9 | 4 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | TEST_SKIP_SCAN_I | 1 | | 3 (0)| 00:00:01 |
————————————————————————————————

Predicate Information (identified by operation id):
—————————————————

2 – access(“ID”=1)
filter(“ID”=1)

B)Index Fast Full Scan
—————————————

Fast full index scans are an alternative to a full table scan when the index contains all the columns that are needed for the query, and at least one column in the index key has the NOT NULL constraint.

A fast full scan accesses the data in the index itself, without accessing the table.

It cannot be used to eliminate a sort operation, because the data is not ordered by the index key.

It reads the entire index using multiblock reads, unlike a full index scan, and can be parallelized.

Fast full index scans cannot be performed against bitmap indexes.

You can specify fast full index scans with the initialization parameter OPTIMIZER_FEATURES_ENABLE or the INDEX_FFS hint.

Example:
—————–
SQL> select /*+INDEX_FFS(test_skip_scan)*/ sex,id from test_skip_scan;

10000 rows selected.

Execution Plan
———————————————————-
Plan hash value: 4280781105

—————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————–
| 0 | SELECT STATEMENT | | 10000 | 40000 | 7 (0)| 00:00:01 |
| 1 | INDEX FAST FULL SCAN| TEST_SKIP_SCAN_I | 10000 | 40000 | 7 (0)| 00:00:01 |
—————————————————————————————–


C)Index Joins
————————-

An index join is a hash join of several indexes that together contain all the table columns that are referenced in the query. If an index join is used, then no table access is needed, because all the relevant column values can be retrieved from the indexes. An index join cannot be used to eliminate a sort operation.

You can specify an index join with the INDEX_JOIN hint. For more information on the INDEX_JOIN hint.

SQL> select sex,id from test_skip_scan where id in (select col1 from test_tab);

1000 rows selected.

Execution Plan
———————————————————-
Plan hash value: 1059662925

—————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————-
| 0 | SELECT STATEMENT | | 999 | 6993 | 9 (12)| 00:00:01 |
|* 1 | HASH JOIN RIGHT SEMI | | 999 | 6993 | 9 (12)| 00:00:01 |
| 2 | INDEX FAST FULL SCAN| TEST_TAB_I | 1000 | 3000 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | TEST_SKIP_SCAN | 10000 | 40000 | 6 (0)| 00:00:01 |
—————————————————————————————-

Predicate Information (identified by operation id):
—————————————————

1 – access(“ID”=”COL1”)

D)Bitmap Indexes
———————————–

A bitmap join uses a bitmap for key values and a mapping function that converts each bit position to a rowid. Bitmaps can efficiently merge indexes that correspond to several conditions in a WHERE clause, using Boolean operations to resolve AND and OR conditions.

Bitmap indexes and bitmap join indexes are available only if you have purchased the Oracle Enterprise Edition.

Expert are always welcome for their valuable comment or suggestion for the above post.

Related Post:

https://samadhandba.wordpress.com/2011/01/04/oversize-of-datatype-varchar2-causes-performance-issue/

https://samadhandba.wordpress.com/2011/02/16/what-and-when-index-scans-is-used/

https://samadhandba.wordpress.com/2011/02/16/index-skip-full-fast-full-index-index-joins-bitmap-indexes-scan/

https://samadhandba.wordpress.com/2011/02/14/sample-table-scans-in-oracle/

https://samadhandba.wordpress.com/2011/02/14/when-you-would-make-index-and-when-not/

https://samadhandba.wordpress.com/2011/02/14/optimize-data-access-path-in-oracle-2/

https://samadhandba.wordpress.com/2011/02/13/troubleshoot-unusable-index-in-oracle/

https://samadhandba.wordpress.com/2011/02/13/the-possible-causes-for-excessive-undo-generation-2/

https://samadhandba.wordpress.com/2011/02/13/three-basic-steps-of-sql-tuning/

https://samadhandba.wordpress.com/2011/02/13/goals-for-tuning-2/

https://samadhandba.wordpress.com/2011/02/04/ora-12054-cannot-set-the-on-commit-refresh-attribute-for-the-materialized-view/

Read Full Post »

If a procedural object, such as a stored PL/SQL function or a view, becomes invalid, the DBA does not necessarily have to do anything: the first time it is accessed, Oracle will attempt to recompile it, and this may well succeed.

But if an index becomes unusable for any reason, it must always be repaired explicitly before it can be used.

This is because an index consists of the index key values, sorted into order, each with the relevant
rowid. The rowid is the physical pointer to the location of the row to which the index key refers.

If the rowids of the table are changed, then the index will be marked as unusable. This could occur for a number of reasons. Perhaps the most common is that the table has been moved, with the ALTER TABLE…MOVE command. This will change the physical placement of all the rows, and therefore the index entries will be pointing to the wrong place. Oracle will be aware of this and will therefore not permit use of the index.

Identifying Unusable Indexes
———————————
In earlier releases of the Oracle database, when executing SQL statements, if the session attempted to use an unusable index it would immediately return an error, and the statement would fail. Release 10g of the database changes this behavior. If a statement attempts to use an unusable index, the statement will revert to an execution plan that does not require the index.

Thus, statements will always succeed—but perhaps at the cost of greatly reduced performance. This behavior is controlled by the instance parameter SKIP_UNUSABLE_INDEXES, which defaults to TRUE. The exception to this is if the index is necessary to enforce a constraint: if the index on a primary key column becomes unusable, the table will be locked for DML.

To detect indexes which have become unusable, query the DBA_INDEXES view:
SQL> select owner, index_name from dba_indexes where status=’UNUSABLE’;

Repairing Unusable Indexes
—————————————-

To repair the index, it must be re-created with the ALTER INDEX…REBUILD command.

This will make a pass through the table, generating a new index with correct rowid pointers
for each index key. When the new index is completed, the original unusable index is dropped.

The syntax of the REBUILD command has several options. The more important ones are TABLESPACE, ONLINE, and NOLOGGING. By default, the index will be rebuilt within its current tablespace, but by specifying a table space with the TABLESPACE keyword, it can be moved to a different one.

Also by default, during the course of the rebuild the table will be locked for DML. This can be avoided by using the ONLINE keyword.

1)Create Table and insert row in it:
—————————————-
SQL> create table test ( a number primary key);
Table created.

SQL> insert into test values(1);
1 row created.

SQL> commit;
Commit complete.

2)Check the Index Status
————————–
SQL> select index_name, status from user_indexes;
INDEX_NAME STATUS
—————————— ——–
SYS_C0044513 VALID

3)Move the Table and Check Status:
————————————
SQL> alter table test move;
Table altered.

SQL> select index_name, status from user_indexes;
INDEX_NAME STATUS
—————————— ——–
SYS_C0044513 UNUSABLE

4)Rebuild The Index:
———————–
SQL> alter index SYS_C0044514 rebuild online;
Index altered.

SQL> select index_name, status from user_indexes;
INDEX_NAME STATUS
—————————— ——–
SYS_C0044514 VALID

Read Full Post »

————————————
DATABASE PERFORMANCE
————————————-
FREQUENTLY ASKED QUESTIONS
———————————————-
QUESTIONS
—————
1. What are the prioritized tuning steps to implement a database with optimal performance?
2. What are the tools that can be used to monitor database performance?
3. Who should be involved in the database tuning process?
4. What are the major tuning areas in database performance tuning?
5. How to identify which tuning area contributes to the database performance problem?
6. How to improve the identified poor performing area?
7. Where can one find the descriptions of v$ views and Wait Events?
8. What is the appropriate size of SGA?
9. How to analyze and interpret results from utlbstat/utlestat or statspack?
10. Why does the database performance change after upgrade or migration?
11. Where can one learn more about database performance tuning?

ANSWERS
————–

1. What are the prioritized tuning steps to implement a database with optimal performance?

Proactive tuning during the database design and development stage is the most effective way to achieve optimal
database performance. The following list has been ordered according to their general return on effort.

– Tune the business rules.
– Tune the data design
– Tune the application design.
– Tune the logical structure of the database.
– Tune the database operations.
– Tune the access paths.
– Tune memory allocation.
– Tune the I/O and physical structure.
– Tune the resource contention.
– Tune the underlying platform(s).

The reactive tuning approach is driven by identifying the most significant bottleneck and making the appropriate
changes to reduce or eliminate the effect of that bottleneck It requires to gather statistical information to detect
where the bottleneck is taking place and then apply corrective actions. It is recommended that changes be made
to a system only after you have confirmed that there is a bottleneck. The detailed steps are described in the section:
How to identify which tuning area contributes to the database performance problem

2. What are the tools that can be used to monitor database performance?

The database has a complete set of statistics and mechanisms to alert when problems are occurring.
Gathering information to tune an Oracle database can be obtained with the following tools and database resources:

– Alert log and trace files: The first step in detecting a performance problem is searching for errors or warnings
issued by the database. These files keep track of this information.
– V$ views: Database statistical information is stored in the V$ Views.
– Utlbstat/utlestat and STATSPACK (available from 8.1.6): These tools bundled with the Oracle Server
generate complete reports of the database activity. The new STATSPACK utility bundled with Oracle 8.1.6
and above provides more flexibility in managing statistical snapshots.
– OEM Performance Pack. The Performance pack offers a complete set of graphical tools to monitor the
performance of the database.

3. Who should be involved in the database tuning process?

The tuning process is usually viewed as a task of the database administrator. Ideally, the database tuning process
should involve database administrators, system administrators, application developers, and end users.
Involving these resources, will ensure to set performance targets and user expectations according with the business
needs and available resources.

4. What are the major tuning areas in database performance tuning?

– Memory – shared pool, large pool, buffer cache, redo log buffer, and sort area size.
– I/O – distributing I/O, striping, multiple DBWn processes, and DBWn I/O slaves.
– CPU – CPU utilization.
– Space management – extent allocation and Oracle block efficiency.
– Redo log and checkpoint – redo log file configuration, redo entries, and checkpoint.
– Rollback segment – sizing rollback segments.
– Network

5. How to identify which tuning area contributes to the database performance problem?

– Gather Database statistical information using STATSPACK or UTLSTAT reports when
the performance is both good and bad.
– Obtain operating system and application statistical information using the same approach.
Oracle relies on the hardware capacity and when the system resources are permanently
busy you can be facing a capacity problem.
– Then Examine the Host System and Oracle Statistics for any evidence.
– Define actions to solve bottlenecks prioritizing those actions that will impact the most
the performance of the application/database. Some simple actions can improve dramatically
the performance, so consider to implement them first. (See Note:148373.1)

6. How to improve the identified poor performing area?

Although each tuning area requires its specific tuning methods, the underlying tuning methodology for all areas is
the same. The general tuning process can be divided into the following steps:

– Set a realistic and quantitative performance target. The target should match the business rules and users’
expectations, but also be bounded by available resources of the system.
– Change one thing at a time. Evaluating the effect from manipulating one variable at a time is more efficient
than manipulating more than one variable at the same time, especially when there could be more than one
factor contributing to the performance problem.
– Evaluate the effect of change. Examine the new results from utlbstat/utlestat or statspack.
Is there any improvement from the change?
> If there is improvement resulting from the change, more change to the same factor can be made.
> If there is no improvement resulting from the change, the change needs to be reversed, and go back to
Step 2 to change another factor.
– Stop when the performance target is reached.

Multiple cycles of the tuning process may be needed to reach the final performance target.

7. Where can one find the descriptions of v$ views and wait events?

8. What is the appropriate size of SGA?

The appropriate size of SGA is system dependent, and it is limited by the available system resources. For optimal
performance, SGA should fit into real memory avoiding the need for swapping. When sizing the SGA consider to
tune all memory structures (the shared pool, the buffer cache and the redolog buffer cache) together so you can
assign appropriate resources to each according with the database requirements.

9. How to analyze and interpret results from utlbstat/utlestat or statspack?

10. Why does the database performance change after an upgrade or migration?

Database performance tuning is an ongoing process throughout the life of a database. Any changes to the system
could disrupt the balance of a previously well-tuned database. Database upgrades or migrations usually come with
a great deal of changes to the system, so one expects to see some changes to the database performance. It is best to
perform intensive testing on a development database before upgrading or migrating the production database.

11. Where can one learn more about database performance tuning?

– Oracle 7/8/8i Designing and Tuning for Performance Manual – Note 152140.1
– Oracle9i Database Performance Methods Manual
– Oracle9i Database Performance Guide and Reference
– Oracle Education Instructor-Led Training, Enterprise DBA Part 2: Performance Tuning.

Read Full Post »