Friday, June 7, 2013

Ralph Kimball Design Tips

April 5, 2003
http://www.intelligententerprise.com//030405/606warehouse1_1.jhtml

The Soul of the Data Warehouse, Part Two: Drilling Across

By Ralph Kimball
The three fundamental themes that make up the soul of every data warehouse are drilling down, drilling across, and handling time. In Part One of "The Soul of the Data Warehouse," I showed that drilling down was nothing more than adding a row header, any row header, to an existing query. Although we often grouse about SQL's limitations as a report writer, when it comes to drilling down, SQL gracefully expands an existing query whenever a new row header is added. This simple result led to the powerful realization that when data is organized in a symmetrical, predictable fashion starting at the most atomic level, all queries and applications benefit.
If drilling down is the most fundamental maneuver in a data warehouse, drilling across is a close second. From the perspective of an answer set, drilling across adds more data to an existing row. Note that this result isn't what you get from a UNION of rows from separate queries. It's better described as the column accretion from separate queries.
Drilling across by adding another measured fact to the SELECT list from the existing fact table mentioned in the query is a trivial accomplishment. What's more interesting and important is adding another measured fact from a new fact table.
The issues raised by this simple view of drilling across are at the heart of data warehouse architecture. These issues boil down to an observation and a choice.
Drill-across observation: The new fact table called for in the drill-across operation must share certain dimensions with the fact table in the original query.
Certain dimensions will be named in the original query because they contribute row headers. Remember that these row headers are the basis of the grouping that creates the answer-set row. These dimensions will appear in the FROM clause of the SQL code and will be joined to the fact table through the relationship of a foreign key to primary key. The new fact table must also support exactly these same row headers, or the context of the answer-set row is meaningless.
Drill-across choice: Either send a single, simultaneous SQL request to the two fact tables or send two separate requests.
Although sending a single SQL request to the two fact tables seems cleaner, this choice can become a showstopper. Sending a single request means mentioning both fact tables in the FROM clause of the SQL code and joining both fact tables in some way to the common dimension tables I just discussed. This commingling of two fact tables in the same SQL statement causes these problems:

Because the two fact tables will be joined together either directly or through the common dimensions, the query must specify whether the many-to-many relationship between the two fact tables is handled with inner or outer joins. This fundamental challenge arises from the relational model. It's effectively impossible to get this right, even if you're an expert SQL programmer. Depending on the relative cardinality of the two fact tables, your aggregated numeric totals can either be too low or too high, or both! Even if you don't believe me, you have to deal with the next bullet point.
The vast majority of queries the relational database receives are generated by powerful query tools and report writers, and you have no direct control over the SQL they emit. You don't want control over the SQL. Some of these tools generate mind-boggling reams of SQL and you can't effectively intervene.
Emitting a single SQL statement precludes you from requesting data from separate table spaces, separate machines, or separate database vendors. You're stuck in the same table space on the same machine talking to one database vendor. If you can easily avoid this problem, why take on these restrictions?
Finally, if you emit a single SQL statement involving both fact tables, you'll almost certainly be unable to use any of the powerful query-rewrite tools that perform aggregate navigation. Aggregate navigation is the most cost-effective way to make dramatic gains in data warehouse performance. For more on aggregate navigation, see my column "Aggregate Navigation with (Almost) No Metadata" (August 1996) in the DBMS magazine archives accessible through http://www.ralphkimball.com/ or at www.dbmsmag.com/9608d54.html.

Implementing Drill-Across

If you've followed the logic of the observation and the choice, the architecture to support drill-across begins to emerge. I use some modern data warehouse words to describe the two key aspects of this architecture:

All fact tables in a drill-across query must use conformed dimensions.
The actual drill-across query consists of a multi-pass set of separate requests to the target fact tables followed by a simple sort/merge on the identical row headers returned from each request.

The simplest definition of conformed dimensions is that two instances of a conformed dimension are identical. So if two fact tables have a "Customer" dimension, then "Customer" is conformed if the two dimensions are exactly the same. But this definition is unnecessarily restrictive. Here's the precise definition of conformed dimensions: Two dimensions are conformed if the fields that you use as common row headers have the same domain.
When you bring two separate queries together in a drill-across operation, both queries must have the same number of row headers, arranged from left to right in the same order. All the rest of the columns (the computed facts) in the two queries, by definition, are not row headers. In other words, an independent examination of both queries shows that neither query has rows that duplicate the same row headers. To put it another way, the row headers form a unique key for each row of the answer set.
To sort/merge the two queries, you must sort them the same way. At this point, it becomes possible to merge the rows of the two queries together in a single pass. The resulting merged answer set has a single set of row headers plus the combined set of computed facts returned from both queries. Because traditional sort/merge is the same as an outer join, it is possible for a row in the final merged answer set to have nulls for either the first set of computed facts or the second set, but not both!
Once you've visualized the sort/merge step in drilling across, you really understand conformed dimensions. With conformed dimensions, the only thing you care about is matching row headers. If the contents of the respective fields you're using for the sort/merge are drawn from the same domains, then the match makes sense. If you try to match row headers from two dimensions that aren't conformed, you're guaranteed to get garbage. The sort/merge will fail, and the SQL engine will post the results from the two queries on separate lines — and probably in separate sorting locations in the merged answer set.

Amazing Magic

In my classes, I sometimes describe conformed dimensions as either dimensions that are exactly equal (the trivial case) or dimensions where "one is a subset of the other." For example, a brand dimension may be a subset of a more detailed product dimension. In this case, you can drill across two fact tables, one at the brand level with a brand dimension (such as a forecast), and the other at a detailed product level with a product dimension (such as sales transactions). Assume that the product dimension is a nice flat table containing the low cardinality brand attribute.
If the row header of the two queries is simply "brand," then some amazing magic takes place. The engine automatically aggregates both data sets to the brand level, which is exactly the right level for the drill-across. If the names of the brands are drawn from the same domain, you can complete the drill-across (of forecast vs. actual) by confidently merging the rows with the same brand names. Many commercial query and report-writing tools perform this drill-across operation.
You can see that it's possible to conform two dimensions even when they have some incompatible fields. You just need to be careful to avoid using these incompatible fields as row headers in drill-across queries. Not avoiding it lets a dimension contain some private fields that are meaningful only to a local user group.

Centralizing vs. Conforming

One final thought: In a recent guest column, my colleague Margy Ross pointed out that the decision to physically centralize a data warehouse has very little to do with conformed dimensions. (See Resources below.) Her point was that if you combine two data sets in a drill-across operation, you have to label them the same way, which is true whether the data sets are tightly administered on a single piece of hardware by one DBA or are loosely administered by remote IT organizations that merely agree to create a set of overlapping labels.
Ralph Kimball (founder of the Ralph Kimball Group) co-invented the Star Workstation at Xerox and founded Red Brick Systems. He has three best-selling data warehousing books in print, including The Data Warehouse Toolkit, Second Edition (Wiley, 2002). He teaches dimensional data warehouse design through Kimball University and critically reviews large data warehouse projects. You can reach him through his Web site, http://www.ralphkimball.com/.

RESOURCES

Related Article at IntelligentEnterprise.com:
"To Be or Not To Be Centralized," by Margy Ross, Feb. 1, 2003
"The Soul of the Data Warehouse, Part One: Drilling Down," March 20, 2003

Design tip 152 slowly changing dimension types 0 4 5 6 7

Ralph introduced the concept of slowly changing dimension (SCD) attributes in 1996. Dimensional modelers, in conjunction with the business’s data governance representatives, must specify the data warehouse’s response to operational attribute value changes. Most Kimball readers are familiar with the core SCD approaches: type 1 (overwrite), type 2 (add a row), and type 3 (add a column). Since legibility is a key component of the Kimball mantra, we sometimes wish Ralph had given these techniques more descriptive names, such as “overwrite” instead of “type 1.” But at this point, the SCD type numbers are part of our industry’s vernacular.

We have written about more advanced SCD patterns, such as the 2005 article entitled “Slowly Changing Dimensions are Not Always as Easy as 1, 2, 3.” However, we’ve not consistently named the more advanced and hybrid techniques. With the third edition of The Data Warehouse Toolkit (Wiley, 2013), we’ve decided to assign “type numbers” to several techniques that have been described, but not precisely labeled in the past. Our hope is that more specific technique names will facilitate clearer communication between DW/BI team members.

Type 0: Retain Original

With type 0, the dimension attribute value never changes, so facts are always grouped by this original value. Type 0 is appropriate for any attribute labeled “original,” such as a customer’s original credit score, or any durable identifiers. Type 0 also applies to most date dimension attributes.

Type 4: Add Mini-Dimension

The type 4 technique is used when a group of dimension attributes are split off into a separate mini-dimension. This approach is useful when dimension attribute values are relatively volatile. Frequently-used attributes in multi-million row dimension tables are also mini-dimension design candidates, even if they don’t change frequently. A surrogate key is assigned to each unique profile or combination of attribute values in the mini-dimension. The surrogate keys of both the base dimension and mini-dimension profile are captured as foreign keys in the fact table.

The following type 5, 6, and 7 techniques are hybrids that combine the basics to support the common requirement to both accurately preserve historical attribute values, plus report historical facts according to current attribute values. The hybrid approaches provide more analytic flexibility, albeit with greater complexity.

Type 5: Add Mini-Dimension and Type 1 Outrigger

The type 5 technique builds on the type 4 mini-dimension by embedding a “current profile” mini-dimension key in the base dimension that’s overwritten as a type 1 attribute. This approach, called type 5 because 4 + 1 equals 5, allows the currently-assigned mini-dimension attribute values to be accessed along with the base dimension’s others without linking through a fact table. Logically, we typically represent the base dimension and current mini-dimension profile outrigger as a single table in the presentation layer. The outrigger attributes should have distinct column names, like “Current Income Level,” to differentiate them from attributes in the mini-dimension linked to the fact table. The ETL team must update/overwrite the type 1 mini-dimension reference whenever the current mini-dimension changes over time. If the outrigger approach does not deliver satisfactory query performance, then the mini-dimension attributes could be physically embedded (and updated) in the base dimension.

Type 6: Add Type 1 Attributes to Type 2 Dimension

Type 6 builds on the type 2 technique by also embedding current attributes in the dimension so that fact rows can be filtered or grouped by either the type 2 value in effect when the measurement occurred or the attribute’s current value. The type 6 moniker was suggested by an HP engineer in 2000 because it’s a type 2 row with a type 3 column that’s overwritten as a type 1; both 2 + 3 + 1 and 2 x 3 x 1 equal 6. With this approach, the current attributes are updated on all prior type 2 rows associated with a particular durable key, as illustrated by the following sample rows:

Type 7: Dual Type 1 and Type 2 Dimensions

With type 7, the fact table contains dual foreign keys for a given dimension: a surrogate key linked to the dimension table where type 2 attributes are tracked, plus the dimension’s durable supernatural key linked to the current row in the type 2 dimension to present current attribute values.

Type 7 delivers the same functionality as type 6, but it’s accomplished via dual keys instead of physically overwriting the current attributes with type 6. Like the other hybrid approaches, the current dimension attributes should be distinctively labeled to minimize confusion.

Finally, here’s a figure from theThe Data Warehouse Toolkit, Third Edition that highlights the implications of each slowly changing dimension technique on the analysis of fact table performance metrics. As we’ve warned in the past, there’s more to consider than simply 1, 2 and 3!

http://www.kimballgroup.com/2005/03/slowly-changing-dimensions-are-not-always-as-easy-as-1-2-3/
How do you deal with changing dimensions? Hybrid approaches fill gaps left by the three fundamental techniques.

Unlike most OLTP systems, a major objective of a data warehouse is to track history. So, accounting for change is one of the analyst’s most important responsibilities. A sales force region reassignment is a good example of a business change that may require you to alter the dimensional data warehouse. We’ll discuss how to apply the right technique to account for the change historically. Hang on to your hats — this is not an easy topic.

Data warehouse design presumes that facts, such as customer orders or product shipments, will accumulate quickly. But the supporting dimension attributes of the facts, such as customer name or product size, are comparatively static. Still, most dimensions are subject to change, however slow. When dimensional modelers think about changing a dimension attribute, the three elementary approaches immediately come to mind: slowly changing dimension (SCD) types 1, 2 and 3.

As a quick review… Type 1 is most appropriate when processing corrections; this technique won’t preserve historically accurate associations. The changed attribute is simply updated (overwritten) to reflect the most current value.With a type 2 change, a new row with a new surrogate primary key is inserted into the dimension table to capture changes. Both the prior and new rows contain as attributes the natural key (or durable identifier), the most-recent-row flag and the row effective and expiration dates.With type 3, another attribute is added to the existing dimension row to support analysis based on either the new or prior attribute value. This is the least commonly needed technique.

These three fundamental techniques are adequate for most situations. However, what happens when you need variations that build on these basics to serve more analytically mature data warehouse users? Business folks sometimes want to preserve the historically accurate dimension attribute associated with a fact (such as at the time of sale or claim), but maintain the option to roll up historical facts based on current dimension characteristics. That’s when you need hybrid variations of the three main types. We’ll lead you through the hybrids in this column.

The Mini Dimension with “Current” Overwrite

When you need historical tracking but are faced with semi-rapid changes in a large dimension, pure type 2 tracking is inappropriate. If you use a mini dimension, you can isolate volatile dimension attributes in a separate table rather than track changes in the primary dimension table directly. The mini-dimension grain is one row per “profile,” or combination of attributes, while the grain of the primary dimension might be one row per customer. The number of rows in the primary dimension may be in the millions, but the number of mini-dimension rows should be a fraction of that. You capture the evolving relationship between the mini dimension and primary dimension in a fact table. When a business event (transaction or periodic snapshot) spawns a fact row, the row has one foreign key for the primary dimension and another for the mini-dimension profile in effect at the time of the event.

Profile changes sometimes occur outside of a business event, for example when a customer’s geographic profile is updated without a sales transaction. If the business requires accurate point-in-time profiling, a supplemental factless fact table with effective and expiration dates can capture every relationship change between the primary and profile dimensions. One more embellishment with this technique is to add a “current profile” key to the primary dimension. This is a type 1 attribute, overwritten with every profile change, that’s useful for analysts who want current profile counts regardless of fact table metrics or want to roll up historical facts based on the current profile. You’d logically represent the primary dimension and profile outrigger as a single table to the presentation layer if doing so doesn’t harm performance. To minimize user confusion and error, the current attributes should have column names that distinguish them from the mini-dimension attributes. For example, the labels should indicate whether a customer’s marketing segment designation is a current one or an obsolete one that was effective when the fact occurred — such as “historical marital status at time of event” in the profile mini dimension and “current marital status” in the primary customer dimension.

Type 2 with “Current” Overwrite

Another variation for tracking unpredictable changes while supporting rollup of historical facts to current dimension attributes is a hybrid of type 1 and type 2. In this scenario, you capture a type 2 attribute change by adding a row to the primary dimension table. In addition, you have a “current” attribute on each row that you overwrite (type 1) for the current and all previous type 2 rows. You retain the historical attribute in its own original column. When a change occurs, the most current dimension row has the same value in the uniquely labeled current and historical (“as was” or “at time of event”) columns.

You can expand this technique to cover not just the historical and current attribute values but also a fixed, end-of-period value as another type 1 column. Although it seems similar, the end-of-period attribute may be unique from either the historical or current perspective. Say a customer’s segment changed on Jan. 5 and the business wants to create a report on Jan. 10 to analyze last period’s data based on the customer’s Dec. 31 designation. You could probably derive the right information from the row effective and expiration dates, but providing the end-of-period value as an attribute simplifies the query. If this query occurs frequently, it’s better to have the work done once, during the ETL process, rather than every time the query runs. You can apply the same logic to other fixed characteristics, such as the customer’s original segment, which never changes. Instead of having the historical and current attributes reside in the same physical table, the current attributes could sit in an outrigger table joined to the dimension natural key. The same natural key, such as Customer ID, likely appears on multiple type 2 dimension rows with unique surrogate keys. The outrigger contains just one row of current data for each natural key in the dimension table; the attributes are overwritten whenever change occurs. To promote ease of use, have the core dimension and outrigger of current values appear to the user as one — unless this hurts application performance.

Type 2 with Natural Keys in the Fact Table

If you have a million-row dimension table with many attributes requiring historical and current tracking, the last technique we described becomes overly burdensome. In this situation, consider including the dimension natural key as a fact table foreign key, in addition to the surrogate key for type 2 tracking. This technique gives you essentially two dimension tables associated with the facts, but for good reason. The type 2 dimension has historically accurate attributes for filtering or grouping based on the effective values when the fact table was loaded. The dimension natural key joins to a table with just the current type 1 values. Again, the column labels in this table should be prefaced with “current” to reduce the risk of user confusion. You use these dimension attributes to summarize or filter facts based on the current profile, regardless of the values in effect when the fact row was loaded. Of course, if the natural key is unwieldy or ever reassigned, then you should use a durable surrogate reference key instead.

This approach delivers the same functionality as the type 2 with “current” overwrite technique we discussed earlier; that technique spawns more attribute columns in a single dimension table, while this approach relies on two foreign keys in the fact table.

While it’s uncommon, we’re sometimes asked to roll up historical facts based on any specific point-in-time profile, in addition to reporting them by the attribute values when the fact measurement occurred or by the current dimension attribute values. For example, perhaps the business wants to report three years of historical metrics based on the attributes or hierarchy in effect on Dec. 1 of last year. In this case, you can use the dual dimension keys in the fact table to your advantage. You first filter on the type 2 dimension table’s row effective and expiration dates to locate the attributes in effect at the desired point in time. With this constraint, a single row for each natural or durable surrogate key in the type 2 dimension has been identified. You can then join to the natural/durable surrogate dimension key in the fact table to roll up any facts based on the point-in-time attribute values. It’s as if you’re defining the meaning of “current” on the fly. Obviously, you must filter on the type 2 row dates or you’ll have multiple type 2 rows for each natural key, but that’s fundamental in the business’s requirement to report any history on any specified point-in-time attributes. Finally, only unveil this capability to a limited, highly analytic audience. This embellishment is not for the faint of heart.

Series of Type 3 Attributes

Say you have a dimension attribute that changes with a predictable rhythm, such as annually, and the business needs to summarize facts based on any historical value of the attribute (not just the historically accurate and current, as we’ve primarily been discussing). For example, imagine the product line is recategorized at the start of every fiscal year and the business wants to look at multiple years of historical product sales based on the category assignment for the current year or any prior year.

This situation is best handled with a series of type 3 dimension attributes. On every dimension row, have a “current” category attribute that can be overwritten, as well as attributes for each annual designation, such as “2004 category” and “2003 category.” You can then group historical facts based on any annual categorization.

This seemingly straightforward technique isn’t appropriate for the unpredictable changes we described earlier. Customer attributes evolve uniquely. You can’t add a series of type 3 attributes to track the prior attribute values (“prior-1,” “prior-2” and so on) for unpredictable changes, because each attribute would be associated with a unique point in time for nearly every row in the dimension table.

Balance Power against Ease of Use

Before using hybrid techniques to support sophisticated change tracking, remember to maintain the equilibrium between flexibility and complexity. Users’ questions and answer sets will vary depending on which dimension attributes are used for constraining or grouping. Given the potential for error or misinterpretation, hide the complexity (and associated capabilities) from infrequent users.

Design-tip-153-three-critical-components-for-successful-self-service-bi

Modelling free text comments

Thursday, January 17, 2013

Oracle Interview Questions - Part I

Skillbuilders

Keyword/Topics for an Oracle Interview :

Oracle 9i, 10g, 11g Architecture (Oracle Internals - Part of SGA, PGA)

General :

9i :

10g :

11g :

12c Architecture

RAC :

RAC Architecture Diagram

Summary

Oracle Server = Oracle Instance + Oracle Database
Oracle Instance = Background Processes and Memory Structures
Oracle Database = Set of Files
Oracle Memory Structure

SGA (System Global Area) - Part of Oracle Memory shared by all processes in an instance

Fixed SGA
Variable SGA

DB Buffer Cache = db_block_buffers
Shared Pool = shared_pool_size

Library Cache comprising of (Shared SQL area for sql and execution plan + UGA)
Reserved Pool
DD Cache
Result Cache (11g)
Enqueues and Latches

Redo Log Buffer = log_buffer
Java Pool
Large Pool (optional) = large_pool_size
KEEP Pool
Recycle Buffer Pool
Streams Pool (11g)
Other structures like lock and latch management

Links

PGA - Program Global Area - Part of Oracle Memory private to each process.

Session Memory

Private SQL Area

Bind Variables
Query Execution State info

SQL Work Areas

Sorting Data
Hash Tables for hash join

Processes

Server Processes
Background Processes

Server (Snnn)
Dispatcher (Dnnn)
Queue Monitor (QMNn)
System Monitor (SMON)
Process Monitor (PMON)
Database Writer (DBW0 or DBWn)
Log Writer (LGWR)
Checkpoint (CKPT)
Recoverer (RECO)
Lock Manager Server (LMS) – Real Application Clusters only
Archiver (ARCn)

Files

Data files
Undo files
Redo files
Control files
Parameter file
ora files
Alert log
Trace files
Archive files
Backup dump files

SMON - Server Monitor manages the server in case of failure, performs instance recovery.
PMON - Process Monitor helps to release resources held by a failed process or help in process recovery.
SGA - System Global Area is part of oracle memory that stores data and control info common for all oracle user processes.
PGA - Program Global Area is part of oracle memory that stores data and control info for a single oracle user process.
UGA - User Global Area
PFILE - Oracle param file specifies path of the control files and how the memory is allocated and other characteristics of the Oracle Instance.
Redo Log File - Records chgs made to the db
CKPT - puts a timestamp on all control and data files when a checkpoint occurs
A checkpoint switch occurs when DBWR writes all modified buffers in the SGA buffer cache to the datafiles.
A Tablespace in Oracle comprises of one or more data files.
PCTFREE - a param which specifies how much space shld be left free in a block for future updates.
DBWn - writes the modified blocks from db buffer cache in the SGA to the db files
LGWR is a sys log writer that records the system activities and writes nto a log file.
A Chained Row is a row that is stored across multiple database blocks as it is too large to fit into a single database block
HWM - High Water Mark

http://gerardnico.com/wiki/database/oracle/high_water_mark

The database doesn't know what is on a block unless and until it reads the block.

You can think at the high-water mark (HWM) as the rightmost block that ever contained data in a segment.
The High water mark is the boundary between used and unused space in a segment.
High Water Mark = TOTAL BLOCKS−UNUSED BLOCKS−1
OR High Water Mark = USER_SEGMENTS.BLOCKS−USER_TABLES.EMPTY_BLOCKS−1
Truncated, (TRUNCATE will reset the HWM of a table back to 'zero')

SCN - System Change Number http://gerardnico.com/wiki/database/oracle/scn

A system change number (SCN) is a logical, internal time stamp used by Oracle Database. SCNs order events that occur within the database, which is necessary to satisfy the ACID properties of a transaction.

Oracle Database uses SCNs to mark the SCN before which all changes are known to be on disk so that recovery avoids applying unnecessary redo. The database also uses SCNs to mark the point at which no redo exists for a set of data so that recovery can stop.

SCNs occur in a monotonically increasing sequence. Oracle Database can use an SCN like a clock because an observed SCN indicates a logical point in time and repeated observations return equal or greater values. If one event has a lower SCN than another event, then it occurred at an earlier time with respect to the database. Several events may share the same SCN, which means that they occurred at the same time with respect to the database.

A scn can be created with the savepoint command ?
Transaction

Every transaction has an SCN. For example, if a transaction updates a row, then the database records the SCN at which this update occurred. Other modifications in this transaction have the same SCN. When a transaction commits, the database records an SCN for this commit.
Process

Oracle Database increments SCNs in the system global area (SGA). When a transaction modifies data, the database writes a new SCN to the undo data segment assigned to the transaction. The log writer process then writes the commit record of the transaction immediately to the online redo log. The commit record has the unique SCN of the transaction. Oracle Database also uses SCNs as part of its instance recovery and media recovery mechanisms.
ORA_ROWSCN

For each row, ORA_ROWSCN returns the conservative upper bound system change number (SCN) of the most recent change to the row. This pseudocolumn is useful for determining approximately when a row was last updated. It is not absolutely precise, because Oracle tracks SCNs by transaction committed for the block in which the row resides.

SELECT MAX(ORA_ROWSCN), SCN_TO_TIMESTAMP(MAX(ORA_ROWSCN)) FROM SCOTT.EMP;

9i vs 10g vs 11g
How does Oracle execute query internally

All SQL statements have to go through the following stages of execution:

1) PARSE: Every SQL statement has to be parsed which includes checking the syntax, validating, ensuring that all references to objects are correct and ensuring that relevant privileges to those object exist.
2) BIND: After parsing, the oracle server knows the meaning of the oracle statement but still may not have enough info(values for variables) to execute the statement. The process of obtaining these value is called as bind values.
3) EXECUTE: After binding, the Oracle server executes the statement.
4) FETCH: In the fetch stage, rows are selected and ordered and each successive row retrieves another row of the result until the last row has been fetched. This stage is only for certain DML statements like SELECT.

When we pass a SELECT query it executes under some phases. That includes

1) User submits a request from the client or request is recieved from an app via jdbc/odbc
2) The request goes from the client to the listener which identifies the db service and passes it to the server.
2) Checking by Server Process - Which type of Query is this - SQL or PL/SQL.
3) If SQL- server process checks if it's already available under library cache under shared pool and acquires a handle, if not then it places the code in SQL area available after parsing if space is available else apply LRU to create space.
4) If PL/SQL - server process puts the SQL code in SQL area after above step and checks if PL code is available in PL area available under library cache area under shared pool, if not then it places the code in PL area available after parsing if space is available else apply LRU to create space.
5) Now Parsing (Syntax, Privileges on user passing the query checks)
6) Check if execution plan is already available or not in shared sql area by creating a hash key and doing a lookup.
7) If not then it creates an execution plan on the basis of available statistics in the database dictionary cache. Checks if space is available in the shared pool to place the execution plan. If space is available it places the plan on the shared pool, waits for a latch and uses the same. If the Shared pool is full it will apply LRU algo before putting the sql on the shared sql area. If sql and plan are already available, uses available execution plan directly.
8) Checks if the data is available in the result cache, if available will use the same.
9) Else checks if the data blocks contains required data are available in buffer cache or not
10) If yes, server process pick the data from BC and return to the user. If not server process reads the data blocks from data files and put the data blocks into buffer cache and return to the user.

The order of Query Execution is following (Right to Left, Bottom Up)

1)From Clause
2)Where Clause
3)Group By Clause
4)Having Clause
5)Select
6)Order By Clause

Sum of the sizes of all the data files specified in the create tablespace determines the init size of a tablespace
INIT size of Diff between 9i, 10g, 11g
Optimistic and Pessimistic Locking
Data Concurrency and Consistency
Session (login) vs Transaction (commit) vs Unit of Work (begin end block)

Use the BEGIN_DISCRETE_TRANSACTION procedure to improve the performance of short, non-distributed transactions.
Use the SET TRANSACTION command with the USE ROLLBACK SEGMENT parameter to explicitly assign a transaction to an appropriate rollback segment. This can eliminate the need to dynamically allocate additional extents, which can reduce overall system performance.
Use the SET TRANSACTION command with the ISOLATION LEVEL set to SERIALIZABLE to get ANSI/ISO serializable transactions.
Establish standards for writing SQL statements so that you can take advantage of shared SQL areas. Oracle recognizes identical SQL statements and allows them to share memory areas. This reduces memory usage on the database server and increases system throughput.
Use the ANALYZE command to collect statistics that can be used by Oracle to implement a cost-based approach to SQL statement optimization. You can supply additional "hints" to the optimizer as needed.
Call the DBMS_APPLICATION_INFO.SET_ACTION procedure before beginning a transaction to register and name a transaction for later use when measuring performance across an application. You should specify what type of activity a transaction performs so that the system tuners can later see which transactions are taking up the most system resources.
Increase user productivity and query efficiency by including user-written PL/SQL functions in SQL expressions as described in "Calling Stored Functions from SQL Expressions".
Create explicit cursors when writing a PL/SQL application.
When writing precompiler programs, increasing the number of cursors using MAX_OPEN_CURSORS can often reduce the frequency of parsing and improve performance.
To commit a transaction, use the COMMIT command. The following two statements are equivalent and commit the current transaction:
```
COMMIT WORK;
COMMIT;
```
The COMMIT command lets you include the COMMENT parameter along with a comment (less than 50 characters) that provides information about the transaction being committed. This option is useful for including information about the origin of the transaction when you commit distributed transactions:
```
COMMIT COMMENT 'Dallas/Accts_pay/Trans_type 10B';
```


A read-only transaction is started with a SET TRANSACTION statement that includes the READ ONLY option. For example:
SET TRANSACTION READ ONLY;


A transaction explicitly acquires the specified table locks when a LOCK TABLE statement is executed. A LOCK TABLE statement manually overrides default locking. When a LOCK TABLE statement is issued on a view, the underlying base tables are locked. The following statement acquires exclusive table locks for the EMP_TAB and DEPT_TAB tables on behalf of the containing transaction:
LOCK TABLE Emp_tab, Dept_tab
    IN EXCLUSIVE MODE NOWAIT;

