Thursday, March 10, 2016

Oracle TNS Listener Poison Attack

Oracle TNS Poison Attack vulnerability, which was published as CVE 2012-1675 with CVSS score of 7.5, allows a malicious hacker to take complete control of the database server without authentication.

As explained in the article Oracle TNS Listener Poison Attack that I wrote for Information Security Buzz, the fix for this vulnerability depends upon the Oracle version that you are running. 

The default listener configuration in 11.2.0.4 is vulnerable. You have to explicitly specify VALID_NODE_CHECKING_REGISTRATION_<listener_name> to LOCAL or ON or 1 in listener.ora to address this issue.

What makes this vulnerability still relevant is that there are many organizations still running 11.2.0.4 and they haven’t addressed this vulnerability. As explained in this article, the fix is quite easy.

Monday, December 21, 2015

Oracle, please bundle OPatch with PSUs/SPUs!

OPatch is an Oracle-supplied utility that is used by DBAs to apply patches (PSUs/SPUs/Bundles etc.) to Oracle installations. It is the only supported and documented way to apply Oracle PSUs and SPUs. Of course, it is recommended and required as well.

Before applying Oracle patches, we need to verify that the OPatch version recommended by Oracle in PSU/SPU README is already there on the server.

If it is not, then we will need to download the recommended version of OPatch. (By the way, we don’t attempt to use the non-recommended OPatch!) The fastest way to download OPatch that I know of is to get it directly from https://updates.oracle.com/download/6880880.html  URL. You still need to choose Oracle software version and platform. After downloading it to your PC, you will need to copy it to the server through VPN tunnel using copy or ftp utilities, rename the old OPatch directory under Oracle Home, move it there and then unzip it. Quite a few tasks! Of course, takes time!

So my questions, or request to Oracle is to bundle the OPatch with Oracle patches (PSUs/CPUS etc), eliminating all the additional steps mentioned above. OPatch will be moved to the server along with PSUs/SPUs. No need to move it separately. We can easily save 15 minutes or so of our time. Why not? It is the first step towards automation.


DBAs do spend considerable amount of time applying quarterly patches. The time saved could be used elsewhere to improve the health of the databases! Not possible now. Hopefully, in near future! 

Friday, March 13, 2015

Gold from the ASHes!

Oracle's ASH, or Active Session History, statistics are simply a gold mine. ASH's power stems from its capability to correlate performance issues to a specific database user or a SQL statement very quickly. Active Session History, as name implies, stores session states for each and every active database session. A session is considered active if it is on CPU or waiting for a non-idle event (or OS call). 

Since Oracle samples active sessions every second, each sample is an statistical approximation of one second. If an ASH record indicates that a session is on CPU, then you could say that the session has consumed 1 second of CPU. By the same token, if an ASH record indicates that a session is waiting for a non-idle event, then you could say that the session has waited for a second for that event. 

For example, if there are ASH 100 records showing various sessions on CPU for a 30-minute time interval, then you could say DB CPU Time is 100 seconds. During the same interval, if there are 200 ASH records showing various wait events(OS calls), then you could say that the database has waited for 200 seconds. In this case, DB Time would be 100 + 200 = 300 seconds. 

As we know, DB Time and DB CPU time can also be derived from an AWR report which naturally raises a question - do DB TIme and DB CPU Time statistics, reported by AWR report, match with those derived from the ASH? I would contend that it is not important to have a 100% match for ASH and AWR statistics. What is more important is that ASH statistics would help you identify a problem very quickly which AWR may not be able to do do. Anyway, let me first attempt to answer this question and then explain why it is futile to attempt to compare two sets of statistics. 

Back to the question, ASH and AWR statistics may or may not match. AWR statistics represent the difference between two snapshots while ASH statistics are samples of all active session collected every second. There may be sampling errors in ASH Data. 

