Tuning Queries That Cannot be Modified

PURPOSE

Query Tuning issues can often be alleviated by modification of the query in question, either to add hints or to change the query structure to avoid an issue. However in some cases the query cannot be modified because it is hardcoded in an application or is generated by application code. This document suggests methods that you can use to attempt to alter the access path taken by a SQL query without changing it.

SCOPE & APPLICATION

Analysts & customers trying to tune SQL queries.

The main thrust of the article is to provide information on how to affect query plans where the query cannot be modified. By way of completeness suggestions have also been provided for queries that can be modified.

N.B. Much of the information discussed here is recorded in other articles. However, the idea of this article is to group the required information together and reference other material as necessary. Also note that some of the suggestions will have no affect on queries that use the Rule based optimizer because they are specific to Cost based. These suggestions are highlighted as such.

SQL Query Tuning suggestions

There are usually 2 situations when a query needs be tuned.

• Query cannot be modified (e.g. where the query is generated from within the application).
• Query in question can be modified

Query cannot be modified

Often a query that is performing badly cannot be modified. This may be because the query is embedded in an application or is generated code. There are a number of possibilities for tuning such queries.

Remember that all these options can also be used for queries that can be edited.

N.B. It should be noted that all the suggestions below are workarounds to the problem of inflexibility in the front end application. Oracle provides a mechanism to modify the query plan (hints). Ultimately, if none of the workarounds below are acceptable there is little that can be done. In this case, perhaps the base application needs to be modified to accept query hints...

• Modify Statistics (CBO ONLY)

The cost based optimizer uses table, column and index statistics to determine the access path for a query.

Modification of these statistics (either reducing or increasing or even deleting) may cause the optimizer to choose a different access path for the query. Remember that the statistics that have been gathered for the optimizer's use are not 100% complete. There is a trade off between plan accuracy, the time taken to gather statistics and the space that statistics take up in the database. Also there is no attempt to hold statistics on columns whose values are clearly related in application terms. For example some column values may actually be related in reality (e.g. Town and State – Paris is always in France). Oracle does not maintain any statistics that would record this relationship.

Gather table statistics with a different sample size:

Choosing a different sample size may gather statistics that reflect the actuality of your data more closely. Usually a higher sample size is recommended but sometimes a lower sample or even no statistics can give the desired results. See the following articles for information regarding gathering statistics: Note:114671.1 and Note:44961.1

Add histograms to relevant columns:

Column histograms are a valuable tool that can increase the accuracy of statistics for columns with an uneven data distribution. Changing column statistics modifies the selectivity for predicates on those columns and so can have a favourable affect on queries. See Oracle9i Database Performance Guide and Reference Page 3-20 “Using Histograms” for more details.

• Add or remove indexes (BOTH CBO/RBO)

Additional indexes may provide a better access path for slow queries. Equally an unselective index may be causing an inefficient access path. The CBO should be able to determine the relative usefulness of different indexes if the statistics provided are adequate. If indexes are causing a problem but cannot be removed then they can be disabled using hints or other methods. See Note:122986.1

• Are there 8 or more tables in the join?

If so then the CBO may not be even considering the join order that you want to use. Consider using the Parameter:OPTIMIZER_MAX_PERMUTATIONS parameter to change the maximum number of permutations considered. Also See Note:73489.1 for information on maximum permutations considered by the optimizer.

• Create a view with embedded hints (BOTH CBO/RBO)

Often creating and then referencing a view that contains hints that force the access path that you require can force a desired access path. This is especially useful for distributed queries where a view can be built on a remote node. There is a distributed query example in Note:33838.1 Determining the execution plan for a distributed query

• Create a Materialised View (Snapshot) (BOTH CBO/RBO)

This is similar to view creation except that you store the query results somewhere. It operates by prefetching some or all of the data for the query so that it does not have to be fetched at execution time. See the Oracle9i Data Warehousing Guide Chapter 8 “Materialized Views”

• Modify Instance Parameters (BOTH CBO/RBO)

Modification of Instance wide parameters can help change the access path for queries but be aware that it may also change other access paths in a detrimental fashion.

• Modify the degree of parallelism on the table/indexes (CBO ONLY)

Modifying the degree of parallelism on a table can cause the plan to change. Increasing the degree of parallelism is likely to make full table scans appear cheaper and more attractive while reducing it will make FTS look less attractive and ultimately will stop PQO being used (if degree set to 1 or noparallel). See Note:203238.1 Using Parallel Query for more details.

• Consider using stored outlines to maintain the desired access path (CBO ONLY)

The stored outline facility provides the opportunity for developers to fix the plan of any query they submit to the database and control how it executes.

Some example implementations may include:

Test system where the good plan can be generated

This plan that could be stored and then transferred to the live environment.

Generation of a good plan for storing as an outline by Manipulating the schema to force a specific plan

For example by removing all the indexes that should not be selected / making indexes look very attractive for example by using punitive FTS parameters (reduce DB_FILE_MULTIBLOCK_READ_COUNT to 1) and encouraging index parameters (OPTIMIZER_INDEX_COST_ADJ & OPTIMIZER_INDEX_CACHING) a desired plan may be chosen since no other choices are available. Then storing an outline under these conditions, reverting the changes and applying the outline to force the desired plan.

For reference, please refer to:

Note:67536.1 Stored Outline Quick Reference

Note:463288.1 How to generate an outline with a good plan loaded into shared_pool

Note:102311.1 How to Move Stored Outlines for One Application from One Database to Another

Oracle9i Database Performance Guide and Reference Chapter7 “Using Plan Stability”

@ Also See Note:92202.1 which shows how you can manipulate outlines – this is NOT supported.

Query can be modified

Modifying the query should really be the preferred option as hints are the recommended method for modifying access paths.

• Add Hints (BOTH CBO/RBO)

Hints are directives added to the select list of a query. They force the query to operate in a particular manner. Hints are available to alter all manner of factors that affect queries including single table access methods, join order and methods.

See Note:29236.1 QREF: SQL Statement HINTS

Advanced hint usage:

See the following notes for more information on using hints effectively:

Note:50607.1 How to specify an index hint

Note:62364.1 Hints and subqueries

Note:69992.1 Why is my hint ignored?

Note:68084.1 Using hints to optimize an Update with subquery not using index on updated table

• Create inline views (BOTH CBO/RBO)

Inline views are a technique that effectively embeds a view definition within the from clause of a query. It is useful as the inline view can be modified to prevent merging occurring and force query blocks to be executed separately from the rest of the query due to view merging restrictions. Forcing the query block to execute separately can also have performance benefits in some circumstances.

• Add more selective predicates (BOTH CBO/RBO)

It is possible that the existing predicates for a query are unselective and that other predicates can be added to reduce the result set earlier in the query execution. This may have the effect of reducing the number of rows handled by the query early on and affect the performance of the query. Obviously it is important not to change the result of the query.

• Divide up the work of one query into multiple queries (BOTH CBO/RBO)

A query may be able to be broken into a number of smaller queries. These could then be run concurrently or serially. The effect of this may be to reduce memory and sort space requirements. The outputs of the smaller queries could be inserted in to a temporate the global result.

• Rewrite the query in plsql (BOTH CBO/RBO)

It may be possible to rewrite the query in pl/sql and thus take advantage of the procedural code that this offers. Some queries can benefit from running procedurally.

• Rewrite the query (BOTH CBO/RBO)

Re-writing a complex query may open up additional access paths or force the query to operate in a different way.

Final Note

Remember that if your query:

• Returns large volumes of data
• Has unselective predicates that cause much data to be searched to return the data you want
• Is maxing out physical aspects of the hardware

then you may not be able to improve its performance other than increasing the processing power of your hardware (i.e. cpu/memory upgrades etc).

RELATED DOCUMENTS

Note:73489.1 Affect of Number of Tables on Join Order Permutations

Note:33838.1 Determining the execution plan for a distributed query

Note:203238.1 Using Parallel Query

@ Note:92202.1 Manipulating Stored Outlines – this is NOT supported

Note:29236.1 QREF: SQL Statement HINTS

Note:50607.1 How to specify an index hint

Note:62364.1 Hints and subqueries

Note:69992.1 Why is my hint ignored?

Note:68084.1 Using hints to optimize an Update with subquery not using index on updated table

Recommendations for Gathering Optimizer Statistics on 10g

Quick Recreate Recommendation

To achieve a quick delete and recreate of the statistics on an individual table and it's indexes (adding column statistics for any skewed columns) and following the recommendations in this article use:

exec dbms_stats.delete_table_stats(ownname=>'user_name',-

tabname=>'table_name',cascade_indexes=>true);

exec dbms_stats.gather_table_stats(ownname=>'user_name',-

tabname=>'table_name',-

estimate_percent => 100,-

cascade=>true,-

method_opt=>'for all columns size skewonly');

For explanation of these recommendations, see below. For more usage examples see the end of this article.

Note that, from 10gR2 statistics can be restored using:

Note:452011.1 Master Note: Restoring table statistics in 10G onwards

IMPORTANT: PLEASE NOTE:

• These recommendations apply to the majority of databases.
• The recommendations aim to generate statistics with as much statistical accuracy as possible. To this end 100% sample sizes are suggested since any reduction in sample size is always a concession to accuracy. It is acknowledged that such 100% samples are potentially time consuming and consideration needs to be made to fit the statistics gathering activities within the existing maintenance window.
  
• Gathering new optimizer statistics should maintain or improve existing execution plans, but it is possible that some queries performance may degrade. Note that from 10gR1 previous copies of statistics are maintained by default for the last 30 days and can be restored in the case of problems. See:
  Note:452011.1 Master Note: Restoring table statistics in 10G onwards
• Gathering new optimizer statistics may invalidate cursors in the shared pool so it is prudent to restrict all gathering operations execution to periods of low activity in the database, such as the scheduled maintenance windows.
• For very large systems, the gathering of statistics can be a very time consuming and resource intensive activity.In this environment sample sizes need to be carefully controlled to ensure that gathering completes within acceptable timescale and resource constraints and within the maintenance window. For guidance on this topic See:
  

Note:44961.1 Statistics Gathering: Frequency and Strategy Guidelines

In these environments, it is also recommended to utilise change based statistics gathering to avoid re-gathering information unnecessarily.Please see:

Note:237901.1 Gathering Schema or Database Statistics Automatically - Examples

Note:377152.1 Best Practices for automatic statistics collection on Oracle 10g

Gathering Object statistics

The Cost Based Optimizer (CBO) uses statistics to determine the execution plan for a particular query. Potentially, with reduced sample sizes, sampling could produce different statistics due to chance groupings of data that may be the result of differing loading methods etc.

On 10g it is recommended to:

• Gather statistics using scheduled statistics gathering scripts. In most cases the default scripts provide an adequate level of sampling taking into account the following recommendations:
  
• Use sample size that is large enough. On 10g support suggests an estimate sample size of 100% (if it is possible for this to fit within the maintenance window), even if that means that statistics are gathered on a reduced frequency. If 100% is not feasible, try using at least an estimate of 30%. Generally, the accuracy of the statistics overall outweighs the day to day changes in most applications. This setting is because the default AUTO_SAMPLE_SIZE uses a very small estimate percentage which can result in poor estimates.
  
• Ensure all objects (tables and indexes) have stats gathered. An easy way to achieve this is to use the CASCADE parameter.
• Ensuring that any columns with skewed data distribution have histograms collected, and at sufficient resolution using the METHOD_OPT parameter. Support recommends a conservative and more plan-stable approach of "adding a histogram only if it is known to be needed" rather than collecting column statistics on all columns. This can be achieved manually or by using the SKEWONLY option to automatically add column statistics to columns that contain data with a non-uniform distribution. Using the default column statistics setting of AUTO which means that DBMS_STATS will decide which columns to add histogram to where it believes that they may help to produce a better plan. If statistics are not completely up to date then the presence of Histograms can cause trouble when parsing values are out of range, or between values for "frequency" histograms. In these circumstances the optimizer has to make guesses which may be inaccurate and, on occasion, cause poor plans.
  
  
  Note that in earlier versions the default setting for the METHOD_OPT parameter was "FOR ALL COLUMNS SIZE 1" which would collect only a high and a low value and effectively meant that there were no detailed column statistics. It is known that in some cases, the effect of a histogram is adverse to the generation of a better plan so users moving between versions may initially wish to set this parameter to its pre-upgrade release value, and later adjust to the post-upgrade release default value. See:

  Note:465787.1 Managing CBO Stats during an upgrade to 10g or 11g
  

  As ever, testing different values with the application will yield the best results.

• If partitions are in use, gather global statistics if possible due to time constraints. Global stats are very important but gathering is often avoided due to the sizes involved and length of time to required. If 100% samples are not possible then support would recommend going for a minimum of 1%. Gathering with small sample sizes (e.g. 0.001, 0.0001, 0.00001 etc. ) can be very effective but equally, a large proportion of the data will not be examined which could prove decisive to the optimizer's plan choices. Note that the available range for the ESTIMATE_PERCENT parameter is a very flexible [0.000001 -> 100] which can use very small sample sizes suitable for huge partitioned tables. Testing will reveal the most suitable settings for each system.
  See:

  Note:236935.1 Global statistics - An Explanation

• Gather system statistics to reflect the CPU loading of the system and to improve the accuracy of the CBO's estimates by providing the CBO with CPU cost estimates in addition to the normal I/O cost estimates. See:

    Note:470316.1 Using Actual System Statistics (Collected CPU and IO information
    Note:149560.1 Collect and Display System Statistics (CPU and IO) for CBO usage
    Note:153761.1 Scaling the System to Improve CBO optimizer

Note that the defaults for statistics gathering on different versions of Oracle are not necessarily the same, for example:

• ESTIMATE_PERCENT: defaults:
  • 9i : 100%
  • 10g : DBMS_STATS.AUTO_SAMPLE_SIZE (using very small estimate percentage)
  • 11g : DBMS_STATS.AUTO_SAMPLE_SIZE (using larger estimate percentage - 100%)
• METHOD_OPT: defaults:
  • 9i : "FOR ALL COLUMNS SIZE 1" effectively no detailed column statistics.
  • 10g and 11g : "FOR ALL COLUMNS SIZE AUTO" - This setting means that DBMS_STATS decides which columns to add histogram to where it believes that they may help to produce a better plan.

In 11g, using auto size for ESTIMATE_PERCENT defaults to 100% and therefore is as accurate as possible. Additionally, even though a 100% sample is collected, the gathering process is really fast since a new hashing algorithm is used to compute the statistics rather than sorting (in 9i and 10g the "slow" part was typically the sorting). In 10g the default ESTIMATE_PERCENT sample size was extremely small which often resulted in poor statistics and is therefore not recommended.

SAMPLE STATISTIC GATHERING COMMANDS

Gathering statistics an individual table

    
exec dbms_stats.gather_table_stats(  -
       ownname => '  Schema_name ', -
       tabname => '  Table_name  ', -
       estimate_percent => 100,  -
       cascade => TRUE,  -
       method_opt => 'FOR ALL COLUMNS SIZE 1' );  

N.B. replace '  Schema_name ' and '  Table_name  ' with the name of the schema 
and table  to gather statistics for respectively.
Gathering statistics for all objects in a schema
     
exec dbms_stats.gather_schema_stats( -
       ownname => '  Schema_name ', -
       cascade => TRUE, -
       method_opt => 'FOR ALL COLUMNS SIZE 1' );

N.B. replace '  Schema_name ' with the name of the desired schema.    
Gathering statistics for all objects in the database:
        
exec dbms_stats.gather_database_stats( -
       cascade => TRUE, -
       method_opt => 'FOR ALL COLUMNS SIZE 1' );

NOTE:

For cases where column data is known to be skewed and column statistics are known to be beneficial, Replace:

method_opt => 'FOR ALL COLUMNS SIZE 1'

with

method_opt => 'FOR ALL COLUMNS SIZE AUTO'

or with

method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY'

to automatically add column statistics to columns that contain data with a non-uniform distribution.

@ dbms_stats dbms_stats  dbms_stats  dbms_stats  dbms_stats 
References

NOTE:153761.1 - System Statistics: Scaling the System to Improve CBO optimizer

NOTE:236935.1 - Global statistics - An Explanation

NOTE:237901.1 - Gathering Schema or Database Statistics Automatically in 8i and 9i - Examples

NOTE:377152.1 - Best Practices for automatic statistics collection on Oracle 10g

NOTE:388474.1 - Master Note: Recommendations for Gathering Optimizer Statistics on 9i

NOTE:44961.1 - Statistics Gathering: Frequency and Strategy Guidelines

NOTE:452011.1 - Master Note: Restoring table statistics in 10G onwards

NOTE:465787.1 - Managing CBO Stats during an upgrade to 10g or 11g

NOTE:470316.1 - Using Actual System Statistics (Collected CPU and IO information)

Advantages and Disadvantages of using smaller and bigger data blocks

Advantages and Disadvantages of using smaller and bigger data blocks
Whether I will use bigger or smaller data blocks in my database it can be specified by parameter DB_BLOCK_SIZE or DB_nK_CACHE_SIZE. With the settings I can I can differentiate three types of data blocks in oracle.

1)Small Block(2KB-4KB)
2)Medium Block(8KB)
3)Large Block(16KB-32KB)

Advantages of Bigger Blocks
--------------------------------------------------------------------
•Using bigger blocks means more data transfer per I/O call. So faster data transfer from disk to memory.

•Using bigger blocks means more space for key storage in the branch nodes of B*-tree indexes, which reduces index height, which improves the performance of indexed queries.

•When using large block there are less probability of chained and migrated rows, which in turn reduced the number of reads required to get the information.

