Oracle 10g Cost Based Optimizer

Hello Friends, here i like to give some brief on Cost based optimizer in Oracle, i am not covering Rule based optimizer as it is not much in use as well not recommended. plz share ur feedback or if u like to contribute more on this topic,

Oracle 10g Cost Based Optimizer

Whenever you execute a SQL statement, a component of the database known as the optimizer must decide how best to access the data operated on by that statement. To figure out the optimal execution path for a statement, the optimizers consider the following:

• The syntax you’ve specified for the statement
• Any conditions that the data must satisfy (the WHERE clauses)
• The database tables your statement will need to access
• All possible indexes that can be used in retrieving data from the table
• The Oracle RDBMS version
• The current optimizer mode
• SQL statement hints
• All available object statistics (generated via the ANALYZE command)
• The physical table location (distributed SQL)
• INIT.ORA settings (parallel query, async I/O, etc.)

Understanding the Cost-Based Optimizer

The information required by the cost-based optimizer is available once a table has been analyzed via the ANALYZE command, or via the DBMS_STATS facility. Plz note The ANALYZE command and the DBMS_STATS functions collect statistics about tables, clusters, and indexes, and store those statistics in the data dictionary.

The RDBMS kernel defaults to using the cost-based optimizer under a number of situations, including the following:

• OPTIMIZER_MODE = CHOOSE has been specified in the INIT.ORA file, and statistics exist for at least one table involved in the statement
• An ALTER SESSION SET OPTIMIZER_MODE = CHOOSE command has been executed, and statistics exist for at least one table involved in the statement
• An ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS (or ALL_ROWS) command has been executed, and statistics exist for at least one table involved in the statement
• A statement uses the FIRST_ROWS or ALL_ROWS hint (e.g., SELECT /*+ FIRST_ROWS */. . .)

ANALYZE command

The way that you analyze your tables can have a dramatic effect on your SQL performance. If your DBA forgets to analyze tables or indexes after a table re-build, the impact on performance can be devastating. If your DBA analyzes each weekend, a new threshold may be reached and Oracle may change its execution plan. The new plan will more often than not be an improvement, but will occasionally be worse.

If you do want to analyze frequently, use DBMS_STATS.EXPORT_SCHEMA_STATS to back up the existing statistics prior to re-analyzing. This gives you the ability to revert back to the previous statistics if things screw up. When you analyze, you can have Oracle look at all rows in a table (ANALYZE COMPUTE) or at a sampling of rows (ANALYZE ESTIMATE). Typically, When a table is analyzed using ANALYZE, all associated indexes are analyzed as well. If an index is subsequently dropped and recreated, it must be re-analyzed. Be aware that the procedures DBMS_STATS.GATHER_SCHEMA_STATS and GATHER_TABLE_STATS analyze only tables by default, not their indexes. When using those procedures, you must specify the CASCADE=>TRUE option for indexes to be analyzed as well.

Following are some sample ANALYZE statements:

ANALYZE TABLE EMP ESTIMATE STATISTICS SAMPLE 5 PERCENT FOR ALL INDEXED COLUMNS;

ANALYZE INDEX EMP_NDX1 ESTIMATE STATISTICS SAMPLE 5 PERCENT FOR ALL INDEXED COLUMNS; ANALYZE TABLE EMP COMPUTE STATISTICS FOR ALL INDEXED COLUMNS;

If you analyze a table by mistake, you can delete the statistics. For example:

ANALYZE TABLE EMP DELETE STATISTICS;

Analyzing can take an excessive amount of time if you use the COMPUTE option on large objects. We find that on almost every occasion, ANALYZE ESTIMATE 5 PERCENT on a large table forces the optimizer make the same decision as ANALYZE COMPUTE.

Inner workings of the cost-based optimizer

The cost-based optimizer is flexible and can adapt to its environment. This adaptation is possible only once the necessary underlying object statistics have been refreshed (re-analyzed). What is constant is the method by which the cost-based optimizer calculates each possible execution plan and evaluates its cost (efficiency).

The cost-based optimizer’s functionality can be (loosely) broken into the following steps:

1.       Parse the SQL (check syntax, object privileges, etc.).

2.       Generate a list of all potential execution plans.

3.       Calculate (estimate) the cost of each execution plan using all available object statistics.

4.       Select the execution plan with the lowest cost.

The cost-based optimizer will be used only if at least one table within a SQL statement has statistics (table statistics for unanalyzed tables are estimated). If no statistics are available for any table involved in the SQL, the RDBMS will resort to the rule-based optimizer, unless the cost-based optimizer is forced via statement-level HINTS or by an optimizer goal of ALL_ROWS or FIRST_ROWS.

To understand how the cost-based optimizer works and, ultimately, how to exploit it, we need to understand how it thinks.

Primary key and/or UNIQUE index equality —A UNIQUE index’s selectivity is recognized as 100%. No other indexed access method is more precise. For this reason, a unique index is always used when available.

 Non-UNIQUE index equality —For non-UNIQUE indexes, index selectivity is calculated. The cost-based optimizer makes the assumption that the table (and subsequent indexes) have uniform data spread unless you use the FOR ALL INDEXED COLUMNS option of the ANALYZE. That option will make the cost-based optimizer aware of how the data in the indexed columns is skewed.

 Range evaluation —For index range execution plans, selectivity is evaluated. This evaluation is based on a column’s most recent high-value and low-value statistics. Again, the cost-based optimizer makes the assumption that the table (and subsequent indexes) have uniform data spread unless you use the FOR ALL INDEXED COLUMNS option when analyzing the table.

 Range evaluation over bind variables—For index range execution plans, selectivity is guessed. Prior to Oracle9i, because bind variable values are not available at parse time (values are passed to the cursor after the execution plan has been decided), the optimizer cannot make decisions based on bind variable values. The optimizer assumes a rule of thumb of 25% selectivity for unbounded bind variable ranges (e.g., WHERE dept_no = :b1) and 50% selectivity for bounded ranges (WHERE dept_no > :b1 AND dept_no < :b2). Beginning with Oracle9i, the cost-based optimizer obtains bind variable values prior to determining an execution plan.

 System resource usage —By default, the cost-based optimizer assumes that you are the only person accessing the database. Oracle9i gives you the ability to store information about system resource usage, and can make much better informed decisions based on workload (read up on the DBMS_STATS.GATHER_SYSTEM_STATS package).

 Current statistics are important —The cost-based optimizer can make poor execution plan choices when a table has been analyzed but its indexes have not been, or when indexes have been analyzed but not the tables.

Happy Diwali – Enjoy…

To All Friends & Readers,

We are taking vacations and will be back again after diwali. Wishing everyone a great diwali and prosperous happy new year. Let’s take some break and enjoy. We will continue our knowledge journey

KnowOracle Team wishing you a Happy Diwali…

How to be a Programmer: A Short, Comprehensive, and Personal Summary

Few days’ back I stumble up on this very good essay titled: “How to be a Programmer: A Short, Comprehensive, and Personal Summary” by Robert L Read. The essay is must read for all those whoever are part of the “Software Programmer” tribe. It’s a comprehensive essay detailing various facets of professional life of Software engineer.

The essay deals with range of topics starting from basic debugging skills to managing projects, conceiving good design and to the extent of how to manage the people, team dynamics and personal aspects like “When to go home” etc.

The author has been very concise and focused while explaining all these topics. The best part of the article is that it’s very generic and the suggestion/recommendation holds for a person working as any role in software development with any sort of technology.

Another good part of the essay is the manner in which it is structured. All the skills required in order to be a successful programmer are grouped into three sections namely: “Beginner”, “Intermediate” and “Advanced”. The “Beginner” section talks about things like debugging, performance tuning, fundamental concepts like memory and i/o management, testing, experimenting, team skills, working with poor code, source code control etc. All are damn interesting and must read.