ASH experts or statistician may be able to elaborate more on ASH sampling algorithm and answer this question intelligently. In my small world, I have observed ASH statistics to be off by 10 to 20%. Let me show you how to calculate differences in DB Time and DB CPU Time as reported by AWR and ASH.

  SELECT TO_CHAR (ASHTime, 'MM/DD/YY:hh24') ASHTime,
         ASHDBTime,
         ASHCPUTime,
         AWRDBTime,
         AWRCPUTime,
         ROUND ( (ASHDBTime - AWRDBTime) / ASHDBTime * 100) DBTimeDiff,
         ROUND ( (ASHCPUTime - AWRCPUTime) / ASHCPUTime * 100) CPUTimeDiff
   FROM (  SELECT TRUNC (SAMPLE_TIME, 'HH24') ASHTime,
                   ROUND (COUNT (*) / 60) ASHDBTime,
                   ROUND (SUM (DECODE (session_state, 'ON CPU', 1, 0)) / 60)
                      ASHCPUTime
             FROM v$active_session_history ash
             WHERE     sample_time >= TO_DATE ('&&s_begin_time', 'MM/DD/YYYY:HH24:MI:SS')
                   AND sample_time <= TO_DATE ('&&s_end_time', 'MM/DD/YYYY:HH24:MI:SS')
          GROUP BY TRUNC (SAMPLE_TIME, 'HH24')) ASH,
         (  SELECT TRUNC (BEGIN_INTERVAL_TIME, 'HH24') AWRTime,
                   ROUND (SUM (EE1.VALUE - EE2.VALUE) / 60000000) AWRCPUTime
             FROM DBA_HIST_SNAPSHOT SS,
                   DBA_HIST_SYS_TIME_MODEL EE1,
                   DBA_HIST_SYS_TIME_MODEL EE2
             WHERE     EE1.SNAP_ID = EE2.SNAP_ID + 1
                   AND EE1.INSTANCE_NUMBER = EE2.INSTANCE_NUMBER
                   AND EE1.STAT_NAME = EE2.STAT_NAME
                   AND EE1.SNAP_ID = SS.SNAP_ID
                   AND EE1.INSTANCE_NUMBER = SS.INSTANCE_NUMBER
                   AND SS.BEGIN_INTERVAL_TIME >=
                          TO_DATE ('&&s_begin_time', 'MM/DD/YYYY:HH24:MI:SS')
                   AND SS.BEGIN_INTERVAL_TIME <=
                          TO_DATE ('&&s_end_time', 'MM/DD/YYYY:HH24:MI:SS')
                   AND EE1.STAT_NAME IN ('DB CPU')
                   AND EE2.STAT_NAME IN ('DB CPU')
             GROUP BY TRUNC (BEGIN_INTERVAL_TIME, 'HH24'), EE1.STAT_NAME) AWRCPUTime,
         (  SELECT TRUNC (BEGIN_INTERVAL_TIME, 'HH24') AWRTime,
                   ROUND (SUM (EE1.VALUE - EE2.VALUE) / 60000000) AWRDBTime
             FROM DBA_HIST_SNAPSHOT SS,
                   DBA_HIST_SYS_TIME_MODEL EE1,
                   DBA_HIST_SYS_TIME_MODEL EE2
             WHERE     EE1.SNAP_ID = EE2.SNAP_ID + 1
                   AND EE1.INSTANCE_NUMBER = EE2.INSTANCE_NUMBER
                   AND EE1.STAT_NAME = EE2.STAT_NAME
                   AND EE1.SNAP_ID = SS.SNAP_ID
                   AND EE1.INSTANCE_NUMBER = SS.INSTANCE_NUMBER
                   AND SS.BEGIN_INTERVAL_TIME >=
                          TO_DATE ('&&s_begin_time', 'MM/DD/YYYY:HH24:MI:SS')
                   AND SS.BEGIN_INTERVAL_TIME <=
                          TO_DATE ('&&s_end_time', 'MM/DD/YYYY:HH24:MI:SS')
                   AND EE1.STAT_NAME IN ('DB time')
                   AND EE2.STAT_NAME IN ('DB time')
             GROUP BY TRUNC (BEGIN_INTERVAL_TIME, 'HH24'), EE1.STAT_NAME) AWRDBTime
   WHERE ASH.ASHTIME = AWRCPUTime.AWRTime 
   AND ASH.ASHTIME = AWRDBTime.AWRTime
   ORDER BY 1;

ASHTIME      ASHDBTIME ASHCPUTIME  AWRDBTIME AWRCPUTIME DBTIMEDIFF% CPUTIMEDIFF%
----------- ---------- ---------- ---------- ---------- ----------- ------------
03/12/15:22         51         48         44         42         14          13
03/12/15:23         51         49         45         43         12          12
03/13/15:00         25         22         20         18         20          18
03/13/15:01         50         48         45         42         10          13
03/13/15:02         47         45         41         40         13          11
03/13/15:03         57         55         51         50         11           9
03/13/15:04         46         45         41         40         11          11
03/13/15:05         44         42         39         37         11          12
03/13/15:06         43         41         37         36         14          12
03/13/15:07         43         42         37         36         14          14
03/13/15:08         42         41         38         36         10          12
03/13/15:09         42         41         38         36         10          12
03/13/15:10         43         41         38         37         12          10
03/13/15:11         44         42         38         36         14          14
03/13/15:12         43         41         38         37         12          10
03/13/15:13         43         42         38         36         12          14
03/13/15:14         46         42         39         37         15          12
03/13/15:15         43         42         37         36         14          14

As you could see from the above report, DB Time and DB CPU Time statistics diverge by 10 to 20%, but that's OK. I think you are more interested in knowing that there was a problem than knowing 100% accurate values in ASH stats. Let me explain you why. 

Since samples are taken every second, it may miss an event or activity which started and completed between the two consecutive samples. I don't think as a performance analyst, you are interested in an event or activity which took less than a second. Any wait events lasting more than a second will be captured. Any SQLs running more than a second will be captured. Any long running queries lasting more than a second will be captured. So ASH does capture any events or activities of significance. It may not capture events or activities that don't span two consecutive ASH samples, but I don't think it will impede your abilities to diagnose database issues.

As mentioned above, SQLs, which complete within a second between the two samples, are not captured, but if they are run frequently enough, then ASH sampling algorithm would capture it and you would know about it.

If we can predict presidential elections through polls which are based on statistical sampling algorithms, then ASH surely can tell you about performance issues that your database may be facing. ASH is a gold mine of diagnostic information. Just dig it, but beware. ASH comes with a cost - it requires diagnostic pack license.


Tuesday, March 3, 2015

Performance impact of sub-query factoring

Oracle introduced the WITH clause, or sub-query factoring clause, in Oracle 9.2. The WITH clause is processed as an inline view or resolved as a temporary table. The WITH clause improves the readability of the code for sure, but may not yield performance benefits in all cases as one might have thought. Each case should be specifically examined to assess performance benefits. I will discuss a SQL with has UNION.

Original SQL Statement without sub-query factoring

SELECT D1.ID1, D2.ID2
FROM MAP_HISTORY  MH, DEPT D1, DEPT D2
WHERE     UPD_TABLE = 'ROSTER'
       AND D1.ID = MH.NEW_VALUE
       AND D2.ID = MH.OLD_VALUE
UNION
SELECT D1.ID1, D1ID2
FROM MAP_HISTORY  MH, DEPT D1, DEPT D2
WHERE     UPD_TABLE = 'ROSTER'
       AND D1.ID = MH.NEW_VALUE
       AND D2.ID = MH.OLD_VALUE;

As you could see from the SQL above, the MAP_HISTORY  table is accessed in each UNION. The EXPLAIN PLAN of this statement shows that MAP_HISTORY  table was accessed twice. The cost of each index access was 13. The total cost for two accesses was 26

SQL Autotrace statistics show that this query accessed 3745 blocks, as shown below.

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       3745  consistent gets
          0  physical reads
          0  redo size
      18535  bytes sent via SQL*Net to client
       1057  bytes received via SQL*Net from client
         65  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        959  rows processed


Modified SQL with sub-query factoring

WITH MH as
  ( SELECT NEW_VALUE, OLD_VALUE
    FROM MAP_HISTORY
    WHERE UPD_TABLE = 'ROSTER' )
SELECT D1.ID1, D2ID2
  FROM  MH, DEPT D1, DEPT D2
 WHERE D1.ID = MH.NEW_VALUE
       AND D2.ID = MH.OLD_VALUE
UNION
SELECT D1.ID1, D1.ID2
  FROM MH, DEPT D1, DEPT D2
 WHERE D1.ID = MH.NEW_VALUE
       AND D2.ID = MH.OLD_VALUE;


The EXPLAIN PLAN shows that Oracle created a temporary table. The cost to create the temporary table was 13. The temporary table was accessed twice, each with a cost of 4. So the total cost for the new structure was 13 + 4 + 4 = 21 which is less than 26 in the original query, but the execution plan changed and overall cost increased to 739. (Note: cost may not translate into actual time taken by the query. This is a completely separate topic and discussion for some other time).

As could see from the AUTOTRACE statistics, the number of consistent gets are 3768, but also there are 10 physical reads. There are also physical writes to write out the temporary table.

Statistics
----------------------------------------------------------
         65  recursive calls
         20  db block gets
       3768  consistent gets
         10  physical reads
       1156  redo size
      18535  bytes sent via SQL*Net to client
       1057  bytes received via SQL*Net from client
         65  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        959  rows processed

What factors impact performance of sub-query factoring?
  • Change in Execution Plan which may be slower
  • Cost to write out data to the temporary tablespace 
  • Cost to read data from the temporary table
  • No indexes on temporary table and hence Oracle may choose HASH join in the SQL with the WITH clause
  • Frequency of temporary table access


Monday, June 23, 2014

How to specify SQL Hints in Oracle correctly


As we know, Oracle honors the hints as long as they are properly specified. The catch phrase here is "properly specified." If you don't specify the hints properly, Oracle simply ignores the hints without any errors or warnings which can be quite frustrating. We all have our favorite stories about hints not being honored by Oracle. We all have tried and tried so many permutations and combinations to make the hints work, but Oracle somehow stubbornly ignores our hints. But that's not Oracle's fault! You need to specify the hints properly. Otherwise, Oracle will simply ignore them. The following are few useful tips that would help you specify hints properly.

Useful Tips
  • Syntax - You must specify hints within /*+   <hint>   */
  • Case-insensitive - Hints are not case sensitive so you don't need to worry about upper or lower case.
  • Incorrect names - If names of the tables or indexes are not correct in the hints or have typos in them, Oracle will simply ignore the hints.
  • Availability of access path - If you specify a hint for an access path which is not available, then Oracle simply ignores the hint. For example, if you specify an index hint, but there are no indexes on the table, then Oracle will simply ignore the hint.
  • Join hints - Inner table and outer table must be specified properly. Otherwise hints may not work.

How to correctly specify Nested Loop Join Hint

If you want Oracle to use nested loop join method, then you need to specify the hint as /*+ leading(outer_table)  use_nl (inner_table) */. You may be fine with only use_nl  (inner_table) hint, but to leave nothing to doubt, you should specify leading (outer_table) hint as well. Let's take an example.

As shown below, the un-hinted execution plan uses hash join.

SELECT *
FROM T1, T2
WHERE T1.ID = T2.T1_ID                                                   

-----------------------------------                                                           
| Id  | Operation          | Name |                                                           
-----------------------------------                                                           
|   0 | SELECT STATEMENT   |      |                                                           
|   1 |  HASH JOIN         |      |                                                           
|   2 |   TABLE ACCESS FULL| T1   |                                                           
|   3 |   TABLE ACCESS FULL| T2   |                                                           
-----------------------------------                                                           

If you need nested loop join method on tables t1 and t2, then specify the hint as follows. Note that T1 is the inner table or row source and T2 is the outer table or row source.

SELECT /*+ leading(t2) use_nl(t1) */ *
FROM T1, T2
WHERE T1.ID = T2.T1_ID                                                                                      

-----------------------------------                                                           
| Id  | Operation          | Name |                                                           
-----------------------------------                                                           
|   0 | SELECT STATEMENT   |      |                                                           
|   1 |  NESTED LOOPS      |      |                                                           
|   2 |   TABLE ACCESS FULL| T2   |                                                           
|   3 |   TABLE ACCESS FULL| T1   |                                                           
-----------------------------------                                                           


If you need a nested loop join method with T2 as inner table and T1 as outer table, then specify hint as follows:

SELECT /*+ leading(t1) use_nl(t2) */ *
FROM T1, T2
WHERE T1.ID = T2.T1_ID                                                                                      

-----------------------------------                                                           
| Id  | Operation          | Name |                                                           
-----------------------------------                                                           
|   0 | SELECT STATEMENT   |      |                                                           
|   1 |  NESTED LOOPS      |      |                                                           
|   2 |   TABLE ACCESS FULL| T1   |                                                           
|   3 |   TABLE ACCESS FULL| T2   |                                                           
-----------------------------------                                                           


How to correctly specify Nested Loop (Semi) Join Hint - EXISTS Query

If you want to force nested loop (semi) join method, then you need to specify /*+ use_nl (inner_table) */ in the sub-query. Let's take an example. The below is un-hinted execution plan.

SELECT *
FROM T1
WHERE EXISTS ( SELECT 'x'                
               FROM T2                            
               WHERE T1.ID = T2.T1_ID )                                                           

-----------------------------------                                                           
| Id  | Operation          | Name |                                                           
-----------------------------------                                                           
|   0 | SELECT STATEMENT   |      |                                                           
|   1 |  HASH JOIN SEMI    |      |                                                           
|   2 |   TABLE ACCESS FULL| T1   |                                                           
|   3 |   TABLE ACCESS FULL| T2   |                                                           
-----------------------------------                                                           


To force nested loop (semi) join, please note how to specify leading and use_nl hints along with appropriate inner_table and outer_table.

SELECT /*+ leading(t1) */ *
FROM T1
WHERE EXISTS ( SELECT /*+ use_nl(t2) */ 'x'                
               FROM T2                
               WHERE T1.ID = T2.T1_ID )                                                                                    

-----------------------------------                                                           
| Id  | Operation          | Name |                                                           
-----------------------------------                                                           
|   0 | SELECT STATEMENT   |      |                                                           
|   1 |  NESTED LOOPS SEMI |      |                                                           
|   2 |   TABLE ACCESS FULL| T1   |                                                           
|   3 |   TABLE ACCESS FULL| T2   |                                                           
-----------------------------------                                                           


How to correctly specify Nested Loop (Semi) Join Hint - IN Queries

If you have a SQL with IN clause, as shown below, then use_nl hint must be specified within the inner query as shown below. Leading hint is specified in the outer query. The first example shows un-hinted statement while the second example shows hinted statement.

SELECT *
FROM t1
WHERE id IN ( SELECT t1_id FROM t2 )                           

-----------------------------------                                                           
| Id  | Operation          | Name |                                                           
-----------------------------------                                                           
|   0 | SELECT STATEMENT   |      |                                                           
|   1 |  HASH JOIN SEMI    |      |                                                           
|   2 |   TABLE ACCESS FULL| T1   |                                                           
|   3 |   TABLE ACCESS FULL| T2   |                                                           
-----------------------------------                                                           


SELECT /*+ leading(t1) */ * 
FROM t1
WHERE id IN ( SELECT /*+ use_nl(t2) */ t1_id              
              FROM t2 )                                                   

-----------------------------------                                                           
| Id  | Operation          | Name |                                                           
-----------------------------------                                                           
|   0 | SELECT STATEMENT   |      |                                                           
|   1 |  NESTED LOOPS SEMI |      |                                                           
|   2 |   TABLE ACCESS FULL| T1   |                                                           
|   3 |   TABLE ACCESS FULL| T2   |                                                           
-----------------------------------                                                           


How to correctly specify Nested Loop (anti) Join Hint  - NOT EXISTS Queries

The specifications for nested loop (anti) join hint are similar to nested loop (semi) join hint specifications. You must specify use_nl hint in the sub-query with inner table. The leading hint should be specified in the outer query. The first example shows un-hinted statement while the second example shows hinted statement.

SELECT  *
FROM T1
WHERE NOT EXISTS ( SELECT 'x'
                   FROM T2                    
                   WHERE T1.ID = T2.T1_ID )                                          

-----------------------------------                                                           
| Id  | Operation          | Name |                                                           
-----------------------------------                                                           
|   0 | SELECT STATEMENT   |      |                                                           
|   1 |  HASH JOIN ANTI    |      |                                                           
|   2 |   TABLE ACCESS FULL| T1   |                                                           
|   3 |   TABLE ACCESS FULL| T2   |                                                           
-----------------------------------                                                           


SELECT  /*+ leading(t1) */ *
FROM T1
WHERE NOT EXISTS ( SELECT /*+ use_nl(t2) */ 'x'                     
                   FROM T2                    
                   WHERE T1.ID = T2.T1_ID )                                                                            


-----------------------------------                                                           
| Id  | Operation          | Name |                                                           
-----------------------------------                                                           
|   0 | SELECT STATEMENT   |      |                                                           
|   1 |  NESTED LOOPS ANTI |      |                                                           
|   2 |   TABLE ACCESS FULL| T1   |                                                           
|   3 |   TABLE ACCESS FULL| T2   |                                                           
-----------------------------------