DB_BLOCK_SIZE and DB_FILE_MULTIBLOCK_READ_COUNT 4 – What is Wrong with this Quote?

5 12 2010

Decmber 5, 2010

(Back to the Previous Post in the Series)

I recently reviewed the book “Oracle Tuning the Definitive Reference Second Edition”, and did not provide an in-depth technical review of the entire book.  As I stated in a comment in the earlier blog article, I would like to clarify that my review on the Amazon site is not intended to suggest that people should not buy the book. As the publisher’s website states that the book is written for senior Oracle DBAs, I suggest that senior DBAs, Oracle Certified Masters, and other people who are able to research the book’s contents *should* buy the book and post reviews of the book that highlight specific content of the book indicating whether or not that content is correctly stated. Such feedback will be a benefit to the Oracle community, and could help to improve Oracle Database books that are published in the future. I think that we need to keep a positive outlook in order to see things improve

With that said, what is wrong with the following quote from page 530 of the book (please excuse the length of this quote, I tried to make it as short as possible without losing the context of the material, and without destroying the sentence structure)?

“Remember, the db_file_multiblock_read_count parameter is used to tell Oracle how many blocks to retrieve in the single I/O operation and the setting is platform-dependent. The most common settings ranged from 4 to 64 blocks per single multi-block I/O execution.

The ‘automatically tuned’ db_file_multiblock_read_count in 10gr2 and beyond uses external disk workload statistics that are gathered via the dbms_stats.gather_system_stats package to determine the optimal setting.

A sub-optimal setting for db_file_multiblock_read_count can running SQL performance because it can cause the optimizer to favor full-scan access. This would cause some beginners to adjust for this by turning the wrong knob, lowering the setting for optimizer_index_cost_adj instead of using dbms_stats.gather_system_stats.

10gr2 and beyond, the db_file_multiblock_read_count is not used to estimate the average number of blocks read and a separate metric for the estimated number of actual block reads. Instead, the optimizer computes two new values, one for optimizer costing and another for the number of I/O requests.”

What, if anything, is wrong with the above quote from the book?

The point of blog articles like this one is not to insult authors who have spent thousands of hours carefully constructing an accurate and helpful book, but instead to suggest that readers investigate when something stated does not exactly match what one believes to be true.  It could be that the author “took a long walk down a short pier”, or that the author is revealing accurate information which simply cannot be found through other resources (and may in the process be directly contradicting information sources you have used in the past).  If you do not investigate in such cases, you may lose an important opportunity to learn something that could prove to be extremely valuable.





DB_BLOCK_SIZE and DB_FILE_MULTIBLOCK_READ_COUNT 3 – What is Wrong with this Quote?

4 12 2010

December 4, 2010

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

I recently reviewed the book “Oracle Tuning the Definitive Reference Second Edition”, and did not provide an in-depth technical review of the entire book.  As I stated in a comment in the earlier blog article, I would like to clarify that my review on the Amazon site is not intended to suggest that people should not buy the book. As the publisher’s website states that the book is written for senior Oracle DBAs, I suggest that senior DBAs, Oracle Certified Masters, and other people who are able to research the book’s contents *should* buy the book and post reviews of the book that highlight specific content of the book indicating whether or not that content is correctly stated. Such feedback will be a benefit to the Oracle community, and could help to improve Oracle Database books that are published in the future. I think that we need to keep a positive outlook in order to see things improve

With that said, what is wrong with the following quote from page 719 of the book (please ignore the error in the SQL statement found on that page for the moment)?

“Four factors synchronize to help the CBO choose whether or use an index or a full-table scan:

7.  The selectivity of a column value
8.  The db_block_size
9.  The avg_row_len
10. The cardinality

An index scan is usually faster if a data column has high selectivity and a low clustering_factor as shown in Figure 15.8.”

What, if anything, is wrong with the above quote from the book?

The point of blog articles like this one is not to insult authors who have spent thousands of hours carefully constructing an accurate and helpful book, but instead to suggest that readers investigate when something stated does not exactly match what one believes to be true.  It could be that the author “took a long walk down a short pier”, or that the author is revealing accurate information which simply cannot be found through other resources (and may in the process be directly contradicting information sources you have used in the past).  If you do not investigate in such cases, you may lose an important opportunity to learn something that could prove to be extremely valuable.

A Google book search indicates that a slightly modified version of the statement appeared in a couple of other books:

Other pages found during a Google search of the phrase:

—————–

For some reason, the following quote seems to come to mind:

“Fool me once, shame on me.  Fool me twice, shame on you.” (reference)





DB_BLOCK_SIZE and DB_FILE_MULTIBLOCK_READ_COUNT 2 – What is Wrong with this Quote?

3 12 2010

December 3, 2010

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

I recently reviewed the book “Oracle Tuning the Definitive Reference Second Edition”, and did not provide an in-depth technical review of the entire book.  As I stated in a comment in the earlier blog article, I would like to clarify that my review on the Amazon site is not intended to suggest that people should not buy the book. As the publisher’s website states that the book is written for senior Oracle DBAs, I suggest that senior DBAs, Oracle Certified Masters, and other people who are able to research the book’s contents *should* buy the book and post reviews of the book that highlight specific content of the book indicating whether or not that content is correctly stated. Such feedback will be a benefit to the Oracle community, and could help to improve Oracle Database books that are published in the future. I think that we need to keep a positive outlook in order to see things improve.

With that said, what is wrong with the following quote from page 749 of the book?

“Oracle says that if the query returns less than 40 percent of the table rows in an ordered table or seven percent of the rows in an unordered table, the query can be tuned to use an index in lieu of the full-table scan, but in reality there is no fixed number because it depends on many factors like the db_block_size and db_file_multiblock_read_count.”

“… The most common cause of unnecessary full-table scans is a optimizer_mode that favors full-table scans (like all_rows) or a missing index, especially a function-based indexes.”

What, if anything, is wrong with the above quotes from the book?

The point of blog articles like this one is not to insult authors who have spent thousands of hours carefully constructing an accurate and helpful book, but instead to suggest that readers investigate when something stated does not exactly match what one believes to be true.  It could be that the author “took a long walk down a short pier”, or that the author is revealing accurate information which simply cannot be found through other resources (and may in the process be directly contradicting information sources you have used in the past).  If you do not investigate in such cases, you may lose an important opportunity to learn something that could prove to be extremely valuable.

A Google book search indicates that the first statement appeared in a couple of other books:

Other pages found during a Google search of the first phrase:





DB_BLOCK_SIZE and DB_FILE_MULTIBLOCK_READ_COUNT – What is Wrong with this Quote?

2 12 2010

December 2, 2010

(Forward to the Next Post in the Series)

It has been a couple of months since the last blog article that asked “What is Wrong with this Quote”, so I thought that I would try to add a couple of more blog articles to this series.  I recently reviewed the book “Oracle Tuning the Definitive Reference Second Edition”, but my review stopped when it extended to 24 pages (12 point Times New Roman font, 1 inch margins).   Why 24 pages?  It was probably just coincidence.

Page 531 of the book states the following:

“When multiple blocksizes are implemented, the db_block_size should be set based on the size of the tablespace where the large object full scans will be occurring.  The db_file_multiblock_read_count parameter is only applicable for tables/indexes that are full scanned.”

“With the implementation of multiple blocksizes, Oracle MetaLink notes that the db_file_multiblock_read_count should always be set to a value that sums to the largest supported blocksize of 32k.”

Examples are provided in the book that show that the value for the DB_FILE_MULTIBLOCK_READ_COUNT parameter multiplied by the value for the DB_BLOCK_SIZE parameter should always equal 32KB.  Thus, with a database block size of 16KB, the DB_FILE_MULTIBLOCK_READ_COUNT parameter value should be set to 2.

What, if anything, is wrong with the above quotes from the book?

The point of blog articles like this one is not to insult authors who have spent thousands of hours carefully constructing an accurate and helpful book, but instead to suggest that readers investigate when something stated does not exactly match what one believes to be true.  It could be that the author “took a long walk down a short pier”, or that the author is revealing accurate information which simply cannot be found through other resources (and may in the process be directly contradicting information sources you have used in the past).  If you do not investigate in such cases, you may lose an important opportunity to learn something that could prove to be extremely valuable.

A Google search found similar information here:





Down for the Count – Multiple Choice Quiz

8 08 2010

August 8, 2010 (Modified August 8, 2010, August 10, 2010)

I am not much of a supporter of True or False type questions, nor do I much care for multiple choice type questions.  It seems that essay questions are usually the only appropriate type of questions on exams.  Take, for example the following question that might appear on a SQL exam:

Three, and only three, user sessions are connected to the database.  Session 1 creates 2 tables using the following commands:

DROP TABLE T1 PURGE;
DROP TABLE T2 PURGE;
CREATE TABLE T1(COL1 NUMBER);
CREATE TABLE T2(COL1 NUMBER);

These are the only tables in the database named T1 and T2, as shown by the following:

SELECT
  TABLE_NAME
FROM
  DBA_TABLES
WHERE
  TABLE_NAME IN ('T1','T2');

TABLE_NAME
------------------------------
T1
T2

The following operations are performed in order:

In Session 1:

INSERT INTO T1
SELECT
  ROWNUM
FROM
  DUAL
CONNECT BY
  LEVEL<=100;

COMMIT;

INSERT INTO T1
SELECT
  ROWNUM
FROM
  DUAL
CONNECT BY
  LEVEL<=100;

In Session 2:

INSERT INTO T1
SELECT
  ROWNUM
FROM
  DUAL
CONNECT BY
  LEVEL<=100;

INSERT INTO T2
SELECT
  *
FROM
  T1;

COMMIT;

INSERT INTO T2
SELECT
  *
FROM
  T2;

In Session 3:

SELECT
  COUNT(*)
FROM
  T2;

What value is displayed when session 3 executes its query against table T2?
a.  600
b.  200
c.  100
d.  0
e.  All of the above
f.  None of the above

After you answer the multiple choice question, explain why your answer is correct.

————–
Edit roughly 4.5 hours after the initial publishing of this blog article: 7 people, including the first person to comment (Sean) saw a value of 1600 for answer a. – that value was modified within minutes of the first comment appearing in this article to the value 600.  The number 1600 was either a typo or an answer that I thought no one would ever select.  For fun, let’s add the following to the list of possible answers for the question… could it be the correct answer?:
a2.  1600

—————-

Edit August 10, 2010: The Test Answers:

As I stated a couple of days ago, I intend to reveal the correct answer to the question posed by this blog article.  As mentioned by Gary, just because a person (or certification board) designs a test question, that by default does not mean that the person knows the correct answer to the question.

Within roughly 15 minutes of this blog post appearing on Sunday Sean provided a very good answer with strong justification.  If I only had 60 seconds to answer the question, I would hope to be able to work out the same solution.  That said, the WordPress blog article category for this article is “Quiz – Whose Answer is it Anyway?” and answers E and F seem to be a bit of a clue that something is a bit strange about this question.  I had more than 60 seconds to think about the answer, so I will pick one of the other answers.

I suspect that several readers were a bit suspicious about the question in this blog article that just might appear on some sort of standardized test (I designed the question, so I hope that it does not).  Gary, Joel Garry, and Martin Berger stepped up to the challenge and offered suggestions regarding how there *might* be more than one answer to the provided question.  As you might guess, the numbers selected for the answers are not random.  I thought that I would share with you the thoughts that I had when putting together the potential test answers.

——

b. 200 – assumes that we are using SQL*Plus or some other tool that does not auto-commit after every DML call, have not modified the environment of the tool, and do not closed the tool after the script for each session was executed – SQL*Plus commits by default when exiting.  This is probably the right answer.

——

a. 600 – assumes that we are using a tool that auto-commits after every DML statement by default, or possibly using a database (other than Oracle) that auto-commits after every DML statement.  This could be a Visual Basic (or VBScript) program using ODBC or OLEDB (I might have stated ADO before) that does not start a transaction with a controllable end-point unless the BeginTrans method of the connection object is called first – or it could just as easily be a Java program using JDBC that does not call the setAutoCommit method of the connection object with a parameter of false.  Gary mentioned another possibility.

——

d. 0 – the question states that there are three and exactly three sessions connected to the database, so it probably is not reasonable to connect or disconnect from the database.  This is an instance where a pre-existing condition in one of the sessions might cause problems.  For example, assume that these three sessions had been connected for a while and one of the previous questions in the test asked about the serializable isolation level.  So, at some point in session 3 the command ALTER SESSION SET ISOLATION_LEVEL=SERIALIZABLE; was executed and a transaction started – maybe something as simple as: SELECT * FROM TABLE2 WHERE 0=1 FOR UPDATE;  With this isolation level, once a transaction begins, the answers to all SQL statements are as of the start time for the transaction.  Session 3 will therefore see a count of 0 for table T2.

Another possibility is that either session 2 or (mutually exclusive) session 3 is connected as a different user.  The problem is that the test showed that there is only one table T1 and one table T2 in the database.  Suppose that one of the earlier test questions asked about synonyms and a table named TABLE2 was created in session 1 with a public synonym name of T2.  If session 2 is connected as a different user, it will actually insert into TABLE2 when attempting to insert into table T2, and session 3 will report the number of rows in the real table T2.  Note that the output does not show whether or not the inserts were successful, so it is possible that session 2 could not resolve the name T2 and returned an error.  If session 3 is connected as a different user, it will report the number of rows in table TABLE2, rather than T2.

Another possibility is that either session 2 or session 3 is connected as a different user and a public synonym points to a view created on table T2 that includes a WHERE clause of COL1=0.  Note that the output does not show whether or not the inserts were successful, so the view could have been created WITH CHECK OPTION.

——

c. 100 – the easiest way to obtain this value is if session 2 had at some point issued the command ALTER SESSION SET ISOLATION_LEVEL=SERIALIZABLE; and already had an in-process transaction (maybe SELECT * FROM TABLE2 WHERE 0=1 FOR UPDATE;).

A second possibility of obtaining a value of 100 is if at some point in the past session 1 executed the following commands:

CREATE OR REPLACE VIEW VIEW_T2 AS
SELECT
  *
FROM
  T2
WHERE
  COL1<=50;
 
GRANT ALL ON VIEW_T2 TO PUBLIC;
 
CREATE PUBLIC SYNONYM T1 FOR T1;
CREATE PUBLIC SYNONYM T2 FOR VIEW_T2;

In this case, sessions 2 and 3 are logged in as the same user, which is different from session 1.

——

a2. 1600 – you might be able to guess how I obtained this number.  I performed the test several times, obtaining different results each time, not realizing that session 1 could not drop tables T1 and T2 because session 2 had an active transaction referencing one or both of those tables (maybe you could not guess how I obtained that number).

How else might you obtain a value of 1600?  Assume that at some point in the past session 1 executed the following commands:

CREATE TABLE TABLE2(COL1 NUMBER);
GRANT ALL ON TABLE2 TO PUBLIC;
CREATE PUBLIC SYNONYM T1 FOR T1;
CREATE PUBLIC SYNONYM T2 FOR TABLE2;
 
CREATE OR REPLACE TRIGGER T2_POPULATE AFTER INSERT ON TABLE2
BEGIN
  DELETE FROM T2;
 
  INSERT INTO
    T2
  SELECT
    ROWNUM
  FROM
    DUAL
  CONNECT BY
    LEVEL<=1600;
END;
/

Now every time a transaction inserts into table TABLE2 all rows will be deleted from table T2 and 1600 new rows will be inserted into table T2.  Session 1 and session 3 are connected to the database as the same user.  Session 2 is connected to the database as a different user.

——
 
e. All of the Above – all answers were obtained without violating what the question asked, so I guess that e is the correct answer.

——

f. None of the Above – the count of table T2 will produce a single resulting value, not 5 values, and the value returned could very well be something other than the 5 values listed, depending on the prior state of the database.

————————

How else might you obtain the different potential answers listed for the test question?





PGA Memory and PGA_AGGREGATE_TARGET, is there Something Wrong with this Quote?

4 08 2010

August 4, 2010

I started reading the “Expert Oracle Database Architecture: Oracle Database 9i, 10g, and 11g Programming Techniques and Solutions” book cover-to-cover.  Very interesting book so far, and it appears that a lot of time was spent reworking the previous release of the book so that it targets Oracle Database 11g R2, while not losing the book’s usefulness for prior releases of Oracle Database.  On page 131 of the book I found an interesting quote that made me wonder… is there something wrong with this quote (roughly the same quote is found on page 125 of the first edition of the book)?

“… You may well have multiple work areas in your session for multiple queries, or more than one sort or hash operation in a single query, but each work area will be about 5 percent or less of the PGA_AGGREGATE_TARGET.”

Visit the above Google books links above for the quote’s context.

Is there something wrong with the above quote?  (Possible hint _SMM_MAX_SIZE.)





Redo Log Buffer – What is Wrong with this Quote?

24 06 2010

June 24, 2010

I am in the process of reading the June 2010 printing (fourth printing) of the “Oracle Performance Firefighting” book.  To say that I have stumbled upon one or two gems in the book is probably an understatement.  The book is filled with interesting statements – this time I found a couple of interesting paragraphs that describe several initialization parameters and their effect on the redo log buffer prior to Oracle 9i R2, and the current behavior.

Redo Log Buffer Prior to Oracle Database 9i R2, page 297:

Having a single redo allocation latch makes enforcing redo serialization very straightforward. But as you can imagine, having a single redo allocation latch also can become a point of contention. To reduce the likelihood of this, server processes hold the allocation latch just long enough to allocate redo log buffer space. There is also the instance parameter _log_small_entry_max_size, which is used to shift allocation latch activity onto one of the redo copy latches, as discussed in the ‘Redo Allocation Latch Contention’ section later in this chapter. To further reduce the contention possibilities, Oracle allows for multiple redo copy latches. The instance parameter _log_simultaneous_copies is used to control the number of redo copy latches.

Redo Log Buffer Oracle Database 9i R2 and Later, page 298:

By default, the number of redo strands is dynamic, but it can be made static by setting the hidden instance parameter _log_parallelism_dynamic to false. When Oracle is dynamically controlling the number of redo strands, the maximum number of strands is controlled by the hidden instance parameter _log_parallelism_max. The DBA can specifically set the number of redo strands via the hidden parameter _log_parallelism. The default number of redo strands is surprisingly low—perhaps two.

What, if anything, is wrong with the above quotes from the book (it is possible that nothing is wrong)?  Keep in mind that these blog articles are intended to generate discussion – be sure to check any and all comments attached to the articles in this series.  The comment section is where the heart of the blog article material in this series will be found.

The point of blog articles like this one is not to insult authors who have spent thousands of hours carefully constructing an accurate and helpful book, but instead to suggest that readers investigate when something stated does not exactly match what one believes to be true.  It could be that the author “took a long walk down a short pier”, or that the author is revealing accurate information which simply cannot be found through other resources (and may in the process be directly contradicting information sources you have used in the past).  If you do not investigate in such cases, you may lose an important opportunity to learn something that could prove to be extremely valuable.





Undo Segments – What is Wrong with this Quote?

21 06 2010

June 21, 2010

I located another interesting section of a paragraph in the June 2010 printing of the “Oracle Performance Firefighting” book.  From page 231:

“By default, Oracle tries to assign only one active transaction per undo segment. If each undo segment has an active transaction and if there is space available in the undo tablespace, Oracle will automatically create an additional undo segment. This usually takes care of the buffer busy waits. However, if there is no more room in the undo segment tablespace, multiple transactions will be assigned to each undo segment, and eventually undo segment header contention will result. The solution is to add another database file to your undo segment tablespace, thus enabling Oracle to create additional undo segments. It’s that simple.”

What, if anything, is wrong with the above quote from the book (it is possible that nothing is wrong)?  Keep in mind that these blog articles are intended to generate discussion – be sure to check any and all comments attached to the articles in this series.  The comment section is where the heart of the blog article material in this series will be found.

The point of blog articles like this one is not to insult authors who have spent thousands of hours carefully constructing an accurate and helpful book, but instead to suggest that readers investigate when something stated does not exactly match what one believes to be true.  It could be that the author “took a long walk down a short pier”, or that the author is revealing accurate information which simply cannot be found through other resources (and may in the process be directly contradicting information sources you have used in the past).  If you do not investigate in such cases, you may lose an important opportunity to learn something that could prove to be extremely valuable.





Database Writer Parameters – What is Wrong with this Quote?

18 06 2010

June 18, 2010

While reading the June 2010 printing of the “Oracle Performance Firefighting” book I keep stumbling across interesting sections of the book.  Here is an interesting section – I hope that I did not exclude too much of the surrounding section of the book, causing the paragraph to lose contextual meaning (I am trying to balance how much typing I need to do with how much needs to be quoted to avoid losing the meaning of the material).  From page 219:

“From an Oracle perspective, I start thinking of any instance parameter that may increase Oracle’s IO writing efficiency. For example, I would investigate looking for a way to increase the database writer’s batch write size. As I mentioned, there are version-specific ways to alter the database writer’s batch size. Investigate the _db_block_write_batch and the _db_writer_max_writes parameters. Also considering increasing the instance parameter _db_writer_max_scan_pct (the default may be 40, for 40%) or _db_writer_max_scan_cnt, as they determine how many LRU buffer headers a server process will scan before it signals the database writer to start writing. Increasing these parameters provides more time for the write list to build up, and therefore results in more blocks written per database writer IO request.”

What, if anything, is wrong with the above quote from the book?  Keep in mind that these blog articles are intended to generate discussion – be sure to check any and all comments attached to the articles in this series.  The comment section is where the heart of the blog article material in this series will be found.

The point of blog articles like this one is not to insult authors who have spent thousands of hours carefully constructing an accurate and helpful book, but instead to suggest that readers investigate when something stated does not exactly match what one believes to be true.  It could be that the author “took a long walk down a short pier”, or that the author is revealing accurate information which simply cannot be found through other resources (and may in the process be directly contradicting information sources you have used in the past).  If you do not investigate in such cases, you may lose an important opportunity to learn something that could prove to be extremely valuable.





_SMALL_TABLE_THRESHOLD Parameter and Buffer Cache – What is Wrong with this Quote?

17 06 2010

June 17, 2010

Warning: this article is certain to bring tears of pain to your eyes while you attempt to determine what is right and what is wrong.

While reading the June 2010 printing of the “Oracle Performance Firefighting” book I found a couple of interesting lines about the buffer cache and the effects, or lack there-of, of the _SMALL_TABLE_THRESHOLD parameter.  This quote is from page 208 (this is a long quote, required to not lose the context of the discussion topic - please excuse any typos):

“The single most radical departure from the modified LRU algorithm [introduced in Oracle Database 6, per the book] is known as midpoint insertion [introduced in Oracle Database 8.1.5, per the book]. Each LRU chain is divided into a hot and cold region. When a buffer is read from disk and a free buffer has been found, the buffer and buffer header replace the previous buffer and buffer header contents, and then the buffer header is moved to the LRU chain midpoint. Single-block read, multiblock read, fast-full scan, or full-table scan—it makes no difference. The buffer header is not inserted at the MRU end of the LRU chain, but rather at the midpoint…

Because the window scheme used in the modified LRU algorithm is no longer used, the hidden instance parameter _small_table_threshold became deprecated. However, in Oracle Database 11g, it is being used again, but for a different purpose. Starting with this version, the _small_table_threshold parameter is the threshold for a server process to start issuing direct reads.”

Before deciding what about the above is correct or incorrect, take a look at Metalink Doc ID 787373.1.  A couple of words from that document to encourage you to take a look at the original material:

“Applies to Version: 9.0.1.0 to 11.2.0.1.0: When loading objects and data into the buffer cache… best way to load for full scans. This functionality is called smallness logic and is driven by a hidden parameter called: _small_table_threshold. … Oracle will load the object via the buffer cache as this is more efficient than doing a direct read operation.”

Before deciding what about the above Metalink document is correct or incorrect, take a look at this AskTom article.  A couple of words from that article:

“no, you are not correct on your guess about the [_SMALL_TABLE_THRESHOLD] parameter, it controls the caching of the blocks (whether they are cached like a single block IO or cached as we cache full scan blocks) – not the method of IO.”

Before deciding what about the above AskTom article is still correct, take a look at this blog article.  A couple of words from that article:

“When direct path reads starts to happen?  It is known that somewhat reliable figure is your _small_table_threshold multiplied by 5 (mentioned by Tanel Poder on oracle-l recently).”

One final blog article, from the comments section:

“A table is deemed ‘small’ by Oracle if it’s 2% or less of the buffer cache in size. A small table is ‘cached’ when read via a FTS and is not immediately loaded into the least recently used end of the LRU list as becomes a ‘large’ table read via a FTS…”

“When a block is read from a ‘large’ table via a FTS, the blocks are basically loaded into the LRU end of the LRU list, even though they’ve only just been loaded into the buffer cache. As such, the chances of them remaining in the buffer cache is minimal and will likely be immediately overwritten once processed.

However, when a block is read via an index, the blocks are loaded somewhere near the ‘middle’ of the LRU list, not at the LRU end of the list…”

What, if anything, is wrong with the above quote from the book?  Bonus question, what, if anything, is wrong with the Metalink document?  Maybe there is a problem with the other quotes? 

The point of blog articles like this one is not to insult authors who have spent thousands of hours carefully constructing an accurate and helpful book, but instead to suggest that readers investigate when something stated does not exactly match what one believes to be true.  It could be that the author “took a long walk down a short pier”, or that the author is revealing accurate information which simply cannot be found through other resources (and may in the process be directly contradicting information sources you have used in the past).  If you do not investigate in such cases, you may lose an important opportunity to learn something that could prove to be extremely valuable.





System/Kernel Mode CPU Usage – What is Wrong with this Quote?

16 06 2010

June 16, 2010

While reading the June 2010 printing of the “Oracle Performance Firefighting” book I found an interesting paragraph that left me wondering if something more, or perhaps something else should be stated.  This quote is from page 104 (this is a long quote, required to not lose the context of the discussion topic - please excuse any typos):

“System time: This is when a core is spending time processing operating system kernel code. Virtual memory management, process scheduling, power management, or essentially any activity not directly related to a user task is classified as system time. From an Oracle-centric perspective, system time is pure overhead. It’s like paying taxes. It must be done, and there are good reasons (usually) for doing it, but it’s not under the control of the business—it’s for the government. Normally, Oracle database CPU subsystems spend about 5% to 40% of their active time in what is called system mode. If you’re from a non-Unix background, you may be more familiar with the term kernel mode or privileged mode, which is, in essence, system time.”

What, if anything, is wrong with the above quote from the book? 

The point of blog articles like this one is not to insult authors who have spent thousands of hours carefully constructing an accurate and helpful book, but instead to suggest that readers investigate when something stated does not exactly match what one believes to be true.  It could be that the author “took a long walk down a short pier”, or that the author is revealing accurate information which simply cannot be found through other resources (and may in the process be directly contradicting information sources you have used in the past).  If you do not investigate in such cases, you may lose an important opportunity to learn something that could prove to be extremely valuable.





Log File Sync – What is Wrong with this Quote?

16 06 2010

June 16, 2010

While reading the June 2010 printing of the “Oracle Performance Firefighting” book I found an interesting paragraph that left me wondering if something is missing.  This quote is from page 61 (this is a long quote, required to not lose the context of the discussion topic - please excuse any typos):

“As another example, suppose that you receive a call about long commit times. The users are saying ‘submits’ or ‘saves’ are taking longer today than yesterday. Upon an examination of the average log file sync wait times (which indicate commit times from an Oracle perspective), you discover there is no change from the previous day. Therefore, you know the performance issue is not because of Oracle’s commit mechanism and most likely not related to the database.”  [This period marks the end of Part 3, "IO Wait Time Summary with Event Details"]