Disadvantages of bigger Blocks
----------------------------------------------------------------------
•If the rows are predominated random then you are increasing the possibility of contention in the buffer cache. Because now with same same amount of memory in buffer cache as it was in small blocks, we need more memory in the buffer cache to keep the same amount of buffers in memory in the buffer cache.

•If you have high transactional concurrency to a segment, using bigger blocks is just going to make the concurrency even higher.


Advantages and disadvantages of these blocks
---------------------------------------------------------------------
1)Small Block(2KB-4KB): The advantage of small blocks are they reduce block contention and they are really good where there is small rows or the selectivity of rows are highly random.

The disadvantages of small blocks are they have relatively larger overhead.

2)Medium Block(8KB): The advantage of medium blocks are if the rows are of medium size then you can bring a number of rows in a single I/O.

The disadvantage of it is space in the buffer cache will be wasted if you are doing random access to small rows and have a large block size. For example, with an 8KB block size and 60 byte row size, you are wasting 8000-60=7940 bytes in the buffer cache when doing random access.

3)Large Block(16KB-32KB): If you use larger block then relatively less overhead. Per I/O you can fetch more data. This is very good for sequential access, or very large rows.

Large block size is not good for index blocks used in an OLTP(Online Transaction Processing) type environment, because they increase block contention on the index leaf blocks.

Related Documents:
-------------------------
General Idea of Database Block Size and BLOCKSIZE
Choose an optimal Data block size in Oracle

DB_BLOCK_SIZE, DB_nK_CACHE_SIZE and BLOCKSIZE
First I want to say about BLOCKSIZE cluase in oracle. The BLOCKSIZE cluase is used to specify the block size for the tablespace. If you don't specify this clause while tablespace creation then standard that is default blocksize is used which is specified by parameter DB_BLOCK_SIZE. For example, You have DB_BLOCK_SIZE set to 8K=8192 and you specified CREATE TABLESPACE command without any BLOCKSIZE clause then database use 8k blocksize for the specified tablespace.

The parameter DB_BLOCK_SIZE specifies the size of Oracle database blocks in bytes. The default value of this parameter is 8192 and value ranges between 2048 to 32768. But it must be multiple of physical block size at device level.

Now lets have an attention of the DB_nK_CACHE_SIZE parameters. Here n is variable and can be 2, 4, 8, 16, 32. That is DB_2K_CACHE_SIZE, DB_4K_CACHE_SIZE, DB_16K_CACHE_SIZE etc are available. Now question may come why we will set DB_nK_CACHE_SIZE? What advantage we will get. Before look for advantage (which will be discussed in another topic) let me why we need to set this parameter? This parameter will need to be set whenever I wish to make or want to make a tablespace with non-standard data block size.

Suppose your standard block size that block size set by DB_BLOCK_SIZE is 8K and you want to make a tablespace with block size 16K then at first you need to set DB_16K_CACHE_SIZE and then you need to create tablespace with BLOCKSIZE clause specifying 16K.

Here is an example which shows database standard block size is 8k and you have made an tablespace with 16k blocksize.

Example:
------------------------------
SQL> show parameter db_block_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192

ALTER SYSTEM SET DB_16K_CACHE_SIZE=200M SCOPE=BOTH;
ALTER SYSTEM SET DB_CREATE_FILE_DEST='/oradata2';
CREATE TABLESPACE TEST BLOCKSIZE 16K;

Remember You can set this parameter only when DB_BLOCK_SIZE has a value other than nK. For example, if DB_BLOCK_SIZE=8192, then it is illegal to specify the parameter DB_8K_CACHE_SIZE Because the size for the 8 KB block cache is already specified by DB_CACHE_SIZE.

Choose an optimal Data block size in Oracle
Before going to proceed it will be better if you have an idea about DB_BLOCK_SIZE, BLOCKSIZE and DB_nK_CACHE_SIZE. In my post B_BLOCK_SIZE, BLOCKSIZE and DB_nK_CACHE_SIZE I tried to give an idea.

The default database data block buffer size is specified by the parameter DB_BLOCK_SIZE. It is common to ask what will be the data block size of my oracle database? Should I increase or decrease data block size whenever there is performance issue of my database. There is common scenario that with one blocksize a query takes 30 minutes and with another query of a tablespace a query takes 2 or 3 minutes. So choosing an optimal data block size is very necessary task.

The general rule while choosing an optimal data block size is,

•For OLTP (Online Transaction Processing) systems use smaller block sizes.
•For DSS (Decision Support Systems) systems use larger block sizes.

Whatever the size of the data, the goal is to minimize the number of reads required to retrieve the desired data.

•If the rows are small and access to the rows are random , then choose a smaller block size.
•If the rows are small and access is sequential, then choose a larger block size.
•If the rows are small and access is both random and sequential, then it might be effective to choose a larger block size.
•If the rows are large, such as rows containing large object (LOB) data, then choose a larger block size.


In my next topics I demonstrated on advantage and disadvantage of settings different types of blocks.

Parameters that enable and control Query Optimizer Features

Parameters that enable and control Query Optimizer Features
You know that Query optimizer is responsible to determine the best execution/explain plan. In many cases you may be astonished that the data is same in both database and you have gather statistics but both database give different execution plan. The possible reason for it it the variation of initialization parameter between two database. Now in the later section in this post we will have a look at the initialization parameter that affect the optimizer to determine execution plan.

A)Enable Query Optimizer Feature
------------ --------- --------- --------- -----
OPTIMIZER_FEATURES_ ENABLE Parameter
------------ --------- --------- --------- ---------
•It is a string type parameter and takes oracle version number as argument.
•Based on this parameter settings it is determined how oracle optimizer behaves.
•Every new release of oracle version comes with new feature for optimizer. Thus new version of optimizer can collect extra features of a query based on which execution plan can changes. If you upgrade your oracle to newer version and your don't want to change your execution plan according to new one (keep like older) then you can set this parameter to older one.
•The valid values of this parameter can be,
8.0.0 | 8.0.3 | 8.0.4 | 8.0.5 | 8.0.6 | 8.0.7 | 8.1.0 | 8.1.3 | 8.1.4 | 8.1.5 | 8.1.6 | 8.1.7 | 9.0.0 | 9.0.1 | 9.2.0 | 10.0.0 | 10.1.0 | 10.1.0.3 | 10.1.0.4 | 10.2.0.1|10. 2.0.2|10. 2.0.3| etc.


B)Control the Behavior of the Query Optimizer
------------ --------- --------- --------- --------- --------- --------- ---------
Here is the list of initialization parameters that can be used to control the behavior of the query optimizer.

1)CURSOR_SHARING
2)DB_FILE_MULTIBLOC K_READ_COUNT
3)OPTIMIZER_ INDEX_CACHING
4)OPTIMIZER_ INDEX_COST_ ADJ
5)OPTIMIZER_ MODE
6)PGA_AGGREGATE_ TARGET
7)STAR_TRANSFORMATI ON_ENABLED

1)CURSOR_SHARING: •The optimizer generates the execution plan based on the presence of the bind variables but not the actual literal values.

•Based on the settings of this parameter -CURSOR_SHARING it converts literal values in SQL statements to bind variables and affect the execution plan of SQL statements.

•This parameter determines what kind of SQL statements can share the same cursors and can have any of either three values EXACT or SIMILAR or FORCE.

•If it is set to EXACT then it only allows statements with identical text to share the same cursor.

•SIMILAR settings causes statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect either the meaning of the statement or the degree to which the plan is optimized. SIMILAR is default.

•Forces specified that statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect the meaning of the statement.

2)DB_FILE_MULTIBLOCK_READ_COUNT:•This parameter specifies the number of blocks that are read in a single I/O during a full table scan or index fast full scan.

•The optimizer uses the value of DB_FILE_MULTIBLOCK_READ_COUNT to cost full table scans and index fast full scans.

•Larger values result in a cheaper cost for full table scans and can result in the optimizer choosing a full table scan over an index scan.

•If this parameter is not set explicitly (or is set is 0), the optimizer will use a default value of 8 when costing full table scans and index fast full scans.

3)OPTIMIZER_INDEX_CACHING:•This parameter controls the costing of an index probe in conjunction with a nested loop.

•The range of values 0 to 100 for OPTIMIZER_INDEX_CACHING indicates percentage of index blocks in the buffer cache, which modifies the optimizer's assumptions about index caching for nested loops and IN-list iterators.

•A value of 100 infers that 100% of the index blocks are likely to be found in the buffer cache and the optimizer adjusts the cost of an index probe or nested loop accordingly.

•Use caution when using this parameter because execution plans can change in favor of index caching.

4)OPTIMIZER_INDEX_COST_ADJ:•OPTIMIZER_INDEX_COST_ADJ lets you tune optimizer behavior for access path selection to be more or less index friendly—that is, to make the optimizer more or less prone to selecting an index access path over a full table scan.

•The default for this parameter is 100 percent, at which the optimizer evaluates index access paths at the regular cost.

