PRVG-10603 : Clusterware version consistency failed

I got this error while upgrading my grid version from 11.2.0.3 to 11.2.0.4 on Linux box.

./cluvfy stage -pre crsinst -upgrade -n node01,node02-rolling  -src_crshome /optware/grid/11.2.0.3 -dest_crshome /optware/grid/11.2.0.4 -dest_version 11.2.0.4.0 -fixup -fixupdir /orabase/grid/fixup -verbose

Here is the error:

PRVF-4038 : CRS is not installed on nodes:
***-phys1
Verification will proceed with nodes:
***-phys2

PRVG-10603 : Clusterware version consistency failed **********************Check first inventory

Pre-check for cluster services setup was unsuccessful.
Checks did not pass for the following node(s):
***-phys1

I checked first my inventory is valid or not. You must have ONE inventory showing your ALL homes. This is output of node02

more /etc/oraInst.loc

#Oracle Installer Location File Location
#Tue Mar 26 06:05:04 EDT 2013
inst_group=dba
inventory_loc=/optware/grid/11.2.0.3/oraInventory

more inventory.xml
All rights reserved. –>
<!– Do not modify the contents of this file by hand. –>
<INVENTORY>
<VERSION_INFO>
<SAVED_WITH>11.2.0.4.0</SAVED_WITH>
<MINIMUM_VER>2.1.0.6.0</MINIMUM_VER>
</VERSION_INFO>
<HOME_LIST>
<HOME NAME=”oraGrid11gR2_20130322″ LOC=”/optware/grid/11.2.0.3″ TYPE=”O” IDX=”1″ CRS=”true”>  *********************TRUE for old home, at most one grid home is showing “CRS=true”
<NODE_LIST>
<NODE NAME=”isgswdbd66n1″/>
<NODE NAME=”isgswdbd66n2″/>
</NODE_LIST>
</HOME>
<HOME NAME=”oraDB11gR2_20130320″ LOC=”/optware/oracle/11.2.0.3/db” TYPE=”O” IDX=”2″>
<NODE_LIST>
<NODE NAME=”isgswdbd66n1″/>
<NODE NAME=”isgswdbd66n2″/>
</NODE_LIST>
</HOME>
<HOME NAME=”Ora11g_gridinfrahome1” LOC=”/optware/grid/11.2.0.4″ TYPE=”O” IDX=”3″/> ****New home must be on both nodes
<HOME NAME=”OraDb11g_home1” LOC=”/optware/oracle/11.2.0.4/db” TYPE=”O” IDX=”4″>  ****New home must be on both nodes
<NODE_LIST>
<NODE NAME=”isgswdbd66n1″/>
<NODE NAME=”isgswdbd66n2″/>
</NODE_LIST>
</HOME>
</HOME_LIST>
<COMPOSITEHOME_LIST>
</COMPOSITEHOME_LIST>
</INVENTORY

But the first node has no inventory entities showing new installation for ASM and RDBMS home. So you’ve to attach these homes on node01 (missing inventory entities).Don’t try to update inventory.xml manually use runInstaller, run it on node01

./runInstaller -silent -attachHome -local ORACLE_HOME=”/optware/grid/11.2.0.4″ ORACLE_HOME_NAME=”Ora11g_gridinfrahome1” “CLUSTER_NODES={node02,node01}” LOCAL_NODE=’node01′

./runInstaller -silent -attachHome -local ORACLE_HOME=”/optware/oracle/11.2.0.4/db” ORACLE_HOME_NAME=”OraDb11g_home1” “CLUSTER_NODES={node02,node01}” LOCAL_NODE=’node01′

and check your inventory on inventory.xml whether it is there or not and make sure that you’ve ONE inventory pointing from /etc/oraInst.loc

