Catálogo de publicaciones - libros

In this chapter, we took a look at the Oracle memory structure. We started at the process and session level, examining the PGA and UGA, and their relationship. We saw how the mode in which we connect to Oracle will dictate how memory is organized. A dedicated server connection implies more memory used in the server process than under a shared server connection, but that use of a shared server connection implies there will be the need for a significantly larger SGA. Then, we discussed the main structures of the SGA itself. We discovered the differences between the Shared pool and the Large pool, and looked at why we might want a Large pool to “save” our Shared pool. We covered the Java pool and how it is used under various conditions, and we looked at the block buffer cache and how that can be subdivided into smaller, more focused pools.

Now we are ready to move on to the physical processes that make up the rest of an Oracle instance.

In this chapter, we explored the important types of files used by the Oracle database, from slowly parameter files (without which you won’t even be able to get started) to the all important redo log and data files. We examined the storage structures of Oracle from tablespaces to segments, and then extents, and finally down to database blocks, the smallest unit of storage. We reviewed how checkpointing works in the database, and we even started to look ahead at what some of the physical processes or threads of Oracle do.

In this chapter, we took a look at redo and undo, and what they mean to the developer. I’ve mostly presented here things for you to be on the lookout for, since it is actually the DBAs or SAs who must correct these issues. The most important things to take away from this chapter are the significance of redo and undo, and the fact that they are not overhead—they are integral components of the database, and are necessary and mandatory. Once you have a good understanding of how they work and what they do, you’ll be able to make better use of them. Understanding that you are not “saving” anything by committing more frequently than you should (you are actually wasting resources, as it takes more CPU, more disk, and more programming) is probably the most important point. Understand what the database needs to do, and then let the database do it.

In this chapter, we explored the concept of parallel execution in Oracle. I started by presenting an analogy to help frame where and when parallel execution is applicable, namely when we have long-running statements or procedures and plenty of available resources.

Then we looked at how Oracle can employ parallelism. We started with parallel query and how Oracle can break large serial operations, such as a full scan, into smaller pieces that can run concurrently. We moved on to parallel DML (PDML) and covered the rather extensive list of restrictions that accompany it.

Then we looked at the sweet spot for parallel operations: parallel DDL. Parallel DDL is a tool for the DBA and developer alike to quickly perform those large maintenance operations typically done during off-peak times when resources are available. We briefly touched on the fact that Oracle provides parallel recovery before we moved on to discuss procedural parallelism. Here we saw two techniques for parallelizing our procedures: one where Oracle does it and the other where we do it ourselves.

If we’re designing a process from scratch, we might well consider designing it to allow Oracle to parallelize it for us, as the future addition or reduction of resources would easily permit the degree of parallelism to vary. However, if we have existing code that needs to quickly be “fixed“ to be parallel, we may opt for do-it-yourself (DIY) parallelism, which we covered by examining two techniques, rowid ranges and primary key ranges, both of which use DBMS_JOB to carry out the job in parallel in the background for us.

In this chapter, we covered many of the ins and outs of data loading and unloading. We started with SQL*Loader (SQLLDR) and examined many of the basic techniques for loading delimited data, fixed-width data, LOBs, and the like. We discussed how this knowledge carries right over into external tables, a new feature in Oracle9i and later that is useful as a replacement for SQLLDR, but that still utilizes our SQLLDR skills.

Then we looked at the reverse process, data unloading, and how to get data out of the database in a format that other tools, such as spreadsheets or the like, may use. In the course of that discussion, we developed a PL/SQL utility to demonstrate the process—one that unloads data in a SQLLDR-friendly format, but could easily be modified to meet our needs.

Lastly, we looked at a new Oracle 10g feature, the external table unload, and the ability to easily create and move extracts of data from database to database.