•Any other value makes the optimizer evaluate the access path at that percentage of the regular cost. For example, a setting of 50 makes the index access path look half as expensive as normal.

5)OPTIMIZER_MODE:This parameter is discussed on About Parameter OPTIMIZER_MODE
6)PGA_AGGREGATE_TARGET: This parameter automatically controls the amount of memory allocated for sorts and hash joins. Larger amounts of memory allocated for sorts or hash joins reduce the optimizer cost of these operations. This parameter is discussed on About PGA_AGGREGATE_TARGET parameter
7)STAR_TRANSFORMATION_ENABLED: •STAR_TRANSFORMATION_ENABLED determines whether a cost-based query transformation will be applied to star queries.

•If it is set to TRUE the optimizer will consider performing a cost-based query transformation on the star query.

•If it is set to FALSE the transformation will not be applied.

•If it is set to TEMP_DISABLE the optimizer will consider performing a cost-based query transformation on the star query but will not use temporary tables in the star transformation.

Moving a Table to a New Segment or Tablespace

Moving a Table to a New Segment or Tablespace
•The ALTER TABLE...MOVE statement enables you to relocate data of a non-partitioned table or of a partition of a partitioned table into a new segment, and optionally into a different tablespace.

•This statement also lets you modify any of the storage attributes of the table or partition, including those which cannot be modified using ALTER TABLE.

•It is good to remember that The ALTER TABLE...MOVE statement does not permit DML against the table while the statement is executing. If you want to leave the table available for DML while moving it it the you have to use DBMS_REDEFINITION package to move online.

•Moving a table changes the rowids of the rows in the table. This causes indexes on the table to be marked UNUSABLE, and DML accessing the table using these indexes will receive an ORA-01502 error. The indexes on the table must be dropped or rebuilt. Likewise, any statistics for the table become invalid and new statistics should be collected after moving the table.

Example:
------------ ---
SQL> select TABLESPACE_NAME, HEADER_FILE, HEADER_BLOCK from dba_segments where owner='ARJU' and segment_name= 'TEST';

TABLESPACE_NAME HEADER_FILE HEADER_BLOCK
------------ --------- --------- ----------- ------------
USER_TBS 11 1283

Here HEADER_BLOCK indicates the ID of the block containing the segment header. And HEADER_FILE is the data file id. If I move the table to a new segment then header block number will be change. Lets have a look at it.

SQL> alter table test move;
Table altered.

SQL> select TABLESPACE_NAME, HEADER_FILE, HEADER_BLOCK from dba_segments where owner='ARJU' and segment_name= 'TEST';

TABLESPACE_NAME HEADER_FILE HEADER_BLOCK
------------ --------- --------- ----------- ------------
USER_TBS 11 1459

Peoplesoft Administration Tools

Peoplesoft Administration Tools/Enterprise People Tools

Peoplesoft admin tools commonly referred to as Enterprise people tools make maintenance easy and cost-effective.

Uses Of People Tools :
1) Manage the applications deployed in the environment
2) Perform admin tasks such as configuring security,monitor performance,troubleshoot,upgrade to new version,apply patches,transfer data from target to source system.
3) Admin tasks can be performed using the browser interface that is used to perform peoplesoft business transaction

List Of Admin Tools :
1) PeopleTools Security.
2) PeopleSoft Performance Monitor.
3) PeopleSoft Diagnostic Framework.
4) PeopleSoft Data Mover.
5) Data Integrity Tools.
6) PeopleTools Utilities.
7) PeopleSoft Configuration Manager.

PeopleTools Security :
Peopletools security addresses the security threats by grouping access privileges(authorizations) in the form of rolesThere can be Manager role,admin role etc which restricts the access.There are different users when the application is deployed in production.The application can be accessed via internet and poses security threat.Limiting access through roles addresses this issue.Different users include suppliers,vendors,internet users,employees,customers.PeopleTools Security enables us to manage the role-based system, including directory group import capabilities, to leverage your Lightweight Directory Access Protocol (LDAP) directory facilities.

Peoplesoft Performance Monitor:
It is a built-in utility that enables sysadmins to monitor the performance of main elements in peoplesoft system,such as web servers,application servers,peplesoft process scheduler servers.
We can analyze historical data and monitor real-time performance.
We get the following reports using peoplesoft performance monitor:
1) Durations and key metrics of PeopleTools runtime execution, such as SQL statements and PeopleCode events.
2) Key resource metrics, such as host CPU utilization and web server execution threads.
The metrics provided by PeopleSoft Performance Monitor enable system administrators to:
1) Monitor real-time system performance.
2) Identify poorly performing tiers, hosts, domains, servers, application code, and SQL in a PeopleSoftenvironment.
3) Identify performance trends.
4) Address and isolate performance bottlenecks.

Peoplesoft Diagnostic Framework :
Peoplesoft Diagnostic Framework is designed to diagonize and resolve incidents by Peoplesoft Global Support Centre(GSC).
It enables the critical system data to be packaged and sent to PeopleSoft Global Support Center without the need for IT intervention. This reduces dministration overhead and can help PeopleSoft solve customer issues more quickly.

The diagnostic framework retrieves diagnostic information from a PeopleSoft database enabling us to:
1) Discover problematic application-related data.
2) Explore setup details.
3) Present information to PeopleSoft support in a common format.

Peoplesoft Datamover:
Peoplesoft datamover provides a development interface that enables us to script various maintenance tasks.Scripts can be run from command-line or development environment.

PeopleSoft Data Mover enables us to perform the following tasks:
1) Transfer application data between PeopleSoft databases.
2) Move PeopleSoft databases across operating systems and database platforms.
3) Execute Structured Query Language (SQL) statements against any PeopleSoft database, regardless of the underlying operating system or database platform.
4) Create, edit, and run scripts.

Peoplesoft Data Archive Manager:
Data Archiving that enables transaction recovery and maintains data integrity is critical for any application and Peoplesoft data can be archived using Peoplesoft data archive manager.

PeopleSoft Data Archive Manager provides an integrated and consistent framework for archiving data from PeopleSoft applications. Using a predefined template, we can select any queries and multiple objects that meet our archiving and restoration requirements. Leveraging the Archive Query in
PeopleSoft Query, we can easily define and customize your archive template.

Data Integrity Tools :
It is used to verify integrity of data in peoplesoft system.

We use these tools during installations, upgrades, and system configuration to verify the PeopleSoft system and compare it to the underlying SQL objects to ensure synchronization.

Data Integrity tools include:

SYSAUDIT - The System Audit (SYSAUDIT) identifies orphaned PeopleSoft objects and other inconsistencies within the system. An example of an orphaned object is a module of PeopleCode that exists, but which does not relate to any
other objects in the system.

DDDAUDIT - The Database Audit Report (DDDAUDIT) finds inconsistencies between PeopleTools record and index definitions and the database objects. This audit focuses on tables, views, and indexes.

SQL Alter - The PeopleSoft Application Designer SQL Alter function brings SQL tables into accordance with PeopleTools record definitions.

Peopletool Utilities :
The PeopleTools Utilities provides a set of tools for accomplishing some of these
more infrequent tasks.

Examples of tasks that require the use of the PeopleTools Utilities are:
1) Setting your base language.
2) Specifying a specific style sheet.
3) Setting PeopleBook Help locations.
4) Editing messages in the message catalog.
5) Entering values in the URL catalog.
6) Setting PeopleCode and SQL trace options.

Peoplesoft Configuration Manager :
PeopleSoft Configuration Manager simplifies Windows workstation administration by enabling us to adjust PeopleSoft registry settings from one central location.

The Configuration Manager applies only to workstations being used by developers and system administrators who need access to the development
environment. For example, developers who need to access PeopleSoft Application Designer to view and modify pages, records, components and so on need the appropriate options set in the PeopleSoft Configuration
Manager.

PeopleSoft Configuration Manager contains a variety of controls that let us set up workstations for connecting to the database, connecting to application servers, or setting trace options.

We can set up one development workstation to reflect the environment at our site, and then export the configuration file, which can be shared among all the development workstations at our site.

Delete logfile older date

In Oracle database there are some files which reguarly create day by day such as trace files, log files, archivelog files & Exported dump files.

In this article i will show how to remove above files as batch job.



Linux platform



We can use find command for this purpose.



for example:



find $TRACEFILE/* .trc -mtime +7 -exec rm {} \;



above script remove all .trc files which older than 7 days.



Schedule: Through crontab utility schedule above script.



For Reference



Windows platform



We can use FORFILES.exe for this purpose



find more about forfiles.exe



for example:



K:\FORFILES. EXE /P K:\archive_dump_ files -s /M *.* /D -5 /C "cmd /c del @FILE echo @FILE"



In above example files deleted older than 5 days from "archive_dump_ files" location.



Schedule: We can use WINDOWS SCHEDULER TASK for this purpose.

Migrating Database To ASM Using RMAN

Migrating Database To ASM Using RMAN

RMAN is the only tool that can backup ASM files.It is not possible to backup using :
1)user-managd bakups
2) Os utilities and commands - they cant see ASM files as ASM files are created on raw disk and there is no file system for ASM files.

RMAN is used to migrate database to ASM.Follow the steps below.

Consider the case where we have an instance named "neworacle".We have three ASM diskgroups dgnew1 - dtles,dgnew2 - controlfile, dgnew3 - online log files.

Follow the steps given below:

1) Change COROLFILES instance parameter to point towards disk group.
SQL> Alter system set controlfiles='+dgnew2','+dgnew3' scope=spfile;

2) Shutdown the instance.
SQL> shutdown immediate;

3) Start database in nomount mode.
SQL>startup nomount;

4) Launch RMAN(RMAN client),restore controlfile from its original location:
RMAN> restore controlfile from '/originalloc/contrl1.con';

5) Issue the steps from RMAN prompt.We can script the list of steps and execute from RMAN prompt as well.This migrates the datafiles from its original location to ASM.
RMAN>shutdown immediate;
RMAN> startup mount;
RMAN>backup as copy database format '+dgnew1';
RMAN> switch database to copy;
RMAN>alter database open;

6) To migrate redologs, create new members in the diskgroups and drop the old members.
SQL>alter database add logfile member '+dgnew2','+dgnew3' to group 1;
SQL>alter database drop logfile member '/originalloc/log1a.rdo','/originalloc/log1b.rdo';

7) Final step is to move tempfiles.It is not possible to move tempfiles(temporary tablespace).So drop and recreate the temporary tablespace on a disk group.

Comments in Oracle

Comments in Oracle
You can comments to any SQL statements or any schema objects.

A)Comments Within SQL Statements
------------ --------- --------- --------- -
Comments within SQL statements do not affect the statement execution. The only exception is use of hints. With use hints the SQL statements is affected. In fact comment is used to make your application easier for you to read and maintain.

Within SQL statements you can include comment in two ways.
1)With /* and */
------------ --------- -----
Format is,
Begin with slash (/) and an asterisk (*) together (/*) + comment Text + End with an asetrisk (*) and a slash (/) together (*/).

With this format text can span multiple lines.

The opening and terminating characters need not be separated from the text by a space or a line break.

Example:
-----------
SQL> select second_col /*This is column
2 Name */ from test /*This is Table Name*/ ;

2)with --
------------ -----
The text that begin with two hyphens (--) is considered as comments. The comments written in this way can't span multiple lines. End the comment with a line break.

Example:
------------
SQL> select second_col -- THIS IS COMMENT
2 FROM TEST;

B)Comments on Schema Objects

Recycle Bin

Recycle Bin

What is Recycle Bin?
It is a feature in recent Oracle database versions that is used to enable the Flashback Drop feature.Recyclebin is an initialization parameter in the initSID.ora file.By default Recyclebin feature is enabled.

How can you enable and disable recyclebin?
We can enable and disable the recyclebin feature using the following commands:ALTER SESSION,ALTER SYSTEM.

How do you enable Recyclebin feature?
ALTER SESSION SET recyclebin = ON;
ALTER SYSTEM SET recyclebin = ON;

How do you disable REcyclebin feature?
ALTER SESSION SET recyclebin = OFF;
ALTER SYSTEM SET recyclebin = OFF;

What are the views used to manage recyclebin?
USER_RECYCLEBIN- Information about recyclebin of a particular user
DBA_RECYCLEBIN - Information on all dropped objects in the recyclebin. This can be used by administrator

Does disabling purges all the objects already in the recyclebin?
Disabling the recycle bin does not purge or otherwise affect objects already in the recycle bin.

How do you recyclebin from SQL*PLUS prompt?
We can use the following SQL*PLUS command:
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
EMPLOYEES BIN$yrMKlZaVMhfgNAgAIMenRA==$0 TABLE 2003-10-27:14:00:19

How to permanently remove objects from the recyclebin?
We can permanently remove the objects from the recyclebin using PURGE Command.Few examples as given below:
PURGE TABLE BIN$jsleilx392mk2=293$0;
PURGE TABLE int_admin_emp;
PURGE TABLESPACE example;
PURGE TABLESPACE example USER oe;
PURGE RECYCLEBIN;

How do we restore objects from a recyclebin?
Use the FLASHBACK TABLE ... TO BEFORE DROP statement to recover objects from the recycle bin.

Give an example for restoring objects from recyclebin?
FLASHBACK TABLE int_admin_emp TO BEFORE DROP
RENAME TO int2_admin_emp;
FLASHBACK TABLE BIN$yrMKlZaVMhfgNAgAIMenRA==$0 TO BEFORE DROP;

What happens to dependent objects when we restore them from recyclebin?
When you restore a table from the recycle bin, dependent objects such as indexes do not get their original names back; they retain their system-generated recycle bin names. We must manually rename dependent objects if we want to restore their original names. If we plan to manually restore original names for dependent objects, ensure that we make note of each dependent object's system-generated recycle bin name before you restore the table.

Give an example of restoring table and its dependent objects:-
1) After dropping JOB_HISTORY and before restoring it from the recycle bin, run the following query:

SELECT OBJECT_NAME, ORIGINAL_NAME, TYPE FROM RECYCLEBIN;
OBJECT_NAME ORIGINAL_NAME TYPE
------------------------------ ------------------------- --------
BIN$DBo9UChtZSbgQFeMiAdCcQ==$0 JHIST_JOB_IX INDEX
BIN$DBo9UChuZSbgQFeMiAdCcQ==$0 JHIST_EMPLOYEE_IX INDEX
BIN$DBo9UChvZSbgQFeMiAdCcQ==$0 JHIST_DEPARTMENT_IX INDEX
BIN$DBo9UChwZSbgQFeMiAdCcQ==$0 JHIST_EMP_ID_ST_DATE_PK INDEX
BIN$DBo9UChxZSbgQFeMiAdCcQ==$0 JOB_HISTORY TABLE

2) Restore the table with the following command:

FLASHBACK TABLE JOB_HISTORY TO BEFORE DROP;

3) Run the following query to verify that all JOB_HISTORY indexes retained their system-generated recycle bin names:

SELECT INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME = 'JOB_HISTORY';

INDEX_NAME
------------------------------
BIN$DBo9UChwZSbgQFeMiAdCcQ==$0
BIN$DBo9UChtZSbgQFeMiAdCcQ==$0
BIN$DBo9UChuZSbgQFeMiAdCcQ==$0
BIN$DBo9UChvZSbgQFeMiAdCcQ==$0

4) Restore the original names of the first two indexes as follows:

ALTER INDEX "BIN$DBo9UChtZSbgQFeMiAdCcQ==$0" RENAME TO JHIST_JOB_IX;
ALTER INDEX "BIN$DBo9UChuZSbgQFeMiAdCcQ==$0" RENAME TO JHIST_EMPLOYEE_IX;

Double quotes are required around the system-generated names.

How to Create and Use OMF

How to Create and Use OMF
OMF indicates Oracle Managed Files. With the use of Oracle-managed files the administration of an Oracle Database can be simplified. Oracle-managed files eliminate the need for you, the DBA, to directly manage the operating system files comprising an Oracle Database. You specify operations in terms of database objects rather than filenames.

Enable the Creation of OMFs
--------------------------------
The following initialization parameters allow the database server to use the Oracle-managed files feature.

1)DB_CREATE_FILE_DEST: Defines the location of the default file system directory where the database creates datafiles or tempfiles when no file specification is given in the creation operation. It is also used as the default file system directory for redo log and control files if DB_CREATE_ONLINE_LOG_DEST_n is not specified.

2)DB_CREATE_ONLINE_LOG_DEST_n:Defines the location of the default file system directory for redo log files and control file creation when no file specification is given in the creation operation. You can use this initialization parameter multiple times, where n specifies a multiplexed copy of the redo log or control file. You can specify up to five multiplexed copies.

3)DB_RECOVERY_FILE_DEST:Defines the location of the default file system directory where the database creates RMAN backups when no format option is used, archived logs when no other local destination is configured, and flashback logs. Also used as the default file system directory for redo log and control files if DB_CREATE_ONLINE_LOG_DEST_n is not specified.

Both of these initialization parameters are dynamic, and can be set using the ALTER SYSTEM or ALTER SESSION statement.

An Example of using OMF :
---------------------------
1)Setting the parameter for the session:

SQL> alter session set db_create_file_dest='/oradata';
Session altered.

2)Create Tablespace using OMF:

SQL> create tablespace omf_tbs;
Tablespace created.

3)Check the data file Location:

SQL> select file_name from dba_data_files where tablespace_name='OMF_TBS';
FILE_NAME
--------------------------------------------------------------------------------
/oradata/ARJUT/datafile/o1_mf_omf_tbs_4049w4op_.dbf

Here ARJUT is the Database Name.

The dafault location for datafile is Your settings for parameter/Database Name/datafile/Unique Name.dbf

Managing Oracle Processes

