Check out Oracle Whitepapers.com - http://www.oraclewhitepapers.com
This site is a collection of white papers submitted to Oracle Corporation. The site, hosted by Oracle, provides documentation on everything from industry trend analysis to application integration. The best part about it is the majority of the documents are submitted by companies and individuals other than Oracle. There are some very invaluable research documents on the site. Particularity helpful to senior managers and executives.
Monday, October 15, 2007
Saturday, October 13, 2007
Oracle Integration Repository
Yet another invaluable resource...the Oracle Integration Repository. For implementors, this site provides the common API's available to integrate with Oracle applications. This site has everything from e-business open interface tables to available XML services and formats.
Check it out - http://irep.oracle.com/index.html
Check it out - http://irep.oracle.com/index.html
Saturday, October 06, 2007
Documenting Oracle Applications Customizations with applcust.txt
Occasionally we find reason to customize Oracle Applications. While Oracle strongly discourages this, they agree in certain cases it is necessary. There is a file (applcust.txt) located in $APPL_TOP/admin that allows you to list all customizations you have made to the applications.
Oracle Applications uses applcust.txt during the patching process to notify you that your customizations may be overwritten. In the file, you can list the original file name and various other comments related to the changes made.
Note: As a best practice, make a copy of the standard code and place the modified version in your custom top. You may also be able to achieve your desired functionality with personalizations or custom extensions (which are supported by Oracle Corporation.)
Oracle Applications uses applcust.txt during the patching process to notify you that your customizations may be overwritten. In the file, you can list the original file name and various other comments related to the changes made.
Note: As a best practice, make a copy of the standard code and place the modified version in your custom top. You may also be able to achieve your desired functionality with personalizations or custom extensions (which are supported by Oracle Corporation.)
Wednesday, October 03, 2007
Oracle Applications 11i Vision Instances
Solution Beacon is hosting Vision instances for Oracle Applications versions 11.5.9, 11.5.10, and 11.5.10.2 as well as R12. I have a earlier post on this blog regarding access to R12. Click the link below to gain access to the 11i instances.
Note: You must have an Oracle CSI number to gain access to the applications.
Solution Beacon Oracle Applications 11i Vision Instances
Note: You must have an Oracle CSI number to gain access to the applications.
Solution Beacon Oracle Applications 11i Vision Instances
Patrick Gresham’s Top 10 Oracle PL/SQL Performance Tips
1. Avoid NOT EQUAL Operators ‘<>’ and ‘!=”
When these operators are used, the indexes on columns referenced cannot be used. If you need to use the ‘<>’ operator, you can alternatively use the OR clause to distinguish the <> conditions.
2. Avoid ‘IS NULL’ and ‘IS NOT NULL’
The value NULL is undefined, therefore an index on the referenced column will not be used. Creating defaults for NULL values as part of table creation can help with this issue. (i.e.. column VARCHAR2(1) NOT NULL DEFAULT ‘N”)
3. Avoid FUNCTIONS in SQL Where Clause
The optimizer will not use an index when a function is used on an indexed column.
4. Comparing datatypes that are not similar
The Oracle database converts a VARCHAR2 column to a numeric when comparing a string to a number. When this happens, the referenced index is not used. (i.e account_number = 100100).
5. Use the EXISTS function when possible
Utilize the EXISTS function rather than the IN function. The EXIST function looks for a single row match. The IN function has to return all qualifying rows.
6. Use BIND variables
With the use of bind variables, SQL statements can be reused in memory rather than having to be re-parsed with each call.
7. Where are you referencing form fields?
Avoid referencing form fields within the body of a trigger or program unit. Developer 2000 Forms has a PL/SQL engine independent of the database. Occasionally, the engine has to pass SQL off to the database for parsing. To minimize the communication between the client and server, pass form fields via an argument list.
8. Utilize the FORALL statement rather than a FOR LOOP.
When inserting/updating/deleting a large number of rows, you can do the update collectively, rather then one record at a time. This is quicker because only one context switch has to occur between PL/SQL and SQL.
9. Utilize the BULK COLLECT statement
BULK COLLECT works similar to a FORALL statement. It will fetch all rows from the database rather than one row at a time.
10. Using NOCOPY
The PL/SQL engine will pass the parameter by reference rather than by value. This means the values do not have to be copied back and forth. The greatest advantage is seen when passing large records or collections. Can only be used with OUT or IN OUT parameters.
References:
Richard J. Niemiec, Oracle Performance Tuning, Osborne/McGraw-Hill Book Co., 1999
Scott Urman, Oracle8i Advanced PL/SQL Programming, Osborne/McGraw-Hill Book Co., 2000
When these operators are used, the indexes on columns referenced cannot be used. If you need to use the ‘<>’ operator, you can alternatively use the OR clause to distinguish the <> conditions.
2. Avoid ‘IS NULL’ and ‘IS NOT NULL’
The value NULL is undefined, therefore an index on the referenced column will not be used. Creating defaults for NULL values as part of table creation can help with this issue. (i.e.. column VARCHAR2(1) NOT NULL DEFAULT ‘N”)
3. Avoid FUNCTIONS in SQL Where Clause
The optimizer will not use an index when a function is used on an indexed column.
4. Comparing datatypes that are not similar
The Oracle database converts a VARCHAR2 column to a numeric when comparing a string to a number. When this happens, the referenced index is not used. (i.e account_number = 100100).
5. Use the EXISTS function when possible
Utilize the EXISTS function rather than the IN function. The EXIST function looks for a single row match. The IN function has to return all qualifying rows.
6. Use BIND variables
With the use of bind variables, SQL statements can be reused in memory rather than having to be re-parsed with each call.
7. Where are you referencing form fields?
Avoid referencing form fields within the body of a trigger or program unit. Developer 2000 Forms has a PL/SQL engine independent of the database. Occasionally, the engine has to pass SQL off to the database for parsing. To minimize the communication between the client and server, pass form fields via an argument list.
8. Utilize the FORALL statement rather than a FOR LOOP.
When inserting/updating/deleting a large number of rows, you can do the update collectively, rather then one record at a time. This is quicker because only one context switch has to occur between PL/SQL and SQL.
9. Utilize the BULK COLLECT statement
BULK COLLECT works similar to a FORALL statement. It will fetch all rows from the database rather than one row at a time.
10. Using NOCOPY
The PL/SQL engine will pass the parameter by reference rather than by value. This means the values do not have to be copied back and forth. The greatest advantage is seen when passing large records or collections. Can only be used with OUT or IN OUT parameters.
References:
Richard J. Niemiec, Oracle Performance Tuning, Osborne/McGraw-Hill Book Co., 1999
Scott Urman, Oracle8i Advanced PL/SQL Programming, Osborne/McGraw-Hill Book Co., 2000
Subscribe to:
Posts (Atom)
Posts
