Consider the following query and execution plan:
SELECT a.customer_name, COUNT (DISTINCT b.invoice_id) "Open Invoices",
COUNT (c.invoice_id) "Open Invoice Items"
FROM customers a, invoices b, invoice_items c
WHERE b.invoice_status = 'OPEN'
AND a.customer_id = b.customer_id
AND c.invoice_id (+) = b.invoice_id
GROUP BY a.customer_name;
ID PARENT OPERATION OBJECT_NAME
---- ------ ----------------------------------- ------------------------------
0 SELECT STATEMENT
1 0 SORT GROUP BY
2 1 NESTED LOOPS OUTER
3 2 HASH JOIN
4 3 TABLE ACCESS BY INDEX ROWID INVOICES
5 4 INDEX RANGE SCAN INVOICES_STATUS
6 3 TABLE ACCESS FULL CUSTOMERS
7 2 INDEX RANGE SCAN INVOICE_ITEMS_PK
This execution plan is more complex than the previous two, and here you can start to get a feel for the way in which complex operations get broken down into simpler subordinate operations. To execute this query, the database server will do the following: First Oracle will perform a range scan on the invoices_status index to get the ROWIDs of all rows in the invoices table with the desired status. For each ROWID found, the record from the invoices table will be fetched.
This set of invoice records will be set aside for a moment while the focus turns to the customers table. Here, Oracle will fetch all customers records with a full table scan. To perform a hash join between the invoices and customers tables, Oracle will build a hash from the customer records and use the invoice records to probe the customer hash.
Next, a nested loops join will be performed between the results of the hash join and the invoice_items_pk index. For each row resulting from the hash join, Oracle will perform a unique scan of the invoice_items_pk index to find index entries for matching invoice items. Note that Oracle gets everything it needs from the index and doesn’t even need to access the invoice_items table at all. Also note that the nested loops operation is an outer join. A sort operation for the purposes of grouping is performed on the results of the nested loops operation in order to complete the SELECT statement.
It is interesting to note that Oracle choose to use a hash join and a full table scan on the customers table instead of the more traditional nested loops join. In this database there are many invoices and a relatively small number of customers, making a full table scan of the customers table less expensive than repeated index lookups on the customers_pk index. But suppose the customers table was enormous and the relative number of invoices was quite small. In that scenario a nested loops join might be better than a hash join. Examining the execution plan allows you to see which join method Oracle is using. You could then apply optimizer hints to coerce Oracle to use alternate methods and compare the performance.
You may wonder how I got that whole detailed explanation out of the eight line execution plan listing shown above. Did I read anything into the execution plan? No! It’s all there! Understanding the standard inputs and outputs of each type of operation and coupling this with the indenting is key to reading an execution plan.
A nested loops join operation always takes two inputs: For every row coming from the first input, the second input is executed once to find matching rows. A hash join operation also takes two inputs: The second input is read completely once and used to build a hash. For each row coming from the first input, one probe is performed against this hash. Sorting operations, meanwhile, take in one input. When the entire input has been read, the rows are sorted and output in the desired order.
Now let’s look at a query with a more complicated execution plan:
SELECT customer_name
FROM customers a
WHERE EXISTS
(
SELECT 1
FROM invoices_view b
WHERE b.customer_id = a.customer_id
AND number_of_lines > 100
)
ORDER BY customer_name;
ID PARENT OPERATION OBJECT_NAME
---- ------ ----------------------------------- ------------------------------
0 SELECT STATEMENT
1 0 SORT ORDER BY
2 1 FILTER
3 2 TABLE ACCESS FULL CUSTOMERS
4 2 VIEW INVOICES_VIEW
5 4 FILTER
6 5 SORT GROUP BY
7 6 NESTED LOOPS
8 7 TABLE ACCESS BY INDEX ROWID INVOICES
9 8 INDEX RANGE SCAN INVOICES_CUSTOMER_ID
10 7 INDEX RANGE SCAN INVOICE_ITEMS_PK
This execution plan is somewhat complex because the query includes a subquery that the optimizer could not rewrite as a simple join, and a view whose definition could not be merged into the query. The definition of the invoices_view view is as follows:
CREATE OR REPLACE VIEW invoices_view
AS
SELECT a.invoice_id, a.customer_id, a.invoice_date, a.invoice_status,
a.invoice_number, a.invoice_type, a.total_amount,
COUNT(*) number_of_lines
FROM invoices a, invoice_items b
WHERE b.invoice_id = a.invoice_id
GROUP BY a.invoice_id, a.customer_id, a.invoice_date, a.invoice_status,
a.invoice_number, a.invoice_type, a.total_amount;
Here is what this execution plan says: Oracle will execute this query by reading all rows from the customers table with a full table scan. For each customer record, the invoices_view view will be assembled as a filter and the relevant contents of the view will be examined to determine whether the customer should be part of the result set or not.
Oracle will assemble the view by performing an index range scan on the invoices_customer_id index and fetching the rows from the invoices table containing one specific customer_id. For each invoice record found, the invoice_items_pk index will be range scanned to get a nested loops join of invoices to their invoice_items records. The results of the join are sorted for grouping, and then groups with 100 or fewer invoice_items records are filtered out.
What is left at the step with ID 4 is a list of invoices for one specific customer that have more than 100 invoice_items records associated. If at least one such invoice exists, then the customer passes the filter at the step with ID 2. Finally, all customer records passing this filter are sorted for correct ordering and the results are complete.
Note that queries involving simple views will not result in a “view” operation in the execution plan. This is because Oracle can often merge a view definition into the query referencing the view so that the table accesses required to implement the view just become part of the regular execution plan. In this example, the GROUP BY clause embedded in the view foiled Oracle’s ability to merge the view into the query, making a separate “view” operation necessary in order to execute the query.
Also note that the filter operation can take on a few different forms. In general, a filter operation is where Oracle looks at a set of candidate rows and eliminates some based on certain criteria. This criteria could involve a simple test such as number_of_lines > 100 or it could be an elaborate subquery.
In this example, the filter at step ID 5 takes only one input. Here Oracle evaluates each row from the input one at a time and either adds the row to the output or discards it as appropriate. Meanwhile, the filter at step ID 2 takes two inputs. When a filter takes two inputs, Oracle reads the rows from the first input one at a time and executes the second input once for each row. Based on the results of the second input, the row from the first input is either added to the output or discarded.
Oracle is able to perform simple filtering operations while performing a full table scan. Therefore, a separate filter operation will not appear in the execution plan when Oracle performs a full table scan and throws out rows that don’t satisfy a WHERE clause. Filter operations with one input commonly appear in queries with view operations or HAVING clauses, while filter operations with multiple inputs will appear in queries with EXISTS clauses.
An important note about execution plans and subqueries: When a SQL statement involves subqueries, Oracle tries to merge the subquery into the main statement by using a join. If this is not feasible and the subquery does not have any dependencies or references to the main query, then Oracle will treat the subquery as a completely separate statement from the standpoint of developing an execution plan—almost as if two separate SQL statements were sent to the database server. When you generate an execution plan for a statement that includes a fully autonomous subquery, the execution plan may not include the operations for the subquery. In this situation, you need to generate an execution plan for the subquery separately.
Monday, September 27, 2010
HOW TO REMOVE CRS AUTO START AND RESTART FOR A RAC INSTANCE
HOW TO REMOVE CRS AUTO START AND RESTART FOR A RAC INSTANCE
-----------------------------------------------------------
OVERVIEW
Oracle Clusterware (CRS) is a new feature of Oracle Database 10g Real
Application Clusters that further differentiates RAC for high availability and
scalability. CRS provides automated management and sophisticated monitoring of
RAC instances and is designed to enhance the overall user experience of cluster
database management.
By default, CRS is configured to auto-start database instances as a part of node
boot and provide lights-out instance failure detection followed by an
auto-restart of the failed instance. However, on some special occasions,
it might be highly desirable to limit the level of protection CRS provides
for a RAC database. Namely, this implies preventing instances from auto-starting
on boot and not auto-restarting failed instances. The latter, however, may be
relaxed to allow a single attempt to restart a failed instance. This way, CRS
will attempt to restore availability of the instance, but avoid thrashing if a
problem that caused the instance to fail also keeps preventing it from
successfully recovering on restart. Either way, the choice to customize this is
left to the DBA.
Oracle Database 10g Real Application Clusters release 10.1.0.4 and above make
it possible to accomplish the above stated change in CRS behavior. This document
lists the steps necessary to limit the level of CRS protection over a RAC
database.
HIGH LEVEL APPROACH
In a nutshell, the procedure amounts to the following two parts
1. Identifying a set of CRS resources that affect the behavior
2. Modifying a few profile attributes to contain special values for the
resources identified in the first step
The following sections will cover both phases in detail.
IDENTIFYING RELEVANT RESOURCES
The automated management of a RAC database is accomplished by modeling various
RAC applications/features as CRS resources. A CRS resource is defined by a
profile, which contains a list of attributes that tell CRS how manage the
resource. CRS resources created to manage a RAC database can be identified as
belonging to a well-known type. There is a finite and relatively small number
of types. The type may be easily identified given the name of a resource: each
name ends with a. For instance, ora.linux.db is of type db,
which happens to mean database. To display the names of the resources managed
by the CRS, use the crs_stat command from a clustered node. The output of the
command is a set of names and states of resources registered on the cluster to
which the node belongs.
Figure 1. Example Listing resource names using crs_stat
$ crs_stat
NAME=ora.linux.ar.us.oracle.com.cs
TYPE=application
TARGET=OFFLINE
STATE=OFFLINE
NAME=ora.linux.ar.us.oracle.com.linux.srv
TYPE=application
TARGET=OFFLINE
STATE=OFFLINE
Additional output is available but has been truncated for clarity's sake
The relevant resources are the resources that belong to the following 4 types:
db - Denotes resources that represent a database
srv -Denotes resources that represent a service member.
cs - Denotes resources that represent a service.
inst - Denotes resources that represent a database instance
The CRS profiles for these resources must be modified for the new CRS behavior
to take effect for all RAC databases installed on the cluster. If, however,
the affect of the change is to be limited to a subset of installed databases,
the list of resources needs to be filtered further. (The rest of this section
should be skipped if the new CRS behavior. is to be in effect for all databases
installed on the cluster.)
Please note that since more than one database may be installed on a cluster,
to modify the level of protection for a particular database, one must identify
the resources that represent entities of this database. This may be easily
accomplished since the names of the resources belonging to the above- stated
types always start with ora.. For instance, ora.linux.db
means that the resource belongs to the database named linux. Only resources of
the above-enumerated types that belong to the selected databases will need to
have their profiles modified.
MODIFYING RESOURCE PROFILES
Please note that Oracle strongly discourages any modifications made to CRS
profiles for any resources starting with. Never make any modifications to
CRS profiles for resources but the ones explicitly described below in
this document.
To modify a profile attribute for a resource, the following steps must be
followed:
1.Generate the resource profile file by issuing the following command:
crs_stat -p resource_name > $CRS_HOME/crs/public/resource_name.cap
2.Update desired attributes by editing the file created in step 1.
3.Commit the updates made as a part of the previous step by issuing the
following command
crs_register -u resource_name
4. Verify the updates have been committed by issuing the following command
crs_stat -p resource_name
For each of the resources identified as a part of the preceding section, the
following modifications must be made:
1.Resources of type inst must have the following attributes modified
AUTO_START must be set to 2
RESTART_ATTEMPTS must be set to 0 or 1. The former value will prevent
CRS from attempting to restart a failed instance at all while the latter
will grant it a single attempt; if this only attempt is unsuccessful,
CRS will leave the instance as is.
2.Resources of type db, srv, cs must have the following attributes modified
AUTO_START must be set to 2
-----------------------------------------------------------
OVERVIEW
Oracle Clusterware (CRS) is a new feature of Oracle Database 10g Real
Application Clusters that further differentiates RAC for high availability and
scalability. CRS provides automated management and sophisticated monitoring of
RAC instances and is designed to enhance the overall user experience of cluster
database management.
By default, CRS is configured to auto-start database instances as a part of node
boot and provide lights-out instance failure detection followed by an
auto-restart of the failed instance. However, on some special occasions,
it might be highly desirable to limit the level of protection CRS provides
for a RAC database. Namely, this implies preventing instances from auto-starting
on boot and not auto-restarting failed instances. The latter, however, may be
relaxed to allow a single attempt to restart a failed instance. This way, CRS
will attempt to restore availability of the instance, but avoid thrashing if a
problem that caused the instance to fail also keeps preventing it from
successfully recovering on restart. Either way, the choice to customize this is
left to the DBA.
Oracle Database 10g Real Application Clusters release 10.1.0.4 and above make
it possible to accomplish the above stated change in CRS behavior. This document
lists the steps necessary to limit the level of CRS protection over a RAC
database.
HIGH LEVEL APPROACH
In a nutshell, the procedure amounts to the following two parts
1. Identifying a set of CRS resources that affect the behavior
2. Modifying a few profile attributes to contain special values for the
resources identified in the first step
The following sections will cover both phases in detail.
IDENTIFYING RELEVANT RESOURCES
The automated management of a RAC database is accomplished by modeling various
RAC applications/features as CRS resources. A CRS resource is defined by a
profile, which contains a list of attributes that tell CRS how manage the
resource. CRS resources created to manage a RAC database can be identified as
belonging to a well-known type. There is a finite and relatively small number
of types. The type may be easily identified given the name of a resource: each
name ends with a
which happens to mean database. To display the names of the resources managed
by the CRS, use the crs_stat command from a clustered node. The output of the
command is a set of names and states of resources registered on the cluster to
which the node belongs.
Figure 1. Example Listing resource names using crs_stat
$ crs_stat
NAME=ora.linux.ar.us.oracle.com.cs
TYPE=application
TARGET=OFFLINE
STATE=OFFLINE
NAME=ora.linux.ar.us.oracle.com.linux.srv
TYPE=application
TARGET=OFFLINE
STATE=OFFLINE
Additional output is available but has been truncated for clarity's sake
The relevant resources are the resources that belong to the following 4 types:
db - Denotes resources that represent a database
srv -Denotes resources that represent a service member.
cs - Denotes resources that represent a service.
inst - Denotes resources that represent a database instance
The CRS profiles for these resources must be modified for the new CRS behavior
to take effect for all RAC databases installed on the cluster. If, however,
the affect of the change is to be limited to a subset of installed databases,
the list of resources needs to be filtered further. (The rest of this section
should be skipped if the new CRS behavior. is to be in effect for all databases
installed on the cluster.)
Please note that since more than one database may be installed on a cluster,
to modify the level of protection for a particular database, one must identify
the resources that represent entities of this database. This may be easily
accomplished since the names of the resources belonging to the above- stated
types always start with ora.
means that the resource belongs to the database named linux. Only resources of
the above-enumerated types that belong to the selected databases will need to
have their profiles modified.
MODIFYING RESOURCE PROFILES
Please note that Oracle strongly discourages any modifications made to CRS
profiles for any resources starting with
CRS profiles for
this document.
To modify a profile attribute for a resource, the following steps must be
followed:
1.Generate the resource profile file by issuing the following command:
crs_stat -p resource_name > $CRS_HOME/crs/public/resource_name.cap
2.Update desired attributes by editing the file created in step 1.
3.Commit the updates made as a part of the previous step by issuing the
following command
crs_register -u resource_name
4. Verify the updates have been committed by issuing the following command
crs_stat -p resource_name
For each of the resources identified as a part of the preceding section, the
following modifications must be made:
1.Resources of type inst must have the following attributes modified
AUTO_START must be set to 2
RESTART_ATTEMPTS must be set to 0 or 1. The former value will prevent
CRS from attempting to restart a failed instance at all while the latter
will grant it a single attempt; if this only attempt is unsuccessful,
CRS will leave the instance as is.
2.Resources of type db, srv, cs must have the following attributes modified
AUTO_START must be set to 2
RMAN Terminology
Explanation of RMAN Terminology
RMAN TARGET DATABASE – An RMAN Target Database is the primary database that will be backed up for RAC Grid creation. In RMAN’s terminology, the term target database identifies the database that is undergoing a backup, restore or recovery operation by the RMAN Recovery Manager.
RMAN AUXILIARY DATABASE – An Auxiliary Database is a RAC Grid that will be created as a result of the duplication of the target database. In RMAN’s terminology, Auxiliary instance identifies an instance which RMAN connects in order to execute the duplicate command.
RMAN CHANNEL – An RMAN Channel is a communication pipeline between a RMAN executable and a target or auxiliary database. An RMAN channel consists of a server session on the target or auxiliary database and a data stream from the database to the backup device or vice-versa. RMAN console sends commands to the database using this channel, and the server session running on the database executes the command on behalf of the RMAN Recovery Manager.
RMAN AUTOMATIC CHANNEL ALLOCATION – RMAN Channels can be configured to use a set of default attributes for each operation when a channel is not allocated manually. By default, RMAN configures a channel of device type, DISK, to be used for automatic channel allocation.
RMAN MANUAL CHANNEL ALLOCATION - As the name suggests, a channel can be configured manually for special needs such as increasing the degree of parallelism. Channels can be allocated manually by using the ALLOCATE CHANNEL command in the RUN block of RMAN statement.
Connecting to an Auxiliary Database
To use the DUPLICATE command or to perform RMAN TSPITR, you need to connect to an auxiliary instance. In these examples, assume that these variables have the following meanings.
Variable Meaning
SYS User with SYSDBA privileges
oracle The password for connecting as SYSDBA specified in the target database's orapwd file
trgt The net service name for the target database
rman Owner of the recovery catalog having RECOVERY_CATALOG_OWNER privilege
cat The password for user RMAN specified in the recovery catalog's orapwd file
catdb The net service name for the recovery catalog database
aux The password for connecting as SYSDBA specified in the auxiliary database's orapwd file
auxdb The net service name for the auxiliary database
If the auxiliary database uses password files for authentication, then you can connect using a password for either local or remote access. If you are connecting remotely through a net service name, then authentication through a password file is mandatory.
Connecting to an Auxiliary Database from the Command Line
To connect to an auxiliary instance from the operating system command line, enter the following:
% rman AUXILIARY SYS/aux@auxdb
To connect to the target, auxiliary, and recovery catalog databases, issue the following (all on one line):
% rman TARGET SYS/oracle@trgt CATALOG rman/cat@catdb AUXILIARY SYS/aux@auxdb
Connecting to an Auxiliary Database from the RMAN Prompt
Alternatively, you can start RMAN and connect to the auxiliary database from the RMAN prompt:
% rman
RMAN> CONNECT AUXILIARY SYS/aux@auxdb
To connect to the target, auxiliary, and recovery catalog databases, issue:
% rman
RMAN> CONNECT TARGET SYS/oracle@trgt
RMAN> CONNECT CATALOG rman/cat@catdb
RMAN> CONNECT AUXILIARY SYS/aux@auxdb
RMAN TARGET DATABASE – An RMAN Target Database is the primary database that will be backed up for RAC Grid creation. In RMAN’s terminology, the term target database identifies the database that is undergoing a backup, restore or recovery operation by the RMAN Recovery Manager.
RMAN AUXILIARY DATABASE – An Auxiliary Database is a RAC Grid that will be created as a result of the duplication of the target database. In RMAN’s terminology, Auxiliary instance identifies an instance which RMAN connects in order to execute the duplicate command.
RMAN CHANNEL – An RMAN Channel is a communication pipeline between a RMAN executable and a target or auxiliary database. An RMAN channel consists of a server session on the target or auxiliary database and a data stream from the database to the backup device or vice-versa. RMAN console sends commands to the database using this channel, and the server session running on the database executes the command on behalf of the RMAN Recovery Manager.
RMAN AUTOMATIC CHANNEL ALLOCATION – RMAN Channels can be configured to use a set of default attributes for each operation when a channel is not allocated manually. By default, RMAN configures a channel of device type, DISK, to be used for automatic channel allocation.
RMAN MANUAL CHANNEL ALLOCATION - As the name suggests, a channel can be configured manually for special needs such as increasing the degree of parallelism. Channels can be allocated manually by using the ALLOCATE CHANNEL command in the RUN block of RMAN statement.
Connecting to an Auxiliary Database
To use the DUPLICATE command or to perform RMAN TSPITR, you need to connect to an auxiliary instance. In these examples, assume that these variables have the following meanings.
Variable Meaning
SYS User with SYSDBA privileges
oracle The password for connecting as SYSDBA specified in the target database's orapwd file
trgt The net service name for the target database
rman Owner of the recovery catalog having RECOVERY_CATALOG_OWNER privilege
cat The password for user RMAN specified in the recovery catalog's orapwd file
catdb The net service name for the recovery catalog database
aux The password for connecting as SYSDBA specified in the auxiliary database's orapwd file
auxdb The net service name for the auxiliary database
If the auxiliary database uses password files for authentication, then you can connect using a password for either local or remote access. If you are connecting remotely through a net service name, then authentication through a password file is mandatory.
Connecting to an Auxiliary Database from the Command Line
To connect to an auxiliary instance from the operating system command line, enter the following:
% rman AUXILIARY SYS/aux@auxdb
To connect to the target, auxiliary, and recovery catalog databases, issue the following (all on one line):
% rman TARGET SYS/oracle@trgt CATALOG rman/cat@catdb AUXILIARY SYS/aux@auxdb
Connecting to an Auxiliary Database from the RMAN Prompt
Alternatively, you can start RMAN and connect to the auxiliary database from the RMAN prompt:
% rman
RMAN> CONNECT AUXILIARY SYS/aux@auxdb
To connect to the target, auxiliary, and recovery catalog databases, issue:
% rman
RMAN> CONNECT TARGET SYS/oracle@trgt
RMAN> CONNECT CATALOG rman/cat@catdb
RMAN> CONNECT AUXILIARY SYS/aux@auxdb
Thursday, September 23, 2010
Parsing SQL Statements in Oracle
Parsing SQL Statements in Oracle
One of the first steps Oracle takes in processing a SQL statement is to parse it. During the parsing phase, Oracle will break down the submitted SQL statement into its component parts, determine what type of statement it is (Query, DML, or DDL), and perform a series of checks on it. Two important concepts for the DBA to understand is (1) what are the steps involved in the parse phase and (2) what is the difference between a hard parse and a soft parse.
The Syntax Check & Semantic Analysis
The first two function of the parse phase Syntax Check and Semantic Analysis happen for each and every SQL statement within the database.
Syntax Check
Oracle checks that the SQL statement is valid. Does it make sense given the SQL grammar documented in the SQL Reference Manual? Does it follow all of the rules for SQL?
Semantic Analysis
This function of the parse phase, takes the Syntax Check one step further by checking if the statement is valid in light of the objects in the database. Do the tables and columns referenced in the SQL statement actually exist in the database? Does the user executing the statement have access to the objects and are the proper privileges in place? Are there ambiguities in the statement? For example, consider a statement that references two tables emp1 and emp2 and both tables have a column name. The following statement "select name from emp1, emp where..." is ambiguous; the query doesn't know which table to get name from.
Although Oracle considers the first two functions of the parse phase (checking the validity of the SQL statement and then checking the semantics to ensure that the statement can be properly executed), the difference is sometimes hard to see from the users perspective. When Oracle reports an error to the user during the parse phase, it doesn't just come out and say "Error within the Syntax Function" or "Error within the Semantics Function".
For example, the following SQL statement fails with a syntax error:
SQL> select from where 4;
select from where 4
*
ERROR at line 1:
ORA-00936: missing expression
Here is an example of a SQL statement that fails with a semantic error:
SQL> select * from table_doesnt_exist;
select * from table_doesnt_exist
*
ERROR at line 1:
ORA-00942: table or view does not exist
Hard Parse vs. Soft Parse
We now consider the next and one of the most important functions of Oracle's parse phase. The Oracle database now needs to check in the Shared Pool to determine if the current SQL statement being parsed has already been processed by any other sessions.
If the current statement has already been processed, the parse operation can skip the next two functions in the process: Optimization and Row Source Generation. If the parse phase does, in fact, skip these two functions, it is called a soft parse. A soft parse will save a considerable amount of CPU cycles when running your query. On the other hand, if the current SQL statement has never been parsed by another session, the parse phase must execute ALL of the parsing steps. This type of parse is called a hard parse. It is especially important that developers write and design queries that take advantage of soft parses so that parsing phase can skip the optimization and row source generation functions, which are very CPU intensive and a point of contention (serialization). If a high percentage of your queries are being hard-parsed, your system will function slowly, and in some cases, not at all.
Oracle uses a piece of memory called the Shared Pool to enable sharing of SQL statements. The Shared Pool is a piece of memory in the System Global Area (SGA) and is maintained by the Oracle database. After Oracle completes the first two functions of the parse phase (Syntax and Semantic Checks), it looks in the Shared Pool to see if that same exact query has already been processed by another session. Since Oracle has already performed the semantic check, it has already determined:
Exactly what table(s) are involved
That the user has access privileges to the tables.
Oracle will now look at all of the queries in the Shared Pool that have already been parsed, optimized, and generated to see if the hard-parse portion of the current SQL statement has already been done.
Why not Check the Shared Pool First?
Now that you understand the steps involved in parsing SQL statements, it's time to take it one step further. Oracle will always keep an unparsed representation of the SQL code in the Shared Pool, and that the database will perform a hashing algorithm to quickly located the SQL code. OK, so why doesn't Oracle make this step (checking the Shared Pool for a matching statement) the first step in its parsing phase, before making any other checks.
Even when soft parsing, Oracle needs to parse the statement before it goes looking in the Shared Pool. One of the big reason's for this sequence is the Semantic Check. Consider the following query:
select * from emp;
Assume that this query was first submitted by user "SCOTT" and that the "emp" table in the FROM clause is a table owned by SCOTT. You then submit the same exact query (as a user other than SCOTT) to Oracle. The database has no idea what "emp" is a reference to. Is it a synonym to another table? Is it a view in your schema that references another table? For this reason, Oracle needs to perform a Semantic Check on the SQL statement to ensure that the code you are submitting is going to reference the same exact objects you are requesting in your query.
Then remember that Oracle needs to syntactically parse the query before it can semantically parse it. The hash is good only for finding query strings that are the same; it doesn't help the database figure out if the referenced statements are the same statement as in you execution context.
One of the first steps Oracle takes in processing a SQL statement is to parse it. During the parsing phase, Oracle will break down the submitted SQL statement into its component parts, determine what type of statement it is (Query, DML, or DDL), and perform a series of checks on it. Two important concepts for the DBA to understand is (1) what are the steps involved in the parse phase and (2) what is the difference between a hard parse and a soft parse.
The Syntax Check & Semantic Analysis
The first two function of the parse phase Syntax Check and Semantic Analysis happen for each and every SQL statement within the database.
Syntax Check
Oracle checks that the SQL statement is valid. Does it make sense given the SQL grammar documented in the SQL Reference Manual? Does it follow all of the rules for SQL?
Semantic Analysis
This function of the parse phase, takes the Syntax Check one step further by checking if the statement is valid in light of the objects in the database. Do the tables and columns referenced in the SQL statement actually exist in the database? Does the user executing the statement have access to the objects and are the proper privileges in place? Are there ambiguities in the statement? For example, consider a statement that references two tables emp1 and emp2 and both tables have a column name. The following statement "select name from emp1, emp where..." is ambiguous; the query doesn't know which table to get name from.
Although Oracle considers the first two functions of the parse phase (checking the validity of the SQL statement and then checking the semantics to ensure that the statement can be properly executed), the difference is sometimes hard to see from the users perspective. When Oracle reports an error to the user during the parse phase, it doesn't just come out and say "Error within the Syntax Function" or "Error within the Semantics Function".
For example, the following SQL statement fails with a syntax error:
SQL> select from where 4;
select from where 4
*
ERROR at line 1:
ORA-00936: missing expression
Here is an example of a SQL statement that fails with a semantic error:
SQL> select * from table_doesnt_exist;
select * from table_doesnt_exist
*
ERROR at line 1:
ORA-00942: table or view does not exist
Hard Parse vs. Soft Parse
We now consider the next and one of the most important functions of Oracle's parse phase. The Oracle database now needs to check in the Shared Pool to determine if the current SQL statement being parsed has already been processed by any other sessions.
If the current statement has already been processed, the parse operation can skip the next two functions in the process: Optimization and Row Source Generation. If the parse phase does, in fact, skip these two functions, it is called a soft parse. A soft parse will save a considerable amount of CPU cycles when running your query. On the other hand, if the current SQL statement has never been parsed by another session, the parse phase must execute ALL of the parsing steps. This type of parse is called a hard parse. It is especially important that developers write and design queries that take advantage of soft parses so that parsing phase can skip the optimization and row source generation functions, which are very CPU intensive and a point of contention (serialization). If a high percentage of your queries are being hard-parsed, your system will function slowly, and in some cases, not at all.
Oracle uses a piece of memory called the Shared Pool to enable sharing of SQL statements. The Shared Pool is a piece of memory in the System Global Area (SGA) and is maintained by the Oracle database. After Oracle completes the first two functions of the parse phase (Syntax and Semantic Checks), it looks in the Shared Pool to see if that same exact query has already been processed by another session. Since Oracle has already performed the semantic check, it has already determined:
Exactly what table(s) are involved
That the user has access privileges to the tables.
Oracle will now look at all of the queries in the Shared Pool that have already been parsed, optimized, and generated to see if the hard-parse portion of the current SQL statement has already been done.
Why not Check the Shared Pool First?
Now that you understand the steps involved in parsing SQL statements, it's time to take it one step further. Oracle will always keep an unparsed representation of the SQL code in the Shared Pool, and that the database will perform a hashing algorithm to quickly located the SQL code. OK, so why doesn't Oracle make this step (checking the Shared Pool for a matching statement) the first step in its parsing phase, before making any other checks.
Even when soft parsing, Oracle needs to parse the statement before it goes looking in the Shared Pool. One of the big reason's for this sequence is the Semantic Check. Consider the following query:
select * from emp;
Assume that this query was first submitted by user "SCOTT" and that the "emp" table in the FROM clause is a table owned by SCOTT. You then submit the same exact query (as a user other than SCOTT) to Oracle. The database has no idea what "emp" is a reference to. Is it a synonym to another table? Is it a view in your schema that references another table? For this reason, Oracle needs to perform a Semantic Check on the SQL statement to ensure that the code you are submitting is going to reference the same exact objects you are requesting in your query.
Then remember that Oracle needs to syntactically parse the query before it can semantically parse it. The hash is good only for finding query strings that are the same; it doesn't help the database figure out if the referenced statements are the same statement as in you execution context.
Thursday, September 16, 2010
Master node at Oracle Clusterware level
[oracle@dbrac1 dump]$ cat $ORA_CRS_HOME/log/`hostname`/cssd/ocssd* |grep master
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 2
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 2
[ CSSD]CLSS-3001: local node number 1, master node number 2
[ CSSD]CLSS-3001: local node number 1, master node number 2
[ CSSD]CLSS-3001: local node number 1, master node number 2
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 2
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 2
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 2
[ CSSD]CLSS-3001: local node number 1, master node number 2
[ CSSD]CLSS-3001: local node number 1, master node number 2
[ CSSD]CLSS-3001: local node number 1, master node number 2
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 2
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
[ CSSD]CLSS-3001: local node number 1, master node number 1
Monday, September 6, 2010
HOW TO APPLY ORACLE PATCH (OPATCH)
HOW TO APPLY ORACLE PATCH (OPATCH)
====================================================
To apply Opatch conditions are db and listener both must be down as opatch will update your current ORACLE_HOME with patches.
in single instance its not possible.
but for RAC instance its possible.
as in RAC there will be two seperate oracle home and two seperate instances running once instance on each oracle_home
use this command:
opatch napply -skip_subset -skip_duplicate -local -oh $ORACLE_HOME
when using -local parameter and -oh $ORACLE_HOME this means this patch session will only apply patch to current sourced ORACLE_HOME.
steps before applying patch:
----------------------------
1) check the database status.
wch_db.sql
-----------
select name,
open_mode,
database_name,
created,
log_mode,
platform_name
from v$database;
2) Check the object's invalid.
user_inv.sql
============
SELECT owner,
COUNT(*)
FROM dba_objects
WHERE status = 'INVALID'
GROUP BY owner;
count_inv.sql
-------------
select count(*)
from dba_objects
WHERE status ='INVALID';
3) Take backup of invalid's
create table bk_inv_ as select * from dba_objects
where status='INVALID';
4) check opatch version using
opatch -v
if opatch version is not compatible check the readme file and
download the latest version and uncompress
in $ORACLE_HOME.
5) check oraInst.loc file pointing to your current $ORACLE_HOME or not.
cat /etc/oraInst.loc
inventory_loc=/u01/app/oracle/10.2.0/GlobalOraInv
inst_group=dba
if your server have more then one $ORACLE_HOME then comment the other $ORACLE_HOME and
uncomment the current $ORACLE_HOME
inventory must point to the current $ORACLE_HOME which is getting patched.
6) check free space on $ORACLE_HOME
df -h $ORACLE_HOME
7) chek the utilities like
which ld
which ar
which make
etc as per readme file.
8) unzip the patch
unzip -d /loc_2_unzip p.zip
9) Go the patch directory
cd /loc_2_unzip/patch_number
10) Bring down the listner.
cd $ORACLE_HOME/bin
lsnrctl stop
11) Bring down the database
Shutdown immediate.
12) export opatch
export PATH=$PATH:$HOME:$ORACLE_HOME/OPatch:/bin
13) Start the patch
skip_duplicate
Skips patches to be applied that are duplicates of other patches installed in the Oracle home. Two patches are duplicates if they fix the same set of bugs.
skip_subset
Skips patches to be applied that are subsets of other patches installed in the Oracle home. One patch is a subset of another patch if the former fixes a subset of bugs fixed by the latter.
opatch napply -skip_subset -skip_duplicate
for RAC database then database can be up
as it may be having more then one instance
so you can bring down one instance and listener and apply the patch and open it
and then do the same on another node.
like this db will be up and no user will face issue in outage also.
to apply opatch in RAC instance (We can use -skip_subset -skip_duplicate with Opatch napply only? And is it right that if we are installing patch for the first time on a oracle software we will generally use opatch apply. consider I have applied october cpu patch and I am applying November patch. so while applting Oct patch I shall just use opatch apply and while applying Nov patch i shall use opatch napply -skip_subset -skip_duplicate)
///////////*************************************************888
The readme stats whether you should use opatch apply or opatch napply.
I am not sure whether november CPU patch is a bundle of multiple patch or single patch. If it is bundle of multiple patch then you should be using opatch napply irrespective of whether you applied any patch on your software previously. apply and naply does not depends on previous patches that are appled on your database. It just depend onl current opatch session. If you need to install just one patch currently use opatch apply. And if you want to install multiple patch in a single session use opatch napply.
And regarding -skip_subset and -skip_duplicate you are correct it shoudl be used only with opatch napply.
For more info use below command
opatch apply -help
opatch napply -help
**********************************************************///////////
opatch napply -skip_subset -skip_duplicate -local -oh $ORACLE_HOME
when using -local parameter and
-oh $ORACLE_HOME this means this patch session will only apply patch to current ORACLE_HOME only.
--------------------------------------------------------
. All-Node Patch
. Shutdown all Oracle instances on all nodes
. Apply the patch to all nodes
. Bring all nodes up
. Minimum downtime
. Shutdown the Oracle instance on node 1
. Apply the patch to the Oracle instance on node 1
. Shutdown the Oracle instance on node 2
. Apply the patch to the Oracle instance on node 2
. Shutdown the Oracle instance on node 3
. At this point, instances on nodes 1 and 2 can be brought up
. Apply the patch to the Oracle instance on node 3
. Startup the Oracle instance on node 3
. (no downtime)
. Shutdown the Oracle instance on node 1
. Apply the patch to the Oracle instance on node 1
. Start the Oracle instance on node 1
. Shutdown the Oracle instance on node 2
. Apply the patch to the Oracle instance on node 2
. Start the Oracle instance on node 2
. Shutdown the Oracle instance on node 3
. Apply the patch to the Oracle instance on node 3
. Start the Oracle instance on node 3
-------------------------------------------------------------
14) Once patch installation is completed need to do post patching steps.
a) starup the instance
startup
b) Loading modified sqlfiles into the database.
@$ORACLE_HOME/rdbms/admin/catbundle.sql cpu apply
to check the logs generated
catbundle_CPU__APPLY_.log
catbundle_CPU__GENERATE_.log
c) Recompiling Views in the Database
shutdown immediate
startup upgrade
@$ORACLE_HOME/cpu/view_recompile/view_recompile_jan2008cpu.sql
shutdown immediate
startup
If it is a RAC instance.
shutdown
startup nomount
alter database set cluster database=false scope=spfile;
shutdown
startup upgrade
@?/cpu/view_recompile/view_recompile_jan2008cpu.sql
shutdown
startup
alter database set cluster database=true scope=spfile;
restart the database.
cd $CRS_HOME/bin
srvctl start database -d
15) If any invalid objects were reported, run the utlrp.sql script as follows
user_inv.sql
============
SELECT owner,
COUNT(*)
FROM dba_objects
WHERE status = 'INVALID'
GROUP BY owner;
count_inv.sql
-------------
select count(*)
from dba_objects
WHERE status ='INVALID';
if any new invalids seen then again take backup of invalid objects and compile it.
create table bk_inv_ as select * from dba_objects
where status='INVALID';
@?/rdbms/admin/utlrp.sql --- to compile the invalid objects.
16) Confirm that patch has been applied successfully or not at db level also.
post_patch.sql
--------------
col action_time for a40
col action for a15
col namespace for a15
col version for a15
col comments for a40
set pages 1000
set lines 170
select * from registry$history ;
====================================================
To apply Opatch conditions are db and listener both must be down as opatch will update your current ORACLE_HOME with patches.
in single instance its not possible.
but for RAC instance its possible.
as in RAC there will be two seperate oracle home and two seperate instances running once instance on each oracle_home
use this command:
opatch napply -skip_subset -skip_duplicate -local -oh $ORACLE_HOME
when using -local parameter and -oh $ORACLE_HOME this means this patch session will only apply patch to current sourced ORACLE_HOME.
steps before applying patch:
----------------------------
1) check the database status.
wch_db.sql
-----------
select name,
open_mode,
database_name,
created,
log_mode,
platform_name
from v$database;
2) Check the object's invalid.
user_inv.sql
============
SELECT owner,
COUNT(*)
FROM dba_objects
WHERE status = 'INVALID'
GROUP BY owner;
count_inv.sql
-------------
select count(*)
from dba_objects
WHERE status ='INVALID';
3) Take backup of invalid's
create table bk_inv_ as select * from dba_objects
where status='INVALID';
4) check opatch version using
opatch -v
if opatch version is not compatible check the readme file and
download the latest version and uncompress
in $ORACLE_HOME.
5) check oraInst.loc file pointing to your current $ORACLE_HOME or not.
cat /etc/oraInst.loc
inventory_loc=/u01/app/oracle/10.2.0/GlobalOraInv
inst_group=dba
if your server have more then one $ORACLE_HOME then comment the other $ORACLE_HOME and
uncomment the current $ORACLE_HOME
inventory must point to the current $ORACLE_HOME which is getting patched.
6) check free space on $ORACLE_HOME
df -h $ORACLE_HOME
7) chek the utilities like
which ld
which ar
which make
etc as per readme file.
8) unzip the patch
unzip -d /loc_2_unzip p.zip
9) Go the patch directory
cd /loc_2_unzip/patch_number
10) Bring down the listner.
cd $ORACLE_HOME/bin
lsnrctl stop
11) Bring down the database
Shutdown immediate.
12) export opatch
export PATH=$PATH:$HOME:$ORACLE_HOME/OPatch:/bin
13) Start the patch
skip_duplicate
Skips patches to be applied that are duplicates of other patches installed in the Oracle home. Two patches are duplicates if they fix the same set of bugs.
skip_subset
Skips patches to be applied that are subsets of other patches installed in the Oracle home. One patch is a subset of another patch if the former fixes a subset of bugs fixed by the latter.
opatch napply -skip_subset -skip_duplicate
for RAC database then database can be up
as it may be having more then one instance
so you can bring down one instance and listener and apply the patch and open it
and then do the same on another node.
like this db will be up and no user will face issue in outage also.
to apply opatch in RAC instance (We can use -skip_subset -skip_duplicate with Opatch napply only? And is it right that if we are installing patch for the first time on a oracle software we will generally use opatch apply. consider I have applied october cpu patch and I am applying November patch. so while applting Oct patch I shall just use opatch apply and while applying Nov patch i shall use opatch napply -skip_subset -skip_duplicate)
///////////*************************************************888
The readme stats whether you should use opatch apply or opatch napply.
I am not sure whether november CPU patch is a bundle of multiple patch or single patch. If it is bundle of multiple patch then you should be using opatch napply irrespective of whether you applied any patch on your software previously. apply and naply does not depends on previous patches that are appled on your database. It just depend onl current opatch session. If you need to install just one patch currently use opatch apply. And if you want to install multiple patch in a single session use opatch napply.
And regarding -skip_subset and -skip_duplicate you are correct it shoudl be used only with opatch napply.
For more info use below command
opatch apply -help
opatch napply -help
**********************************************************///////////
opatch napply -skip_subset -skip_duplicate -local -oh $ORACLE_HOME
when using -local parameter and
-oh $ORACLE_HOME this means this patch session will only apply patch to current ORACLE_HOME only.
--------------------------------------------------------
. All-Node Patch
. Shutdown all Oracle instances on all nodes
. Apply the patch to all nodes
. Bring all nodes up
. Minimum downtime
. Shutdown the Oracle instance on node 1
. Apply the patch to the Oracle instance on node 1
. Shutdown the Oracle instance on node 2
. Apply the patch to the Oracle instance on node 2
. Shutdown the Oracle instance on node 3
. At this point, instances on nodes 1 and 2 can be brought up
. Apply the patch to the Oracle instance on node 3
. Startup the Oracle instance on node 3
. (no downtime)
. Shutdown the Oracle instance on node 1
. Apply the patch to the Oracle instance on node 1
. Start the Oracle instance on node 1
. Shutdown the Oracle instance on node 2
. Apply the patch to the Oracle instance on node 2
. Start the Oracle instance on node 2
. Shutdown the Oracle instance on node 3
. Apply the patch to the Oracle instance on node 3
. Start the Oracle instance on node 3
-------------------------------------------------------------
14) Once patch installation is completed need to do post patching steps.
a) starup the instance
startup
b) Loading modified sqlfiles into the database.
@$ORACLE_HOME/rdbms/admin/catbundle.sql cpu apply
to check the logs generated
catbundle_CPU__APPLY_.log
catbundle_CPU__GENERATE_.log
c) Recompiling Views in the Database
shutdown immediate
startup upgrade
@$ORACLE_HOME/cpu/view_recompile/view_recompile_jan2008cpu.sql
shutdown immediate
startup
If it is a RAC instance.
shutdown
startup nomount
alter database set cluster database=false scope=spfile;
shutdown
startup upgrade
@?/cpu/view_recompile/view_recompile_jan2008cpu.sql
shutdown
startup
alter database set cluster database=true scope=spfile;
restart the database.
cd $CRS_HOME/bin
srvctl start database -d
15) If any invalid objects were reported, run the utlrp.sql script as follows
user_inv.sql
============
SELECT owner,
COUNT(*)
FROM dba_objects
WHERE status = 'INVALID'
GROUP BY owner;
count_inv.sql
-------------
select count(*)
from dba_objects
WHERE status ='INVALID';
if any new invalids seen then again take backup of invalid objects and compile it.
create table bk_inv_ as select * from dba_objects
where status='INVALID';
@?/rdbms/admin/utlrp.sql --- to compile the invalid objects.
16) Confirm that patch has been applied successfully or not at db level also.
post_patch.sql
--------------
col action_time for a40
col action for a15
col namespace for a15
col version for a15
col comments for a40
set pages 1000
set lines 170
select * from registry$history ;
Subscribe to:
Posts (Atom)