What, if anything, is wrong with the above quote from the book? 

The point of blog articles like this one is not to insult authors who have spent thousands of hours carefully constructing an accurate and helpful book, but instead to suggest that readers investigate when something stated does not exactly match what one believes to be true.  It could be that the author “took a long walk down a short pier”, or that the author is revealing accurate information which simply cannot be found through other resources (and may in the process be directly contradicting information sources you have used in the past).  If you do not investigate in such cases, you may lose an important opportunity to learn something that could prove to be extremely valuable.





CPU Run Queue – What is Wrong with this Quote?

14 06 2010

June 14, 2010

I found another interesting couple of lines in the June 2010 printing of the “Oracle Performance Firefighting” book.  This quote is from page 116:

“While it may seem strange, the run queue reported by the operating system includes processes waiting to be serviced by a CPU as well as processes currently being serviced by a CPU. This is why you may have heard it’s OK to have the run queue up to the number of CPUs or CPU cores.”

Also, this quote from page 123:

“Have you ever heard someone say, ‘Our CPUs are not balanced. We’ve got to get that fixed.’? You might have heard this if you’re an operating system kernel developer or work for Intel, but not as an Oracle DBA. This is because there is a single CPU run queue, and any available core can service the next transaction.”

Also, this quote from page 136:

“The other area of misrepresentation has to do with time in the CPU run queue. When Oracle reports that a process has consumed 10 ms of CPU time, Oracle does not know if the process actually consumed 10 ms of CPU time or if the process first waited in the CPU run queue for 5 ms and then received 5 ms of CPU time.”

Interesting… regarding the first quote - most of what I have read about the CPU run queue seemed to indicate that the process was removed from the run queue when the process is running on the CPU, and then re-inserted into the run queue when the process stops executing on the CPU (assuming that the process has not terminated and is not suspended).  The “Oracle Performance Firefighting” book lacks a test case to demonstrate that the above is true, so I put together a test case using the CPU load generators on page 197 of the “Expert Oracle Practices” book, the Linux sar command, and a slightly modified version (set to refresh every 30 seconds rather than every second) of the WMI script on pages 198-200 of the “Expert Oracle Practices” book.

For the test, I will use the following command on Linux:

sar -q 30 10

Immediately after the above command is started, a copy of the Linux version of the CPU load generator will be run (the load generator runs for 10 minutes and then exits):

#!/bin/bash
i=0
STime=`date +%s`

while [ `date +%s` -lt $(($STime+$((600)))) ]; do
  i=i+0.000001
done

Every time a new line is written by the sar utility another copy of the CPU load generator is started.  For the first test run I manually launched a new command line from the GUI and then started the script.  For the second test run I first opened as many command line windows as necessary, and prepared each to execute the script.  Here is the output (the runq-sz column shows the run queue):

[root@airforce-5 /]# sar -q 30 10
Linux 2.6.18-128.el5 (airforce-5.test.com)      06/13/2010

05:39:16 PM   runq-sz  plist-sz   ldavg-1   ldavg-5  ldavg-15
05:39:46 PM         1       228      0.76      0.37      0.16
05:40:16 PM         2       230      1.04      0.48      0.21
05:40:46 PM         3       232      1.31      0.59      0.25
05:41:16 PM         4       233      1.86      0.79      0.33
05:41:46 PM         6       237      2.81      1.11      0.45
05:42:16 PM         7       241      3.71      1.48      0.59
05:42:46 PM         9       244      5.56      2.14      0.84
05:43:16 PM        12       247      7.86      3.00      1.16
05:43:46 PM        16       250     10.29      4.04      1.56
05:44:16 PM        14       250     12.03      5.06      1.98
Average:            7       239      4.72      1.91      0.75

[root@airforce-5 /]# sar -q 30 10
Linux 2.6.18-128.el5 (airforce-5.test.com)      06/13/2010

05:50:53 PM   runq-sz  plist-sz   ldavg-1   ldavg-5  ldavg-15
05:51:23 PM         1       237      0.54      3.41      2.76
05:51:53 PM         3       239      1.44      3.35      2.76
05:52:23 PM         3       241      2.20      3.35      2.78
05:52:53 PM         5       242      3.31      3.51      2.85
05:53:23 PM         7       245      4.53      3.78      2.96
05:53:53 PM        10       247      6.40      4.31      3.16
05:54:23 PM        13       249      8.60      5.03      3.43
05:54:53 PM        13       249     10.71      5.87      3.76
05:55:23 PM        16       253     12.16      6.68      4.10
05:55:53 PM        14       251     13.02      7.41      4.42
Average:            8       245      6.29      4.67      3.30

For the sake of comparison, here is a CPU load generator script that executes on Windows that performs the same operation as the script which was executed on Linux:

Dim i
Dim dteStartTime

dteStartTime = Now

Do While DateDiff("n", dteStartTime, Now) < 10
  i = i + 0.000001
Loop

Let’s use the WMI script in place of the Linux sar command and repeat the test.  The WMI script will be started, the CPU load generator script will be started, and every time the WMI script outputs a line another copy of the CPU load generator script will be started.  Here is the output from the WMI script (the Q. Length column shows the run queue):

6/13/2010 6:29:27 PM Processes: 53 Threads: 825 C. Switches: 1757840 Q. Length: 0
6/13/2010 6:29:57 PM Processes: 54 Threads: 826 C. Switches: 32912 Q. Length: 0
6/13/2010 6:30:27 PM Processes: 56 Threads: 831 C. Switches: 71766 Q. Length: 0
6/13/2010 6:30:57 PM Processes: 58 Threads: 836 C. Switches: 39857 Q. Length: 0
6/13/2010 6:31:27 PM Processes: 59 Threads: 830 C. Switches: 33946 Q. Length: 0
6/13/2010 6:31:57 PM Processes: 59 Threads: 821 C. Switches: 27955 Q. Length: 1
6/13/2010 6:32:27 PM Processes: 61 Threads: 830 C. Switches: 32088 Q. Length: 0
6/13/2010 6:32:57 PM Processes: 63 Threads: 826 C. Switches: 27027 Q. Length: 0
6/13/2010 6:33:29 PM Processes: 64 Threads: 827 C. Switches: 22910 Q. Length: 3
6/13/2010 6:34:01 PM Processes: 66 Threads: 836 C. Switches: 22936 Q. Length: 4
6/13/2010 6:34:34 PM Processes: 68 Threads: 839 C. Switches: 34076 Q. Length: 5
6/13/2010 6:35:07 PM Processes: 70 Threads: 840 C. Switches: 25564 Q. Length: 8

What, if anything, is wrong with the above quotes from the book?  The comments in this article might be helpful.

