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.
Monday, June 23, 2008
Thursday, June 19, 2008
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.
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
•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.
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.
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.
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.
Friday, June 13, 2008
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
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
Thursday, June 12, 2008
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.
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
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
Wednesday, June 11, 2008
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
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
Monday, June 9, 2008
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.
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 .. ;
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 .. ;
Materialized view for Data Warehouse
Materialized view for Data Warehouse
Dear friends,
Did you hear about Materialized view?
Ans: Yes, No or May be.
Now I will explain you in details
What is materialized view?
1. Materialized view is normal database object like “table,index”
2. It is basically use for Data warehouse or Replication purpose.
3. Snapshot is synonym for materialized view.
4. A materialized view can be stored in the same database as its base tables or in a different database
5. A materialized view provides access to table data by storing the results of a query in a separate schema object. Unlike an ordinary view, which does not take up any storage space or contain any data, a materialized view contains the rows resulting from a query against one or more base tables or views.
6. A materialized view improve response time through query rewrite or reduce execution time.
Note: Query rewrite is a mechanism where Oracle or applications from the end user or database transparently improve query response time, by automatically rewriting the SQL query to use the materialized view instead of accessing the original tables. Query rewrites are particularly useful in a data warehouse environment.
What is syntax for materialized view?
Create materialized view “materialized_view_name”
Build [immediate / deferred]
Refresh [fash [on commit / demand]]
Enable query rewrite
As
Sql statement
/
Now Details explanation about syntax
1. Create materialized view “name’
I hope it is understood because self explanatory
2. Build [immediate / deferred]
1. Build immediate -- > materialized view populate with data on creation time from tables.
2. Build deferred --- > materialized view not populate with data on creation time, we have to populate later.
Eg:
With build immediate
create materialized view mymview1
build immediate
as
select ename,sum(sal) from emp group by ename;
SQL> select count(*) from emp;
COUNT(*)
----------
14
Note: materialized view populate with data on creation time.
With build deferred
create materialized view mymview2
build deferred
as
select ename,sum(sal) from emp group by ename;
SQL> select count(*) from mymview2;
COUNT(*)
----------
0
Note: materialized view not populate with data on creation time, we have to populate manually through DBMS_MVIEW.RERESH package.
3. Refresh [fast [on commit / demand]]
Refresh is method for populate materialized view with data whenever made changes in their base tables.
There are five refresh method for materialized view
1. refresh fast (materialized view log needed)
2. refresh on commit
3. refresh on demand
What is materialized view log?
A materialized view log is a schema object that records changes to a master table's data so that a materialized view defined on the master table can be refreshed incrementally.
Each materialized view log is associated with a single master table. The materialized view log resides in the same database and schema as its master table.
Eg:
With refresh on commit
SQL> select count(*) from emp;
COUNT(*)
----------
14
SQL> create materialized view MYVIEW1
2 build immediate
3 refresh on commit
4 enable query rewrite
5 as
6 select ename, sum(sal) from emp group by ename;
Materialized view created.
SQL> select count(*) from myview1;
COUNT(*)
----------
14
SQL> insert into emp (empno,ename,sal) values(1754,'TAJ',2500);
1 row created.
SQL> commit;
Commit complete.
SQL> select count(*) from myview1;
COUNT(*)
----------
15
With refresh on commit
SQL> select count(*) from emp;
COUNT(*)
----------
15
SQL> create materialized view MYVIEW5
2 build immediate
3 refresh on demand
4 enable query rewrite
5 as
6 select ename,sum(sal) from emp group by ename;
Materialized view created.
SQL> select count(*) from myview5;
COUNT(*)
----------
15
SQL> insert into emp (empno,ename,sal)values(1100,'xyz',1000);
1 row created.
SQL> commit;
Commit complete.
SQL> select count(*) from emp;
COUNT(*)
----------
16
SQL> select count(*) from myview5;
COUNT(*)
----------
15
SQL> exec dbms_mview.refresh('MYVIEW5');
PL/SQL procedure successfully completed.
SQL> select count(*) from myview5;
COUNT(*)
----------
16
Whenever any changes made in base tables and perform commit then materialized view refreshed.
4. Enable query rewrite
A materialized view is like a query with a result that is materialized and stored in a table. When a user query is found compatible with the query associated with a materialized view, the user query can be rewritten in terms of the materialized view. This technique improves the execution of the user query, because most of the query result has been precomputed. The query transformer looks for any materialized views that are compatible with the user query and selects one or more materialized views to rewrite the user query. The use of materialized views to rewrite a query is cost-based. That is, the query is not rewritten if the plan generated without the materialized views has a lower cost than the plan generated with the materialized views.
Note: query_rewrite_enabled=true AND query_rewrite_integrity=enforced must be set to use query rewrite feature
5. How Materialized view improve query performance.
Let’s check with demo
1. Create Big Table :)
SQL> create table big
2 as select * from all_objects
3 union all
4 select * from all_objects
5 union all
6 select * from all_objects
7 union all
8 select * from all_objects
9 union all
10 select * from all_objects;
Table created.
SQL> select count(*) from big;
COUNT(*)
----------
188995
2. Now execute below query and check query statistics
SQL> alter system flush shared_pool;
System altered.
SQL> set autotrace traceonly
SQL> set timing on
SQL> select owner,count(*) from big group by owner;
12 rows selected.
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=624 Card=188024 By
tes=3196408)
1 0 SORT (GROUP BY) (Cost=624 Card=188024 Bytes=3196408)
2 1 TABLE ACCESS (FULL) OF 'BIG' (TABLE) (Cost=598 Card=1880
24 Bytes=3196408)
Statistics
----------------------------------------------------------
957 recursive calls
0 db block gets
2844 consistent gets---- > too big :)
0 physical reads
0 redo size
1028 bytes sent via SQL*Net to client
504 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
10 sorts (memory)
0 sorts (disk)
12 rows processed
3. Now Create materialized view and try
SQL> create materialized view MYVIEWBIG
2 build immediate
3 refresh on commit
4 enable query rewrite
5 as
6 select owner,count(*) from big group by owner;
Materialized view created.
SQL> alter system flush shared_pool;
System altered.
SQL> set autotrace traceonly
SQL> set timing on
SQL> select owner,count(*) from big group by owner;
12 rows selected.
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=12 Bytes=36
0)
1 0 MAT_VIEW REWRITE ACCESS (FULL) OF 'MYVIEWBIG' (MAT_VIEW RE
WRITE) (Cost=3 Card=12 Bytes=360)
Statistics
----------------------------------------------------------
7356 recursive calls
0 db block gets
1313 consistent gets --- > just less then half :)
0 physical reads
0 redo size
1028 bytes sent via SQL*Net to client
504 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
49 sorts (memory)
0 sorts (disk)
12 rows processed
4. What Privileges Need for materialized view ?
To create a materialized view in your own schema, you must have the CREATE MATERIALIZED VIEW privilege and the SELECT privilege to any tables referenced that are in another schema. To create a materialized view in another schema, you must have the CREATE ANY MATERIALIZED VIEW privilege and the owner of the materialized view needs SELECT privileges to the tables referenced if they are from another schema. Moreover, if you enable query rewrite on a materialized view that references tables outside your schema, you must have the GLOBAL QUERY REWRITE privilege or the QUERY REWRITE object privilege on each table outside your schema.
5. How to check Status of a Materialized View?
SQL> select mview_name,query,rewrite_enabled,refresh_mode,build_mode,staleness,c
ompile_state
2 from user_mviews;
MVIEW_NAME QUERY R REFRES BUILD_MOD STALENESS COMPILE_ST
---------- -------------------- - ------ --------- ---------- ----------
MYBIG select owner, count( Y DEMAND IMMEDIATE NEEDS_COMP NEEDS_COMP
*) from big group by ILE ILE
owner
MYMVIEW2 select ename,sum(sal N DEMAND DEFERRED UNUSABLE NEEDS_COMP
) from emp group by ILE
ename
MYVIEW5 select ename,sum(sal Y DEMAND IMMEDIATE FRESH VALID
) from emp group by
ename
MYMVIEW3 select ename,sum(sal Y COMMIT IMMEDIATE UNUSABLE VALID
) from emp group by
ename
MYVIEW1 select ename, sum(sa Y COMMIT IMMEDIATE FRESH VALID
l) from emp group by
Explanation:
MVIEW_NAME -------- > Materialized View name
QUERY ------------- > Query that defines the materialized view
REFRESH_ENABLED --- > If query_rewrite_enabled=TRUE then value is “Y” otherwise “N”
REFRESH_MODE ------ > DEMAND, COMMIT, NEVER
BUILD_MODE -------- > IMMEDIATE, DEFERRED
STALENESS --------- > Relationship between the contents of the materialized view and the contents of the materialized view's masters.
COMPILE_STATE ----- > Validity of the materialized view with respect to the objects upon which it depends
VALID -> No objects depends has changed since the last validation
NEED_COMPILE -> Some objects depends materialized view has changed an “alter materialized view … compile;” statement is required to validate this materialized view
Dear friends,
Did you hear about Materialized view?
Ans: Yes, No or May be.
Now I will explain you in details
What is materialized view?
1. Materialized view is normal database object like “table,index”
2. It is basically use for Data warehouse or Replication purpose.
3. Snapshot is synonym for materialized view.
4. A materialized view can be stored in the same database as its base tables or in a different database
5. A materialized view provides access to table data by storing the results of a query in a separate schema object. Unlike an ordinary view, which does not take up any storage space or contain any data, a materialized view contains the rows resulting from a query against one or more base tables or views.
6. A materialized view improve response time through query rewrite or reduce execution time.
Note: Query rewrite is a mechanism where Oracle or applications from the end user or database transparently improve query response time, by automatically rewriting the SQL query to use the materialized view instead of accessing the original tables. Query rewrites are particularly useful in a data warehouse environment.
What is syntax for materialized view?
Create materialized view “materialized_view_name”
Build [immediate / deferred]
Refresh [fash [on commit / demand]]
Enable query rewrite
As
Sql statement
/
Now Details explanation about syntax
1. Create materialized view “name’
I hope it is understood because self explanatory
2. Build [immediate / deferred]
1. Build immediate -- > materialized view populate with data on creation time from tables.
2. Build deferred --- > materialized view not populate with data on creation time, we have to populate later.
Eg:
With build immediate
create materialized view mymview1
build immediate
as
select ename,sum(sal) from emp group by ename;
SQL> select count(*) from emp;
COUNT(*)
----------
14
Note: materialized view populate with data on creation time.
With build deferred
create materialized view mymview2
build deferred
as
select ename,sum(sal) from emp group by ename;
SQL> select count(*) from mymview2;
COUNT(*)
----------
0
Note: materialized view not populate with data on creation time, we have to populate manually through DBMS_MVIEW.RERESH package.
3. Refresh [fast [on commit / demand]]
Refresh is method for populate materialized view with data whenever made changes in their base tables.
There are five refresh method for materialized view
1. refresh fast (materialized view log needed)
2. refresh on commit
3. refresh on demand
What is materialized view log?
A materialized view log is a schema object that records changes to a master table's data so that a materialized view defined on the master table can be refreshed incrementally.
Each materialized view log is associated with a single master table. The materialized view log resides in the same database and schema as its master table.
Eg:
With refresh on commit
SQL> select count(*) from emp;
COUNT(*)
----------
14
SQL> create materialized view MYVIEW1
2 build immediate
3 refresh on commit
4 enable query rewrite
5 as
6 select ename, sum(sal) from emp group by ename;
Materialized view created.
SQL> select count(*) from myview1;
COUNT(*)
----------
14
SQL> insert into emp (empno,ename,sal) values(1754,'TAJ',2500);
1 row created.
SQL> commit;
Commit complete.
SQL> select count(*) from myview1;
COUNT(*)
----------
15
With refresh on commit
SQL> select count(*) from emp;
COUNT(*)
----------
15
SQL> create materialized view MYVIEW5
2 build immediate
3 refresh on demand
4 enable query rewrite
5 as
6 select ename,sum(sal) from emp group by ename;
Materialized view created.
SQL> select count(*) from myview5;
COUNT(*)
----------
15
SQL> insert into emp (empno,ename,sal)values(1100,'xyz',1000);
1 row created.
SQL> commit;
Commit complete.
SQL> select count(*) from emp;
COUNT(*)
----------
16
SQL> select count(*) from myview5;
COUNT(*)
----------
15
SQL> exec dbms_mview.refresh('MYVIEW5');
PL/SQL procedure successfully completed.
SQL> select count(*) from myview5;
COUNT(*)
----------
16
Whenever any changes made in base tables and perform commit then materialized view refreshed.
4. Enable query rewrite
A materialized view is like a query with a result that is materialized and stored in a table. When a user query is found compatible with the query associated with a materialized view, the user query can be rewritten in terms of the materialized view. This technique improves the execution of the user query, because most of the query result has been precomputed. The query transformer looks for any materialized views that are compatible with the user query and selects one or more materialized views to rewrite the user query. The use of materialized views to rewrite a query is cost-based. That is, the query is not rewritten if the plan generated without the materialized views has a lower cost than the plan generated with the materialized views.
Note: query_rewrite_enabled=true AND query_rewrite_integrity=enforced must be set to use query rewrite feature
5. How Materialized view improve query performance.
Let’s check with demo
1. Create Big Table :)
SQL> create table big
2 as select * from all_objects
3 union all
4 select * from all_objects
5 union all
6 select * from all_objects
7 union all
8 select * from all_objects
9 union all
10 select * from all_objects;
Table created.
SQL> select count(*) from big;
COUNT(*)
----------
188995
2. Now execute below query and check query statistics
SQL> alter system flush shared_pool;
System altered.
SQL> set autotrace traceonly
SQL> set timing on
SQL> select owner,count(*) from big group by owner;
12 rows selected.
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=624 Card=188024 By
tes=3196408)
1 0 SORT (GROUP BY) (Cost=624 Card=188024 Bytes=3196408)
2 1 TABLE ACCESS (FULL) OF 'BIG' (TABLE) (Cost=598 Card=1880
24 Bytes=3196408)
Statistics
----------------------------------------------------------
957 recursive calls
0 db block gets
2844 consistent gets---- > too big :)
0 physical reads
0 redo size
1028 bytes sent via SQL*Net to client
504 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
10 sorts (memory)
0 sorts (disk)
12 rows processed
3. Now Create materialized view and try
SQL> create materialized view MYVIEWBIG
2 build immediate
3 refresh on commit
4 enable query rewrite
5 as
6 select owner,count(*) from big group by owner;
Materialized view created.
SQL> alter system flush shared_pool;
System altered.
SQL> set autotrace traceonly
SQL> set timing on
SQL> select owner,count(*) from big group by owner;
12 rows selected.
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=12 Bytes=36
0)
1 0 MAT_VIEW REWRITE ACCESS (FULL) OF 'MYVIEWBIG' (MAT_VIEW RE
WRITE) (Cost=3 Card=12 Bytes=360)
Statistics
----------------------------------------------------------
7356 recursive calls
0 db block gets
1313 consistent gets --- > just less then half :)
0 physical reads
0 redo size
1028 bytes sent via SQL*Net to client
504 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
49 sorts (memory)
0 sorts (disk)
12 rows processed
4. What Privileges Need for materialized view ?
To create a materialized view in your own schema, you must have the CREATE MATERIALIZED VIEW privilege and the SELECT privilege to any tables referenced that are in another schema. To create a materialized view in another schema, you must have the CREATE ANY MATERIALIZED VIEW privilege and the owner of the materialized view needs SELECT privileges to the tables referenced if they are from another schema. Moreover, if you enable query rewrite on a materialized view that references tables outside your schema, you must have the GLOBAL QUERY REWRITE privilege or the QUERY REWRITE object privilege on each table outside your schema.
5. How to check Status of a Materialized View?
SQL> select mview_name,query,rewrite_enabled,refresh_mode,build_mode,staleness,c
ompile_state
2 from user_mviews;
MVIEW_NAME QUERY R REFRES BUILD_MOD STALENESS COMPILE_ST
---------- -------------------- - ------ --------- ---------- ----------
MYBIG select owner, count( Y DEMAND IMMEDIATE NEEDS_COMP NEEDS_COMP
*) from big group by ILE ILE
owner
MYMVIEW2 select ename,sum(sal N DEMAND DEFERRED UNUSABLE NEEDS_COMP
) from emp group by ILE
ename
MYVIEW5 select ename,sum(sal Y DEMAND IMMEDIATE FRESH VALID
) from emp group by
ename
MYMVIEW3 select ename,sum(sal Y COMMIT IMMEDIATE UNUSABLE VALID
) from emp group by
ename
MYVIEW1 select ename, sum(sa Y COMMIT IMMEDIATE FRESH VALID
l) from emp group by
Explanation:
MVIEW_NAME -------- > Materialized View name
QUERY ------------- > Query that defines the materialized view
REFRESH_ENABLED --- > If query_rewrite_enabled=TRUE then value is “Y” otherwise “N”
REFRESH_MODE ------ > DEMAND, COMMIT, NEVER
BUILD_MODE -------- > IMMEDIATE, DEFERRED
STALENESS --------- > Relationship between the contents of the materialized view and the contents of the materialized view's masters.
COMPILE_STATE ----- > Validity of the materialized view with respect to the objects upon which it depends
VALID -> No objects depends has changed since the last validation
NEED_COMPILE -> Some objects depends materialized view has changed an “alter materialized view … compile;” statement is required to validate this materialized view
Materialized view for Data Warehouse
Materialized view for Data Warehouse
Dear friends,
Did you hear about Materialized view?
Ans: Yes, No or May be.
Now I will explain you in details
What is materialized view?
1. Materialized view is normal database object like “table,index”
2. It is basically use for Data warehouse or Replication purpose.
3. Snapshot is synonym for materialized view.
4. A materialized view can be stored in the same database as its base tables or in a different database
5. A materialized view provides access to table data by storing the results of a query in a separate schema object. Unlike an ordinary view, which does not take up any storage space or contain any data, a materialized view contains the rows resulting from a query against one or more base tables or views.
6. A materialized view improve response time through query rewrite or reduce execution time.
Note: Query rewrite is a mechanism where Oracle or applications from the end user or database transparently improve query response time, by automatically rewriting the SQL query to use the materialized view instead of accessing the original tables. Query rewrites are particularly useful in a data warehouse environment.
What is syntax for materialized view?
Create materialized view “materialized_view_name”
Build [immediate / deferred]
Refresh [fash [on commit / demand]]
Enable query rewrite
As
Sql statement
/
Now Details explanation about syntax
1. Create materialized view “name’
I hope it is understood because self explanatory
2. Build [immediate / deferred]
1. Build immediate -- > materialized view populate with data on creation time from tables.
2. Build deferred --- > materialized view not populate with data on creation time, we have to populate later.
Eg:
With build immediate
create materialized view mymview1
build immediate
as
select ename,sum(sal) from emp group by ename;
SQL> select count(*) from emp;
COUNT(*)
----------
14
Note: materialized view populate with data on creation time.
With build deferred
create materialized view mymview2
build deferred
as
select ename,sum(sal) from emp group by ename;
SQL> select count(*) from mymview2;
COUNT(*)
----------
0
Note: materialized view not populate with data on creation time, we have to populate manually through DBMS_MVIEW.RERESH package.
3. Refresh [fast [on commit / demand]]
Refresh is method for populate materialized view with data whenever made changes in their base tables.
There are five refresh method for materialized view
1. refresh fast (materialized view log needed)
2. refresh on commit
3. refresh on demand
What is materialized view log?
A materialized view log is a schema object that records changes to a master table's data so that a materialized view defined on the master table can be refreshed incrementally.
Each materialized view log is associated with a single master table. The materialized view log resides in the same database and schema as its master table.
Eg:
With refresh on commit
SQL> select count(*) from emp;
COUNT(*)
----------
14
SQL> create materialized view MYVIEW1
2 build immediate
3 refresh on commit
4 enable query rewrite
5 as
6 select ename, sum(sal) from emp group by ename;
Materialized view created.
SQL> select count(*) from myview1;
COUNT(*)
----------
14
SQL> insert into emp (empno,ename,sal) values(1754,'TAJ',2500);
1 row created.
SQL> commit;
Commit complete.
SQL> select count(*) from myview1;
COUNT(*)
----------
15
With refresh on commit
SQL> select count(*) from emp;
COUNT(*)
----------
15
SQL> create materialized view MYVIEW5
2 build immediate
3 refresh on demand
4 enable query rewrite
5 as
6 select ename,sum(sal) from emp group by ename;
Materialized view created.
SQL> select count(*) from myview5;
COUNT(*)
----------
15
SQL> insert into emp (empno,ename,sal)values(1100,'xyz',1000);
1 row created.
SQL> commit;
Commit complete.
SQL> select count(*) from emp;
COUNT(*)
----------
16
SQL> select count(*) from myview5;
COUNT(*)
----------
15
SQL> exec dbms_mview.refresh('MYVIEW5');
PL/SQL procedure successfully completed.
SQL> select count(*) from myview5;
COUNT(*)
----------
16
Whenever any changes made in base tables and perform commit then materialized view refreshed.
4. Enable query rewrite
A materialized view is like a query with a result that is materialized and stored in a table. When a user query is found compatible with the query associated with a materialized view, the user query can be rewritten in terms of the materialized view. This technique improves the execution of the user query, because most of the query result has been precomputed. The query transformer looks for any materialized views that are compatible with the user query and selects one or more materialized views to rewrite the user query. The use of materialized views to rewrite a query is cost-based. That is, the query is not rewritten if the plan generated without the materialized views has a lower cost than the plan generated with the materialized views.
Note: query_rewrite_enabled=true AND query_rewrite_integrity=enforced must be set to use query rewrite feature
5. How Materialized view improve query performance.
Let’s check with demo
1. Create Big Table :)
SQL> create table big
2 as select * from all_objects
3 union all
4 select * from all_objects
5 union all
6 select * from all_objects
7 union all
8 select * from all_objects
9 union all
10 select * from all_objects;
Table created.
SQL> select count(*) from big;
COUNT(*)
----------
188995
2. Now execute below query and check query statistics
SQL> alter system flush shared_pool;
System altered.
SQL> set autotrace traceonly
SQL> set timing on
SQL> select owner,count(*) from big group by owner;
12 rows selected.
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=624 Card=188024 By
tes=3196408)
1 0 SORT (GROUP BY) (Cost=624 Card=188024 Bytes=3196408)
2 1 TABLE ACCESS (FULL) OF 'BIG' (TABLE) (Cost=598 Card=1880
24 Bytes=3196408)
Statistics
----------------------------------------------------------
957 recursive calls
0 db block gets
2844 consistent gets---- > too big :)
0 physical reads
0 redo size
1028 bytes sent via SQL*Net to client
504 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
10 sorts (memory)
0 sorts (disk)
12 rows processed
3. Now Create materialized view and try
SQL> create materialized view MYVIEWBIG
2 build immediate
3 refresh on commit
4 enable query rewrite
5 as
6 select owner,count(*) from big group by owner;
Materialized view created.
SQL> alter system flush shared_pool;
System altered.
SQL> set autotrace traceonly
SQL> set timing on
SQL> select owner,count(*) from big group by owner;
12 rows selected.
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=12 Bytes=36
0)
1 0 MAT_VIEW REWRITE ACCESS (FULL) OF 'MYVIEWBIG' (MAT_VIEW RE
WRITE) (Cost=3 Card=12 Bytes=360)
Statistics
----------------------------------------------------------
7356 recursive calls
0 db block gets
1313 consistent gets --- > just less then half :)
0 physical reads
0 redo size
1028 bytes sent via SQL*Net to client
504 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
49 sorts (memory)
0 sorts (disk)
12 rows processed
4. What Privileges Need for materialized view ?
To create a materialized view in your own schema, you must have the CREATE MATERIALIZED VIEW privilege and the SELECT privilege to any tables referenced that are in another schema. To create a materialized view in another schema, you must have the CREATE ANY MATERIALIZED VIEW privilege and the owner of the materialized view needs SELECT privileges to the tables referenced if they are from another schema. Moreover, if you enable query rewrite on a materialized view that references tables outside your schema, you must have the GLOBAL QUERY REWRITE privilege or the QUERY REWRITE object privilege on each table outside your schema.
5. How to check Status of a Materialized View?
SQL> select mview_name,query,rewrite_enabled,refresh_mode,build_mode,staleness,c
ompile_state
2 from user_mviews;
MVIEW_NAME QUERY R REFRES BUILD_MOD STALENESS COMPILE_ST
---------- -------------------- - ------ --------- ---------- ----------
MYBIG select owner, count( Y DEMAND IMMEDIATE NEEDS_COMP NEEDS_COMP
*) from big group by ILE ILE
owner
MYMVIEW2 select ename,sum(sal N DEMAND DEFERRED UNUSABLE NEEDS_COMP
) from emp group by ILE
ename
MYVIEW5 select ename,sum(sal Y DEMAND IMMEDIATE FRESH VALID
) from emp group by
ename
MYMVIEW3 select ename,sum(sal Y COMMIT IMMEDIATE UNUSABLE VALID
) from emp group by
ename
MYVIEW1 select ename, sum(sa Y COMMIT IMMEDIATE FRESH VALID
l) from emp group by
Explanation:
MVIEW_NAME -------- > Materialized View name
QUERY ------------- > Query that defines the materialized view
REFRESH_ENABLED --- > If query_rewrite_enabled=TRUE then value is “Y” otherwise “N”
REFRESH_MODE ------ > DEMAND, COMMIT, NEVER
BUILD_MODE -------- > IMMEDIATE, DEFERRED
STALENESS --------- > Relationship between the contents of the materialized view and the contents of the materialized view's masters.
COMPILE_STATE ----- > Validity of the materialized view with respect to the objects upon which it depends
VALID -> No objects depends has changed since the last validation
NEED_COMPILE -> Some objects depends materialized view has changed an “alter materialized view … compile;” statement is required to validate this materialized view
Dear friends,
Did you hear about Materialized view?
Ans: Yes, No or May be.
Now I will explain you in details
What is materialized view?
1. Materialized view is normal database object like “table,index”
2. It is basically use for Data warehouse or Replication purpose.
3. Snapshot is synonym for materialized view.
4. A materialized view can be stored in the same database as its base tables or in a different database
5. A materialized view provides access to table data by storing the results of a query in a separate schema object. Unlike an ordinary view, which does not take up any storage space or contain any data, a materialized view contains the rows resulting from a query against one or more base tables or views.
6. A materialized view improve response time through query rewrite or reduce execution time.
Note: Query rewrite is a mechanism where Oracle or applications from the end user or database transparently improve query response time, by automatically rewriting the SQL query to use the materialized view instead of accessing the original tables. Query rewrites are particularly useful in a data warehouse environment.
What is syntax for materialized view?
Create materialized view “materialized_view_name”
Build [immediate / deferred]
Refresh [fash [on commit / demand]]
Enable query rewrite
As
Sql statement
/
Now Details explanation about syntax
1. Create materialized view “name’
I hope it is understood because self explanatory
2. Build [immediate / deferred]
1. Build immediate -- > materialized view populate with data on creation time from tables.
2. Build deferred --- > materialized view not populate with data on creation time, we have to populate later.
Eg:
With build immediate
create materialized view mymview1
build immediate
as
select ename,sum(sal) from emp group by ename;
SQL> select count(*) from emp;
COUNT(*)
----------
14
Note: materialized view populate with data on creation time.
With build deferred
create materialized view mymview2
build deferred
as
select ename,sum(sal) from emp group by ename;
SQL> select count(*) from mymview2;
COUNT(*)
----------
0
Note: materialized view not populate with data on creation time, we have to populate manually through DBMS_MVIEW.RERESH package.
3. Refresh [fast [on commit / demand]]
Refresh is method for populate materialized view with data whenever made changes in their base tables.
There are five refresh method for materialized view
1. refresh fast (materialized view log needed)
2. refresh on commit
3. refresh on demand
What is materialized view log?
A materialized view log is a schema object that records changes to a master table's data so that a materialized view defined on the master table can be refreshed incrementally.
Each materialized view log is associated with a single master table. The materialized view log resides in the same database and schema as its master table.
Eg:
With refresh on commit
SQL> select count(*) from emp;
COUNT(*)
----------
14
SQL> create materialized view MYVIEW1
2 build immediate
3 refresh on commit
4 enable query rewrite
5 as
6 select ename, sum(sal) from emp group by ename;
Materialized view created.
SQL> select count(*) from myview1;
COUNT(*)
----------
14
SQL> insert into emp (empno,ename,sal) values(1754,'TAJ',2500);
1 row created.
SQL> commit;
Commit complete.
SQL> select count(*) from myview1;
COUNT(*)
----------
15
With refresh on commit
SQL> select count(*) from emp;
COUNT(*)
----------
15
SQL> create materialized view MYVIEW5
2 build immediate
3 refresh on demand
4 enable query rewrite
5 as
6 select ename,sum(sal) from emp group by ename;
Materialized view created.
SQL> select count(*) from myview5;
COUNT(*)
----------
15
SQL> insert into emp (empno,ename,sal)values(1100,'xyz',1000);
1 row created.
SQL> commit;
Commit complete.
SQL> select count(*) from emp;
COUNT(*)
----------
16
SQL> select count(*) from myview5;
COUNT(*)
----------
15
SQL> exec dbms_mview.refresh('MYVIEW5');
PL/SQL procedure successfully completed.
SQL> select count(*) from myview5;
COUNT(*)
----------
16
Whenever any changes made in base tables and perform commit then materialized view refreshed.
4. Enable query rewrite
A materialized view is like a query with a result that is materialized and stored in a table. When a user query is found compatible with the query associated with a materialized view, the user query can be rewritten in terms of the materialized view. This technique improves the execution of the user query, because most of the query result has been precomputed. The query transformer looks for any materialized views that are compatible with the user query and selects one or more materialized views to rewrite the user query. The use of materialized views to rewrite a query is cost-based. That is, the query is not rewritten if the plan generated without the materialized views has a lower cost than the plan generated with the materialized views.
Note: query_rewrite_enabled=true AND query_rewrite_integrity=enforced must be set to use query rewrite feature
5. How Materialized view improve query performance.
Let’s check with demo
1. Create Big Table :)
SQL> create table big
2 as select * from all_objects
3 union all
4 select * from all_objects
5 union all
6 select * from all_objects
7 union all
8 select * from all_objects
9 union all
10 select * from all_objects;
Table created.
SQL> select count(*) from big;
COUNT(*)
----------
188995
2. Now execute below query and check query statistics
SQL> alter system flush shared_pool;
System altered.
SQL> set autotrace traceonly
SQL> set timing on
SQL> select owner,count(*) from big group by owner;
12 rows selected.
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=624 Card=188024 By
tes=3196408)
1 0 SORT (GROUP BY) (Cost=624 Card=188024 Bytes=3196408)
2 1 TABLE ACCESS (FULL) OF 'BIG' (TABLE) (Cost=598 Card=1880
24 Bytes=3196408)
Statistics
----------------------------------------------------------
957 recursive calls
0 db block gets
2844 consistent gets---- > too big :)
0 physical reads
0 redo size
1028 bytes sent via SQL*Net to client
504 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
10 sorts (memory)
0 sorts (disk)
12 rows processed
3. Now Create materialized view and try
SQL> create materialized view MYVIEWBIG
2 build immediate
3 refresh on commit
4 enable query rewrite
5 as
6 select owner,count(*) from big group by owner;
Materialized view created.
SQL> alter system flush shared_pool;
System altered.
SQL> set autotrace traceonly
SQL> set timing on
SQL> select owner,count(*) from big group by owner;
12 rows selected.
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=12 Bytes=36
0)
1 0 MAT_VIEW REWRITE ACCESS (FULL) OF 'MYVIEWBIG' (MAT_VIEW RE
WRITE) (Cost=3 Card=12 Bytes=360)
Statistics
----------------------------------------------------------
7356 recursive calls
0 db block gets
1313 consistent gets --- > just less then half :)
0 physical reads
0 redo size
1028 bytes sent via SQL*Net to client
504 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
49 sorts (memory)
0 sorts (disk)
12 rows processed
4. What Privileges Need for materialized view ?
To create a materialized view in your own schema, you must have the CREATE MATERIALIZED VIEW privilege and the SELECT privilege to any tables referenced that are in another schema. To create a materialized view in another schema, you must have the CREATE ANY MATERIALIZED VIEW privilege and the owner of the materialized view needs SELECT privileges to the tables referenced if they are from another schema. Moreover, if you enable query rewrite on a materialized view that references tables outside your schema, you must have the GLOBAL QUERY REWRITE privilege or the QUERY REWRITE object privilege on each table outside your schema.
5. How to check Status of a Materialized View?
SQL> select mview_name,query,rewrite_enabled,refresh_mode,build_mode,staleness,c
ompile_state
2 from user_mviews;
MVIEW_NAME QUERY R REFRES BUILD_MOD STALENESS COMPILE_ST
---------- -------------------- - ------ --------- ---------- ----------
MYBIG select owner, count( Y DEMAND IMMEDIATE NEEDS_COMP NEEDS_COMP
*) from big group by ILE ILE
owner
MYMVIEW2 select ename,sum(sal N DEMAND DEFERRED UNUSABLE NEEDS_COMP
) from emp group by ILE
ename
MYVIEW5 select ename,sum(sal Y DEMAND IMMEDIATE FRESH VALID
) from emp group by
ename
MYMVIEW3 select ename,sum(sal Y COMMIT IMMEDIATE UNUSABLE VALID
) from emp group by
ename
MYVIEW1 select ename, sum(sa Y COMMIT IMMEDIATE FRESH VALID
l) from emp group by
Explanation:
MVIEW_NAME -------- > Materialized View name
QUERY ------------- > Query that defines the materialized view
REFRESH_ENABLED --- > If query_rewrite_enabled=TRUE then value is “Y” otherwise “N”
REFRESH_MODE ------ > DEMAND, COMMIT, NEVER
BUILD_MODE -------- > IMMEDIATE, DEFERRED
STALENESS --------- > Relationship between the contents of the materialized view and the contents of the materialized view's masters.
COMPILE_STATE ----- > Validity of the materialized view with respect to the objects upon which it depends
VALID -> No objects depends has changed since the last validation
NEED_COMPILE -> Some objects depends materialized view has changed an “alter materialized view … compile;” statement is required to validate this materialized view
What is the difference between @ and @@ in Sql*plus?
What is the difference between @ and @@ in Sql*plus?
From Sql*plus we can execute any script with START command. @ is similar to START. If you write SQL>@file.txt then the contents inside file.txt will be executed.
The command @@ is almost identical to the @ ("at" sign) command.
From Sql*plus we can execute any script with START command. @ is similar to START. If you write SQL>@file.txt then the contents inside file.txt will be executed.
The command @@ is almost identical to the @ ("at" sign) command.
Subscribe to:
Posts (Atom)