ÈçºÎ¿´¶®ORACLEÖ´Ðмƻ®
ÈçºÎ¿´¶®ORACLEÖ´Ðмƻ®
Ò»¡¢Ê²Ã´ÊÇÖ´Ðмƻ®
An explain plan is a representation of the access path that is taken when a query is executed within Oracle.
¶þ¡¢ÈçºÎ·ÃÎÊÊý¾Ý
At the physical level Oracle reads blocks of data. The smallest amount of data read is a single Oracle block, the largest is constrained by operating system limits (and multiblock i/o). Logically Oracle finds the data to read by using the following methods:
Full Table Scan (FTS) --È«±íɨÃè
Index Lookup (unique & non-unique) --Ë÷ÒýɨÃ裨ΨһºÍ·ÇΨһ£©
Rowid --ÎïÀíÐÐid
Èý¡¢Ö´Ðмƻ®²ã´Î¹ØÏµ
When looking at a plan, the rightmost (ie most inndented) uppermost operation is the first thing that is executed. --²ÉÓÃ×îÓÒ×îÉÏ×îÏÈÖ´ÐеÄÔÔò¿´²ã´Î¹ØÏµ£¬ÔÚͬһ¼¶Èç¹ûij¸ö¶¯×÷ûÓÐ×ÓID¾Í×îÏÈÖ´ÐÐ
1.Ò»¸ö¼òµ¥µÄÀý×Ó£º
SQL> select /*+parallel (e 4)*/ * from emp e;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=82 Bytes=7134)
1 0 TABLE ACCESS* (FULL) OF 'EMP' (Cost=1 Card=82 Bytes=7134):Q5000
--[:Q5000]±íʾÊDz¢Ðз½Ê½
1 PARALLEL_TO_SERIAL SELECT /*+ NO_EXPAND ROWID(A1) */ A1."EMPNO"
,A1."ENAME",A1."JOB",A1."MGR",A1."HI
ÓÅ»¯Ä£Ê½ÊÇCHOOSEµÄÇé¿öÏ£¬¿´Cost²ÎÊýÊÇ·ñÓÐÖµÀ´¾ö¶¨²ÉÓÃCBO»¹ÊÇRBO£º
SELECT STATEMENT [CHOOSE] Cost=1234 --CostÓÐÖµ£¬²ÉÓÃCBO
SELECT STATEMENT [CHOOSE] --CostΪ¿Õ£¬²ÉÓÃRBO(9IÊÇÈç´ËÏÔʾµÄ)
2.²ã´ÎµÄ¸¸×Ó¹ØÏµµÄÀý×Ó£º
PARENT1
**FIRST CHILD
****FIRST GRANDCHILD
**SECOND CHILD
Here the same principles apply
Ïà¹ØÎĵµ£º
1¡¢²é¿´ORACLE×î´óÓαêÊý
C:\Documents and Settings\Administrator>sqlplus "sys/admin@test151 as sysdba" £¨sysÒÔdbaµÇ¼test151·þÎñ£©
SQL*Plus: Release 9.2.0.1.0 - Production on ÐÇÆÚËÄ 11ÔÂ 5 09:08:04 2009
Copyright (c) 1982, 2002, Oracle Corporation. All rights ......
1.ORACLE²ÉÓÃ×Ô϶øÉϵÄ˳Ðò½âÎöWHERE×Ó¾ä,¸ù¾ÝÕâ¸öÔÀí,±íÖ®¼äµÄÁ¬½Ó±ØÐëдÔÚÆäËûWHEREÌõ¼þ֮ǰ, ÄÇЩ¿ÉÒÔ¹ýÂ˵ô×î´óÊýÁ¿¼Ç¼µÄÌõ¼þ±ØÐëдÔÚWHERE×Ó¾äµÄĩβ.
¡¡¡¡ÀýÈç:
¡¡¡¡(µÍЧ)
¡¡¡¡SELECT … from EMP E WHERE SAL > 50000 AND JOB = ‘MANAGER’ AND 25 < (SELECT COUNT(*) from EMP WH ......
´¦Àí·½·¨Ò» £º
¼ì²éÄǸö±í±»Ëø
select sess.sid,sess.serial#, lo.oracle_username,lo.os_user_name,ao.object_name,lo.locked_mode
from v$locked_object lo,dba_objects ao,v$session sess
where ao.object_id = lo.object_id
and lo.session_id = sess.sid;
½âËø
alter system kill session '273,45';
´¦Àí·½·¨¶ ......
¼ì²âËø£º
SELECT A.OWNER,
A.OBJECT_NAME,
B.XIDUSN,
B.XIDSLOT,
B.XIDSQN,
B.SESSION_ID,
B.ORACLE_USERNAME,
B.OS_USER_NAME,
B.PROCESS,
B.LOCKED_MODE,
C.MACHINE,
C.STATUS,
C.SERVER,
C.SID,
C.SERIAL#,
C.PROGRAM
from ALL_OBJECTS A,
V$LOCKED_OBJECT B,
SYS.GV_$SESSION C
WHERE ( A.OBJE ......