Managing Oracle Processes
In this post we are going to discuss the starting and stopping of various oracle processes including database instances,ASM(Automatic Storage Management) instances,CSS Daemon,Oracle Net Listener,iSQL*PLUS,Ultrasearch,Oracle Enterprise Manager database control,Oracle Management Agent.

Database Instances and ASM instances:
1) Identify the Oracle SID and Oracle home directory for the instance .

In Solaris: $ cat /var/opt/oracle/oratab
On other operating systems: $cat /etc/oratab

The oratab file contains lines similar to the following, which identify the SID and corresponding Oracle home directory for each database or Automatic Storage Management instance on the system:

sid:oracle_home_directory:[YN]

We use the plus sign (+) as the first character in the SID of Automatic Storage Management instances.

2) Depending on default shell, run the oraenv or coraenv script to set the environment variables for the instance

Bourne, Bash, or Korn shell:$ . /usr/local/bin/oraenv
C shell:% source /usr/local/bin/coraenv

3) When prompted, specify the SID for the instance.

4) Run the following commands to shut down the instance:

$ sqlplus /nolog
SQL> CONNECT SYS/sys_password as SYSDBA
SQL> SHUTDOWN NORMAL

5) Quit the SQL*PLUS prompt

Run the following commands to start the instance:

$ sqlplus /nolog
SQL> CONNECT SYS/sys_password as SYSDBA
SQL> STARTUP

CSS Daemon :

To stop the Oracle Cluster Services Synchronization (CSS) daemon, run the following command:

On AIX and Mac OS X:/etc/init.cssd stop
On other platforms:/etc/init.d/init.cssd stop

To stop and restart the CSS daemon :

$ORACLE_HOME/bin/localconfig reset

Oracle Net listener :

Run the following command to determine the listener name and Oracle home directory for the Oracle Net listener that we want to stop:

On Mac OS X:$ ps -auxwww grep tnslsnr
On other platforms:$ ps -ef grep tnslsnr

This command displays a list of the Oracle Net listeners running on the system. The output of this command is similar to the following:

94248 ?? I 0:00.18 oracle_home1/bin/tnslsnr listenername1 -inherit
94248 ?? I 0:00.18 oracle_home2/bin/tnslsnr listenername2 -inherit

In this sample output, listenername1 and listenername2 are the names of the listeners.

If required, set the ORACLE_HOME environment variable to specify the appropriate Oracle home directory for the listener that we want to stop:

Bourne, Bash, or Korn shell:$ ORACLE_HOME=oracle_home1
$ export ORACLE_HOME
C shell:% setenv ORACLE_HOME oracle_home1

Run the following command to stop the Oracle Net listener:

$ $ORACLE_HOME/bin/lsnrctl stop listenername

If the name of the listener is the default name, LISTENER, then you do not have to specify the name in this command.

To start an Oracle Net listener:

If required, set the ORACLE_HOME environment variable to specify the appropriate Oracle home directory for the listener that we want to start:

Bourne, Bash, or Korn shell:$ ORACLE_HOME=oracle_home1
$ export ORACLE_HOME
C shell:% setenv ORACLE_HOME oracle_home1

Run the following command to restart the Oracle Net listener:

$ $ORACLE_HOME/bin/lsnrctl start [listenername]

We must specify the listener name only if it is different from the default listener name, LISTENER. The listener name is mentioned in the listener.ora file. To display the contents of this file, run the following command:

$ more $ORACLE_HOME/network/admin/listener.ora

iSQL*Plus :
To stop iSQL*Plus :

1) set the ORACLE_HOME environment variable to specify the appropriate Oracle home directory for iSQL*Plus:
Bourne, Bash, or Korn shell:$ ORACLE_HOME=oracle_home
$ export ORACLE_HOME
C shell:% setenv ORACLE_HOME oracle_home

2) Run the following command to stop iSQL*Plus:
$ $ORACLE_HOME/bin/isqlplusctl stop

Starting iSQL*Plus :
1) Set the ORACLE_HOME environment variable to specify the appropriate Oracle home directory for the iSQL*Plus instance that we want to start:

Bourne, Bash, or Korn shell:$ ORACLE_HOME=oracle_home
$ export ORACLE_HOME
C shell:% setenv ORACLE_HOME oracle_home

2) Run the following command to start iSQL*Plus:
$ $ORACLE_HOME/bin/isqlplusctl start

Ultra Search :
To stop Oracle Ultra Search:
1) set the ORACLE_HOME environment variable to specify the appropriate Oracle home directory for Oracle Ultra Search:
Bourne, Bash, or Korn shell:$ ORACLE_HOME=oracle_home
$ export ORACLE_HOME
C shell:% setenv ORACLE_HOME oracle_home

2) Run the following command to stop Oracle Ultra Search:
$ORACLE_HOME/bin/searchctl stop

To start Oracle Ultra Search:
1) set the ORACLE_HOME environment variable to specify the appropriate Oracle home directory for Oracle Ultra Search:

Bourne, Bash, or Korn shell:$ ORACLE_HOME=oracle_home
$ export ORACLE_HOME
C shell:% setenv ORACLE_HOME oracle_home

2) Run the following command to start Oracle Ultra Search:
$ORACLE_HOME/bin/searchctl start

Enterprise Manager Database Control :
To stop Oracle Enterprise Manager Database Control:
1) Depending on our default shell, run the oraenv or coraenv script to set the environment for the database managed by the Database Control that we want to stop:
coraenv script:% source /usr/local/bin/coraenv
oraenv script:$ . /usr/local/bin/oraenv

2) Run the following command to stop the Database Control:
$ORACLE_HOME/bin/emctl stop dbconsole

To start Database Control:
1) Set the ORACLE_SID and ORACLE_HOME environment variables to identify the SID and Oracle home directory for the database control that you want to start:
Bourne, Bash, or Korn shell:$ ORACLE_HOME=oracle_home
$ ORACLE_SID=sid
$ export ORACLE_HOME ORACLE_SID
C shell:% setenv ORACLE_HOME oracle_home
% setenv ORACLE_SID sid

2) Run the following command to start the Database Control:
$ORACLE_HOME/bin/emctl start dbconsole

Oracle Management Agent :

If we are using Oracle Enterprise Manager Grid Control to manage multiple Oracle products from a central location, then wemust have an Oracle Management Agent installed on each host system.

Typically, the Oracle Management Agent is installed in its own Oracle home directory.

To stop Oracle Management Agent:

1) Run the following command to determine the Oracle home directory for Oracle Management Agent:
On Mac OS X:$ ps -auxwww grep emagent
On other platforms:$ ps -ef grep emagent
This command displays information about the Oracle Management Agent processes. The output of this command is similar to the following:
94248 ?? I 0:00.18 oracle_home/agent/bin/emagent ...

2) If required, set the ORACLE_HOME environment variable to specify the appropriate Oracle home directory for the Oracle Management Agent:
Bourne, Bash, or Korn shell:
$ ORACLE_HOME=oracle_home
$ export ORACLE_HOME
C shell:% setenv ORACLE_HOME oracle_home

3) Run the following command to stop Oracle Management Agent:
$ORACLE_HOME/agent/bin/emctl stop agent

To start Oracle Management Agent:

1) set the ORACLE_HOME environment variable to specify the appropriate Oracle home directory for Oracle Management Agent:
Bourne, Bash, or Korn shell:$ ORACLE_HOME=oracle_home
$ export ORACLE_HOME
C shell:% setenv ORACLE_HOME oracle_home

2) Run the following command to start Oracle Management Agent :
$ORACLE_HOME/agent/bin/emctl start agent

JOINS in details

Example of Antijoin, Semijoin, Curtesian Product,Self join
Antijoin Example:
------------ --------- ---
If I want to wish to select a list of students who are not in a particular set departments that I can use antijoin as below.

SQL>SELECT * FROM student
WHERE deptid NOT IN
(SELECT deptid FROM dept
WHERE deptid = 3)
ORDER BY NAME;
STDID NAME DEPTID
---------- ------------ --- ----------
24101 Raju 1

Semijoin Example:
------------ --------- -
Whenever only one row needs to be returned from the departments table, even though many rows in the employees table might match the subquery.
SQL>SELECT * FROM dept
WHERE EXISTS
(SELECT * FROM student
WHERE dept.deptid = student.deptid
)
ORDER BY deptname;
DEPTID DEPTNAME
---------- ----------
3 CSE

Crossjoin Example: Cartesian Product
------------ --------- --------- ---
SQL> insert into student values(22440, 'Adu',2);
1 row created.

SQL> select name,deptname from dept CROSS JOIN student;
NAME DEPTNAME
------------ --- ----------
Rafi EEE
Raju EEE
Arju EEE
Adu EEE
Rafi CSE
Raju CSE
Arju CSE
Adu CSE

8 rows selected.

