Rise and Do Honor! Oracle Awards

If you get the chance, check out the Oracle Award-Winners from the November/December edition of Oracle Magazine. You can find them at this link:

http://www.oracle.com/technology/oramag/oracle/06-nov/o66eca.html

I got the chance to meet a few of these fine people while at OpenWorld, and have spoken to others before and afterward as a fellow blogger. If you haven’t checked out Eddie Awad’s new OraDot.com OraNA, I’d highly recommend it. It’s like the Digg an outstanding news aggregator for Oracle, and has tons of great articles.

Why the strikethroughs? Read Eddie’s comment below. Thanks Eddie!

Now that Oracle has officially made the OpenWorld 2006 presentations public, I figured I’d make mine available from my site. You can click the link below to download it in PDF format:

Putting the Express Back Into Oracle Application Express with AJAX

Enjoy! I am also working on getting the example I used in my presentation on a public site. Stay tuned!

I answered a question on the OTN forum this morning that I figured is pertinent enough to post on my blog…it’s a very common question among DBAs and developers, and I think there are plenty of myths surrounding it as well.

Question: I am updating 1 million rows in Oracle 10g platform, normally when I do it in oracle 9i I run it as batch process and commit after each batch. Obviously to avoid/control undo generation. But in Oracle 10g I am told undo management is automatic and I do not need run the update as batch process.

Is this right please throw some light on this new feature – automatic undo management

Answer: Automatic undo management was available in 9i as well, and my guess is you were probably using it there. However, I’ll assume for the sake of this writing that you were using manual undo management in 9i and are now on automatic.

Automatic undo management depends upon UNDO_RETENTION, a parameter that defines how long Oracle should try to keep committed transactions in UNDO. However, this parameter is only a suggestion. You must also have an UNDO tablespace that’s large enough to handle the amount of UNDO you will be generating/holding, or you will get ORA-01555: Snapshot too old, rollback segment too small errors.

You can use the UNDO advisor to find out how large this tablespace should be given a desired UNDO retention, or look online for some scripts…just google for: oracle undo size

Oracle 10g also gives you the ability to guarantee undo. This means that instead of throwing an error on SELECT statements, it guarantees your UNDO retention for consistent reads and instead errors your DML that would cause UNDO to be overwritten.

Now, for your original question…yes, it’s easier for the DBA to minimize the issues of UNDO when using automatic undo management. If you set the UNDO_RETENTION high enough with a properly sized undo tablespace you shouldn’t have as many issues with UNDO. How often you commit should have nothing to do with it, as long as your DBA has properly set UNDO_RETENTION and has an optimally sized UNDO tablespace. Committing more often will only result in your script taking longer, more LGWR/DBWR issues, and the “where was I” problem if there is an error (if it errors, where did it stop?).

Lastly (and true even for manual undo management), if you commit more frequently, you make it more possible for ORA-01555 errors to occur. Because your work will be scattered among more undo segments, you increase the chance that a single one may be overwritten if necessary, thus causing an ORA-01555 error for those that require it for read consistency.

It all boils down to the size of the undo tablespace and the undo retention, in the end…just as manual management boiled down to the size, amount, and usage of rollback segments. Committing frequently is a peroxide band-aid: it covers up the problem, tries to clean it, but in the end it just hurts and causes problems for otherwise healthy processes.

For any readers using IE, you may notice that the page content doesn’t position properly on the page. It looks great on other browsers, but IE has a serious issue when it comes to padding, margins, and really anything pixel related.

I’m working on fixing this issue so IE users can enjoy the same brilliant clarity Firefox users have. At least while visiting my site.

In the meantime, go Firefox!

I received a question from a reader that I figured I’d address here.

Question: The oracle documentation tells us that when we put a tablespace in backup mode, the first DML in the session logs the entire block in the redo log buffer and not just the changed vectors. i have two questions regarding this:

1) Can we simulate an example to see this happening?
2) What can be the purpose of logging the entire block the first time and not do the same subsequently?

Answer:

No problem! Below, I’ve created a simulation. Pay attention to the “redo size” statistic in each.

First, I have updated a single row of the employees table.

SQL> set autotrace trace stat
SQL> update employees set first_name = 'Stephen' where employee_id = 100;

1 row updated.

Statistics
----------------------------------------------------------
          0  recursive calls
          1  db block gets
          1  consistent gets
          0  physical reads
        292  redo size
        669  bytes sent via SQL*Net to client
        598  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> rollback;

Rollback complete.

Notice the redo size was only 292 bytes, not a very large amount. Now, let’s put the USERS tablespace into hot backup mode.

SQL> alter tablespace users begin backup;

Tablespace altered.

SQL> update employees set first_name = 'Stephen' where employee_id = 100;

1 row updated.

Statistics
----------------------------------------------------------
          0  recursive calls
          2  db block gets
          1  consistent gets
          0  physical reads
       8652  redo size
        670  bytes sent via SQL*Net to client
        598  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

Wow! Quite a bit of a difference. This time, we can see that at least an entire block was written to redo; 8,652 bytes total. Let’s run it one more time, with the tablespace still in hot backup mode.

SQL> /

1 row updated.

Statistics
----------------------------------------------------------
          0  recursive calls
          1  db block gets
          1  consistent gets
          0  physical reads
        292  redo size
        671  bytes sent via SQL*Net to client
        598  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

This time, it only used 292 bytes, the same as the original amount. However, to address your second question, we’re going to attempt changing a different block, by changing a record in the departments table instead of employees.

SQL> update departments set department_name = 'Test Dept.' where department_id = 270;

1 row updated.

Statistics
----------------------------------------------------------
         17  recursive calls
          1  db block gets
          5  consistent gets
          1  physical reads
       8572  redo size
        673  bytes sent via SQL*Net to client
        610  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

The result is that another entire block was written to redo. In your question, you stated: “The oracle documentation tells us that when we put a tablespace in backup mode, the first DML in the session logs the entire block in the redo log buffer and not just the changed vectors”

This is close, but not right on the mark. It is not the first DML of the session, but the first DML to a block that is written to redo. You’re absolutely right when you imply that the first DML of the session would make no sense. However, when Oracle writes the first DML for the block, it ensures that the redo logs/archive trail contains at least one full representation of each block that is changed. Subsequent changes will therefore be safe.

This process exists to resolve block fractures. A block fracture occurs when a block is being read by the backup, and being written to at the same time by DBWR. Because the OS (usually) reads blocks at a different rate than Oracle, your OS copy will pull pieces of an Oracle block at a time. What if the OS copy pulls half a block, and while that is happening, the block is changed by DBWR? When the OS copy pulls the second half of the block it will result in mismatched halves, which Oracle would not know how to reconcile.

This is also why the SCN of the datafile header does not change when a tablespace enters hot backup mode. The current SCNs are recorded in redo, but not in the datafile. This is to ensure that Oracle will always recover over the datafile contents with redo entries. When recovery occurs, the fractured datafile block will be replaced with a complete block from redo, making it whole again. After Oracle can be certain it has a complete block, all it needs are the vectors.

I hope this answered your question!

OpenWorld is over, and it’s time to get back to blogging! I’ve got plenty of updates, including Tip #5, my trip to Jamaica, OpenWorld itself (including my presentation), and other various notes. Expect updates over the next couple days.

All,

Sorry about the cliffhanger on the final tip of my five part series; the Windows installation my DB was on crashed, and I have to recover the OS so I can get the database back up and running. That’s why I just love the Mac.

There will be an update soon, promise.

Wouldn’t it be fantastic if you could simply pull data from the database in XML? Many shops use XML for data transfer, web services, reports, and more. Oracle’s XML Publisher product can retrieve XML from an HTTP feed and use it to generate rich reports with graphs, images, and other content, and then mail, fax, print, or FTP them.

All we need is the actual XML!

For this, we have the DBMS_XMLGEN package. This package generates XML on the fly using any query you want; in addition, it’s extremely easy to use from either the SQL prompt or in code, as it’s just a simple query.

Generating XML From the Database

Take this standard query:

SQL> select employee_id, first_name, last_name, phone_number
  2  from employees where rownum < 6

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 PHONE_NUMBER
----------- -------------------- ------------------------- --------------------
        100 Steven               King                      515.123.4567
        101 Neena                Kochhar                   515.123.4568
        102 Lex                  De Haan                   515.123.4569
        103 Alexander            Hunold                    590.423.4567
        104 Bruce                Ernst                     590.423.4568

We get our standard output with no frills. Behold as we transform this into XML!

set pages 0
set linesize 150
set long 9999999
set head off
SQL> select dbms_xmlgen.getxml('select employee_id, first_name,
  2  last_name, phone_number from employees where rownum < 6') xml
  3  from dual

<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <EMPLOYEE_ID>100</EMPLOYEE_ID>
  <FIRST_NAME>Steven</FIRST_NAME>
  <LAST_NAME>King</LAST_NAME>
  <PHONE_NUMBER>515.123.4567</PHONE_NUMBER>
 </ROW>
 <ROW>
  <EMPLOYEE_ID>101</EMPLOYEE_ID>
  <FIRST_NAME>Neena</FIRST_NAME>
  <LAST_NAME>Kochhar</LAST_NAME>
  <PHONE_NUMBER>515.123.4568</PHONE_NUMBER>
 </ROW>
 <ROW>
  <EMPLOYEE_ID>102</EMPLOYEE_ID>
  <FIRST_NAME>Lex</FIRST_NAME>
  <LAST_NAME>De Haan</LAST_NAME>
  <PHONE_NUMBER>515.123.4569</PHONE_NUMBER>
 </ROW>
 <ROW>
  <EMPLOYEE_ID>103</EMPLOYEE_ID>
  <FIRST_NAME>Alexander</FIRST_NAME>
  <LAST_NAME>Hunold</LAST_NAME>
  <PHONE_NUMBER>590.423.4567</PHONE_NUMBER>
 </ROW>
 <ROW>
  <EMPLOYEE_ID>104</EMPLOYEE_ID>
  <FIRST_NAME>Bruce</FIRST_NAME>
  <LAST_NAME>Ernst</LAST_NAME>
  <PHONE_NUMBER>590.423.4568</PHONE_NUMBER>
 </ROW>
</ROWSET>

Beautiful! Fully compliant XML that can be easily integrated into any application, with ROWSET and ROW tags in place to identify nodes, and tags for each column you pulled out of the database.

All we had to do was wrap the query in the DBMS_XMLGEN.GETXML function call, selected from DUAL. The query remained exactly the same.

A More Advanced Use

Let's take it one step further. Most XML has subnodes for each main node. For instance, what if we wanted to pull XML for every department, and a subnode for every employee under it? We can use the CURSOR function!

SQL> select department_id, department_name,
  2  cursor(select first_name, last_name
  3  from employees e
  4  where e.department_id = d.department_id) emp_row
  5  from departments d
  6* where rownum < 4

DEPARTMENT_ID DEPARTMENT_NAME                EMP_ROW
------------- ------------------------------ --------------------
           10 Administration                 CURSOR STATEMENT : 3

CURSOR STATEMENT : 3

FIRST_NAME           LAST_NAME
-------------------- -------------------------
Jennifer             Whalen

           20 Marketing                      CURSOR STATEMENT : 3

CURSOR STATEMENT : 3

FIRST_NAME           LAST_NAME
-------------------- -------------------------
Michael              Hartstein
Pat                  Fay

           30 Purchasing                     CURSOR STATEMENT : 3

CURSOR STATEMENT : 3

FIRST_NAME           LAST_NAME
-------------------- -------------------------
Den                  Raphaely
Alexander            Khoo
Shelli               Baida
Sigal                Tobias
Guy                  Himuro
Karen                Colmenares

6 rows selected.

The results don't look too impressive at the SQL prompt. However, watch as we surround it with a call to DBMS_XMLGEN.GETXML:

SQL> select dbms_xmlgen.getxml('
  2  select department_id, department_name,
  3  cursor(select first_name, last_name
  4  from employees e
  5  where e.department_id = d.department_id) emp_row
  6  from departments d
  7  where rownum < 4
  8* ') from dual

<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <DEPARTMENT_ID>10</DEPARTMENT_ID>
  <DEPARTMENT_NAME>Administration</DEPARTMENT_NAME>
  <EMP_ROW>
   <EMP_ROW_ROW>
    <FIRST_NAME>Jennifer</FIRST_NAME>
    <LAST_NAME>Whalen</LAST_NAME>
   </EMP_ROW_ROW>
  </EMP_ROW>
 </ROW>
 <ROW>
  <DEPARTMENT_ID>20</DEPARTMENT_ID>
  <DEPARTMENT_NAME>Marketing</DEPARTMENT_NAME>
  <EMP_ROW>
   <EMP_ROW_ROW>
    <FIRST_NAME>Michael</FIRST_NAME>
    <LAST_NAME>Hartstein</LAST_NAME>
   </EMP_ROW_ROW>
   <EMP_ROW_ROW>
    <FIRST_NAME>Pat</FIRST_NAME>
    <LAST_NAME>Fay</LAST_NAME>
   </EMP_ROW_ROW>
  </EMP_ROW>
 </ROW>
 <ROW>
  <DEPARTMENT_ID>30</DEPARTMENT_ID>
  <DEPARTMENT_NAME>Purchasing</DEPARTMENT_NAME>
  <EMP_ROW>
   <EMP_ROW_ROW>
    <FIRST_NAME>Den</FIRST_NAME>
    <LAST_NAME>Raphaely</LAST_NAME>
   </EMP_ROW_ROW>
   <EMP_ROW_ROW>
    <FIRST_NAME>Alexander</FIRST_NAME>
    <LAST_NAME>Khoo</LAST_NAME>
   </EMP_ROW_ROW>
   <EMP_ROW_ROW>
    <FIRST_NAME>Shelli</FIRST_NAME>
    <LAST_NAME>Baida</LAST_NAME>
   </EMP_ROW_ROW>
   <EMP_ROW_ROW>
    <FIRST_NAME>Sigal</FIRST_NAME>
    <LAST_NAME>Tobias</LAST_NAME>
   </EMP_ROW_ROW>
   <EMP_ROW_ROW>
    <FIRST_NAME>Guy</FIRST_NAME>
    <LAST_NAME>Himuro</LAST_NAME>
   </EMP_ROW_ROW>
   <EMP_ROW_ROW>
    <FIRST_NAME>Karen</FIRST_NAME>
    <LAST_NAME>Colmenares</LAST_NAME>
   </EMP_ROW_ROW>
  </EMP_ROW>
 </ROW>
</ROWSET>

Note that I didn't change the query in any way. But check out the results! We have each DEPARTMENT as a ROW tag, and the cursor we created gives us an EMP_ROW node containing recurring EMP_ROW_ROW nodes.

Conclusion

This can be extremely useful for quick retrieval of web records. Simply make a page that accepts input, such as DEPARTMENT_ID, and passes it into the query above. Display the response directly to the screen, and you have an easy XML display program. The best part comes with reports. XML Publisher is made to accept XML that looks just like this and form extremely detailed reports using templates made in Microsoft Word. With queries such as these and XML Publisher you can have a full reporting suite that easily pulls data, forms it into a PDF, DOC, XLS, or HTML report, and distributes it anywhere you would like it to go.

Today’s (well, yesterday’s really) trick deals with the storage of data. Let me first say that the methods I’m about to show you are NOT useful for where clauses or in normal database design. However, they can be very useful when you need a reference table that can store anything in a simple column.

For instance, take a look at Google. Google is an amazing search engine, capable of parsing billions of sites and enabling fast searching of the data it harvests.

In addition, Google is able to parse prices, mileage, dates, and other important elements, then use them as they were meant to be used; for instance, using a price to power Froogle’s bargain hunting tool.

If we were trying to duplicate the same concept into Oracle, we’d somehow need to create a table with an ID and a token, the token being the word that was parsed out of a complete document. However, what if we want that column to be able to store numbers, characters, dates…and we want them to act natively?

Enter ANYDATA. ANYDATA is a “self describing data instance type.” All this truly means is that the type not only holds a value, but it also holds its own datatype within. It also employs many methods that allow us to perform various functions with it.

So, back to our parser! We’ll start off with a very simple table:

create table search_words (
token_id number not null primary key,
token sys.anydata
);

Now we must populate the table, but normal inserts unfortunately wont work. We must use ANYDATA’s built-in Convert methods to perform our inserts. We’ll use the phrase “Make 50 dollars on 12/01/06″

INSERT INTO SEARCH_WORDS
VALUES (1, sys.anydata.convertVarchar2('Make');
INSERT INTO SEARCH_WORDS
VALUES (2, sys.anydata.convertNumber(50);
INSERT INTO SEARCH_WORDS
VALUES (3, sys.anydata.convertVarchar2('dollars');
INSERT INTO SEARCH_WORDS
VALUES (4, sys.anydata.convertVarchar2('on');
INSERT INTO SEARCH_WORDS
VALUES (5, sys.anydata.convertDate(to_date('12/01/2006', 'MM/DD/YYYY');
commit;

And now we have data! Let’s query it and see what it looks like.

SQL> select * from search_words; TOKEN_ID TOKEN()
---------- --------------------
1 ANYDATA()
2 ANYDATA()
3 ANYDATA()
4 ANYDATA()
5 ANYDATA()

Oops! That’s not how we want our data to look. Unfortunately, Oracle is not yet sophisticated enough to understand which data type it is querying. Because of this, we have to make a function.

CREATE OR REPLACE FUNCTION getTokens(pv_token in sys.anydata)
RETURN VARCHAR2 IS

lv_number      NUMBER;
lv_date     DATE;
lv_varchar2 VARCHAR2(4000);

BEGIN

CASE pv_token.getTypeName
WHEN 'SYS.NUMBER' THEN
IF (pv_token.getNumber(lv_number) = dbms_types.success) THEN
lv_varchar2 := lv_number;
END IF;
WHEN 'SYS.DATE' THEN
IF (pv_token.getDate(lv_date) = dbms_types.success) THEN
lv_varchar2 := lv_date;
END IF;
WHEN 'SYS.VARCHAR2' THEN
IF (pv_token.getVarchar2(lv_varchar2) = dbms_types.success) THENa
NULL;
END IF;
ELSE
lv_varchar2 := 'unknown datatype';
END CASE;

RETURN lv_varchar2;

END getTokens;

Notice that we used the ANYDATA methods again, but this time with getVarchar2, getNumber, and getDate. Finally, let’s go ahead and query the table using the function we created.

SQL> column token format a20
SQL> select token_id, getTokens(token) token from search_words;

TOKEN_ID TOKEN
---------- --------------------
1 Make
2 50
3 dollars
4 on
5 01-DEC-06

And we have our data! Since we have to use a function, you can see why this would not be very good in a WHERE clause. However, for storing multiple datatypes in a single table, it’s tops.

You can find more information about ANYDATA in the Database Application Developer’s Guide, and information about its methods in the PL/SQL Packages and Types Reference.

Join me next time to learn about pulling XML from the database…with any query!

It seems I’ve run out of time in the day…I had hoped to provide an article today on Trick #3, yet work has piled up and I was not able to get it done.

Rest assured, despite my previous article, that I did not spend all day playing with my new iPod!

Tomorrow (well, today really, as it’s past midnight) I will try to catch up with Tricks 3 and 4. Stay tuned!