dba_data_files: Number of database blocks file_name data file name and path file_id data file identification tablespace_name data corresponding to the file name of the tablespace bytes data file size bocks data file size corresponding relative_fno Tablespace-relative file number = file_idautoextensible database file can be automatically extended maxbytes maxblocks the The number of bytes that can be extended by files, and the number of data blocks. User_bytesuser_blocks represents the size of the data file that the user can use, by equal to the space used by the DataFile Size-LMT / DMT. # About Automatic Extended Properties ALTER TABLESPACE ... DATAFILE '...' 'SIZE ... AUTOEXTENT ON NEXT ... MAXSIZE ... # LML (local management tablespace) ALTER TABLESPACE ... DATAFILE' ... 'size. ..extent management local autoallocate / uniform size ...; # DML (dictionary management tablespace) create tablespace ... datafile '...' size ... extent management dictionaryblocksize ... default storage (initial next minextents maxextents pctincrease) v $ datafile: describes the synchronization information of the data file. DBA_FREE_SPACE: BYTES, Blocks Indicates that the tablespace free space size and block number V $ filestat: phyrds, phyWRTS represents a physical disk read, written database role: http://blog.9cbs.net/lunar2000/archive/2004/08 / 02/58382.aspx# focus: If it is a full table scan, the size of the interval may result in performance issues. Oracle will read multiple blocks at a time when the full menu is scanned. The number of pieces read each time will be subject to the initialization parameter db_file_multiblock_read_count and the I / O buffer size of the operating system. For example, if the size of the Oracle Block is 4KB, the operating system I / O buffer size is 64KB, then you can read up to 16 each block at a full table scan, so the value of DB_FILE_MULTIBLOCK_READ_COUNT is set. It has changed the performance of the full table scan for more than 16. Query V $ FileStat to verify the initial statistics of the test:
SQL> SELECT PHYRDS, PHYBLKRD FROM V $ fileStat
2 WHERE FILE # = 15;
Phyrds phyblkrd
---------- ----------
0 0
Perform full mete scan on the table:
SQL> SELECT Count (*) from lunar;
Count (*)
------------
27547
Query V $ fileStat to determine the termination statistics of the test:
SQL> SELECT PHYRDS, PHYBLKRD FROM V $ fileStat
2 WHERE FILE # = 15;
Phyrds phyblkrd
---------- ----------
24 376
SQL>
The value of the initial statistics of Phyrds and Phyblkrd is 0;
The values of the termination statistics of Phyrds and Phyblkrd are 24 and 376, respectively;
PHYRDS termination statistics of the value - the value of the initial statistics of Phyrds = 24;
PHYBLKRD termination statistics - the value of the initial statistics of phyblkrd = 376;
PHYBLKRD's difference / PHYRDS difference = 15.67
Therefore, effective multi-piece read count is 16
It should be noted that if it is not tested with new tablespace, then test the difference in step 3 and 5, change the DB_FILE_MULTIBLOCK_READ_COUNT parameter in the session stage, and then get the difference in step 3 and 5, And repeat the test.
Remember, do not set the value of the DB_FILE_MULTIBLOCK_READ_COUNT parameter to the value.
Setting the interval size Size Consideration should be reasonable to use Oracle's capabilities to perform multi-block access when scanning in full menu, while reading operations cannot be across intervals. For example, it is assumed that the operating system I / O buffer size is 64KB, checks to read a 640KB size table. If set to 64KB per interval, a total of 10 intervals, if the full table scan is performed, Oracle needs 10 times. Operation (equivalent to reading a range); if set to a 640KB interval, Oracle still requires 10 readings (because the operating system I / O buffer size is 64KB), the visible compression interval does not improve performance; if set For each interval 80kb, a total of 8 intervals, each interval Oracle needs to read twice, first read 64KB, the second reading of the remaining 16KB (read operation cannot be cross-section), so a total of 16 times Operation (equivalent to reading a range). The impact of interval size settings to performance is obvious. · Creating a range that is significantly greater than or equal to the operating system I / O buffer size (preferably an integer multiple of the operating system I / O buffer size). In this way, if the interval is very large, even if the interval size is not an integer number of operating system I / O buffers, there is only a small additional read operation (if the above 640KB and 80KB differences) are required.
• Set DB_FILE_MULTIBLOCK_READ_COUNT to take full advantage of the operating system I / O buffer. The size of the DB_FILE_MULTIBLOCK_READ_COUNT <= Operation System I / O buffer / Oracle Block should be considered. If DB_FILE_MULTIBLOCK_READ_COUNT is set too large, the optimizer considers full table scan more efficient and changing the execution plan, and then the actual situation is not.
· If you must create a small range, create its size is an integer_extents: user_segments: user_segments: user_segments: User_SEGments:
