Enqueues – are shared memory structures (locks) that serialize access to database resources. They can be associated with a session or transaction. Enqueue names are displayed in the LOCK_TYPE column of the DBA_LOCK and DBA_LOCK_INTERNAL data dictionary views.
In case your session is slow you can always check what it’s waiting for in v$session table.
This article presents how you can suggest join order for tables. It’s recommended to read following article:
This article presents hints which can be used to force Oracle to use specific join operation between two tables. This article is not trying to prove which joins is better but just try to show how to force Oracle database to “play game” as we want even if our tactic is wrong.
It’s recommended to read following article before it:
This article presents how result cache can be used together with PL/SQL.
it’s important to read following article
Let’s prepare simple function that is using result cache functionality
create or replace function sleep_fnc(id number) return number result_cache is begin dbms_lock.sleep(5); return 5; end; / FUNCTION SLEEP_FNC compiled
Before reading this article I suggest to read article about deterministic functions
SQL Result cache are especially usefull for warhouses were users read many rows and return few rows. Returned rows are saved in SGA and can be reused if the same query is executed once again in the same or other session. Cached SQL results become stale when data in the objects being accessed by the query is modified.
NOTE: for RAC configuration each instance has got own private result cache and can’t be used by other instances. However invalidations work accross instances.
Database Resident Connection Pooling (DRCP) is pool of connections. Establishing connection is very expensive process so DRCP solves it by sharing such connections between users. It’s important for “stateless” applications which are establishing connections very often (Web based).
DRCP is available to all database clients that use the OCI driver including C, C++, and PHP.
This article presents and tries to explain joins between two tables that can be encountered in Oracle: HASH, NESTED, MERGE
---------------------------------------------- | Id | Operation | Name ---------------------------------------------- | 0 | SELECT STATEMENT | | 1 | HASH JOIN | | 2 | TABLE ACCESS FULL| <outer table> <- hash table in memory | 3 | TABLE ACCESS FULL| <inner table> -----------------------------------------------
This kind of join is the most popular in warehouses and enables to join very effectively large tables, sets of rows. <inner table> is quite often called “driving table” – table which searches data in other table. <outer table> which is hashed in memory you can treat as MEMORY INDEX.
This article presents how to use new operators PIVOT, UNPIVOT in Oracle 11g
PIVOTING quarter
| product | country | quarter | amount |
| jeans | poland | Q1 | 10 |
| jeans | us | Q2 | 20 |
| jeans | germany | Q3 | 30 |
| shorts | japan | Q1 | 10 |
| shorts | poland | Q1 | 15 |
| shorts | us | Q2 | 20 |
| product | Q1 | Q2 | Q3 |
| jeans | 10 | 20 | 30 |
| shorts | 25 | 20 |
Oracle delivers very nice extensions for GROUP BY clause which are very useful for developers and DBAs:
This article presents new feature 11g Direct NFS Client.
Oracle database kernel implements NFS version 3 client protocol. It means Oracle rdbms is not using anymore OS NFS drivers to communicate with NFS V3 NAS devices but own implementation. This results in consistent performance for many platforms and easy setup. It eliminates scenario where data is cached on OS level and in SGA. Oracle Direct NFS client is using asynchronous I/O and supports up to 4 parallel network paths (no need for bonded expensive network interfaces).
This presentation is based on following article: