Tony’s Oracle Tips

September 23, 2008

Can an online redo log be too big?

Filed under: Uncategorized — tonyhasler @ 7:12 pm

I was asked this question recently in an interview and muffed the answer. Being somewhat flustered by the barrage of questions in my ear I stated that if an online redo log was too large then the cost of a checkpoint would rise. I delivered this answer with confidence but I was unsure if I was right. Now I am sure I was wrong!

Checkpointing is the process that Oracle uses to flush dirty buffers out of the buffer cache back to their “home” locations in the datafiles. This process avoids the need to “replay” the changes to these blocks should instance recovery be required. It must occur each time a log switch occurs but may happen more frequently.  I was thinking that the larger the redo log the longer the interval between checkpoints would be and the longer said checkpoints would therefore take.

Baloney!  Oracle 9i introduced a feature known as “Checkpoint Autotuning” which causes checkpoints to occur frequently enough to avoid lengthy recoveries even if you make your online redo logs huge.  The only way to force long checkpoints is to explicitly set the FAST_START_MTTR_TARGET initialisation parameter to zero.  According to this article (http://forums.oracle.com/forums/thread.jspa?messageID=2644477) on the OTN forums the default value of zero is different from the explicitly set value of zero!  Now I don’t feel so bad for messing up!

 

P.S. I got the job!

September 15, 2008

Partitioned outer join funny

Filed under: SQL Fun — tonyhasler @ 2:39 pm

Look at these two queries designed to run in the SCOTT schema:


select * from dept d partition by (dname)
                   right join emp e on d.deptno=e.deptno;
select * from dept d partition by (deptno)
                   right join emp e on d.deptno=e.deptno;

A partitioned outer join splits one of the two tables in the query into partitions with all rows in each partition having the same value for the specified column(s). The first example above splits the four rows in the “dept” table into four partitions each of which has one row. These four partitions are then individually joined to the “emp” table with 14 rows each. The result is 56 rows – one for each combination of “emp” and “dept” rows.

I would have thought the same result would apply when one partitioned the “dept” table by “deptno” instead of “dname”. However, you only get 14 rows, one for each row in “emp”.

The result is the same when run on 10gR2 and 11g. I think this is a bug. As confirmation I tried this:


select * from dept d partition by (deptno)
                   right join emp e on d.deptno+1=e.deptno+1;

And this returned 56 rows!

September 7, 2008

SQL Puzzle 2 – What can we talk about?

Filed under: SQL Fun — tonyhasler @ 1:11 am

Ever been with a group of strangers and struggled to find a topic of conversation that you are all interested in?

Well if you have then these SQL statements are for you. We begin with two tables INTERESTS and PEOPLE. A third table PERSON_INTEREST_MAP is used to describe the many-to-many relationship between the first two tables, i.e. what interests each person has. Let us begin by creating the three tables and populating them:


DROP TABLE sqlfun.person_interest_map;
DROP TABLE sqlfun.people;
DROP TABLE sqlfun.interests;

CREATE TABLE sqlfun.people
(person_name VARCHAR2(100), person_id INTEGER  PRIMARY KEY);

CREATE TABLE sqlfun.interests
(interest_name VARCHAR2(100), interest_id INTEGER PRIMARY KEY);

CREATE TABLE sqlfun.person_interest_map
(
  person_id INTEGER,
  interest_id INTEGER
);

ALTER TABLE sqlfun.person_interest_map ADD (
  CONSTRAINT person_interest_map_r01
 FOREIGN KEY (interest_id)
 REFERENCES sqlfun.interests (interest_id),
  CONSTRAINT person_interest_map_r02
 FOREIGN KEY (person_id)
 REFERENCES sqlfun.people (person_id));

INSERT INTO sqlfun.people (person_name, person_id) VALUES ('Adam', 1);
INSERT INTO sqlfun.people (person_name, person_id) VALUES ('Bob', 2);
INSERT INTO sqlfun.people (person_name, person_id) VALUES ('Charlie', 3);

INSERT INTO sqlfun.interests (interest_name, interest_id) VALUES ('TV', 1);
INSERT INTO sqlfun.interests (interest_name, interest_id) VALUES ('Sports', 2);
INSERT INTO sqlfun.interests (interest_name, interest_id) VALUES ('Politics', 3);
INSERT INTO sqlfun.interests (interest_name, interest_id) VALUES ('Tony''s blog', 4);

INSERT INTO sqlfun.person_interest_map (person_id, interest_id) VALUES (1, 1);
INSERT INTO sqlfun.person_interest_map (person_id, interest_id) VALUES (1, 2);
INSERT INTO sqlfun.person_interest_map (person_id, interest_id) VALUES (1, 4);
INSERT INTO sqlfun.person_interest_map (person_id, interest_id) VALUES (2, 1);
INSERT INTO sqlfun.person_interest_map (person_id, interest_id) VALUES (2, 3);
INSERT INTO sqlfun.person_interest_map (person_id, interest_id) VALUES (2, 4);
INSERT INTO sqlfun.person_interest_map (person_id, interest_id) VALUES (3, 2);
INSERT INTO sqlfun.person_interest_map (person_id, interest_id) VALUES (3, 3);
INSERT INTO sqlfun.person_interest_map (person_id, interest_id) VALUES (3, 4);

Ok, we can see that our three people have three interests each. Here are our two queries:



SELECT interest_name
  FROM interests
       NATURAL LEFT JOIN
       (SELECT i.interest_id, 1 dummy
          FROM people p CROSS JOIN interests i
               LEFT JOIN person_interest_map m
               ON m.person_id = p.person_id
             AND m.interest_id = i.interest_id
         WHERE m.person_id IS NULL) i
 WHERE dummy IS NULL;

SELECT DISTINCT interest_name
           FROM interests i
          WHERE NOT EXISTS (
                   SELECT NULL
                     FROM people p
                    WHERE NOT EXISTS (
                             SELECT NULL
                               FROM person_interest_map m
                              WHERE m.person_id = p.person_id
                                AND m.interest_id = i.interest_id));

First question: what is the difference between the two queries? Second question: what do they do?

To see the answers see the next page:

September 3, 2008

Change session id in APEX URL

Filed under: SQL Fun — tonyhasler @ 10:44 pm

Some of the more difficult questions to get answered are those that require expertise in more than one area.  I recently posted a question on OTN about forcing password changes during an Application Express login when a password expired.  I was given a couple of options but elected to redirect to the password change page using OWA_UTIL.REDIRECT_URL from a post-authentication routine in my authentication scheme.  However, the bit that didn’t get answered was how to change the session ID in the middle of the FSP_AFTER_LOGIN_URL to avoid the user having to login again after the password change. 

Providing you are using 10g you can use regular expressions to do this.  The following expression returns the correct result even when the URL (the bind variable FSP_AFTER_LOGIN_URL has colons replaced by vertical bars) is very short or non-existent.  You can use this in a page computation to set another page item and then use that item as the basis of a branch.  The code assumes that page 13 is the application home page.  Remember to change it if you cut and paste this code.


CASE
    WHEN :fsp_after_login_url IS NOT NULL
    THEN REGEXP_REPLACE (REPLACE (:fsp_after_login_url, '|', ':'),
                           '^(.*?)(:|$)(.*?)(:|$)(.*?)(:|$)(.*)$',
                           '\1:\3:' || v ('SESSION') || ':\7'
                         )
     ELSE 'f?p=' || :app_id || ':13:' || v ('SESSION')
END

Let us work through this.   Firstly, we use standard replace to change the ‘|’ characters back to ‘:’s.  The first bracked section (group) in the pattern is just an arbitrary string.  The ‘?’ makes the string as short as possible.  However, the second group must be a colon or the end of the string so the first group picks up everything up to the first colon (or the whole line if there isn’t one).  The same logic means that the third group must be the page number and the fifth group the old session.  Note that if either or both of these strings are not supplied then the groups are null.  The seventh group is any remainder.

Because the pattern starts with ‘^’ (beginning of line) and ends with ‘$’ (end of line) the entire string is replaced.  The replacement string places the value of the APEX item SESSION into the URL in place of the fifth group.

Here are some example test results:



set echo off
set heading off
set feedback off

VARIABLE fsp_after_login_url varchar2(200);

EXEC :fsp_after_login_url := 'f?p=123|99|old_session|a|b|c';

SELECT CASE
          WHEN :fsp_after_login_url IS NOT NULL
             THEN REGEXP_REPLACE (REPLACE (:fsp_after_login_url, '|', ':'),
                                  '^(.*?)(:|$)(.*?)(:|$)(.*?)(:|$)(.*)$',
                                  '\1:\3:' || 'NEW_SESSION' || ':\7'
                                 )
          ELSE 'NOT APPLICABLE'
       END RESULT
  FROM DUAL;

EXEC :fsp_after_login_url := 'f?p=123|99|old_session';

/

EXEC :fsp_after_login_url := 'f?p=123|99';

/
EXEC :fsp_after_login_url := 'f?p=123';

/

results are:



f?p=123:99:NEW_SESSION:a:b:c

f?p=123:99:NEW_SESSION:

f?p=123:99:NEW_SESSION:

f?p=123::NEW_SESSION:

Create a free website or blog at WordPress.com.