after rootupgrade.sh inventory will be updated by OUI
<?xml version=”1.0″ standalone=”yes” ?>
<!– Copyright (c) 1999, 2013, Oracle and/or its affiliates.
All rights reserved. –>
<!– Do not modify the contents of this file by hand. –>
<INVENTORY>
<VERSION_INFO>
<SAVED_WITH>11.2.0.4.0</SAVED_WITH>
<MINIMUM_VER>2.1.0.6.0</MINIMUM_VER>
</VERSION_INFO>
<HOME_LIST>
<HOME NAME=”oraGrid11gR2_20130322″ LOC=”/optware/grid/11.2.0.3″ TYPE=”O” IDX=”1″>
<NODE_LIST>
<NODE NAME=”isgswdbd66n1″/>
<NODE NAME=”isgswdbd66n2″/>
</NODE_LIST>
</HOME>
<HOME NAME=”oraDB11gR2_20130320″ LOC=”/optware/oracle/11.2.0.3/db” TYPE=”O” IDX=”2″>
<NODE_LIST>
<NODE NAME=”isgswdbd66n1″/>
<NODE NAME=”isgswdbd66n2″/>
</NODE_LIST>
</HOME>
<HOME NAME=”Ora11g_gridinfrahome1″ LOC=”/optware/grid/11.2.0.4″ TYPE=”O” IDX=”3″ CRS=”true”>  ************************ only once CRS=TRUE .(must be)
<NODE_LIST>
<NODE NAME=”isgswdbd66n1″/>
<NODE NAME=”isgswdbd66n2″/>
</NODE_LIST>
</HOME>
<HOME NAME=”OraDb11g_home1″ LOC=”/optware/oracle/11.2.0.4/db” TYPE=”O” IDX=”4″>
<NODE_LIST>
<NODE NAME=”isgswdbd66n1″/>
<NODE NAME=”isgswdbd66n2″/>
</NODE_LIST>
</HOME>
</HOME_LIST>
<COMPOSITEHOME_LIST>
</COMPOSITEHOME_LIST>
</INVENTORY

How to move execution plan to another database using SQLHC/SQLT

One of my  query has different execution plan on both databases. One is running faster but the other is not.So I decided to move faster execution plan to slow database.

On faster database,

SQL> @sqlt/utl/coe_xfr_sql_profile.sql  SQL_ID PLAN_HASH_VALUE

This will create a script named coe_xfr_sql_profile_**SQL_ID***_***PLAN_HASH_VALUE***.sql in the local directory.

Edit this script and change the line:that starts
old force_match => FALSE
new force_match => TRUE

On the slower database,execute this script

SQL> @coe_xfr_sql_profile_***.sql

and then execute the query again and get the report and check the plan is correct.

SQL>START sqlhc.sql T sql_id

P.S

SQLHC is running online mode of SQLT

Why my tablespace report is slow on one of my databases ?

Application team started complain about getting table space report is too slow.When I checked it, It took 20 min to get report. But I can get the same report in a sec. from other databases.

I executed SQL script and get the SQLHC report.

——————————————————————————————————————
| Id  | Operation                         | Name                 | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
——————————————————————————————————————
|   0 | SELECT STATEMENT                  |                      |       |       |       |  1628 (100)|          |
|   1 |  SORT ORDER BY                    |                      |    17 |  1163 |       |  1627  (37)| 00:00:02 |
|   2 |   UNION-ALL                       |                      |       |       |       |            |          |
|   3 |    HASH JOIN OUTER                |                      |    16 |  1008 |       |  1621  (37)| 00:00:02 |
|   4 |     VIEW                          |                      |    16 |   528 |       |     9  (23)| 00:00:01 |
|   5 |      HASH GROUP BY                |                      |    16 |   416 |       |     9  (23)| 00:00:01 |
|   6 |       VIEW                        | DBA_DATA_FILES       |    42 |  1092 |       |     8  (13)| 00:00:01 |
|   7 |        UNION-ALL                  |                      |       |       |       |            |          |
|   8 |         HASH JOIN                 |                      |     1 |    89 |       |     3   (0)| 00:00:01 |
|   9 |          NESTED LOOPS             |                      |     1 |    41 |       |     3   (0)| 00:00:01 |
|  10 |           NESTED LOOPS            |                      |     1 |    24 |       |     2   (0)| 00:00:01 |
|  11 |            TABLE ACCESS FULL      | FILE$                |     1 |    21 |       |     2   (0)| 00:00:01 |
|  12 |            FIXED TABLE FIXED INDEX| X$KCCFE (ind:1)      |     1 |     3 |       |     0   (0)|          |
|  13 |           TABLE ACCESS CLUSTER    | TS$                  |     1 |    17 |       |     1   (0)| 00:00:01 |
|  14 |            INDEX UNIQUE SCAN      | I_TS#                |     1 |       |       |     0   (0)|          |
|  15 |          FIXED TABLE FULL         | X$KCCFN              |    41 |  1968 |       |     0   (0)|          |
|  16 |         NESTED LOOPS              |                      |    41 |  3813 |       |     5  (20)| 00:00:01 |
|  17 |          HASH JOIN                |                      |    41 |  3690 |       |     5  (20)| 00:00:01 |
|  18 |           NESTED LOOPS            |                      |    41 |  2993 |       |     2   (0)| 00:00:01 |
|  19 |            HASH JOIN              |                      |    41 |  2378 |       |     2   (0)| 00:00:01 |
|  20 |             FIXED TABLE FULL      | X$KCCFN              |    41 |  1968 |       |     0   (0)|          |
|  21 |             TABLE ACCESS FULL     | FILE$                |    42 |   420 |       |     2   (0)| 00:00:01 |
|  22 |            FIXED TABLE FIXED INDEX| X$KTFBHC (ind:1)     |     1 |    15 |       |     0   (0)|          |
|  23 |           TABLE ACCESS FULL       | TS$                  |    16 |   272 |       |     2   (0)| 00:00:01 |
|  24 |          FIXED TABLE FIXED INDEX  | X$KCCFE (ind:1)      |     1 |     3 |       |     0   (0)|          |
|  25 |     VIEW                          |                      |     7 |   210 |       |  1612  (37)| 00:00:02 |
|  26 |      HASH GROUP BY                |                      |     7 |   161 |       |  1612  (37)| 00:00:02 |
|  27 |       VIEW                        | DBA_FREE_SPACE       | 46412 |  1042K|       |  1597  (36)| 00:00:02 |
|  28 |        UNION-ALL                  |                      |       |       |       |            |          |
|  29 |         NESTED LOOPS              |                      |     1 |    66 |       |     2   (0)| 00:00:01 |
|  30 |          NESTED LOOPS             |                      |     1 |    60 |       |     2   (0)| 00:00:01 |
|  31 |           TABLE ACCESS FULL       | FET$                 |     1 |    39 |       |     2   (0)| 00:00:01 |
|  32 |           TABLE ACCESS CLUSTER    | TS$                  |     1 |    21 |       |     0   (0)|          |
|  33 |            INDEX UNIQUE SCAN      | I_TS#                |     1 |       |       |     0   (0)|          |
|  34 |          INDEX UNIQUE SCAN        | I_FILE2              |     1 |     6 |       |     0   (0)|          |
|  35 |         NESTED LOOPS              |                      |  2715 |   114K|       |     8  (75)| 00:00:01 |
|  36 |          HASH JOIN                |                      |  2715 |    98K|       |     7  (72)| 00:00:01 |
|  37 |           TABLE ACCESS FULL       | TS$                  |    10 |   270 |       |     2   (0)| 00:00:01 |
|  38 |           FIXED TABLE FULL        | X$KTFBFE             |  2959 | 29590 |       |     4 (100)| 00:00:01 |
|  39 |          INDEX UNIQUE SCAN        | I_FILE2              |     1 |     6 |       |     0   (0)|          |
|  40 |         HASH JOIN                 |                      | 43695 |  4693K|       |  1108  (38)| 00:00:02 |
|  41 |          TABLE ACCESS FULL        | TS$                  |    10 |   270 |       |     2   (0)| 00:00:01 |
|  42 |          HASH JOIN                |                      | 47619 |  3859K|       |  1104  (38)| 00:00:02 |
|  43 |           INDEX FULL SCAN         | I_FILE2              |    42 |   252 |       |     1   (0)| 00:00:01 |
|  44 |           HASH JOIN               |                      |   100K|  7519K|  7520K|  1098  (37)| 00:00:02 |
|  45 |            FIXED TABLE FULL       | X$KTFBUE             |   100K|  6347K|       |   205 (100)| 00:00:01 |
|  46 |            TABLE ACCESS FULL      | RECYCLEBIN$          |   795K|  9316K|       |   443  (28)| 00:00:01 |
|  47 |         HASH JOIN                 |                      |     1 |    91 |       |   480  (33)| 00:00:01 |
|  48 |          NESTED LOOPS             |                      |     1 |    79 |       |     4   (0)| 00:00:01 |
|  49 |           NESTED LOOPS            |                      |     1 |    73 |       |     4   (0)| 00:00:01 |
|  50 |            TABLE ACCESS FULL      | TS$                  |     1 |    21 |       |     2   (0)| 00:00:01 |
|  51 |            TABLE ACCESS CLUSTER   | UET$                 |     1 |    52 |       |     2   (0)| 00:00:01 |
|  52 |             INDEX RANGE SCAN      | I_FILE#_BLOCK#       |     1 |       |       |     2   (0)| 00:00:01 |
|  53 |           INDEX UNIQUE SCAN       | I_FILE2              |     1 |     6 |       |     0   (0)|          |
|  54 |          TABLE ACCESS FULL        | RECYCLEBIN$          |   795K|  9316K|       |   443  (28)| 00:00:01 |
|  55 |    HASH GROUP BY                  |                      |     1 |   155 |       |     6  (17)| 00:00:01 |
|  56 |     NESTED LOOPS OUTER            |                      |     1 |   155 |       |     5   (0)| 00:00:01 |
|  57 |      NESTED LOOPS                 |                      |     1 |   127 |       |     4   (0)| 00:00:01 |
|  58 |       HASH JOIN                   |                      |     2 |   220 |       |     2   (0)| 00:00:01 |
|  59 |        NESTED LOOPS               |                      |     5 |   355 |       |     0   (0)|          |
|  60 |         NESTED LOOPS              |                      |     5 |   285 |       |     0   (0)|          |
|  61 |          FIXED TABLE FULL         | X$KCCTF              |     6 |    72 |       |     0   (0)|          |
|  62 |          FIXED TABLE FIXED INDEX  | X$KCCFN (ind:1)      |     1 |    45 |       |     0   (0)|          |
|  63 |         FIXED TABLE FIXED INDEX   | X$KTFTHC (ind:1)     |     1 |    14 |       |     0   (0)|          |
|  64 |        VIEW                       | V_$TEMP_SPACE_HEADER |     3 |   117 |       |     2   (0)| 00:00:01 |
|  65 |         NESTED LOOPS              |                      |     3 |   141 |       |     2   (0)| 00:00:01 |
|  66 |          TABLE ACCESS FULL        | TS$                  |     1 |    27 |       |     2   (0)| 00:00:01 |
|  67 |          FIXED TABLE FIXED INDEX  | X$KTFTHC (ind:2)     |     3 |    60 |       |     0   (0)|          |
|  68 |       TABLE ACCESS BY INDEX ROWID | TS$                  |     1 |    17 |       |     1   (0)| 00:00:01 |
|  69 |        INDEX UNIQUE SCAN          | I_TS1                |     1 |       |       |     0   (0)|          |
|  70 |      VIEW PUSHED PREDICATE        | V_$TEMP_EXTENT_POOL  |     1 |    28 |       |     1   (0)| 00:00:01 |
|  71 |       NESTED LOOPS                |                      |     1 |    79 |       |     1   (0)| 00:00:01 |
|  72 |        TABLE ACCESS BY INDEX ROWID| TS$                  |     1 |    27 |       |     1   (0)| 00:00:01 |
|  73 |         INDEX UNIQUE SCAN         | I_TS1                |     1 |       |       |     0   (0)|          |
|  74 |        FIXED TABLE FIXED INDEX    | X$KTSTFC (ind:1)     |     1 |    52 |       |     0   (0)|          |
——————————————————————————————————————
I purged the recycle bin.

purge dba_recyclebin;

select count(*) from dba_recyclebin; –> should return 0. it may take time to purge.

and gather the fixed object statistic again and also  dictionary statistics.

exec DBMS_STATS.GATHER_FIXED_OBJECTS_STATS ;
exec DBMS_STATS.GATHER_DICTIONARY_STATS ; –>More important

exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname => ‘SYS’, cascade => TRUE, estimate_percent => 100);

It solved my problem.My query came in a second.

After that I executed SQLHC again and see no full table scan on RECYLEBIN

SELECT STATEMENT                      |                      |      1 |        |       |    70 (100)|          |     12 |00:00:00.19 |    4841 |      2 |       |       |          |
|   1 |  SORT ORDER BY                        |                      |      1 |     17 |  1163 |    69  (51)| 00:00:01 |     12 |00:00:00.19 |    4841 |      2 |  2048 |  2048 | 2048  (0)|
|   2 |   UNION-ALL                           |                      |      1 |        |       |            |          |     12 |00:00:00.17 |    4841 |      2 |       |       |          |
|*  3 |    HASH JOIN OUTER                    |                      |      1 |     16 |  1008 |    63  (54)| 00:00:01 |      9 |00:00:00.17 |    4789 |      2 |  1368K|  1368K|  921K (0)|
|   4 |     VIEW                              |                      |      1 |     16 |   528 |     9  (23)| 00:00:01 |      9 |00:00:00.12 |      68 |      0 |       |       |          |
|   5 |      HASH GROUP BY                    |                      |      1 |     16 |   416 |     9  (23)| 00:00:01 |      9 |00:00:00.12 |      68 |      0 |  1048K|  1048K|  993K (0)|
|   6 |       VIEW                            | DBA_DATA_FILES       |      1 |     42 |  1092 |     8  (13)| 00:00:01 |     42 |00:00:00.12 |      68 |      0 |       |       |          |
|   7 |        UNION-ALL                      |                      |      1 |        |       |            |          |     42 |00:00:00.12 |      68 |      0 |       |       |          |
|*  8 |         HASH JOIN                     |                      |      1 |      1 |    89 |     3   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |      0 |   982K|   982K|  162K (0)|
|   9 |          NESTED LOOPS                 |                      |      1 |      1 |    41 |     3   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |      0 |       |       |          |
|  10 |           NESTED LOOPS                |                      |      1 |      1 |    24 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |      0 |       |       |          |
|* 11 |            TABLE ACCESS FULL          | FILE$                |      1 |      1 |    21 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |      0 |       |       |          |
|* 12 |            FIXED TABLE FIXED INDEX    | X$KCCFE (ind:1)      |      0 |      1 |     3 |     0   (0)|          |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|  13 |           TABLE ACCESS CLUSTER        | TS$                  |      0 |      1 |    17 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 14 |            INDEX UNIQUE SCAN          | I_TS#                |      0 |      1 |       |     0   (0)|          |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 15 |          FIXED TABLE FULL             | X$KCCFN              |      0 |     41 |  1968 |     0   (0)|          |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|  16 |         NESTED LOOPS                  |                      |      1 |     41 |  3813 |     5  (20)| 00:00:01 |     42 |00:00:00.12 |      66 |      0 |       |       |          |
|* 17 |          HASH JOIN                    |                      |      1 |     41 |  3690 |     5  (20)| 00:00:01 |     42 |00:00:00.01 |      66 |      0 |  1421K|  1421K| 1132K (0)|
|  18 |           NESTED LOOPS                |                      |      1 |     41 |  2993 |     2   (0)| 00:00:01 |     42 |00:00:00.01 |      44 |      0 |       |       |          |
|* 19 |            HASH JOIN                  |                      |      1 |     41 |  2378 |     2   (0)| 00:00:01 |     42 |00:00:00.01 |       2 |      0 |  2440K|  2440K| 1279K (0)|
|* 20 |             FIXED TABLE FULL          | X$KCCFN              |      1 |     41 |  1968 |     0   (0)|          |     42 |00:00:00.01 |       0 |      0 |       |       |          |
|* 21 |             TABLE ACCESS FULL         | FILE$                |      1 |     42 |   420 |     2   (0)| 00:00:01 |     42 |00:00:00.01 |       2 |      0 |       |       |          |
|* 22 |            FIXED TABLE FIXED INDEX    | X$KTFBHC (ind:1)     |     42 |      1 |    15 |     0   (0)|          |     42 |00:00:00.01 |      42 |      0 |       |       |          |
|  23 |           TABLE ACCESS FULL           | TS$                  |      1 |     16 |   272 |     2   (0)| 00:00:01 |     16 |00:00:00.01 |      22 |      0 |       |       |          |
|* 24 |          FIXED TABLE FIXED INDEX      | X$KCCFE (ind:1)      |     42 |      1 |     3 |     0   (0)|          |     42 |00:00:00.11 |       0 |      0 |       |       |          |
|  25 |     VIEW                              |                      |      1 |     11 |   330 |    53  (59)| 00:00:01 |      9 |00:00:00.05 |    4721 |      2 |       |       |          |
|  26 |      HASH GROUP BY                    |                      |      1 |     11 |   253 |    53  (59)| 00:00:01 |      9 |00:00:00.05 |    4721 |      2 |  1088K|  1088K|  993K (0)|
|  27 |       VIEW                            | DBA_FREE_SPACE       |      1 |  25367 |   569K|    45  (52)| 00:00:01 |   3270 |00:00:00.03 |    4721 |      2 |       |       |          |
|  28 |        UNION-ALL                      |                      |      1 |        |       |            |          |   3270 |00:00:00.03 |    4721 |      2 |       |       |          |
|  29 |         NESTED LOOPS                  |                      |      1 |      1 |    66 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |      22 |      0 |       |       |          |
|  30 |          NESTED LOOPS                 |                      |      1 |      1 |    60 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |      22 |      0 |       |       |          |
|  31 |           TABLE ACCESS FULL           | FET$                 |      1 |      1 |    39 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |      22 |      0 |       |       |          |
|* 32 |           TABLE ACCESS CLUSTER        | TS$                  |      0 |      1 |    21 |     0   (0)|          |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 33 |            INDEX UNIQUE SCAN          | I_TS#                |      0 |      1 |       |     0   (0)|          |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 34 |          INDEX UNIQUE SCAN            | I_FILE2              |      0 |      1 |     6 |     0   (0)|          |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|  35 |         NESTED LOOPS                  |                      |      1 |   2971 |   124K|     8  (75)| 00:00:01 |   3236 |00:00:00.03 |     642 |      0 |       |       |          |
|* 36 |          HASH JOIN                    |                      |      1 |   2971 |   107K|     7  (72)| 00:00:01 |   3236 |00:00:00.03 |     638 |      0 |  1421K|  1421K| 1135K (0)|
|* 37 |           TABLE ACCESS FULL           | TS$                  |      1 |     10 |   270 |     2   (0)| 00:00:01 |      9 |00:00:00.01 |      22 |      0 |       |       |          |
|  38 |           FIXED TABLE FULL            | X$KTFBFE             |      1 |   3238 | 32380 |     5 (100)| 00:00:01 |   3236 |00:00:00.02 |     616 |      0 |       |       |          |
|* 39 |          INDEX UNIQUE SCAN            | I_FILE2              |   3236 |      1 |     6 |     0   (0)|          |   3236 |00:00:00.01 |       4 |      0 |       |       |          |
|* 40 |         HASH JOIN                     |                      |      1 |  22394 |  2405K|    26  (66)| 00:00:01 |     34 |00:00:00.01 |    4035 |      2 |  2061K|  2061K| 1490K (0)|
|  41 |          INDEX FULL SCAN              | I_FILE2              |      1 |     42 |   252 |     1   (0)| 00:00:01 |     42 |00:00:00.01 |       1 |      0 |       |       |          |
|  42 |          NESTED LOOPS                 |                      |      1 |  45879 |  4659K|    23  (66)| 00:00:01 |     34 |00:00:00.01 |    4034 |      2 |       |       |          |
|  43 |           MERGE JOIN                  |                      |      1 |      2 |    78 |     9  (12)| 00:00:01 |      2 |00:00:00.01 |    4022 |      0 |       |       |          |
  |  44 |            TABLE ACCESS BY INDEX ROWID| RECYCLEBIN$          |      1 |      2 |    24 |     6   (0)| 00:00:01 |      2 |00:00:00.01 |    4000 |      0 |       |       |          |
                      |  45 |             INDEX FULL SCAN           | RECYCLEBIN$_TS       |      1 |      2 |       |     4   (0)| 00:00:01 |      2 |00:00:00.01 |    3998 |      0 |       |       |          |
|* 46 |            SORT JOIN                  |                      |      2 |     10 |   270 |     3  (34)| 00:00:01 |      2 |00:00:00.01 |      22 |      0 |  2048 |  2048 | 2048  (0)|
|* 47 |             TABLE ACCESS FULL         | TS$                  |      1 |     10 |   270 |     2   (0)| 00:00:01 |      9 |00:00:00.01 |      22 |      0 |       |       |          |
|* 48 |           FIXED TABLE FIXED INDEX     | X$KTFBUE (ind:1)     |      2 |  25000 |  1586K|     7 (100)| 00:00:01 |     34 |00:00:00.01 |      12 |      2 |       |       |          |
|  49 |         NESTED LOOPS                  |                      |      1 |      1 |    91 |     9   (0)| 00:00:01 |      0 |00:00:00.01 |      22 |      0 |       |       |          |
|  50 |          NESTED LOOPS                 |                      |      1 |      2 |    91 |     9   (0)| 00:00:01 |      0 |00:00:00.01 |      22 |      0 |       |       |          |
|  51 |           NESTED LOOPS                |                      |      1 |      1 |    79 |     4   (0)| 00:00:01 |      0 |00:00:00.01 |      22 |      0 |       |       |          |
|  52 |            NESTED LOOPS               |                      |      1 |      1 |    73 |     4   (0)| 00:00:01 |      0 |00:00:00.01 |      22 |      0 |       |       |          |
|* 53 |             TABLE ACCESS FULL         | TS$                  |      1 |      1 |    21 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |      22 |      0 |       |       |          |
|  54 |             TABLE ACCESS CLUSTER      | UET$                 |      0 |      1 |    52 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 55 |              INDEX RANGE SCAN         | I_FILE#_BLOCK#       |      0 |      1 |       |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 56 |            INDEX UNIQUE SCAN          | I_FILE2              |      0 |      1 |     6 |     0   (0)|          |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 57 |           INDEX RANGE SCAN            | RECYCLEBIN$_TS       |      0 |      2 |       |     3   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
                      |* 58 |          TABLE ACCESS BY INDEX ROWID  | RECYCLEBIN$          |      0 |      1 |    12 |     5   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|  59 |    HASH GROUP BY                      |                      |      1 |      1 |   155 |     6  (17)| 00:00:01 |      3 |00:00:00.02 |      52 |      0 |   809K|   809K|  748K (0)|
|  60 |     NESTED LOOPS OUTER                |                      |      1 |      1 |   155 |     5   (0)| 00:00:01 |      6 |00:00:00.02 |      52 |      0 |       |       |          |
|  61 |      NESTED LOOPS                     |                      |      1 |      1 |   127 |     4   (0)| 00:00:01 |      6 |00:00:00.02 |      43 |      0 |       |       |          |
|* 62 |       HASH JOIN                       |                      |      1 |      2 |   220 |     2   (0)| 00:00:01 |      6 |00:00:00.02 |      34 |      0 |  1483K|  1483K|  915K (0)|
|  63 |        NESTED LOOPS                   |                      |      1 |      5 |   355 |     0   (0)|          |      6 |00:00:00.02 |       6 |      0 |       |       |          |
|  64 |         NESTED LOOPS                  |                      |      1 |      5 |   285 |     0   (0)|          |      6 |00:00:00.02 |       0 |      0 |       |       |          |
|* 65 |          FIXED TABLE FULL             | X$KCCTF              |      1 |      6 |    72 |     0   (0)|          |      6 |00:00:00.01 |       0 |      0 |       |       |          |
|* 66 |          FIXED TABLE FIXED INDEX      | X$KCCFN (ind:1)      |      6 |      1 |    45 |     0   (0)|          |      6 |00:00:00.01 |       0 |      0 |       |       |          |
|* 67 |         FIXED TABLE FIXED INDEX       | X$KTFTHC (ind:1)     |      6 |      1 |    14 |     0   (0)|          |      6 |00:00:00.01 |       6 |      0 |       |       |          |
|  68 |        VIEW                           | V_$TEMP_SPACE_HEADER |      1 |      3 |   117 |     2   (0)| 00:00:01 |      6 |00:00:00.01 |      28 |      0 |       |       |          |
|  69 |         NESTED LOOPS                  |                      |      1 |      3 |   141 |     2   (0)| 00:00:01 |      6 |00:00:00.01 |      28 |      0 |       |       |          |
|* 70 |          TABLE ACCESS FULL            | TS$                  |      1 |      1 |    27 |     2   (0)| 00:00:01 |      2 |00:00:00.01 |      22 |      0 |       |       |          |
|* 71 |          FIXED TABLE FIXED INDEX      | X$KTFTHC (ind:2)     |      2 |      3 |    60 |     0   (0)|          |      6 |00:00:00.01 |       6 |      0 |       |       |          |
|* 72 |       TABLE ACCESS BY INDEX ROWID     | TS$                  |      6 |      1 |    17 |     1   (0)| 00:00:01 |      6 |00:00:00.01 |       9 |      0 |       |       |          |
|* 73 |        INDEX UNIQUE SCAN              | I_TS1                |      6 |      1 |       |     0   (0)|          |      6 |00:00:00.01 |       3 |      0 |       |       |          |
|* 74 |      VIEW PUSHED PREDICATE            | V_$TEMP_EXTENT_POOL  |      6 |      1 |    28 |     1   (0)| 00:00:01 |      5 |00:00:00.01 |       9 |      0 |       |       |          |
|  75 |       NESTED LOOPS                    |                      |      6 |      1 |    79 |     1   (0)| 00:00:01 |     25 |00:00:00.01 |       9 |      0 |       |       |          |
|* 76 |        TABLE ACCESS BY INDEX ROWID    | TS$                  |      6 |      1 |    27 |     1   (0)| 00:00:01 |      6 |00:00:00.01 |       9 |      0 |       |       |          |
|* 77 |         INDEX UNIQUE SCAN             | I_TS1                |      6 |      1 |       |     0   (0)|          |      6 |00:00:00.01 |       3 |      0 |       |       |          |
|* 78 |        FIXED TABLE FIXED INDEX        | X$KTSTFC (ind:1)     |      6 |      1 |    52 |     0   (0)|          |     25 |00:00:00.01 |       0 |      0 |       |       |          |
——————————————————————————————————————————————————————————————–

Here is the query

select /*+ monitor gather_plan_statistics */
a.tablespace_name,
round(a.bytes_alloc / 1024 / 1024, 2) megs_alloc,
round(nvl(b.bytes_free, 0) / 1024 / 1024, 2) megs_free,
round((a.bytes_alloc – nvl(b.bytes_free, 0)) / 1024 / 1024, 2) megs_used,
round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100,2) Pct_Free,
100 – round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100,2) Pct_used,
a.autoext
FROM
(
select f.tablespace_name,
sum(f.bytes) bytes_alloc,
max(f.autoextensible) autoext
from dba_data_files f
group by tablespace_name
) a,
(
select f.tablespace_name,
sum(f.bytes) bytes_free
from dba_free_space f
group by tablespace_name
) b
WHERE a.tablespace_name = b.tablespace_name (+)
UNION ALL
SELECT
h.tablespace_name,
round(sum(h.bytes_free + h.bytes_used) / 1048576, 2) megs_alloc,
round(sum((h.bytes_free + h.bytes_used) – nvl(p.bytes_used, 0)) / 1048576, 2) megs_free,
round(sum(nvl(p.bytes_used, 0))/ 1048576, 2) megs_used,
round((sum((h.bytes_free + h.bytes_used) – nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100,2) Pct_Free,
100 – round((sum((h.bytes_free + h.bytes_used) – nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100,2) pct_used,
max(f.autoextensible) autoext
FROM
sys.v_$TEMP_SPACE_HEADER h, sys.v_$Temp_extent_pool p, dba_temp_files f
WHERE
p.file_id(+) = h.file_id
AND
p.tablespace_name(+) = h.tablespace_name
AND
f.file_id = h.file_id
AND
f.tablespace_name = h.tablespace_name
GROUP BY
h.tablespace_name, f.maxbytes
ORDER BY 1

and check plan with command

select * from table(dbms_xplan.display_cursor);

To Index or Not to Index on Exadata System

I started reading Expert Oracle Exadata 2nd Edition yesterday,I read 1 st edition before. I can say if you like working with Exadata ,you should read this book.

When the topic comes to Exadata, one of the popular discussion is about  using index on Exadata. Some says (Sales forces 🙂 ) you don’t need any indexes on Exadata but I don’t agree with them.

Most of the databases  are in the real world system  have mixed workload,long running query  which is throughput  sensitive in DSS and latency-sensitive statement in OLTP , so how to deal with indexes ? DSS doesn’t like index even it is analytic function index (Thanks smart scan) but on the other hand OLTP system need indexes.

So you can create 2 services ,one is for DSS system named, for example DSSSRV the other one is for OLTP is OLTPSRV

But you don’t want DSS system to use index , so put your indexes in INVISIBLE mode (come with 11g) which you don’t need to use them in DSS connection ,and make it VISIBLE when your connections come from OLTP service.

ALTER INDEX  oltp_index1 INVISIBLE;

select index_name, visibility  from user_indexes 3 where index_name = ‘oltp_index1’;

Write little procedure and put it into LOGON trigger later.

CREATE procedure coming_service is
begin
if LOWER(sys_context(‘userenv’,’service:name’)) = ‘DSSRV’ then
execute immediate ‘Alter session set optimizer_use_invisible_indexes = FALSE’;
elsif  LOWER(sys_context(‘userenv’,’service:name’)) = ‘OLTPSRV’ then
execute immediate ‘Alter session set optimizer_use_invisible_indexes =TRUE’;
end if;
end;
/

Alter session set optimizer_use_invisible_indexes = TRUE

This command is session level command , so it is valid only your session.

P.S

The procedure was taken from Chapter 17 –Unlearning  Some Things We Thought We Knew