Self Join Example:
------------ ---------
A self join to return the name of each employee along with the name of the employee's manager.
SELECT e1.last_name| |' works for '||e2.last_name
"Employees and Their Managers"
FROM employees e1, employees e2
WHERE e1.manager_id = e2.employee_ id
Joins in Oracle.
In this section I have given definition of several join terms. In another section inshallah I will demonstrate example of joins in oracle.
What is Join?
---------------------
•In a straightforward a join is a query that combines rows from two or more tables, views, or materialized views.

•Oracle Database performs a join whenever multiple tables appear in the FROM clause of the query.

•In join if any two of these tables have a column name in common, then you must qualify all references to these columns throughout the query with table names to avoid ambiguity.

Join Conditions
-------------------------
•Most join queries contain at least one join condition, either in the FROM clause or in the WHERE clause.

•The join condition compares two columns, each from a different table.

•To execute a join, Oracle Database combines pairs of rows, each containing one row from each table, for which the join condition evaluates to TRUE.

•To execute a join of three or more tables, Oracle first joins two of the tables based on the join conditions comparing their columns and then joins the result to another table based on join conditions containing columns of the joined tables and the new table. Oracle continues this process until all tables are joined into the result.

•The optimizer determines the order in which Oracle joins tables based on the join conditions, indexes on the tables, and, any available statistics for the tables.

•WHERE clause that contains a join condition can also contain other conditions that refer to columns of only one table. These conditions can further restrict the rows returned by the join query.

Types of Joins in Oracle:
-----------------------------------
A)Equijoins
------------------------
An equijoin is a join with a join condition containing an equality operator. An equijoin combines rows that have equivalent values for the specified columns.

B)Self Joins
-----------------
A self join is a join of a table to itself. This table appears twice in the FROM clause and is followed by table aliases that qualify column names in the join condition. To perform a self join, Oracle Database combines and returns rows of the table that satisfy the join condition.

C)Cartesian Products
--------------------------
If two tables in a join query have no join condition, then Oracle Database returns their Cartesian product. Oracle combines each row of one table with each row of the other. A Cartesian product always generates many rows and is rarely useful.

D)Inner Joins
-------------------
An inner join is a join of two or more tables that returns only those rows that satisfy the join condition. It is also called simple join.

E)Outer Joins
------------------------
An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition.

Outer join can be categorizes into three.
a)Left Outer Join: To write a query that performs an outer join of tables A and B and returns all rows from A (a left outer join), use the LEFT [OUTER] JOIN syntax in the FROM clause, or apply the outer join operator (+) to all columns of B in the join condition in the WHERE clause. For all rows in A that have no matching rows in B, Oracle Database returns null for any select list expressions containing columns of B.

b)Right Outer Join:To write a query that performs an outer join of tables A and B and returns all rows from B (a right outer join), use the RIGHT [OUTER] JOIN syntax in the FROM clause, or apply the outer join operator (+) to all columns of A in the join condition in the WHERE clause. For all rows in B that have no matching rows in A, Oracle returns null for any select list expressions containing columns of A.

c)Full Outer Join:To write a query that performs an outer join and returns all rows from A and B, extended with nulls if they do not satisfy the join condition (a full outer join), use the FULL [OUTER] JOIN syntax in the FROM clause.

E)Antijoins
---------------
An antijoin returns rows from the left side of the predicate for which there are no corresponding rows on the right side of the predicate. That is, it returns rows that fail to match (NOT IN) the subquery on the right side.

F)Semijoins
---------------------
A semijoin returns rows that match an EXISTS subquery without duplicating rows from the left side of the predicate when multiple rows on the right side satisfy the criteria of the subquery.

Semijoin and antijoin transformation cannot be done if the subquery is on an OR branch of the WHERE clause.
Examples of All outer joins and Equijoins in oracle
In previous post I have define all types of joins in oracle. In this post I will show example of all outer joins one by one. Also I have shown an example of equijoins. Hopefully I next post I will give example of semijoin, antijoin, selfjoin and cartesian product.

To demonstrate example I have used two table customers and orders and insert some data into it.

/* create the customer table */

Create Table Customers (
CustNo Integer Not Null Primary Key,
CustName Char (20),
Address Char (40)
);

/* create the orders table */

Create Table Orders (
OrderNo Integer Not Null Primary Key,
CustNo Integer,
OrderDate Date
);

/* put some data into the customer table */

Insert into Customers Values (1,'P. Jones','Leeds');
Insert into Customers Values (2,'A. Chan','Hong Kong');
Insert into Customers Values (3,'K. Green','Columbia');
Insert into Customers Values (4,'B. Smith','Leeds');
Insert into Customers Values (5,'A. Khan','');

/* put some data into the orders table */

Insert into Orders Values (1,1,'24-JAN-96');
Insert into Orders Values (2,1,'31-JAN-96');
Insert into Orders Values (3,2,'04-FEB-96');
Insert into Orders Values (4,4,'12-FEB-96');
Insert into Orders Values (5,6,'12-FEB-96');

Now have a look at both tables' data.
SQL> select * from customers;

CUSTNO CUSTNAME ADDRESS
---------- -------------------- ----------------------------------------
1 P. Jones Leeds
2 A. Chan Hong Kong
3 K. Green Columbia
4 B. Smith Leeds

SQL> select * from orders;

ORDERNO CUSTNO ORDERDATE
---------- ---------- ---------
1 1 24-JAN-96
2 1 31-JAN-96
3 2 04-FEB-96
4 4 12-FEB-96
5 6 12-FEB-96

Example of Left Outer Join:
-------------------------------
SQL> select CustName, OrderDate, Address
from Customers c, Orders o
where c.CustNo = o.CustNo(+) and c.address='Leeds';

or recommended later 9i syntax,
SQL> select CustName, OrderDate, Address
from Customers c left outer join Orders o
on c.CustNo = o.CustNo
where c.Address = 'Leeds'; 2 3 4
CUSTNAME ORDERDATE ADDRESS
-------------------- --------- ----------------------------------------
P. Jones 24-JAN-96 Leeds
P. Jones 31-JAN-96 Leeds
B. Smith 12-FEB-96 Leeds

Right Outer Join Example:
----------------------------
SQL> select CustName, OrderDate, Address
from Customers c, Orders o
where c.CustNo(+) = o.CustNo and o.OrderDate > '31-JAN-96';

CUSTNAME ORDERDATE ADDRESS
-------------------- --------- ----------------------------------------
A. Chan 04-FEB-96 Hong Kong
B. Smith 12-FEB-96 Leeds
12-FEB-96
or recommended later 9i syntax
SQL> select CustName, OrderDate, Address
from Customers c Right outer join Orders o
on c.CustNo = o.CustNo
where o.OrderDate > '31-JAN-96';
CUSTNAME ORDERDATE ADDRESS
-------------------- --------- ----------------------------------------
A. Chan 04-FEB-96 Hong Kong
B. Smith 12-FEB-96 Leeds
12-FEB-96

Full outer join Example:
----------------------------
SQL> select CustName, OrderDate, Address
from Customers c Full outer join Orders o
on c.CustNo = o.CustNo
where o.OrderDate > '31-JAN-96';

CUSTNAME ORDERDATE ADDRESS
-------------------- --------- ----------------------------------------
A. Chan 04-FEB-96 Hong Kong
B. Smith 12-FEB-96 Leeds
12-FEB-96

Without condition here is the example of full, left and right outer join.
SQL> select CustName, OrderDate, Address
from Customers c Full outer join Orders o
on c.CustNo = o.CustNo;
CUSTNAME ORDERDATE ADDRESS
-------------------- --------- ----------------------------------------
P. Jones 24-JAN-96 Leeds
P. Jones 31-JAN-96 Leeds
A. Chan 04-FEB-96 Hong Kong
B. Smith 12-FEB-96 Leeds
K. Green Columbia
12-FEB-96

6 rows selected.

SQL> select CustName, OrderDate, Address
from Customers c right outer join Orders o
on c.CustNo = o.CustNo;
CUSTNAME ORDERDATE ADDRESS
-------------------- --------- ----------------------------------------
P. Jones 31-JAN-96 Leeds
P. Jones 24-JAN-96 Leeds
A. Chan 04-FEB-96 Hong Kong
B. Smith 12-FEB-96 Leeds
12-FEB-96

SQL> select CustName, OrderDate, Address
from Customers c left outer join Orders o
on c.CustNo = o.CustNo;
CUSTNAME ORDERDATE ADDRESS
-------------------- --------- ----------------------------------------
P. Jones 24-JAN-96 Leeds
P. Jones 31-JAN-96 Leeds
A. Chan 04-FEB-96 Hong Kong
B. Smith 12-FEB-96 Leeds
K. Green Columbia

Example of Equijoins
--------------------------
SQL> select address, orderdate from customers c, orders o where c.custno=o.custno order by orderdate;

ADDRESS ORDERDATE
---------------------------------------- ---------
Leeds 24-JAN-96
Leeds 31-JAN-96
Hong Kong 04-FEB-96
Leeds 12-FEB-96

Difference between inner join and outer join
An inner join will return a row only if there is a joined row with data in both tables- being joined. An outer join will return a row even if the other table doesn't have a corresponding row.

With an example I will make the scenarios clear. I will create two tables named student and dept. In the student table there will be one department but the corresponding department is not exist in dept table. Now I perform both inner join and outer join on deptid column.

SQL> create table student(stdid number, name varchar2(15),deptid number);
Table created.

SQL> insert into student values (024413,'Rafi',3);
SQL> insert into student values (024101,'Raju',1);
SQL> insert into student values (024434,'Arju',3);

SQL> create table dept(deptid number,deptname varchar2(10));
Table created.

SQL> insert into dept values(2,'EEE');
SQL> insert into dept values(3,'CSE');

SQL> select s.name,d.deptname from dept d INNER JOIN student s on d.deptid=s.deptid;
NAME DEPTNAME
--------------- ----------
Rafi CSE
Arju CSE

See in case of INNER join rows return only if joined row exist with data in both tables.

SQL> select s.name,d.deptname from dept d RIGHT OUTER JOIN student s on d.deptid=s.deptid;
NAME DEPTNAME
--------------- ----------
Arju CSE
Rafi CSE
Raju

SQL> select s.name,d.deptname from dept d LEFT OUTER JOIN student s on d.deptid=s.deptid;
NAME DEPTNAME
--------------- ----------
Rafi CSE
Arju CSE
EEE

SQL> select s.name,d.deptname from dept d FULL OUTER JOIN student s on d.deptid=s.deptid;
NAME DEPTNAME
--------------- ----------
Rafi CSE
Arju CSE
EEE
Raju

As we see in case of OUTER join it return a row even if the other table doesn't have a corresponding row.
Difference between join, Inner join,Equijoin and Natural Join
An inner join is a join with a join condition that may contain both equality or non-equality sign whereas an equijoin is a join with a join condition that only contain only equality sign.
So we can say an equijoin is a type of inner join containing (Equal)= operator in the join condition.

It is good to know the difference between join and INNER JOIN keywoed. Actually there is no difference. If we write JOIN then by default INNER JOIN is performed. In the example it is also shown.
The following example will make you more clear.

In this example I used data as in example of Difference between Inner join and Outer join
SQL> select s.name,d.deptname from dept d, student s where d.deptid=s.deptid;
NAME DEPTNAME
--------------- ----------
Rafi CSE
Arju CSE

This example represents both INNER join and equijoin.
SQL> select s.name,d.deptname from dept d INNER JOIN student s on d.deptid=s.deptid;
NAME DEPTNAME
--------------- ----------
Rafi CSE
Arju CSE

Above example also represents both INNER join and equijoin.
SQL> select s.name,d.deptname from dept d INNER JOIN student s on d.deptid<>s.deptid;

NAME DEPTNAME
--------------- ----------
Rafi EEE
Raju EEE
Arju EEE
Raju CSE

Above example represents an inner join but not a equijoin.
SQL> select s.name,d.deptname from dept d JOIN student s on d.deptid<>s.deptid;

NAME DEPTNAME
--------------- ----------
Rafi EEE
Raju EEE
Arju EEE
Raju CSE

Above example show JOIN and INNER join keyword is same. If we don't specify INNER then by default inner join is performed.

Now let's have a look at natural join.
SQL> desc dept;
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPTID NUMBER
DEPTNAME VARCHAR2(10)

SQL> desc student;
Name Null? Type
----------------------------------------- -------- ----------------------------
STDID NUMBER
NAME VARCHAR2(15)
DEPTID NUMBER

After describing both table we see both table have a same column name deptid. Now if I perform natural join then tables with same column name is joined.
SQL> select s.name,d.deptname from dept d NATURAL JOIN student s ;
NAME DEPTNAME
--------------- ----------
Rafi CSE
Arju CSE

As both table have same column deptid so deptid is joined.

Now I rename deptid column and see the result. We will notice in that case inner join will be performed.

SQL> alter table student rename column deptid to deptid1;
Table altered.

SQL> select s.name,d.deptname from dept d NATURAL JOIN student s ;

NAME DEPTNAME
--------------- ----------
Rafi EEE
Raju EEE
Arju EEE
Rafi CSE
Raju CSE
Arju CSE

6 rows selected.

As both table don't have same column name so normal join/inner join is performed.
SQL> select s.name,d.deptname from dept d, student s ;

NAME DEPTNAME
--------------- ----------
Rafi EEE
Raju EEE
Arju EEE
Rafi CSE
Raju CSE
Arju CSE

6 rows selected.

SQL> select s.name,d.deptname from dept d CROSS JOIN student s ;

NAME DEPTNAME
--------------- ----------
Rafi EEE
Raju EEE
Arju EEE
Rafi CSE
Raju CSE
Arju CSE

6 rows selected.

Efficient Tips for Finding Unused Oracle Segments!!

Efficient Tips for Finding Unused Oracle Segments!!
Oracle provides a number of options for finding unused or read only Oracle segments. AWR, Auditing, Statspack reports or Oracle views can be used for fining unused oracle segments. However you must be careful about the retention of data you use for the purpose of analysis. Whatever option you are using but it must be ensured that the last instance startup time is enough behind for the analysis need.
If you analyze say 20 days data for finding unused/read-only Oracle segments where you have set the retention to default 7 days then you will result in wrong analysis. Therefore it is very important to control the retention of your Statspack or AWR reports. Usually the default 7 days (1440 min) retention seems to be to short in many cases and therefore it is advised to set the retention to 30 days (43200 min).
Retention:
You can check current AWR retention by running below command.
SELECT RETENTION FROM DBA_HIST_WR_CONTROL;
Similarly you can run below command to set new retention value.
EXECUTE DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(INTERVAL => 60, RETENTION => 43200);
Oracle 10g Automatic Workload Repository:
Oracle DBA_HIST_SQL_PLAN displays SQL execution plans whereas DBA_HIST_SQLSTAT displays historical information about SQL statistics.
SELECT .. FROM DBA_HIST_SQL_PLAN P, DBA_HIST_SQLSTAT S WHERE P.SQL_ID = S.SQL_ID;
Oracle DBA_HIST_SEG_STAT displays historical information about segment-level statistics. Whereas the DBA_HIST_SEG_STAT_OBJ displays all the names of the segments captured in the workload repository. Oracle DBA_HIST_SEG_STAT_OBJ view is used with the DBA_HIST_SEG_STAT view.
SELECT .. FROM DBA_HIST_SEG_STAT SS, DBA_HIST_SEG_STAT_OBJ SO
WHERE SS.OBJ# = SO.OBJ# AND SS.DATAOBJ# = SO.DATAOBJ# AND SS.DBID = SO.DBID;
Standard Oracle Auditing:
AUDIT_TRAIL=DB
AUDIT SELECT ON BY ACCESS;
SELECT .. FROM SYS.AUD$ WHERE .. ;
Fine-grained Oracle Auditing:
Oracle fine-grained auditing helps us solve the problem of capturing read access. This type of auditing is managed by DBMS_FGA package. A PL /SQL procedure is executed whenever a match is made with the predicate.
BEGIN
DBMS_FGA.ADD_POLICY (OBJECT_SCHEMA=>'..', OBJECT_NAME=>'..', POLICY_NAME=>'..');
COMMIT;
END;
/
SELECT .. FROM DBA_FGA_AUDIT_TRAIL SA, DBA_OBJECTS SO
WHERE SA.OBJECT_NAME = SO.OBJECT_NAME AND SA.OBJECT_SCHEMA = SO.OWNER;
DBA_TAB_MODIFICATIONS:
Oracle DBA_TAB_MODIFICATIONS describes modifications to all tables in the database that have been modified since the last time statistics were gathered on the tables. Since the information in DBA_TAB_MODIFICATIONS is reset after statistics gathering therefore you can use it to capture the modification amounts for your segments from the last statistics gathering time.
SELECT .. FROM DBA_TAB_MODIFICATIONS WHERE .. ;
Oracle Statspack:
SELECT ..
COUNT(DECODE(SS.COMMAND_TYPE, 2, 1, 6, 1, 7, 1, 189, 1)) SQL_CHANGE,
COUNT(DECODE(SS.COMMAND_TYPE, 3, 1)) SQL_READ
FROM PERFSTAT.STATS$SQLTEXT SS WHERE SS.SQL_TEXT LIKE ‘% ‘||T.TABLE_NAME||' %';
You can also use STATS$SEG_STAT and STATS$SEG_STAT_OBJ in Statspack PERFSTAT .
SELECT .. FROM PERFSTAT.STATS$SEG_STAT SS, PERFSTAT.STATS$SEG_STAT_OBJ SO
WHERE SS.OBJ# = SO.OBJ# AND SS.DATAOBJ# = SO.DATAOBJ# AND SS.DBID = SO.DBID;
Oracle V$ACCESS view:
Oracle V$ACCESS view can be used to display objects in the database that are currently locked and the sessions that are accessing them.
SELECT .. FROM V$ACCESS WHERE .. ;