Speed Oracle database queries with parallel features
by Donald Burleson | Jan 24, 2000 8:00:00 AM
Tags: Donald Burleson
0 comment(s) Email Share Digg Yahoo! Buzz Twitter Facebook Google del.icio.us StumbleUpon Reddit Newsvine Technorati Save Print Recommend 50
Takeaway: How would you like to accelerate your Oracle database queries? Donald Burleson says you can, if you take advantage of parallel features.
One of the most exciting, yet most overlooked, features of Oracle databases is the ability to dedicate multiple processors to service an Oracle query. The Oracle database has implemented parallel query features that allow a query to effectively use both symmetric multiprocessors (SMP) and massively parallel processors (MPP). Using these features, it is possible to read a one-gigabyte table with sub-second response time. Let’s begin with a review of these architectures.
A little background
Beginning in the 1960s, IBM began to implement mainframe processors with multiple CPUs. These were known as dyadic (two processors) or quadratic (four processors). Once these processors were implemented, software and database developers struggled with developing products that could take advantage of the ability to use multiple processors to service a task. These tools generally took the form of segmentation features that dedicated specific tasks to specific processors. They did not incorporate any ability to dynamically dedicate tasks to processors or to load-balance between CPUs.
Once the UNIX operating system became popular in the 1980s, hardware vendors (SUN, IBM, and Hewlett-Packard) began to offer computers with multiple CPUs and shared memory. These were known as SMP processors. On the other end of the spectrum, hardware vendors were experimenting with machines that contained hundreds, and even thousands, of individual CPUs. These became known as massively parallel processors.
As the Oracle database grew in popularity, the Oracle architects began to experiment with techniques that would allow the Oracle software to take advantage of these parallel features. However, it is not necessary to have parallel processors (SMP or MPP) in order to use and benefit from parallel processing. Even on the same processor, multiple processes can speed up queries.
Using Oracle parallel query
Oracle version 7.2 and above can partition an SQL query into sub-queries and dedicate separate processors to each one. At this time, parallel query is useful only for queries that perform full-table scans on long tables, but the performance improvements can be dramatic.
Here's how it works. Instead of having a single query server to manage the I/O against the table, parallel query allows the Oracle query server to dedicate many processes to simultaneously access the data. (See Figure A.)
Figure A
To be most effective, the table should be partitioned onto separate disk devices, such that each process can do I/O against its segment of the table without interfering with the other simultaneous query processes. However, the client-server environment of the 1990s relies on RAID or a logical volume manager (LVM), which scrambles data files across disk packs in order to balance the I/O load. Consequently, full utilization of parallel query involves "striping" a table across numerous data files, each on a separate device.
Even if your system uses RAID or LVM, there are still some performance gains from using parallel query. In addition to using multiple processes to retrieve the table, the query manager will also dedicate numerous processes to simultaneously sort the result set. (See Figure B.)
Figure B
However, parallel query works best with symmetric multiprocessor (SMP) boxes. Also, it is important to configure the system to maximize the I/O bandwidth, either through disk striping or high-speed channels. Because of the parallel sorting feature, it is also a good idea to beef up the memory on the processor.
While sorting is no substitute for using a pre-sorted index, the parallel query manager will service requests far faster than if you use a single process. The data retrieval itself will not be particularly fast, since all of the retrieval processes are competing for a channel on the same disk. But each sort process has its own sort area (as determined by the sort_area_size init.ora parameter), so the sorting of the result set will progress very quickly.
In addition to full-table scans and sorting, the parallel query option also allows for parallel processes for merge joins and nested loops.
Query setup
Invoking the parallel query option requires several steps. The most important is that the execution plan for the query specifies a full-table scan. If the output of the execution plan does not indicate a full-table scan, the query can be forced to ignore the index by using query hints.
The number of processors dedicated to service an SQL request is ultimately determined by Oracle query manager, but the programmer can specify the upper limit on the number of simultaneous processes. When using the cost-based optimizer, the PARALLEL hint can be embedded into the SQL to specify the number of processes. For instance:
select /*+ FULL(employee_table) PARALLEL(employee_table, 4) */
employee_name from
employee_table where
emp_type = 'SALARIED';
If you are using SMP with many CPUs, you can issue a parallel request and leave it up to each Oracle instance to use its default degree of parallelism. For example:
select /*+ FULL(employee_table) PARALLEL(employee_table, DEFAULT, DEFAULT) */
employee_name
from employee_table
where emp_type = 'SALARIED';
Parallel query parameters
Several important init.ora parameters have a direct impact on parallel query:
sort_area_size—The higher the value, the more memory available for individual sorts on each parallel process. Note that the sort_area_size parameter allocates memory for every query on the system that invokes a sort. For example, if a single query needs more memory, and you increase the sort_area_size, all Oracle tasks will allocate the new amount of sort area, regardless of whether they will use all of the space.
parallel_min_servers—This value specifies the minimum number of query servers that will be active on the instance. There are system resources involved in starting a query server, and having the query server started and waiting for requests will accelerate processing. Note that if the actual number of required servers is less than the values of parallel_min_servers, the idle query servers will be consuming unnecessary overhead, and the value should be decreased.
parallel_max_servers—This value specifies the maximum number of query servers allowed on the instance. This parameter will prevent Oracle from starting so many query servers that the instance cannot service all of them properly.
Parallel dictionary queries
To see how many parallel query servers are busy at any given time, the following query can be issued against the v$pq_sysstat table:
select * from v$pq_sysstat where statistic = 'Servers Busy';
STATISTIC VALUE
------------------- ------------
Servers Busy 30
In this case, we see that 30 parallel servers are busy at this moment. Do not be misled by this number. Parallel query servers are constantly accepting work or returning to idle status, so it is a good idea to issue the query many times over a one-hour period. Only then will you receive a realistic measure of how many parallel query servers are being used.
Don Burleson is a leading database author with expertise in data warehouse technology and tuning. He has more than 15 years' experience as a database administrator, working with some of the world’s most sophisticated Oracle systems.
Friday, December 12, 2008
Friday, November 14, 2008
10 Truths on Investing in Stocks
1. Investment rewards can only be increased by the assumption of greater risk
This fundamental law of finance is supported by centuries of historical data. US stocks have provided a compounded rate of return of 11 per cent per year since 1926, but this return came only at substantial risk to investors: total returns were negative in three out of ten years. Higher risk is the price one pays for more generous returns.
2. Your actual risk in stock and bond investing depends on the length of time you hold your investment
Holders of a diversified stock portfolio in the US, from 1950 to 2000, were treated to a range of annual total returns, which varied from +52% to -26%. There was no dependability of earning an adequate return in any single year. But if you held your portfolio for 25 years in the same period, your overall return would have been close to 11% -- whichever 25 years you were invested.
In other words, by holding stocks for relatively long periods of time, you can be reasonably sure of earning the generous rates of return available from common stocks.
3. Decide how much risk you are willing to take to get high returns
JP Morgan once had a friend who was so worried about his stock holdings that he could not sleep at night. Morgan advised him to 'sell down to his sleeping point'. He wasn't kidding.
Every investor must decide the trade-off he or she is willing to make between eating well and sleeping well. Your tolerance for risk informs the types of investment -- stocks, bonds, money-market accounts, property -- that you make. So what's your sleeping point?
4. Dollar-cost averaging can reduce the risk of investing in stocks and bonds
Dollar-cost averaging simply means investing the same fixed amount of money in, for example, the shares of a mutual fund at regular intervals -- say, every month or quarter -- over a long period.
It can reduce (but not avoid) the risks of equity investment by ensuring that the entire portfolio of stocks will not be purchased at temporarily inflated prices.
5. Stock prices are anchored to 'fundamentals' but the anchor is easily pulled up and then dropped in another place
The most important fundamental influence on prices is the level and duration of the future growth of corporate earnings and dividends. But earnings growth is not easily estimated, even by market professionals.
In times of optimism, it is easy to convince yourself that your favorite company will enjoy substantial and persistent growth over an extended period. In times of pessimism, many security analysts will not project any growth that is not 'visible' and hence will estimate only modest growth rates for the corporations they follow.
Given that expected growth rates and the price the market is willing to pay for growth can both change rapidly on the basis of market psychology, the concept of a firm intrinsic value for shares must be an elusive will-o-the-wisp.
6. If you buy stocks directly, confine your purchases to companies that appear able to sustain above-average earnings growth for at least five years and which can be bought at reasonable price-earnings multiples
As difficult as it may be, picking stocks whose earnings grow is the name of the game. Consistent growth not only increases the earnings and dividends of the company but may also increase the multiple (P/E) that the market is willing to pay for those earnings.
The purchaser of a stock whose earnings begin to grow rapidly has a potential double benefit: both the earnings and the multiple may increase.
7. Never pay more for a stock than can reasonably be justified by a firm foundation of value
Although I am convinced that you can never judge the exact intrinsic value of a stock, I do feel that you can roughly gauge when a stock seems to be reasonably priced. The market price earnings multiple (P/E) is a good place to start: you should buy stocks selling at multiples in line with, or not very much above, this ratio.
Note that, although similar, this is not simply another endorsement of the 'buy low P/E stocks' strategy. Under my rule it is perfectly alright to buy a stock with a P/E multiple slightly above the market average -- as long as the company's growth prospects are substantially above average.
8. Buy stocks with the kinds of stories of anticipated growth on which investors can build castle in the air
Stocks are like people -- some have more attractive personalities than others, and the improvement in a stock's P/E multiple may be smaller and slower to be realized if its story never catches on. The key to success is being where other investors will be, several months before they get there. Ask yourself whether the story about your stock is one that is likely to catch the fancy of the crowd.
9. Trade as little as possible
Frequent switching between stocks accomplishes nothing but subsidizing your broker and increasing your tax burden when you do realize gains. My own philosophy leads me to minimize trading as much as possible. I am merciless with the losers, however.
With few exceptions, I sell before the end of each calendar year any stocks on which I have a loss. The reason for this is that losses are deductible (up to certain amounts) for tax purposes, or can offset gains you may already have taken. Thus, taking losses can actually reduce the amount of loss by lowering your tax bill.
10. Give serious thought to index funds
Most investors will be better off buying index funds (funds that buy and hold all the stocks in a broad stock market index) rather than buying individual stocks.
Index funds provide broad diversification, low expenses and are tax efficient. Index funds regularly beat two-thirds of the actively managed funds with which they compete.
This fundamental law of finance is supported by centuries of historical data. US stocks have provided a compounded rate of return of 11 per cent per year since 1926, but this return came only at substantial risk to investors: total returns were negative in three out of ten years. Higher risk is the price one pays for more generous returns.
2. Your actual risk in stock and bond investing depends on the length of time you hold your investment
Holders of a diversified stock portfolio in the US, from 1950 to 2000, were treated to a range of annual total returns, which varied from +52% to -26%. There was no dependability of earning an adequate return in any single year. But if you held your portfolio for 25 years in the same period, your overall return would have been close to 11% -- whichever 25 years you were invested.
In other words, by holding stocks for relatively long periods of time, you can be reasonably sure of earning the generous rates of return available from common stocks.
3. Decide how much risk you are willing to take to get high returns
JP Morgan once had a friend who was so worried about his stock holdings that he could not sleep at night. Morgan advised him to 'sell down to his sleeping point'. He wasn't kidding.
Every investor must decide the trade-off he or she is willing to make between eating well and sleeping well. Your tolerance for risk informs the types of investment -- stocks, bonds, money-market accounts, property -- that you make. So what's your sleeping point?
4. Dollar-cost averaging can reduce the risk of investing in stocks and bonds
Dollar-cost averaging simply means investing the same fixed amount of money in, for example, the shares of a mutual fund at regular intervals -- say, every month or quarter -- over a long period.
It can reduce (but not avoid) the risks of equity investment by ensuring that the entire portfolio of stocks will not be purchased at temporarily inflated prices.
5. Stock prices are anchored to 'fundamentals' but the anchor is easily pulled up and then dropped in another place
The most important fundamental influence on prices is the level and duration of the future growth of corporate earnings and dividends. But earnings growth is not easily estimated, even by market professionals.
In times of optimism, it is easy to convince yourself that your favorite company will enjoy substantial and persistent growth over an extended period. In times of pessimism, many security analysts will not project any growth that is not 'visible' and hence will estimate only modest growth rates for the corporations they follow.
Given that expected growth rates and the price the market is willing to pay for growth can both change rapidly on the basis of market psychology, the concept of a firm intrinsic value for shares must be an elusive will-o-the-wisp.
6. If you buy stocks directly, confine your purchases to companies that appear able to sustain above-average earnings growth for at least five years and which can be bought at reasonable price-earnings multiples
As difficult as it may be, picking stocks whose earnings grow is the name of the game. Consistent growth not only increases the earnings and dividends of the company but may also increase the multiple (P/E) that the market is willing to pay for those earnings.
The purchaser of a stock whose earnings begin to grow rapidly has a potential double benefit: both the earnings and the multiple may increase.
7. Never pay more for a stock than can reasonably be justified by a firm foundation of value
Although I am convinced that you can never judge the exact intrinsic value of a stock, I do feel that you can roughly gauge when a stock seems to be reasonably priced. The market price earnings multiple (P/E) is a good place to start: you should buy stocks selling at multiples in line with, or not very much above, this ratio.
Note that, although similar, this is not simply another endorsement of the 'buy low P/E stocks' strategy. Under my rule it is perfectly alright to buy a stock with a P/E multiple slightly above the market average -- as long as the company's growth prospects are substantially above average.
8. Buy stocks with the kinds of stories of anticipated growth on which investors can build castle in the air
Stocks are like people -- some have more attractive personalities than others, and the improvement in a stock's P/E multiple may be smaller and slower to be realized if its story never catches on. The key to success is being where other investors will be, several months before they get there. Ask yourself whether the story about your stock is one that is likely to catch the fancy of the crowd.
9. Trade as little as possible
Frequent switching between stocks accomplishes nothing but subsidizing your broker and increasing your tax burden when you do realize gains. My own philosophy leads me to minimize trading as much as possible. I am merciless with the losers, however.
With few exceptions, I sell before the end of each calendar year any stocks on which I have a loss. The reason for this is that losses are deductible (up to certain amounts) for tax purposes, or can offset gains you may already have taken. Thus, taking losses can actually reduce the amount of loss by lowering your tax bill.
10. Give serious thought to index funds
Most investors will be better off buying index funds (funds that buy and hold all the stocks in a broad stock market index) rather than buying individual stocks.
Index funds provide broad diversification, low expenses and are tax efficient. Index funds regularly beat two-thirds of the actively managed funds with which they compete.
Wednesday, October 22, 2008
Good Artcile on Oralce 11i SQL Auto Tuning Feature from Burleson dba-oracle.com
Oracle has touted their new 11g “holistic” approach to SQL tuning as “fully automated SQL tuning”, but the marketing hype must be separated from the reality. The main benefit is that the DBA can now test changes to global parameters against a real-world workload, using a SQL Tuning Set (STS). Holistic tuning in Oracle 11g is offered through several functional areas, most importantly the SQL Performance Advisor (SPA) and the automated SQL Plan Management (SPM) facility. SPA is the natural evolution of the SQLAccess advisor:
• 10g SQLAccess Advisor – The 10g SQL access advisor tests real-world SQL workloads, recommending missing indexes and materialized views.
• 11g SQL Performance Analyzer – The SPA takes the SQLAccess advisor one step further and implements tuning recommendations for any SQL statements that run 3x faster (when tested with a new workload). For more details, see Inside the 11g SQL Performance Advisor .
How fully automated SQL tuning works
In a nutshell, the 11g fully automated SQL tuning is a series of processes and tools, loosely coupled for maximum flexibility.
A – The Setup for fully automatic SQL tuning – Here we capture representative SQL workloads (SQL tuning sets) and set-up a testing environment:
1 - Define the SQL workload - The DBA defines a "set" of problematic SQL statements (or chooses a representative workload). This is called the SQL Tuning set, or STS. This uses the dbms_sqltune.create_sqlset package.
2 - Set-up a changed environment - Here you can chose to change your initialization parms, test your performance against a previous release of the CBO (a very useful features when testing upgrades) or conduct "custom experiments" on the effect of environmental changes on your SQL tuning set.
B – Initial SQL tuning – Using the SQL Performance Analyzer, we optimize our environment using the SQL tuning set:
1 - Schedule & run workload tests - The workload is scheduled for execution during "low usage" periods, so that an empirical sample of real-world execution times can be collected and compared, using different execution plans from the two SQL tuning sets. To do this we run the dbms_sqlpa package. You can also use the OEM SPA “Guided Workflow” wizard.
2 - Implement the changes - For any statements that execute more then 3x faster, after the changes, Oracle 11g will automatically implement the changes via "SQL Profiles", a tool that bypasses the generation of an execution plans for incoming SQL, replacing it with the pre-tuned access plan.
C – Gather Baseline - Create the SQL Plan Baseline - To enable automatic SQL plan capture, set the optimizer_capture_sql_plan_baselines initialization parameter to TRUE.
D – Regression testing and implementation – We test global changes with the SQL Plan Manager (SPM). As the system characteristics change, you can use the SQL Plan Manager to test against real workloads and ensure that all changed execution plans result in at least 3x faster performance.
This is a huge improvement over the hit-and-miss SQL tuning techniques of the past, but it’s not a truly “fully-automated” approach either. We must remember that there will always be “outlier” SQL statements that must be tuned manually.
Fully Automated SQL Tuning is not a Panacea
There are many internal and external factors that influence the elapsed time for a given SQL statement, and the 11g SQL Performance Analyzer (SPA) and SQL Plan Management (SPM), the DBA can establish an "optimal baseline" before diving into the tuning of individual SQL statements:
• Optimize the server kernel - 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. Kernel settings have an indirect effect on SQL performance. For example, a kernel setting may speed up I/O, a change which is noted by the CBO workload statistics (using dbms_stats.gather_workload_stats). This, in turn, directly influences the optimizer's access decisions.
• Adjust your optimizer statistics - Always collect and store optimizer statistics to allow the optimizer to learn more about the distribution of your data to take more intelligent SQL access plans. Also, histograms can hypercharge SQL in cases of determining optimal table join order, and when making access decisions on skewed WHERE clause predicates. Also new in 11g, multi-column statistics can be gathered for use by the optimizer to determine optimal ways to run queries based upon multiple column criteria.
• Adjust optimizer parameters – You can empirically determine the best settings for optimizer_mode, optimizer_index_caching, optimizer_index_cost_adj.
• Optimize the instance - Your choice of db_block_size, db_cache_size, and OS parameters (db_file_multiblock_read_count, cpu_count, etc.) can influence SQL performance.
• Tune with 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.
• 10g SQLAccess Advisor – The 10g SQL access advisor tests real-world SQL workloads, recommending missing indexes and materialized views.
• 11g SQL Performance Analyzer – The SPA takes the SQLAccess advisor one step further and implements tuning recommendations for any SQL statements that run 3x faster (when tested with a new workload). For more details, see Inside the 11g SQL Performance Advisor .
How fully automated SQL tuning works
In a nutshell, the 11g fully automated SQL tuning is a series of processes and tools, loosely coupled for maximum flexibility.
A – The Setup for fully automatic SQL tuning – Here we capture representative SQL workloads (SQL tuning sets) and set-up a testing environment:
1 - Define the SQL workload - The DBA defines a "set" of problematic SQL statements (or chooses a representative workload). This is called the SQL Tuning set, or STS. This uses the dbms_sqltune.create_sqlset package.
2 - Set-up a changed environment - Here you can chose to change your initialization parms, test your performance against a previous release of the CBO (a very useful features when testing upgrades) or conduct "custom experiments" on the effect of environmental changes on your SQL tuning set.
B – Initial SQL tuning – Using the SQL Performance Analyzer, we optimize our environment using the SQL tuning set:
1 - Schedule & run workload tests - The workload is scheduled for execution during "low usage" periods, so that an empirical sample of real-world execution times can be collected and compared, using different execution plans from the two SQL tuning sets. To do this we run the dbms_sqlpa package. You can also use the OEM SPA “Guided Workflow” wizard.
2 - Implement the changes - For any statements that execute more then 3x faster, after the changes, Oracle 11g will automatically implement the changes via "SQL Profiles", a tool that bypasses the generation of an execution plans for incoming SQL, replacing it with the pre-tuned access plan.
C – Gather Baseline - Create the SQL Plan Baseline - To enable automatic SQL plan capture, set the optimizer_capture_sql_plan_baselines initialization parameter to TRUE.
D – Regression testing and implementation – We test global changes with the SQL Plan Manager (SPM). As the system characteristics change, you can use the SQL Plan Manager to test against real workloads and ensure that all changed execution plans result in at least 3x faster performance.
This is a huge improvement over the hit-and-miss SQL tuning techniques of the past, but it’s not a truly “fully-automated” approach either. We must remember that there will always be “outlier” SQL statements that must be tuned manually.
Fully Automated SQL Tuning is not a Panacea
There are many internal and external factors that influence the elapsed time for a given SQL statement, and the 11g SQL Performance Analyzer (SPA) and SQL Plan Management (SPM), the DBA can establish an "optimal baseline" before diving into the tuning of individual SQL statements:
• Optimize the server kernel - 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. Kernel settings have an indirect effect on SQL performance. For example, a kernel setting may speed up I/O, a change which is noted by the CBO workload statistics (using dbms_stats.gather_workload_stats). This, in turn, directly influences the optimizer's access decisions.
• Adjust your optimizer statistics - Always collect and store optimizer statistics to allow the optimizer to learn more about the distribution of your data to take more intelligent SQL access plans. Also, histograms can hypercharge SQL in cases of determining optimal table join order, and when making access decisions on skewed WHERE clause predicates. Also new in 11g, multi-column statistics can be gathered for use by the optimizer to determine optimal ways to run queries based upon multiple column criteria.
• Adjust optimizer parameters – You can empirically determine the best settings for optimizer_mode, optimizer_index_caching, optimizer_index_cost_adj.
• Optimize the instance - Your choice of db_block_size, db_cache_size, and OS parameters (db_file_multiblock_read_count, cpu_count, etc.) can influence SQL performance.
• Tune with 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.
Tuesday, June 17, 2008
Tuning PL/SQL File io
this is a good article from http://www.oracle-developer.net/display.php?id=425
tuning pl/sql file i/o
Unloading Oracle data to flat-files is still very common. There are numerous "unloader" utilities on the web for this purpose and there are also many related topics in the Oracle forums. Methods for writing data to flat-files vary, but strangely Oracle has never provided a tool to do this for us. The tools that are provided by Oracle (export, Data Pump, writeable external tables) write data quickly, but in a proprietary format, so for true ASCII flat-files, we have to resort to our own homegrown utilities.
There are several ways to generate flat-files from data stored in Oracle. We can use Perl, C, C++, Pro*C, Java and other languages that interface with Oracle but it is far more common for us to use SQL or PL/SQL to perform this operation. For example, sqlplus can spool data to flat-files very quickly, but it is a command utility and not part of the database. This makes it generally unsuited to being part of a robust database application.
UTL_FILE, on the other hand, is a built-in package around which we can very easily base our unloader utilities. The main issue with UTL_FILE, however, is that it is a relatively slow tool for unloading data, but in this article we will demonstrate some methods for speeding this up. We will see that with simple techniques we can achieve significant performance gains for our data unloads.
setup
We are going to test several versions of a standard data-unload routine. We will run these on an 11g Release 1 database, but all examples are compatible with 10g and most of them with 9i. The data to be dumped to flat-file will be sourced from a single table of 1 million rows, which we will create as follows.SQL> CREATE TABLE source_data
2 ( x, y, z
3 , CONSTRAINT source_data_pk
4 PRIMARY KEY (x,y,z)
5 )
6 ORGANIZATION INDEX
7 AS
8 SELECT ROWNUM AS x
9 , RPAD('x',50,'x') AS y
10 , RPAD('y',50,'y') AS z
11 FROM dual
12 CONNECT BY ROWNUM <= 1000000;
Table created.
We have created an IOT (index-organized table) to enable us to fully cache the data and eliminate any physical I/O. Each test we run will therefore incur roughly the same I/O costs. Using Autotrace, we will run a couple of full scans of this data until it is all in the buffer cache, as follows.SQL> set autotrace traceonly statistics
SQL> SELECT * FROM source_data;
1000000 rows selected.
Statistics
----------------------------------------------------------
88 recursive calls
0 db block gets
17779 consistent gets
15647 physical reads
0 redo size
108077685 bytes sent via SQL*Net to client
16231 bytes received via SQL*Net from client
2001 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1000000 rows processed
SQL> SELECT * FROM source_data;
1000000 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
17668 consistent gets
0 physical reads
0 redo size
108077883 bytes sent via SQL*Net to client
16231 bytes received via SQL*Net from client
2001 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1000000 rows processed
We have reduced the physical I/O as intended, so to complete our setup we will create an Oracle directory below. This is where we will write our flat-files.SQL> CREATE DIRECTORY dump_dir AS '/u01/app/oracle/dir';
Directory created.
baseline utl_file performance
Before we can tune our code, we need to know our baseline performance. In this case, it will be the time it takes to unload 1 million rows to a flat-file using UTL_FILE.PUT_LINE calls, using the following PL/SQL block.SQL> DECLARE
2
3 v_file UTL_FILE.FILE_TYPE;
4 v_name VARCHAR2(128) := 'utl_file_untuned.txt';
5 v_lines PLS_INTEGER := 0;
6
7 BEGIN
8
9 v_file := UTL_FILE.FOPEN('DUMP_DIR',v_name,'W',32767);
10
11 FOR r IN (SELECT x ',' y ',' z AS csv
12 FROM source_data)
13 LOOP
14 UTL_FILE.PUT_LINE(v_file, r.csv);
15 v_lines := v_lines + 1;
16 END LOOP;
17
18 UTL_FILE.FCLOSE(v_file);
19
20 DBMS_OUTPUT.PUT_LINE('File='v_name'; Lines='v_lines);
21
22 END;
23 /
File=utl_file_untuned.txt; Lines=1000000
PL/SQL procedure successfully completed.
Elapsed: 00:01:06.53
Our baseline performance is approximately 66 seconds to write the data to file. Note the following:
Line 11: each record is pre-formatted with a delimiter in the SQL cursor for convenience. A dump utility would usually accept the delimiter as a parameter and concatenate a record piecemeal (especially if using dynamic SQL);
Lines 11-13: we are using an implicit cursor-for-loop. From Oracle 10g Release 1, this will be optimised by the PL/SQL compiler into bulk collects with an array-size of 100 rows. All examples in this article will use either implicit or explicit bulk fetches of the same size. Note that readers on 9i databases will need to convert this and other implicit cursor examples to use explicit array fetches with BULK COLLECT to ensure that all examples are comparable.
tuning (1): buffering to reduce utl_file i/o
Our baseline code has two repetitive operations. First there is an incremented counter which is used to instrument the example. This has a negligible impact overall. More importantly there are 1 million UTL_FILE I/O operations and these have a far more significant impact. Tracing the baseline example with the PL/SQL Profiler and the new PL/SQL Hierarchical Profiler clearly shows that 50% of the elapsed time is accounted for by our 1 million UTL_FILE.PUT_LINE calls.
Our first technique for tuning this, therefore, will be to reduce the number of I/O operations by buffering the output data. This is quite a simple method. We will use a local variable to buffer up to 32K of data before writing it to file, as follows.SQL> DECLARE
2
3 v_file UTL_FILE.FILE_TYPE;
4 v_buffer VARCHAR2(32767);
5 v_name VARCHAR2(128) := 'utl_file_buffered.txt';
6 v_lines PLS_INTEGER := 0;
7 c_eol CONSTANT VARCHAR2(1) := CHR(10);
8 c_eollen CONSTANT PLS_INTEGER := LENGTH(c_eol);
9 c_maxline CONSTANT PLS_INTEGER := 32767;
10
11 BEGIN
12
13 v_file := UTL_FILE.FOPEN('DUMP_DIR',v_name,'W',32767);
14
15 FOR r IN (SELECT x ',' y ',' z AS csv
16 FROM source_data)
17 LOOP
18
19 IF LENGTH(v_buffer) + c_eollen + LENGTH(r.csv) <= c_maxline THEN
20 v_buffer := v_buffer c_eol r.csv;
21 ELSE
22 IF v_buffer IS NOT NULL THEN
23 UTL_FILE.PUT_LINE(v_file, v_buffer);
24 END IF;
25 v_buffer := r.csv;
26 END IF;
27
28 v_lines := v_lines + 1;
29
30 END LOOP;
31
32 UTL_FILE.PUT_LINE(v_file, v_buffer);
33 UTL_FILE.FCLOSE(v_file);
34
35 DBMS_OUTPUT.PUT_LINE('File='v_name'; Lines='v_lines);
36
37 END;
38 /
File=utl_file_buffered.txt; Lines=1000000
PL/SQL procedure successfully completed.
Elapsed: 00:00:30.06
The algorithm (highlighted above) is reasonably self-explanatory, but we will describe it anyway:
Lines 19-20: UTL_FILE has a maximum write-size of 32,767 bytes. If there is enough room in the buffer for a new record to be added (including newline), then we simply append the new data together with a newline;
Lines 21-26: if we can't fit the new record into the buffer, or the buffer is null (in which case the buffer-length calculation will also evaluate to null), then we must either flush it or start it. The buffer will only be null on the very first entry to the loop, so we make it the very last test;
Lines 22-24: when the buffer is too full to accept any new data, we need to write it to file with UTL_FILE;
Line 25: regardless of whether the buffer has been written or hasn't been started it, we reset/start it with the current data record;
Line 32: once the loop is complete, we write the remaining buffered data to the flat-file.
This technique is simple yet extremely effective. We have reduced our elapsed time to less than half the baseline: in other words, this unloading routine is now twice as fast (for this dataset). With an average record size of 105 bytes, we can buffer approximately 310 records, which means we reduce our UTL_FILE calls to roughly 3,200.
Given the effectiveness of the buffering technique, we will continue to use it for the remaining examples in this article.
a note on utl_file buffering
It is possible to replicate this buffering algorithm using the UTL_FILE.PUT, UTL_FILE.NEW_LINE and UTL_FILE.FFLUSH procedures, but a test with our sample data took over 85 seconds, which is considerably slower than our baseline. For this reason, the example is omitted from this article, but is included in the accompanying download.
tuning (2): using temporary clobs
From Oracle 10g onwards, it is possible to write a CLOB to a file with a single call, using the DBMS_XSLPROCESSOR.CLOB2FILE procedure. In the following example, we will prepare a temporary CLOB with our data instead of writing it with UTL_FILE. When all source data has been added to the CLOB, we will write it to a flat-file in a single call.SQL> DECLARE
2
3 v_file CLOB;
4 v_buffer VARCHAR2(32767);
5 v_name VARCHAR2(128) := 'clob2file_buffered.txt';
6 v_lines PLS_INTEGER := 0;
7 v_eol VARCHAR2(2);
8 v_eollen PLS_INTEGER;
9 c_maxline CONSTANT PLS_INTEGER := 32767;
10
11 BEGIN
12
13 v_eol := CASE
14 WHEN DBMS_UTILITY.PORT_STRING LIKE 'IBMPC%'
15 THEN CHR(13)CHR(10)
16 ELSE CHR(10)
17 END;
18 v_eollen := LENGTH(v_eol);
19
20 DBMS_LOB.CREATETEMPORARY(v_file, TRUE);
21
22 FOR r IN (SELECT x ',' y ',' z AS csv
23 FROM source_data)
24 LOOP
25
26 IF LENGTH(v_buffer) + v_eollen + LENGTH(r.csv) <= c_maxline THEN
27 v_buffer := v_buffer v_eol r.csv;
28 ELSE
29 IF v_buffer IS NOT NULL THEN
30 DBMS_LOB.WRITEAPPEND(
31 v_file, LENGTH(v_buffer) + v_eollen, v_buffer v_eol
32 );
33 END IF;
34 v_buffer := r.csv;
35 END IF;
36
37 v_lines := v_lines + 1;
38
39 END LOOP;
40
41 IF LENGTH(v_buffer) > 0 THEN
42 DBMS_LOB.WRITEAPPEND(
43 v_file, LENGTH(v_buffer) + v_eollen, v_buffer v_eol
44 );
45 END IF;
46
47 DBMS_XSLPROCESSOR.CLOB2FILE(v_file, 'DUMP_DIR', v_name);
48 DBMS_LOB.FREETEMPORARY(v_file);
49
50 DBMS_OUTPUT.PUT_LINE('File='v_name'; Lines='v_lines);
51
52 END;
53 /
File=clob2file_buffered.txt; Lines=1000000
PL/SQL procedure successfully completed.
Elapsed: 00:00:28.65
The CLOB-specific code is highlighted above and is self-explanatory (perhaps with the exception of the end-of-line character assignment on lines 13-17 which is different for Windows. UTL_FILE manages the port-specific end-of-line conversions for us, but with CLOBs we must manage this ourselves).
Of particular interest is the DBMS_XSLPROCESSOR call on line 47, which is our only write operation to the destination flat-file. We can see overall that this technique is similar in performance to our buffered UTL_FILE mechanism (the CLOB method was slightly quicker). We therefore have an alternative method for writing data, but there will be additional costs associated with using CLOBs (for example, temporary tablespace and buffer cache). If the volume of data to be dumped is high, then this method might put too much stress on our temporary tablespace and cause problems for other users (large sort operations, hash joins, global temporary tables etc). Care should therefore be taken when using this method.
tuning (3): parallel execution with pipelined functions
Our final technique for quicker data-dumping from PL/SQL will be to split the workload and have the data written in separate streams. We can do this quite simply by using multiple sessions, with each reading and dumping different ranges of source data (Tom Kyte calls this "DIY parallelism"). A simpler (and possibly more elegant) method is to use Oracle's parallel query (PQ) mechanism and create a parallel pipelined function to split the source data and dump it using multiple PQ slaves.
For this test, we will create a parallel pipelined function that writes the source data to flat-file and returns a single summary record per session. This record will include details of the file and the session that wrote it. It is assumed that readers are familiar with the concept of parallel pipelined functions (some background reading is available if required).
required types
We will begin by creating an object type to define the structure of our pipelined function's return data, as follows.SQL> CREATE TYPE dump_ot AS OBJECT
2 ( file_name VARCHAR2(128)
3 , no_records NUMBER
4 , session_id NUMBER
5 );
6 /
Type created.
Pipelined functions return collections of data, so we will create a nested table type based on our new object.SQL> CREATE TYPE dump_ntt AS TABLE OF dump_ot;
2 /
Type created.
We are now ready to create our parallel pipelined function. We will create and test two versions: one with UTL_FILE and one with a CLOB, because there has been little to distinguish them in the timed comparisons so far.
tuning (3a): parallel utl_file solution
We will begin by creating a parallel pipelined function that uses UTL_FILE to write data, as follows.SQL> CREATE FUNCTION parallel_dump (
2 p_source IN SYS_REFCURSOR,
3 p_filename IN VARCHAR2,
4 p_directory IN VARCHAR2
5 ) RETURN dump_ntt
6 PIPELINED
7 PARALLEL_ENABLE (PARTITION p_source BY ANY) AS
8
9 TYPE row_ntt IS TABLE OF VARCHAR2(32767);
10 v_rows row_ntt;
11 v_file UTL_FILE.FILE_TYPE;
12 v_buffer VARCHAR2(32767);
13 v_sid NUMBER;
14 v_name VARCHAR2(128);
15 v_lines PLS_INTEGER := 0;
16 c_eol CONSTANT VARCHAR2(1) := CHR(10);
17 c_eollen CONSTANT PLS_INTEGER := LENGTH(c_eol);
18 c_maxline CONSTANT PLS_INTEGER := 32767;
19
20 BEGIN
21
22 SELECT sid INTO v_sid FROM v$mystat WHERE ROWNUM = 1;
23 v_name := p_filename '_' TO_CHAR(v_sid) '.txt';
24 v_file := UTL_FILE.FOPEN(p_directory, v_name, 'w', 32767);
25
26 LOOP
27 FETCH p_source BULK COLLECT INTO v_rows LIMIT 100;
28
29 FOR i IN 1 .. v_rows.COUNT LOOP
30
31 IF LENGTH(v_buffer) + c_eollen + LENGTH(v_rows(i)) <= c_maxline THEN
32 v_buffer := v_buffer c_eol v_rows(i);
33 ELSE
34 IF v_buffer IS NOT NULL THEN
35 UTL_FILE.PUT_LINE(v_file, v_buffer);
36 END IF;
37 v_buffer := v_rows(i);
38 END IF;
39
40 END LOOP;
41
42 v_lines := v_lines + v_rows.COUNT;
43
44 EXIT WHEN p_source%NOTFOUND;
45 END LOOP;
46 CLOSE p_source;
47
48 UTL_FILE.PUT_LINE(v_file, v_buffer);
49 UTL_FILE.FCLOSE(v_file);
50
51 PIPE ROW (dump_ot(v_name, v_lines, v_sid));
52 RETURN;
53
54 END parallel_dump;
55 /
Function created.
Before we run a timed test using this function, note the following:
Line 7: we parallel-enable the function and state that Oracle can divide the source data amongst the PQ slaves any way it wishes;
Lines 22-23: each PQ slave will write to its own file, so we append the SID to the filename to make each one unique;
Lines 26-27: the source cursor will be passed as a refcursor parameter, so we must explicitly code a bulk fetch of 100 records to emulate the other examples in this article (i.e. those that benefit from 10g PL/SQL optimisation);
Line 51: on completion of the flat-file, we return a single record summarising the file details.
We will execute this function, as follows.SQL> SELECT *
2 FROM TABLE(
3 parallel_dump(
4 CURSOR(SELECT /*+ PARALLEL(s,4) */
5 x ','
6 y ','
7 z AS csv
8 FROM source_data s),
9 'utl_file_parallel_pipelined',
10 'DUMP_DIR'
11 )) nt;
FILE_NAME NO_RECORDS SESSION_ID
-------------------------------------------------- ---------- ----------
utl_file_parallel_pipelined_136.txt 190758 136
utl_file_parallel_pipelined_135.txt 192640 135
utl_file_parallel_pipelined_117.txt 288960 117
utl_file_parallel_pipelined_121.txt 327642 121
4 rows selected.
Elapsed: 00:00:10.26
This is impressive! We have reduced the elapsed time of our write operation from a baseline of 66 seconds to just 10 seconds. Of course, we now have four files instead of one, but we can easily append these files together with a script of some description (Perl, shell etc). Preferably, we would leave them as separate files and simply read them as though they were a single file by using an external table with an appropriate LOCATION setting (i.e. one that includes all four files).
tuning (3b): parallel temporary clob solution
To complete our tuning tests, we will re-create the parallel pipelined function to use the CLOB method (as noted earlier, this requires at least 10g).SQL> CREATE OR REPLACE FUNCTION parallel_dump (
2 p_source IN SYS_REFCURSOR,
3 p_filename IN VARCHAR2,
4 p_directory IN VARCHAR2
5 ) RETURN dump_ntt
6 PIPELINED
7 PARALLEL_ENABLE (PARTITION p_source BY ANY) AS
8
9 TYPE row_ntt IS TABLE OF VARCHAR2(32767);
10 v_rows row_ntt;
11 v_file CLOB;
12 v_buffer VARCHAR2(32767);
13 v_sid NUMBER;
14 v_name VARCHAR2(128);
15 v_lines PLS_INTEGER := 0;
16 v_eol VARCHAR2(2);
17 v_eollen PLS_INTEGER;
18 c_maxline CONSTANT PLS_INTEGER := 32767;
19
20 BEGIN
21
22 v_eol := CASE
23 WHEN DBMS_UTILITY.PORT_STRING LIKE 'IBMPC%'
24 THEN CHR(13)CHR(10)
25 ELSE CHR(10)
26 END;
27 v_eollen := LENGTH(v_eol);
28
29 SELECT sid INTO v_sid FROM v$mystat WHERE ROWNUM = 1;
30 v_name := p_filename '_' TO_CHAR(v_sid) '.txt';
31
32 DBMS_LOB.CREATETEMPORARY(v_file, TRUE);
33
34 LOOP
35 FETCH p_source BULK COLLECT INTO v_rows LIMIT 100;
36
37 FOR i IN 1 .. v_rows.COUNT LOOP
38
39 IF LENGTH(v_buffer) + v_eollen + LENGTH(v_rows(i)) <= c_maxline THEN
40 v_buffer := v_buffer v_eol v_rows(i);
41 ELSE
42 IF v_buffer IS NOT NULL THEN
43 DBMS_LOB.WRITEAPPEND(
44 v_file, LENGTH(v_buffer) + v_eollen, v_buffer v_eol
45 );
46 END IF;
47 v_buffer := v_rows(i);
48 END IF;
49
50 END LOOP;
51
52 v_lines := v_lines + v_rows.COUNT;
53
54 EXIT WHEN p_source%NOTFOUND;
55 END LOOP;
56 CLOSE p_source;
57
58 IF LENGTH(v_buffer) > 0 THEN
59 DBMS_LOB.WRITEAPPEND(
60 v_file, LENGTH(v_buffer) + v_eollen, v_buffer v_eol
61 );
62 END IF;
63
64 DBMS_XSLPROCESSOR.CLOB2FILE(v_file, p_directory, v_name);
65 DBMS_LOB.FREETEMPORARY(v_file);
66
67 PIPE ROW (dump_ot(v_name, v_lines, v_sid));
68 RETURN;
69
70 END parallel_dump;
71 /
Function created.
We have already described the CLOB and parallel pipelined function techniques, so we will proceed with our timing test, as follows.SQL> SELECT *
2 FROM TABLE(
3 parallel_dump(
4 CURSOR(SELECT /*+ PARALLEL(s,4) */
5 x ','
6 y ','
7 z AS csv
8 FROM source_data s),
9 'clob2file_parallel_pipelined',
10 'DUMP_DIR'
11 )) nt;
FILE_NAME NO_RECORDS SESSION_ID
-------------------------------------------------- ---------- ----------
clob2file_parallel_pipelined_200.txt 248504 200
clob2file_parallel_pipelined_196.txt 232768 196
clob2file_parallel_pipelined_198.txt 248192 198
clob2file_parallel_pipelined_192.txt 270536 192
4 rows selected.
Elapsed: 00:00:27.84
The CLOB technique does not scale at all. It takes approximately the same time in serial or parallel mode, meaning that we should probably avoid this technique and use UTL_FILE instead.
tuning pl/sql file i/o
Unloading Oracle data to flat-files is still very common. There are numerous "unloader" utilities on the web for this purpose and there are also many related topics in the Oracle forums. Methods for writing data to flat-files vary, but strangely Oracle has never provided a tool to do this for us. The tools that are provided by Oracle (export, Data Pump, writeable external tables) write data quickly, but in a proprietary format, so for true ASCII flat-files, we have to resort to our own homegrown utilities.
There are several ways to generate flat-files from data stored in Oracle. We can use Perl, C, C++, Pro*C, Java and other languages that interface with Oracle but it is far more common for us to use SQL or PL/SQL to perform this operation. For example, sqlplus can spool data to flat-files very quickly, but it is a command utility and not part of the database. This makes it generally unsuited to being part of a robust database application.
UTL_FILE, on the other hand, is a built-in package around which we can very easily base our unloader utilities. The main issue with UTL_FILE, however, is that it is a relatively slow tool for unloading data, but in this article we will demonstrate some methods for speeding this up. We will see that with simple techniques we can achieve significant performance gains for our data unloads.
setup
We are going to test several versions of a standard data-unload routine. We will run these on an 11g Release 1 database, but all examples are compatible with 10g and most of them with 9i. The data to be dumped to flat-file will be sourced from a single table of 1 million rows, which we will create as follows.SQL> CREATE TABLE source_data
2 ( x, y, z
3 , CONSTRAINT source_data_pk
4 PRIMARY KEY (x,y,z)
5 )
6 ORGANIZATION INDEX
7 AS
8 SELECT ROWNUM AS x
9 , RPAD('x',50,'x') AS y
10 , RPAD('y',50,'y') AS z
11 FROM dual
12 CONNECT BY ROWNUM <= 1000000;
Table created.
We have created an IOT (index-organized table) to enable us to fully cache the data and eliminate any physical I/O. Each test we run will therefore incur roughly the same I/O costs. Using Autotrace, we will run a couple of full scans of this data until it is all in the buffer cache, as follows.SQL> set autotrace traceonly statistics
SQL> SELECT * FROM source_data;
1000000 rows selected.
Statistics
----------------------------------------------------------
88 recursive calls
0 db block gets
17779 consistent gets
15647 physical reads
0 redo size
108077685 bytes sent via SQL*Net to client
16231 bytes received via SQL*Net from client
2001 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1000000 rows processed
SQL> SELECT * FROM source_data;
1000000 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
17668 consistent gets
0 physical reads
0 redo size
108077883 bytes sent via SQL*Net to client
16231 bytes received via SQL*Net from client
2001 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1000000 rows processed
We have reduced the physical I/O as intended, so to complete our setup we will create an Oracle directory below. This is where we will write our flat-files.SQL> CREATE DIRECTORY dump_dir AS '/u01/app/oracle/dir';
Directory created.
baseline utl_file performance
Before we can tune our code, we need to know our baseline performance. In this case, it will be the time it takes to unload 1 million rows to a flat-file using UTL_FILE.PUT_LINE calls, using the following PL/SQL block.SQL> DECLARE
2
3 v_file UTL_FILE.FILE_TYPE;
4 v_name VARCHAR2(128) := 'utl_file_untuned.txt';
5 v_lines PLS_INTEGER := 0;
6
7 BEGIN
8
9 v_file := UTL_FILE.FOPEN('DUMP_DIR',v_name,'W',32767);
10
11 FOR r IN (SELECT x ',' y ',' z AS csv
12 FROM source_data)
13 LOOP
14 UTL_FILE.PUT_LINE(v_file, r.csv);
15 v_lines := v_lines + 1;
16 END LOOP;
17
18 UTL_FILE.FCLOSE(v_file);
19
20 DBMS_OUTPUT.PUT_LINE('File='v_name'; Lines='v_lines);
21
22 END;
23 /
File=utl_file_untuned.txt; Lines=1000000
PL/SQL procedure successfully completed.
Elapsed: 00:01:06.53
Our baseline performance is approximately 66 seconds to write the data to file. Note the following:
Line 11: each record is pre-formatted with a delimiter in the SQL cursor for convenience. A dump utility would usually accept the delimiter as a parameter and concatenate a record piecemeal (especially if using dynamic SQL);
Lines 11-13: we are using an implicit cursor-for-loop. From Oracle 10g Release 1, this will be optimised by the PL/SQL compiler into bulk collects with an array-size of 100 rows. All examples in this article will use either implicit or explicit bulk fetches of the same size. Note that readers on 9i databases will need to convert this and other implicit cursor examples to use explicit array fetches with BULK COLLECT to ensure that all examples are comparable.
tuning (1): buffering to reduce utl_file i/o
Our baseline code has two repetitive operations. First there is an incremented counter which is used to instrument the example. This has a negligible impact overall. More importantly there are 1 million UTL_FILE I/O operations and these have a far more significant impact. Tracing the baseline example with the PL/SQL Profiler and the new PL/SQL Hierarchical Profiler clearly shows that 50% of the elapsed time is accounted for by our 1 million UTL_FILE.PUT_LINE calls.
Our first technique for tuning this, therefore, will be to reduce the number of I/O operations by buffering the output data. This is quite a simple method. We will use a local variable to buffer up to 32K of data before writing it to file, as follows.SQL> DECLARE
2
3 v_file UTL_FILE.FILE_TYPE;
4 v_buffer VARCHAR2(32767);
5 v_name VARCHAR2(128) := 'utl_file_buffered.txt';
6 v_lines PLS_INTEGER := 0;
7 c_eol CONSTANT VARCHAR2(1) := CHR(10);
8 c_eollen CONSTANT PLS_INTEGER := LENGTH(c_eol);
9 c_maxline CONSTANT PLS_INTEGER := 32767;
10
11 BEGIN
12
13 v_file := UTL_FILE.FOPEN('DUMP_DIR',v_name,'W',32767);
14
15 FOR r IN (SELECT x ',' y ',' z AS csv
16 FROM source_data)
17 LOOP
18
19 IF LENGTH(v_buffer) + c_eollen + LENGTH(r.csv) <= c_maxline THEN
20 v_buffer := v_buffer c_eol r.csv;
21 ELSE
22 IF v_buffer IS NOT NULL THEN
23 UTL_FILE.PUT_LINE(v_file, v_buffer);
24 END IF;
25 v_buffer := r.csv;
26 END IF;
27
28 v_lines := v_lines + 1;
29
30 END LOOP;
31
32 UTL_FILE.PUT_LINE(v_file, v_buffer);
33 UTL_FILE.FCLOSE(v_file);
34
35 DBMS_OUTPUT.PUT_LINE('File='v_name'; Lines='v_lines);
36
37 END;
38 /
File=utl_file_buffered.txt; Lines=1000000
PL/SQL procedure successfully completed.
Elapsed: 00:00:30.06
The algorithm (highlighted above) is reasonably self-explanatory, but we will describe it anyway:
Lines 19-20: UTL_FILE has a maximum write-size of 32,767 bytes. If there is enough room in the buffer for a new record to be added (including newline), then we simply append the new data together with a newline;
Lines 21-26: if we can't fit the new record into the buffer, or the buffer is null (in which case the buffer-length calculation will also evaluate to null), then we must either flush it or start it. The buffer will only be null on the very first entry to the loop, so we make it the very last test;
Lines 22-24: when the buffer is too full to accept any new data, we need to write it to file with UTL_FILE;
Line 25: regardless of whether the buffer has been written or hasn't been started it, we reset/start it with the current data record;
Line 32: once the loop is complete, we write the remaining buffered data to the flat-file.
This technique is simple yet extremely effective. We have reduced our elapsed time to less than half the baseline: in other words, this unloading routine is now twice as fast (for this dataset). With an average record size of 105 bytes, we can buffer approximately 310 records, which means we reduce our UTL_FILE calls to roughly 3,200.
Given the effectiveness of the buffering technique, we will continue to use it for the remaining examples in this article.
a note on utl_file buffering
It is possible to replicate this buffering algorithm using the UTL_FILE.PUT, UTL_FILE.NEW_LINE and UTL_FILE.FFLUSH procedures, but a test with our sample data took over 85 seconds, which is considerably slower than our baseline. For this reason, the example is omitted from this article, but is included in the accompanying download.
tuning (2): using temporary clobs
From Oracle 10g onwards, it is possible to write a CLOB to a file with a single call, using the DBMS_XSLPROCESSOR.CLOB2FILE procedure. In the following example, we will prepare a temporary CLOB with our data instead of writing it with UTL_FILE. When all source data has been added to the CLOB, we will write it to a flat-file in a single call.SQL> DECLARE
2
3 v_file CLOB;
4 v_buffer VARCHAR2(32767);
5 v_name VARCHAR2(128) := 'clob2file_buffered.txt';
6 v_lines PLS_INTEGER := 0;
7 v_eol VARCHAR2(2);
8 v_eollen PLS_INTEGER;
9 c_maxline CONSTANT PLS_INTEGER := 32767;
10
11 BEGIN
12
13 v_eol := CASE
14 WHEN DBMS_UTILITY.PORT_STRING LIKE 'IBMPC%'
15 THEN CHR(13)CHR(10)
16 ELSE CHR(10)
17 END;
18 v_eollen := LENGTH(v_eol);
19
20 DBMS_LOB.CREATETEMPORARY(v_file, TRUE);
21
22 FOR r IN (SELECT x ',' y ',' z AS csv
23 FROM source_data)
24 LOOP
25
26 IF LENGTH(v_buffer) + v_eollen + LENGTH(r.csv) <= c_maxline THEN
27 v_buffer := v_buffer v_eol r.csv;
28 ELSE
29 IF v_buffer IS NOT NULL THEN
30 DBMS_LOB.WRITEAPPEND(
31 v_file, LENGTH(v_buffer) + v_eollen, v_buffer v_eol
32 );
33 END IF;
34 v_buffer := r.csv;
35 END IF;
36
37 v_lines := v_lines + 1;
38
39 END LOOP;
40
41 IF LENGTH(v_buffer) > 0 THEN
42 DBMS_LOB.WRITEAPPEND(
43 v_file, LENGTH(v_buffer) + v_eollen, v_buffer v_eol
44 );
45 END IF;
46
47 DBMS_XSLPROCESSOR.CLOB2FILE(v_file, 'DUMP_DIR', v_name);
48 DBMS_LOB.FREETEMPORARY(v_file);
49
50 DBMS_OUTPUT.PUT_LINE('File='v_name'; Lines='v_lines);
51
52 END;
53 /
File=clob2file_buffered.txt; Lines=1000000
PL/SQL procedure successfully completed.
Elapsed: 00:00:28.65
The CLOB-specific code is highlighted above and is self-explanatory (perhaps with the exception of the end-of-line character assignment on lines 13-17 which is different for Windows. UTL_FILE manages the port-specific end-of-line conversions for us, but with CLOBs we must manage this ourselves).
Of particular interest is the DBMS_XSLPROCESSOR call on line 47, which is our only write operation to the destination flat-file. We can see overall that this technique is similar in performance to our buffered UTL_FILE mechanism (the CLOB method was slightly quicker). We therefore have an alternative method for writing data, but there will be additional costs associated with using CLOBs (for example, temporary tablespace and buffer cache). If the volume of data to be dumped is high, then this method might put too much stress on our temporary tablespace and cause problems for other users (large sort operations, hash joins, global temporary tables etc). Care should therefore be taken when using this method.
tuning (3): parallel execution with pipelined functions
Our final technique for quicker data-dumping from PL/SQL will be to split the workload and have the data written in separate streams. We can do this quite simply by using multiple sessions, with each reading and dumping different ranges of source data (Tom Kyte calls this "DIY parallelism"). A simpler (and possibly more elegant) method is to use Oracle's parallel query (PQ) mechanism and create a parallel pipelined function to split the source data and dump it using multiple PQ slaves.
For this test, we will create a parallel pipelined function that writes the source data to flat-file and returns a single summary record per session. This record will include details of the file and the session that wrote it. It is assumed that readers are familiar with the concept of parallel pipelined functions (some background reading is available if required).
required types
We will begin by creating an object type to define the structure of our pipelined function's return data, as follows.SQL> CREATE TYPE dump_ot AS OBJECT
2 ( file_name VARCHAR2(128)
3 , no_records NUMBER
4 , session_id NUMBER
5 );
6 /
Type created.
Pipelined functions return collections of data, so we will create a nested table type based on our new object.SQL> CREATE TYPE dump_ntt AS TABLE OF dump_ot;
2 /
Type created.
We are now ready to create our parallel pipelined function. We will create and test two versions: one with UTL_FILE and one with a CLOB, because there has been little to distinguish them in the timed comparisons so far.
tuning (3a): parallel utl_file solution
We will begin by creating a parallel pipelined function that uses UTL_FILE to write data, as follows.SQL> CREATE FUNCTION parallel_dump (
2 p_source IN SYS_REFCURSOR,
3 p_filename IN VARCHAR2,
4 p_directory IN VARCHAR2
5 ) RETURN dump_ntt
6 PIPELINED
7 PARALLEL_ENABLE (PARTITION p_source BY ANY) AS
8
9 TYPE row_ntt IS TABLE OF VARCHAR2(32767);
10 v_rows row_ntt;
11 v_file UTL_FILE.FILE_TYPE;
12 v_buffer VARCHAR2(32767);
13 v_sid NUMBER;
14 v_name VARCHAR2(128);
15 v_lines PLS_INTEGER := 0;
16 c_eol CONSTANT VARCHAR2(1) := CHR(10);
17 c_eollen CONSTANT PLS_INTEGER := LENGTH(c_eol);
18 c_maxline CONSTANT PLS_INTEGER := 32767;
19
20 BEGIN
21
22 SELECT sid INTO v_sid FROM v$mystat WHERE ROWNUM = 1;
23 v_name := p_filename '_' TO_CHAR(v_sid) '.txt';
24 v_file := UTL_FILE.FOPEN(p_directory, v_name, 'w', 32767);
25
26 LOOP
27 FETCH p_source BULK COLLECT INTO v_rows LIMIT 100;
28
29 FOR i IN 1 .. v_rows.COUNT LOOP
30
31 IF LENGTH(v_buffer) + c_eollen + LENGTH(v_rows(i)) <= c_maxline THEN
32 v_buffer := v_buffer c_eol v_rows(i);
33 ELSE
34 IF v_buffer IS NOT NULL THEN
35 UTL_FILE.PUT_LINE(v_file, v_buffer);
36 END IF;
37 v_buffer := v_rows(i);
38 END IF;
39
40 END LOOP;
41
42 v_lines := v_lines + v_rows.COUNT;
43
44 EXIT WHEN p_source%NOTFOUND;
45 END LOOP;
46 CLOSE p_source;
47
48 UTL_FILE.PUT_LINE(v_file, v_buffer);
49 UTL_FILE.FCLOSE(v_file);
50
51 PIPE ROW (dump_ot(v_name, v_lines, v_sid));
52 RETURN;
53
54 END parallel_dump;
55 /
Function created.
Before we run a timed test using this function, note the following:
Line 7: we parallel-enable the function and state that Oracle can divide the source data amongst the PQ slaves any way it wishes;
Lines 22-23: each PQ slave will write to its own file, so we append the SID to the filename to make each one unique;
Lines 26-27: the source cursor will be passed as a refcursor parameter, so we must explicitly code a bulk fetch of 100 records to emulate the other examples in this article (i.e. those that benefit from 10g PL/SQL optimisation);
Line 51: on completion of the flat-file, we return a single record summarising the file details.
We will execute this function, as follows.SQL> SELECT *
2 FROM TABLE(
3 parallel_dump(
4 CURSOR(SELECT /*+ PARALLEL(s,4) */
5 x ','
6 y ','
7 z AS csv
8 FROM source_data s),
9 'utl_file_parallel_pipelined',
10 'DUMP_DIR'
11 )) nt;
FILE_NAME NO_RECORDS SESSION_ID
-------------------------------------------------- ---------- ----------
utl_file_parallel_pipelined_136.txt 190758 136
utl_file_parallel_pipelined_135.txt 192640 135
utl_file_parallel_pipelined_117.txt 288960 117
utl_file_parallel_pipelined_121.txt 327642 121
4 rows selected.
Elapsed: 00:00:10.26
This is impressive! We have reduced the elapsed time of our write operation from a baseline of 66 seconds to just 10 seconds. Of course, we now have four files instead of one, but we can easily append these files together with a script of some description (Perl, shell etc). Preferably, we would leave them as separate files and simply read them as though they were a single file by using an external table with an appropriate LOCATION setting (i.e. one that includes all four files).
tuning (3b): parallel temporary clob solution
To complete our tuning tests, we will re-create the parallel pipelined function to use the CLOB method (as noted earlier, this requires at least 10g).SQL> CREATE OR REPLACE FUNCTION parallel_dump (
2 p_source IN SYS_REFCURSOR,
3 p_filename IN VARCHAR2,
4 p_directory IN VARCHAR2
5 ) RETURN dump_ntt
6 PIPELINED
7 PARALLEL_ENABLE (PARTITION p_source BY ANY) AS
8
9 TYPE row_ntt IS TABLE OF VARCHAR2(32767);
10 v_rows row_ntt;
11 v_file CLOB;
12 v_buffer VARCHAR2(32767);
13 v_sid NUMBER;
14 v_name VARCHAR2(128);
15 v_lines PLS_INTEGER := 0;
16 v_eol VARCHAR2(2);
17 v_eollen PLS_INTEGER;
18 c_maxline CONSTANT PLS_INTEGER := 32767;
19
20 BEGIN
21
22 v_eol := CASE
23 WHEN DBMS_UTILITY.PORT_STRING LIKE 'IBMPC%'
24 THEN CHR(13)CHR(10)
25 ELSE CHR(10)
26 END;
27 v_eollen := LENGTH(v_eol);
28
29 SELECT sid INTO v_sid FROM v$mystat WHERE ROWNUM = 1;
30 v_name := p_filename '_' TO_CHAR(v_sid) '.txt';
31
32 DBMS_LOB.CREATETEMPORARY(v_file, TRUE);
33
34 LOOP
35 FETCH p_source BULK COLLECT INTO v_rows LIMIT 100;
36
37 FOR i IN 1 .. v_rows.COUNT LOOP
38
39 IF LENGTH(v_buffer) + v_eollen + LENGTH(v_rows(i)) <= c_maxline THEN
40 v_buffer := v_buffer v_eol v_rows(i);
41 ELSE
42 IF v_buffer IS NOT NULL THEN
43 DBMS_LOB.WRITEAPPEND(
44 v_file, LENGTH(v_buffer) + v_eollen, v_buffer v_eol
45 );
46 END IF;
47 v_buffer := v_rows(i);
48 END IF;
49
50 END LOOP;
51
52 v_lines := v_lines + v_rows.COUNT;
53
54 EXIT WHEN p_source%NOTFOUND;
55 END LOOP;
56 CLOSE p_source;
57
58 IF LENGTH(v_buffer) > 0 THEN
59 DBMS_LOB.WRITEAPPEND(
60 v_file, LENGTH(v_buffer) + v_eollen, v_buffer v_eol
61 );
62 END IF;
63
64 DBMS_XSLPROCESSOR.CLOB2FILE(v_file, p_directory, v_name);
65 DBMS_LOB.FREETEMPORARY(v_file);
66
67 PIPE ROW (dump_ot(v_name, v_lines, v_sid));
68 RETURN;
69
70 END parallel_dump;
71 /
Function created.
We have already described the CLOB and parallel pipelined function techniques, so we will proceed with our timing test, as follows.SQL> SELECT *
2 FROM TABLE(
3 parallel_dump(
4 CURSOR(SELECT /*+ PARALLEL(s,4) */
5 x ','
6 y ','
7 z AS csv
8 FROM source_data s),
9 'clob2file_parallel_pipelined',
10 'DUMP_DIR'
11 )) nt;
FILE_NAME NO_RECORDS SESSION_ID
-------------------------------------------------- ---------- ----------
clob2file_parallel_pipelined_200.txt 248504 200
clob2file_parallel_pipelined_196.txt 232768 196
clob2file_parallel_pipelined_198.txt 248192 198
clob2file_parallel_pipelined_192.txt 270536 192
4 rows selected.
Elapsed: 00:00:27.84
The CLOB technique does not scale at all. It takes approximately the same time in serial or parallel mode, meaning that we should probably avoid this technique and use UTL_FILE instead.
Tuesday, June 10, 2008
Imp Oracle PLSQL Interview Questions
I have taken more than 100 interviews for oracle plsql developer position and Below few set of questions i asked in interviews.
RE: What is Pragma EXECPTION_INIT ? Explain...
Pragma exception_init Allow you to handle the Oracle predefined message by you'r own message. means you can instruct compiler toassociatethe specific message to oracle predefined message at compile time.This way you Improve the Readbility of your program,and handle it accoding to your own way.
It should be declare at the DECLARE section.
example
declare
salary number;
FOUND_NOTHING exception;
Pragma exception_init(FOUND_NOTHING ,100);
begin
select sal in to salaryfrom emp where ename ='ANURAG';
dbms_output.put_line(salary);
exception
WHEN FOUND_NOTHING THEN
dbms_output.put_line(SQLERRM);
end;
RE: What is Data Concarency and Consistency?
Concurrency
How well can multiple sessions access the same data simultaneously
Consistency
How consistent is the view of the data between and within multiple sessions, transactions or statements
RE: What is bulk binding please explain me in brief ?
Bulk Binds (BULK COLLECT , FORALL ) are a PL/SQL technique where, instead of multiple individual SELECT, INSERT, UPDATE or DELETE statements are executed to retrieve from, or store data in, at table, all of the operations are carried out at once, in bulk.
This avoids the context-switching you get when the PL/SQL engine has to pass over to the SQL engine, then back to the PL/SQL engine, and so on, when you individually access rows one at a time. To do bulk binds with Insert, Update and Delete statements, you enclose the SQL statement within a PL/SQL FORALL statement.
To do bulk binds with Select statements, you include the Bulk Collect INTO a collection clause in the SELECT Statement instead of using Simply into .
Collections, BULK COLLECT and FORALL are the new features in Oracle 8i, 9i and 10g PL/SQL that can really make a different to you PL/SQL performance
RE: what is p-code and sourcecode ?
P-code is Pre-complied code stored in Public cache memory of System Global Area after the Oracle instance is started, whereas sourcecode is a simple code of sp, package, trigger, functions etc which are stored in Oracle system defined data dictionary. Every session of oracle access the p-code which have the Execute permission on that objects.
Source code stored in user_objects data dictinary for user defined Store proc, Trigger, Package, Function. DBA_object stores all the db objects in sp. DB. ALL_objects stores all the db objects in sp. schema
According to O Reilly Book , the main difference between Index-By Table(pl-Sql Table) , Varray and nested tables are -
Index-By Table
Nested Table
VARRAY
Dimensionality
Single
Single
Single
Usable in SQL?
No
Yes
Yes
Usable as column datatype in a table?
No
Yes; data stored "out of line" (in separate table)
Yes; data stored "in line" (in same table)
Uninitialized state
Empty (cannot be null); elements undefined
Atomically null; illegal to reference elements
Atomically null; illegal to reference elements
Initialization
Automatic, when declared
Via constructor, fetch, assignment
Via constructor, fetch, assignment
In PL/SQL, elements referenced via
BINARY_INTEGER
(-2,147,483,647 .. 2,147,483,647)
Positive integer between 1 and 2,147,483,647
Positive integer between 1 and 2,147,483,647
Sparse?
Yes
Initially, no; after deletions, yes
No
Bounded?
No
Can be extended
Yes
Can assign value to any element at any time?
Yes
No; may need to EXTEND first
No; may need to EXTEND first, and cannot EXTEND past upper bound
Means of extending
Assign value to element with a new subscript
Use built-in EXTEND procedure (or TRIM to condense), with no predefined maximum
EXTEND (or TRIM), but only up to declared maximum size
Can be compared for equality?
No
No
No
Retains ordering and subscripts when stored in and retrieved from database?
N/A
No
Yes
RE: What is autonomous Transaction? Where are they use...
Autonomous transaction is the transaction which acts independantly from the calling part and could commit the process done.
example using prgma autonomous incase of mutation problem happens in a trigger
RE: why do we need to create a force view?what is its ...
It is some times important to create a view even if the base table does not exist and we are planning to create one but not yet decided the structure, in that case one can create force view. This view can not be accessed and it will be in 'invalid' state unless and untill you will create a base table and compile the force view.
RE:Explain materialized views and how they are used.
Materialized views are objects that are reduced sets of information that have been summarized, grouped, or aggregated from base tables. They are typically used in data warehouse or decision support systems.
RE:When a user process fails, what background process cleans up after it?
PMON
RE: How can you enable a trace for a session?
Use the DBMS_SESSION.SET_SQL_TRACE or
Use ALTER SESSION SET SQL_TRACE = TRUE;
Few other interview questions
1.What are the various types of Exceptions ?
User defined and Predefined Exceptions.
2.Can we define exceptions twice in same block ?
No.
3.What is the difference between a procedure and a function ?
Functions return a single variable by value whereas procedures do not return any variable by value. Rather they return multiple variables by passing variables by reference through their OUT parameter.
4.Can you have two functions with the same name in a PL/SQL block ?
Yes.
5.Can you have two stored functions with the same name ?
Yes.
6.Can you call a stored function in the constraint of a table ?
No.
7.What are the various types of parameter modes in a procedure ?
IN, OUT AND INOUT.
8.What is Over Loading and what are its restrictions ?
OverLoading means an object performing different functions depending upon the no. of parameters or the data type of the parameters passed to it.
9.Can functions be overloaded ?
Yes.
10.Can 2 functions have same name & input parameters but differ only by return datatype
No.
11.What are the constructs of a procedure, function or a package ?
The constructs of a procedure, function or a package are :
variables and constants cursors exceptions
12.Why Create or Replace and not Drop and recreate procedures ?
So that Grants are not dropped.
13.Can you pass parameters in packages ? How ?
Yes. You can pass parameters to procedures or functions in a package.
14.What are the parts of a database trigger ?
The parts of a trigger are: A triggering event or statement A trigger restriction A trigger action
15.What are the various types of database triggers ?
There are 12 types of triggers, they are combination of :Insert, Delete and Update Triggers.Before and After Triggers.Row and Statement Triggers.(3*2*2=12)
16.What is the advantage of a stored procedure over a database trigger ?
We have control over the firing of a stored procedure but we have no control over the firing of a trigger.
17.What is the maximum no. of statements that can be specified in a trigger statement ?
One.
18.Can views be specified in a trigger statement ?No
19.What are the values of :new and :old in Insert/Delete/Update Triggers ?
INSERT : new = new value, old = NULLDELETE : new = NULL, old = old valueUPDATE : new = new value, old = old value
20.What are cascading triggers? What is the maximum no of cascading triggers at a time?
When a statement in a trigger body causes another trigger to be fired, the triggers are said to be cascading. Max = 32.
21.What are mutating triggers ?
A trigger giving a SELECT on the table on which the trigger is written.
22.What are constraining triggers ?
A trigger giving an Insert/Updat e on a table having referential integrity constraint on the triggering table.
23.Describe Oracle database's physical and logical structure ?
Physical : Data files, Redo Log files, Control file.Logical : Tables, Views, Tablespaces, etc.
24.Can you increase the size of a tablespace ? How ?
Yes, by adding datafiles to it.
25.Can you increase the size of datafiles ? How ?
No (for Oracle 7.0)Yes (for Oracle 7.3 by using the Resize clause ----- Confirm !!).
26.What is the use of Control files ?
Contains pointers to locations of various data files, redo log files, etc.
27.What is the use of Data Dictionary ?
Used by Oracle to store information about various physical and logical Oracle structures e.g. Tables, Tablespaces, datafiles, etc
28.What are the advantages of clusters ?
Access time reduced for joins.
29.What are the disadvantages of clusters ?
The time for Insert increases.
30.Can Long/Long RAW be clustered ?No.
31.Can null keys be entered in cluster index, normal index ?Yes.
32.Can Check constraint be used for self referential integrity ? How ?
Yes. In the CHECK condition for a column of a table, we can reference some other column of the same table and thus enforce self referential integrity.
33.What are the min. extents allocated to a rollback extent ?
Two
34.What are the states of a rollback segment ? What is the difference between partly available and needs recovery ?
The various states of a rollback segment are :ONLINE, OFFLINE, PARTLY AVAILABLE, NEEDS RECOVERY and INVALID.
35.What is the difference between unique key and primary key ?
Unique key can be null; Primary key cannot be null.
36.An insert statement followed by a create table statement followed by rollback ? Will the rows be inserted ?
No.
37.Can you define multiple savepoints ?
Yes.
38.Can you Rollback to any savepoint ?Yes.
40.What is the maximum no. of columns a table can have ?
254.
41.What is the significance of the & and && operators in PL SQL ?
The & operator means that the PL SQL block requires user input for a variable. The && operator means that the value of this variable should be the same as inputted by the user previously for this same variable.If a transaction is very large, and the rollback segment is not able to hold the rollback information, then will the transaction span across different rollback segments or will it terminate ?It will terminate (Please check ).
42.Can you pass a parameter to a cursor ?Explicit cursors can take parameters, as the example below shows. A cursor parameter can appear in a query wherever a constant can appear. CURSOR c1 (median IN NUMBER) ISSELECT job, ename FROM emp WHERE sal > median;
43.What are the various types of RollBack Segments ?
Public Available to all instancesPrivate Available to specific instance
44.Can you use %RowCount as a parameter to a cursor ?Yes
45.Is the query below allowed :Select sal, ename Into x From emp Where ename = 'KING'(Where x is a record of Number(4) and Char(15))
Yes
RE: What is Pragma EXECPTION_INIT ? Explain...
Pragma exception_init Allow you to handle the Oracle predefined message by you'r own message. means you can instruct compiler toassociatethe specific message to oracle predefined message at compile time.This way you Improve the Readbility of your program,and handle it accoding to your own way.
It should be declare at the DECLARE section.
example
declare
salary number;
FOUND_NOTHING exception;
Pragma exception_init(FOUND_NOTHING ,100);
begin
select sal in to salaryfrom emp where ename ='ANURAG';
dbms_output.put_line(salary);
exception
WHEN FOUND_NOTHING THEN
dbms_output.put_line(SQLERRM);
end;
RE: What is Data Concarency and Consistency?
Concurrency
How well can multiple sessions access the same data simultaneously
Consistency
How consistent is the view of the data between and within multiple sessions, transactions or statements
RE: What is bulk binding please explain me in brief ?
Bulk Binds (BULK COLLECT , FORALL ) are a PL/SQL technique where, instead of multiple individual SELECT, INSERT, UPDATE or DELETE statements are executed to retrieve from, or store data in, at table, all of the operations are carried out at once, in bulk.
This avoids the context-switching you get when the PL/SQL engine has to pass over to the SQL engine, then back to the PL/SQL engine, and so on, when you individually access rows one at a time. To do bulk binds with Insert, Update and Delete statements, you enclose the SQL statement within a PL/SQL FORALL statement.
To do bulk binds with Select statements, you include the Bulk Collect INTO a collection clause in the SELECT Statement instead of using Simply into .
Collections, BULK COLLECT and FORALL are the new features in Oracle 8i, 9i and 10g PL/SQL that can really make a different to you PL/SQL performance
RE: what is p-code and sourcecode ?
P-code is Pre-complied code stored in Public cache memory of System Global Area after the Oracle instance is started, whereas sourcecode is a simple code of sp, package, trigger, functions etc which are stored in Oracle system defined data dictionary. Every session of oracle access the p-code which have the Execute permission on that objects.
Source code stored in user_objects data dictinary for user defined Store proc, Trigger, Package, Function. DBA_object stores all the db objects in sp. DB. ALL_objects stores all the db objects in sp. schema
According to O Reilly Book , the main difference between Index-By Table(pl-Sql Table) , Varray and nested tables are -
Index-By Table
Nested Table
VARRAY
Dimensionality
Single
Single
Single
Usable in SQL?
No
Yes
Yes
Usable as column datatype in a table?
No
Yes; data stored "out of line" (in separate table)
Yes; data stored "in line" (in same table)
Uninitialized state
Empty (cannot be null); elements undefined
Atomically null; illegal to reference elements
Atomically null; illegal to reference elements
Initialization
Automatic, when declared
Via constructor, fetch, assignment
Via constructor, fetch, assignment
In PL/SQL, elements referenced via
BINARY_INTEGER
(-2,147,483,647 .. 2,147,483,647)
Positive integer between 1 and 2,147,483,647
Positive integer between 1 and 2,147,483,647
Sparse?
Yes
Initially, no; after deletions, yes
No
Bounded?
No
Can be extended
Yes
Can assign value to any element at any time?
Yes
No; may need to EXTEND first
No; may need to EXTEND first, and cannot EXTEND past upper bound
Means of extending
Assign value to element with a new subscript
Use built-in EXTEND procedure (or TRIM to condense), with no predefined maximum
EXTEND (or TRIM), but only up to declared maximum size
Can be compared for equality?
No
No
No
Retains ordering and subscripts when stored in and retrieved from database?
N/A
No
Yes
RE: What is autonomous Transaction? Where are they use...
Autonomous transaction is the transaction which acts independantly from the calling part and could commit the process done.
example using prgma autonomous incase of mutation problem happens in a trigger
RE: why do we need to create a force view?what is its ...
It is some times important to create a view even if the base table does not exist and we are planning to create one but not yet decided the structure, in that case one can create force view. This view can not be accessed and it will be in 'invalid' state unless and untill you will create a base table and compile the force view.
RE:Explain materialized views and how they are used.
Materialized views are objects that are reduced sets of information that have been summarized, grouped, or aggregated from base tables. They are typically used in data warehouse or decision support systems.
RE:When a user process fails, what background process cleans up after it?
PMON
RE: How can you enable a trace for a session?
Use the DBMS_SESSION.SET_SQL_TRACE or
Use ALTER SESSION SET SQL_TRACE = TRUE;
Few other interview questions
1.What are the various types of Exceptions ?
User defined and Predefined Exceptions.
2.Can we define exceptions twice in same block ?
No.
3.What is the difference between a procedure and a function ?
Functions return a single variable by value whereas procedures do not return any variable by value. Rather they return multiple variables by passing variables by reference through their OUT parameter.
4.Can you have two functions with the same name in a PL/SQL block ?
Yes.
5.Can you have two stored functions with the same name ?
Yes.
6.Can you call a stored function in the constraint of a table ?
No.
7.What are the various types of parameter modes in a procedure ?
IN, OUT AND INOUT.
8.What is Over Loading and what are its restrictions ?
OverLoading means an object performing different functions depending upon the no. of parameters or the data type of the parameters passed to it.
9.Can functions be overloaded ?
Yes.
10.Can 2 functions have same name & input parameters but differ only by return datatype
No.
11.What are the constructs of a procedure, function or a package ?
The constructs of a procedure, function or a package are :
variables and constants cursors exceptions
12.Why Create or Replace and not Drop and recreate procedures ?
So that Grants are not dropped.
13.Can you pass parameters in packages ? How ?
Yes. You can pass parameters to procedures or functions in a package.
14.What are the parts of a database trigger ?
The parts of a trigger are: A triggering event or statement A trigger restriction A trigger action
15.What are the various types of database triggers ?
There are 12 types of triggers, they are combination of :Insert, Delete and Update Triggers.Before and After Triggers.Row and Statement Triggers.(3*2*2=12)
16.What is the advantage of a stored procedure over a database trigger ?
We have control over the firing of a stored procedure but we have no control over the firing of a trigger.
17.What is the maximum no. of statements that can be specified in a trigger statement ?
One.
18.Can views be specified in a trigger statement ?No
19.What are the values of :new and :old in Insert/Delete/Update Triggers ?
INSERT : new = new value, old = NULLDELETE : new = NULL, old = old valueUPDATE : new = new value, old = old value
20.What are cascading triggers? What is the maximum no of cascading triggers at a time?
When a statement in a trigger body causes another trigger to be fired, the triggers are said to be cascading. Max = 32.
21.What are mutating triggers ?
A trigger giving a SELECT on the table on which the trigger is written.
22.What are constraining triggers ?
A trigger giving an Insert/Updat e on a table having referential integrity constraint on the triggering table.
23.Describe Oracle database's physical and logical structure ?
Physical : Data files, Redo Log files, Control file.Logical : Tables, Views, Tablespaces, etc.
24.Can you increase the size of a tablespace ? How ?
Yes, by adding datafiles to it.
25.Can you increase the size of datafiles ? How ?
No (for Oracle 7.0)Yes (for Oracle 7.3 by using the Resize clause ----- Confirm !!).
26.What is the use of Control files ?
Contains pointers to locations of various data files, redo log files, etc.
27.What is the use of Data Dictionary ?
Used by Oracle to store information about various physical and logical Oracle structures e.g. Tables, Tablespaces, datafiles, etc
28.What are the advantages of clusters ?
Access time reduced for joins.
29.What are the disadvantages of clusters ?
The time for Insert increases.
30.Can Long/Long RAW be clustered ?No.
31.Can null keys be entered in cluster index, normal index ?Yes.
32.Can Check constraint be used for self referential integrity ? How ?
Yes. In the CHECK condition for a column of a table, we can reference some other column of the same table and thus enforce self referential integrity.
33.What are the min. extents allocated to a rollback extent ?
Two
34.What are the states of a rollback segment ? What is the difference between partly available and needs recovery ?
The various states of a rollback segment are :ONLINE, OFFLINE, PARTLY AVAILABLE, NEEDS RECOVERY and INVALID.
35.What is the difference between unique key and primary key ?
Unique key can be null; Primary key cannot be null.
36.An insert statement followed by a create table statement followed by rollback ? Will the rows be inserted ?
No.
37.Can you define multiple savepoints ?
Yes.
38.Can you Rollback to any savepoint ?Yes.
40.What is the maximum no. of columns a table can have ?
254.
41.What is the significance of the & and && operators in PL SQL ?
The & operator means that the PL SQL block requires user input for a variable. The && operator means that the value of this variable should be the same as inputted by the user previously for this same variable.If a transaction is very large, and the rollback segment is not able to hold the rollback information, then will the transaction span across different rollback segments or will it terminate ?It will terminate (Please check ).
42.Can you pass a parameter to a cursor ?Explicit cursors can take parameters, as the example below shows. A cursor parameter can appear in a query wherever a constant can appear. CURSOR c1 (median IN NUMBER) ISSELECT job, ename FROM emp WHERE sal > median;
43.What are the various types of RollBack Segments ?
Public Available to all instancesPrivate Available to specific instance
44.Can you use %RowCount as a parameter to a cursor ?Yes
45.Is the query below allowed :Select sal, ename Into x From emp Where ename = 'KING'(Where x is a record of Number(4) and Char(15))
Yes
Script to Compile All invalid Objects in a Database Schema
Most of the times we face a situation where you see lots of interdependent components would be in invalid state after compiling a new or existing oracle object. Below given script can be used to compile all invalid objects.
set serveroutput on size 1000000
DECLARE CURSOR invalidcurs_1 IS select * from user_objects where status != 'VALID' and object_type not in ('TRIGGER','VIEW');
CURSOR invalidcurs_2 IS select * from user_objects where status != 'VALID' and object_type in ('TRIGGER','VIEW');
sql_str VARCHAR2(100);
ret_code INTEGER;
m_cur INTEGER;
l_owner varchar2(10);
BEGIN
dbms_output.enable(1000000);
l_owner := '&owner';
FOR rec IN invalidcurs_1
LOOP
dbms_ddl.alter_compile( rec.object_type, l_owner, rec.object_name);
dbms_output.put_line( 'Compiled: ' l_owner '.' rpad(rec.object_name, 30) ' => ' rec.object_type);
END LOOP;
FOR rec IN invalidcurs_2
LOOP
sql_str := 'ALTER ' rec.object_type ' ' rec.object_name ' COMPILE';
m_cur := dbms_sql.open_cursor; dbms_sql.parse (m_cur, sql_str, 2);
ret_code := dbms_sql.execute (m_cur);
dbms_sql.close_cursor (m_cur);
dbms_output.put_line( 'Compiled: ' l_owner '.' rpad(rec.object_name, 30) ' => ' rec.object_type);
END LOOP;
EXCEPTION
WHEN others THEN
dbms_output.put_line ('Error: ' SQLERRM);
END;
set serveroutput on size 1000000
DECLARE CURSOR invalidcurs_1 IS select * from user_objects where status != 'VALID' and object_type not in ('TRIGGER','VIEW');
CURSOR invalidcurs_2 IS select * from user_objects where status != 'VALID' and object_type in ('TRIGGER','VIEW');
sql_str VARCHAR2(100);
ret_code INTEGER;
m_cur INTEGER;
l_owner varchar2(10);
BEGIN
dbms_output.enable(1000000);
l_owner := '&owner';
FOR rec IN invalidcurs_1
LOOP
dbms_ddl.alter_compile( rec.object_type, l_owner, rec.object_name);
dbms_output.put_line( 'Compiled: ' l_owner '.' rpad(rec.object_name, 30) ' => ' rec.object_type);
END LOOP;
FOR rec IN invalidcurs_2
LOOP
sql_str := 'ALTER ' rec.object_type ' ' rec.object_name ' COMPILE';
m_cur := dbms_sql.open_cursor; dbms_sql.parse (m_cur, sql_str, 2);
ret_code := dbms_sql.execute (m_cur);
dbms_sql.close_cursor (m_cur);
dbms_output.put_line( 'Compiled: ' l_owner '.' rpad(rec.object_name, 30) ' => ' rec.object_type);
END LOOP;
EXCEPTION
WHEN others THEN
dbms_output.put_line ('Error: ' SQLERRM);
END;
Oracle Coding Standards
Below given few oracle coding standards I have been using with various projects.
1. Header
Script Files
Section Heading
Section Contents
Author
The name of the person who created it
Date Written
The completion date for the intial code release/ file creation
Description
A brief explanation of the task the code performed
Version Number
The Present Version Number
Example: Script file insertscript will contain:
Rem Author : Rhodes G.
Rem Created On : 09/09/2004
Rem Purpose : to populate master data
Rem Version No : v1.0
Triggers
Section Heading
Section Contents
Name
The name of the trigger.
Author
The name of the person who created it
Date Written
The completion date for the intial code release/ file creation
Description
A brief explanation of the task the code performed
Calls
(Optional) A list of procedures and functions by the code.
Updates
A history of code revision listing author, date and revision description
Version Number
The Present Version Number
Example:
/*
Name : tg_bl_updatePolReg
Description : to maintain history
Author : Rhodes G.
Date Written : 09/09/2004
Calls : pr_insert_his
Change Register :
-------------------------------------------------------------------------------------------------------------
Change # Date Author Description Review by
-------------------------------------------------------------------------------------------------------------
9999 99/99/9999 XXXXXXX XXXXXXXXXXXXXXXXXX XXXXXXX
XXXXXXXXXXXXXXXXXX
-------------------------------------------------------------------------------------------------------------
Version Number : v1.0
*/
Procedures and Functions
Section Heading
Section Contents
Name
The name of the trigger.
Description
A brief explanation of the task the code performed
Author
The name of the person who created it
Date Written
The completion date for the intial code release/ file creation
Inputs
List of incoming parameters with a brief description of each
Outputs
List of Output parameters with a brief description of each
Table Accessed
List of tables on which insert, update & delete is performed
Calls Called by
(Optional) A list of procedures and functions by the code.
Change Register
A history of code revision listing author, date and revision description
Version Number
The Present Version Number
Example:
/*
Name : pkg_manual_bills
Description : to generate manual bills
Author : Rhodes G.
Date Written : 09/09/2004
Inputs : policy_no – Policy no for which bills to be generated
Period - Billing Year & month YYYYMM
Outputs : Bill_no - System generated Bill no.
Table Accessed : Insert on –
Update on -
Delete on -
Calls :
Change Register :
-------------------------------------------------------------------------------------------------------------
Change # Date Author Description Review by
-------------------------------------------------------------------------------------------------------------
9999 99/99/9999 XXXXXXX XXXXXXXXXXXXXXXXXX XXXXXXX
XXXXXXXXXXXXXXXXXX
-------------------------------------------------------------------------------------------------------------
Version Number : v1.0
*/
2. Comments
There are a number of locations in PL/SQL code where comments are expected, the following instances,
Before each Loop structure.
Before each Begin….End sub-block.
Before each conditional logic expression (IF THEN).
Before any other logically significant statements.
If a comment is required placed the comment on the line immediately preceding the line of code. Do not append comments to the end of code. If a comment is warranted by the complexity of the code and you have used meaningful identifiers, the comment should be complicated enough that you need to explain the situation using more than one or two words.
For example
Correct way:
--
--If the balance is over the maximum balance allowed,
--Do not allow further charges.
--
IF (n_account_balance > MAX_BALANCE) THEN
…………
…………
Incorrect way:
IF (n_account_balance > MAX_BALANCE) THEN
…………
---No Charges
…………
3. Indentation
Indentation is the most crucial factor affecting the readability of the code. Consistent indentation makes the code appear structured and makes the code more readable by showing the levels of logic with in the code.
These are the guidelines for indentating code:
· Be consistent i.e. use the same number of spaces for indentation throughout your code.
· Don’t use too much or too little indentations. Four spaces are the most effective level of indentation. When the code is long TAB may used as the standard. Less indentation is not very useful, and more wastes spaces. Remember that 80 character is line limit.
· Indent the variable and constant definition in the variable declaration portion of a PL/SQL block
· Indent WHEN statement following an exception handler to the first level of indentation. Indent the statement following the WHEN statement to the second level of indentation.
EXCEPTION
WHEN NO_DATA_FOUND_THEN
The following are the guidelines for indenting that relate directly to stored prodedure and functions
· When invoking a stored procedure or function, begin the indentation of the parameter at the end of the procedure or function name. Place only one parameters per line and align all the parameters in a columnar fashion.
For example,
Pr_cal_billdue_dt (prm_policy_no,
prm_policy_effdt);
· When defining the parameters for stored procedure or function, align the IN, OUT and IN OUT parameter type definitions vertically, leaving space for both keywords even if and only one keyword is used.
PROCEDURE sample_proc (prm_1 IN number,
prm_2 IN OUT number,
prm_3 OUT varchar2);
Line Length
Avoid lines loner than 80 characters, Since they are not handled well by many terminals and tools.
Wrapping Line
When an expression will not fit on a single line, break it according ro these general principals,
· Break after a comma.
· Break after an operator.
· Prefer higher level breaks to lower level break.
· Align the new line with the beginning of the expression at the same level on the previous line.
In the above rules lead to confusing code or to code that’s squished up against the right margin, just intead by a TAB instead.
4. Naming Conventions
Variables
Types
Prefix
Examples
Local variables
mmy_
mmy_policy_no , mmy_policy_renew_no etc
Package variables
cmn_
cmn_policy_no , cmn_policy_renew_no etc
Parameters
prm_
Prm_policy_no , prm_policy_renew_no etc
global variables
glb_
:GLOBAL.glb_policy_no , :GLOBAL.glb_policy_renew_no etc
Objects
Types
Prefix
Examples
Primary key constraint
Pk_
Unique key constraint
Uk_
Foreign key constraint
Fk_
Check constraint
Ck_
Foreign key index
Fki_
Indexes
I_
Views
V_
Sequence
Seq_
Snapshots
Sn_
Tablespace
Ts_
Clusters
Clu_
Using %TYPE and %ROWTYPE
The use of %TYPE is recommended when declaring variables and constants that refer to a column in a table. Declaration using %TYPE is also useful when dependencies are desired between database objects and variables or parameters.
Due to a decrease in readability when using %TYPE, it is essential that meaningful identifier names can be used.
The use of %ROWTYPE is encouraged, providing comments are used to improve readability of the code. Due to the likelihood of changes in database structure, declarations using %ROWTYPE will help streamline the maintenance of existing procedure during the development process.
5. Programming Practices
Codding Conventions
Coding conventions are suggestions to help standardize the structure and coding style of a script or set of script so that you and others can easily read abd ubderstand the code. Using proper coding sonventions result in precise, readable, and unambiguous source code that is consistent with other language conventions and is intuitive as possible.
By adopting proper coding conventions early in the development cycle, you greatly increase the effectiveness of your development staff throughout the product cycle.
Naming
A variable name should be complete as necessary to describe its purpose. For frequently used or long terms, standards abbreviations are recommended to help keep name length reasonable. In general, variable names greater than 32 characters can be difficult to read. When using abbreviations, make sure they are consistent throughout the entire script.
For example, randomly switching between the variable name cnt and count within a script may lead to confusion.
Declarations
One declaration per line is recommended since it encourages commenting.
For example,
mmy_policy_no policy_reg.policy_no%type; --Policy number
mmy_policy_effdt policy_reg.policy_effdt%type; --Eff. Date
Capitalization
An important aspect of how code is presented is capitalization. This section outlines the rules for both partially and fully capitalized identifiers within code,
· All reserve words, built in functions, pre-defined types, Sql keywords should always be presented in upper case.
· The names of all the standard and user-defined exceptions should be listed in full uppercase.
· Below is the list of all the standard exceptions provided by PL/SQL
CURSOR_ALREADY_OPEN
DUP_VAL_ON_INDEX
INVALID_CURSOR
INVALID_NUMBER
LOGON_DENIED
NO_DATA_FOUND
OTHERS
PROGRAM_ERROR
STORAGE_ERROR
TIMEOUT_ON_RESOURCE
TOO_MANY_ROWS
TRANSACTION_BACKED_OUT
VALUE_ERROR
ZERO_DIVIDE
· The names of all the database triggers should be listed in full upper case.
· All user-defined and other datatypes will be listed in lower case.
· The names of all the tables, views, snapshots, synonyms will be listed in lowercase
All text that is not covered by these rules will be presented in lower case.
6. Errors/ Exceptions Handling
Errors and exeptions should be handled at every level of the application.
This control should be incorporated in the following components,
· Scripts
· Functions
· Procedures
· Triggers
a. In any database application. DML operations such as SELECT, UPDATE, INSERT, DELETE return an error code if they fail due to violation of constraints.
b. The constraints can be Foreign key violation, Primary key violation, Column size mismatch, datatype mismatch, etc.
c. Typically, these error codes or the text populated by the system do not make sense to the end user. The error handling system should intelligently interpret the error code or the text associated with it and return a clear and simple error message to the end user.
In case of error, the error trapping module should preferably identify the error and populate the best suited context sensitive message such as “policy no does not exist” in absence of policy number while reference through a foreign key.
Make it a practice to have EXCEPTION, in all the blocks even if exceptions / errors have no significance.
In cases where exception need not be handled, use WHEN_OTHERS. Ensure that not even a single DML is left without provision for exception.
e.g.
SELECT policy_effdt
FROM policy_reg INTO mmy_polieffdt
WHERE policy_no = ‘VG01’;
EXCEPTION
WHEN NO_DATA_FOUND then
Following are the guidelines for EXCEPTION handling.
1. Every subroutine (Procedure / Function / Trigger) must have WHEN OTHERS handler at the end.This will take care of any error
2. Every procedure/ functions must have two parameters i.e. prm_success_failure and prm_error_message. If a Logical error occurs (Due to Missing information or business rue violation), then set the prm_success_failure to '99. The error message should be driven from the Error master table. Do not hardcode the error message in the subroutine rather pick the error description from the error master table using the error code
3. For any program error due to unhanded exceptions, which are trapped in the Final block of the routine using WHEN OTHERS, set the prm_success_failure to '99', Also track the proper type of error using sqlcode and sqlerrm.
4. Log each and every error in the Error log table. Use the Diag package to log the error. Use the diag.ins_error_log and diag.ins_edit_log to log the errors. Avoid using the diag.pr for error logging.
5. Always log the EL_SOURCE and EL_ADDITIONAL_REFERENCE while logging any error in the error log. The el_source should be the Name of the Package. And el_additional_reference should be the name of the procedure/function inside the package. In case, it's a standalone procedure/Function, keep the el_source and el_additional_reference same.
6. If the error is in the batch object, while logging the error in error log ensure that you populate the EL_BATCH_EXEC_NO column of the error log with the be_sequence.
1. Header
Script Files
Section Heading
Section Contents
Author
The name of the person who created it
Date Written
The completion date for the intial code release/ file creation
Description
A brief explanation of the task the code performed
Version Number
The Present Version Number
Example: Script file insertscript will contain:
Rem Author : Rhodes G.
Rem Created On : 09/09/2004
Rem Purpose : to populate master data
Rem Version No : v1.0
Triggers
Section Heading
Section Contents
Name
The name of the trigger.
Author
The name of the person who created it
Date Written
The completion date for the intial code release/ file creation
Description
A brief explanation of the task the code performed
Calls
(Optional) A list of procedures and functions by the code.
Updates
A history of code revision listing author, date and revision description
Version Number
The Present Version Number
Example:
/*
Name : tg_bl_updatePolReg
Description : to maintain history
Author : Rhodes G.
Date Written : 09/09/2004
Calls : pr_insert_his
Change Register :
-------------------------------------------------------------------------------------------------------------
Change # Date Author Description Review by
-------------------------------------------------------------------------------------------------------------
9999 99/99/9999 XXXXXXX XXXXXXXXXXXXXXXXXX XXXXXXX
XXXXXXXXXXXXXXXXXX
-------------------------------------------------------------------------------------------------------------
Version Number : v1.0
*/
Procedures and Functions
Section Heading
Section Contents
Name
The name of the trigger.
Description
A brief explanation of the task the code performed
Author
The name of the person who created it
Date Written
The completion date for the intial code release/ file creation
Inputs
List of incoming parameters with a brief description of each
Outputs
List of Output parameters with a brief description of each
Table Accessed
List of tables on which insert, update & delete is performed
Calls Called by
(Optional) A list of procedures and functions by the code.
Change Register
A history of code revision listing author, date and revision description
Version Number
The Present Version Number
Example:
/*
Name : pkg_manual_bills
Description : to generate manual bills
Author : Rhodes G.
Date Written : 09/09/2004
Inputs : policy_no – Policy no for which bills to be generated
Period - Billing Year & month YYYYMM
Outputs : Bill_no - System generated Bill no.
Table Accessed : Insert on –
Update on -
Delete on -
Calls :
Change Register :
-------------------------------------------------------------------------------------------------------------
Change # Date Author Description Review by
-------------------------------------------------------------------------------------------------------------
9999 99/99/9999 XXXXXXX XXXXXXXXXXXXXXXXXX XXXXXXX
XXXXXXXXXXXXXXXXXX
-------------------------------------------------------------------------------------------------------------
Version Number : v1.0
*/
2. Comments
There are a number of locations in PL/SQL code where comments are expected, the following instances,
Before each Loop structure.
Before each Begin….End sub-block.
Before each conditional logic expression (IF
Before any other logically significant statements.
If a comment is required placed the comment on the line immediately preceding the line of code. Do not append comments to the end of code. If a comment is warranted by the complexity of the code and you have used meaningful identifiers, the comment should be complicated enough that you need to explain the situation using more than one or two words.
For example
Correct way:
--
--If the balance is over the maximum balance allowed,
--Do not allow further charges.
--
IF (n_account_balance > MAX_BALANCE) THEN
…………
…………
Incorrect way:
IF (n_account_balance > MAX_BALANCE) THEN
…………
…………
3. Indentation
Indentation is the most crucial factor affecting the readability of the code. Consistent indentation makes the code appear structured and makes the code more readable by showing the levels of logic with in the code.
These are the guidelines for indentating code:
· Be consistent i.e. use the same number of spaces for indentation throughout your code.
· Don’t use too much or too little indentations. Four spaces are the most effective level of indentation. When the code is long TAB may used as the standard. Less indentation is not very useful, and more wastes spaces. Remember that 80 character is line limit.
· Indent the variable and constant definition in the variable declaration portion of a PL/SQL block
· Indent WHEN statement following an exception handler to the first level of indentation. Indent the statement following the WHEN statement to the second level of indentation.
EXCEPTION
WHEN NO_DATA_FOUND_THEN
The following are the guidelines for indenting that relate directly to stored prodedure and functions
· When invoking a stored procedure or function, begin the indentation of the parameter at the end of the procedure or function name. Place only one parameters per line and align all the parameters in a columnar fashion.
For example,
Pr_cal_billdue_dt (prm_policy_no,
prm_policy_effdt);
· When defining the parameters for stored procedure or function, align the IN, OUT and IN OUT parameter type definitions vertically, leaving space for both keywords even if and only one keyword is used.
PROCEDURE sample_proc (prm_1 IN number,
prm_2 IN OUT number,
prm_3 OUT varchar2);
Line Length
Avoid lines loner than 80 characters, Since they are not handled well by many terminals and tools.
Wrapping Line
When an expression will not fit on a single line, break it according ro these general principals,
· Break after a comma.
· Break after an operator.
· Prefer higher level breaks to lower level break.
· Align the new line with the beginning of the expression at the same level on the previous line.
In the above rules lead to confusing code or to code that’s squished up against the right margin, just intead by a TAB instead.
4. Naming Conventions
Variables
Types
Prefix
Examples
Local variables
mmy_
mmy_policy_no , mmy_policy_renew_no etc
Package variables
cmn_
cmn_policy_no , cmn_policy_renew_no etc
Parameters
prm_
Prm_policy_no , prm_policy_renew_no etc
global variables
glb_
:GLOBAL.glb_policy_no , :GLOBAL.glb_policy_renew_no etc
Objects
Types
Prefix
Examples
Primary key constraint
Pk_
Unique key constraint
Uk_
Foreign key constraint
Fk_
Check constraint
Ck_
Foreign key index
Fki_
Indexes
I_
Views
V_
Sequence
Seq_
Snapshots
Sn_
Tablespace
Ts_
Clusters
Clu_
Using %TYPE and %ROWTYPE
The use of %TYPE is recommended when declaring variables and constants that refer to a column in a table. Declaration using %TYPE is also useful when dependencies are desired between database objects and variables or parameters.
Due to a decrease in readability when using %TYPE, it is essential that meaningful identifier names can be used.
The use of %ROWTYPE is encouraged, providing comments are used to improve readability of the code. Due to the likelihood of changes in database structure, declarations using %ROWTYPE will help streamline the maintenance of existing procedure during the development process.
5. Programming Practices
Codding Conventions
Coding conventions are suggestions to help standardize the structure and coding style of a script or set of script so that you and others can easily read abd ubderstand the code. Using proper coding sonventions result in precise, readable, and unambiguous source code that is consistent with other language conventions and is intuitive as possible.
By adopting proper coding conventions early in the development cycle, you greatly increase the effectiveness of your development staff throughout the product cycle.
Naming
A variable name should be complete as necessary to describe its purpose. For frequently used or long terms, standards abbreviations are recommended to help keep name length reasonable. In general, variable names greater than 32 characters can be difficult to read. When using abbreviations, make sure they are consistent throughout the entire script.
For example, randomly switching between the variable name cnt and count within a script may lead to confusion.
Declarations
One declaration per line is recommended since it encourages commenting.
For example,
mmy_policy_no policy_reg.policy_no%type; --Policy number
mmy_policy_effdt policy_reg.policy_effdt%type; --Eff. Date
Capitalization
An important aspect of how code is presented is capitalization. This section outlines the rules for both partially and fully capitalized identifiers within code,
· All reserve words, built in functions, pre-defined types, Sql keywords should always be presented in upper case.
· The names of all the standard and user-defined exceptions should be listed in full uppercase.
· Below is the list of all the standard exceptions provided by PL/SQL
CURSOR_ALREADY_OPEN
DUP_VAL_ON_INDEX
INVALID_CURSOR
INVALID_NUMBER
LOGON_DENIED
NO_DATA_FOUND
OTHERS
PROGRAM_ERROR
STORAGE_ERROR
TIMEOUT_ON_RESOURCE
TOO_MANY_ROWS
TRANSACTION_BACKED_OUT
VALUE_ERROR
ZERO_DIVIDE
· The names of all the database triggers should be listed in full upper case.
· All user-defined and other datatypes will be listed in lower case.
· The names of all the tables, views, snapshots, synonyms will be listed in lowercase
All text that is not covered by these rules will be presented in lower case.
6. Errors/ Exceptions Handling
Errors and exeptions should be handled at every level of the application.
This control should be incorporated in the following components,
· Scripts
· Functions
· Procedures
· Triggers
a. In any database application. DML operations such as SELECT, UPDATE, INSERT, DELETE return an error code if they fail due to violation of constraints.
b. The constraints can be Foreign key violation, Primary key violation, Column size mismatch, datatype mismatch, etc.
c. Typically, these error codes or the text populated by the system do not make sense to the end user. The error handling system should intelligently interpret the error code or the text associated with it and return a clear and simple error message to the end user.
In case of error, the error trapping module should preferably identify the error and populate the best suited context sensitive message such as “policy no does not exist” in absence of policy number while reference through a foreign key.
Make it a practice to have EXCEPTION, in all the blocks even if exceptions / errors have no significance.
In cases where exception need not be handled, use WHEN_OTHERS. Ensure that not even a single DML is left without provision for exception.
e.g.
SELECT policy_effdt
FROM policy_reg INTO mmy_polieffdt
WHERE policy_no = ‘VG01’;
EXCEPTION
WHEN NO_DATA_FOUND then
Following are the guidelines for EXCEPTION handling.
1. Every subroutine (Procedure / Function / Trigger) must have WHEN OTHERS handler at the end.This will take care of any error
2. Every procedure/ functions must have two parameters i.e. prm_success_failure and prm_error_message. If a Logical error occurs (Due to Missing information or business rue violation), then set the prm_success_failure to '99. The error message should be driven from the Error master table. Do not hardcode the error message in the subroutine rather pick the error description from the error master table using the error code
3. For any program error due to unhanded exceptions, which are trapped in the Final block of the routine using WHEN OTHERS, set the prm_success_failure to '99', Also track the proper type of error using sqlcode and sqlerrm.
4. Log each and every error in the Error log table. Use the Diag package to log the error. Use the diag.ins_error_log and diag.ins_edit_log to log the errors. Avoid using the diag.pr for error logging.
5. Always log the EL_SOURCE and EL_ADDITIONAL_REFERENCE while logging any error in the error log. The el_source should be the Name of the Package. And el_additional_reference should be the name of the procedure/function inside the package. In case, it's a standalone procedure/Function, keep the el_source and el_additional_reference same.
6. If the error is in the batch object, while logging the error in error log ensure that you populate the EL_BATCH_EXEC_NO column of the error log with the be_sequence.
Subscribe to:
Posts (Atom)