The point of blog articles like this one is not to insult authors who have spent thousands of hours carefully constructing an accurate and helpful book, but instead to suggest that readers investigate when something stated does not exactly match what one believes to be true.  It could be that the author “took a long walk down a short pier”, or that the author is revealing accurate information which simply cannot be found through other resources (and may in the process be directly contradicting information sources you have used in the past).  If you do not investigate in such cases, you may lose an important opportunity to learn something that could prove to be extremely valuable.





DB File Scattered Read Wait Event – What is Wrong with this Quote?

13 06 2010

June 13, 2010 (Modified June 14, 2010)

I found an interesting couple of lines in the June 2010 printing of the “Oracle Performance Firefighting” book.  This quote is from page 43 – I added the text in the square brackets [ ] to provide context for the quote:

Digging a little deeper [into the results of the readv Linux OS call that completed in 0.6ms], we can assert that all the requested blocks must have resided in memory (but not Oracle’s buffer cache memory, since Oracle needed to request them from the operating system). We can make this assertion because a physical spinning IO device cannot return 16 nonsequential blocks (the blocks could be scattered over many physical devices) in less than a single millisecond!

[One sentence removed to cut down the amount of typing for this quote - sentence basically states that Oracle assigns the time consumed to an Oracle wait event.]  Regardless of the operating system or the actual system call, Oracle gives a multiblock IO call the special name db file scattered read, because these multiple blocks can be scattered over the IO subsystem.”

This book was written by Craig Shallahamer, had six technical reviewers (I recognize the names of three), and was printed by Good Catch Publishing.

What, if anything, is wrong with the above quote?

Edit June 14, 2010:
The point of blog articles like this one is not to insult authors who have spent thousands of hours carefully constructing an accurate and helpful book, but instead to suggest that readers investigate when something stated does not exactly match what one believes to be true.  It could be that the author “took a long walk down a short pier”, or that the author is revealing accurate information which simply cannot be found through other resources (and may in the process be directly contradicting information sources you have used in the past).  If you do not investigate in such cases, you may lose an important opportunity to learn something that could prove to be extremely valuable.





True or False – Why Isn’t My Index Getting Used?

25 05 2010

May 25, 2010

(Back to the Previous Post in the Series

The book “Expert One-On-One Oracle” has been on the edge of my desk for the last couple of days.  I cannot recall why I originally pulled it off the bookshelf, but possibly it was needed to satisfy a question about how things used to work in Oracle Database.  I opened the book to a random page, and ended up somewhere around page 303.  Page 303 includes a section title of “Why isn’t my Index Getting Used?”.  This still seems to be a frequent question on various Oracle forums, and volumes have been written on the subject.  A quick Google search finds the following on the topic:

The “Expert One-On-One Oracle” book lists 6 of the cases why an index would not be used.  Some things in Oracle stay the same from one release to the next, while others change.  Which of the following are true or false for Oracle Database 11.1.0.6 or higher, and indicate why the answer is true or false, and if relevant, the Oracle release when the answer changed.  Note that I might intentionally state only part of the case identified in the book, in part to make things more interesting.  Maybe the question of when is it true and when is it not true would be a more realistic request?

Reasons why an index would not be used:

1. The predicate in the WHERE clause does not specify the column at the leading edge of the index.  For example, an index exists on T1(C1, C2) and the WHERE clause in the query is simply WHERE C2=1.

2. The query is attempting to count the number of rows in the table, for example: SELECT COUNT(*) FROM T1;.

3. The query is attempting to apply a function on the indexed columns, for example: SELECT * FROM T1 WHERE TRUNC(DATE_COL) = TRUNC(SYSDATE);.

4. The query is attempting to treat a VARCHAR2/CHAR column that contains only numbers as a numeric column, for example: SELECT * FROM T1 WHERE CHAR_COL = 10;.

5. The index, if used, would be slower, as determined by the calculated cost of the access path.

6. The tables have not been analyzed in a while.  The last time the tables referenced in the WHERE clause were analyzed, the tables were quite small.  Now the tables are significantly larger.  Without up-to-date statistics, the CBO cannot make the correct decisions.

Extra credit: list three other reasons why an index might not be used.

(10053 trace for second test of table T1 in the comments section: OR11_ora_4400_WhyFullTableScanTest - Edit: this is a PDF file, WordPress is not allowing the double extention trick nor Word formatted files for some reason.)





True or False – Optimizer Mode and Tuning Goals

13 05 2010

 May 13, 2010

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

Continuing the true or false blog article series with more questions that seem to have conflicting answers.  Today’s quiz is on the topic of the optimizer mode and tuning goals, inspired by a recent thread that I found in a discussion forum.  A Google search found a couple of related articles.

When answering the questions, answer True or False, followed by a justification for the answer that you provided.

The Articles:

 The Quiz:

1.  As of Oracle 10g R1, there are three methods for joining tables or other row sources: star transformation join, nested loops join, and hash join.

2.  SQL execution speed will be fastest when the first join between two row sources produces the smallest possible result set from the specified row sources.

3.  Oracle Database 10g’s SQL Profiles and SQL Access Advisor help to change execution plans for queries without adding hints.

4. Oracle Database 10g offers three optimizer modes, controlled by the OPTIMIZER_MODE initialization parameter: RULE, COST-BASED, and CHOOSE.

5.  An effective approach for query tuning involves enabling the RULE based optimizer either through a RULE hint, or by changing the session-level optimizer goal with the following SQL statement:

    ALTER SESSION SET OPTIMIZER GOAL = RULE;

6.  The default optimizer mode in Oracle 10g R1 is CHOOSE, which means that the RULE based optimizer will be used if none of the objects referenced in the SQL statement have statistics.

7.  A SELECT statement requiring 10 seconds and performing 10,000 DB_BLOCK_GETS is automatically less efficient that than a transformed version of the SELECT statement that performs 2,000 DB_BLOCK_GETS.

8.  The FIRST_ROWS optimizer mode is appropriate for OLTP type systems because it allows the small resultsets that are exepected by the end users to be returned as quickly as possible.

9.  The RULE based optimizer does not support any features introduced in Oracle since the mid 1990s.

10. Specifying a value for the OPTIMIZER_INDEX_CACHING parameter is recommended, because the value is critical for costing nested loops joins.  Its value should be set to indicate the percentage of an index that is likely to remain in Oracle’s buffer cache.

11. When the PARALLEL_AUTOMATIC_TUNING parameter is set to ON, the cost-based optimizer will artifically increase the calculated cost of index access paths, which increases the frequency of full table scans that may be executed in parallel.

12. With the RULE based optimizer, the driving table is the last table listed in the FROM clause of the SQL statement.  When the Cost-Based optimizer is used, the driving table is the first table listed in the FROM clause of the SQL statement.

13. The Cost-Based optimizer often performs unnecessary full table scans when three or more tables are listed in the FROM clause of a SQL statement.

14. Setting the OPTIMIZER_MODE parameter at the session level to FIRST_ROWS_10 instructs the query optimizer that the SQL statement which will be submitted next will return 10 rows.

I fear that this blog series might be falsely inflating the Google ranking of the Oracle documentation library due to the number of times the documentation library has been referenced - sorry Google users.  :-)





