SQL> show parameter memory_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_target big integer 300M
SQL> select * from v$memory_target_advice;
MEMORY_SIZE MEMORY_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR VERSION
----------- ------------------ ------------ ------------------- ----------
300 1 48 1 0
375 1.25 48 1 0
450 1.5 47 .9691 0
525 1.75 47 .9691 0
600 2 47 .9691 0
SQL>
由于是单机数据库,没有什么负载,可以看出memory_target设为当前大小时(300M)已经是最佳大小了.
--EOF--
2.通过调整参数optimizer_index_cost_adj的大小，演示SQL产生不同执行计划。
SQL> create table t as select * from dba_objects where rownum < 100;
Table created.
SQL> create index idx_t_objid on t(object_id);
Index created.
SQL> set linesize 200
SQL> show parameter optimizer_index_cost_adj
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_index_cost_adj integer 100
SQL> set autotrace traceonly
SQL> select * from t where object_id < 100;
98 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 98 | 20286 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 98 | 20286 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"<100)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
292 recursive calls
0 db block gets
46 consistent gets
0 physical reads
0 redo size
5504 bytes sent via SQL*Net to client
589 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
98 rows processed
SQL> set autotrace off
SQL> alter session set optimizer_index_cost_adj=10;
Session altered.
SQL> show parameter optimizer_index_cost_adj
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_index_cost_adj integer 10
SQL> set autotrace traceonly
SQL> select * from t where object_id < 100;
98 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3638283050
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 98 | 20286 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 98 | 20286 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T_OBJID | 98 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"<100)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
9 recursive calls
0 db block gets
24 consistent gets
0 physical reads
0 redo size
10688 bytes sent via SQL*Net to client
589 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
98 rows processed
SQL>
可以看到optimizer_index_cost_adj值越小越倾向使用索引.
--EOF--
3.通过设置参数DB_FILE_MULTIBLOCK_READ_COUNT 不同的值，演示对SQL效率的影响（sql_trace or 10046 的输出结果）
SQL> drop table t purge;
Table dropped.
SQL> create table t as select * from dba_objects;
Table created.
SQL> insert into t select * from t;
71934 rows created.
SQL> insert into t select * from t;
143868 rows created.
SQL> insert into t select * from t;
287736 rows created.
SQL> commit;
Commit complete.
SQL> exec dbms_stats.gather_table_stats(user,'T');
PL/SQL procedure successfully completed.
SQL> select count(*) from t;
COUNT(*)
----------
575472
SQL> show parameter db_file_multiblock_read_count
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 19
SQL> alter system flush buffer_cache;
System altered.
SQL> alter session set events '10046 trace name context forever,level 12';
Session altered.
SQL> select count(*) from t;
COUNT(*)
----------
575472
SQL> alter session set db_file_multiblock_read_count=128;
Session altered.
SQL> alter system flush buffer_cache;
System altered.
SQL> select count(*) from t;
COUNT(*)
----------
575472
SQL> alter session set events '10046 trace name context off';
Session altered.
SQL> SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Default Trace File';
VALUE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_29994.trc
SQL> exit
[oracle@ora11g ~]$ tkprof /u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_29994.trc /u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_29994.trc.prf sys=no aggregate=no
TKPROF: Release 11.2.0.1.0 - Development on Sat Dec 28 19:52:56 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
[oracle@ora11g ~]$ view /u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_29994.trc.prf
...
********************************************************************************
SQL ID: cyzznbykb509s
Plan Hash: 2966233522
select count(*)
from
t
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.05 0.05 8336 8339 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.05 0.06 8336 8339 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=8339 pr=8336 pw=0 time=0 us)
575472 TABLE ACCESS FULL T (cr=8339 pr=8336 pw=0 time=84221 us cost=2472 size=0 card=575472)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
db file sequential read 3 0.00 0.00
direct path read 529 0.00 0.01
db file scattered read 1 0.00 0.00
SQL*Net message from client 2 5.22 5.22
********************************************************************************
...
********************************************************************************
SQL ID: cyzznbykb509s
Plan Hash: 2966233522
select count(*)
from
t
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.05 0.05 8336 8339 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.06 0.06 8336 8339 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=8339 pr=8336 pw=0 time=0 us)
575472 TABLE ACCESS FULL T (cr=8339 pr=8336 pw=0 time=96381 us cost=1583 size=0 card=575472)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 3 0.00 0.00
SQL*Net message from client 3 4.72 9.38
db file sequential read 3 0.00 0.00
direct path read 74 0.00 0.02
db file scattered read 1 0.00 0.00
********************************************************************************
...
可以看到当db_file_multiblock_read_count参数改大时,会有利于降低多块读操作的IO次数和总的cost.
--EOF--
4.示例说明数据库中“会话”和“进程”之间的关系。
session = process
[oracle@ora11g ~]$ sqlplus scott/tiger
SQL*Plus: Release 11.2.0.1.0 Production on Sat Dec 28 20:09:48 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select p.spid from v$session s, v$process p where s.paddr=p.addr and s.sid = (select sid from v$mystat where rownum=1);
SPID
------------------------
30073
SQL> !
oracle 30073 30070 0 20:09 ? 00:00:00 oracleora11g (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 30097 30077 0 20:09 pts/1 00:00:00 grep oracleora11g
[oracle@ora11g ~]$
session < process
[oracle@ora11g ~]$ sqlplus /nologSQL*Plus: Release 11.2.0.1.0 Production on Sat Dec 28 20:12:26 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL> select count(*) from v$session;
SP2-0640: Not connected
SQL>
session > process
[oracle@ora11g ~]$ sqlplus scott/tiger
SQL*Plus: Release 11.2.0.1.0 Production on Sat Dec 28 20:45:31 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> set autot on stat;
SQL> select username,paddr,sid,serial# from v$session where username is not null;
USERNAME PADDR SID SERIAL#
------------------------------ ---------------- ---------- ----------
SCOTT 0000000072091150 36 260
SCOTT 0000000072091150 40 75
Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
818 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
SQL> select spid from v$process where addr='0000000072091150';
SPID
------------------------
30245
Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
525 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> !
oracle 30245 30242 0 20:45 ? 00:00:00 oracleora11g (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 30275 30256 0 20:47 pts/1 00:00:00 grep oracleora11g
[oracle@ora11g ~]$
--EOF--
[oracle@ora11g ~]$ sqlplus scott/tiger
SQL*Plus: Release 11.2.0.1.0 Production on Sat Dec 28 20:45:31 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select sid,event from v$session_wait where sid = (select sid from v$mystat where rownum=1);
SID EVENT
---------- ----------------------------------------------------------------
36 SQL*Net message to client
SQL>
通过以上query得知当前session正在等待发送消息到客户端.
--EOF--
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