LOCK TABLE Emp_tab IN ROW SHARE MODE;
LOCK TABLE Emp_tab IN ROW EXCLUSIVE MODE;


Table 7-2 Nondefault Locking Behavior

Statement Case 1: row Case 1: table Case 2: row Case 2: table Case 3: row Case 3: table






SELECT





-





-





-





S





-





S






INSERT





X





SRX





X





RX





X





SRX






UPDATE





X





SRX





X





SRX





X





SRX






DELETE





X





SRX





X





SRX





X





SRX






SELECT...FOR UPDATE





X





RS





X





S





X





S






LOCK TABLE... IN..





-





-





-





-





-





-






ROW SHARE MODE





-





RS





-





RS





-





RS






ROW EXCLUSIVE MODE





-





RX





-





RX





-





RX






SHARE MODE





-





S





-





S





-





S






SHARE ROW EXCLUSIVE MODE





-





SRX





-





SRX





-





SRX






EXCLUSIVE MODE





-





X





-





X





-





X






DDL statements





-





X





-





X





-





X

Statement	Case 1: row	Case 1: table	Case 2: row	Case 2: table	Case 3: row	Case 3: table
`SELECT`	-	-	-	S	-	S
`INSERT`	X	SRX	X	RX	X	SRX
`UPDATE`	X	SRX	X	SRX	X	SRX
`DELETE`	X	SRX	X	SRX	X	SRX
`SELECT`...`FOR UPDATE`	X	RS	X	S	X	S
`LOCK TABLE`... `IN..`	-	-	-	-	-	-
`ROW` `SHARE` `MODE`	-	RS	-	RS	-	RS
`ROW EXCLUSIVE MODE`	-	RX	-	RX	-	RX
`SHARE MODE`	-	S	-	S	-	S
`SHARE ROW EXCLUSIVE MODE`	-	SRX	-	SRX	-	SRX
`EXCLUSIVE MODE`	-	X	-	X	-	X
DDL statements	-	X	-	X	-	X


Table 7-3 Summary of ANSI Isolation Levels

Isolation Level Dirty Read (1) Non-Repeatable Read (2) Phantom Read (3)






READ UNCOMMITTED





Possible





Possible





Possible






READ COMMITTED





Not possible





Possible





Possible






REPEATABLE READ





Not possible





Not possible





Possible






SERIALIZABLE





Not possible





Not possible





Not possible






Notes:





(1) A transaction can read uncommitted data changed by another transaction.






(2) A transaction rereads data committed by another transaction and sees the new data.






(3) A transaction can execute a query again, and discover new rows inserted by another committed transaction.



The behavior of Oracle with respect to these isolation levels is summarized below:

Isolation Level Description






READ UNCOMMITTED





Oracle never permits "dirty reads." Although some other database products use this undesirable technique to improve thoughput, it is not required for high throughput with Oracle.






READ COMMITTED





Oracle meets the READ COMMITTED isolation standard. This is the default mode for all Oracle applications. Because an Oracle query only sees data that was committed at the beginning of the query (the snapshot time), Oracle actually offers more consistency than is required by the ANSI/ISO SQL92 standards for READ COMMITTED isolation.






REPEATABLE READ





Oracle does not normally support this isolation level, except as provided by SERIALIZABLE.






SERIALIZABLE





You can set this isolation level using the SET TRANSACTION command or the ALTER SESSION command.



Table 7-4  Read Committed Versus Serializable Transaction


Operation Read Committed Serializable






Dirty write





Not Possible





Not Possible






Dirty read





Not Possible





Not Possible






Non-repeatable read





Possible





Not Possible






Phantoms





Possible





Not Possible






Compliant with ANSI/ISO SQL 92





Yes





Yes






Read snapshot time





Statement





Transaction






Transaction set consistency





Statement level





Transaction level






Row-level locking





Yes





Yes






Readers block writers





No





No






Writers block readers





No





No






Different-row writers block writers





No





No






Same-row writers block writers





Yes





Yes






Waits for blocking transaction





Yes





Yes






Subject to "can't serialize access" error





No





Yes






Error after blocking transaction aborts





No





No






Error after blocking transaction commits





No





Yes

Locking and Blocks
2 Phase Commit
Distributed Transaction is a transaction that includes one or more statements that update the db.
Drop in 10g vs 9i
Data on 2 or more distinct nodes of a distributed db.
Flash back syntax and recycle bin
Yes, Recycle table gets records after TRUNCATE.
Difference between DB Buffer Cache and Result Cache in 11g.

Isolation Level	Dirty Read (1)	Non-Repeatable Read (2)	Phantom Read (3)
`READ UNCOMMITTED`	Possible	Possible	Possible
`READ COMMITTED`	Not possible	Possible	Possible
`REPEATABLE READ`	Not possible	Not possible	Possible
`SERIALIZABLE`	Not possible	Not possible	Not possible
Notes:	(1) A transaction can read uncommitted data changed by another transaction.
	(2) A transaction rereads data committed by another transaction and sees the new data.
	(3) A transaction can execute a query again, and discover new rows inserted by another committed transaction.

Isolation Level	Description
`READ UNCOMMITTED`	Oracle never permits "dirty reads." Although some other database products use this undesirable technique to improve thoughput, it is not required for high throughput with Oracle.
`READ COMMITTED`	Oracle meets the `READ` `COMMITTED` isolation standard. This is the default mode for all Oracle applications. Because an Oracle query only sees data that was committed at the beginning of the query (the snapshot time), Oracle actually offers more consistency than is required by the ANSI/ISO SQL92 standards for `READ` `COMMITTED` isolation.
`REPEATABLE READ`	Oracle does not normally support this isolation level, except as provided by `SERIALIZABLE`.
`SERIALIZABLE`	You can set this isolation level using the `SET` `TRANSACTION` command or the `ALTER` `SESSION` command.

Operation	Read Committed	Serializable
Dirty write	Not Possible	Not Possible
Dirty read	Not Possible	Not Possible
Non-repeatable read	Possible	Not Possible
Phantoms	Possible	Not Possible
Compliant with ANSI/ISO SQL 92	Yes	Yes
Read snapshot time	Statement	Transaction
Transaction set consistency	Statement level	Transaction level
Row-level locking	Yes	Yes
Readers block writers	No	No
Writers block readers	No	No
Different-row writers block writers	No	No
Same-row writers block writers	Yes	Yes
Waits for blocking transaction	Yes	Yes
Subject to "can't serialize access" error	No	Yes
Error after blocking transaction aborts	No	No
Error after blocking transaction commits	No	Yes

The Server Result Cache holds result sets and not data blocks
Datawarehouse Result Cache

A Useful Oracle 11g New Feature

Server Result Cache
Query result cache in Oracle 11g
There are three new result caching features in 11g:

Query Result Cache;
PL/SQL function result cache; and
Client OCI result cache.
Query Result Cache : As its name suggests, the query result cache is used to store the results of SQL queries for re-use in subsequent executions. By caching the results of queries, Oracle can avoid having to repeat the potentially time-consuming and intensive operations that generated the resultset in the first place (for example, sorting/aggregation, physical I/O, joins etc). The cache results themselves are available across the instance (i.e. for use by sessions other than the one that first executed the query) and are maintained by Oracle in a dedicated area of memory. Unlike our homegrown solutions using associative arrays or global temporary tables, the query result cache is completely transparent to our applications. It is also maintained for consistency automatically, unlike our own caching programs.
A brief explanation of each of these parameters is as follows.

result_cache_mode: the result cache can be enabled in three ways: via hint, alter session or alter system. Default is MANUAL which means that we need to explicitly request caching via the RESULT_CACHE hint;
result_cache_max_size: this is the size of the result cache in bytes. The cache is allocated directly from the shared pool but is maintained separately (for example, flushing the shared pool will not flush the result cache);
result_cache_max_result: this specifies the highest percentage of the cache that is able to be used by a single resultset (default 5%); and
result_cache_remote_expiration: this specifies the number of minutes for which a resultset based on a remote object can remain valid. The default is 0 which means that resultsets defendant on remote objects will not be cached.

Oracle 11g New Features

Oracle 11g new features
SQL New Features

PIVOT and UNPIVOT Operators in Oracle Database 11g Release 1
New "PIVOT" SQL clause - The new "pivot" SQL clause will allow quick rollup, similar to an MS-Excel pivot table, where you can display multiple rows on one column with SQL. MS SQL Server 2005 also introduced a pivot clause. Laurent Schneider notes that the new SQL "pivot" syntax is great for converting rows-to-columns and columns-to-rows.
Dynamic SQL - DBMS_SQL is here to stay. It's faster and is being enhanced. DBMS_SQL and NDS can now accept CLOBs (no more 32K limit on NDS). A ref cursor can become a DBMS_SQL cursor and vice versa. DBMS_SQL now supports user-defined types and bulk operations. Source : Lewis Cunningham
The /*+result_cache*/ SQL hint - This suggests that the result data will be cached in the data buffers, and not the intermediate data blocks that were accessed to obtain the query results. You can cache SQL and PL/SQL results for super-fast subsequent retrieval. The "result cache" ties into the "scalable execution" concept. There are three areas of the result cache:

The SQL query result cache - This is an area of SGA memory for storing query results
The PL/SQL function result cache - This result cache can store the results from a PL/SQL function call.
The OCI client result cache - This cache retains results from OCI calls, both for SQL queries or PL/SQL functions.

PL/SQL New Features

PL/SQL Enhancements
PL/SQL "CONTINUE" keyword - This will allow a "C-Like" continue in a loop, skipping an iteration to bypass any "else" Boolean conditions. A nasty PL/SQL GOTO statement is no longer required to exit a Boolean within a loop. Oracle professional Jurgen Kemmelings has an excellent PL/SQL example of the PL/SQL continue clause in-action:

begin
for i in 1..3
loop
    dbms_output.put_line(’i=’||to_char(i));
    if ( i = 2 )
    then
      continue;
    end if;
    dbms_output.put_line(’Only if i is not equal to 2′);
end loop;
end;

Native PL/SQL use of Seq.Nextval in a PL/SQL program.
Disabled state for PL/SQL - Another 11g new feature is a "disabled" state for PL/SQL (as opposed to "enabled" and "invalid" in dba_objects).
Improved PL/SQL stored procedure invalidation mechanism - A new 11g features will be fine grained dependency tracking, reducing the number of objects which become invalid as a result of DDL.
Enhanced PL/SQL warnings - The 11g PL/SQL compiler will issue a warning for a "when others" with no raise.
Stored Procedure named notation - Named notation is now supported when calling a stored procedure from SQL.
PL/SQL can use CLOBs to get around 32KB limitation of SQL character strings.
Invisible Indexes in Oracle Database 11g Release 1 used to test dropping trigger
Partitioning Enhancements in Oracle Database 11g Release 1

Query Result Cache in Oracle Database 11g Release 1

Result Cache
PL/SQL Function cache
OCI Cache

Cross-Session PL/SQL Function Result Cache in Oracle Database 11g Release 1
DDL With the WAIT Option (DDL_LOCK_TIMEOUT) in Oracle Database 11g Release 1
Enhanced Finer Grained Dependency Management in Oracle Database 11g Release 1
PLSTIMER identifies hotspots and performance tuning opportunities in PL/SQL.
Conditional Compilation
Commit in Trigger
Easy PL/SQL compiling - Native Compilation no longer requires a C compiler to compile your PL/SQL. Your code goes directly to a shared library. Source : Lewis Cunningham
Scalable PL/SQL - The next scalable execution feature is automatic creation of "native" PL/SQL (and Java code), with just one parameter for each type with an "on/off" value. This apparently provides a 100% performance boost for pure PL/SQL code, and a 10%-30% performance boost for code containing SQL.
Flashback and LogMiner Enhancements in Oracle Database 11g Release 1
Online Table Redefinition Enhancements in Oracle Database 11g Release 1
Read-Only Tables in Oracle Database 11g Release 1
SQL Performance Analyzer in Oracle Database 11g Release 1
SQL Access Advisor in Oracle Database 11g Release 1
Data Pump Enhancements in Oracle Database 11g Release 1
PL/SQL Hierarchical Profiler in Oracle Database 11g Release 1
PL/SQL New Features and Enhancements in Oracle Database 11g Release 1
Trigger Enhancements in Oracle Database 11g Release 1

FOLLOWS keyword to control sequence of execution for same timing triggers
COMPOUND TRIGGERS helps define single trigger for Statement level and row level trigger
Now ENABLE and DISABLE triggers during creation

Real-Time SQL Monitoring using DBMS_SQLTUNE (REPORT_SQL_MONITOR, REPORT_SQL_MONITOR_LIST and REPORT_SQL_DETAIL)
DBMS_PARALLEL_EXECUTE
APPEND_VALUES Hint in Oracle Database 11g Release 2

FORALL i IN l_tab.first .. l_tab.last
    INSERT /*+ APPEND_VALUES */ INTO forall_test VALUES l_tab(i);

Oracle 11g R2 new parallel query management enhancements
Oracle 11g fully automated SQL tuning tips
Oracle 11g SQL performance analyzer concepts
11g SQL Plan baseline tips
Lillian Hobbs speaks on Oracle 11g SQL tuning transformations
Inside Oracle 11g Database replay
Inside the 11g SQL Performance Analyzer (SPA)
Oracle 11g function-based virtual columns
Virtual Columns in 11g
Oracle Omotion tips

Omotion is an online migration utility (new in 11gR2) that is used with RAC One Node to facilitate super fast instance relocation is cases of server failure.

Oracle column level compression
Oracle optimizer extended statistics tips
Oracle Instance Caging tips
Recursive subquery factoring using the SQL WITH clause

Oracle 12c New Features

Codd 12 Rules

Normalization

1NF - A table is in first normal form if it contains no repeating groups
2NF - A relation is in second normal form (2NF) if and only if it is in 1NF and every nonkey attribute is fully dependent on the primary key. That is No non-prime attribute in the table is functionally dependent on a proper subset of any candidate key. All non-prime attributes should depend on the entire primary key.
3NF - Every non-prime attribute is non-transitively dependent on every candidate key in the table.
BCNF / 3.5 NF - Every determinant must be a candidate key.
4NF - A relation is in 4NF if it has no multi-valued dependencies.
Links

Data Types

Date function print diff between dates and how to print Milliseconds
Max size of varchar2 in SQL and PL/SQL

CHAR= 2000 bytes
VARCHAR2 = 4000 bytes in SQL and 32K in PL/SQL

Data type conversion matrix
Explicit casting using CAST in SQL Query. Syntax : CAST(column AS DATATYPE(size))

MAX LIMITS

Logical Database Limits
Max cols in a table = 1000
Indexes per table = Unlimited
Limit to max no of chars in DBMS_OUTPUT.PUT_LINE = 255 (9i), 32K (10g)



1  BEGIN--9i


2     DBMS_OUTPUT.PUT_LINE( RPAD('x',256,'x') );


3  END;


4  /


xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx


xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx


xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx


xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
PL/SQL procedure successfully completed.

  1 BEGIN--10g
  2    DBMS_OUTPUT.PUT_LINE( RPAD('x',32767,'x') || 'even more char');
  3  END;
  4  /

BEGIN
*
ERROR at line 1:
ORA-20000: ORU-10028: line length overflow, limit of 32767
bytes per line

SQL> set serveroutput on size 1000

SP2-0547: size option 1000 out of range (2000 through 1000000)

SQL> set serveroutput on size unlimited

```
SQL> show serveroutput
```

serveroutput ON SIZE UNLIMITED FORMAT WORD_WRAPPED

```
SQL> set serveroutput on
```
```
SQL> show serveroutput
```

serveroutput ON SIZE UNLIMITED FORMAT WORD_WRAPPED

MAX Limit on the static values in the IN clause is 1000
MAX Limit on the no of sub queries IN the where clause = 255
MAX Limit on the no of sub queries IN the from clause = No Limit
Triggers = 11

Programming Structures

For Loop i index CANNOT be referred or incremented outside the LOOP construct.

PSUEDO Columns

ROWID - Physical location of a row on the disk
ORA_ROWSCN - SCN no for the row
ROWNUM - Logical Number of Row in a resultset
NEXTVAL - Next Value of a Sequence
CURRVAL - Current Value of a Sequence

LIKE is used for wild card compares with % and _
Concatenation operator ||
A many to many relation is implemented using a intersection table / bridge table
Sub-query in IN clause can't have order by clause.
MERGE and Conditional MERGE for 3 operations
Delete from parent when child exist (CASCADE OPT)
Select * from emp where pos = 'sales' or pos = 'pur' and sal > 1500 what will execute first AND or OR. Refer Operator Precedence.
Single row sub query returns more than 1 row error can be avoided using In clause
What is the output of select * from emp where rownum = 2; No rows returns whereas
Select * from emp where rownum <= 2 will give an output, doesn't work with > or =. The filter is applied after the resultset is returned.
But <=1, = 1 and >=1 all will give result
CO-RELATED queries O(m*n) instead of O(m+n) for IN
ANSI compliant equivalent statement
Selecting from a cursor / collection
X RIGHT OUTER JOIN Y will return all rows from the table Y on the RIGHT
In outer join (+) side table can have NULL Cols
Oracle creates implicit cursor for DML operations Insert/Update/Delete
Delete on of the identical rows

Delete from tab t1 where t1.rowid > (Select min(t2.rowid) from tab t2 where t2.emp_no = t1.emp_no);

4 table join will have min 3 join conditions
NULL in IF means do nothing and pass control to the next statement.
Drop Table with purge option drops the table including from the recycle bin

Handling NULLs

NVL - The NVL function allows you to replace null values with a default value. If the value in the first parameter is null, the function returns the value in the second parameter. If the first parameter is any value other than null, it is returned unchanged.
DECODE - The DECODE function is not specifically for handling null values, but it can be used in a similar way to the NVL function, as shown by the following example.
NULLIF - Returns NULL for a condition. The NULLIF function was introduced in Oracle 9i. It accepts two parameters and returns null if both parameters are equal. If they are not equal, the first parameter value is returned.
NVL2 - The NVL2 function accepts three parameters. If the first parameter value is not null it returns the value in the second parameter. If the first parameter value is null, it returns the third parameter.
COALESCE - The COALESCE function was introduced in Oracle 9i. It accepts two or more parameters and returns the first non-null value in a list. If all parameters contain null values, it returns null.

PL/SQL

Variables types as per scope : Local / Global / Constant
DDL in PL/SQL (Yes its possible using EXECUTE IMMEDIATE)

OPERATORS

Operator precedence

NULL , LIKE , BETWEEN , IN, EXISTS
NOT
AND
OR * / + -

INTERVAL BINARY, COLLATE ! - (unary minus), ~ (unary bit inversion) ^ *, /, DIV, %, MOD -, + <<, >> & | = (comparison), <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN BETWEEN, CASE, WHEN, THEN, ELSE NOT AND, && XOR OR, || = (assignment), :=

Data Dictionary Tables

user_views - Objects owned by current schema
all_views - Objects in all schema wherein the current schema has privilege to access
dba_view - All Object in the DB irrespective of any access
user_objects - gives info abt list of objects
user_tables - gives info abt list of tables
user_col_privs - gives info abt column priviledges
user_tab_columns - gives info abt table columns
dba_data_files - gives info about tablespace and size of file

EXTERNAL Tables

External Tables an Introduction
Art of Ext Tabs
Ext Tables
External Table Concepts
ORAFAQ - External Tables
PSOUG - External Table
Oracle External Tables
External Tables
Art more ext tables
Preprocess External Tables
ExternalTables in Oracle 9i
External Tables
Tips for Loading External Tables

create directory etl_dir as 
'C:\etl_dir';

Grant read, write on directory etl_dir to etladmin;

Create table indata1
(
    cust_id     number,
    cust_name    varchar2(20),
    credit_limit number(10)
)
organization external
(
    type oracle_loader
    default directory etl_dir
    access parameters
    (
       records delimited by newline
       fields terminated by ","
    )
    location ('indata1.txt')
)
/

CURSOR SHARING AND TYPES

EXACT : Queries exactly match textually including case, space and literals.
SIMILAR : Queries match ignoring minor textual difference like case, space and literals as long as the plan is the same (as data distribution is for the literal is same).
FORCE : SIMILAR but Plan is different still they are forced to match.
Links

MATERIALIZED VIEW (aka. time based snapshots aka summary tables)

A materialized view stores both definitions of view plus rows resulting from the execution of the view. It is more efficient to use materialized views if query involves summaries, large or multiple joins or both. It is a pre-computed table comprising aggregated or joined data from fact and possibly dimensions tables. Also known as a summary or aggregate table and mainly used for improving query performance or providing replicated data.
A materialized view is a replica of a target master from a single point in time. The master can be either a master table at a master site or a master materialized view at a materialized view site. Whereas in multi-master replication tables are continuously updated by other master sites, materialized views are updated from one or more masters through individual batch updates, known as a refreshes, from a single master site or master materialized view site, as illustrated in Figure 3-1. The arrows in Figure 3-1 represent database links.
Types of materialized views

There are three types of materialized views :

Read-only materialized view
Updateable materialized view
Writeable materialized view

Techniques used to refresh a Materialized View (Procedure used to refresh a mview)

Materialized Views Refresh Techniques

Manual Refresh: Can be performed using DBMS_MVIEW package. (REFRESH, REFRESH_DEPENDENT, REFRESH_ALL_VIEWS)
Automatic Refresh: Can be performed in two ways:

a)ON COMMIT – Materialized view gets updated whenever changes to one of these tables are committed.
b)ON DEMAND – At Specified Time – Refresh is scheduled to occur for specified time by using START WITH & NEXT clauses. For such refreshes, instance must initiate a process with JOB_QUEUE_PROCESSES.

Materialized Views Refresh Methods

FAST Clause

The FAST refreshes use the materialized view logs (as seen above) to send the rows that have changed from master tables to the materialized view.
You should create a materialized view log for the master tables if you specify the REFRESH FAST clause.
SQL> CREATE MATERIALIZED VIEW LOG ON emp;
Materialized view log created.
Materialized views are not eligible for fast refresh if the defined subquery contains an analytic function.

COMPLETE Clause (Also know as full refresh)

The complete refresh re-creates the entire materialized view. If you request a complete refresh, Oracle performs a complete refresh even if a fast refresh is possible.

FORCE Clause

When you specify a FORCE clause, Oracle will perform a fast refresh if one is possible or a complete refresh otherwise. If you do not specify a refresh method (FAST, COMPLETE, or FORCE), FORCE is the default.

PRIMARY KEY and ROWID Clause

WITH PRIMARY KEY is used to create a primary key materialized view i.e. the materialized view is based on the primary key of the master table instead of ROWID (for ROWID clause). PRIMARY KEY is the default option. To use the PRIMARY KEY clause you should have defined PRIMARY KEY on the master table or else you should use ROWID based materialized views.
PRIMARY KEY materialized views allow materialized view master tables to be reorganized without affecting the eligibility of the materialized view for fast refresh.
ROWID materialized views should have a single master table and cannot contain any of the following :

DISTINCT or AGGREGATE functions
GROUP BY Subqueries , JOINS & SET operations
TIMING the refresh

The START WITH clause tells the database when to perform the first replication from the master table to the local base table. It should evaluate to a future point in time. The NEXT clause specifies the interval between refreshes

REFRESHING materialized view

Refreshing a materialized view synchronizes is with its master table. Oracle performs the following operations when refreshing a materialized view. In the case of a complete refresh (using dbms_mview.refresh)

sys.snap$ and sys.mlog$ are updated to reflect the time of the refresh.
The materialized base view is truncated.
All rows selected from the master table are inserted into the snapshot base table.
sys.slog$ is updated to reflect the time of the refresh.

In the case of a fast refresh, the steps are :

sys.snap$ and sys.mlog$ are updated to reflect the time of the refresh.
Rows in the materialized base view are deleted.
All rows selected from the master table are inserted into the snapshot base table.
sys.slog$ is updated to reflect the time of the refresh.
Rows that are not needed anymore for a refresh by any materialized view are deleted from the materialized view log (.MLOG$_table)

If a materialized view is being refreshed can be checked by querying the type of v$lock: if the type is JI a refresh is being performed.
The following query checks for this : select o.owner "Owner", o.object_name "Mat View", username "Username", s.sid "Sid" from v$lock l, dba_objects o, v$session s where o.object_id = l.id1 and l.type ='JI' and l.lmode = 6 and s.sid = l.sid and o.object_type = 'TABLE'
Can a mview be partitioned - Yes
Can you create an index on a mview - Yes
SNAPSHOT too old error

The ORA-01555 is caused by Oracle read consistency mechanism. If you have a long running SQL that starts at 10:30 AM, Oracle ensures that all rows are as they appeared at 10:30 AM, even if the query runs until noon!
Oracles does this by reading the "before image" of changed rows from the online undo segments. If you have lots of updates, long running SQL and too small UNDO, the ORA-01555 error will appear.

From the docs we see that the ORA-01555 error relates to insufficient undo storage or a too small value for the undo_retention parameter:

ORA-01555: snapshot too old: rollback segment number string with name "string" too small.

Cause: Rollback records needed by a reader for consistent read are overwritten by other writers.
Action: If in Automatic Undo Management mode, increase the setting of UNDO_RETENTION. Otherwise, use larger rollback segments.

The ORA-01555 snapshot too old error can be addressed by several remedies:
1. Re-schedule long-running queries when the system has less DML load.
2. Increasing the size of your rollback segment (undo) size. The ORA-01555 snapshot too old also relates to your setting for automatic undo retention.
3. Don't fetch between commits.

QUERY RE-WRITE

Query rewrite is the process of modifying a query to use the view rather than the base table.

User do not explicitly need privileges on materialized views, permission on underlying base tables are required.
Optimizer rewrites the queries in order to utilize materialized views.
GLOBAL_QUERY_REWRITE > QUERY_REWRITE system privileges allows user to enable materialized views.

Links

GTT

Definition

Applications often use some form of Temporary Data Store for processes that are to complicated to complete in a single pass. Often, these temporary stores are defined as database tables or PL/SQL tables. In Oracle 8i, the maintenance and management of temporary tables can be delegated to the server by using Global Temporary Tables.

Common Errors
Use

Improve Performance
Reporting

Global Temporary Tables have three major benefits:

Non-interference between private sets of data.
Ease of getting rid of 'scratch' data. In a heap table you either rollback, or delete it. But in a GTT, you can truncate explicitly, without affecting anyone else (or allow the implicit "truncate on commit / exit" effect to do the same thing).
Decreased redo generation as, by definition, they are non-logging.
Get your remote data into a cursor using database link
Processes your data based on your custom requirement (such as filtering/modifying).
Insert the changed data into a global temporary table.
Join the global temporary table with other local oracle tables based on your requirement.
Reduce complexity and no of joins
Put the resultant data into its final destination table in your local database or to another remote database.
Limitation:

Mixing Global Temporary Tables (GTTs) with permanent tables usually causes some grief to the CBO. It has no information about the number of rows in the GTT, and therefore guesses (badly).
Even if you analyze table .. or dbms_stats.gather_table_stats() you don't get stats on the temporary table.
Set the init parameter dynamic_sampling to at least 2 for GTTs to be sampled at run-time.

Note: All DDL's includes two implicit commits so any rows in a GTT specified with ON COMMIT DELETE ROWS will empty the table.

ON COMMIT

PRESERVE ROWS - truncate table when session ends.
DELETE ROWS - truncate data after each commit.

Links

Miscellaneous Features

If the TRUNCATE statement is issued against a temporary table, only the session specific data is truncated. There is no affect on the data of other sessions.
Data in temporary tables is stored in temp segments in the temp tablespace.
Data in temporary tables is automatically deleted at the end of the database session, even if it ends abnormally.
Indexes can be created on temporary tables. The content of the index and the scope of the index is that same as the database session.
Views can be created against temporary tables and combinations of temporary and permanent tables.
Temporary tables can have triggers associated with them.
Export and Import utilities can be used to transfer the table definitions, but no data rows are processed.
There are a number of restrictions related to temporary tables but these are version specific.

VIEWS

FORCE - Creates view even if the underlying table does not exist
WITH CHECK OPTION - Restricts data for insert statements on view
Read Only - Disallows DML on the view
Indexes on views - Only possible in MView, not possible in regular view
Create or Replace is used to add new columns to a view as it helps preserves grants.
If a table / view is dropped nothing happens to its synonym

SEQUENCE

Sequence and uses
Syntax for sequence create sequence seq1 start with 1
NOCACHE
CYCLE
NEXTVAL
CURRVAL

Packages/Procedures/Functions/Blocks

Adv of a package
Package spec without a body
Adv proc vs sql vs pkg
Encapsulation
Overloading in pkg & outside for proc
Overriding
NOCOPY Hint and Other Hints
Pipelined functions - PIPE ROW () Table ()
EXECUTE
EXEC
Call functions in sql
PRAGMA passes information to compiler uses SGA. EXCEPTION_INIT, SERIALLY REUSABLE, AUTONOMOUS TRANSACTION, RESTRICT REFERENCES (PURITY LEVEL), INLINE

A package that is marked SERIALLY_REUSABLE has the following properties:

Its package variables are meant for use only within the work boundaries, which correspond to calls to the server (either OCI call boundaries or PL/SQL RPC calls to the server).

Note:

If the application programmer makes a mistake and depends on a package variable that is set in a previous unit of work, then the application program can fail. PL/SQL cannot check for such cases.

A pool of package instantiations is kept, and whenever a "unit of work" needs this package, one of the instantiations is "reused", as follows:
- The package variables are reinitialized (for example, if the package variables have default values, then those values are reinitialized).
- The initialization code in the package body is run again.
At the "end work" boundary, cleanup is done.
- If any cursors were left open, then they are silently closed.
- Some non-reusable secondary memory is freed (such as memory for collection variables or long VARCHAR2s).
- This package instantiation is returned back to the pool of reusable instantiations kept for this package.
Serially reusable packages cannot be accessed from database triggers or other PL/SQL subprograms that are called from SQL statements. If you try, then Oracle Database generates an error.
With SERIALLY_REUSABLE packages, application developers have a way of modelling their applications to manage their memory better for scalability. Package state that they care about only for the duration of a call to the server should be captured in SERIALLY_REUSABLE packages.

SERIALLY_REUSABLE
The global memory for serially reusable packages is pooled in the System Global Area (SGA), not allocated to individual users in the User Global Area (UGA). That way, the package work area can be reused. When the call to the server ends, the memory is returned to the pool. Each time the package is reused, its public variables are initialized to their default values or to NULL.
A SERIALLY_REUSABLE pragma can appear in the specification of a bodiless package, or in both the specification and body of a package. The pragma cannot appear only in the body of a package.
Serially reusable packages cannot be accessed from database triggers or other PL/SQL subprograms that are called from SQL statements. If you try, the database generates an error.
The EXCEPTION_INIT pragma associates a user-defined exception name with an Oracle Database error number. You can intercept any Oracle Database error number and write an exception handler for it, instead of using the OTHERS handler.
The RESTRICT REFERENCES pragma asserts that a user-defined subprogram does not read or write database tables or package variables.
Subprograms that read or write database tables or package variables are difficult to optimize, because any call to the subprogram might produce different results or encounter errors.
The RESTRICT REFERENCES pragma is deprecated. Oracle recommends using DETERMINISTIC and PARALLEL_ENABLE (described in Function Declaration and Definition) instead of RESTRICT REFERENCES.
The INLINE pragma specifies that a subprogram call is, or is not, to be inlined. Inlining replaces a subprogram call (to a subprogram in the same program unit) with a copy of the called subprogram.
Example

DECLARE
   deadlock_detected EXCEPTION;
   PRAGMA EXCEPTION_INIT(deadlock_detected, -60);
BEGIN
   NULL; -- Some operation that causes an ORA-00060 error
EXCEPTION
   WHEN deadlock_detected THEN
      NULL; -- handle the error
END;
/

raise_application_error(
      error_number, message[, {TRUE | FALSE}]);

EXCEPTION
  WHEN invalid_number OR STANDARD.INVALID_NUMBER THEN
    -- handle the error
END;

DBMS_STANDARD.RAISE_APPLICATION_ERROR

The INLINE pragma specifies that a subprogram call is, or is not, to be inlined. Inlining replaces a subprogram call (to a subprogram in the same program unit) with a copy of the called subprogram.
The AUTONOMOUS_TRANSACTION pragma marks a routine as autonomous; that is, independent of the main transaction.

In this context, a routine is one of the following:

Top-level (not nested) anonymous PL/SQL block
Standalone, packaged, or nested subprogram
Method of a SQL object type
Database trigger

You cannot apply this pragma to an entire package, but you can apply it to each subprogram in a package.

You cannot apply this pragma to an entire an object type, but you can apply it to each method of a SQL object type.

Unlike an ordinary trigger, an autonomous trigger can contain transaction control statements, such as COMMIT and ROLLBACK, and can issue DDL statements (such as CREATE and DROP) through the EXECUTE IMMEDIATE statement.

In the main transaction, rolling back to a savepoint located before the call to the autonomous subprogram does not roll back the autonomous transaction. Remember, autonomous transactions are fully independent of the main transaction.

If an autonomous transaction attempts to access a resource held by the main transaction (which cannot resume until the autonomous routine exits), a deadlock can occur. The database raises an exception in the autonomous transaction, which is rolled back if the exception goes unhandled.

If you try to exit an active autonomous transaction without committing or rolling back, the database raises an exception. If the exception goes unhandled, or if the transaction ends because of some other unhandled exception, the transaction is rolled back.

AUTONOMOUS TRANSACTION defn and commit

Adv of PL/SQL Procedure over block (AUTHID, DEFINER)

INTO clause variable should be declared

RETURNING CLAUSE

HIERARCHICAL Queries

Used to navigate a hierarchy
CONNECT BY PRIOR ... START WITH

In the hr.employees table, the employee Steven King is the head of the company and has no manager. Among his employees is John Russell, who is the manager of department 80. If we update the employees table to set Russell as King's manager, we will create a loop in the data:

UPDATE employees SET manager_id = 145 WHERE employee_id = 100; SELECT last_name "Employee", LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path" FROM employees WHERE level <= 3 AND department_id = 80 START WITH last_name = 'King' CONNECT BY PRIOR employee_id = manager_id AND LEVEL <= 4; 2 3 4 5 6 7 ERROR: ORA-01436: CONNECT BY loop in user data

The NOCYCLE parameter in the CONNECT BY condition causes Oracle to return the rows in spite of the loop. The CONNECT_BY_ISCYCLE pseudocolumn shows you which rows contain the cycle:

SELECT last_name "Employee", CONNECT_BY_ISCYCLE "Cycle", LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path" FROM employees WHERE level <= 3 AND department_id = 80 START WITH last_name = 'King' CONNECT BY NOCYCLE PRIOR employee_id = manager_id AND LEVEL <= 4;

Simple Example :

SELECT
CONNECT_BY_ROOT

Examples The following example returns the last name of

each employee in department 110, each manager

above that employee in the hierarchy, the number

of levels between manager and employee,

and the path between the two:

SELECT last_name "Employee", CONNECT_BY_ROOT last_name "Manager",

LEVEL-1 "Pathlen", SYS_CONNECT_BY_PATH(last_name, '/') "Path" FROM employees WHERE LEVEL > 1 and department_id = 110 CONNECT BY PRIOR employee_id = manager_id;

last_name, employee_id, manager_id, LEVEL FROM employees

START WITH employee_id = 100 CONNECT BY PRIOR employee_id = manager_id ORDER SIBLINGS BY last_name;

In the hr.employees table, the employee Steven King is the head of the company and has no manager. Among his employees is John Russell, who is the manager of department 80. If we update the employees table to set Russell as King's manager, we will create a loop in the data:

UPDATE employees SET manager_id = 145 WHERE employee_id = 100; SELECT last_name "Employee", LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path" FROM employees WHERE level <= 3 AND department_id = 80 START WITH last_name = 'King' CONNECT BY PRIOR employee_id = manager_id AND LEVEL <= 4; 2 3 4 5 6 7 ERROR: ORA-01436: CONNECT BY loop in user data

The NOCYCLE parameter in the CONNECT BY condition causes Oracle to return the rows in spite of the loop. The CONNECT_BY_ISCYCLE pseudocolumn shows you which rows contain the cycle:

SELECT last_name "Employee", CONNECT_BY_ISCYCLE "Cycle", LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path" FROM employees WHERE level <= 3 AND department_id = 80 START WITH last_name = 'King' CONNECT BY NOCYCLE PRIOR employee_id = manager_id AND LEVEL

<= 4;

Simple Example :

SELECT last_name, employee_id, manager_id, LEVEL FROM employees

START WITH employee_id = 100 CONNECT BY PRIOR employee_id = manager_id ORDER SIBLINGS BY last_name;

INDEXES

Index is useful when 80%+ columns values are not null
FULL TABLE scan is better than Index when fetching more than 20% of the records in the table.
Covering Indexes
Invisible Indexes
Indexes Compression
Types of indexes btree vs bitmap vs function vs reverse key
Bitmap join index

Bitmap join indexes
Difference between bitmap join indexes and materialized view
In Oracle 8i performance improvements were made using materialized views to store the resulting rows of queries. The benefits of this mechanism are still relevant, but a certain subset of the queries used in a data warehouse may benefit from the use of Bitmap Join Indexes.
Oracle 11g bitmap join indexes
A bitmap join index would allow you to index (for example) the DNAME column from DEPT - against the EMP table (emp has deptno, dept has deptno->dname - instead of query and join to find out everyone in the RESEARCH department - you can bitmap join index the data and just query the emp table) to do the same in a materialized view would have you replicate the entire EMP table and the DNAME (at least) column - and then create a bitmap index on it.
A bitmap join index allows you to denormalize in the index, not in the tables.
In a Bitmap Index, each distinct value for the specified column is associated with a bitmap where each bit represents a row in the table. A '1' means that row contains that value, a '0' means it doesn't.

Bitmap Join Indexes extend this concept such that the index contains the data to support the join query, allowing the query to retrieve the data from the index rather than referencing the join tables. Since the information is compressed into a bitmap, the size of the resulting structure is significantly smaller than the corresponding materialized view.

CREATE BITMAP INDEX cust_sales_bji ON sales(customers.state) FROM sales, customers WHERE sales.cust_id = customers.cust_id; SELECT SUM(sales.dollar_amount) FROM sales, customer WHERE sales.cust_id = customer.cust_id AND customer.state = 'California'

Restrictions
Bitmap Join Indexes have the following restrictions:

Parallel DML is currently only supported on the fact table. Parallel DML on one of the participating dimension tables will mark the index as unusable.
Only one table can be updated concurrently by different transactions when using the bitmap join index.
No table can appear twice in the join.
- You cannot create a bitmap join index on an index-organized table or a temporary table.
- The columns in the index must all be columns of the dimension tables.
- The dimension table join columns must be either primary key columns or have unique constraints.
- If a dimension table has composite primary key, each column in the primary key must be part of the join.

Clusters and Indexes
What init param needs to be set for function indexes to work

Oracle Function Based Indexes
QUERY_REWRITE_ENABLED=TRUE

QUERY_REWRITE_INTEGRITY=TRUSTED

CURSORS

Def : Named Pointer to a query
Types : Static (Regular) and RefCursor (Dynamic)
RefCursors

declare type r_cursor is REF CURSOR; c_emp r_cursor; en emp.ename%type;begin open c_emp for select ename from emp; loop fetch c_emp into en; exit when c_emp%notfound; dbms_output.put_line(en); end loop; close c_emp;end;
Read more at http://www.devshed.com/c/a/Oracle/Working-with-REF-CURSOR-in-PL-SQL/#iIAso18fIwMfD18J.99
declare type r_cursor is REF CURSOR; c_emp r_cursor; er emp%rowtype;begin open c_emp for select * from emp; loop fetch c_emp into er; exit when c_emp%notfound; dbms_output.put_line(er.ename || ' - ' || er.sal); end loop; close c_emp;end;
Read more at http://www.devshed.com/c/a/Oracle/Working-with-REF-CURSOR-in-PL-SQL/#iIAso18fIwMfD18J.99
http://www.oracle-base.com/articles/misc/using-ref-cursors-to-return-recordsets.php

CREATE OR REPLACE PROCEDURE get_emp_rs (p_deptno IN emp.deptno%TYPE, p_recordset OUT SYS_REFCURSOR)
AS

```
BEGIN 
```

  OPEN p_recordset FOR
    SELECT ename,
           empno,
           deptno
    FROM   emp
    WHERE  deptno = p_deptno
 ORDER BY ename;
END GetEmpRS;

SET SERVEROUTPUT ON SIZE 1000000 DECLARE l_cursor SYS_REFCURSOR; l_ename emp.ename%TYPE; l_empno emp.empno%TYPE; l_deptno emp.deptno%TYPE; BEGIN get_emp_rs (p_deptno => 30, p_recordset => l_cursor); LOOP FETCH l_cursor INTO l_ename, l_empno, l_deptno; EXIT WHEN l_cursor%NOTFOUND; DBMS_OUTPUT.PUT_LINE(l_ename || ' | ' || l_empno || ' | ' || l_deptno); END LOOP; CLOSE l_cursor; END; /

Complex recordsets
Ref cursors in oracle plsql overview
Oracle plsql ref cursors

Steps to Use : DECLARE, OPEN, FETCH, CLOSE
Opening a already open cursor throws an error.

%ISOPEN
%FOUND
%NOTFOUND - returns false if a fetch statement returns at least one row
%ROWCOUNT

Ref Cursors

Used when you need to dynamically reassign different queries to the same named cursor

CREATE OR REPLACE Function FindCourse
( name_in IN varchar2 )
RETURN number
IS
cnumber number;
CURSOR c1
IS
SELECT course_number
from courses_tbl
where course_name = name_in;
BEGIN
open c1;
fetch c1 into cnumber;
if c1%notfound then
cnumber := 9999;
end if;
close c1;
RETURN cnumber;
END;

Oracle/PLSQL: Cursor Attributes

While dealing with cursors, you may need to determine the status of your cursor. The following is a list of the cursor attributes that you can use.

VAttribute	Explanation
%ISOPEN	- Returns TRUE if the cursor is open, FALSE if the cursor is closed.
%FOUND	- Returns INVALID_CURSOR if cursor is declared, but not open; or if cursor has been closed.- Returns NULL if cursor is open, but fetch has not been executed. - Returns TRUE if a successful fetch has been executed. - Returns FALSE if no row was returned.
%NOTFOUND	- Returns INVALID_CURSOR if cursor is declared, but not open; or if cursor has been closed.- Return NULL if cursor is open, but fetch has not been executed. - Returns FALSE if a successful fetch has been executed. - Returns TRUE if no row was returned.
%ROWCOUNT	- Returns INVALID_CURSOR if cursor is declared, but not open; or if cursor has been closed.- Returns the number of rows fetched. - The ROWCOUNT attribute doesn't give the real row count until you have iterated through the entire cursor. In other words, you shouldn't rely on this attribute to tell you how many rows are in a cursor after it is opened.

COLLECTIONS

Scalar vs composite variable records
Use of pl/sql table or associative arrays, nested tables, varrays
Associative Arrays, PL/SQL Tables, Index by Tables are the same thing
Nested Tables, VARRAYS
Bulk Collect Save Exception Limit
SET Operations
MEMBER OF
Write a program
Links

Collection enhancements in 11g release 2
Using PL/SQL Collections and Records
PL/SQL Collections
PL/SQL 101: Working with Collections
Collections
Collections
Oracle Collections
Developpez Collections
Collection Types
Advanced PL-SQL
Nested Tables
PL-SQL Collections
How to initialise a nested table collection which resides inside a plsql record
Multidimensional Collections

Table 5-1 Characteristics of PL/SQL Collection Types

Collection Type	Number of Elements	Subscript Type	Dense or Sparse	Where Created	Can Be Object Type Attribute
Associative array (or index-by table)	Unbounded	String or integer	Either	Only in PL/SQL block	No
Nested table	Unbounded	Integer	Starts dense, can become sparse	Either in PL/SQL block or at schema level	Yes
Variable-size array (varray)	Bounded	Integer	Always dense	Either in PL/SQL block or at schema level	Yes

Unbounded means that, theoretically, there is no limit to the number of elements in the collection. Actually, there are limits, but they are very high—for details, see Referencing Collection Elements.

Dense means that the collection has no gaps between elements—every element between the first and last element is defined and has a value (which can be NULL).

Referencing : collection_name (subscript)

collection_name (subscript) := expression;

Pre-defined collection types in Oracle

DBMS_DEBUG_VC2COLL
http://www.williamrobertson.net/documents/collection-types.html

select * from table (sys.ODCIVarchar2List('AAA','BBB','CCC')); COLUMN_VALUE ------------ AAA BBB CCC

PERFORMANCE TUNING

Check if the STATISTICS are uptodate
Fastest Access Path is ACCESS BY ROWID
NVL, NULL, NOT will NOT invoke index on the column
Steps in execution of a query

How Oracle Processes SQL Statements

All Oracle SQL statements must be processed the first time that they execute (unless they are cached in the library cache). and SQL execution steps include:
1. A syntax check - Are all keywords present "select . . . from", etc . .
2. A semantic check against the dictionary - Are all table names spelled correctly, etc.
3. The creation of the cost-based decision tree of possible plans
4. The generation of the lowest cost execution plan
5. Binding the execution plan - This is where the table--> tablespace --> datafile translation occurs.
6. Executing the query and fetching the rows.
Parse Phase - During the parse phase, Oracle opens the statement handle, checks whether the statement is OK (both syntactically and whether the involved objects exist and are accessible) and creates an execution plan for executing this statement. Parse call does not return an error if the statement is not syntactically correct.

Parsing can be a very expensive operation that takes a lot of resources to execute. Special problem are so called “hard parses” which happen when there is no previously parsed version of the SQL to reuse.

Once the execution plan is created, it is stored in the library cache (part of theshared_pool_size) to facilitate re-execution. There are two types of parses:
Hard parse - A new SQL statement must be parsed from scratch. (Seehard parse ratio, comparing hard parses to executes). If the database is parsing every statement that is executing, the parse to execute ratio will be close to 1% (high hard parses), often indicating non-reentrant SQL that does not use host variables (see cursor_sharing=force).

Soft parse - A reentrant SQL statement where the only unique feature are host variables. (See soft parse ratio, comparing soft parses to executes). The best-case scenario is a parse to execute ratio of 100% which would indicate an application with fully reentrant SQL that “parses SQL once and executes many times” (also see your setting for session_cached_cursors, as this effects the reentrancy of an SQL statement).
Excessive hard parsing can occur when your shared_pool_size is too small (and reentrant SQL is paged out), or when you have non-reusable SQL statements without host variables. See the cursor_sharing parameter for an easy way to make SQL reentrant and remember that you should always use host variables in you SQL so that they can be reentrant.
- Bind Phase - Once the plan is syntactically created, Oracle gathers the parameters from the client program needed for the execution. It makes the addresses of the program variables “known” to Oracle.
- Execute Phase - During the execute phase, Oracle executes the statement, reports any possible errors, and if everything is as it should be, forms the result set. Unless the SQL statement being executed is a query, this is the last step of the execution.
- Define Phase - Define is analogous to binds, only “output oriented”. The OCI define makes addresses of the output variables “known” to the Oracle process in order to make it possible to the fetch call to know where to put the output variables. The define call is frequently skipped and is largely considered unnecessary because of the automatic variables allocation in PHP.
- Fetch Phase - During the fetch phase, Oracle brings the rows of the result to the program and makes them accessible by the PHP interpreter. Once more, the define and fetch phases are relevant for queries only. The Oracle OCI interface and the PHP OCI8 module contain calls to facilitate each of those phases

SQL exeution steps

The Stages in Processing a SQL Statement

Optimization
A syntax check - Are all keywords present "select . . . from", etc . .
A semantic check against the dictionary - Are all table names spelled correctly, etc.
Object privileges are evaluated.

The creation of the cost-based decision tree of possible plans
The generation of the lowest cost execution plan
Binding the execution plan - This is where the table--> tablespace --> data file translation occurs.
Executing the query and fetching the rows.

SQL TRACE vs TK PROF vs STATSPACK vs AWR

SQL Trace
AWR : Oracle have provided many performance gathering and reporting tools over the years. Originally the UTLBSTAT/UTLESTAT scripts were used to monitor performance metrics. Oracle8i introduced the statspack functionality which Oracle9i extended. In Oracle 10g statspack has evolved into the Automatic Workload Repository (AWR).
AWR

SQL PROFILER (Trace + Timing + No of Execs) vs SQL TRACE (Just Trace)

http://oracleflash.com/44/Write-fast-and-efficient-PLSQL---DBMS_PROFILER.html
http://www.oradev.com/dbms_profiler.jsp
Before DBMS_PROFILER was introduced tuning long PL/SQL programs was a great pain. Tuning PL/SQL was done by SQL TRACE and TKPROF. Code of thousands of lines was monitored via DBMS_UTILITY.GET_TIME to measure time consumed by individual statements.

With the presence of DBMS_PROFILER this has become quite easy to monitor the performance of the PL/SQL programs and identifying the culprit statements consuming too much time while the run of the program.

SOFT PARSE vs HARD PARSE

Parse Phase - During the parse phase, Oracle opens the statement handle, checks whether the statement is OK (both syntactically and whether the involved objects exist and are accessible) and creates an execution plan for executing this statement. Parse call does not return an error if the statement is not syntactically correct.

Parsing can be a very expensive operation that takes a lot of resources to execute. Special problem are so called “hard parses” which happen when there is no previously parsed version of the SQL to reuse.
Once the execution plan is created, it is stored in the library cache (part of the shared_pool_size) to facilitate re-execution. There are two types of parses:

Hard parse - A new SQL statement must be parsed from scratch. (See hard parse ratio, comparing hard parses to executes). If the database is parsing every statement that is executing, the parse to execute ratio will be close to 1% (high hard parses), often indicating non-re-entrant SQL that does not use host variables (see cursor_sharing=force).
Soft parse - A re-entrant SQL statement where the only unique feature are host variables. (See soft parse ratio, comparing soft parses to executes). The best-case scenario is a parse to execute ratio of 100% which would indicate an application with fully re-entrant SQL that “parses SQL once and executes many times” (also see your setting for session_cached_cursors, as this effects the reentrancy of an SQL statement).

Excessive hard parsing can occur when your shared_pool_size is too small (and reentrant SQL is paged out), or when you have non-reusable SQL statements without host variables. See the cursor_sharing parameter for an easy way to make SQL reentrant and remember that you should always use host variables in you SQL so that they can be reentrant.
Bind Phase - Once the plan is syntactically created, Oracle gathers the parameters from the client program needed for the execution. It makes the addresses of the program variables “known” to Oracle.
Execute Phase - During the execute phase, Oracle executes the statement, reports any possible errors, and if everything is as it should be, forms the result set. Unless the SQL statement being executed is a query, this is the last step of the execution.
Define Phase - Define is analogous to binds, only “output oriented”. The OCI define makes addresses of the output variables “known” to the Oracle process in order to make it possible to the fetch call to know where to put the output variables. The define call is frequently skipped and is largely considered unnecessary because of the automatic variables allocation in PHP.
Fetch Phase - During the fetch phase, Oracle brings the rows of the result to the program and makes them accessible by the PHP interpreter. Once more, the define and fetch phases are relevant for queries only. The Oracle OCI interface and the PHP OCI8 module contain calls to facilitate each of those phases.

DBMS_PROFILER VS DBMS_TRACE VS DBMS_SUPPORT VS DBMS_MONITOR

http://www.oradev.com/dbms_profiler.jsp

Before DBMS_PROFILER was introduced tuning long PL/SQL programs was a great pain. Tuning PL/SQL was done by SQL TRACE and TKPROF. Code of thousands of lines was monitored via DBMS_UTILITY.GET_TIME to measure time consumed by individual statements.

With the presence of DBMS_PROFILER this has become quite easy to monitor the performance of the PL/SQL programs and identifying the culprit statements consuming too much time while the run of the program.

HASH JOIN vs MERGE JOIN vs NESTED LOOPS
FAST FULL SCAN vs SKIP SCAN

Refer Hints section

SET AUTOTRACE ON OPTIONS

AUTOTRACE OPTIONS: -
1) SET AUTOTRACE OFF - No AUTOTRACE report is generated.
This is the default. Queries are run as normal.
2) SET AUTOTRACE ON EXPLAIN - The query is run as normal,
and the AUTOTRACE report shows only the optimizer
execution path.
3) SET AUTOTRACE ON STATISTICS - The query is run as normal,
and the AUTOTRACE report shows only the SQL statement
execution statistics.
4) SET AUTOTRACE ON - The query execution takes place,
and the AUTOTRACE report includes both the optimizer
execution path and the SQL statement execution statistics.
5) SET AUTOTRACE TRACEONLY - Like SET AUTOTRACE ON, but
suppresses the printing of the query output, if any.
6) SET AUTOTRACE TRACEONLY STATISTICS - Like SET AUTOTRACE
TRACEONLY, but suppresses the display of the query plan.
It shows only the execution statistics.
7) SET AUTOTRACE TRACEONLY EXPLAIN - Like SET AUTOTACE
TRACEONLY, but suppresses the display of the execution
statistics, showing only the query plan. This setting
does not actually execute the query. It only parses
and explains the query.

SQL*LOADER DIRECT ACCESS PATH v/s CONVENTIONAL PATH

Initial Loading Full data
Lazy Loading
Loader Modes
Conventional path load (the default) uses the SQL INSERT statement and a bind array buffer to load data into database tables. This method is used by all Oracle tools and applications.

When SQL*Loader performs a conventional path load, it competes equally with all other processes for buffer resources. This can slow the load significantly. Extra overhead is added as SQL statements are generated, passed to Oracle, and executed.
The Oracle database looks for partially filled blocks and attempts to fill them on each insert. Although appropriate during normal use, this can slow bulk loads dramatically.
Direct Path Loading : Instead of filling a bind array buffer and passing it to the Oracle database with a SQL INSERT statement, a direct path load uses the direct path API to pass the data to be loaded to the load engine in the server. The load engine builds a column array structure from the data passed to it.
The direct path load engine uses the column array structure to format Oracle data blocks and build index keys. The newly formatted database blocks are written directly to the database (multiple blocks per I/O request using asynchronous writes if the host platform supports asynchronous I/O).

Internally, multiple buffers are used for the formatted blocks. While one buffer is being filled, one or more buffers are being written if asynchronous I/O is available on the host platform. Overlapping computation with I/O increases load performance.

PARTITION EXCHANGE
TRANSPORTABLE TABLESPACES
REWRITING OUTER JOINS USING INLINE VIEWS CORRELATED QUERIES
FOR ALL and BULK COLLECT, INSERT, UPDATE, INTO COLLECTION

http://psoug.org/reference/array_processing.html
DECLARE
CURSOR s_cur IS
SELECT *
FROM servers;
TYPE fetch_array IS TABLE OF s_cur%ROWTYPE;
s_array fetch_array;
BEGIN
  OPEN s_cur;
  LOOP
    FETCH s_cur BULK COLLECT INTO s_array LIMIT 1000;
  FORALL i IN 1..s_array.COUNT
    INSERT INTO servers2 VALUES s_array(i);
  EXIT WHEN s_cur%NOTFOUND;
  END LOOP;
  CLOSE s_cur;
  COMMIT;
END;
DECLARE
TYPE myarray IS TABLE OF servers2.srvr_id%TYPE
INDEX BY BINARY_INTEGER;
d_array myarray;
BEGIN
  d_array(1) := 608;
  d_array(2) := 610;
  d_array(3) := 612;
  FORALL i IN d_array.FIRST .. d_array.LAST
  UPDATE servers2
SET srvr_id = 0
  WHERE srvr_id = d_array(i);
  COMMIT;
END;
/

Using INDICES OF and VALUESOF with Non-Consecutive Index Values
FORALL i IN INDICES OF cust_tab
INSERT INTO valid_orders
(cust_name, amount)
VALUES
(cust_tab(i), amount_tab(i));
FORALL i IN VALUES OF rejected_order_tab
INSERT INTO rejected_orders VALUES (cust_tab(i), amount_tab(i));
DECLARE
-- collections to hold a set of customer names and amounts
SUBTYPE cust_name IS valid_orders.cust_name%TYPE;
TYPE cust_typ IS TABLE OF cust_name;
cust_tab cust_typ;
SUBTYPE order_amount IS valid_orders.amount%TYPE;
TYPE amount_typ IS TABLE OF NUMBER;
amount_tab amount_typ;
-- collections to point into the CUST_TAB collection.
TYPE index_pointer_t IS TABLE OF PLS_INTEGER;
big_order_tab index_pointer_t := index_pointer_t();
rejected_order_tab index_pointer_t := index_pointer_t();
PROCEDURE setup_data
IS
BEGIN
  -- Set up sample order data, with some invalid and 'big' orders
cust_tab := cust_typ('Company1', 'Company2', 'Company3',
'Company4', 'Company5');
amount_tab := amount_typ(5000.01, 0, 150.25, 4000.00,NULL);
END setup_data;
BEGIN
setup_data;
dbms_output.put_line('--- Original order data ---');
  FOR i IN 1..cust_tab.LAST
  LOOP
    dbms_output.put_line('Cust#' || i || ', '|| cust_tab(i) ||
    ': $'||amount_tab(i));
  END LOOP;
  -- Delete invalid orders (where amount is null or 0)
  FOR i IN 1..cust_tab.LAST
  LOOP
    IF amount_tab(i) is null or amount_tab(i) = 0
THEN
      cust_tab.delete(i);
      amount_tab.delete(i);
    END IF;
  END LOOP;
dbms_output.put_line('--Data with deleted invalid orders--');
  FOR i IN 1..cust_tab.LAST LOOP
    IF cust_tab.EXISTS(i) THEN
      dbms_output.put_line('Cust#' || i || ', ' || cust_tab(i) ||
      ': $'||amount_tab(i));
    END IF;
  END LOOP;
  -- Since the subscripts of our collections are not consecutive,
-- we use use FORRALL...INDICES OF to iterate the subscripts
  FORALL i IN INDICES OF cust_tab
  INSERT INTO valid_orders
(cust_name, amount)
  VALUES
(cust_tab(i), amount_tab(i));
  -- Now let's process the order data differently extracting
-- 2 subsets and storing each subset in a different table.
setup_data; -- Reinitialize the CUST_TAB and AMOUNT_TAB collections
  FOR i IN cust_tab.FIRST .. cust_tab.LAST
  LOOP
    IF amount_tab(i) IS NULL OR amount_tab(i) = 0 THEN
      -- add a new element to the collection
      rejected_order_tab.EXTEND;
      -- record original collection subscript
      rejected_order_tab(rejected_order_tab.LAST) := i;
    END IF;
    IF amount_tab(i) > 2000 THEN
      -- Add a new element to the collection
      big_order_tab.EXTEND;
      -- record original collection subscript
      big_order_tab(big_order_tab.LAST) := i;
    END IF;
  END LOOP;
-- run one DML statement on one subset of elements,
-- and another DML statement on a different subset.
  FORALL i IN VALUES OF rejected_order_tab
  INSERT INTO rejected_orders VALUES (cust_tab(i), amount_tab(i));
  FORALL i IN VALUES OF big_order_tab
  INSERT INTO big_orders VALUES (cust_tab(i), amount_tab(i));
  COMMIT;
END;
/
BULK BIND, BULK EXCEPTION, BULK ROWCOUNT

FORALL index_name IN lower_boundary .. upper_boundary
sql_statement
SAVE EXCEPTIONS;
FORALL index_name IN INDICES OF collection
[BETWEEN lower_boundary AND upper_boundary]
sql_statement
SAVE EXCEPTIONS;
FORALL index_name IN INDICES OF collection
VALUES OF index_collection
sql_statement
SAVE EXCEPTIONS;

P.T. FULL TABLE SCANS cost
Diff between ESTIMATE STATISTICS AND COMPUTE STATISTICS

Compute : Computes Statistics for whole Table(for all rows).
Estimate : Computes Statistics on sample of rows over the table.
- Be default, its 1064 rows.
- You can specify rows in numbers or in percentage of total rows.
- If the number or percentage of rows exceeds 50%, Statistics will be computed on all rows in Table.

When is the Cost of the query in the explain plan NULL - When its RULE BASED OPT
1 Trillion records INSERT USER MERGE / PARTITION EXCHANGE / COPY / SQL LOADER / EXTERNAL TABLE / EXPDBIMPDB

10 Billion records INSERT ALL EXCEPT ONE CREATE TABLE
Difference between DBMS_STATS and ANALYZE

Analyze versus dbmsstats
TUNING
Analyze vs dbmsstats
Cost based optimizer is preferred method for oracle optimizer. In order to make good use of the CBO, you need to create accurate statistics. Prior to oracle8i, we use ANALYZE command to gather statistics.

DBMS_STATS package is introduced in oracle8i. Since Oracle8i, Oracle highly recommeds to use DBMS_STATS instead of ANALYZE command. This article is written in oracle10g. I am going to address below topics in this thread....

1. Why oracle recommends to use DBMS_STATS package?
2. What are the advantages of DBMS_STATS compared to ANALYZE?
3. How do we use DBMS_STATS package to analyze the table?
4. What are new features in each version for DBMS_STATS?

Why oracle recommends to use DBMS_STATS since Oracle8i?

1. Gathering statistics can be done in Parallel. This option is not available in ANALYZE command.

2. It is used to collect the stale statistics. I discussed about collecting stale statistics in another topic. Please refer stale statistics to know more about collecting stale statistics.

3. DBMS_STATS is a PL/SQL package. So it is easy to call. But ANALYZE does not.

4. It is used to collect statistics for external tables. But ANALYZE does not.

5. DBMS_STATS used to collect system statistics. But ANALYZE does not.

6. Some time, ANALYZE does not produce accurate statistics. But DBMS_STATS does.

7. We cannot use ANALYZE command to gather statistics for partition or sub partition level. But we can use DBMS_STATS to analyze any specific partition or sub partition. This is especially useful for partition table. We do not need to analyze the Historical data whenever we refresh the current partition.

8. We can transfer statistics from one DB to another DB when we collected statistics through DBMS_STATS. But it can not be done when we use ANALYZE command to collect the statistics. Please refer statistics transfer to know more about transferring statistics.
Example for collecting statistics on table:

DBMS_STATS.GATHER_TABLE_STATS(
OWNNAME => 'TANDEB',
TABNAME => 'CUSTOMER',
PARTNAME => 'PART092009'
GRANULARITY => 'PARTITION',
ESTIMATE_PERCENT => 10,
METHOD_OPT => 'FOR ALL COLUMNS SIZE 1',
CASCADE => TRUE,
NO_INVALIDATE => TRUE);

Example for collecting statistics on Schema:

DBMS_STATS.GATHER_SCHEMA_STATS(
OWNNAME => 'SCOMPPRD',
ESTIMATE_PERCENT => 10,
METHOD_OPT => 'FOR ALL COLUMNS SIZE 1',
OPTIONS => 'GATHER',
CASCADE => TRUE,
NO_VALIDATE => TRUE);

Example for collecting system statistics:

DBMS_STATS.GATHER_SYSTEM_STATS(
GATHERING_MODE => 'INTERVAL',
INTERVAL => 10);

Example for collecting database statistics:

DBMS_STATS.GATHER_DATABASE_STATS(
ESTIMATE_PERCENT => 10,
METHOD_OPT => 'FOR ALL COLUMNS SIZE 1',
CASCADE => TRUE,
NO_VALIDATE => TRUE,
GATHER_SYS => FALSE)

HINTS

Join Order ORDERED vs LEADING

/*+ LEADING */ Hint – specifies the set of tables to be used as the prefix in the execution plan.
- The "leading" hint is ignored if the tables specified cannot be joined first in the order specified
- If you specify two or more conflicting LEADING hints – all of them are ignored
- The ORDERED hint overrides all LEADING hints
/*+ ORDERED */ Hint – Oracle joins tables in the order in which they appear in the FROM clause

The optimizer normally chooses the order in which to join the tables, but it's time-consuming and wrong if you have bad CBO stats (especially histograms)
You may want to specify the "ordered" hint if you know something about the number of rows selected from each table that the optimizer does not

Optimizer Hints in Oracle 11g and hint overkill
Hints Ref
Index scans
Hints

Predicate Pushing

Predicates

Hints of Acceptability
INDEX_SS
An index skip scan uses logical subindexes of a composite index. An index skip scan uses logical subindexes of a composite index. The index skip scan was introduced to allow Oracle to “skip” leading-edge predicates in a multi-column index. You can force an index skip scan with the /*+ index_ss */ hint. For example, consider the following concatenated index on a super-low cardinality column, following by a very selective column:
create index sex_emp_id on emp (sex, emp_id);

Prior to Oracle9i, this index could only be used with both sex and emp_id were present in the SQL query, or when the sex column was specified. The following query would not be able to use the concatenated index:

select emp_id from emp where emp_id = 123;

The Oracle 9i skip scan execution plan allows for our concatenated index to be used, even though sex is not specified in the SQL query WHERE clause. The index skip scan promises that there is no need to build a second index on the emp_id column, since it is technically redundant to the multi-column index. Oracle acknowledges that the index skip scan is not as fast as direct index lookup, but states that the index skip scan is faster than a full-table scan.

INDEX_FFS

The INDEX_FFS hint causes a fast full index scan to be performed rather than a full table scan.

A fast full scan is a full index scan in which the database reads all index blocks in no particular order. The database accesses the data in the index itself, without accessing the table.

This is the optimal access method when all of the information required to answer a query is contained in indexed columns. Thus we will start the demo by first finding indexed columns and then building a query that uses only those columns. And, to make the demo as reliable as possible only those where the column is the first column in the index.

INDEX_COMBINE

The INDEX_COMBINE hint explicitly chooses a bitmap access path for the table. If no indexes are given as arguments for the INDEX_COMBINE hint, then the optimizer uses whatever Boolean combination of bitmap indexes has the best cost estimate for the table. If certain indexes are given as arguments, then the optimizer tries to use some Boolean combination of those particular bitmap indexes.

INDEX_JOIN

The INDEX_JOIN hint explicitly instructs the optimizer to use an index join as an access path. For the hint to have a positive effect, a sufficiently small number of indexes must exist that contain all the columns required to resolve the query.

APPEND - direct-path mode (enable parallel mode)

The APPEND hint lets you enable direct-path INSERT if your database is running in serial mode. Your database is in serial mode if you are not using Enterprise Edition. Conventional INSERT is the default in serial mode, and direct-path INSERT is the default in parallel mode.

In direct-path INSERT, data is appended to the end of the table, rather than using existing space currently allocated to the table. As a result, direct-path INSERT can be considerably faster than conventional INSERT.

NOAPPEND - conventional path mode (disable parallel mode)

The NOAPPEND hint enables conventional INSERT by disabling parallel mode for the duration of the INSERT statement. (Conventional INSERT is the default in serial mode, and direct-path INSERT is the default in parallel mode).

CACHE

The CACHE hint specifies that the blocks retrieved for the table are placed at the most recently used end of the LRU list in the buffer cache when a full table scan is performed. This option is useful for small lookup tables.

Optimization Goals and Approaches (2)	Access Path Hints (17)	Other (20)	Join Operation (7)
ALL_ROWS FIRST_ROWS ~~RULE~~	CLUSTER FULL HASH INDEX NO_INDEX INDEX_ASC INDEX_DESC INDEX_COMBINE INDEX_JOIN INDEX_FFS INDEX_SS INDEX_SS_ASC INDEX_SS_DESC NATIVE_FULL_OUTER_JOIN NO_NATIVE_FULL_OUTER_JOIN NO_INDEX_FFS NO_INDEX_SS	APPEND NOAPPEND CACHE NOCACHE CURSOR_SHARING_EXACT DRIVING_SITE DYNAMIC_SAMPLING MODEL_MIN_ANALYSIS MONITOR NO_MONITOR OPT_PARAM PUSH_PRED NO_PUSH_PRED PUSH_SUBQ NO_PUSH_SUBQ PX_JOIN_FILTER NO_PX_JOIN_FILTER QB_NAME RESULT_CACHE NO_RESULT_CACHE	USE_HASH NO_USE_HASH USE_MERGE NO_USE_MERGE USE_NL USE_NL_WITH_INDEX NO_USE_NL
Join Order (2)	Query Transformation (13)	XML (2)	Parallel Execution (5)
ORDERED LEADING	FACT NO_FACT MERGE NO_MERGE NO_EXPAND USE_CONCAT REWRITE NO_REWRITE NOREWRITE* UNNEST NO_UNNEST STAR_TRANSFORMATION NO_STAR_TRANSFORMATION NO_QUERY_TRANSFORMATION	NO_XMLINDEX_REWRITE NO_XML_QUERY_REWRITE	PARALLEL NOPARALLEL* NO_PARALLEL PARALLEL_INDEX NO_PARALLEL_INDEX NOPARALLEL_INDEX* PQ_DISTRIBUTE

BIND Variables

Oracle bind variables

11g Oracle's Buffer Cache

BIND Variable PEEKING 11g

Bind variable peeking drives me nuts
Bind variable peeking in 11g
Bind variable peeking execution plan inefficiency
Bind Variable Peeking Tips
Bind Variable Peeking by setting the _OPTIM_PEEK_USER_BINDS parameter

ADAPTIVE CURSOR SHARING IN 11G

11g Adaptive Cursor Sharing

SHARING, BIND Variable PEEKING and HISTOGRAMS

Links

Query to check long running queries

select vlo.*,vs.* from v$session_longops vlo, v$sql vs where vlo.sql_hash_value = vs.hash_value and vlo.sql_address = vs.address and vs.child_number=0 and vlo.sofar/vlo.totalwork*100<>100

SCHEDULING

DBMS_JOB
DBMS_SCHEDULER

TRIGGERS

12 TYPES of triggers before, after, row level, statement level, system, non system
Call Sequence of Execution of triggers
AUTONOMOUS TRANSACTION in triggers
INSTEAD OF TRIGGER = trigger on view
Create trigger to add user and source code to log table
Call PROC in triggers to get over max trigger size of 100K
How to avoid mutating trigger error change it to a statement level trigger, use view, use GTT
We can have a max of 12 triggers on a table

PARTITIONING

Partitions (how to specify)
You cannot update partition key
Horizontal vs Vertical partitioning, range, list, hash
Sub partitions
Global Indexes
Local Indexes (Always create local indexes for partitioned tables)
Global vs Local partitioned index
Object partitioning in Oracle
Partition by analytical function
Partitioned tables and indexes
Partition Pruning

Partition pruning improves response time
Partition pruning tips

Beginning with Oracle 11g, partition pruning uses bloom filtering instead of subquery pruning. A bloom filter essentially tests if an element is a member of a set or not. In deciding whether to use partition pruning, a bloom filter uses partition pruning whenever a partitioned object is detected in the SQL statement. This enhances the performance of partition pruning because bloom filtering does not require additional resources. Also, it is not evaluated as a cost based decision. Instead, bloom filtering is constantly active as well as automatically activated for any join with a partitioned object. This transparent enhancement improves the performance of partition pruning.

Managing Partitioned Tables and Indexes in Oracle 10g

ALTER TABLE Maintenance Operations for Table Partitions

