Saturday, August 18, 2007

SAN/NAS Summit 2007

SNIA Focuses on Collaboration to Overcome IT Convergence

A few weeks ago, on June 14, I drove my Nissan Murano to the beautiful City of Philadelphia. I traveled there to accept an invitation by the Storage Networking Industry Association (SNIA) and their partners to the 2007 SAN/NAS Summit 2007 and Networking Forum. The event took place at the Philadelphia Hilton. Indeed, I was very much interested due to my experience working in several Disaster Recovery Management (DRM) solutions, involving RMAN, Unix (Solaris, AiX), and various SAN vendor environments, such as Hitachi, which supports MAA architectures, IBM, and EMC. The event was also related to one of my recent IOUG RMAN presentation (Case Studies on RMAN Metrics), that became a controversial one for some, but a very good presentation for most attendees at the Mandalay Bay. This great and recent SNIA event encompassed a few areas of application that are quite relevant to my consulting career as a Database Architect/DBA and Software Developer. The event focused on IT Collaboration as the leit motif to success by stating that “Convergence is the problem, collaboration is the solution.” With this strategic vision, the conference approached the SAN/NAS environment as conceptually composed by Information Technology (IT), Records & Information Management (RIM), Information Security, and Legal aspects required to attain collaboration in front of convergence.

Technical Framework

On the technical framework, the conference involved several areas of focus, namely:

- Fibre Channel Technologies, involving both existing and new technologies.

-Storage Consolidation, which discussed best practices and deployment strategies, as well a detail study on iSCSI-based SAN configurations, and a discussion of pros of iSCSI such as IP-driven remote control to the block level and cons such as speed in comparison to fast Fibre Channels interconnects. It also discussed future capabilities useful in grid computing networking, including 10Gb Ethernet.

-The Disk and Tape Backup Mechanism discussed Backup and Recovery (BR), Disaster Recovery (DR), and Business Continuity (BC) utilizing a variety of approaches such as Disk-to-Disk (D2D), Disk-to-Tape (D2T), and Disk-to-Disk-to-Tape (D2D2T), in conjunction with the usage of Virtual Tape Libraries (VTL). A final discussion involved the importance of this summit towards consolidation practices, standards, and strategies into an Information Lifecycle Management (ILM) vision. This track also discussed some of the potential pitfalls in disclosing SAN-related information (or exposing them as targets), such as IP and fast-interconnect VIPs, since they become vulnerable to accepting intruding packets and subsequent collapse.

- The Storage Virtualization focus area involved a virtualization tutorial addressed to IT Managers, in particular, a SNIA’s Storage Virtualization Taxonomy perspective.

- Security and Encryption involved a strategy suggesting that there is “no magic crypto fairy dust”, and focused on both symmetric and asymmetric authentication paradigms, such as those driven by the Public Key Infrastructure (PKI). It also discussed encryption capabilities and risks under SAN environments.

- The end-user requirements looked at establishing ILM/DLM and general SNIA standards and best practices to be coded in a series of RFC documents.

Business Framework

The strategic business goals involved, namely:

Reducing Risk

Fundamentally, risk can be expressed by a certain number of ways, namely:

-Loss of strategic opportunities due to the inability of recognized or leverage voluble information.

-Failure to comply with statutory or regulatory retention and destruction requirements.

-Inability to retrieve and productively use business critical information on a daily or historic basis.

Reducing Costs

-The cost of storage hardware and management applications software to meet these new demands is escalating significantly within enterprise IT operations, in spite of the fact that the cost of raw storage capacity continually decreases on a per GB basis.

-The cost to own and operate the required computing and storage infrastructure escalates even more rapidly.

-The above costs are enhanced with the cost of mitigating risk exposure, inefficiency, and lost of business opportunity already identified.

Vendor Streams

Vendor focuses included seminars and tutorials on the following topics, namely:

- iSCSI vs. Fibre Channel Myths and Reality
- NetApp/Decru Session
- Data De-Duplication: Increasing the Power of Disk-Based Backup
- Best Practices in Archival Storage
- HP Storage Essentials

The Accredited Technical Training

Involved two paid tracks on the following topics, namely:

1) Essential Storage, involving Storage Infrastructure and Backup and Recovery Seminars.

2) The Architect, including Storage Capacity Planning and Distance Replication.

Concluding Remarks

-Based on that perspective the conference suggested that Collaboration will require the establishment of a team lead by RIM and IT professionals, but of also risk and security professionals.

-The conference also implicitly responded to the vision that storage and application archiving is a major area of business opportunity for entrepreneurs and venture capitalists.

-The value of attending this event is extraordinary for DBAs, Database Architects, SAN Administrators and IT Managers. If you have not been selected to attend, you can probably find further input at for the next summit in your area. Other sites of interest area:,, and

The event was sponsored by vendor partners, namely, HP, NetApp, Quantum, Equallogic, Plasmon, and ServerGraph; and media partners, namely, CRN, InfoStor, and Storage Networking News.

Final Notes

Upon completing the event, I left the Hilton with a sense of satisfaction and felt I had profited well from this occasion to acquire new technical knowledge. Once more, the sun was bright as I drove back home.

Tuesday, August 14, 2007

Above and Beyond Efficient Correlation

Correlated Subqueries that work and bear accurate results

It has been over twenty years (23 to be precise), since I completed the unofficial Spanish translation of an Introduction to Database Systems, which most of my class peers at Universidad del Norte used during the semesters involved. The translation was rather poor in spite of my good knowledge of the English grammar. While the class was mostly based on IBM’s DB2, the professor had made it generically except for his fabulous expertise with IMS, a hierarchical DBMS, that was key to industries such as airline operations, and –in particular, that of Avianca, then the airline of Colombia. I later expanded this knowledge while studying distributed database systems at Universidad de Los Andes in Santa Fé de Bogotá. Among the most interesting topics that I found great from the mathematical logic approach, I encountered the treatment of correlated subqueries, which was based on the suppliers’ data model in Date’s book. The fact is that I had taken several required applied math in logic and Boolean algebra and special mathematics involving practical relational calculus and algebra. My father had sent me Date’s book from New Jersey, and I had a competitive advantage over all others in the class who had to use the library book or other textbooks in Spanish until my translation was completed. I had a different experience when I got the graduate classes at NJIT and MSU, where I scored very high in SQL projects. At NJIT around 1992, I used both SQL (using relational algebra) and QUEL (using relational calculus) since Ingress was the educational database in place, although I usually completed my school projects at Allied-Signal CAE Center using Oracle6. At Montclair State, I breezed with excellence in my database course using Oracle8i/9i, while I challenged myself to resolve the Stamford University undergraduate exams, which are traditionally an overnight-due challenge for even any expert.

At NJIT, I learned that trying to manually parse correlated subqueries using relational algebra or relational calculus could convey manual un-nesting of subqueries or their virtual conversion into non-correlated ones. The issue is that deriving the results via relational algebra or calculus, did not relate any of the abstraction through which I had previously used as an undergraduate, but instead an Oracle Coursework that I had taken from Allied-Signal in 1991 had greatly increased my ability to derive optimal solutions and practical production solutions for their Oracle6 VAX VMS driven environment. When I taught an OCP class at Farleigh Dickinson University’s Computer Career Institute in Teaneck in 2001, I realized that some of my students encountered similar concerns to those I had, and I got the chance to use Oracle’s demos, in particular, the employee model provided in the Scott’s schema, the most traditional one, which is an excellent source to discuss hierarchical and correlated subqueries, and it also led me to enhance my own perspective in teaching others. As usual it is not the same to know well a topic than trying to teach that topic to others.

On recalling this topic, and how RDBMS’s have progressed to improve the quality of producing accurate results. Oracle, in particular, has the most complete SQL API syntax that utilizes the most recent ANSI SQL 1999 standard available in the market as well as more traditional ones.

Indeed, correlated subqueries allow empowering certain scenarios useful in data mining and business intelligence, which in many cases for RDBMS, other than Oracle, require custom code writing and expansion, and in many cases an additional host language, usually with many unhandled exceptions involving small queries that normally threaten the database integrity. Oracle also provides the greatest number of built-in functionality that can be embedded in any correlated subquery as needed, including also built-in extensions, unstructured data such as XML, and LOBs among others.

Below, is a summary of concepts and Oracle literature that I have utilized in previous case studies, and which I prepared for a recent presentation.


  • Distinguish correlated subqueries from non-correlated ones.

  • Emphasize Oracle’s rules and limitations on correlated subqueries.

  • Summarize a set of best practices by highlighting the related strategies and appropriate syntax.


  • A subquery answers multiparts questions by involving more than one SELECT statement.

  • A subquery in the FROM clause of a SELECT statement is also called an inline view.

  • A subquery in the WHERE clause of a SELECT statement is also called a nested subquery. This also applies to the HAVING subclause in the GROUP BY clause and does not require a WHERE clause in some scenarios.


  • Logical Operators

  • (OR, AND, NOT)

    (mapping to list operators such as IN, NOT IN)

  • Comparison Operators

  • ANY, SOME, ALL (Filters/Enhancers)

  • Other Operators

Mathematical Operators

Unstructured Data (XML)

MODEL clause


  • Unlimited subquery levels in the FROM clause

  • Up to 255 levels of subqueries in the WHERE clause


  • A correlated subquery is evaluated once for each row processed by the parent statement.

  • A correlated subquery answers a multiple-part question whose answer depends on the value in each row processed by the parent statement.

  • Oracle resolves unqualified columns in the subquery by looking in the tables named in the subquery and then in the tables named in the parent statement.

Example 1: List the name and salaries of all employees in the lowest salary grade bucket

SELECT ename,sal

FROM emp e1



FROM salgrade s

WHERE e1.sal BETWEEN s.losal AND s.hisal

AND s.grade = (

SELECT MIN(s2.grade)

FROM salgrade s2



Example 2: List the records of employees whose salary is above the department’s average salary.


FROM emp


WHERE exists


FROM emp e2

WHERE e1.sal > ALL

(SELECT AVG(e3.sal)

FROM emp e3

WHERE e2.deptno =e3.deptno

GROUP by e3.deptno


AND e1.empno =e2.empno


Example 3: List the name, job title, and salary of all employees working for the department that has the most employees in the company.

SELECT e.ename, e.job, e.sal

FROM emp e




FROM emp e1

WHERE e1.deptno = e.deptno

GROUP BY deptno



SELECT MAX(count(e5.deptno)) edcount

FROM emp e5

GROUP BY e5.deptno


) ;

Example 4: List the name of the suppliers who do not ship part P2.

SELECT sname





WHERE s# = s.s#

AND p# = 'P2'


Example 5: Correlated update example: Set the shipping quantity to 0 for all suppliers in London.


SET qty = 0




WHERE s.s# = sp.s#

) = 'London';


  • Subqueries are nested when they appear in the WHERE clause of the parent statement.

  • When Oracle Database evaluates a statement with a nested subquery, it may overlook the most efficient access paths while evaluating the subquery portion multiple times.

  • Subquery unnesting unnests and merges the body of the subquery into the body of the statement that contains it, allowing the consistent query and access path optimizations.

  • Unnesting exceptions include hierarchical subqueries and subqueries that contain a ROWNUM pseudocolumn, one of the set operators, a nested aggregate function, or a correlated reference to a query block that is not the immediate outer query block of the subquery. PL/SQL could be useful in some cases.

  • You can use UNNEST hint to unnest a correlated subquery and HASH_AJ and MERGE_AJ hints for non-correlated ones.


  • Use operators EXISTS and NOT EXISTS rather than IN and NOT in whenever appropriate.

  • Avoid the usage of NOT IN.

  • Use hints if necessary to unnest subqueries.


  • Correlated Subqueries are different from uncorrelated subqueries in that the former are evaluated once for each row processed by the parent statement

  • The Oracle optimizer may successfully unnest subqueries, but it some scenarios it is possible to use hints such as UNNEST, HASH_AJ and MERGE_AJ to entice improved performance tuning.

Saturday, August 11, 2007

Oracle Day New York

The stormy dawn of August 8, 2007 was the beginning of a tough commute to the City of New York from Central New Jersey and other sites in this metropolitan area, but a great and successful technology day was awaiting all attendees at the Marriot Marquis, where Oracle brought the best of the best to present four foundational tracks, and keynote presentations by Ed Abbo, SVP, Applications Development, and Thomas Kurian, SVP, Server Technologies Development. The four tracks were namely:

Track 1. Growth and Change, which focused on the impact of the SOA suite and the application server, including Coherence with a highlight on the Data Grid, and the excellence brought by Oracle Business Process Management. The speakers were David Chappell, Jon Huang, and Arun Manchanda.

Track 2. Better Business Insight, focused entirely in business intelligence with great emphasis on the Oracle Business Intelligence foundation, also including a brief demo. The speakers were Andrew Fateman, John Colson, and Jeff Garwood.

Track 3. Risk Mitigation and Compliance, which emphasized the importance of improving governance through Identity Management (IdM) and Database Security, which presented an interesting PeopleSoft application and demonstration. This track also related presentations on SOA, rich content management, and applications infrastructure in relation to the Oracle Applications Integration Architecture (AIA). The speakers were Sudipt Kapadia, Jody Schiavo, and Nadu Bharadwaj.

Track 4. User experience and Productivity, which highlighted and emphasized the applications and web infrastructure, and discussed the importance of the new Oracle WebCenter in comparison to the previous Oracle Portal infrastructure, and its improvement in relation to installation and deployment with an excellent demonstration.
The speakers were Jody Schiavo, Tom Carew, and Jim Donlon.

Rich Schultz, VP of Middleware Product Marketing made the closing presentation with the topic Applications Infrastructure Futures. Oracle’s Kim Marie Mancuso closed the day with a survey raffle with prizes including full conference passes to Oracle OpenWorld 2007 and Callaway Golf Clubs.

This was a brilliant day to consolidate knowledge and expand understanding of Oracle infrastructure and product line or for the newly involved to just get familiar with Oracle Grid architecture, applications, database and application server and related technologies.

When I walked out from the event I encountered spontaneous very positive comments about the outcome of this event among those who attended, in relation to upgrading awareness on the new products and technologies involved.

The afternoon sun was also very bright, as I left the Marriot Marquis.

Tuesday, August 7, 2007

Expanding on Oracle10g RAC Knowledge

Image 1. A Sunday walk in New York city.

Enhancing RAC Knowledge to Cultivate Relevant Expertise

Transferring previous expertise in cluster-related technology is probably a typical and countable task. Thus, the experience attained with the Oracle8i Parallel Server (OPS) most likely applied to those who first used Oracle9i RAC. Historically, however, the principle of database clusters had existed since Oracle Version 6, the first version I actually used in production in 1991. But it is certain that great improvements first occurred with Oracle9i RAC, and that evidently Oracle10g releases have become an enormous success for all enthusiasts of this technology. You can join for free the Oracle RAC SIG group, partly sponsored by IOUG, and periodically attend free seminars, which enhance your RAC knowledgebase. The website can be reached at, and it keeps growing with good work.

I have personally worked with Sun clusters to implement grid, and HARD (Hardware Available Resilient Data),i.e., hardware-driven high availability architectures, such as those implementing RAC and Data Guard using Hitachi SAN or IBM GPFS, and the installation has become more complex since I installed the Oracle Parallel Server and had my first tested an Oracle9i RAC installation.

Last week, I was a guest at Oracle in Iselin, NJ, and attended the Oracle10g RAC Workshop using Oracle10g Release 2 (database and clusterware), a good one-day session to enhance any previous related skills. The RAC technology workshop is very well organized and includes a series of consistent ASM, clusterware, and database installation and configuration, RAC process monitoring, and fundamental manageability practices involving Grid Control, and basic command line interface, but it is not a replacement for full RAC training. Both RAC and Grid Control are foundational to the Fusion Middleware infrastructure and the Application Integration Architecture (AIA). My appreciation goes to Oracle and my congratulations to Oracle Senior Solution Architect, Ted Nanayakkara, for his great presentation work.

At the, there are very good presentations on performance tuning, and also on RAC usage for datawarehousing and Maximum Availability Architectures (MAA), with some other presentations involving fundamental architectural concepts.

Friday, August 3, 2007

Advances in Multiple Block Size Caches

Advances in Multiple Blocks Size Caches

New architectures involving Ajax, XML, and SOA architectures have placed a great deal of pressure for leveraging cache utilization and for explicit differentiation of resources, as for instance those involving Ajax clients and XML-driven client applications, in particular. The ability for persistent database objects to perform with less and nearly no I/O and latch contention when dynamic, transparent cache utilization occurs is an outstanding feature of what I called the OMBDB paradigm. Indeed, this paradigms is obviously supporting not only datawarehousing and business intelligence driven architectures, but also emerging technologies and existing persistent architectures such as EJBs and Hibernate, all of which require explicit faster cache utilization, and using multiple block caches act as an intelligent cache differentiator that automatically leverage cache performance optimization.

