How Does Oracle perform a full table scan experiment?

Source: Internet
Author: User
From the following experiment, we can see that the full table scan is performed to retrieve data according to the extentblock sequence.

From the following experiment, we can see that the full table scan is performed to retrieve data according to the extent/block sequence.

From the following experiment, we can see that the full table scan is performed to retrieve data according to the extent/block sequence. The first experiment has the 'db file scattered read' waiting event ,, the second experiment has loaded the data into databuffer, so the experiment is not waiting.

SQL> create table test as select * from dba_objects;
SQL> select extent_id, file_id, block_id, blocks
2 from dba_extents
3 where segment_name = 'test'
4 order by 1;
EXTENT_ID FILE_ID BLOCK_ID BLOCKS
----------------------------------------
0 6 83849 8
1 6 83857 8
2 6 83865 8
3 6 83873 8
4 6 83881 8
5 6 83889 8
6 6 83897 8
7 6 83905 8
8 6 83913 8
9 6 83921 8
10 6 83929 8
11 6 83937 8
12 6 83945 8
13 6 100409 8
14 6 100417 8
15 6 117121 8
16 6 83977 128
17 6 84105 128
18 6 84233 128
19 6 84361 128
20 6 84489 128
SQL> show parameter db_file_multiblock_read_count

NAME TYPE VALUE
-----------------------------------------------------------------------------
Db_file_multiblock_read_count integer 16

SQL> alter session set events 'immediate trace name flush_cache level 1 ';
SQL> alter session set events '10046 trace name context forever, level 12 ';

First experiment:
SQL> select count (1) from test;
COUNT (1)
----------
51255

View the 10046 file in the first experiment:

Arsing in cursor #5 len = 27 dep = 0 uid = 61 oct = 3 lid = 61 tim = 3414138868 hv = 1143379599 ad = '302eb520'
Select count (1) from test
END OF STMT
PARSE #5: c = 46875, e = 236549, p = 330, cr = 59, cu = 0, mis = 1, r = 0, dep = 0, og = 1, tim = 3414138867
BINDS #5:
EXEC #5: c = 0, e = 45, p = 0, cr = 0, cu = 0, mis = 0, r = 0, dep = 0, og = 1, tim = 3414139031
WAIT #5: nam = 'SQL * Net message to client' ela = 2 driver id = 1413697536 # bytes = 1 p3 = 0 obj # = 95737 tim = 3414139067
WAIT #5: nam = 'SQL * Net message from client' ela = 211 driver id = 1413697536 # bytes = 1 p3 = 0 obj # = 95737 tim = 3414139327
WAIT #5: nam = 'db file scattered read 'ela = 4594 file # = 6 block # = 83852 blocks = 5 obj # = 95737 tim = 3414143992
WAIT #5: nam = 'db file scattered read 'ela = 424 file # = 6 block # = 83857 blocks = 4 obj # = 95737 tim = 3414144545
WAIT #5: nam = 'db file scattered read 'ela = 161 file # = 6 block # = 83862 blocks = 3 obj # = 95737 tim = 3414144820
WAIT #5: nam = 'db file scattered read 'ela = 288 file # = 6 block # = 83866 blocks = 7 obj # = 95737 tim = 3414145211
WAIT #5: nam = 'db file scattered read 'ela = 314 file # = 6 block # = 83873 blocks = 8 obj # = 95737 tim = 3414145670
WAIT #5: nam = 'db file scattered read 'ela = 404 file # = 6 block # = 83882 blocks = 7 obj # = 95737 tim = 3414146232
WAIT #5: nam = 'db file scattered read 'ela = 156 file # = 6 block # = 83889 blocks = 3 obj # = 95737 tim = 3414146536
WAIT #5: nam = 'db file scattered read 'ela = 195 file # = 6 block # = 83893 blocks = 4 obj # = 95737 tim = 3414146840
WAIT #5: nam = 'db file scattered read 'ela = 280 file # = 6 block # = 83898 blocks = 7 obj # = 95737 tim = 3414147226
WAIT #5: nam = 'db file scattered read 'ela = 1438 file # = 6 block # = 83905 blocks = 8 obj # = 95737 tim = 3414148811
WAIT #5: nam = 'db file scattered read 'ela = 340 file # = 6 block # = 83914 blocks = 7 obj # = 95737 tim = 3414149308
WAIT #5: nam = 'db file scattered read 'ela = 365 file # = 6 block # = 83921 blocks = 8 obj # = 95737 tim = 3414149813
WAIT #5: nam = 'db file scattered read 'ela = 285 file # = 6 block # = 83930 blocks = 6 obj # = 95737 tim = 3414150250
WAIT #5: nam = 'db file scattered read 'ela = 128 file # = 6 block # = 83937 blocks = 2 obj # = 95737 tim = 3414150515
WAIT #5: nam = 'db file scattered read 'ela = 219 file # = 6 block # = 83940 blocks = 5 obj # = 95737 tim = 3414150826
WAIT #5: nam = 'db file scattered read 'ela = 343 file # = 6 block # = 83946 blocks = 7 obj # = 95737 tim = 3414151317
WAIT #5: nam = 'db file scattered read 'ela = 382 file # = 6 block # = 100409 blocks = 5 obj # = 95737 tim = 3414151838
WAIT #5: nam = 'db file scattered read 'ela = 187 file # = 6 block # = 100415 blocks = 2 obj # = 95737 tim = 3414152147
WAIT #5: nam = 'db file scattered read 'ela = 190 file # = 6 block # = 100418 blocks = 2 obj # = 95737 tim = 3414152418
WAIT #5: nam = 'db file scattered read 'ela = 249 file # = 6 block # = 100421 blocks = 4 obj # = 95737 tim = 3414152745

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.