Management Server(MS) running on database nodes.

From exadata database machine administration guide:
“Starting with Oracle Exadata Database Machine 12.1.2.1.0
The database nodes now run the Management Server (MS). Previously MS ran only on the storage nodes.
The database nodes now run a new service called Database Machine Service (dbmsrv).
This new service is based on the MS that runs on the storage servers and provides enhanced management capabilities to the database nodes.”

[root@###exa03db01 ~]# dbmcli
DBMCLI: Release – Production on Mon Dec 14 13:25:01 CST 2015

Copyright (c) 2007, 2014, Oracle. All rights reserved.

DBMCLI> list dbserver detail
name: ###exa03db01
bbuStatus: normal
coreCount: 18/36
cpuCount: 36/36
diagHistoryDays: 7
fanCount: 16/16
fanStatus: normal
id: #########
interconnectCount: 2
interconnect1: ib0
interconnect2: ib1
ipaddress1: 192.168.10.1/22
ipaddress2: 192.168.10.2/22
kernelVersion: 2.6.39-400.250.4.el6uek.x86_64
locatorLEDStatus: off
makeModel: Oracle Corporation ORACLE SERVER X5-2
metricHistoryDays: 7
msVersion: OSS_12.1.2.1.2_LINUX.X64_150617.1
notificationMethod: snmp
notificationPolicy: critical,warning,clear
powerCount: 2/2
powerStatus: normal
releaseImageStatus: success
releaseVersion: 12.1.2.1.2.150617.1
releaseTrackingBug: 20748218
snmpSubscriber: host=10.240.6.21,port=162,community=public,type=ASR
status: online
temperatureReading: 22.0
temperatureStatus: normal
upTime: 32 days, 19:15
msStatus: running
rsStatus: running

DBMCLI>
DBMCLI> help

HELP [topic]
Available Topics:
ALTER
ALTER ALERTHISTORY
ALTER DBSERVER
ALTER IBPORT
ALTER THRESHOLD
CREATE
CREATE DBSERVER
CREATE THRESHOLD
DESCRIBE
DROP
DROP ALERTHISTORY
DROP DBSERVER
DROP THRESHOLD
LIST
LIST ALERTDEFINITION
LIST ALERTHISTORY
LIST DBSERVER
LIST IBPORT
LIST LUN
LIST METRICCURRENT
LIST METRICDEFINITION
LIST METRICHISTORY
LIST PHYSICALDISK
LIST THRESHOLD
SET
SPOOL
START

DBMCLI> list alerthistory
2_1 2015-09-09T07:04:25-05:00 critical “File system “/u01” is 89% full, which is above the 80% threshold. This alert will be cleared when file system “/u01″ becomes less than 75% full. Top three directories ordered by total space usage are as follows: /u01/app : 56.64G /u01/Software : 13.62G /u01/onecommand : 12.43G”

Like Exadata storage serves you can create file system threshold limit instead of crontab scripts.

DBMCLI> list metriccurrent
DS_BBU_TEMP ##exa03db01 26.0 C
DS_CPUT ##exa03db01 3.2 %
DS_CPUT_MS ##exa03db01 0.0 %
DS_FANS ##exa03db01 16
DS_FSUT / 42 %
DS_FSUT /boot 9 %
DS_FSUT /u01 34 %
DS_FSUT /u02 45 %
DS_FSUT /usr/openv 34 %
DS_MEMUT ##exa03db01 58 %
DS_MEMUT_MS ##exa03db01 0.1 %
DS_RUNQ ##exa03db01 1.4
DS_SWAP_IN_BY_SEC ##exa03db01 0.0 KB/sec
DS_SWAP_OUT_BY_SEC ##exa03db01 0.0 KB/sec
DS_SWAP_USAGE ##exa03db01 0 %
DS_TEMP ##exa03db01 22.0 C
DS_VIRTMEM_MS ##exa03db01 4,260 MB
N_HCA_MB_RCV_SEC ##exa03db01 0.351 MB/sec
N_HCA_MB_TRANS_SEC ##exa03db01 0.336 MB/sec
N_IB_MB_RCV_SEC HCA-1:1 0.158 MB/sec
N_IB_MB_RCV_SEC HCA-1:2 0.193 MB/sec
N_IB_MB_TRANS_SEC HCA-1:1 0.159 MB/sec
N_IB_MB_TRANS_SEC HCA-1:2 0.177 MB/sec
N_IB_UTIL_RCV HCA-1:1 0.0 %
N_IB_UTIL_RCV HCA-1:2 0.0 %
N_IB_UTIL_TRANS HCA-1:1 0.0 %
N_IB_UTIL_TRANS HCA-1:2 0.0 %
N_NIC_KB_RCV_SEC ##exa03db01 84.8 KB/sec
N_NIC_KB_TRANS_SEC ##exa03db01 3.5 KB/sec

DBMCLI> CREATE THRESHOLD DS_FSUT comparison=’>’,warning=75,critical=90
Threshold DS_FSUT successfully created

DBMCLI> alter THRESHOLD DS_FSUT comparison=”>”, warning=70
Threshold DS_FSUT successfully altered

DBMCLI> list threshold detail
name: DS_FSUT
comparison: >
critical: 90.0
warning: 70.0

DBMCLI> drop threshold DS_FSUT;
Threshold DS_FSUT successfully dropped

DBMCLI> list threshold detail

Advertisements
Posted in Oracle Database | Leave a comment

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

Posted in Oracle Database | Leave a comment

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

Posted in Oracle Database | Leave a comment

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);

Posted in Oracle Database | Leave a comment

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

Posted in Oracle Database | Leave a comment

ORA-4030 Error during SQL Loader –mmap(offset=207343616, len=8192) failed with errno=12 for the file oracle***

I got these error during sqlldr process.

In the trace file, I’m seeing lot of mmap error. This process using tons of map entries.

mmap(offset=207343616, len=8192) failed with errno=12 for the file oracle***1B
mmap(offset=207343616, len=8192) failed with errno=12 for the file oracle***1B
mmap(offset=207343616, len=8192) failed with errno=12 for the file oracle***1B
mmap(offset=207343616, len=8192) failed with errno=12 for the file oracle***1B
mmap(offset=207343616, len=8192) failed with errno=12 for the file oracle***1B
mmap(offset=207343616, len=8192) failed with errno=12 for the file oracle***1B
mmap(offset=207343616, len=8192) failed with errno=12 for the file oracle***1B
mmap(offset=207343616, len=8192) failed with errno=12 for the file oracle***1B
mmap(offset=207343616, len=8192) failed with errno=12 for the file oracle***1B

So the system was running out of map entries. Default number of map entries is 65536 (number of)
The default realfree allocator pagesize is 64KB.So you can use up to 4GB memory for process.

But increasing it to 262144 ,you can use up to 16GB memory.

more /proc/sys/vm/max_map_count
$ sysctl -w vm.max_map_count=262144

Second option on the database side, you can set parameter _realfree_heap_pagesize_hint=262144 and _use_realfree_heap=TRUE

Posted in ORA-4030 | Leave a comment

is Http service enabled on Web ILOM Url ?

What if your https:///iPages/i_login.asp doesn’t work ?
connnect ILOM IP with ssh connection,

set /SP/services/http servicestate=enabled
Set ’servicestate’ to ’enabled’

and then invoke your address again
https:///iPages/i_login.asp

Posted in exadata | Leave a comment