Maintenance Operation	Range	Hash	List	Composite: Range/Hash	Composite: Range/List
Adding Partitions	`ADD PARTITION`	`ADD PARTITION`	`ADD PARTITION`	`ADD PARTITION` `MODIFY PARTITION ... ADD SUBPARTITION`	`ADD PARTITION` `MODIFY PARTITION ... ADD SUBPARTITION`
Coalescing Partitions	n/a	`COALESCE PARTITION`	n/a	`MODIFY PARTITION ... COALESCE SUBPARTITION`	n/a
Dropping Partitions	`DROP PARTITION`	n/a	`DROP PARTITION`	`DROP PARTITION`	`DROP PARTITION` `DROP SUBPARTITION`
Exchanging Partitions	`EXCHANGE PARTITION`	`EXCHANGE PARTITION`	`EXCHANGE PARTITION`	`EXCHANGE PARTITION` `EXCHANGE SUBPARTITION`	`EXCHANGE PARTITION` `EXCHANGE SUBPARTITION`
Merging Partitions	`MERGE PARTITIONS`	n/a	`MERGE PARTITIONS`	`MERGE PARTITIONS`	`MERGE PARTITIONS` `MERGE SUBPARTITIONS`
Modifying Default Attributes	`MODIFY DEFAULT ATTRIBUTES`	`MODIFY DEFAULT ATTRIBUTES`	`MODIFY DEFAULT ATTRIBUTES`	`MODIFY DEFAULT ATTRIBUTES` `MODIFY DEFAULT ATTRIBUTES FOR PARTITION`	`MODIFY DEFAULT ATTRIBUTES` `MODIFY DEFAULT ATTRIBUTES FOR PARTITION`
Modifying Real Attributes of Partitions	`MODIFY PARTITION`	`MODIFY PARTITION`	`MODIFY PARTITION`	`MODIFY PARTITION` `MODIFY SUBPARTITION`	`MODIFY PARTITION` `MODIFY SUBPARTITION`
Modifying List Partitions: Adding Values	n/a	n/a	`MODIFY PARTITION...ADD VALUES`	n/a	`MODIFY SUBPARTITION ... ADD VALUES`
Modifying List Partitions: Dropping Values	n/a	n/a	`MODIFY PARTITION...DROP VALUES`	n/a	`MODIFY SUBPARTITION ... DROP VALUES`
Modifying a Subpartition Template	n/a	n/a	n/a	`SET SUBPARTITION TEMPLATE`	`SET SUBPARTITION TEMPLATE`
Moving Partitions	`MOVE PARTITION`	`MOVE PARTITION`	`MOVE PARTITION`	`MOVE SUBPARTITION`	`MOVE SUBPARTITION`
Renaming Partitions	`RENAME PARTITION`	`RENAME PARTITION`	`RENAME PARTITION`	`RENAME PARTITION` `RENAME SUBPARTITION`	`RENAME PARTITION` `RENAME SUBPARTITION`
SplittingPartitions	`SPLIT PARTITION`	n/a	`SPLIT PARTITION`	`SPLIT PARTITION`	`SPLIT PARTITION` `SPLIT SUBPARTITION`
Truncating Partitions	`TRUNCATE PARTITION`	`TRUNCATE PARTITION`	`TRUNCATE PARTITION`	`TRUNCATE PARTITION` `TRUNCATE SUBPARTITION`	`TRUNCATE PARTITION` `TRUNCATE SUBPARTITION`

EXCEPTION HANDLING

Common Errors : http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/errors.htm
Exception Types

Named like Oracle Predefined Error List

TOO_MANY_ROWS
NO_DATA_FOUND
DUP_VAL_ON_INDEX
VALUE_ERROR
ZERO_DIVIDE

Unnamed
User Defined

SQLCODE and SQLERRM
BULK COLLECT ..... SAVE EXCEPTIONS in which table it is stored. Can you specify your own table ? No
RAISE_APPLICATION_ERROR
DBMS_UTILITY.FORMAT_ERROR_STACK
Links

Handling PL/SQL Errors

ANALYTICAL FUNCTIONS

CONNECT BY START WITH ... PARTITION BY SYNTAX
COLASE
LEAD
LAG
CUBE vs ROLLUP
Show rows in a single column
http://www.oracle-base.com/articles/misc/analytic-functions.php

Linear Regression Functions
	20.1.Introduction( 1 )	20.14.Stats_Mw_Test( 4 )
	20.2.REGR_AVGX( 2 )	20.15.STATS_ONE_WAY_ANOVA( 8 )
	20.3.REGR_AVGY( 1 )	20.16.STATS_T_TEST_INDEP( 4 )
	20.4.REGR_COUNT( 2 )	20.17.STATS_T_TEST_INDEPU( 4 )
	20.5.REGR_INTERCEPT( 1 )	20.18.STATS_T_TEST_ONE( 4 )
	20.6.REGR_R2( 1 )	20.19.STATS_T_TEST_PAIRED( 4 )
	20.7.REGR_SLOPE( 1 )	20.20.STATS_WSR_TEST( 4 )
	20.8.REGR_SXX( 1 )	20.21.STDDEV( 1 )
	20.9.REGR_SXY( 1 )	20.22.STDDEV_POP( 1 )
	20.10.REGR_SYY( 2 )	20.23.STDDEV_SAMP( 1 )
	20.11.STATS_CROSSTAB( 7 )	20.24.VARIANCE( 1 )
	20.12.STATS_F_TEST( 5 )	20.25.VAR_POP( 1 )
	20.13.STATS_MODE( 1 )	20.26.VAR_SAMP( 1 )

Statistical Functions
	19.1.AVG( 1 )	19.5.COVAR_POP( 1 )
	19.2.Corr( 1 )	19.6.COVAR_SAMP( 1 )
	19.3.Corr_K( 3 )	19.7.Cume_Dist( 1 )
	19.4.Corr_S( 3 )	19.8.MEDIAN( 1 )

Analytical Functions
16.1.Introduction( 6 ) 16.18.NTILE( 7 )
16.2.ROWNUM( 4 ) 16.19.Nulls First Last( 9 )
16.3.ROW_NUMBER( 5 ) 16.20.OVER( 11 )
16.4.RANK( 7 ) 16.21.PERCENTILE_CONT( 3 )
16.5.DENSE_RANK( 8 ) 16.22.PERCENTILE_DISC( 1 )
16.6.PERCENT_RANK( 4 ) 16.23.RANGE( 4 )
16.7.PARTITION_BY( 7 ) 16.24.ROLLUP( 12 )
16.8.CUBE( 8 ) 16.25.MODEL( 1 )
16.9.CUME_DIST( 4 ) 16.26.SPREADSHEET( 2 )
16.10.FIRST_VALUE( 1 ) 16.27.Top( 1 )
16.11.LAST_VALUE( 1 ) 16.28.UNBOUNDED( 2 )
16.12.Group by( 1 ) 16.29.Window Functions( 11 )
16.13.GROUPING( 12 ) 16.30.HAVING( 1 )
16.14.GROUPING_ID( 3 ) 16.31.Order( 1 )
16.15.First( 1 ) 16.32.Table Joins( 2 )
16.16.Last( 1 ) 16.33.Where( 1 )
16.17.LEAD LAG( 5 )

System Packages
	31.1.DBMS_ALERT( 7 )	31.22.dbms_rls( 5 )
	31.2.dbms_application_info( 7 )	31.23.dbms_rowid( 11 )
	31.3.DBMS_AQ( 1 )	31.24.dbms_session( 4 )
	31.4.dbms_aqadm( 2 )	31.25.dbms_space( 2 )
	31.5.dbms_crypto( 7 )	31.26.DBMS_SQL( 20 )
	31.6.DBMS_DB_VERSION( 1 )	31.27.DBMS_STATS( 10 )
	31.7.DBMS_DDL( 1 )	31.28.DBMS_TRANSACTION( 1 )
	31.8.DBMS_DEBUG( 1 )	31.29.DBMS_TYPES( 1 )
	31.9.dbms_fga( 1 )	31.30.dbms_utility( 13 )
	31.10.dbms_java( 1 )	31.31.dbms_wm( 1 )
	31.11.DBMS_JOB( 8 )	31.32.dbms_xmlschema( 1 )
	31.12.DBMS_LOB( 9 )	31.33.File Read with UTL_FILE( 4 )
	31.13.dbms_lock( 2 )	31.34.owa_util( 1 )
	31.14.dbms_logmnr( 4 )	31.35.File Write with UTL_FILE( 4 )
	31.15.dbms_metadata( 2 )	31.36.system_privilege_map( 1 )
	31.16.dbms_obfuscation_toolkit( 3 )	31.37.HTP( 11 )
	31.17.DBMS_OUTPUT( 11 )	31.38.UTL_FILE( 9 )
	31.18.DBMS_PIPE( 11 )	31.39.utl_raw( 6 )
	31.19.dbms_preprocessor( 2 )	31.40.utl_smtp( 2 )
	31.20.DBMS_PROFILER( 7 )	31.41.utl_tcp( 1 )
	31.21.DBMS_RANDOM( 2 )

TRANSPOSING DATA

NORMALIZATION - UNION
DENORMALIZATION - CASE
LISTAGG

ORACLE DBMS_**** and Other UTILITIES

Built in packages
DBMS_TRACE, DBMS_PROFILER, DBMS_SUPPORT, DBMS_MONITOR
WRAP command encrypts PL/SQL code.
ADIDENT utility is used to find the version of any file
DBMS_ALERT - Package DBMS_ALERT lets you use database triggers to alert an application when specific database values change. The alerts are transaction based and asynchronous (that is, they operate independently of any timing mechanism). For example, a company might use this package to update the value of its investment portfolio as new stock and bond quotes arrive.
DBMS_PIPE - Package DBMS_PIPE allows different sessions to communicate over named pipes. (A pipe is an area of memory used by one process to pass information to another.) You can use the procedures PACK_MESSAGE and SEND_MESSAGE to pack a message into a pipe, then send it to another session in the same instance or to a waiting application such as a UNIX program.
At the other end of the pipe, you can use the procedures RECEIVE_MESSAGE and UNPACK_MESSAGE to receive and unpack (read) the message. Named pipes are useful in many ways. For example, you can write a C program to collect data, then send it through pipes to stored procedures in an Oracle database.
UTL_FILE -Package UTL_FILE lets PL/SQL programs read and write operating system (OS) text files. It provides a restricted version of standard OS stream file I/O, including open, put, get, and close operations.
When you want to read or write a text file, you call the function FOPEN, which returns a file handle for use in subsequent procedure calls. For example, the procedure PUT_LINE writes a text string and line terminator to an open file, and the procedure GET_LINE reads a line of text from an open file into an output buffer.

GRANTS AND PRIVILEGES

Grant granter rights (WITH GRANT OPTION)
Roles help grant privileges to a group of users

CONSTRAINTS (alter table enable /disable constraint)

DISABLE CONSTRAINT
DISABLE CONSTRAINT NO CHECK
DISABLE ALL CONSTRAINT
ENABLE NOVALIDATE
We CANNOT change not DIFFERABLE CONSTRAINT

COMPILATION

When will an object become invalid
Conditional Compilation in 11g

Audit

Select d.name,l.sessions_current,s.value,l.sessions_highwater from v$database d, v$sysstats s, v$license l

Debugging and code maintainability :

Add RETURN or EXIT till the point you want to run
Prefer to use GTT over Collections
Need to find missing records in join use outer join in ANSI syntax

Impact Analysis and Dependencies

all_dependencies or dba_dependencies
Table for Indirect Dependencies

DBLINK what is a remote database a node

Shutdown / Startup Options

Normal
Immediate
Regular

Standards

Database Object Naming Rules : Max Limit is 30 Bytes/ Chars except DB Name which is 8 Bytes and DB Links which are 128 Bytes

Puzzles/Tricks

Num to word

to_char(to_date(col,'j'),'jsp')

Convert multiple rows to a single column

SELECT SUBSTR (SYS_CONNECT_BY_PATH (NAME , ','), 2) FRUITS_LIST
FROM (SELECT NAME , ROW_NUMBER () OVER (ORDER BY NAME ) RN,
COUNT (*) OVER () CNT
FROM FRUITS)
WHERE RN = CNT
START WITH RN = 1
CONNECT BY RN = PRIOR RN + 1;

Oracle tips tricks
select split('123,45,6,7',1,',') from dual

Unix and Oracle

Common Unix Commands & Usage
ksh shell script 'helloworld' after 25 secs

Additional Reference :

Links to popular blogs

Oracle SQL Join Factorization Transformation (JFT) Tips

TechSpace

Total Pageviews

Friday, June 7, 2013

Ralph Kimball Design Tips

The Soul of the Data Warehouse, Part Two: Drilling Across

Implementing Drill-Across

Amazing Magic

Centralizing vs. Conforming

RESOURCES

The Mini Dimension with “Current” Overwrite

Type 2 with “Current” Overwrite

Type 2 with Natural Keys in the Fact Table

Series of Type 3 Attributes

Balance Power against Ease of Use

Thursday, January 17, 2013

Oracle Interview Questions - Part I

Transaction

Every transaction has an SCN. For example, if a transaction updates a row, then the database records the SCN at which this update occurred. Other modifications in this transaction have the same SCN. When a transaction commits, the database records an SCN for this commit.

Process

Table 7-2 Nondefault Locking Behavior

Table 7-3 Summary of ANSI Isolation Levels

Table 7-4 Read Committed Versus Serializable Transaction

Pre-defined collection types in Oracle

DBMS_DEBUG_VC2COLL

The Stages in Processing a SQL Statement

The `APPEND` hint lets you enable direct-path `INSERT` if your database is running in serial mode. Your database is in serial mode if you are not using Enterprise Edition. Conventional `INSERT` is the default in serial mode, and direct-path `INSERT` is the default in parallel mode.

The `NOAPPEND` hint enables conventional `INSERT` by disabling parallel mode for the duration of the `INSERT` statement. (Conventional `INSERT` is the default in serial mode, and direct-path `INSERT` is the default in parallel mode).

Total Pageviews

Friday, June 7, 2013

Ralph Kimball Design Tips

The Soul of the Data Warehouse, Part Two: Drilling Across

Implementing Drill-Across

Amazing Magic

Centralizing vs. Conforming

RESOURCES

The Mini Dimension with “Current” Overwrite

Type 2 with “Current” Overwrite

Type 2 with Natural Keys in the Fact Table

Series of Type 3 Attributes

Balance Power against Ease of Use

Thursday, January 17, 2013

Oracle Interview Questions - Part I

Transaction

Every transaction has an SCN. For example, if a transaction updates a row, then the database records the SCN at which this update occurred. Other modifications in this transaction have the same SCN. When a transaction commits, the database records an SCN for this commit.

Process

Table 7-2 Nondefault Locking Behavior

Table 7-3 Summary of ANSI Isolation Levels

Table 7-4 Read Committed Versus Serializable Transaction

Pre-defined collection types in Oracle

DBMS_DEBUG_VC2COLL

The Stages in Processing a SQL Statement

The APPEND hint lets you enable direct-path INSERT if your database is running in serial mode. Your database is in serial mode if you are not using Enterprise Edition. Conventional INSERT is the default in serial mode, and direct-path INSERT is the default in parallel mode.

The NOAPPEND hint enables conventional INSERT by disabling parallel mode for the duration of the INSERT statement. (Conventional INSERT is the default in serial mode, and direct-path INSERT is the default in parallel mode).

The `APPEND` hint lets you enable direct-path `INSERT` if your database is running in serial mode. Your database is in serial mode if you are not using Enterprise Edition. Conventional `INSERT` is the default in serial mode, and direct-path `INSERT` is the default in parallel mode.

The `NOAPPEND` hint enables conventional `INSERT` by disabling parallel mode for the duration of the `INSERT` statement. (Conventional `INSERT` is the default in serial mode, and direct-path `INSERT` is the default in parallel mode).