读书笔记-《基于Oracle的SQL优化》-第二章-1 - 数据库编程

IN
1 14 6
OPERATION
------------------------------
OPTIONS
--------------------------------------------------------------------------------
OBJECT_NAME ID CARDINALITY COST
------------------------------ ---------- ----------- ----------

TABLE ACCESS
BY INDEX ROWID
DEPT 2 4 2

INDEX
FULL SCAN

OPERATION
------------------------------
OPTIONS
--------------------------------------------------------------------------------
OBJECT_NAME ID CARDINALITY COST
------------------------------ ---------- ----------- ----------
PK_DEPT 3 4 1
SORT
JOIN
4 14 4
TABLE ACCESS
OPERATION
------------------------------
OPTIONS
--------------------------------------------------------------------------------
OBJECT_NAME ID CARDINALITY COST
------------------------------ ---------- ----------- ----------
FULL
EMP 5 14 3
6 rows selected.
SQL> select count(*) from v$transaction where ses_addr='00000001EEC37778';
1

SQL> select count(*) from v$locked_object;
1
SQL> select object_id from v$locked_object; 5003

SQL> select owner, object_name from dba_objects where object_id=5003; SYS PLAN_TABLE$

Oracle 10g： SQL> select version from v$instance;
10.2.0.4.0

SQL> desc t;
Name Null Type
----------------------------------------- -------- ----------------------------
X NUMBER
SQL> select operation, options, object_name, id, cardinality, cost from sys.plan_table$;
SELECT STATEMENT
0 10013476 483
PX COORDINATOR
1
PX SEND
QC (RANDOM)
:TQ10000 2 10013476 483
PX BLOCK
ITERATOR
3 10013476 483
TABLE ACCESS
FULL
T 4 10013476 483

SQL> select count(*) from v$transaction where ses_addr='00000000A5A07A70';
1

SQL> select count(*) from v$locked_object;
0
SQL> select table_name, degree from user_tables; TABLE_NAME DEGREE
------------------------------ -------------------- T 8
alter table t parallel(degree 1);

SQL> select table_name, degree from user_tables; TABLE_NAME DEGREE
------------------------------ -------------------- T 1
SQL> select operation, options, object_name, id, cardinality, cost from sys.plan_table$;
SELECT STATEMENT
0 10013476 3483
TABLE ACCESS
FULL
T 1 10013476 3483
(2)、DBMS_XPLAN包 select * from table(dbms_xplan.display); select * from table(dbms_xplan.display_cursor(null, null, 'advanced')); advanced比all显示结果更详细一些。 select * from table(dbms_xplan.display_cursor('sql_id/hash_value', child_cursor_number, 'advanced')); select * from table(dbms_xplan.display_awr('sql_id')); (3)、SQLPLUS中的AUTOTRACE开关 SET AUTOTRACE ON(SET AUTOT ON)：显示执行结果，执行计划和资源消耗。 SET AUTOTRACE OFF(SET AUTOT OFF)：只显示执行结果。 SET AUTOTRACE TRACEONLY：不显示执行结果，与ON区别只显示执行结果的数量，不显示执行结果的具体内容，适合于执行结果的具体内容特别长，刷屏的SQL，这时只关心执行计划和资源消耗量。 SET AUTOTRACE TRACEONLY EXPLAIN(SET AUTOT TRACE EXP)：与TRACEONLY区别不显示资源消耗量和执行计划，只显示执行计划。 SET AUTOTTRACE TRACEONLY STATISTICS(SET AUTOT TRACE STAT)：只显示资源消耗量，与TRACEONLY区别不显示执行计划，只显示执行结果的数量和资源消耗量。 (4)、10046事件和explain plan、dbms_xplan和autotrace开关不同之处：所得到的的执行计划中明确显示了目标SQL实际执行计划中每一个执行步骤所消耗的逻辑读、物理读和花费的时间。 USER_DUMP_DEST生成trace文件。激活10046事件： alter session set events '10046 trace name context forever, level 12' oradebug event 10046 trace name context forever, level 12，推荐这种方法，因为可以在激活10046事件后执行命令oradebug tracefile_name来得到当前session所对应的的trace文件的具体路径和名称。通常值是12，表示产生的trace文件中除了有目标SQL的执行计划和资源消耗明细之外，还会包含目标SQL所使用的绑定变量的值及该session所经历的的等

读书笔记-《基于Oracle的SQL优化》-第二章-1(二)