dual是一个oracle表。具体用处不说了。这里聊聊有用的。
客户的一个系统，60分钟statspack采样，发现select count(*) from dual 在这段时间共执行6,775,149次，平均每秒执行1800次。而每次执行该语句逻辑读3个——巨大的消耗。
为何会有如此多的后台执行？因为webLogic为了测试一个连接是否安全有效，使用该语句进行连接测试。而有两个weblogic的参数与之相关：
BEA建议为JDBC池去掉Test Released Connections选项，而只保留Test Reserved Connections选项。此外，可以考虑激活Inactive Timeout设置，建议设置值为大于等于5分钟，也即300s
我去掉了Test Released Connections选项，一小时内，可以减少大约200万左右的语句执行，相信设置了timeout参数后，执行数会大大的降低。
另外转篇文章：
| All code examples are using Oracle9i.
The credit for the solution belongs to Anjo Kolk, Gaja Krishna Vaidyanatha (via Rachel Carmichael) and Tim Gorman X$ structures are table-ized references to memory structures and are not officially, nor well, documented. This solution must be tested and validated against each release of Oracle. What is DUAL? DUAL is a table that is created by the script $ORACLE_HOME/rdbms/admin/sql.bsq. It contains one column named DUMMY of datatype varchar2(1) and is populated with a single value ‘X’. According to the notes in the creation script, it is required for the PL/SQL STANDARD package. Interestingly, it is also noted that the optimizer knows that DUAL contains only 1 row. I’m not very confident in this assertion, but that is for another time. Actual call from sql.bsq create table dual /* pl/sql’s standard pckg requires dual. */ (dummy varchar2(1)) /* note, the optimizer knows sys.dual is single row */ storage (initial 1) / insert into dual values(’X') / create public synonym dual for dual / grant select on dual to public with grant option / How is it used in SQL and PL/SQL? A common usage is to perform scalar functions that do not require data from the database. ‘SELECT SYSDATE FROM DUAL’ will return the current date ‘SELECT 1 + 1 FROM DUAL’ to perform arithematic functions ‘SELECT MY_SEQUENCE.NEXTVAL FROM DUAL’ to retrieve sequence values Because DUAL contains only one row (at least in theory and in healthy databases), a single value will be returned. What are the performance implications of DUAL? There are two performance implications to using dual. The first is the I/O required to perform the operation. The second is the wait for system resources if the dual table is considered ‘hot’. Suprisingly, a simple ‘SELECT * FROM DUAL’ is not a low cost operation. Even when it returns a single row, it performs multiple I/O operations. This is caused by the Full Table Scan performed. Each time an FTS occurs, the segment header of the object must be read to determine the high water mark, even if the block containing the DUAL data is resident in memory. By itself, accessing DUAL is not expensive when compared to other statements performing hundreds, even thousands, of I/Os to retrieve actual data. However, the impact comes when the table is accessed thousands of times for basic operations. If DUAL is accessed thousands of times each day, it could easily become the table responsible to 5%, 10% or more of the total I/O of the system. Version Consistent Gets DB Block Gets 8.1.7 1 4 9.0.1 1 2 Using the AUTOTRACE facility, the explain plan for ‘SELECT * FROM DUAL’ is Execution Plan ———————————————————- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1) 1 0 TABLE ACCESS (FULL) OF ‘DUAL’ (Cost=1 Card=1) Cary Millsap’s Paper ‘Misunderstandings About Oracle Internals:When to Create an Index’ referenced a 10 fold increase in performance in 8i when an index was placed on dual. However, in my testing in 9i indicated that there was no change. When an index is placed on DUAL and the objects are analyzed, the execution plan remains the same. SQL> show user USER is “SYS” SQL> create index dual_ix 2 on dual (dummy) 3 tablespace users; Index created. SQL> analyze table dual compute statistics; Table analyzed. SQL> analyze index dual_ix compute statistics; Index analyzed. SQL> connect scott/tiger Connected. SQL> set autotrace traceonly SQL> select ‘X’ 2 from dual; Execution Plan ———————————————————- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1) 1 0 TABLE ACCESS (FULL) OF ‘DUAL’ (Cost=1 Card=1) Statistics ———————————————————- 0 recursive calls 2 db block gets 1 consistent gets 0 physical reads 0 redo size 377 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed What is the solution? Instead of selecting from DUAL, use the memory structure X$DUAL. However, you are not able to grant users direct access to the sys.x$ objects, so a view is created and a synonym placed on the view. In looking at X$DUAL, it includes the INST_ID column, which indicates the INSTANCE ID. This is why the ‘where inst_id = userenv(’INSTANCE’) predicate is added. SQL> desc x$dual Name Null? Type ——– ——– —— ADDR RAW(4) INDX NUMBER INST_ID NUMBER DUMMY VARCHAR2(1) SQL> select * from x$dual; ADDR INDX INST_ID D ——– ———- ———- - 016133C0 0 1 X SQL> create view v_dual as select dummy from sys.x$dual where inst_id = userenv(’INSTANCE’);
View created.
SQL> grant select on v_dual to public;
Grant succeeded. SQL> create public synonym vdual for v_dual;
Synonym created.
SQL> connect scott/tiger Connected. SQL> set autotrace traceonly; SQL> select * 2 from vdual; Execution Plan ———————————————————- ERROR: ORA-01039: insufficient privileges on underlying objects of the view SP2-0612: Error generating AUTOTRACE EXPLAIN report Statistics ———————————————————- 0 recursive calls 0 db block gets 0 consistent gets 0 physical reads 0 redo size 379 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed If each call is rewritten to use VDUAL view instead of the DUAL table, there would be a significant reduction in I/O operations. Of course, there is a tradeoff…how much time must be devoted to rewriting code? One option would be to replace the dual table with the view described above. The outstanding question is what impact would it have on Oracle processing? Looks like I have more research to do… |
我在自己的测试机上试验了，在dual上建立index逻辑读也降低为0，不清楚是真实还是假象。至少没有在生产数据库上测试。
如果不敢拿dual进行操作的话，上面的是一个方法。因为在weblogic的设置里面，可以选择使用什么表来进行连接的测试。
今天在网上闲逛，发现一篇老外的文章，跟我的内容差不多。
内容如下：
Dueling DUAL with BEA Weblogic and TestConnectionsOnReserve.
Monday Oct 30, 2006
You would think that the “DUAL” table, a simple stub table, would not be a performance topic - but I have seen this for years on high-end benchmarks. People develop applications or tests for applications which tend to over-use the DUAL table. Most commonly, this comes in the form of “select abc.nextseq from DUAL” and “select sysdate from DUAL”. This is typically, not a problem for small severs with a low level of concurrency, but it can be bottle-neck on high-end severs with lots of processors.
The problem with DUAL (in Oracle 9i and below) is that this “fake table” hashes to a “real” cache line 
If over-used it can cause a “cache buffers chains” latch contention like crazy. The most dangerous over-use situations are systemic ones. I can get around these issues in most benchmark environments, but cringe when I see the embedded use DUAL.
In BEA websphere, there is a parameter called “TestConnectionsOnReservere”. This parameter sends a SQL statement to the database before *EVERY* user statement…. talk about overhead! This not only adds SQL*Net round trips increasing network use, but most commonly uses the “SQL SELECT 1 from DUAL” as the test statement What is worse, the overhead just continues to increase as the load is increased. Ken Gottry discusses the performance impact in an article he wrote. This study used a 2-way server to show the performance impact. It is much worse on a high-end server.
What can you do?
Avoid setting the TestConnectionsOnReserve within BEA. The performance cost in terms of potential latch contention and network over-head is too high. If you must use this paramenter, use the “X$DUAL” table instead. Oracle 10g, uses this by default and while it avoids the latching issues, the networking component this parameter is still present.
