June 2003

The Specialist
Database News You Can Use
A monthly newsletter for Oracle users

June 2003
From Database Specialists
Expert onsite and remote DBA services
for your Oracle databases
Tel: 415-344-0500 • Toll-Free: 1-888-648-0500

Load Your Data Faster

At OracleWorld 2002, Database Specialists’ president Roger Schrag participated on the DBA User Expert panel. A person in the audience asked: “I am designing an application that will need to load 10 to 12 million rows of data per hour into Oracle tables from flat files. What Oracle features and techniques should I be looking at to achieve this performance requirement?” This was an excellent question—one that DBAs and developers everywhere can relate to on some level. Ask DB Specialists: System Change Numbers

In his latest paper, Roger Schrag reviews some of the Oracle features and techniques that can be employed to speed up data loads from flat files into Oracle tables. The method that is ideal for your situation will depend on many factors—there is no “one size fits all.” Roger uses a flat file containing 100,000 records from a call center application and loads the data into Oracle using a variety of techniques, comparing elapsed time and CPU time. Find out the benefits and the caveats of several different methods in this informative eight page paper. Read it at http://dbspecialists.wpengine.com/presentations.html.

 

As we mentioned in the last issue of The Specialist, many readers submit questions asking for solutions, hints, and suggestions for solving complex Oracle issues (our specialty!). Based on your feedback, the May issue debuted this exciting new column. We received many great questions—thanks everyone. If you have a challenging issue to share with the Oracle user community, email your questions to askdbspecialists06@dbspecialists.com. Include your name, telephone number and problem description. Because we get a lot of requests, we may not have time to personally respond to every question. So, stay tuned for future issues of The Specialist to see your questions answered. The Oracle Underground

This month’s question comes from Alan M. in San Mateo, California: I have a table (table A) with a post-insert trigger that updates three other tables. After a successful insert to table A and a COMMIT is issued, is all of the insert and update activity represented by one SCN (system change number) or are there four separate SCNs (excluding the rollback and redo SCN stuff)? I’m thinking in terms of LogMiner and point-in-time recovery where you can recover to a specific SCN.

Terry Sutton of the Database Specialists team responds: System Change Numbers, or SCNs, are monotonically increasing numbers assigned to transactions. Oracle uses SCNs to ensure read-consistency in queries and to make sure the correct order is maintained when applying transactions during recovery.

Inserts, updates, and deletes performed by a database trigger are all part of the same transaction as the DML statement that causes the trigger to fire. If table1 has a post-insert trigger that inserts the value “somevalue” into table2, then the statement “INSERT INTO table1 VALUES (1, 2, 3)” is really no different than two statements in a single transaction:

  • INSERT INTO table1 VALUES (1, 2, 3)
  • INSERT INTO table2 VALUES (‘somevalue’)

If you insert a row into table1 and then roll the insert back, the rows inserted or updated by the database triggers on table1 will also be rolled back. An SCN represents an entire transaction. Since the statements are all part of the same transaction, they are all represented by one SCN.

 

You’ll find the Oracle Underground on the web at http://www.www.orafaq.com/faq.htm. This site puts a wealth of information at the fingertips of Oracle professionals all over the world. According to the website, this popular place for Oracle users receives “more than 200,000 unique pairs of eyes that visit The Oracle FAQ every month.” On the site, you’ll find a variety of resources to explore, including: Great Resources from Oracle Users Group

  • Script and Code Exchange
  • Books on many topices related to Oracle
  • Forums
  • Job directories
  • USENET Archives

 

The Spring Conference of the Northern California Oracle Users Group (NoCOUG) on May 15 was a huge success. Many popular speakers attended the event, including Thomas Kyte, Vice President, Core Technologies, Oracle Corporation and author of the “Ask Tom” column in Oracle Magazine. Thomas conducted a two-hour session entitled, “How to Figure Out What’s Wrong,” and provided many scripts and tools. You’ll find his presentation available for downloading on the NoCOUG website at http://www.nocoug.org/presentations.html.

In addition, you’ll find presentions on materialized views, implementing Oracle9i Data Guard for higher availability, plus quite a few others.

 

Leave a Reply

Your email address will not be published. Required fields are marked *