I have successfully tested, like many other DBAs and developers, that beyond any possible SGA tuning that using multiple-block-size database can certainly improve performance through this performance approach. OMBDB is ideal for Ajax, XML-driven clients and other SOA-driven architectures.

There has been a great deal of improvements to the usage of multiple-block-size databases including the flexibility to manage the involved initialization parameters in a more dynamic way. Besides, I also believe that your overall your data warehouse, decision support, and business analytics can perform better is a custom multiple block size database environment. The Oracle11g partitioning features can greatly enhance the OBMDB paradigm, since there are several other ways to achieve custom partitioning, one can think of other ways to further optimize performance thereon. I would like to encourage everyone to include this paradigm as part of your Oracle11g beta testing as well.

Here are some useful links:

Below is an excerpt of my original paper presented at IOUG LIVE 2005.


OMBDB: An Innovative Paradigm
for Data Warehousing Architectures

Anthony D. Noriega, Oracle Consultant, BSSE, MSCS, OCP-DBA


Managing a multi-block data warehouse database falls in a special category that involves business intelligence and combines expertise in areas such as Extract Transform Load (ETL), On-Line-Analytical Process (OLAP), Data Mining, in conjunction with custom applications, legacy systems, and related business process applications, such as Supply Change Management (SCM), Customer Relationship Management (CRM), and Enterprise Resource Planning (ERP) systems. This paper intends to enhance a blueprint for major production applications in VLDB. The paper and presentation will discuss how to best utilize multiple block size databases in conjunction with table partitioning and related techniques, table compression, and best strategies for extent and segment space management and related tablespace utilization, including best Index Allocation, Index Organized Tables benchmarks. Finally, the discussion will center in critical aspects of tuning and usage of the DMBS_STATS, DBMS_UTILITY, and CBO-based application and instance performance co-tuning. The paper will also discuss upgrade and migration issues from versions 9i to 10g, and a framework for a potential case study. This paper will discuss advantages and disadvantages, pitfalls, and a robust strategy to attain the most out of multi-block database performance.

Pre-Configuration Tasks

An important pre-creation and pre-configuration task is to choose the appropriate storage format approach. In most cases, simply considering mirroring and striping seems a practical solution. Thus, Striping and Mirroring Everything (SAME) is key to providing a healthy level or redundancy and leverage contingent recovery processes. When using Automatic Storage Management (ASM), the ASM instance will resolve these issues via its volume balancing metadata[1], once files are set as coarse striping, e.g., data files, or fine striping, e.g., online redo and control files. Potential combinations of RAID file system and ASM are quite possible and fully supported by Oracle10g. Previous storage device approaches such as OCFS and other RAW can also be considered and sometimes mixed with existing approaches. However, ASM and RAID 10 or RAID 0+1 are currently considered the most reliable approaches, with ASM in ways to become the de facto standard for high-availability architectures.

Steps to Create a Multi-Block Database

The following lines summarize the necessary steps to accomplish the creation of a multiple block size database, namely:

  1. Create a basic data warehouse database either using the Database Configuration Assistant (DBCA) or manually from cloned or originally designed scripts. Remember that database templates are useful when using the DBCA and that larger block sizes are recommended for data warehousing projects. It is recommended that you choose your default database block size as large as your operating system can afford or at least 16K, which ever will be more practical in your specific scenario.
  2. Once the database creation is completed, it is necessary to configure the database caches for each different block size to be supported. The instance db_nK_cache_size parameters, namely, db_32k_cache_size, db_16k_cache_size, db_8k_cache_size, db_4k_cache_size, and db_2k_cache_size can be set accordingly, provided that they do not match the database default block size. Although Oracle10g supports dynamic settings of these parameters, it is recommended for them to be set natively at startup. In Oracle9i, it was necessary to bounce the database to accomplish this task.
  3. At this time, the DBA is in a position to create tablespaces matching these database buffer cache sizes and allow for the usage of DDL to add objects matching each individual cache size.
  4. ¨ The subsequent creation of a server parameter file, spfile, from the update init.ora parameter file is highly recommended, to maintain robustness on both instance architecture scopes.

General Initialization Parameters Guidelines

At this stage, it is highly important to support the concept that dynamic memory management is essential to achieve he highest level or reliability. Planning on an automatic setting of auto-tune and dynamic memory management initialization parameters is crucial to establish a set of good business practices. Furthermore, it is appropriate to apply similar concerns for most automatically tunable framework management issues and related parameters, for instance, undo management and program global. Similarly, achieving early tuning and overall planning of additional components resources, such as Oracle Data Mining or Streams AQ, Oracle Messaging Gateway, and Procedural Gateways for Websphere MQ, is appropriate at this stage, since they require specific planning which could otherwise translate into undesirable downtime.

A Blueprint to Work with Multiple Block Size Tablespaces

