Oracle stored procedures and triggers are faster than traditional code, which leads to an increase in popularity. As application code moves away from external programs and into the database engine, DBAs need to understand the related memory requirements for Oracle stored procedures and know how to manage stored procedures for optimal database performance.
There are many compelling benefits to putting all Oracle SQL inside stored procedures, including:
* Better performance. Oracle stored procedures load once into the shared pool and remain there unless they become paged out. Subsequent executions of the stored procedure are far faster than executions of external code.
* Coupling of data with behavior. DBAs can use naming conventions to couple relational tables with the behaviors associated with a table (using Oracle stored procedures as "methods"). If all behaviors associated with the employee table are prefixed with the table name--employee.hire, employee.give_raise, for example--the data dictionary can be queries to list all behaviors associated with a table (select * from dba_objects where owner = 'EMPLOYEE'), and it's easy to identify and reuse code.
* Isolation of code. Since all SQL is moved out of the external programs and into the Oracle stored procedures, the application programs become nothing more than calls to stored procedures. As such, it becomes very simple to swap out one database and swap in another one.
Read more about using stored procedures here:
http://oracle-tips.c.topica.com/maamAC1abMvSPbLGJrib/
Tips for Using Oracle Stored Procedures
Professional Dress Code Tips
First impressions count. A professional consultant who doesn’t take the time to maintain a professional appearance presents the image of not being able to perform adequately on the job. This professional dress code is codified because many professionals have never been taught appropriate professional appearance and demeanor.
Professional dress code standards are alive and well in major financial and executive management. Anyone who aspires to top management knows that personal appearance counts.
If you look and behave like a highly trained and well-groomed professional, you will win the respect and honor of valued clients.
A fresh haircut, spit-shined shoes and a crisp suit go a long way in establishing a professional demeanor.
Read more about professional dress code tips here:
http://oracle-tips.c.topica.com/maamACVabMvSybLGJrib/
Oracle downsizing: How to Avoid Layoffs
The collapse of major financial institutions has rippled through the economy and managers everywhere are searching for ways to stretch their dollars. In this article, we will explore the ways that Oracle Managers trim their budgets to survive the looming worldwide recession.
The market for Oracle professionals has been a boom-or-bust market. In years where demand exceeds supply (1996-1999), anyone with even the most marginal skills could secure a job in Oracle technology. But bank failures, global insecurity, the credit crunch and declining sales have made Corporations faced with tough choices. Some must either limp along with fewer resources or face bankruptcy, and managers everywhere are struggling to find cost cutting opportunities throughout the organization.
This has had a direct and noticeable impact on the Oracle community. Layoffs are rampant, and those Oracle professionals who are fortunate enough to have their jobs are faced with cutting corners. Many perks that were considered essential to an Oracle professional a few years ago are gone.
When faced with the reality of doing layoffs most managers try to be as fair as possible, preparing an objective comparison of cost vs. performance for each employee. However, you must also consider intangible aspects such as the loss of institutional knowledge which is especially critical for Oracle DBA’s who understand the complex nuances of your systems.
Read more about how Oracle managers handle layoffs in uncertain times here:
http://oracle-tips.c.topica.com/maamypyabMiYxbLGJrib/
Speed Oracle SQL with Temporary Tables
Using temporary tables with Dictionary Views
The prudent use of temporary tables can dramatically improve Oracle SQL performance. To illustrate the concept, consider the following example from the DBA world. In the query that follows, we want to identify all users who exist within Oracle who have not been granted a role. We could formulate the query as an anti-join with a noncorrelated subquery (against a complex view) as shown here:
select
username
from
dba_users
where
username NOT IN
(select grantee from dba_role_privs);
This query runs in 18 seconds. As you may remember from Chapter 12, these anti-joins can often be replaced with an outer join. However, we have another option by using CTAS. Now, we rewrite the same query to utilize temporary tables by selecting the distinct values from each table.
create table
temp1
as
select
username
from
dba_users;
create table
temp2
as
select distinct
grantee
from
dba_role_privs;
select
username
from
temp1
where
username not in
(select grantee from temp2);
With the addition of temporary tables to hold the intermediate results, this query runs in less than three seconds, a 6× performance increase. Again, it is not easy to quantify the reason for this speed increase, since the DBA views do not map directly to Oracle tables, but it is clear that temporary table show promise for improving the execution speed of certain types of Oracle SQL queries.
Using Temporary Tables
If the amount of data to be processed or utilized from your PL/SQL procedure is too large to fit comfortably in a PL/SQL table, use a GLOBAL TEMPORARY table rather than a normal table. A GLOBAL TEMPORARY table has a persistent definition but data is not persistent and the global temporary table generates no redo or rollback information. For example if you are processing a large number of rows, the results of which are not needed when the current session has ended, you should create the table as a temporary table instead:
create global temporary table
results_temp (...)
on commit preserve rows;
The “on commit preserve rows” clause tells the SQL engine that when a transaction is committed the table should not be cleared.
The global temporary table will be created in the users temporary tablespace when the procedure populates it with data and the DIRECT_IO_COUNT will be used to govern the IO throughput (this usually defaults to 64 blocks).
Oracle 11g New Features
At Oracle Openworld 2006, Oracle announced some exciting new features of Oracle 11g, and they promise 482 new Oracle11g features. For me, the most exciting new features relate to tools that automate the well-structured DBA tasks, freeing-up the DBA to pursue more challenging work.
- 11g SQL new features
- 11g language support new features
- 11g PL/SQL New Features
- 11g DBA features
- 11g RAC new features & enhancements
- 11g performance features
- 11g security new features
- 11g Enterprise Manager new features
As of 2007, the commercial database market is very mature and expectations are high. All of the leading databases do a good job in storing and retrieving data, and customers are now demanding self-tuning databases, intelligent engines that detect and correct sub-optimal conditions.
Oracle 11g is the clear leader in this area. Oracle has invested heavily into self-tuning capabilities including automated storage and memory management and intelligent tuning advisors. Now in 11g, Oracle closes the loop and offers intelligent automation tools to create a self-healing database. The most important 11g new automation features include a SQL tuning advisor that automatically tunes SQL statements.
How to Export Oracle Data
Data export is a common task for an Oracle DBA, but it is not necessarily a straightforward one. DBAs have to know how to handle Oracle export errors, work with multiple tables and rows and export stored procedures and functions. That's why SearchOracle.com created the Oracle data export guide, packed with best practices, step-by-step instructions and example scripts on how to export Oracle data.
Explore this learning guide to get all the data export errors and solutions recommended by our experts:
http://oracle-tips.c.topica.com/maamt05abLTMfbLGJrib/
Turbocharge SQL with Advanced Oracle Indexing
Oracle includes numerous data structures to improve the speed of Oracle SQL queries. Taking advantage of the low cost of disk storage, Oracle includes many new indexing algorithms that dramatically increase the speed with which Oracle queries are serviced. This article explores the internals of Oracle indexing; reviews the standard b-tree index, bitmap indexes, function-based indexes, and index-only tables (IOTs); and demonstrates how these indexes may dramatically increase the speed of Oracle SQL queries.
Oracle uses indexes to avoid the need for large-table, full-table scans and disk sorts, which are required when the SQL optimizer cannot find an efficient way to service the SQL query. I begin our look at Oracle indexing with a review of standard Oracle b-tree index methodologies.
The Oracle b-tree index
The oldest and most popular type of Oracle indexing is a standard b-tree index, which excels at servicing simple queries. The b-tree index was introduced in the earliest releases of Oracle and remains widely used with Oracle.
Read more on Turbocharging SQL:
http://oracle-tips.c.topica.com/maamrWGabLGVXbLGJrib/
Tuning individual SQL statements
The acronym SQL stands for Structured Query Language. SQL is an industry standard database query language that was adopted in the mid-1980s. It should not be confused with commercial products such as Microsoft SQL Server or open source products such as MySQL, both of which use the acronym as part of the title of their products.
Do this before you start individual SQL statement tuning
This broad-brush approach can save thousands of hours of tedious SQL tuning because you can hundreds of queries at once. Remember, you MUST do this first, else later changes to the optimizer parameters or statistics may un-tune your SQL Remember, you must ALWAYS start with system-level SQL tuning, else later changes might undo your tuned execution plans:
* Optimize the server kernel - You must always tune your disk and network I/O subsystem (RAID, DASD bandwidth, network) to optimize the I/O time, network packet size and dispatching frequency.
* Adjusting your optimizer statistics - You must always collect and store optimizer statistics to allow the optimizer to learn more about the distribution of your data to take more intelligent execution plans. Also, histograms can hypercharge SQL in cases of determining optimal table join order, and when making access decisions on skewed WHERE clause predicates.
* Adjust optimizer parameters - Optimizer optimizer_mode, optimizer_index_caching, optimizer_index_cost_adj.
* Optimize your instance - Your choice of db_block_size, db_cache_size, and OS parameters (db_file_multiblock_read_count, cpu_count, &c), can influence SQL performance.
* Tune your SQL Access workload with physical indexes and materialized views - Just as the 10g SQLAccess advisor recommends missing indexes and missing materialized views, you should always optimize your SQL workload with indexes, especially function-based indexes, a Godsend for SQL tuning.
11g Note: The Oracle 11g SQL Performance Analyzer (SPA), is primarily designed to speed up the holistic SQL tuning process.
Once you create a workload (called a SQL Tuning Set, or STS), Oracle will repeatedly execute the workload, using sophisticated predictive models (using a regression testing approach) to accurately identify the salient changes to SQL execution plans, based on your environmental changes.
Read more on SQL tuning steps:
http://oracle-tips.c.topica.com/maamqe5abLw4obLGJrib/