测试了非工作量模式下Oracle11gR2全表扫描的成本计算,现在测试一下在工作量模式下Oracle11gR2全表扫描的成本计算首先讲表blocks
测试了非工作量模式下Oracle11gR2全表扫描的成本计算,现在测试一下在工作量模式下Oracle11gR2全表扫描的成本计算
首先讲表blocks增加到10003个
SQL> select owner,blocks from dba_tables where table_name='TEST' and owner='TEST';
OWNER BLOCKS
------------------------------ ----------
TEST 10003
然后人工设置工作量的CPUSPEED=2500,单块读等于5,,多块读等于30,MBRC等于12
SQL> begin
dbms_stats.set_system_stats('CPUSPEED',2500);
dbms_stats.set_system_stats('SREADTIM',5);
dbms_stats.set_system_stats('MREADTIM',30);
dbms_stats.set_system_stats('MBRC',12);
end;
/ 2 3 4 5 6 7
PL/SQL procedure successfully completed.
利用explain plan得到CPU_COST---这里等于 72735764
SQL> explain plan for select count(*) from test;
Explained.
SQL> select cpu_cost from plan_table;
CPU_COST
----------
72735764
成本计算公式如下:
Cost = (
#SRds * sreadtim +
#MRds * mreadtim +
CPUCycles / cpuspeed /1000
) / sreadtime
#SRds - number of single block reads
#MRds - number of multi block reads
#CPUCyles - number of CPU cycles
sreadtim - single block read time
mreadtim - multi block read time
cpuspeed - CPU cycles per second
Cost = (
#SRds * sreadtim + ---SRds=0
#MRds * mreadtim + ---MRds=BLOCKS/MBCR=10003/12, mreadtim=30
CPUCycles / cpuspeed / 1000 ---CPUCycles=PLAN_TABLE.CPU_COST,cpuspeed=2500
) / sreadtime
所以人工计算的成本等于:
SQL> select ceil(10003/12*30/5)+ceil(72735764/2500/5/1000)+1 from dual;
CEIL(10003/12*30/5)+CEIL(72735764/2500/5/1000)+1
------------------------------------------------
5009
SQL> set autot trace
SQL> select count(*) from test;
Execution Plan
----------------------------------------------------------
Plan hash value: 1950795681
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5009 (1)| 00:00:26 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TEST | 10000 | 5009 (1)| 00:00:26 |
-------------------------------------------------------------------
人工计算的cost正好等于Oracle计算的Cost 这里也说明Oracle11gR2 在工作量模式下,全表扫描的成本计算方法依然同Oracle9i,Oracle10g
工作量模式下,从全表扫描的成本可以看出,参数db_file_multiblock_read_count 的更改对全表扫描成本计算没有影响,有影响的是MBRC,举个例子:
SQL> show parameter db_file_multiblock_read_count
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 16
SQL> set autot trace
SQL> select count(*) from test;
Execution Plan
----------------------------------------------------------
Plan hash value: 1950795681
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5009 (1)| 00:00:26 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TEST | 10000 | 5009 (1)| 00:00:26 |
-------------------------------------------------------------------
SQL> alter session set db_file_multiblock_read_count=32;
Session altered.
SQL> select count(*) from test;
Execution Plan
----------------------------------------------------------
Plan hash value: 1950795681
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5009 (1)| 00:00:26 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TEST | 10000 | 5009 (1)| 00:00:26 |
-------------------------------------------------------------------
可以看到更改db_file_multiblock_read_count对于成本没有任何影响,因为工作量模式下的COST只跟MBRC有关。
相关链接

InnoDBBufferPool reduces disk I/O by caching data and indexing pages, improving database performance. Its working principle includes: 1. Data reading: Read data from BufferPool; 2. Data writing: After modifying the data, write to BufferPool and refresh it to disk regularly; 3. Cache management: Use the LRU algorithm to manage cache pages; 4. Reading mechanism: Load adjacent data pages in advance. By sizing the BufferPool and using multiple instances, database performance can be optimized.

Compared with other programming languages, MySQL is mainly used to store and manage data, while other languages such as Python, Java, and C are used for logical processing and application development. MySQL is known for its high performance, scalability and cross-platform support, suitable for data management needs, while other languages have advantages in their respective fields such as data analytics, enterprise applications, and system programming.

MySQL is worth learning because it is a powerful open source database management system suitable for data storage, management and analysis. 1) MySQL is a relational database that uses SQL to operate data and is suitable for structured data management. 2) The SQL language is the key to interacting with MySQL and supports CRUD operations. 3) The working principle of MySQL includes client/server architecture, storage engine and query optimizer. 4) Basic usage includes creating databases and tables, and advanced usage involves joining tables using JOIN. 5) Common errors include syntax errors and permission issues, and debugging skills include checking syntax and using EXPLAIN commands. 6) Performance optimization involves the use of indexes, optimization of SQL statements and regular maintenance of databases.

MySQL is suitable for beginners to learn database skills. 1. Install MySQL server and client tools. 2. Understand basic SQL queries, such as SELECT. 3. Master data operations: create tables, insert, update, and delete data. 4. Learn advanced skills: subquery and window functions. 5. Debugging and optimization: Check syntax, use indexes, avoid SELECT*, and use LIMIT.

MySQL efficiently manages structured data through table structure and SQL query, and implements inter-table relationships through foreign keys. 1. Define the data format and type when creating a table. 2. Use foreign keys to establish relationships between tables. 3. Improve performance through indexing and query optimization. 4. Regularly backup and monitor databases to ensure data security and performance optimization.

MySQL is an open source relational database management system that is widely used in Web development. Its key features include: 1. Supports multiple storage engines, such as InnoDB and MyISAM, suitable for different scenarios; 2. Provides master-slave replication functions to facilitate load balancing and data backup; 3. Improve query efficiency through query optimization and index use.

SQL is used to interact with MySQL database to realize data addition, deletion, modification, inspection and database design. 1) SQL performs data operations through SELECT, INSERT, UPDATE, DELETE statements; 2) Use CREATE, ALTER, DROP statements for database design and management; 3) Complex queries and data analysis are implemented through SQL to improve business decision-making efficiency.

The basic operations of MySQL include creating databases, tables, and using SQL to perform CRUD operations on data. 1. Create a database: CREATEDATABASEmy_first_db; 2. Create a table: CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY, titleVARCHAR(100)NOTNULL, authorVARCHAR(100)NOTNULL, published_yearINT); 3. Insert data: INSERTINTObooks(title, author, published_year)VA


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

SublimeText3 English version
Recommended: Win version, supports code prompts!

Safe Exam Browser
Safe Exam Browser is a secure browser environment for taking online exams securely. This software turns any computer into a secure workstation. It controls access to any utility and prevents students from using unauthorized resources.

Dreamweaver Mac version
Visual web development tools

EditPlus Chinese cracked version
Small size, syntax highlighting, does not support code prompt function

SublimeText3 Mac version
God-level code editing software (SublimeText3)