Tablespace creation, tuning, and monitoring could either be a nearly automated activity or a complex one involving a great amount of DBA resources and time, depending on how well the process is organized and managed. In general, all tablespaces are likely to be locally managed (LMT), and there is nearly no reason to utilize dictionary managed any longer, but backwards compatibility could be an issue. The package DBMS_SPACE_ADMIN[2] is provided for conversion purposes upon migrating, and the recommendation is for a thorough conversion of all tablespaces to locally managed. Thus, as a rule it EXTENT MANAGEMENT LOCAL is a key factor to improve I/O performance. The AUTOALLOCATE option is preferred for PERMANENT tablespaces while the UNIFORM ALLOCATION is advised for TEMPORARY TABLESPACES. Similarly, AUTOMATIC SEGMENT SPACE MANAGEMENT is highly recommended in order to avoid the overhead caused by the handling of free lists and related structures. All related storage parameters will then be ignored by Oracle. Specifying the BLOCKSIZE clause is necessary when the tablespace block size is different from the database default. Similarly, other DDL tasks are relatively easy to achieve. The great innovation in Oracle10g is the usage of the BIGFILE and SMALLFILE clause. The former is intended to accommodate very large objects in VLDB, while the latter is the default option and can be omitted. In spite of larger block sizes and larger data files, multiple block size databases can also take advantage from the usage of DBMS_SERVER_ALERT settings to control thresholds and high water marks and make necessary adjustments. This is in spite of the pro-active work done by a combination of tasks involving Automatic Database Diagnostic Monitoring and the Automatic Workload Repository. Different block size tablespaces are also useful when properly aligned with business rules and technical requirements in order to prevent and minimize row fragmentation and migration issues.


'/oradata/adndba/portal/ts_dataw_idx_01.dbf' SIZE 120G






Exhibit 1. Scripts creating Bigfile tablespaces with a block size different from the database default.

Working with Physical and Logical Objects

Most of the tasks that relate to working with logical objects involve tables, indexes, and materialized views, all of which can utilize database segments. Relevant physical objects, can involve data files and ASM files, among others.

Working with Tables

Table block size is inherited from tablespace block size, and —like many other tablespace parameter— they require a careful selection matching logical objects, such as tables and indexes, accordingly. Therefore, it is appropriate to consider this issue when specifying extent allocation, regardless of the approach utilized. In general, new features offered by Oracle10g, such as ENABLE ROW MOVEMENT, can lead to outstanding dynamic manipulation on larger non-default block size tablespaces. Thus tasks related to table relocation and redefinition can be accomplished with much better timing.

Using Partitioned Tables

When partitioning tables on multiple block size databases, information about tablespace block sizes needs to be handy for the DBA to make the best decisions on how to allocated each partition. Because partitioned tables are subject to several constraints, for instance, when performing a partition exchange, merge, split or move, it is a best practice to consistently set segments in similar block size tablespaces for the best outcome. All types of partitions, namely, range, hash, list, and all possible composite partitioning options thereon are quite possible on any non-default block size tablespace and related cache operations are equally reliable. Attempting to mix block size in partition objects will result in error messages.

ERROR at line 1:

ORA-14519: Conflicting tablespace blocksizes for table : Tablespace TS_ADN4KTBS1 block size 4096 [partition specification] conflicts with previously specified/implied tablespace DATAPAR1 block size 16384 [partitionspecification]

Exhibit 3. Error Message. Oracle can detect inaccurate block size mixes when running partitioning DDL scripts.

Using Index-Organized Tables (IOT)

Index organized table are also subject to several rules and constraints. In particular, it is appropriate to consistently plan on equal-block size for both the original and overflow tablespaces. The vast majority of practical considerations applicable to partitioned tables are also applicable to partitioned index-organized tables (IOT).

Using Compressed Tables

Utilizing the appropriate compression algorithm can lead to optimal storage and cache utilization. It has been investigated statistically that the performance degradation due to compression can be negligible in comparison to other benefits in can provide such as storage and cache optimization. Partitioned tables are usually candidates for compression in a data warehouse.

Working with Indexes

Indices involved in data warehousing databases are likely to be partitioned due not only to their overall segment size but also due to specific business rules, leveraging I/O both logically and physically, and establishing a consistent architecture paradigm.

Using Partitioned Indexes

Partition indexes can be of two main types, namely, local or global, as discussed below:

Local Partitions

Local index partitioning is preferred in most instances because of its performance benefits, in addition to the easiness to establish a one-to-one correspondence with the respective table segment.

Global Partitions

Global index partition achieve relevance when hashing operations can provide optimal performance, and when the number of changing DML transactions, in particular, a combination of a greater number of deletes and inserts together in comparison to overall updates or certain table DDL, can lead to undesirable invalidation.












Exhibit 4. Sample Partitioned Index SQL Scripts

Using Other Index Types

Function indexes, reverse-key, and bitmap indexes are equally supported on tablespaces with block size different from the database default. In most cases, larger non-default block size tablespaces can provide better performance for both disk access and cache access, when holding primary and foreign key indexes, but the customization of how to best allocate indexes in relation to its density or ratio to a specific block size is a the discretion and expertise of each DBA. A perspective on content-based indexing can lead to equivalent impressions, which is very useful in XML, Text and Intermedia applications. In most cases in regression tests, Locally-Managed Tablespaces (LMT) with the AUTOALLOCATE option and AUTOMATIC SEGMENT SPACE MANAGEMENT (ASSM) have been used in order to obtain optimal results.

Working with Materialized Views

Materialized views have a significant role in data warehousing, data marts and reporting databases. Materialized views also support the set of rules applicable to table and index partitioning, and concerns can be enhanced by the utilization of analytical functions, and replication.

Working with LOBs and User-Defined Datatypes

Working with CLOBs, BLOBs, BFILE, and XMLTYPE and other complex user-defined data types represents an overall match for selected large block size tablespaces. The usage of the supplied PL/SQL packages and API allows the easy implementation and development of applications with a solid architecture. When using user-defined data types they will automatically be stored as LOB, if such data type is greater than 4000 bytes, regardless of whether the STORE AS LOB clause is specified accordingly.

Performance Tuning

The greatest significant upgrade from Oracle9i to Oracle10g does not relate to how much easier and consistent it is to handle the auto-tune memory structures but rather the impact of the Automatic Workload Repository (AWR) in conjunction with the ADDM (Automatic Database Diagnostic Monitoring) for the sole purpose of achieving optimal performance in a pro-active way. Therefore, the positive impact of further visibility and monitoring capability in the most recent release is enhanced by the consistently established auto tuning memory structures, namely, the shared pool, the buffer cache, affecting the db_cache_size, db_Nk_cache_size, the db_keep_cache_size, the db_recycle_cache_size. Furthermore, the PGA dynamic statistics, visible in the V$PGASTAT view, play a crucial role in the reliability of the Oracle instance, in particular when the WORKAREA_SIZE_POLICY is set to automatic. Oracle Enterprise Manager can assist with great user-friendliness in tuning each of these memory structures.

SQL> SELECT * FROM v$pgastat order by 1;


---------------------------------------- ---------------- ---------------

PGA memory freed back to OS 0 bytes

aggregate PGA auto target 14330880 bytes

aggregate PGA target parameter 25165824 bytes

bytes processed 537556992 bytes

cache hit percentage 100 percent

extra bytes read/written 1083392 bytes

global memory bound 1257472 bytes

maximum PGA allocated 41468928 bytes

maximum PGA used for auto workareas 1226752 bytes

maximum PGA used for manual workareas 266240 bytes

over allocation count 0

total PGA allocated 22992896 bytes

total PGA inuse 9285632 bytes

total PGA used for auto workareas 0 bytes

total PGA used for manual workareas 0 bytes

total freeable PGA memory 0 bytes

16 rows selected.

Exhibit 5. Querying the V$PGASTAT view.

Normally, besides the DB_KEEP_CACHE_SIZE, the automatic tuning cache components, namely, the Shared Pool, the Default Buffer Cache, the Java Pool, and the Large Pool are expected to add up altogether the SGA_TARGET value. It is possible to configure a RECYCLE buffer pool for blocks pertaining to segments that you do not want to remain in memory. Furthemore, the SGA_TARGET parameter could be increased up to the value specified for the SGA_MAX_SIZE, or otherwise reduced. If the DBA reduces the value of SGA_TARGET, the system identifies one or more automatically tunable components to release memory, including DB_NK_CACHE_SIZE values and respective memory areas. The DBA can reduce SGA_TARGET until one or more automatically tuned components reach their minimum size. Oracle Database determines the minimum allowable value for SGA_TARGET taking into account several factors, including values set for the automatically sized components, manually sized components that use SGA_TARGET space, and number of CPUs. The change in the amount of physical memory consumed when SGA_TARGET is modified depends on the operating system. There are important view such as, namely, V$SGASTAT, V$CACHE_ADVICE, V$SYSSTAT, V$SYSTEM_EVENT, V$SESSION_WAIT, V$WAIT_STAT, and V$PGASTAT, among others than can lead to understanding memory structure tuning and optimal shared and dynamic memory utilization.


However, the habit of keeping a tuning and statistics gathering strategy is within the goals of best practices of leading Database Administrators. The usage of STATSPACK is appropriate, when consistently used with a chronologically approach, since it can allow the building of statistics patterns and the dredging of a performance map useful in the process improvement for database administration.

Exhibit 6. Sample Statspack Report Showing Multiple Block Size Buffer Pool and I/O statistics (Oracle10g)


---------------------------------------- -----------------------------------

__db_cache_size 12582912

db_16k_cache_size 83886080

db_2k_cache_size 0

db_32k_cache_size 0

db_4k_cache_size 8388608

db_8k_cache_size 0

db_cache_advice ON

db_cache_size 4194304

db_keep_cache_size 0

db_recycle_cache_size 0

10 rows selected.

SQL> ALTER SYSTEM set db_keep_cache_size =4m;

System altered.

SQL> alter system set db_recycle_cache_size=4m;

System altered.

SQL> SELECT name,value

2 FROM v$parameter

3 WHERE name like '%db%cache%'


5 /


---------------------------------------- -----------------------------------

__db_cache_size 4194304

db_16k_cache_size 83886080

db_2k_cache_size 0

db_32k_cache_size 0

db_4k_cache_size 8388608

db_8k_cache_size 0

db_cache_advice ON

db_cache_size 4194304

db_keep_cache_size 4194304

db_recycle_cache_size 4194304

10 rows selected.

SQL> SELECT name,class,value

2 FROM v$sysstat

3 WHERE name like '%buffer%'

4 OR name like '%cache%'


SQL> /


--------------------------------------------- ---------- -------------

Commit SCN cached 128 2

DBWR checkpoint buffers written 8 16580

DBWR thread checkpoint buffers written 8 656

buffer is not pinned count 72 752095

buffer is pinned count 72 760846

commit cleanout failures: buffer being written 8 1

consistent gets from cache 8 2074808

db block gets from cache 8 1014371

dirty buffers inspected 8 18079

free buffer inspected 8 160733

hot buffers moved to head of LRU 8 27648

no buffer to keep pinned count 72 1

physical reads cache 8 145416

physical writes from cache 8 37524

pinned buffers inspected 8 188

redo buffer allocation retries 2 64

switch current to new buffer 8 3114

table scans (cache partitions) 64 0

28 rows selected.

Exhibit 7. Database cache parameters values and usage statistics before and after some dynamic adjustments.


The systematic approach to gather statistics using PL/SQL scripts with supplied packages, such as DBMS_UTILITY and DBMS_STATS, allow to derive analytical power in understanding the instance performance behavioral pattern. The following script shows a possible utilization of these two supplied packages:


DBMS_UTILITY.analyze_database ( METHOD => 'ESTIMATE',



DBMS_STATS.delete_schema_stats( OWNNAME => 'SYS' );

Exhibit 8. A brief script to quickly analyze a database and estimate statistics accordingly.


Oracle Cache Advisories allow DBAs to make decisions on how to dynamically adjust certain memory structures such as the database buffer cache, the shared pool and consequently the System Global Area (SGA) actual target (SGA_TARGET). By utilizing the Cache Advisory available in the Instance Manager, thresholds for each buffer cache can be visualized from a chart view. Similarly, the DBA can query the V$DB_CACHE_ADVICE[3] view, which displays estimated physical read factors and buffers used for estimate as the main source to establish the best cache size value. Cache advisories are very useful when recycle and keep cache are in place, and are very helpful to maintain a consistent balance between cache objects and relevant read/write access to non-cache objects on disks, and they represent a complementary effort to leverage balanced database access in conjunction with RAID and ASM approaches. The ADDM and AWR based advisory frames work leads to a comprehensive set of available pro-active options. Thus, Oracle advisories can be basically summarized as follows:

  • Buffer Cache Advisory
  • Share Pool Advisory
  • PGA Advisory
  • Segment Advisor
  • Undo Advisor
  • SQL Advisor

SQL> select name, block_size,

2 advice_status,

3 SUM(size_for_estimate) "SUM_ESTD SYZE",

4 SUM(estd_physical_reads) "SUM_ESTD PHYS READS",

5 SUM(estd_physical_read_time) "SUM_ESTD READ TIME"

6 FROM v$db_cache_advice

7 GROUP BY name,block_size,advice_status

8* order by 1

SQL> /


------------ ---------- --- ------------- ------------------- ------------------

DEFAULT 4096 ON 40 0 0

DEFAULT 8192 ON 12 63029 402

DEFAULT 16384 ON 1680 0 0

KEEP 8192 ON 12 0 0

RECYCLE 8192 ON 12 0 0

Exhibit 9. Querying the Oracle V$DB_CACHE_ADVICE view.

Undo Management

When working with Undo management, it is important to consider using at least two undo tablespaces, particularly, at peak production time or in the event of a major application upgrade, large load or import task and so on, where the undo_retention value may not lead to releasing undo space as expected because the intensiveness of the import processes, which can overrule the UNDO_RETENTION goals as far as releasing undo space. The main decision criteria here is to be able to switch from the undo tablespace nearly overflowing to the rather empty one and avoid unnecessary intensive addition of data files to the undo tablespace. This is particularly certain if a parameter such as _DISABLE_LOGGING is set to speed up an Import utility process and minimize redo logging activity, if a technique such as ORAPEPI, or alike, is eventually used. It is always possible to switch between tablespaces.

In fact, the 80-20 rule is a good approach. Once one Undo tablespace reaches 80% full, then use the ALTER SYSTEM SET UNDO_TABLESPACE . The DBA can query the V$UNDOSTAT or DBA_ROLLBACK_SEGS to determine the current UNDO tablespace, which is also useful to further visualize online and offline rollback or undo segments.


At the instance level, when the DBA optimizer_mode to either CHOOSE or COST, Oracle performance CBO-based optimization is activated, representing a typical approach for data warehouses. Similar hints can apply at an application level where FIRST_ROWS_n rather than FIRST_ROWS, n being a typical power or 10, can be enabled for custom optimal performance for a particular application. An important performance tuning concern is not to assume that ALL_ROWS will always produce the best results. Oracle multi-block databases can provide optimization in situations where these hints are very valuable, in particular, if a table has been properly designed and utilizes optimal indexing capabilities.

High Availability

The best recommendation in a RAC environment is to have each node’s cache sharing a production database to be able to equate the cache size in such a fashion that tables caching is significantly alike, such that performance issues related to node heterogeneous configuration are less likely to occur. Similarly, recommendations, rules and constraints previously discussed do apply for RAC and other relevant approaches, such as Oracle Data Guard. In particular, the SAME approach discussed with ASM and RAID concerns brings great relevance to these concerns. Therefore, the rules, constraints, and recommendations discussed also apply to any level of availability, including maximum availability data warehouse, where RMAN—the only certified backup solution for ASM— is agnostic of tablespace block size, except for cataloging purposes. Furthermore, it is potentially helpful to implement any goal-oriented RAC, such as Load Balancing, Speed Up, and Scale Up among others with this perspective in mind. Finally, keeping a consistent approach that enforces these rules and recommendations will allow for standalone, RAC, and Data Guard databases to be better suited for overall grid computing. Oracle Enterprise Manager Grid Control can greatly leverage and optimize all aspects of resource management when established in the grid.

On Oracle10g Replication and Streams

When working with replication using either Oracle Advanced Replication, Oracle Procedural Replication, or similarly when using Oracle Streams, it is important to be particularly careful about matching associated tablespaces both on the Master and Replica or Target sites in conjunction with the tables involved. In Oracle10g, the usage of Streams MQ enhances the concept that data warehouses can also validate the concept that dynamic data available to the data warehouse can also exist outside the database, and be available through inbound and outbound heterogeneous propagation from and to that queue.

Similarly, it is particularly important for the DBA to be aware of underlying object components, such replicating a partition table using a local index, since invalidation of this objects may lead to a time consuming rebuilding process, and consider consistency rules on block sizes at all times. It is also of great importance to envision, design, and implement a strategy that can contemplate not only tuning at the initial replication stage, but also the resilient periodic replicating job processes on groups, and materialized views, whose intensiveness needs to be carefully measured and planned for. This is also essentially true when associated with the timeliness and frequency of the replication jobs, the load imposed on the system and channel, and the Storage Area Network (SAN) or Network Attached Storage (NAS) channel itself. In fact, this issue is applicable mostly if the entire database image is replicated and it has commensurate validity as well when a Business Continuity Volume (BCV) is using multiple block sizes and partitioning.


A key aspect of where business intelligence comes in place is precisely in relation to Oracle’s unique ability to productively achive the following business goals:

  • Provide innovative content and document management with extensive datatype support, including user-defined, XMLTYPE, and customized support for LOBs.
  • Align Oracle Applications and third party SCM, ERP, and CRM systems with data warehousing technology on the basis of custom block size support and associated object caching, table compression and partitioning, among other options.
  • Integrate existing proprietary information and legacy systems into new data warehouses, data marts, and reporting databases, whose infrastructure outperforming any historic achievements.
  • Integrate and support technologies such as Oracle Data Mining, Business Intellengence Beans (Bi Beans), OLAP, XML, and Java-driven Service Oriented Applications (SOA).
  • Provide the infrastructure to enhance systems via development paradigms such as J2EE and Model View Controller (MVC) with JDeveloper, or third party approaches such as Microsoft .net, or the Eclipse integrated IDE environment. Other tools such as Oracle Warehouse Builder (or third parties like Informatica), Discoverer, TopLink, and the Embedded Data Warehouse can align the capabilities exposed by mutiple block size databases.

OMBDB can achieve many other goals when supporting business intelligence systems and applications.



3. pctfree 5 pctused 50

4. build immediate

5. using no index

6. refresh force

7. enable query rewrite

8. AS


10. GROUPING_ID(D.did,D.dname,,p.pname,e.empid, GID1,








18. GROUPING_ID(w.region,w.state, GID2,

19. INITCAP(w.region) AS "Region",

20. w.state AS "State",

21. INITCAP(W.CITY) AS "city",

22. w.quarter AS quarter,

23. w.month AS month,

24. GROUPING_ID(w.year,w.quarter,w.month) GID3,

25 GROUPING_ID(w.region,,w.state,w.year,w.quarter,w.month) GID4,




29. FROM DBAMBA.employee@biportal E,

30. DBAMBA.work_schedule@biportal W,

31. DBAMBA.project@biportal P,

32. DBAMBA.division@biportal D

33. WHERE D.did = E.did

34. AND E.empid = W.empid(+)

35. AND D.did =


37. ( (D.did,D.dname,,P.pname,E.empid,,

38. (w.region,w.state,,

39. (w.quarter,w.year,w.quarter),

40. (w.region,w.state,,w.year,w.quarter,w.month),

41. ROLLUP(d.dname),

42. CUBE(p.pname),


44. )

SQL> /

Materialized view created.

Exhibit 10. Sample Materialized View, using OLAP functions, created in tablespace with a block size different from the database default

Some Business Intelligence Scenarios

Intelligent document management systems today can take advantage of Oracle multiblock databases that can critically align size and business properties and methods to specific logical object containers. As such, a bigfile tablespace can hold large amounts of data, rich content, and in many cases replace the operability of old systems, for instance, tape-based broadcasting and video conferencing, where a maximum availability approach is strongly recommended. This can result not only in great savings for companies involved in this business, but also the technological redefinition or, sometimes, almost the reconversion of the very business paradigm.

In today’s Supply Chain Management world, Radio Frequency Identification (RFID) has become a requirement for leading companies utilizing sensor-based computing and object-oriented relational systems. Leveraging appropriate segment allocation, and aligning information that may utilize user-defined and XML datatype as well as custom media manipulation is key to provide optimal database performance.

Independently from the operating system involved, Oracle10g can achieve the desirable level of customization in all aspects that business intelligence processes, such as data mining, OLAP, and related ERP, SCM, and CRM systems could require.

Besides, database instances that utilize multi-block caches can further leverage performance tuning and optimize memory resources and equivalently provide an outstanding level of I/O performance and reliability, with the advantage that all intelligent technology applied has a solid infrastructure to support it.

At the present time, when most companies are in the process of migrating their data warehouses to Oracle10g, migration and upgrade business and technical process can prove to be challenging and time-consuming. The process of migrating itself an ERP system is subject to many milestones, each one which could represent a bottleneck in the project timeline.


The selection, design and implementation of an Oracle multiple block size database for a data warehousing project is an innovative paradigm for many businesses and their applications. The many aspects where DBAs can derive the appropriate level of information technology support for business intelligence using OMBDB are numerous, such as mentioned utilization of LOBs, and XML types in content rich applications, to leverage the diversity of data structures and the persistence and volatility of objects handled by increasingly complex multi-tier applications. Among the most important associated technologies, it is possible to highlight the following ones:

  • Table and Index Partitioning, including relevant operations such as smart splitting or exchange.
  • Large Object and User-Defined Datatypes
  • Large Tablespace (BIGFILE) size support for very large databases (VLDBs), including multiple block sizes.
  • Table compression.
  • Practical and seamless integration with ERP, SCM, CRM and legacy systems.
  • Integration with cluster and high availability approaches.
  • Support on file systems, conventional raw devices, OCFS, RAID, and ASM environments.
  • The OMBDB paradigm can be easily integrated with Oracle technologies such as Oracle Business Intelligence, Oracle Applications Server and Oracle Collaboration Suite.

Utilizing Oracle multiblock databases in data warehousing based systems will prove in the long-term to be a reliable methodology to approach the diversity of information and related business intellegence applications processes when integrating existing systems, consolidating older systems with existing or newly created ones, to avoid redundancy and lower costs of operations, among other factors. The input received from those already using multiblock databases in highly satisfactory in areas such as marketing, advertisement, finance, pharmaceutical, document management, manufacturing, inventory control, and entertainment industry.

[1] The RBAL and ARBn process are involved.

[2] The procedure DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL is run to perform a conversion from Dictionary Manage Tablespace (DMT) to Locally Managed Tablespace (LMT).

[3] The DB_CACHE_ADVICE initialization parameter needs to be set to either ON or READY