Learn SQL tuning tricks
While many Oracle professionals learned about the relational model and SQL in college, they make the common mistake of thinking that SQL tuning is governed by well-defined decision rules.
Oracle's SQL is only one of a host of different SQL optimizer implementations, and, like any other vendor implementation, there are many nuances and tricks to SQL tuning.
Not all Oracle SQL tuning tricks make sense, but nonetheless, there are powerful yet counterintuitive tricks that can make SQL run at optimal performance levels.
Becoming an expert in Oracle SQL tuning is not trivial, and there are no shortcuts. Only by tuning thousands of SQL statements can you fully understand the subtleties and nuances of Oracle SQL and be able to effective tune large and complex SQL statements.
..more..
Integrating Streams Replication and Standby Databases
The use of Oracle Streams Replication has become more and more common, replacing the more cumbersome Multimaster Advanced Replication. Many shops use Stream Replication to off load reporting and high impact loads from production databases. Based on my experience, more than half of the shops have the destination database in the same rack as the source database. With both databases located in close proximity, many shops are also implementing physical standbys of one or both of the replicated databases in a remote location. Oracle Streams and Dataguard Physical Standby database integrate easily, once you understand how the two technologies fit together.
..more..
..more..
Tracking & auditing changes to your init.ora parameters
A very important auditing task for any production database is the ability to track changes to the powerful initialization parameters. Many of these parameters are "silver bullets", single parameters that have a profound impact on system-wide behavior. This is especially true for SQL optimizer changes.
A single change to an optimizer parameter might affect thousands of SQL statements, a disaster in a controlled production environment.
Auditing changes to init.ora parameters (via pfile or spfile) is an important DBA task. Sometimes, users having “alter system” privileges can make unauthorized changes to the initialization parameters in the spfile on a production database. Hence, auditing changes to parameters is a critical DBA task.
..more..
A single change to an optimizer parameter might affect thousands of SQL statements, a disaster in a controlled production environment.
Auditing changes to init.ora parameters (via pfile or spfile) is an important DBA task. Sometimes, users having “alter system” privileges can make unauthorized changes to the initialization parameters in the spfile on a production database. Hence, auditing changes to parameters is a critical DBA task.
..more..
SQL tuning with views
Oracle views provide a benefit in these important areas:
·Code reuse: Views ensure that everyone uses the exact same SQL to access their tables.
·Column access security: Using the "grant" security model, views can be used for column-level security, such that some columns in a table may be "hidden" by not specifying them in a view.
For all of the benefits that Oracle views provide, they do come at a cost. One downside to using views is that it adds complexity to the SQL and makes it harder for the optimizer to service a query with the minimum amount of resources. This can pertain to either I/O or CPU resources, depending on the optimizer goal.
While it is clear that views are useful for end-user ad hoc queries and cases where the goal is to simplify the syntax of complex SQL queries, the following serious problems can occur when queries contain views:
·Predicate pushing: The downside to reusable code is that WHERE clause predicates must be "pushed" down the hierarchy of views to the base query. This adds processing overhead to the optimizer and increases the chances of a bad execution plan
·Non mergeable views: Because a view is an encapsulation of a complex query, it is used as if it were a discrete relational table. Hence, Oracle must pre-materialize a view whenever it is used in a query. This creates a hidden sub-plan that must be exposed for SQL tuning.
·Unnecessary overhead: Views are abused when they introduce unnecessary complexity. For example, there may be a call to a view that is composed of 10 tables where the result set only requires data from two tables.
·Excessive hard parsing: Predicate pushing may result in a hard parse of the underlying SQL that is executed. Hence, it is important to make sure bind variables are used instead of literals in SQL code calling views.
Read on to see important tips and tricks for tuning SQL that contains views.
..more..
·Code reuse: Views ensure that everyone uses the exact same SQL to access their tables.
·Column access security: Using the "grant" security model, views can be used for column-level security, such that some columns in a table may be "hidden" by not specifying them in a view.
For all of the benefits that Oracle views provide, they do come at a cost. One downside to using views is that it adds complexity to the SQL and makes it harder for the optimizer to service a query with the minimum amount of resources. This can pertain to either I/O or CPU resources, depending on the optimizer goal.
While it is clear that views are useful for end-user ad hoc queries and cases where the goal is to simplify the syntax of complex SQL queries, the following serious problems can occur when queries contain views:
·Predicate pushing: The downside to reusable code is that WHERE clause predicates must be "pushed" down the hierarchy of views to the base query. This adds processing overhead to the optimizer and increases the chances of a bad execution plan
·Non mergeable views: Because a view is an encapsulation of a complex query, it is used as if it were a discrete relational table. Hence, Oracle must pre-materialize a view whenever it is used in a query. This creates a hidden sub-plan that must be exposed for SQL tuning.
·Unnecessary overhead: Views are abused when they introduce unnecessary complexity. For example, there may be a call to a view that is composed of 10 tables where the result set only requires data from two tables.
·Excessive hard parsing: Predicate pushing may result in a hard parse of the underlying SQL that is executed. Hence, it is important to make sure bind variables are used instead of literals in SQL code calling views.
Read on to see important tips and tricks for tuning SQL that contains views.
..more..
Ethics for the Oracle DBA
Do DBAs have a social or ethical responsibility to the organizations they work for?
The role of a database administrator has changed according to the technology of database management systems (DBMSs) as well as the needs of the owners of the databases. For example, although logical and physical database designs are traditionally the duties of a database analyst or database designer, a DBA may be tasked to perform those duties.
Why we need a code of ethics:
System Administrators (SAs) and DBAs generally have high levels of access into computer systems at major corporations, financial institutions, educational facilities and brokerage firms. The IT professionals have access to highly-confidential information, prior to it becoming public knowledge, such as IPOs, stock ratings, debt ratings, test questions and answers just to name a few.
.. more ..
The role of a database administrator has changed according to the technology of database management systems (DBMSs) as well as the needs of the owners of the databases. For example, although logical and physical database designs are traditionally the duties of a database analyst or database designer, a DBA may be tasked to perform those duties.
Why we need a code of ethics:
System Administrators (SAs) and DBAs generally have high levels of access into computer systems at major corporations, financial institutions, educational facilities and brokerage firms. The IT professionals have access to highly-confidential information, prior to it becoming public knowledge, such as IPOs, stock ratings, debt ratings, test questions and answers just to name a few.
.. more ..
Subscribe to:
Posts (Atom)