True or False – Hash Joins

12 05 2010

May 12, 2010

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

Continuing the true or false blog article series with more questions that seem to have conflicting answers.  Today’s quiz is on the topic of the hash joins, inspired by a recent thread that I found in a discussion forum.

When answering the questions, answer True or False, followed by a justification for the answer that you provided.

The Articles:

The Quiz:

1.  A hash join is ideal when joining a small table to a large table, and in such cases a hash join is typically faster than a nested loops join or a sort-merge join.

2.  When presented with very large tables in a production database, the Oracle query optimizer will attempt to avoid using hash joins unless the PGA_AGGREGATE_TARGET or HASH_AREA_SIZE (if PGA_AGGREGATE_TARGET is not used) is increased to a large value.

3.  When a PGA_AGGREGATE_TARGET is in use, one session cannot use all of the memory specified by PGA_AGGREGATE_TARGET nor all of the available memory in the server, whichever is the lower value.

4.  A hash join may use only 200MB of memory, or 5% of the PGA_AGGREGATE_TARGET, whichever is the lower value.

5.  The query optimizer will not select to use a hash join unless the hash join is able to complete in memory, as controlled by the PGA_AGGREGATE_TARGET and/or HASH_AREA_SIZE parameter values.

6.  A hash join is only faster than a nested loops join when the memory available for the hash join is at least 1.6 times the sum of bytes in the rows of the table to be hashed.

7.  A hash join’s performance is affected when the hash join overflows the memory established by the HASH_AREA_SIZE parameter, and the hash join spills to the TEMP tablespace.

8.  The degree of parallelism for a table (or individual SQL statement) must be adjusted when a full table scan is used to retrieve the rows during a hash join.  Hash joins typically require the parallel degree to be set to a value greater than 1 in order to improve performance over other join methods.

9.  The _PGA_MAX_SIZE parameter should be adjusted to improve performance when joining large tables with a hash join.

10. The Oracle query optimizer will commonly ignore USE_HASH hints.

11. The OPTIMIZER_INDEX_COST_ADJ parameter and OPTIMIZER_MAX_PERMUTATIONS parameters control the optimizer’s decision to use hash joins.

12. When the PGA_AGGREGATE_TARGET parameter is not used, the HASH_AREA_SIZE parameter should be set to 50% of the square root of the bytes in the smaller of the two row sources being joined, with a minimum value of 1MB.

13. The row source listed below the words HASH JOIN in an execution plan is the source for the hash table – the optimizer typically tries to select the smaller estimated row source of the two row sources as the source for the hash table. The second row source listed below the words HASH JOIN in an execution plan is fully scanned, probing the generated hash table in search of a match.

When attempting to answer the true or false questions, think about under what conditions the answer to the question will be true and under what conditions the answer to the question must be false.





True or False – NVL

11 05 2010

May 11, 2010

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

Continuing the true or false blog article series with more questions that seem to have conflicting answers, if the answers to those questions depend on the source of the information found through Internet searches.  Today’s quiz is on the topic of the NVL function, inspired by a recent thread that I found in a discussion forum.

When answering the questions, answer True or False, followed by a justification for the answer that you provided.

The Articles:

The Quiz:

1.  The most common use of the NVL function is to prevent division by zero errors in mathematical equations.

2.  The SQL statement:

SELECT NVL(CURRENT_STATUS, "Not disclosed") FROM T1;

   replaces NULL values found in the CURRENT_STATUS column of table T1 with the phrase: Not disclosed

3.  The NVL function replaces a NULL column value with any other value.

4.  The NVL function is the most efficient method for converting NULL values to a non-NULL value.

To help you with the questions, here is a test table:

CREATE TABLE T1(
  C1 NUMBER,
  C2 NUMBER,
  C3 NUMBER,
  C4 DATE,
  C5 DATE,
  C6 DATE,
  C7 VARCHAR2(20),
  C8 VARCHAR2(20),
  C9 VARCHAR2(20),
  PRIMARY KEY(C1));

INSERT /*+ APPEND */ INTO
  T1
SELECT
  ROWNUM,
  DECODE(MOD(ROWNUM,5),0,NULL,ROWNUM),
  DECODE(MOD(ROWNUM,5),0,NULL,ROWNUM),
  TRUNC(SYSDATE)+TRUNC(ROWNUM/100),
  DECODE(MOD(ROWNUM,5),0,NULL,TRUNC(SYSDATE)+TRUNC(ROWNUM/100)),
  DECODE(MOD(ROWNUM,5),0,NULL,TRUNC(SYSDATE)+TRUNC(ROWNUM/100)),
  RPAD(CHR(MOD(ROWNUM-1,26)+65)||
    CHR(MOD(ROWNUM,26)+65)||
    CHR(MOD(ROWNUM+1,26)+65)||
    CHR(MOD(ROWNUM+2,26)+65),20,'A'),
  DECODE(MOD(ROWNUM,5),0,NULL,
   RPAD(CHR(MOD(ROWNUM-1,26)+65)||
    CHR(MOD(ROWNUM,26)+65)||
    CHR(MOD(ROWNUM+1,26)+65)||
    CHR(MOD(ROWNUM+2,26)+65),20,'A')),
  DECODE(MOD(ROWNUM,5),0,NULL,
   RPAD(CHR(MOD(ROWNUM-1,26)+65)||
    CHR(MOD(ROWNUM,26)+65)||
    CHR(MOD(ROWNUM+1,26)+65)||
    CHR(MOD(ROWNUM+2,26)+65),20,'A'))
FROM
  DUAL
CONNECT BY
  LEVEL<=1000000;

COMMIT;

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE)

The above creates a table with 1,000,000 rows, where one of every 5 rows contains NULL values in columns C2, C3, C5, C6, C8, and C9.

For some of the quiz questions, the following script might be helpful:

SET ARRAYSIZE 1000
SET AUTOTRACE TRACEONLY STATISTICS
SET TIMING ON

ALTER SESSION SET TRACEFILE_IDENTIFIER = 'NVL_TEST';
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 8';

SELECT /*+ GATHER_PLAN_STATISTICS */
  COALESCE(C3,C2,C1) NUM,
  COALESCE(C6,C5,C4) DAT,
  COALESCE(C9,C8,C7) VCAR
FROM
  T1;

SELECT /*+ GATHER_PLAN_STATISTICS */
  NVL(C3,NVL(C2,C1)) NUM,
  NVL(C6,NVL(C5,C4)) DAT,
  NVL(C9,NVL(C8,C7)) VCAR
FROM
  T1;

SELECT /*+ GATHER_PLAN_STATISTICS */
  NVL(C3,C1) NUM,
  NVL(C6,C4) DAT,
  NVL(C9,C7) VCAR
FROM
  T1;

SELECT /*+ GATHER_PLAN_STATISTICS */
  DECODE(C3,NULL,DECODE(C2,NULL,C1,C2),C3) NUM,
  DECODE(C6,NULL,DECODE(C5,NULL,C4,C5),C6) DAT,
  DECODE(C9,NULL,DECODE(C8,NULL,C7,C8),C9) VCAR
FROM
  T1;

SELECT /*+ GATHER_PLAN_STATISTICS */
  NVL(C3,NVL(C2,C4)) COL1,
  NVL(C6,NVL(C5,C7)) COL2
FROM
  T1;

SELECT
  SYSDATE
FROM
  DUAL;

ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';

It is probably best to execute the above script several times to limit the effects of physical block reads.  You may see different performance results whether or not the 10046 trace is enabled, whether or not physical block reads are performed on every execution, and whether or not the test is executed directly on the database server.  The GATHER_PLAN_STATISTICS hint is used to permit DBMS_XPLAN.DISPLAY_CURSOR to retrieve the execution plan with the ALLSTATS LAST format parameter.





True or False – Undo

9 05 2010

May 9, 2010

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

Continuing the true or false blog article series with more questions that seem to have conflicting answers, if the answers to those questions depend on the source of the information found through Internet searches.  Today’s quiz is on the topic of Oracle Database’s undo functionality, and in some cases you may need to consult several of the suggested articles in order to correctly answer each of the questions.

When answering the questions, answer True or False, followed by a justification for the answer that you provided.  Yes, there are a lot of questions, but part of the entertainment in these types of exercises is reading an article and identifying what is logically right or wrong.  Of course, as with the previous quizzes, any answers that attempt to utilize logical fallacies will be scored as incorrect.

The Articles:

The Quiz:

1.  Rollback segment shrinks cause the next session to use the rollback segment to wait while the rollback segment is reduced in size to its optimal size.

2.  An OPTIMAL size for rollback segments should never be set because when the rollback segments are correctly sized, they should never increase in size.

3.  The ideal rollback segment size is partially determined by the values of the DB_WRITER_PROCESSES, LOG_BUFFER, AND LOG_CHECKPOINT_INTERVAL initialization parameters, as well as the COMMIT frequency.

4.  Large, long running batch jobs should be manually set to use a single, large rollback segment to reduce the risk of ORA-01555 (snapshot too old) errors.

5.  Adding a large undo segment when manual undo management is in use will effectively reduce the chances of sessions receiving ORA-01555 (snapshot too old) errors.

6.  When automatic undo management is used, ORA-01555 (snapshot too old) errors are triggered by Oracle Database creating many small undo segments in an undo tablespace that is too small.

7.  In a database with a single session connected, it is not possible to trigger an ORA-01555 (snapshot too old) error when the session is not modifying the tables that are being queried.

8.  When manual undo management is in use, only UPDATE transactions are able to automatically cause an undo segment to grow; SELECT statements do not cause the undo segments to grow, thus increasing the risk of ORA-01555 (snapshot too old) errors when the undo segments are small.

9.  When manual undo management is in use, it is a common practice for DBAs to assign a transaction to a specific rollback segment using the SET TRANSACTION command, but this is unnecessary with automatic undo management.

10. The automatically created undo segments in the SYSTEM tablespace will only be used when modifying objects residing in the SYSTEM tablespace.

 11. The UNDO_TABLESPACE parameter must be set to an undo tablespace’s name when automatic undo management is used.

12. The V$WAITSTAT view includes a block class that indicates the number of waits on the system undo header blocks.

13. The SESSIONS parameter should be set to a value no larger than the expected maximum number of user sessions since the SESSIONS parameter affects the number of undo segments created when automatic undo management is used.

14. When automatic undo management is used, as the number of sessions connected to the database instance increases, the instance dynamically adds additional (offline) undo segments in response to the sessions merely connecting.  The TRANSACTIONS_PER_ROLLBACK_SEGMENT parameter determines the number of additional sessions that must be connected before an additional undo segment is added.

15. SELECT type SQL statements do not generate redo nor undo.

16. In most databases, DML or DDL is performed in only 10% of all transactions in the database.

17. After a session changes data and issues a COMMIT, all sessions with access permissions to view the data are able to immediately see those changes.

18. Read consistency in Oracle means that data included in the query results will always be as of the moment the query was submitted.

19. Undo segments may be used to support flashback type operations.

20. The redo logs contain undo records, which allows Oracle Database to optimize the process of recovering from a shutdown abort, and also means that a future release of Oracle Database will no longer need dedicated undo tablespaces.

Note that a new blog article category has been added to quickly locate the quiz articles.  When attempting to answer the true or false questions, think about under what conditions the answer to the question will be true and under what conditions the answer to the question must be false.





True or False – Data Dump Import and Indexes

6 05 2010

May 6, 2010

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

While reading one of the OTN threads that asked whether Data Pump import will automatically rebuild indexes, I notice that someone posted an interesting response that appeared to be quoted from another source.  I searched the Internet and found the text that was copied into the OTN post, directly below a heading that read “Maximizing Oracle Import (impdp) Performance” – it was then that I decided it was time for another true or false quiz.

The reading material:

  1. http://www.dba-oracle.com/oracle_tips_load_speed.htm
  2. http://download.oracle.com/docs/cd/E14072_01/server.112/e10701/dp_import.htm
  3. http://download.oracle.com/docs/cd/E14072_01/server.112/e10701/dp_export.htm

Read through the above material and attempt to answer the following questions – as in the past, state why the answer is true or false.  Since the first article was last revised in March 2006, I probably should have referenced the documentation for Oracle Database 10g R2, but the Oracle Database 11g R2 documentation should be sufficient.

1.  The options used when data is exported with Data Pump Export (expdp) have no control over how the data may be imported with Data Pump Import (impdp).

2.  Data Pump Export supports enabling and disabling direct path for exports.

3.  Set the ANALYZE parameter of impdp to N to prevent statistics from being gathered on imported tables and indexes.

4.  Using solid state disks (SSD) will speed up import rates when the import job is otherwise fully tuned.

5.  Setting the COMMIT parameter of impdp to N suppresses the committing of the imported data until the load process ends.

6.  Setting the INDEXES parameter of impdp to N prevents indexes from being created until the import process completes.

7.  Setting the INDEXES parameter of impdp to N eliminates the index maintenance overhead when data is imported into a pre-existing table having pre-existing indexes.

8.  When the creation of indexes is postponed through the use of the INDEXES parameter of impdp, the resulting text file should be edited to set the parallel degree of each index to one less than the value of the CPU_COUNT initialization parameter’s value.

9.  Using the EXCLUDE=STATISTICS parameter value of impdp prevents the automatic of gathering of object statistics by impdp.  Setting this parameter value will greatly improve impdp performance.

10. When the _disable_logging initialization parameter is set to TRUE, either a SHUTDOWN ABORT or instance crash will disable/corrupt the database.  This corruption may be prevented by creating a backup of the database before modifying the value of that hidden initialization parameter.

Edit – May 6, 2010: The original title of this blog article was intended to be “True or False – Data Pump Import and Indexes” – a bit of unintentional humor was caused by the typo in the article title.  There are several definitions of the word “Dump“, some of which are more fitting than others.

SchedulerAutomation.zip








Follow

Get every new post delivered to your Inbox.

Join 137 other followers