How Many Topics can be Packed into a Short OTN Thread?

The thread starts off with a simple statement, without a lot of technical detail (kind of like this example):

“We are runing in 10g (10.2.0.3), when using full table scan, the perfromance is very slow. Is a bug in ASM or SQL program problem? How can I vertify the problem come from? I have runing health check in oracle but found nothing.”

So, where does the thread head? Topics?

“By definition the Full Table Scan access is the most feared enemy, you should avoid that monster when your tables are huge (many rows &/or long rows).”

“Not all full table scans are bad, not all indexes are good”

Enabling a 10046 trace file for a session might help.

“Usually you don’t want your applications to access a very large part of a large table. This will be very slow and could deteriorate the performance of your application severely.”

“We had the same issues with full table scans in 10.2.0.3 where we had gathered system stats”

“If I’m not mistaken, Oracle says that if you query 7.5% of the table rows and above you are usually better off with an FTS” A link was provided by someone else to a site that made a similar claim.

“I can very easily give you an example where an index would be the best option to query 99% of data. I can very easily give you an example where a FTS is the best option to query 1% of data.”

What is noise in a thread?

A full tablescan reads all of the blocks up to the high watermark – but does it always?

“The only way to improve the end-to-end performance of a full-table scan is Oracle parallel query (OPQ).” – or is it?

“That’s improved the tablescan by a factor of nearly 30 simply by changing the array fetch size”

Properly setting the DB_FILE_MULTIBLOCK_READ_COUNT will have an impact on the performance of a full table scan.

The value of a 10046 trace, a test case.

In this OTN thread I provided a nice litte test case that showed a 10046 trace where a full table scan operation did not read all of the table blocks up to the high watermark for the table. That test case appears below:

Did the TABLE ACCESS FULL (full table scan) operation in the plan indicate that Oracle read all blocks up to the high water mark (I intentially excluded the Access/Filter Predicates)? Oracle did NOT read all blocks up to the high water mark, regardless of what the plan shows. The proof is in the 10046 trace file:

From the trace file we see many interesting details, including the absence of the typical db file scattered reads commonly associated with full table scans. From the trace it is also possible to see that 100 rows were read at a time, with a fairly consistent delay between requests for each set of the 100 rows. What else might we see in the trace file that would help us identify the source of a performance problem?

Actions

Information

2 responses

21012010

Taral Desai(01:44:14) :

Hi Charles,

It’s very interesting one. Also, can you please elaborate if possible from Jonathan post saying

” through the index, you will hit the root block of the index one, and the table block once for a total of 2 LIOs. If you do a tablescan you will access the segment header block twice (9i onwards) and the table block once for a total of 3 LIO”

1. Table scan will access segment block twice . How can we arrive at this number any proof

2. So, does this means that ever block access has 3 LIO for table.

3. If i am understanding right then for Index range scan it will hit root + branch + blevel (total 3 LIO) for ever scan.(every value scan) any example

I was hoping that Jonathan would see your question and provide you with a very carefully constructed answer to your questions. The results could be different depending on whether you are using dictionary managed tablespaces, locally managed tablespaces with manual freelist management, or locally managed tablespaces with ASSM AUTO ALLOCATEd management.

Keep in mind that Jonathan is referring to a very specific case in the response that you quoted, a case where there is a table with a single row in a table with a single column that also has an index on that single column. In such a case, the index structure will contain a single block – the root block, so an index access to the one block would require reading the index root block, and then the table block pointed to by the index entry. I believe that Richard Foote’s blog contains several examples of how this would work for very small index structures.

So, how to set up a test case (note that I modified this to have a table with two columns)?

As you can see from the above, the full table scan required 7 logical IOs, and the index access required 2 logical IOs (1 for the index root block, and a second for the parent operation’s table access). Let’s take a look in the trace files:
From event10200test1 (full table scan – slightly trimmed for space):

So, during the full table scan, Oracle read blocks 4, 5, 6, 7, and 8 – for a total of 5 logical IO blocks reads. But, the DBMS_XPLAN output clearly showed that were 7 logical IO blocks read, so what about the other two logical IOs. I suppose that there is another event that needs to be set to see those logical IOs.

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:

<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: