An explain plan is a representation of the access path that is taken when a query is executed within Oracle. Query processing can be divided into 7 phases:
Steps [1]-[6] are handled by the parser. Step [7] is the execution of the statement. The explain plan is produced by the parser. Once the access path has been decided upon it is stored in the library cache together with the statement itself. We store queries in the library cache based upon a hashed representation of that query. When looking for a statement in the library cache, we first apply a hashing algorithm to the statement and then we look for this hash value in the library cache. This access path will be used until the query is reparsed. Terminology
How does Oracle access data? 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:
Explain plan Hierarchy Simple explain plan: Query Plan ----------------------------------------- SELECT STATEMENT [CHOOSE] Cost=1234 TABLE ACCESS FULL LARGE [:Q65001] [ANALYZED] The rightmost uppermost operation of an explain plan is the first thing that the explain plan will execute. In this case TABLE ACCESS FULL LARGE is the first operation. This statement means we are doing a full table scan of table LARGE. When this operation completes then the resultant row source is passed up to the next level of the query for processing. In this case it is the SELECT STATEMENT which is the top of the query. [CHOOSE] is an indication of the optimizer_goal for the query. This DOES NOT necessarily indicate that plan has actually used this goal. The only way to confirm this is to check the cost= part of the explain plan as well. For example the following query indicates that the CBO has been used because there is a cost in the cost field: SELECT STATEMENT [CHOOSE] Cost=1234 However the explain plan below indicates the use of the RBO because the cost field is blank: SELECT STATEMENT [CHOOSE] Cost= The cost field is a comparative cost that is used internally to determine the best cost for particular plans. The costs of different statements are not really directly comparable. [:Q65001] indicates that this particular part of the query is being executed in parallel. This number indicates that the operation will be processed by a parallel query slave as opposed to being executed serially. [ANALYZED] indicates that the object in question has been analyzed and there are currently statistics available for the CBO to use. There is no indication of the 'level' of analysis done. Access Methods in detail Full Table Scan (FTS) In a FTS operation, the whole table is read up to the high water mark (HWM). The HWM marks the last block in the table that has ever had data written to it. If you have deleted all the rows then you will still read up to the HWM. Truncate resets the HWM back to the start of the table. FTS uses multiblock i/o to read the blocks from disk. Multiblock i/o is controlled by the parameter <PARAM:db_block_multi_block_read_count>. This defaults to: db_block_buffers / ( (PROCESSES+3) / 4 ) Maximum values are OS dependant Buffers from FTS operations are placed on the Least Recently Used (LRU) end of the buffer cache so will be quickly aged out. FTS is not recommended for large tables unless you are reading >5-10% of it (or so) or you intend to run in parallel. Example FTS explain plan: SQL> explain plan for select * from dual; Query Plan ----------------------------------------- SELECT STATEMENT [CHOOSE] Cost= TABLE ACCESS FULL DUAL Index lookup Data is accessed by looking up key values in an index and returning rowids. A rowid uniquely identifies an individual row in a particular data block. This block is read via single block i/o. In this example an index is used to find the relevant row(s) and then the table is accessed to lookup the ename column (which is not included in the index): SQL> explain plan for select empno,ename from emp where empno=10; Query Plan ------------------------------------ SELECT STATEMENT [CHOOSE] Cost=1 TABLE ACCESS BY ROWID EMP [ANALYZED] INDEX UNIQUE SCAN EMP_I1 Notice the 'TABLE ACCESS BY ROWID' section. This indicates that the table data is not being accessed via a FTS operation but rather by a rowid lookup. In this case the rowid has been produced by looking up values in the index first. The index is being accessed by an 'INDEX UNIQUE SCAN' operation. This is explained below. The index name in this case is EMP_I1. If all the required data resides in the index then a table lookup may be unnecessary and all you will see is an index access with no table access. In the following example all the columns (empno) are in the index. Notice that no table access takes place: SQL> explain plan for select empno from emp where empno=10; Query Plan ------------------------------------ SELECT STATEMENT [CHOOSE] Cost=1 INDEX UNIQUE SCAN EMP_I1 Indexes are presorted so sorting may be unecessary if the sort order required is the same as the index. SQL> explain plan for select empno,ename from emp where empno > 7876 order by empno; Query Plan ------------------------------------------------------------- SELECT STATEMENT [CHOOSE] Cost=1 TABLE ACCESS BY ROWID EMP [ANALYZED] INDEX RANGE SCAN EMP_I1 [ANALYZED] In this case the index is sorted so ther rows will be returned in the order of the index hence a sort is unecessary. SQL> explain plan for select /*+ Full(emp) */ empno,ename from emp where empno> 7876 order by empno; Query Plan ------------------------------------------------------------- SELECT STATEMENT [CHOOSE] Cost=9 SORT ORDER BY TABLE ACCESS FULL EMP [ANALYZED] Cost=1 Card=2 Bytes=66 Because we have forced a FTS the data is unsorted and so we must sort the data after it has been retrieved. There are 4 methods of index lookup:
Index unique scan Method for looking up a single key value via a unique index. Always returns a single value You must supply AT LEAST the leading column of the index to access data via the index, However this may return > 1 row as the uniqueness will not be guaranteed. SQL> explain plan for select empno,ename from emp where empno=10; Query Plan ------------------------------------ SELECT STATEMENT [CHOOSE] Cost=1 TABLE ACCESS BY ROWID EMP [ANALYZED] INDEX UNIQUE SCAN EMP_I1 Index range scan Method for accessing multiple column values You must supply AT LEAST the leading column of the index to access data via the index Can be used for range operations (e.g. > < <> >= <= between) SQL> explain plan for select empno,ename from emp where empno > 7876 order by empno; Query Plan ------------------------------------------------------- SELECT STATEMENT [CHOOSE] Cost=1 TABLE ACCESS BY ROWID EMP [ANALYZED] INDEX RANGE SCAN EMP_I1 [ANALYZED] A non-unique index may return multiple values for the predicate col1 = 5 and will use an index range scan SQL> explain plan for select mgr from emp where mgr = 5 Query plan -------------------- SELECT STATEMENT [CHOOSE] Cost=1 INDEX RANGE SCAN EMP_I2 [ANALYZED] Index Full Scan In certain circumstances it is possible for the whole index to be scanned as opposed to a range scan (i.e. where no constraining predicates are provided for a table). Full index scans are only available in the CBO as otherwise we are unable to determine whether a full scan would be a good idea or not. We choose an index Full Scan when we have statistics that indicate that it is going to be more efficient than a Full table scan and a sort. For example we may do a Full index scan when we do an unbounded scan of an index and want the data to be ordered in the index order. The optimizer may decide that selecting all the information from the index and not sorting is more efficient than doing a FTS or a Fast Full Index Scan and then sorting. An Index full scan will perform single block i/o's and so it may prove to be inefficient. Index BE_IX is a concatenated index on big_emp (empno,ename) SQL> explain plan for select empno,ename from big_emp order by empno,ename; Query Plan ------------------------------------------------------------ SELECT STATEMENT [CHOOSE] Cost=26 INDEX FULL SCAN BE_IX [ANALYZED] Index Fast Full Scan Scans all the block in the index Rows are not returned in sorted order Introduced in 7.3 and requires V733_PLANS_ENABLED=TRUE and CBO may be hinted using INDEX_FFS hint uses multiblock i/o can be executed in parallel can be used to access second column of concatenated indexes. This is because we are selecting all of the index. Note that INDEX FAST FULL SCAN is the mechinism behind fast index create and recreate. Index BE_IX is a concatenated index on big_emp (empno,ename) SQL> explain plan for select empno,ename from big_emp; Query Plan ------------------------------------------ SELECT STATEMENT [CHOOSE] Cost=1 INDEX FAST FULL SCAN BE_IX [ANALYZED] Selecting the 2nd column of concatenated index: SQL> explain plan for select ename from big_emp; Query Plan ------------------------------------------ SELECT STATEMENT [CHOOSE] Cost=1 INDEX FAST FULL SCAN BE_IX [ANALYZED] Rowid This is the quickest access method available Oracle simply retrieves the block specified and extracts the rows it is interested in. Most frequently seen in explain plans as Table access by Rowid SQL> explain plan for select * from dept where rowid = ':x'; Query Plan ------------------------------------ SELECT STATEMENT [CHOOSE] Cost=1 TABLE ACCESS BY ROWID DEPT [ANALYZED] Table is accessed by rowid following index lookup: SQL> explain plan for select empno,ename from emp where empno=10; Query Plan ------------------------------------ SELECT STATEMENT [CHOOSE] Cost=1 TABLE ACCESS BY ROWID EMP [ANALYZED] INDEX UNIQUE SCAN EMP_I1 Joins A Join is a predicate that attempts to combine 2 row sources We only ever join 2 row sources together Join steps are always performed serially even though underlying row sources may have been accessed in parallel. Join order - order in which joins are performed The join order makes a significant difference to the way in which the query is executed. By accessing particular row sources first, certain predicates may be satisfied that are not satisfied by with other join orders. This may prevent certain access paths from being taken. Suppose there is a concatenated index on A(a.col1,a.col2). Note that a.col1 is the leading column. Consider the following query: select A.col4 from A,B,C where B.col3 = 10 and A.col1 = B.col1 and A.col2 = C.col2 and C.col3 = 5 We could represent the joins present in the query using the following schematic: B <---> A <---> C col3=10 col3=5 There are really only 2 ways we can drive the query: via B.col3 or C.col3. We would have to do a Full scan of A to be able to drive off it. This is unlikely to be efficient with large tables; If we drive off table B, using predicate B.col3=10 (as a filter or lookup key) then we will retrieve the value for B.col1 and join to A.col1. Because we have now filled the leading column of the concatenated index on table A we can use this index to give us values for A.col2 and join to A. However if we drive of table c, then we only get a value for a.col2 and since this is a trailing column of a concatenated index and the leading column has not been supplied at this point, we cannot use the index on a to lookup the data. So it is likely that the best join order will be B A C. The CBO will obviously use costs to establish whether the individual access paths are a good idea or not. If the CBO does not choose this join order then we can hint it by changing the from clause to read: from B,A,C and using the /*+ ordered */ hint. The resultant query would be: select /*+ ordered */ A.col4 from B,A,C where B.col3 = 10 and A.col1 = B.col1 and A.col2 = C.col2 and C.col3 = 5 Join Types
Sort Merge Join Rows are produced by Row Source 1 and are then sorted Rows from Row Source 2 are then produced and sorted by the same sort key as Row Source 1. Row Source 1 and 2 are NOT accessed concurrently Sorted rows from both sides are then merged together (joined) MERGE / \ SORT SORT | | Row Source 1 Row Source 2 If the row sources are already (known to be) sorted then the sort operation is unecessary as long as both 'sides' are sorted using the same key. Presorted row sources include indexed columns and row sources that have already been sorted in earlier steps. Although the merge of the 2 row sources is handled serially, the row sources could be accessed in parallel. SQL> explain plan for select /*+ ordered */ e.deptno,d.deptno from emp e,dept d where e.deptno = d.deptno order by e.deptno,d.deptno; Query Plan ------------------------------------- SELECT STATEMENT [CHOOSE] Cost=17 MERGE JOIN SORT JOIN TABLE ACCESS FULL EMP [ANALYZED] SORT JOIN TABLE ACCESS FULL DEPT [ANALYZED] Sorting is an expensive operation, especially with large tables. Because of this, SMJ is often not a particularly efficient join method. Nested Loops First we return all the rows from row source 1 Then we probe row source 2 once for each row returned from row source 1 Row source 1 ~~~~~~~~~~~~ Row 1 -------------- -- Probe -> Row source 2 Row 2 -------------- -- Probe -> Row source 2 Row 3 -------------- -- Probe -> Row source 2 Row source 1 is known as the outer table Row source 2 is known as the inner table Accessing row source 2 is known a probing the inner table For nested loops to be efficient it is important that the first row source returns as few rows as possible as this directly controls the number of probes of the second row source. Also it helps if the access method for row source 2 is efficient as this operation is being repeated once for every row returned by row source 1. SQL> explain plan for select a.dname,b.sql from dept a,emp b where a.deptno = b.deptno; Query Plan ------------------------- SELECT STATEMENT [CHOOSE] Cost=5 NESTED LOOPS TABLE ACCESS FULL DEPT [ANALYZED] TABLE ACCESS FULL EMP [ANALYZED] Hash Join New join type introduced in 7.3 More efficient in theory than NL & SMJ Only accessible via the CBO Smallest row source is chosen and used to build a hash table and a bitmap The second row source is hashed and checked against the hash table looking for joins. The bitmap is used as a quick lookup to check if rows are in the hash table and are especially useful when the hash table is too large to fit in memory. SQL> explain plan for select /*+ use_hash(emp) */ empno from emp,dept where emp.deptno = dept.deptno; Query Plan --------------------------------- SELECT STATEMENT [CHOOSE] Cost=3 HASH JOIN TABLE ACCESS FULL DEPT TABLE ACCESS FULL EMP Hash joins are enabled by the parameter HASH_JOIN_ENABLED=TRUE in the init.ora or session. TRUE is the default in 7.3 Cartesian Product A Cartesian Product is done where they are no join conditions between 2 row sources and there is no alternative method of accessing the data Not really a join as such as there is no join! Typically this is caused by a coding mistake where a join has been left out. It can be useful in some circumstances - Star joins uses cartesian products. Notice that there is no join between the 2 tables: SQL> explain plan for select emp.deptno,dept,deptno from emp,dept Query Plan ------------------------------ SLECT STATEMENT [CHOOSE] Cost=5 MERGE JOIN CARTESIAN TABLE ACCESS FULL DEPT SORT JOIN TABLE ACCESS FULL EMP The CARTESIAN keyword indicate that we are doing a cartesian product. Operations Operations that show up in explain plans
Sorts There are a number of different operations that promote sorts
Note that if the row source is already appropriately sorted then no sorting is required. This is now indicated in 7.3: SORT GROUP BY NOSORT INDEX FULL SCAN ..... In this case the group by operation simply groups the rows it does not do the sort operation as this has already been completed. Sorts are expensive operations especially on large tables where the rows do not fit in memory and spill to disk. By default sort blocks are placed into the buffer cache. This may result in aging out of other blocks that may be reread by other processes. To avoid this you can use the parameter <Parameter:SORT_DIRECT_WRITES> which does not place sort blocks into the buffer cache. Filter Has a number of different meanings used to indicate partition elimination may also indicate an actual filter step where one row source is filtering another functions such as min may introduce filter steps into query plans In this example there are 2 filter steps. The first is effectively like a NL except that it stops when it gets something that it doesn't like (i.e. a bounded NL). This is there because of the not in. The second is filtering out the min value: SQL> explain plan for select * from emp where empno not in (select min(empno) from big_emp group by empno); Query Plan ------------------ SELECT STATEMENT [CHOOSE] Cost=1 FILTER **** This is like a bounded nested loops TABLE ACCESS FULL EMP [ANALYZED] FILTER **** This filter is introduced by the min SORT GROUP BY NOSORT INDEX FULL SCAN BE_IX This example is also interesting in that it has a NOSORT function. The group by does not need to sort because the index row source is already pre sorted. Views When a view cannot be merged into the main query you will often see a projection view operation. This indicates that the 'view' will be selected from directly as opposed to being broken down into joins on the base tables. A number of constructs make a view non mergeable. Inline views are also non mergeable. In the following example the select contains an inline view which cannot be merged: SQL> explain plan for select ename,tot from emp, (select empno,sum(empno) tot from big_emp group by empno) tmp where emp.empno = tmp.empno; Query Plan ------------------------ SELECT STATEMENT [CHOOSE] HASH JOIN TABLE ACCESS FULL EMP [ANALYZED] VIEW SORT GROUP BY INDEX FULL SCAN BE_IX In this case the inline view tmp which contains an aggregate function cannot be merged into the main query. The explain plan shows this as a view step. Partition Views Allows a large table to be broken up into a number of smaller partitions which can be queried much more quickly than the table as a whole a union all view is built over the top to provide the original functionality Check constraints or where clauses provide partition elimination capabilities SQL> explain plan for select /*+ use_nl(p1,kbwyv1) ordered */ sum(prc_pd) from parent1 p1, kbwyv1 where p1.class = 22 and kbwyv1.bitm_numb = p1.bitm_numb and kbwyv1.year = 1997 and kbwyv1.week between 32 and 33 ; Query Plan ----------------------------------------- SELECT STATEMENT [FIRST_ROWS] Cost=1780 SORT AGGREGATE NESTED LOOPS [:Q65001] Ct=1780 Cd=40 Bt=3120 TABLE ACCESS FULL PARENT1 [:Q65000] [AN] Ct=20 Cd=40 Bt=1040 VIEW KBWYV1 [:Q65001] UNION-ALL PARTITION [:Q65001] FILTER [:Q64000] TABLE ACCESS FULL KBWYT1 [AN] Ct=11 Cd=2000 Bt=104000 TABLE ACCESS FULL KBWYT2 [AN] Ct=11 Cd=2000 Bt=104000 TABLE ACCESS FULL KBWYT3 [AN] Ct=11 Cd=2000 Bt=104000 FILTER [:Q61000] TABLE ACCESS FULL KBWYT4 [AN] Ct=11 Cd=2000 Bt=104000 KBWYV1 is a view on 4 tables KBWYT1-4. KBWYT1-4 contain rows for week 31-34 respectively and are maintained by check constraints. This query should only return rows from partions 2 & 3. The filter operation indicates this. Partitions 1 & 4 are eliminated at execution time. The view line indicates that the view is not merged. The union-all partion information indicates that we have recognised this as a partition view. Note that the tables can be accessed in parallel. Remote Queries Only shows remote in the OPERATION column OTHER column shows query executed on remote node OTHER_NODE shows where it is executed Different operational characteristics for RBO & CBO RBO - Drags everything across the link and joins locally CBO - Uses cost estimates to determine whether to execute remotely or locally SQL> explain plan for select * from dept@loop_link; Query Plan ------------------------------------------------------- SELECT STATEMENT REMOTE [CHOOSE] Cost=1 TABLE ACCESS FULL DEPT [SJD.WORLD] [ANALYZED] In this case the whole query has been sent to the remote site. The other column shows nothing. SQL> explain plan for select a.dname,avg(b.sal),max(b.sal) from dept@loop_link a, emp b where a.deptno=b.deptno group by a.dname order by max(b.sal),avg(b.sal) desc; Query Plan ----------------------------------------------------- SELECT STATEMENT [CHOOSE] Cost=20 SORT ORDER BY [:Q137003] [PARALLEL_TO_SERIAL] SORT GROUP BY [:Q137002] [PARALLEL_TO_PARALLEL] NESTED LOOPS [:Q137001] [PARALLEL_TO_PARALLEL] REMOTE [:Q137000] [PARALLEL_FROM_SERIAL] TABLE ACCESS FULL EMP [:Q137001] [ANALYZED] [PARALLEL_COMBINED_WITH_PARENT] Bind Variables Bind variables are recommended in most cases because they promote sharing of sql code At parse time the parser has NO IDEA what the bind variable contains. With RBO this makes no difference but with CBO, which relies on accurate statistics to produce plans, this can be a problem. Defining bind variables in sqlplus: variable x varchar2(18); assigning values: begin :x := 'hello'; end; / SQL> explain plan for select * from dept where rowid = ':x'; Query Plan ------------------------------------ SELECT STATEMENT [CHOOSE] Cost=1 TABLE ACCESS BY ROWID DEPT [ANALYZED] Parallel Query Main indicators that a query is using PQO:
Columns to look in for information
Parallel Query operates on a producer/consumer basis. When you specify parallel degree 4 oracle tries to allocate 4 producer slaves and 4 consumer slaves. The producers can feed any of the consumers. If there are only 2 slaves available then we use these. If there is only 1 slave available then we go serial If there are none available then we use serial. If parallel_min_percent is set then we error ora 12827 instead of using a lower number of slaves or going serial. Consumer processes typically perform a sorting function. If there is no requirement for the data to be sorted then the consumer slaves are not produced and we end up with the number of slaves used matching the degree of parallelism as opposed to being 2x the degree. Parallel Terms
Examples of parallel queries Assumptions OPTIMIZER_MODE = CHOOSE DEPT is small compared to EMP DEPT has an index (DEPT_INDX) on deptno column Three examples are presented Query #1: Serial Query #2: Parallel Query #3: Parallel, with forced optimization to RULE and forced usage of DEPT_INDX Sample Query #1 (Serial) select A.dname, avg(B.sal), max(B.sal) from dept A, emp B where A.deptno = B.deptno group by A.dname order by max(B.sal), avg(B.sal) desc; Execution Plan #1 (Serial) OBJECT_NAME OBJECT_NODE OTHER ------------------------------- ----------- ------- SELECT STATEMENT SORT ORDER BY SORT GROUP BY MERGE JOIN SORT JOIN TABLE ACCESS FULL emp SORT JOIN TABLE ACCESS FULL dept Notice that the object_node and other columns are empty Sample Query #2 (Query #1 with parallel hints) select /*+ parallel(B,4) parallel(A,4) */ A.dname, avg(B.sal), max(B.sal) from dept A, emp B where A.deptno = B.deptno group by A.dname order by max(B.sal), avg(B.sal) desc; Execution Plan #2 (Parallel) OBJECT_NAME OBJECT_NODE OTHER ------------------------------- ----------- ------- SELECT STATEMENT Cost = ?? SORT ORDER BY :Q55004 **[7]** SORT GROUP BY :Q55003 **[6]** MERGE JOIN :Q55002 **[5]** SORT JOIN :Q55002 **[4]** TABLE ACCESS FULL emp :Q55001 **[2]** SORT JOIN :Q55002 **[3]** TABLE ACCESS FULL dept :Q55000 **[1]** Execution Plan #2 -- OTHER column **[1]** (:Q55000) "PARALLEL_FROM_SERIAL" Serial execution of SELECT DEPTNO, DNAME FROM DEPT **[2]** (:Q55001) "PARALLEL_TO_PARALLEL" SELECT /*+ ROWID(A1)*/ A1."DEPTNO" C0, A1."SAL" C1 FROM "EMP" A1 WHERE ROWID BETWEEN :1 AND :2 **[3]** (:Q55002) "PARALLEL_COMBINED_WITH_PARENT" **[4]** (:Q55002) "PARALLEL_COMBINED_WITH_PARENT" **[5]** (:Q55002) "PARALLEL_TO_PARALLEL" SELECT /*+ ORDERED USE_MERGE(A2)*/ A2.C1 C0, A1.C1 C1 FROM :Q55001 A1,:Q55000 A2 WHERE A1.C0=A2.C0 **[6]** (:Q55003) "PARALLEL_TO_PARALLEL" SELECT MAX(A1.C1) C0, AVG(A1.C1) C1, A1.C0 C2 FROM :Q55002 A1 GROUP BY A1.C0 **[7]** (:Q55004) "PARALLEL_FROM_SERIAL" SELECT A1.C0 C0, A1.C1 C1, A1.C2 C2 FROM :Q55003 A1 ORDER BY A1.CO, A1.C1 DESC Sample Query #3 (Query #2 with fudged hints) select /*+ index(A dept_indx) parallel(B,4) parallel(A,4) */ A.dname, avg(B.sal), max(B.sal) from dept A, emp B where A.deptno = B.deptno group by A.dname order by max(B.sal), avg(B.sal) desc; Execution Plan #3 (Parallel) OBJECT_NAME OBJECT_NODE OTHER ----------------------------------- ----------- ------- SELECT STATEMENT Cost = ?? SORT ORDER BY :Q58002 **[6]** SORT GROUP BY :Q58001 **[5]** NESTED LOOPS JOIN :Q58000 **[4]** TABLE ACCESS FULL emp :Q58000 **[3]** TABLE ACCESS BY ROWID dept :Q58000 **[2]** INDEX RANGE SCAN dept_indx :Q58000 **[1]** Execution Plan #3 -- OTHER column **[1]** (:Q58000) "PARALLEL_COMBINED_WITH_PARENT" **[2]** (:Q58000) "PARALLEL_COMBINED_WITH_PARENT" **[3]** (:Q58000) "PARALLEL_COMBINED_WITH_PARENT" **[4]** (:Q58000) "PARALLEL_TO_PARALLEL" SELECT /*+ ORDERED USE_NL(A2) INDEX(A2) */ A2."DNAME" C0, A1.C0 C1 FROM (SELECT /*+ ROWID(A3) */ A3."SAL" CO, A3."DEPTNO" C1 FROM "EMP" A3 WHERE ROWID BETWEEN :1 AND :2) A1, "DEPT" A2 WHERE A2."DEPTNO" = A1.C1 **[5]** (:Q58001) "PARALLEL_TO_PARALLEL" SELECT MAX(A1.C1) C0, AVG(A1.C1) C1, A1.C0 C2 FROM :Q58000 A1 GROUP BY A1.C0 **[6]** (:Q58002) "PARALLEL_TO_SERIAL" SELECT A1.C0 C0, A1.C1 C1, A1.C2 C2 FROM :Q58001 A1 ORDER BY A1.C0, A1.C1 DESC The following is a sample execution plan. SQL> explain plan for 2 select e.empno, e.ename, d.dname 3 from emp e, dept d 4 where e.deptno = d.deptno 5 and e.deptno = 10; Explained. SQL> SELECT * FROM table(dbms_xplan.display(null,null,'basic')); PLAN_TABLE_OUTPUT ------------------------------------------------------------ Plan hash value: 568005898 ------------------------------------------------------------ | Id | Operation | Name | ------------------------------------------------------------ | 0 | SELECT STATEMENT | | | 1 | NESTED LOOPS | | | 2 | TABLE ACCESS BY INDEX ROWID| DEPT | | 3 | INDEX UNIQUE SCAN | PK_DEPT | | 4 | TABLE ACCESS FULL | EMP | ------------------------------------------------ Using the rules above, you could say; Operation 0 is the root of the tree; it has one child, Operation 1 Operation 1 has two children, which is Operation 2 and 4 Operation 2 has one child, which is Operation 3 Below is the graphical representation of the execution plan. If you read the tree; In order to perform Operation 1, you need to perform Operation 2 and 4. Operation 2 comes first; In order to perform 2, you need to perform its Child Operation 3. In order to perform Operation 4, you need to perform Operation 2 Operation 0 (SELECT STATEMENT) | | | Operation 1 (NESTED LOOPS) / \ / \ / \ / \ / \ / \ Operation 2 Operation 4 (TABLE ACCESS (TABLE ACCESS FULL) BY INDEX ROWID) | | | Operation 3 (INDEX UNIQUE SCAN) Operation 3 accesses DEPT table using INDEX UNIQUE SCAN and passes the ROWID to Operation 2 Operation 2 returns all the rows from DEPT table to Operation 1 Operation 1 performs Operation 4 for each row returned by Operation 2 Operation 4 performs a full table scan (TABLE ACCESS FULL) scan and applies the filter E.DEPTNO=10 and returns the rows to Operation 1 Operation 1 returns the final results to Operation 0 Reference :: click here | |||||||||||||||||||||||||||||||||
Friday, January 13, 2012
Interpreting Explain Plan
Tuesday, January 10, 2012
ORA-1031: Insufficient Privileges
This is one of very common and frequently occuring error . According to the docs note ,the cause of the ORA-01031 is :
Cause : An attempt was made to change the current username or password without the appropriate privilege. This error also occurs if attempting to install a database without the necessary operating system privileges. When Trusted Oracle is configure in DBMS MAC, this error may occur if the user was granted the necessary privilege at a higher label than the current login .
Action : Ask the database administrator to perform the operation or grant the required privileges. For Trusted Oracle users getting this error although granted the appropriate privilege at a higher label, ask the database administrator to re-grant the privilege at the appropriate label .
As we see the above action , nothing is explained in details and only mention about the privileges issues . But ORA-1031 may not only occurs with insufficient privileges but may other reasons too. Here i have try to cover all the possible reason of this error .
1.) OS authentication :
2.) Password file authentication :
1.) OS Authentication : OS authentication allows Oracle to pass control of user authentication to the operating system .The OS authentication is the process of verifying the identity of the user connecting to the database with the information managed by the OS. An OS user is able to use this authentication method if the following conditions are met:
ii.) The OS authentication is allowed by the server settings(sqlnet.authentication_services is set correctly) : On Unix Parameter sqlnet.authentication_services must be set to (ALL) or to (BEQ, <other values>) for this to work. On Windows this parameter must be set to (NTS) or (NONE) or ALL.
If we have configured the Operating System authentication and an OS user is a member of the special groups OSDBA or OSOPER then the OS user does not have to provide any credentials while connecting to the database as SYSDBA or AS SYSOPER from the oracle Server host machine when using the bequeath protocol. As long as the session is not established through the listener the OS Authentication will be used and the credentials provided will be ignored. This means that the users who are able to use the OS authentication can use any username and any password to connect to the database locally on the Oracle Server using the below syntax:
$sqlplus / as sysdba
or
SQL> connect any_username/any_password AS SYSDBA
2.) Password File Authentication : The password file provides a method to authenticate privileged users from a remote (over sqlnet / listener) location . By default the user SYS gets an entry in the passwordfile when we create it so we can connect to a remote database . The credentials provided when connecting remotely as sysdba are compared to the contents of the passwordfile. For example
$sqlplus sys/xxxx@db_name as sysdba Password file authentication is enabled by setting the database parameter remote_login_password file to "shared" or "exclusive". For more about Password file Click Here
If we grant the SYSDBA or SYSOPER privilege to any additional user then that user will also get an entry in the passwordfile: the hashed password of that user is then copied to the passwordfile , when this user connects,the effective user will be SYS .
Note: When both OS authentication and password file authentication are enabled then the OS Authentication will be used. This means that we can connect with any username/password combination.
Here we will considering all the possible issue related to ORA-1031
Case 1 :
One of the reason of getting ORA-1031 may be because the osuser is not the member of dba group .In case of Linux , the osuser user must be the memeber of "DBA" group . To check the group the use the below command .
[oracle@Ramtech ~]$ id uid=501(oracle) gid=502(oinstall) groups=501(dba),502(oinstall)
In case of Window, OS user must be a member of ora_dba group . Check whether the OS user is a member of ORA_DBA or not by using the below command :
C:\>echo %username% ( for current osuser )
Neerajs
C:\>NET LOCALGROUP ORA_DBA
Alias name ORA_DBA
Comment Oracle DBA Group
Members
------------------------------------
Neerajs
NT AUTHORITY\SYSTEM
The command completed successfully.
If the osuser is not the member of the above group then add the osuser to the DBA and ORA_DBA in case of Linux and Window respectively.
Check the value of the SQLNET.AUTHENTICATION_SERVICES parameter in file $ORACLE_HOME/network/admin/SQLNET.ORA .
On Unix/Linux : This parameter should not be set if no strong authentication method is used. If such a method is being used then set the parameter to one of the following values:
SQLNET.AUTHENTICATION_SERVICES = (ALL)
or
SQLNET.AUTHENTICATION_SERVICES = (BEQ,<the strong auth method>)
Where <the strong auth method> can be any combination of the following values: TCPS, KERBEROS5, RADIUS .I will cover more about the sqlnet parameter in my later post .
On Windows : This parameter should be set to NTS or if needed we can add other strong authentication methods besides NTS as such NONE .
SQLNET.AUTHENTICATION_SERVICES = (NTS)SQLNET.AUTHENTICATION_SERVICES = (NTS,TCPS)
Note: If the parameter is set to NONE then the OS authentication will be disabled and the user will have to provide a valid username/password combination to be able to connect to the database. On Windows the user who is not able to connect as sysdba using OS authentication might be a domain user. Check the following if we are in this scenario:
B.) Oracle Service must be started as a user who is able to check the group membership for any domain user who might be connecting as sysdba locally.
C.) Check whether the clocks of the RDBMS Server and of the Active Directory Server are perfectly synchronized. Even small clock drifts can cause issues to the underlying kerberos authentication mechanism used by default on Windows. In these cases the ORA-1031 would be most of the times intermittent.
D.) Check whether the Oracle Service is started by an user whose name contains non ASCII characters .CASE 3 :
Check the value of parameter remote_login_passwordfile. This has to be set to either EXCLUSIVE or SHARED .
SQL> show parameter remote_login_passwordfile
NAME TYPE VALUE
------------------------------- -------- ---------------
remote_login_passwordfile string EXCLUSIVE
If the parameter is not set correctly then modify it and then restart the database:
SQL> alter system set remote_login_passwordfile=exclusive scope=spfile ;
Check whether the password file with the correct name exists in the right directory/folder and has the right ownership and permissions .
On Unix/Linux : The password file with the name orapw<ORACLE_SID> must exist in directory $ORACLE_HOME/dbs. If it does not exist then recreate it using the orapwd command .
[oracle@Ramtech dbs]$orapwd file=$ORACLE_HOME/dbs/orapw<sid> password=<password> force=y ignorecase=n
[oracle@Ramtech dbs]$ ls -l orapw*
-rw-r----- 1 oracle oinstall 1536 Jan 10 14:44 orapwcomcastOn Windows : The default location of the password file on Windows is folder %ORACLE_HOME%/database and the name of the password file must be pwd<%ORACLE_SID%>.ora. When the passwordfile authentication is being used Oracle searches for the password file in the following locations(in this exact order):
The folder pointed to by the registry key HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HomeXX\ORA_PWFILE
The default location ( %ORACLE_HOME%\database)
If the password file does not exist in the right folder then create it using the orapwd command:
C:\> cd %ORACLE_HOME/database
C:\> orapwd file=pwd<sid>.ora password=<password> force=y nosysdba=n
Note: Make sure that the password file exists in the folder specified by the registry keys if these are set.
CASE 4 :
Check whether the user was granted the SYSDBA privilege. Sometimes , we may get this error when we don't have sysdba privileges and try to connect as sysdba when sqlnet.authentication_services is NONE . For example :
C:\>sqlplus scott/tiger@noida as sysdbaSQL*Plus: Release 11.2.0.1.0 Production on Tue Jan 10 17:56:27 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
ERROR:
ORA-01031: insufficient privileges
Check the v$pwfile_users view to find the user which is having sysdba or sysoper privileges .
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP SYSAS
------------ ------- -------- --------
SYS TRUE TRUE FALSE
If the user is not granted the privilege then log as sys user and run:
SQL> grant SYSDBA to scott ;
where scott is the user wewant to use to connect as sysdba.
CASE 5 : If the problem is not solved after reviewing the above notes open a service request with Oracle Support .
Comments welcome ....
Enjoy J J J
Monday, January 9, 2012
How to Reconfigure OEM in Oracle 10g on Linux
We generally find that the OEM is not working due to the various reason . The best approach to solve this issue is to drop the OEM and reconfigure it . OEM may not work for various reason . In my case , I have install the oracle 10g on my system and then changed the computer host name . This leads the OEM not working . The following are the steps to configure the OEM :
Step 1 : Change IP to Machine name :
i.) Change the $ORACLE_HOME/network/admin/listener.ora file from an IP number to machine name.
ii.) Change the $ORACLE_HOME/network/admin/tnsnames.ora file from an IP number to a machine name.
Step 2 : Change the computer name :
Open the network file and replace with new HOSTNAME . (in my case, I have changed from localhost to Ramtech.com) .
NETWORKING=yes
NETWORKING_IPV6=no
HOSTNAME=Ramtech.com
save the changes and exit vi. Now edit the /etc/hosts file and set new host name . In hosts file edit the line and replace it with new one as
[root@Ramtech ~]# vi /etc/hosts
127.0.0.1 localhost
192.168.80.131 Ramtech.com
save and exit vi. The changes to /etc/hosts and /etc/sysconfig/network are necessary to make our changes persistent . Finally, we will restart the network to apply the changes we made to above file .
Shutting down interface eth0: [ OK ]
Shutting down loopback interface: [ OK ]
Bringing up loopback interface: [ OK ]
Bringing up interface eth0: [ OK ]
Now we use the hostname command to check the changes :
[root@Ramtech ~]# hostname
Ramtech.com
Step 3 : Drop the em configuration :
First , we will drop the existing console and repository .
[oracle@Ramtech ~]$ export ORACLE_SID=noida
[oracle@Ramtech ~]$ emca -deconfig dbcontrol db -repos drop
STARTED EMCA at Jan 8, 2012
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Enter the following information:
Database SID: noida
Listener port number: 1521
Password for SYS user:
Password for SYS user:
Do you wish to continue? [yes(Y)/no(N)] : y
oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /home/oracle/product/10.2.0/db_1/cfgtoollogs/emca/noida/emca_2012-01-08_05-52-43-PM.log.
Jan 8, 2012 5:55:32 PM oracle.sysman.emcp.EMDBPreConfig performDeconfiguration
WARNING: EM is not configured for this database. No EM-specific actions can be performed.
Jan 8, 2012 5:55:33 PM oracle.sysman.emcp.EMReposConfig dropRepository
INFO: Dropping the EM repository (this may take a while) ...
Jan 8, 2012 5:56:52 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Enterprise Manager configuration completed successfully
FINISHED EMCA at Jan 8, 2012 5:56:59 PM
Step 4 : Drop the user and dependent :
If any of the below objects exists ,then we will face error , so before creating the OEM drop the following :
SQL> drop user SYSMAN cascade;
SQL> drop user MGMT_VIEW ;
SQL> drop role MGMT_USER ;
and finally drop all the dependent synonyms of "sysman" schema .
SQL> spool drop_synonym.sql
SQL> SELECT 'drop public synonym ' || synonym_name || ';' FROM dba_synonyms WHERE table_owner = 'SYSMAN';
SQL> @drop_synonym.sql
Step 5 : Re-create the OEM :
[oracle@Ramtech ~]$ export ORACLE_SID=noida
[oracle@Ramtech ~]$ emca -config dbcontrol db -repos create
STARTED EMCA at Jan 8, 2012 5:58:50 PM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Enter the following information:
Database SID: noida
Listener port number: 1521
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
-----------------------------------------------------------------
You have specified the following settings
Database ORACLE_HOME ................ /home/oracle/product/10.2.0/db_1
Database hostname ................ Ramtech.com
Listener port number ................ 1521
Database SID ................ noida
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............
-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: Y
Jan 8, 2012 5:59:31 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /home/oracle/product/10.2.0/db_1/cfgtoollogs/emca/noida/emca_2012-01-08_05-58-49-PM.log.
Jan 08, 2012 06:01:30 PM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
Jan 08, 2012 06:17:38 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Jan 08, 2012 06:18:50 PM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Jan 08, 2012 06:23:01 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Jan 08, 2012 06:23:02 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is http://Ramtech.com:5500/em <<<<<<<<<<<
Enterprise Manager configuration completed successfully
FINISHED EMCA at Jan 08, 2012 06:23:02 PM
Step 6 : Start and stop the dbconsole :
$ emctl start dbconsole
$ emctl stop dbconsole
$ emctl status dbconsole
Note : The same steps are used in window also. Sometimes we cannot able to recreate the oem(in case of window) in such case delete the instance(using oradim) and again create the instance (using oradim) and then try to configure the OEM .
Enjoy J J J
Subscribe to:
Posts (Atom)