Amar Kumar Padhi

On occasion, it may be necessary to implement business logic where, when one routine or business process is being run by a user, certain other routine(s) should not be allowed to run and vice versa. This requirement may be a functional or a technical need. You may be able to relate to the following scenarios: […]

Is your data at risk? Users with valid database username/password combos can log into a database from SQL*Plus and view or edit critical data not accessible from an application itself. The question is, how do you prevent users from accessing data via SQL*Plus? Learn how to insert restrictions in the PRODUCT_USER_PROFILE (PUP) table, owned by […]

We have various levels of security (inside and outside of Oracle database) that can be implemented according to one’s requirements. Mentioned here is a way of implementing security against structural changes or Data Definition Language (DDL) changes. This security is put within the database for logical objects. What it means for the DBA DDL commands […]

In the final installment of this series, we will cover the basics of using Stored Outlines and Oracle Application specific information. 14. Stored Outlines Oracle provides the option of changing the execution plan for running queries, without changing the code itself. This feature also allows us to insulate our application execution plans against any structural […]

Previous installments of this series have covered how the Cost Based Optimizer works and behaves. We will now look at how to assist the optimizer to do its job properly. 11. Hints Hints are exactly what it means–clues or directives that will assist the optimizer in choosing an execution plan. Developers know more about the […]

We covered the various methods of generating statistics in the last installment; moving forward, let’s look at the data skewness problem and the use of the Monitoring option for updating statistics on the fly. 10. Histograms Histogram tells the optimizer how the data is distributed for a column. This information is used in determining the […]

9. Generating Statistics In Part 1 of this series, we discussed the basics of statistics. This month we’ll explore statistics in-depth, learning how well we can maintain them. There are two ways of gathering statistics, COMPUTE and ESTIMATE. COMPUTE results in 100% statistics generation and is accurate. The drawback is that it needs ample time […]

Part 3 of Oracle Optimizer: Moving to and working with CBO discusses the setup changes that should be considered for migrating to CBO. 8. Setup changes for migrating to CBO This article highlights a number of key points to consider when moving to CBO. In addition, it highlights a number of good maintenance practices. Tuning […]

Part 2 of this series covers the Initialization parameters and Hidden or Internal Oracle parameters that influence the Optimizer in choosing execution plans. It is very important to setup these parameters properly. 6. Initialization parameters that affect the Optimizer Apart from generating statistics, the setup of the below mentioned parameters play a very crucial role […]

If your application was developed in earlier releases of Oracle (v. 7 and earlier), chances are that your database is running in Rule based optimizer. This article will help you understand the Oracle Optimizer and various efficient ways of moving to Cost based Optimizer. This is Part 1 of a five part series. Part 1 […]

Locally Managed Tablespace (LMT) is one of the key features in Oracle database. These have been made available since Oracle 8i. It is worth using LMTs considering the benefits in doing so. I have put forward some scenarios that may be worth noting, for systems that are already using LMTs or planning to shift to […]

An application can always be fine-tuned for better performance with the use of better alternatives or with the new features introduced with every release of Oracle. Simply inspecting the code can bring out the bottlenecks eating up your processing time. Using explain plan to fine tune the SQL statements resolves issues most of the time. […]