“Intermediate” section covers some of the soft skills, which are of paramount importance. Things like Personal skills, how to stay motivated, how to grow professionally, how to deal with non-engineer etc. and technical things like managing development time, evaluating and managing third party software, when to apply fancy computer science. Finally the “Advanced” section talks about how to make technology judgment, using embedded languages, dealing with schedule pressure, growing a system, dealing with organization chaos etc.

So all and all very interesting read.

Shivmohan – Oracle 11i Multi Org – Definition

What is the MultiOrg and what is it used for?

MultiOrg or Multiple Organizations Architecture allows multiple operating units and their relationships to be defined within a single installation of Oracle Applications. This keeps each operating unit’s transaction data separate and secure. Further all organizations can share some master data like supplier, customer, bank, AFF, payment terms, price lists etc.

Use the following query to determine if MuliOrg is intalled:

select multi_org_flag from fnd_product_groups;

 

Thanks – Shivmohan Purohit

Oracle Application – A Brief Introduction

Oracle Financials, part of Oracle E-Business Suite, helps financial officers create an information-driven enterprise that synchronizes data centrally from all systems-including third-party systems-so financial information is consistently collected, calculated, analyzed, and stored, ensuring data integrity while reducing costs. This approach improves visibility into business, financial, and compliance performance across the enterprise; strengthens your control to enforce compliance with company and regulatory policy; and increases operational efficiency.

NoetixViews for Oracle E-Business Suite

NoetixViews for Oracle E-Business Suite

NoetixViews includes an extensive set of reporting objects (metadata) that deliver best-practice business views of the application’s database using an expert blend of table joins, cross operational functionality, customizations and indexes.

Ø      Business views for Oracle Financials, Order Management, Procurement, Projects, Discrete and Process Manufacturing, Human Resources, Grants, Service, Advanced Supply Chain Planning, and Enterprise Asset Management.

Ø      Metadata tailored to your configuration of Business Groups, Sets of Books, Operating Units, and Inventory Organizations.

Ø      Basic views for reports mapped to the Oracle screens; value-added views that integrate data from more complex business processes; and cross-functional views that integrate data from multiple application modules for full business process end-to-end reporting.

Ø      Unique features include special columns for key and descriptive flexfields, and lookups; support for multiple currencies and language translations; and special primary and foreign key columns for joining multiple views together for advanced reporting.

Ø      Built-in security seamlessly integrates with the data access rules of your Oracle responsibilities. Also included is support for module-specific data access controls such as GL flexfield security rules and HR security profiles.

This set of views enhances the following AOL components:

- Audit Trail

- Common Quick Picks

- Concurrent Manager Rules

- Concurrent Program Executables

- Concurrent Programs

- Currencies

- Database Conflicts in Concurrent Processing

- Descriptive Flexfields and Segments

- Flexfield Hierarchies, Values and Security

- Flexfield and Segment Qualifiers

- Key Flexfield Cross-Validation Rules

- Indexes

- Languages

- Printers

- Profile Options

- Report Request Groups

- Requests and Arguments

- Responsibilities

- Security

- Shorthand Flexfield Aliases

Thanks – shivmohan purohit

New feature added – Email subscription & more….

Friends, you must have observe, we have added few useful feature, first like to know your view on header image, we tried to outline the purpose and approach about this site.

also we encourage you to participate and enroll yourself of email subscription, so that you can get regular updates automatically in your emails also.

 

Thanks – Shivmohan Purohit

Oracle Expert – Mandeep Dhami

Hello Friedns, as in continuation to involve Subject Matter Experts to improve and get to knowledge, here today we showcasing Mr Mandeep Dhami, An Oracle Application experts in his area. I encourage you all to ask ur query/ question to him.

Name	Mandeep Dhami
Expertise	Module: Payables, Assets, Internet Expense and GL
Organization	Current: Merrill Lynch Past: J.P. Morgan Chase
Contacts	Email: mandeepdhami4u@hotmail.com

 1.      Tell something about you, your professional experience/ background, past assignment, Interests.

Ans: I have just got into the world of Oracle Professionals, working as Functional Consultant on Payable, Assets, Internet Expense and GL modules. So far I have been involved in implementing the above modules of Oracle across various locations in Asia Pacific region. I have been involved from operations side giving requirements to the deployment team, who then sets up the system and then I get involved in doing System Integration Testing (SIT). Once SIT is successfully done we get the same testing done by end users as part of Users Acceptance Testing (UAT). This also involves giving training and preparing training material for the end users.

2.      If any professional want to become functional consultant (either in manufacturing or financials), how she/he should take it and go ahead?

Ans: It will be too early for me to comment on this as I am quit novice in this role. But whatever I have experienced so far I can say that the functional consultant should have good knowledge about the working of various modules from end users perspective.

3.      Give some commonly used Functional Concepts/ Questions in Oracle Applications’ Financials Modules.

Ans:  What accounting entries are generated when an invoice is entered and paid?

What’s the difference between cross validation rule and security rule?

4.      What is your favorite Oracle Applications modules, share some of your interesting professional work experience.

Ans: My favorite module is Payables. In terms of interesting experience I can recollect one when implementing oracle payables for one of the locations where they were giving cash advance to employee. This was also a new learning for me. I got the solution for the same after consultation with my colleagues from the team. The real problem was to sale this solution to the location as first of all this application was new for them and this functionality was to be used by them only in the region. It took couple of training sessions with detailed flow chart and training material to get them agree to the solution suggested.

5.      Your advice/ Suggestion for Oracle Technical and Functional Professions

Ans: I would say that the professional should have good knowledge about the modules they are working on and should always strive to be up to date with latest happenings on their areas of work. I would also suggest visiting such blog (http://knoworacle.wordpress.com/) which are full of information and knowledge.

6.      Your feedback on Knoworacle.wordpress.com

Ans: Very good initiative by you to share knowledge on Oracle. There are various articles on different modules of Oracle and all these is from your own practical experience which makes this initiative all that more special, important and accurate.

Thanks – Shivmohan Purohit ( knoworacle.wordpress.com )    

Let’s join – learn and share together with knowOracle

      

As an Individual, I can see you can join and participate in following 5 levels with KnowOracle and you can start with level 1 and based on your interest you can explore more, here are some detail as follows

1. Email Subscription – This is helpful to get all lastest articles/ post in email and also stay in touch with new notificaiton for upcoming FREE trainings and advisory sessions. 

 

2. Reviewer / Supporter - If you are using articles / posts for your purpose, plz share your thoughts / opinion through feedback/ comments. this will help us to improve and understand what to do to bring more quality articles. Also if you think that KnowOracle is useful for other, do share links / articles with your friends/ collegues/ seniors/ juniors. You will be part of our reviewer/ support group.

If you have 7 or more people for any kind of training need or advisory help, we would arrange an exculsive FREE training / session for the group. this way we like to show gratitude for your effort as supporter for KnowOracle. Also special invitations for upcoming exclusive briefing on specific topics ( by invitations only )

3. Author – If you have article / articles / white papaer/ tutorials / how to / tips / faq / demos / scripts etc , any kind of study material which you like to contribute and share , do send us, we will review it and will format and publish it. As a author you will be owner of that and will get full credit and copyright. We will be just publishing and sharing along with your name.

4. Contributor – If you have more articles or you like to join as contributor , then first you need to be author to join and if you contributing more than 10 documents , subsequently we will involve you as contributor, where you yourself can write , format and design your posts / articles and publish on KnowOracle.

5. Expert - If you have expertise/ mastery on any particular topic/ tool/ module and you are interested to write or give session/ training, do let us know, we will arrange logistics and audience to showcase your skills. We wont charge to you or anyone. All activities on KnowOracle is FREE. Ofcourse you will have complete rights on your contents ( audio, video, demos ) — to know about Current Experts — Clike here