Skip to content

DBAzine.com

Sections
Personal tools
You are here: Home » Blogs » Chris Foot Blog » Chris Foot's Oracle10g Blog » Administering Terabyte Tables - An Interview with Data Warehouse DBA Jim Dojonovic
Seeking new owner for this high-traffic DBAzine.com site.
Tap into the potential of this DBA community to expand your business! Interested? Contact us today.
Who Are You?
I am a:
Mainframe True Believer
Distributed Fast-tracker

[ Results | Polls ]
Votes : 3626
 

Administering Terabyte Tables - An Interview with Data Warehouse DBA Jim Dojonovic Administering Terabyte Tables - An Interview with Data Warehouse DBA Jim Dojonovic

Consider this to be yet another "Stop the Presses Blog". I originally intended to discuss the different toolsets that DBAs can use to reclaim unused space allocated to Oracle objects. This installment was to be the follow-up to my previous blog on the Segment Analyzer. But we have hit an important milestone here at Giant Eagle, so I thought I would deviate from my intended topic for one blog.

If you have been following this series, you'll know that my unit is responsible for administering several large data warehouses. The warehouse that we most recently converted to 10G has a single, partitoned table that has just grown to one terabyte in size. The entire database is multiple terabytes and rapidly growing at hundreds (and hundreds) of MEGs per month. We estimate that within 2 years, the database will be close to 10 terabytes in size. I thought it might be interesting to interview our "big data" DBA, Jim Dojonovic. Jim is responsible for administering our data warehouses.
Interview with Ace Warehouse DBA Jim Dojonovic

Question:
How long have you been working with large data stores?

Answer:
I have been Giant Eagle's data warehouse DBA for the last five years. I started in the group as a traditional database administrator but kind of evolved into Giant Eagle's data warehouse administrator over the years.


Question:
What do you like about administering data warehouses?

Answer:
The challenge. Large databases are very unforgiving. You make a mistake and you're cooked. You quickly learn that what you can easily do in other databases can create absolute havoc in a data warehouse.


Question:
Can you give me an example of this?

Answer:
Sure. How about adding a column with a not-null constraint and a predefined default value to a table? The statement used to perform the alteration may run pretty quickly on a table with thousands, or even millions of rows but if you run that statement on a table with hundreds of millions or billions of rows there's a good chance you'll lock the table up and impact production processing for hours. Possibly even days for that matter.

A better alternative is to add the column without the not-null constraint and default value. You can then execute SQL statements to update the table with the predefined default value. You have your choice to run a parallel DML statement or just logically break up the table and run separate SQL statements to reduce the amount of time it takes to perform the update. The last step is to execute the alter statement to add the not-null constraint and predefined default value. Updating the table using SQL statements to add the predefined default value has less overhead and impact than the alter DDL statement.


Question:
So what are some of the other challenges?

Answer:
Besides performance? Backing them up. Making alterations to objects without tying them up for hours.


Question:
What are your recommendations for warehouse backups?

Answer:
There are lots of technologies out there. Oracle's latest hot button is disk backups, which we will be looking at in the near future. But if you have to rely upon Oracle provided tools, you can't beat RMAN. We run weekly full backups and use cumulative incrementals throughout the week. We run hot backups with 8 parallel streams to multiple tape devices. With that many streams doing the incrementals, we don't feel it is necessary to run partial database backups on a subset of the database structures. We stick with incrementals on the entire database.


Question:
How many data warehouses are you responsible for?

Answer:
I'm currently responsible for 4 production data warehouses. Our hardware platforms are AIX servers with EMC Symmetrix disk connected via SAN. We use both Oracle9i and Oracle10G as the database. It's my personal statement of direction to migrate all of Giant Eagle's data warehouses to 10G as soon as possible.


Question:
OK, so how does a single DBA administer that many data warehouses?

Answer:
The key is to come up with a set of best practices and strictly adhere to them in all of the environments you are responsible for. What works in one warehouse will probably work in all of them. I'm lucky to have a strong set of application developers who know how to access large data stores. We have an excellent quality review process here. The queries must be written to retrieve the data in the shortest time possible without dominating finite system resources. It becomes a delicate balancing act at times.

Our data administrator has done an excellent job designing our warehouses. If you don't have good design, you're in for big problems. Giant Eagle also has units that monitor hardware and disk performance statistics VERY closely. At the first indication of a problem, we are reviewing in-depth performance statistics. Our operating system support technicians are absolutely top notch when it comes to tuning operating systems for data warehouses. In addition, the SAN and EMC disk administrators know how to design disk subsystems to support large data stores. We are very proactive. If we see a hot spot on a set of disks, we don't wait until it becomes a problem. We take steps to correct it immediately.

I'm also big fan of both 9I OEM and 10G EM administrative toolsets. Why code when you can point and click?

To sum it up, if you have the right team supporting you and strong group of application coders, you don't need an entire squad of DBAs to support large data stores. We do it here with one primary DBA. If everyone that supports the data warehouse steps up to the plate and executes, the whole task of supporting large data stores becomes rather easy. I'd like to be able say that I'm some kind of super DBA that can support several multiple terabyte warehouses. But that's not it; we just don't have any weak links. It makes my job easy. But you have to remember that we have been supporting warehouses here at Giant Eagle before they became popular. We have over 10 years of experience and have been able to create a strong set of best practices during that time. That's the key.


Question:
Can you provide us with a few pointers on performance?

Answer:
Since you're my boss, I sure will. The keys to good performance are partitioning, parallelism, adequate memory and CPU resources and fast disk that is configured for performance. If you have that combination, you'll do OK.

The Oracle optimizer is both partition and parallel aware. It doesn't make a difference if your table is a terabyte in size, if you can break it up and store it in small chunks, you'll be able to get data out of it pretty quickly. If the optimizer thinks that it can satisfy the query by accessing one or two partitions of a table, the server process will only access those partitions. All of the other partitions (and terabytes of data) aren't accessed. You need to choose your partitioning keys very carefully. That's when having a data administrator that understands performance is of great value.

You also need to use heavy parallelism. We run our platforms hard because we hit them with so many parallel processes. You take a single query or DML statement and split the workload among dozens of child processes. As long as your hardware server can handle the workload generated by the parallel processes, don't hesitate to experiment with high numbers of parallel processes. Just remember that although a high number of parallel processes may work with a few concurrent users, it may cause problems as your number of concurrent users increases. It becomes a balancing act. It takes a while, but once you learn, it's easy.

You must also have enough memory to handle the SGA and PGA. I currently have SGAs and PGAs that are hundreds (and thousands) of MEGs in size. Like CPU horsepower, your organization can't scrimp on memory. It is so important to give large databases enough memory. I monitor the cache areas around the clock using 10G performance alerts.

Last but not least, you've got to have efficient disk layouts that are optimized for high performance. Oracle provides numerous internal statistics that measure I/O performance. We combine those statistics with the output from our other monitoring tools to keep very close tabs on our disk systems.


Question:
Can you tell us a little about some of the database features you use on a regular basis?

Answer:
We are heavy users of materialized views. You can equate them to summary tables that were used in the past. Materialized views allow you to store the pre-computed results of data calculations, such as sums, counts, averages and data table joins. It is more efficient to access a pre-computed result instead of computing the desired result from detail data every time a query is executed.

We also take advantage of external tables, which provide a mechanism to view data stored in external sources as if it were a table in the database. This ability to read external data provides a more straightforward method of loading and transforming data from external sources. This prevents us from having to reserve space inside the database for staging tables or write external programs to transform the data outside of the database environment. External tables streamline the ETL function by merging the transformation and loading processes.

We also use the database resource manager to limit the amount of resources consumed on the warehouse servers. In addition, we are certainly proponents of star schema processing here.

The 10G features I'm most fond of are all of the advisors, which you've done a good job writing about, the ability to monitor O/S performance from Enterprise Manager and the advanced monitoring thresholds you can set. I also use automatic memory management, which allows me to allocate one big chunk of memory to the Oracle SGA. Oracle then becomes responsible for dynamically allocating memory between the shared pool, data buffer cache, etc.. It works great! I set it and forget it, so to speak. The advancements to flashback are also very beneficial. I have never had to use the flashback feature yet but I have seen some of my fellow DBAs use it here to prevent database recoveries. Being able to recover data deleted or updated by mistake is very beneficial. It makes me rest easier knowing I have another tool in the toolbox I can use to recover from incorrect changes made to database data.

Although I haven't implemented it yet, I am highly interested in evaluating Oracle ASM. It's truly the next evolutionary step in database technology. I predict that, sooner or later, most Oracle installations will use ASM to administer disk. Striping is the key to warehouse disk performance. Currently, when we add a new disk into a striped file system we have to move the data to a different set of temporary drives, reconfigure the stripe with the new disk and move the data back on to the original stripe. This requires that the data be taken offline. ASM would allow us to add the drive directly to the stripe without being forced to take an outage. Oracle ASM would then distribute the data during off-peak hours.


Question:
What is the future of data warehousing?

Answer:
Data warehouses aren't solely used for pure decision support any more. That went out years ago. Truthfully, you can describe some of our warehouses as "really, really large operational data stores". Large data stores will be used for both tactical and strategic decision making. Advancements in hardware and software will continue unabated. Let's face it; the hardware, O/S and database markets are extremely competitive. A competitive market arena forces all vendors to continuously adjust pricing and accelerate the release of new products as well as enhancements to existing products. Consumers can expect the improving hardware performance trends to continue. This rapid release of new features and dramatic improvements in hardware performance makes our jobs exciting.

Next Up
We get back on topic when we discuss the different toolsets we can use to reclaim unused space allocated to Oracle objects.

Thanks for Reading!


Tuesday, September 06, 2005  |  Permalink |  Comments (0)
trackback URL:   http://www.dbazine.com/blogs/blog-cf/chrisfoot/blogentry.2005-09-06.2918203481/sbtrackback
 

Powered by Plone