Extents of an Oracle Database DBA’s Knowledge Base

18 04 2012

April 18, 2012

I saw an interesting thread on the OTN forums this morning that forced me to stop and think about several items.  The thread contains a question posed during a recent job interview – one of the best questions that I have seen (dare I say, better than one I might have crafted myself).  Consider this: you are given a SQL statement, and a raw 10046 trace captured at level 12.  The question is very simple: Is the SQL statement efficient or not?

What is the point of the interview question?  Barely scratching the surface:

  • It tests how you will approach unfamiliar problems if you encounter the problem once hired – it may not be this exact question, but this may be similar to a real problem that was faced by the company’s DBAs. Do you panic and say that it cannot be solved, do you say that the job belongs to someone else, do you say that there is no point in looking at the raw trace file, or do you dive into the problem and think about what could happen if… (yes, there is something here, but I do not want to spoil the fun for other candidates that may face this exact problem).
  • It determines in part what types of material you have read to further enhance your knowledge of Oracle Database.
  • It determines whether or not you recognize the potential problems that may be associated with specific Oracle Database releases (unpatched and feeling ANSI?)
  • It determines whether or not you recognize differences in predicated cardinality and the actual number of rows returned, and how that may affect the performance outcome. Is it better to filter early or filter late, and does that apply to this situation?
  • Is there a DISTINCT possibility that the ROWNUM function may allow an arbitrary set of rows to be returned, possibly dependent on the value of the OPTIMIZER_FEATURES_ENABLE parameter (see *** item below)?
  • It determines whether or not you follow logic or magic when troubleshooting problems.

Reflecting on the question a bit, did the person in the interview have access to Google, TKPROF, the Oracle Database documentation, books with red covers, books with yellow covers, books with white covers, books with blue covers, etc.

*** A year ago in an OTN thread I made the following comment that applies to at least one of the above bullet point and also the answer to the question posed by the interviewer:

“Other than the “HASH GROUP BY” operation on the second line of the printed execution plan for 11.2.0.2, and the “SORT GROUP BY” operation on the second line of the printed execution plan for 10.1.0.3, the execution plans are identical. Keep in mind that 11.2.0.2, due to adaptive cursor sharing, is capable of changing the execution plan for future executions (there was a single parse call in this case, so adaptive cursor sharing likely did not take place). Also, keep in mind that 11.2.0.2 by default in a 10046 trace will output the row source operation execution plan after the first execution, rather than when the cursor was closed – this explains the difference in the ROWS column in the execution plan. If we look closely at the summary information, 11.2.0.2 performed 113,319 consistent gets in 673 executions, while 10.1.0.3 performed 175,168 consistent gets in 644 executions. Each execution in 11.2.0.2 is in theory more efficient than each execution in 10.1.0.3, yet the average time per execution is much longer.”

In your opinion, what do you think of the interview question?

(Please do not provide an exact answer to the interviewer’s question for at least 14 days, there is no sense in disrupting the interview process.)





Reproducing a Canned Report using a Single SQL Statement

11 04 2012

April 11, 2012

I recently received an interesting request for assistance from an ERP email mailing list.  The author of the email wanted to reproduce a canned report found in the ERP package so that the information could be published on a Microsoft Sharepoint system.  The author of the email is using SQL Server for the database backend, but we will ignore that bit of complexity for the moment.

The canned report that ships with the ERP system looks similar to the following:

Basically, the end user enters a start and an end date for the report, the ERP system checks the shop resource availability calendar for the days between the start and end dates, and then calculates the Capacity value from that calendar (there is a default shop resource calendar, and an override calendar for some of the shop resources).  The Act Hrs column in the report is simply the sum of hours calculated from the employee labor transactions that are recorded in real-time on the production floor – that column value is easy to calculate, but is subject to minor rounding errors and date calculation errors when an employee labor transaction starts before midnight and ends after midnight on either the start date or end date specified by the end user running the report.  The Est Hrs column in the report is a little more difficult to calculate, requiring a bit of calculation that determines what item the employee actually claimed to have produced, and in what quantity – what if the employee claimed to have produced 6 of 10 widgets in the specified time period, might he have completed 99% of the seventh widget but not yet reported that widget complete?  The EFF column is simply the Est Hours column divided by the Act Hrs column, with the result multiplied by 100.  The Act Util column is also easy to calculate: the Act Hrs column value divided by the Capacity column value, with the result multiplied by 100.  The Est Util column is simply the Est Hrs column value divided by the Capacity column value, with the result multiplied by 100.

So, where do we start in trying to reproduce this particular report?  How about enabling a 10046 trace for the session that creates the report.  Analyzing the resulting trace file might provide some insight into how the report is built.  Below is a summary of the SQL statements found in the trace file:

Query 1: (this query that retrieves the estimated hours, shop resource description, and various other information that does not appear on the report – this appears to be the query that provokes the ERP system to issue the queries that follow)

SELECT
  L.RESOURCE_ID,
  L.WORKORDER_TYPE,
  L.WORKORDER_BASE_ID, 
  L.WORKORDER_LOT_ID,
  L.WORKORDER_SPLIT_ID,
  L.WORKORDER_SUB_ID, 
  L.OPERATION_SEQ_NO,
  L.TRANSACTION_DATE,
  L.HOURS_WORKED,
  L.GOOD_QTY, 
  L.TYPE,
  L.EMPLOYEE_ID, 
  OP.SETUP_HRS,
  OP.RUN,
  OP.RUN_TYPE,
  OP.LOAD_SIZE_QTY,
  OP.CALC_END_QTY, 
  R.DESCRIPTION, 
  RTRIM(E.LAST_NAME),
  RTRIM(E.FIRST_NAME),
  E.MIDDLE_INITIAL, 
  W.PART_ID,
  P.DESCRIPTION,
  L.TRANSACTION_ID 
FROM
  LABOR_TICKET L,
  OPERATION OP,
  SHOP_RESOURCE R,
  EMPLOYEE E,
  WORK_ORDER W,
  PART P 
WHERE
  L.RESOURCE_ID = R.ID 
  AND L.EMPLOYEE_ID = E.ID 
  AND L.WORKORDER_TYPE = OP.WORKORDER_TYPE 
  AND L.WORKORDER_BASE_ID = OP.WORKORDER_BASE_ID 
  AND L.WORKORDER_LOT_ID = OP.WORKORDER_LOT_ID 
  AND L.WORKORDER_SPLIT_ID = OP.WORKORDER_SPLIT_ID 
  AND L.WORKORDER_SUB_ID = OP.WORKORDER_SUB_ID 
  AND L.OPERATION_SEQ_NO = OP.SEQUENCE_NO  
  AND W.TYPE = OP.WORKORDER_TYPE 
  AND W.BASE_ID = OP.WORKORDER_BASE_ID 
  AND W.LOT_ID = OP.WORKORDER_LOT_ID 
  AND W.SPLIT_ID = OP.WORKORDER_SPLIT_ID 
  AND W.SUB_ID = OP.WORKORDER_SUB_ID  
  AND W.PART_ID = P.ID (+)  
  AND TRUNC(L.TRANSACTION_DATE) BETWEEN :1  AND :2
ORDER BY
  L.RESOURCE_ID,
  L.WORKORDER_TYPE,
  L.WORKORDER_BASE_ID, 
  L.WORKORDER_LOT_ID,
  L.WORKORDER_SPLIT_ID,
  L.WORKORDER_SUB_ID,
  L.OPERATION_SEQ_NO;

Query 2: (this query returns the quantity of a specific resource that is available for each of three shifts… for instance, the number of employees with a specific skill)

SELECT
  SHIFT_1_CAPACITY,
  SHIFT_2_CAPACITY,
  SHIFT_3_CAPACITY 
FROM
  SHOP_RESOURCE 
WHERE
  ID = :1;

Query 3: (this query returns the number of hours of availability for a specific resource, for each day of the week; the default resource calendar has a NULL value for the RESOURCE_ID column, so the specified sort order will return the default resource calendar rows before the over-ride calendar for a specific shop resource)

SELECT 
  DAY_OF_WEEK,
  START_OF_DAY,
  SHIFT_1,
  SHIFT_2,
  SHIFT_3 
FROM
  CALENDAR_WEEK 
WHERE
  (SCHEDULE_ID IS NULL OR SCHEDULE_ID = 'STANDARD') 
  AND (RESOURCE_ID IS NULL OR RESOURCE_ID = :1 ) 
ORDER BY
  SCHEDULE_ID,
  RESOURCE_ID DESC,
  DAY_OF_WEEK;

Query 4: (this query returns the exceptions, planned days of unavailabilty, for the default resource calendar and the shop resource specific over-ride calendar.  I just noticed a logic error in this SQL statement – what about those scheduled exceptions that start before the user specified start date and/or those scheduled exceptions that end after the user specified end date, where some of those dates fall into the user specified date range?)

SELECT  
  START_DATE,
  END_DATE,
  START_OF_DAY,
  SHIFT_1,
  SHIFT_2,
  SHIFT_3, 
  SHIFT_1_CAPACITY,
  SHIFT_2_CAPACITY,
  SHIFT_3_CAPACITY 
FROM
  CALENDAR_CHANGE 
WHERE
  (SCHEDULE_ID IS NULL OR SCHEDULE_ID = 'STANDARD') 
  AND (RESOURCE_ID IS NULL OR RESOURCE_ID = :1 ) 
  AND START_DATE >= :2 AND END_DATE <= :3
ORDER BY
  SCHEDULE_ID,
  RESOURCE_ID,
  START_DATE;

Query 5: (this query returns the number of operation setup hours for a specific operation, time spent preparing to produce parts, reported on the shop floor – it would seem that Query 1 could be modified to return this information)

SELECT
  SUM(HOURS_WORKED) 
FROM
  LABOR_TICKET 
WHERE
  WORKORDER_TYPE = :1     
  AND WORKORDER_BASE_ID = :2
  AND WORKORDER_LOT_ID = :3
  AND WORKORDER_SPLIT_ID = :4
  AND WORKORDER_SUB_ID = :5
  AND OPERATION_SEQ_NO = :6
  AND TYPE = 'S';

Now that we have a general idea of what steps the ERP system is performing to reproduce the report, where do we start?  We start by telling the OP (the person with the SQL Server backend) that with an Oracle Database backend, we could produce this report using a single SQL statement (without any stored procedures) – that approach would certainly eliminate a lot of back and forth communication between the client computer and the database server, which is an important consideration if a high latency network connects the two devices.  For the sake of simplicity, I will set the above query 4 aside for now.  Since there is an index on the LABOR_TICKET.TRANSACTION_DATE column, the ERP system loses the benefit of that index by applying the TRUNC function to that column’s values – so we will need to fix that problem.

First, we need to know how many hours of capacity are available for each resource.  If a specific over-ride schedule (shop calendar) is not available for a shop resource, then the standard schedule is used.  To retrieve the schedules for the shop resources that do not have a defined unique schedule, we can create a Cartesian join between two tables with a NOT EXISTS clause to exclude those shop resources with an over-ride schedule:

SELECT
  SR.ID AS RESOURCE_ID, 
  CW.DAY_OF_WEEK,
  CW.SHIFT_1*SR.SHIFT_1_CAPACITY AS HOURS_1,
  CW.SHIFT_2*SR.SHIFT_2_CAPACITY AS HOURS_2,
  CW.SHIFT_3*SR.SHIFT_3_CAPACITY AS HOURS_3,
  'DEFAULT' CALENDAR_TYPE
FROM
  CALENDAR_WEEK CW,
  SHOP_RESOURCE SR
WHERE
  (CW.SCHEDULE_ID IS NULL OR CW.SCHEDULE_ID = 'STANDARD')
  AND CW.RESOURCE_ID IS NULL
  AND NOT EXISTS (
    SELECT
      C.RESOURCE_ID
    FROM
      CALENDAR_WEEK C
    WHERE
      SR.ID=C.RESOURCE_ID);

We also need to retrieve the shop resources with defined over-ride schedules:

SELECT
  SR.ID AS RESOURCE_ID, 
  CW.DAY_OF_WEEK,
  CW.SHIFT_1*SR.SHIFT_1_CAPACITY AS HOURS_1,
  CW.SHIFT_2*SR.SHIFT_2_CAPACITY AS HOURS_2,
  CW.SHIFT_3*SR.SHIFT_3_CAPACITY AS HOURS_3,
  'RESOURCE' CALENDAR_TYPE
FROM
  CALENDAR_WEEK CW,
  SHOP_RESOURCE SR
WHERE
  (CW.SCHEDULE_ID IS NULL OR CW.SCHEDULE_ID = 'STANDARD')
  AND CW.RESOURCE_ID=SR.ID;

Finally, we need to UNION ALL the above two queries:

SELECT
  SR.ID AS RESOURCE_ID, 
  CW.DAY_OF_WEEK,
  CW.SHIFT_1*SR.SHIFT_1_CAPACITY AS HOURS_1,
  CW.SHIFT_2*SR.SHIFT_2_CAPACITY AS HOURS_2,
  CW.SHIFT_3*SR.SHIFT_3_CAPACITY AS HOURS_3,
  'DEFAULT' CALENDAR_TYPE
FROM
  CALENDAR_WEEK CW,
  SHOP_RESOURCE SR
WHERE
  (CW.SCHEDULE_ID IS NULL OR CW.SCHEDULE_ID = 'STANDARD')
  AND CW.RESOURCE_ID IS NULL
  AND NOT EXISTS (
    SELECT
      C.RESOURCE_ID
    FROM
      CALENDAR_WEEK C
    WHERE
      SR.ID=C.RESOURCE_ID)
UNION ALL
SELECT
  SR.ID AS RESOURCE_ID, 
  CW.DAY_OF_WEEK,
  CW.SHIFT_1*SR.SHIFT_1_CAPACITY AS HOURS_1,
  CW.SHIFT_2*SR.SHIFT_2_CAPACITY AS HOURS_2,
  CW.SHIFT_3*SR.SHIFT_3_CAPACITY AS HOURS_3,
  'RESOURCE' CALENDAR_TYPE
FROM
  CALENDAR_WEEK CW,
  SHOP_RESOURCE SR
WHERE
  (CW.SCHEDULE_ID IS NULL OR CW.SCHEDULE_ID = 'STANDARD')
  AND CW.RESOURCE_ID=SR.ID
ORDER BY
  RESOURCE_ID,
  DAY_OF_WEEK;

The ERP system uses the following code numbers to represent each day of the week in the output produced by the above SQL statement:

0: Saturday
1: Sunday
2: Monday
3: Tuesday
4: Wednesday
5: Thursday
6: Friday

On Oracle Database, we are able to use the TO_DATE function to almost reproduce the above translation table, although NLS settings that are in effect could produce results that require further adjustment.  The following formula returns 1 for a Sunday and 6 for a Friday, but 7 for Saturday:

TO_DATE(TRANSACTION_DATE, 'D')

We are able to convert the 7 value for Saturdays into a 0 by using the MOD function to return the remainder value after dividing by 7:

MOD(TO_DATE(TRANSACTION_DATE, 'D'), 7)

Assume that we want to generate a report for the dates between March 26, 2012 and April 14, 2012.  We need a way to determine the number of Mondays, Tuesdays, Wednesdays, Thursdays, Fridays, Saturdays, and Sundays between the two dates (including the end-point dates).  There are a couple of approaches to this problem:

  1. Use the CONNECT BY LEVEL syntax to generate a row for each date between the start and end dates.
  2. Use a pre-created, statistically defined table that simply lists all of the dates between an arbitrary start and end date.
  3. Use a stored procedure to calculate the number of each day of the week between the start and end dates.
  4. Mathematically calculate within the SQL statement the number of each day of the week.

I will use the first of the above approaches… I suppose this approach would be a bit of a challenge with a SQL Server backend.

SELECT
  TO_DATE('03/26/2012','MM/DD/YYYY')+(LEVEL-1) MY_DATE,
  TO_CHAR(TO_DATE('03/26/2012','MM/DD/YYYY')+(LEVEL-1), 'D') AS D,
  MOD(TO_CHAR(TO_DATE('03/26/2012','MM/DD/YYYY')+(LEVEL-1), 'D'),7) DAY_OF_WEEK
FROM
  DUAL
CONNECT BY
  LEVEL <= (TO_DATE('04/14/2012','MM/DD/YYYY') - TO_DATE('03/26/2012','MM/DD/YYYY'))+1;

MY_DATE   D DAY_OF_WEEK
--------- - -----------
26-MAR-12 2           2
27-MAR-12 3           3
28-MAR-12 4           4
29-MAR-12 5           5
30-MAR-12 6           6
31-MAR-12 7           0
01-APR-12 1           1
02-APR-12 2           2
03-APR-12 3           3
04-APR-12 4           4
05-APR-12 5           5
06-APR-12 6           6
07-APR-12 7           0
08-APR-12 1           1
09-APR-12 2           2
10-APR-12 3           3
11-APR-12 4           4
12-APR-12 5           5
13-APR-12 6           6
14-APR-12 7           0

If we were to use the above in a production environment, we certainly would convert the literal (constant) date values to bind variables.  The DAY_OF_WEEK column values (assuming that the NLS settings result in Sunday being the first day of the week) are the important values in the above output.  If we also implemented query 4 used by the ERP system, then the MY_DATE column values would also need to be considered.  How many Mondays, Tuesdays, Wednesdays, etc. are between the two dates?:

SELECT
  MOD(TO_CHAR(TO_DATE('03/26/2012','MM/DD/YYYY')+(LEVEL-1), 'D'),7) DAY_OF_WEEK,
  COUNT(*) NUM_DAYS
FROM
  DUAL
CONNECT BY
  LEVEL <= (TO_DATE('04/14/2012','MM/DD/YYYY') - TO_DATE('03/26/2012','MM/DD/YYYY'))+1
GROUP BY
  MOD(TO_CHAR(TO_DATE('03/26/2012','MM/DD/YYYY')+(LEVEL-1), 'D'),7);

DAY_OF_WEEK   NUM_DAYS
----------- ----------
          0          3
          1          2
          2          3
          3          3
          4          3
          5          3
          6          3

As shown above, just 2 Sundays, but 3 of every other day of the week.

We now have two SQL statements (the one that indicates the available capacity per day of week, and the other that indicates the number of each day of the week in the user specified time interval) that need to be joined together:

SELECT
  C.RESOURCE_ID,
  C.DAY_OF_WEEK,
  (C.HOURS_1 + C.HOURS_2 + C.HOURS_3) * D.NUM_DAYS
FROM
  (SELECT
    SR.ID AS RESOURCE_ID, 
    CW.DAY_OF_WEEK,
    CW.SHIFT_1*SR.SHIFT_1_CAPACITY AS HOURS_1,
    CW.SHIFT_2*SR.SHIFT_2_CAPACITY AS HOURS_2,
    CW.SHIFT_3*SR.SHIFT_3_CAPACITY AS HOURS_3,
    'DEFAULT' CALENDAR_TYPE
  FROM
    CALENDAR_WEEK CW,
    SHOP_RESOURCE SR
  WHERE
    (CW.SCHEDULE_ID IS NULL OR CW.SCHEDULE_ID = 'STANDARD')
    AND CW.RESOURCE_ID IS NULL
    AND NOT EXISTS (
      SELECT
        C.RESOURCE_ID
      FROM
        CALENDAR_WEEK C
      WHERE
        SR.ID=C.RESOURCE_ID)
  UNION ALL
  SELECT
    SR.ID AS RESOURCE_ID, 
    CW.DAY_OF_WEEK,
    CW.SHIFT_1*SR.SHIFT_1_CAPACITY AS HOURS_1,
    CW.SHIFT_2*SR.SHIFT_2_CAPACITY AS HOURS_2,
    CW.SHIFT_3*SR.SHIFT_3_CAPACITY AS HOURS_3,
    'RESOURCE' CALENDAR_TYPE
  FROM
    CALENDAR_WEEK CW,
    SHOP_RESOURCE SR
  WHERE
    (CW.SCHEDULE_ID IS NULL OR CW.SCHEDULE_ID = 'STANDARD')
    AND CW.RESOURCE_ID=SR.ID) C,
  (SELECT
    MOD(TO_CHAR(TO_DATE('03/26/2012','MM/DD/YYYY')+(LEVEL-1), 'D'),7) DAY_OF_WEEK,
    COUNT(*) NUM_DAYS
  FROM
    DUAL
  CONNECT BY
    LEVEL <= (TO_DATE('04/14/2012','MM/DD/YYYY') - TO_DATE('03/26/2012','MM/DD/YYYY'))+1
  GROUP BY
    MOD(TO_CHAR(TO_DATE('03/26/2012','MM/DD/YYYY')+(LEVEL-1), 'D'),7) ) D
WHERE
  C.DAY_OF_WEEK=D.DAY_OF_WEEK;

Above is a somewhat large and complicated SQL statement, if you simply jumped into the middle of this article.

We still need to GROUP the result by the RESOURCE_ID column and add in the DESCRIPTION that is associated with each RESOURCE_ID:

SELECT
  C.RESOURCE_ID,
  C.DESCRIPTION,
  SUM((C.HOURS_1 + C.HOURS_2 + C.HOURS_3) * D.NUM_DAYS) CAPACITY
FROM
  (SELECT
    SR.ID AS RESOURCE_ID, 
    SR.DESCRIPTION,
    CW.DAY_OF_WEEK,
    CW.SHIFT_1*SR.SHIFT_1_CAPACITY AS HOURS_1,
    CW.SHIFT_2*SR.SHIFT_2_CAPACITY AS HOURS_2,
    CW.SHIFT_3*SR.SHIFT_3_CAPACITY AS HOURS_3,
    'DEFAULT' CALENDAR_TYPE
  FROM
    CALENDAR_WEEK CW,
    SHOP_RESOURCE SR
  WHERE
    (CW.SCHEDULE_ID IS NULL OR CW.SCHEDULE_ID = 'STANDARD')
    AND CW.RESOURCE_ID IS NULL
    AND NOT EXISTS (
      SELECT
        C.RESOURCE_ID
      FROM
        CALENDAR_WEEK C
      WHERE
        SR.ID=C.RESOURCE_ID)
  UNION ALL
  SELECT
    SR.ID AS RESOURCE_ID,
    SR.DESCRIPTION,
    CW.DAY_OF_WEEK,
    CW.SHIFT_1*SR.SHIFT_1_CAPACITY AS HOURS_1,
    CW.SHIFT_2*SR.SHIFT_2_CAPACITY AS HOURS_2,
    CW.SHIFT_3*SR.SHIFT_3_CAPACITY AS HOURS_3,
    'RESOURCE' CALENDAR_TYPE
  FROM
    CALENDAR_WEEK CW,
    SHOP_RESOURCE SR
  WHERE
    (CW.SCHEDULE_ID IS NULL OR CW.SCHEDULE_ID = 'STANDARD')
    AND CW.RESOURCE_ID=SR.ID) C,
  (SELECT
    MOD(TO_CHAR(TO_DATE('03/26/2012','MM/DD/YYYY')+(LEVEL-1), 'D'),7) DAY_OF_WEEK,
    COUNT(*) NUM_DAYS
  FROM
    DUAL
  CONNECT BY
    LEVEL <= (TO_DATE('04/14/2012','MM/DD/YYYY') - TO_DATE('03/26/2012','MM/DD/YYYY'))+1
  GROUP BY
    MOD(TO_CHAR(TO_DATE('03/26/2012','MM/DD/YYYY')+(LEVEL-1), 'D'),7) ) D
WHERE
  C.DAY_OF_WEEK=D.DAY_OF_WEEK
GROUP BY
  C.RESOURCE_ID,
  C.DESCRIPTION
ORDER BY
  RESOURCE_ID;

The above SQL statement results in the following output – we now have the first column in the ERP system’s canned report:

At this point, my claim of being able to reproduce this canned report in a single SQL statement might seem suspect – all of the above work for just a single column in the report.  It’s easy, just build the report in separate units, and then join the separate units.

As I mentioned earlier, calculating the estimated average hours and average actual hours could be a little difficult.  We might start with something like this:

SELECT
  LT.RESOURCE_ID,
  O.RUN_HRS AS EST_HOURS,
  O.CALC_END_QTY AS OPERATION_QTY,
  ROUND(O.RUN_HRS/O.CALC_END_QTY,2) AS AVG_EST_HOURS,
  SUM(LT.HOURS_WORKED) AS ACT_HOURS,
  SUM(LT.GOOD_QTY) AS COMPLETED_QTY,
  ROUND(DECODE(SUM(LT.GOOD_QTY),0,SUM(LT.HOURS_WORKED),SUM(LT.HOURS_WORKED)/SUM(LT.GOOD_QTY)),2) AVG_ACT_HOURS
FROM
  LABOR_TICKET LT,
  OPERATION O
WHERE
  LT.WORKORDER_TYPE='W'
  AND LT.TYPE='R'
  AND LT.WORKORDER_TYPE=O.WORKORDER_TYPE
  AND LT.WORKORDER_BASE_ID=O.WORKORDER_BASE_ID
  AND LT.WORKORDER_LOT_ID=O.WORKORDER_LOT_ID
  AND LT.WORKORDER_SPLIT_ID=O.WORKORDER_SPLIT_ID
  AND LT.WORKORDER_SUB_ID=O.WORKORDER_SUB_ID
  AND LT.OPERATION_SEQ_NO=O.SEQUENCE_NO
  AND LT.SHIFT_DATE BETWEEN TO_DATE('03/26/2012','MM/DD/YYYY') AND TO_DATE('04/14/2012','MM/DD/YYYY')
GROUP BY
  LT.RESOURCE_ID,
  O.RUN_HRS,
  O.CALC_END_QTY
ORDER BY
  LT.RESOURCE_ID;

The output of the above SQL statement looks similar to the following:

Nice, if we were just interested in summarizing by the individual operations.  However, the above will not work as a starting point for the next two columns in the report because we need to summarized by the RESOURCE_ID column – if we group on that column, we will throw off the accuracy of the efficiency calculations in the report.  So, we need to take a step back and try again.

First, we will summarize the data from the LABOR_TICKET table by itself – notice that I am not using the TRUNC function around the date column, and I am using the SHIFT_DATE column rather than the TRANSACTION_DATE column (the SHIFT_DATE column in this table is better able to work around the issue with labor transactions that cross midnight, and the time portion of the date values are already truncated to midnight):

SELECT
  LT.WORKORDER_TYPE,
  LT.WORKORDER_BASE_ID,
  LT.WORKORDER_LOT_ID,
  LT.WORKORDER_SPLIT_ID,
  LT.WORKORDER_SUB_ID,
  LT.OPERATION_SEQ_NO,
  LT.RESOURCE_ID,
  SUM(LT.HOURS_WORKED) AS ACT_HOURS,
  SUM(LT.GOOD_QTY) AS COMPLETED_QTY
FROM
  LABOR_TICKET LT
WHERE
  LT.SHIFT_DATE BETWEEN TO_DATE('03/26/2012','MM/DD/YYYY') AND TO_DATE('04/14/2012','MM/DD/YYYY')
  AND LT.TYPE='R'
  AND LT.WORKORDER_TYPE='W'
GROUP BY
  LT.WORKORDER_TYPE,
  LT.WORKORDER_BASE_ID,
  LT.WORKORDER_LOT_ID,
  LT.WORKORDER_SPLIT_ID,
  LT.WORKORDER_SUB_ID,
  LT.OPERATION_SEQ_NO,
  LT.RESOURCE_ID
HAVING
  SUM(LT.HOURS_WORKED)>0;

Now that we have pre-summarized the results from the LABOR_TICKET table, we can slide the above SQL statement into an inline view and join that inline view with the OPERATION table:

SELECT
  LT.RESOURCE_ID,
  SUM(ROUND(O.RUN_HRS/O.CALC_END_QTY*LT.COMPLETED_QTY,2)) AS ENG_HOURS,
  SUM(LT.ACT_HOURS) AS ACT_HOURS,
  ROUND(SUM(ROUND(O.RUN_HRS/O.CALC_END_QTY*LT.COMPLETED_QTY,2))/SUM(LT.ACT_HOURS)*100,2) AS EFF
FROM
  (SELECT
    LT.WORKORDER_TYPE,
    LT.WORKORDER_BASE_ID,
    LT.WORKORDER_LOT_ID,
    LT.WORKORDER_SPLIT_ID,
    LT.WORKORDER_SUB_ID,
    LT.OPERATION_SEQ_NO,
    LT.RESOURCE_ID,
    SUM(LT.HOURS_WORKED) AS ACT_HOURS,
    SUM(LT.GOOD_QTY) AS COMPLETED_QTY
  FROM
    LABOR_TICKET LT
  WHERE
    LT.SHIFT_DATE BETWEEN TO_DATE('03/26/2012','MM/DD/YYYY') AND TO_DATE('04/14/2012','MM/DD/YYYY')
    AND LT.TYPE='R'
    AND LT.WORKORDER_TYPE='W'
  GROUP BY
    LT.WORKORDER_TYPE,
    LT.WORKORDER_BASE_ID,
    LT.WORKORDER_LOT_ID,
    LT.WORKORDER_SPLIT_ID,
    LT.WORKORDER_SUB_ID,
    LT.OPERATION_SEQ_NO,
    LT.RESOURCE_ID
  HAVING
    SUM(LT.HOURS_WORKED)>0) LT,
  OPERATION O
WHERE
  LT.WORKORDER_TYPE=O.WORKORDER_TYPE
  AND LT.WORKORDER_BASE_ID=O.WORKORDER_BASE_ID
  AND LT.WORKORDER_LOT_ID=O.WORKORDER_LOT_ID
  AND LT.WORKORDER_SPLIT_ID=O.WORKORDER_SPLIT_ID
  AND LT.WORKORDER_SUB_ID=O.WORKORDER_SUB_ID
  AND LT.OPERATION_SEQ_NO=O.SEQUENCE_NO
GROUP BY
  LT.RESOURCE_ID
ORDER BY
  LT.RESOURCE_ID;

The result of the above SQL statement is much more useful than the original attempt.  Note that the above SQL statement makes certain assumptions about the estimated (ENG_HOURS) that the ERP system does not make, so the resulting numbers could be slightly different.

We now have three more columns of our report.

The final step is to put the complete SQL statement together, joining the previously created portion of the SQL statement that determined the available capacity with the just created SQL statement that determined the actual and estimated utilization.  The resulting SQL statement appears as follows:

SELECT
  C.RESOURCE_ID,
  C.DESCRIPTION,
  C.CAPACITY,
  E.ACT_HOURS,
  E.ENG_HOURS AS EST_HOURS,
  E.EFF,
  DECODE(C.CAPACITY,0,0,ROUND(E.ACT_HOURS/C.CAPACITY*100,2)) AS ACT_UTIL,
  DECODE(C.CAPACITY,0,0,ROUND(E.ENG_HOURS/C.CAPACITY*100,2)) AS EST_UTIL
FROM
  (SELECT
    C.RESOURCE_ID,
    C.DESCRIPTION,
    SUM((C.HOURS_1 + C.HOURS_2 + C.HOURS_3) * D.NUM_DAYS) CAPACITY
  FROM
    (SELECT
      SR.ID AS RESOURCE_ID, 
      SR.DESCRIPTION,
      CW.DAY_OF_WEEK,
      CW.SHIFT_1*SR.SHIFT_1_CAPACITY AS HOURS_1,
      CW.SHIFT_2*SR.SHIFT_2_CAPACITY AS HOURS_2,
      CW.SHIFT_3*SR.SHIFT_3_CAPACITY AS HOURS_3,
      'DEFAULT' CALENDAR_TYPE
    FROM
      CALENDAR_WEEK CW,
      SHOP_RESOURCE SR
    WHERE
      (CW.SCHEDULE_ID IS NULL OR CW.SCHEDULE_ID = 'STANDARD')
      AND CW.RESOURCE_ID IS NULL
      AND NOT EXISTS (
        SELECT
          C.RESOURCE_ID
        FROM
          CALENDAR_WEEK C
        WHERE
          SR.ID=C.RESOURCE_ID)
    UNION ALL
    SELECT
      SR.ID AS RESOURCE_ID,
      SR.DESCRIPTION,
      CW.DAY_OF_WEEK,
      CW.SHIFT_1*SR.SHIFT_1_CAPACITY AS HOURS_1,
      CW.SHIFT_2*SR.SHIFT_2_CAPACITY AS HOURS_2,
      CW.SHIFT_3*SR.SHIFT_3_CAPACITY AS HOURS_3,
      'RESOURCE' CALENDAR_TYPE
    FROM
      CALENDAR_WEEK CW,
      SHOP_RESOURCE SR
    WHERE
      (CW.SCHEDULE_ID IS NULL OR CW.SCHEDULE_ID = 'STANDARD')
      AND CW.RESOURCE_ID=SR.ID) C,
    (SELECT
      MOD(TO_CHAR(TO_DATE('03/26/2012','MM/DD/YYYY')+(LEVEL-1), 'D'),7) DAY_OF_WEEK,
      COUNT(*) NUM_DAYS
    FROM
      DUAL
    CONNECT BY
      LEVEL <= (TO_DATE('04/14/2012','MM/DD/YYYY') - TO_DATE('03/26/2012','MM/DD/YYYY'))+1
    GROUP BY
      MOD(TO_CHAR(TO_DATE('03/26/2012','MM/DD/YYYY')+(LEVEL-1), 'D'),7) ) D
  WHERE
    C.DAY_OF_WEEK=D.DAY_OF_WEEK
  GROUP BY
    C.RESOURCE_ID,
    C.DESCRIPTION) C,
  (SELECT
    LT.RESOURCE_ID,
    SUM(ROUND(O.RUN_HRS/O.CALC_END_QTY*LT.COMPLETED_QTY,2)) AS ENG_HOURS,
    SUM(LT.ACT_HOURS) AS ACT_HOURS,
    ROUND(SUM(ROUND(O.RUN_HRS/O.CALC_END_QTY*LT.COMPLETED_QTY,2))/SUM(LT.ACT_HOURS)*100,2) AS EFF
  FROM
    (SELECT
      LT.WORKORDER_TYPE,
      LT.WORKORDER_BASE_ID,
      LT.WORKORDER_LOT_ID,
      LT.WORKORDER_SPLIT_ID,
      LT.WORKORDER_SUB_ID,
      LT.OPERATION_SEQ_NO,
      LT.RESOURCE_ID,
      SUM(LT.HOURS_WORKED) AS ACT_HOURS,
      SUM(LT.GOOD_QTY) AS COMPLETED_QTY
    FROM
      LABOR_TICKET LT
    WHERE
      LT.SHIFT_DATE BETWEEN TO_DATE('03/26/2012','MM/DD/YYYY') AND TO_DATE('04/14/2012','MM/DD/YYYY')
      AND LT.TYPE='R'
      AND LT.WORKORDER_TYPE='W'
    GROUP BY
      LT.WORKORDER_TYPE,
      LT.WORKORDER_BASE_ID,
      LT.WORKORDER_LOT_ID,
      LT.WORKORDER_SPLIT_ID,
      LT.WORKORDER_SUB_ID,
      LT.OPERATION_SEQ_NO,
      LT.RESOURCE_ID
    HAVING
      SUM(LT.HOURS_WORKED)>0) LT,
    OPERATION O
  WHERE
    LT.WORKORDER_TYPE=O.WORKORDER_TYPE
    AND LT.WORKORDER_BASE_ID=O.WORKORDER_BASE_ID
    AND LT.WORKORDER_LOT_ID=O.WORKORDER_LOT_ID
    AND LT.WORKORDER_SPLIT_ID=O.WORKORDER_SPLIT_ID
    AND LT.WORKORDER_SUB_ID=O.WORKORDER_SUB_ID
    AND LT.OPERATION_SEQ_NO=O.SEQUENCE_NO
  GROUP BY
    LT.RESOURCE_ID) E
WHERE
  E.RESOURCE_ID=C.RESOURCE_ID
ORDER BY
  C.RESOURCE_ID;

The resulting output appears as follows:

Well, that was easy… :-) 

The solution reminds me of the phrase “How to Eat an Elephant“.  Elephant poaching is illegal in most parts of the world, so I propose changing this phrase to “How to Walk an Elephant”.  One step at a time, and make certain not to stand directly in front or directly behind.  ;-)





Failed Logon Attempts

4 04 2012

April 4, 2012

A fair number of articles on this blog are Oracle performance related in one respect or another.  I started thinking about how to measure how much more efficient something is when compared to something that is never performed.  Consider a situation where you had a database user account that is a member of the DEFAULT Oracle Database profile that is configured as follows:

ALTER PROFILE DEFAULT LIMIT
  PASSWORD_LIFE_TIME UNLIMITED
  FAILED_LOGIN_ATTEMPTS 5
  PASSWORD_LOCK_TIME 1
  PASSWORD_VERIFY_FUNCTION NULL; 

With the above configuration, passwords do not expire after a specified number of days, there are no password complexity requirements, and after five failed logon attempts, the account is automatically locked for one day.  The above configuration is not ideal from a security standpoint, but that is not the point of this blog article.

Assume that the one database user account is shared by multiple people (or multiple utility programs with an embedded username and password).  Suddenly, you find that your efficient utility program becomes inefficient… to the point that the “utility” portion of the program never has an opportunity to execute.  Well, that was unexpected, the database user account is locked out.  How did that happen?

The following SQL statement is probably quite simplistic for many of the readers of this blog, and probably should have been included in my Neat Tricks article that I published a couple of years ago, but I thought that I would include the SQL statement here to save myself a couple of minutes of typing the next time I need to figure out who-done-it:

SELECT
  TO_CHAR(TIMESTAMP,'MM/DD HH24:MI') TIMESTAMP,
  SUBSTR(OS_USERNAME,1,20) OS_USERNAME,
  SUBSTR(USERNAME,1,20) USERNAME,
  SUBSTR(TERMINAL,1,20) TERMINAL,
  ACTION_NAME,
  RETURNCODE
FROM
  SYS.DBA_AUDIT_SESSION
WHERE
  USERNAME LIKE 'MYUSER%'
  AND TIMESTAMP BETWEEN SYSDATE-1 AND SYSDATE
ORDER BY
  TIMESTAMP DESC;

TIMESTAMP   OS_USERNAME          USERNAME TERMINAL        ACTION_NAME          RETURNCODE
----------- -------------------- -------- --------------- -------------------- ----------
04/03 11:33 USER1                MYUSER   CUSER1          LOGON                         0
04/03 11:33 USER1                MYUSER   CUSER1          LOGOFF                        0
04/03 11:33 USER1                MYUSER   CUSER1          LOGOFF                        0
04/03 11:33 USER1                MYUSER   CUSER1          LOGON                         0
04/03 10:54 USER2                MYUSER   CUSER2          LOGOFF                        0
04/03 10:53 USER2                MYUSER   CUSER2          LOGON                         0
04/03 10:53 USER2                MYUSER   CUSER2          LOGON                         0
04/03 10:53 USER2                MYUSER   CUSER2          LOGON                         0
04/03 09:58 USER3                MYUSER   CUSER3          LOGOFF                        0
04/03 09:56 USER4                MYUSER   CUSER4          LOGON                         0
04/03 09:56 USER4                MYUSER   CUSER4          LOGOFF                        0
04/03 09:51 USER5                MYUSER   CUSER5          LOGON                         0
04/03 09:51 USER5                MYUSER   CUSER5          LOGON                         0
04/03 09:51 USER3                MYUSER   CUSER3          LOGON                         0
04/03 09:51 USER5                MYUSER   CUSER5          LOGOFF                        0
04/03 09:51 USER5                MYUSER   CUSER5          LOGOFF                        0
04/03 09:44 USER4                MYUSER   CUSER4          LOGOFF                        0
04/03 09:29 USER3                MYUSER   CUSER3          LOGOFF                        0
04/03 09:10 USER4                MYUSER   CUSER4          LOGON                         0
04/03 09:09 USER3                MYUSER   CUSER3          LOGON                         0
04/03 09:06 USER3                MYUSER   CUSER3          LOGOFF                        0
04/03 09:06 USER3                MYUSER   CUSER3          LOGOFF                        0
04/03 08:47 USER3                MYUSER   CUSER3          LOGON                         0
04/03 07:50 USER4                MYUSER   CUSER4          LOGON                         0
04/03 07:50 USER4                MYUSER   CUSER4          LOGOFF                        0
04/03 07:50 USER4                MYUSER   CUSER4          LOGOFF BY CLEANUP             0
04/03 07:50 USER4                MYUSER   CUSER4          LOGOFF BY CLEANUP             0
04/03 07:50 USER4                MYUSER   CUSER4          LOGOFF BY CLEANUP             0
04/03 07:50 USER4                MYUSER   CUSER4          LOGON                         0
04/03 07:50 USER4                MYUSER   CUSER4          LOGON                         0
04/03 07:50 USER4                MYUSER   CUSER4          LOGON                         0
04/03 07:12 USER3                MYUSER   CUSER3          LOGON                         0
04/03 07:12 NETWORK SERVICE      MYUSER   SERVER          LOGOFF                        0
04/03 07:11 NETWORK SERVICE      MYUSER   SERVER          LOGON                         0
04/03 07:11 IUSR_WEBSERV         MYUSER   SERVER          LOGON                         0
04/03 06:35 NETWORK SERVICE      MYUSER   SERVER          LOGOFF                        0
04/03 06:35 IUSR_WEBSERV         MYUSER   SERVER          LOGOFF                        0
04/03 06:35 NETWORK SERVICE      MYUSER   SERVER          LOGOFF                        0
04/03 06:34 IUSR_WEBSERV         MYUSER   SERVER          LOGON                         0
04/03 06:34 NETWORK SERVICE      MYUSER   SERVER          LOGON                         0
04/03 06:34 NETWORK SERVICE      MYUSER   SERVER          LOGON                         0
04/03 06:29 USER3                MYUSER   CUSER3          LOGOFF                        0
04/03 06:29 USER3                MYUSER   CUSER3          LOGON                         0
04/03 06:28 NETWORK SERVICE      MYUSER   SERVER          LOGOFF                        0
04/03 06:28 NETWORK SERVICE      MYUSER   SERVER          LOGOFF                        0
04/03 06:27 IUSR_WEBSERV         MYUSER   SERVER          LOGOFF                        0
04/03 06:27 NETWORK SERVICE      MYUSER   SERVER          LOGON                         0
04/03 06:26 IUSR_WEBSERV         MYUSER   SERVER          LOGON                         0
04/03 06:26 USER3                MYUSER   CUSER3          LOGOFF                        0
04/03 06:26 USER3                MYUSER   CUSER3          LOGON                         0
04/03 06:26 NETWORK SERVICE      MYUSER   SERVER          LOGON                         0
04/03 06:23 USER3                MYUSER   CUSER3          LOGON                     28000
04/03 06:23 USER3                MYUSER   CUSER3          LOGON                     28000
04/03 06:22 NETWORK SERVICE      MYUSER   SERVER          LOGON                     28000
04/03 06:22 NETWORK SERVICE      MYUSER   SERVER          LOGON                     28000
04/03 02:30 USER5                MYUSER   SERVER2         LOGON                     28000
04/02 19:53 USER1                MYUSER   CUSER1-LT       LOGON                     28000
04/02 19:53 USER1                MYUSER   CUSER1-LT       LOGON                     28000
04/02 19:11 USER1                MYUSER   CUSER1-LT       LOGON                     28000
04/02 19:11 USER1                MYUSER   CUSER1-LT       LOGON                     28000
04/02 18:37 USER1                MYUSER   CUSER1-LT       LOGON                     28000
04/02 18:37 USER1                MYUSER   CUSER1-LT       LOGON                     28000
04/02 18:37 USER1                MYUSER   CUSER1-LT       LOGON                      1017
04/02 18:37 USER1                MYUSER   CUSER1-LT       LOGON                     28000
04/02 18:32 USER1                MYUSER   CUSER1-LT       LOGON                      1017
04/02 18:32 USER1                MYUSER   CUSER1-LT       LOGON                      1017
04/02 18:31 USER1                MYUSER   CUSER1-LT       LOGON                      1017
04/02 18:31 USER1                MYUSER   CUSER1-LT       LOGON                      1017
04/02 17:08 USER3                MYUSER   CUSER3          LOGOFF                        0
04/02 17:03 USER3                MYUSER   CUSER3          LOGOFF                        0
04/02 17:03 USER3                MYUSER   CUSER3          LOGON                         0
04/02 16:55 USER3                MYUSER   CUSER3          LOGOFF                        0
04/02 16:55 USER3                MYUSER   CUSER3          LOGON                         0
04/02 16:54 USER3                MYUSER   CUSER3          LOGON                         0
04/02 16:52 USER3                MYUSER   CUSER3          LOGOFF                        0
04/02 16:45 NETWORK SERVICE      MYUSER   SERVER          LOGOFF                        0
04/02 16:45 NETWORK SERVICE      MYUSER   SERVER          LOGOFF                        0
04/02 16:45 IUSR_WEBSERV         MYUSER   SERVER          LOGOFF                        0
04/02 16:44 NETWORK SERVICE      MYUSER   SERVER          LOGON                         0
04/02 16:44 NETWORK SERVICE      MYUSER   SERVER          LOGON                         0
04/02 16:44 IUSR_WEBSERV         MYUSER   SERVER          LOGON                         0
04/02 16:43 USER3                MYUSER   CUSER3          LOGOFF                        0
04/02 16:42 USER3                MYUSER   CUSER3          LOGON                         0
04/02 16:42 USER3                MYUSER   CUSER3          LOGON                         0
04/02 16:32 USER6                MYUSER   CUSER6          LOGON                         0
04/02 16:32 USER6                MYUSER   CUSER6          LOGON                         0
04/02 16:32 USER6                MYUSER   CUSER6          LOGOFF                        0
04/02 16:32 USER6                MYUSER   CUSER6          LOGOFF                        0
04/02 16:21 USER3                MYUSER   CUSER3          LOGON                         0
04/02 16:20 USER3                MYUSER   CUSER3          LOGON                         0
04/02 16:20 USER3                MYUSER   CUSER3          LOGOFF                        0
04/02 16:19 USER3                MYUSER   CUSER3          LOGOFF                        0
04/02 16:15 USER3                MYUSER   CUSER3          LOGON                         0
04/02 15:40 USER3                MYUSER   CUSER3          LOGON                         0
04/02 15:40 USER3                MYUSER   CUSER3          LOGOFF                        0
04/02 15:31 USER3                MYUSER   CUSER3          LOGOFF                        0
04/02 15:06 USER1                MYUSER   USER1           LOGON                         0
04/02 15:06 USER1                MYUSER   USER1           LOGOFF                        0
04/02 15:06 USER1                MYUSER   USER1           LOGON                         0 

A couple of the rows in the above output are slightly out of sequence, but the order of the output is close enough for my needs.  The RETURNCODE column is the number associated with the ORA- error code that was returned to the client computer:

  • RETURNCODE=0 indicates success
  • RETURNCODE=1017 indicates bad password
  • RETURNCODE=28000 indicates account is locked out

Reviewing the above output, operating system user USER3 logged on successfully at 5:03 PM and logged off at 5:08 PM.  Operating system user USER1 attempted to log on at 6:31 PM from a computer named CUSER1-LT, but failed due to an invalid password.  By 6:37 PM, that same operating system user and computer combination had entered an incorrect password five times, which resulted in an ORA-28000 account lockout error being returned starting with the sixth logon attempt.

At 2:30 AM, USER5 on a computer named SERVER2 attempted to connect using the same database user account, but was also greeted with an ORA-28000.  The same fate awaited operating system user NETWORK SERVICE (this was a web-based logon attempt) and USER3 at 6:22 AM and 6:23 AM, respectively.  The problem was obviously corrected by 6:26 AM, quite likely through the application of the following SQL statement:

ALTER USER MYUSER ACCOUNT UNLOCK;

If you tried executing the above query, you might have noticed that the query execution required a bit of time.  Enterprise Manager also, apparently, executes a somewhat similar SQL statement every 30 minutes.  You can learn more about the potential performance impact of Enterprise Manager’s use of SYS.DBA_AUDIT_SESSION here.

I again started thinking about how to measure how much more efficient something is when compared to something that is never performed… I suppose that it is important to first determine whether or not the task at hand is important before deciding if not doing something is infinitely more efficient or infinitely less efficient than would be the case if the task at hand were completed.





Which PLAN_HASH_VALUE Appears in V$SQLAREA?

28 03 2012

March 28, 2012

A recent question on the OTN forums asked which PLAN_HASH_VALUE appears in V$SQLAREA when there are multiple child cursors for a single SQL_ID value, when some child cursors have a different execution plan.  Certainly, this bit of information must be in the Oracle Database documentation.  Let’s check the V$SQLAREA documentation for Oracle Database 11.2:

“Numeric representation of the SQL plan for this cursor. Comparing one PLAN_HASH_VALUE to another easily identifies whether or not two plans are the same (rather than comparing the two plans line by line).”

Well, that was not helpful, but it does remind me of something that I saw when I went out for a drive in the countryside this past weekend (all within about a 50 meter radius – click a picture to see a larger view of the picture):

 

OK, now that the initial frustration of not obtaining an answer from the documentation has subsided, let’s put together a quick test case to see if we are able to help the OP find an answer to his question.  We will borrow a slightly modified version of a test script that generates skewed data which was used in another article

CREATE TABLE T1 AS
SELECT
  ROWNUM C1,
  DECODE(ROWNUM,1,1,0) C2,
  LPAD('A',255,'A') C3
FROM
  DUAL
CONNECT BY
  LEVEL<=10000;

CREATE UNIQUE INDEX IND_T1_C1 ON T1(C1);
CREATE INDEX IND_T1_C2 ON T1(C2);

ALTER TABLE T1 MODIFY (C1 NOT NULL, C2 NOT NULL);

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE,ESTIMATE_PERCENT=>100,METHOD_OPT=>'FOR ALL INDEXED COLUMNS SIZE 254') 

For the initial test (in Oracle Database 11.2.0.2), I will use the BIND_AWARE hint to save Oracle from having to determine that the execution plan could (should) depend on the bind variable value, rather than having to rely on adaptive cursor sharing to eventually obtain the same effect:

SET LINESIZE 120
SET PAGESIZE 1000

VARIABLE V1 NUMBER
EXEC :V1:=1

SELECT /*+ BIND_AWARE */
  C1,
  C2,
  C3
FROM
  T1
WHERE
  C2=:V1;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));

SQL_ID  7p4yxrzwwuybt, child number 0
-------------------------------------
SELECT /*+ BIND_AWARE */   C1,   C2,   C3 FROM   T1 WHERE   C2=:V1

Plan hash value: 236868917

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1        |     1 |   136 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_T1_C2 |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C2"=:V1)

SELECT
  PLAN_HASH_VALUE
FROM
  V$SQLAREA
WHERE
  SQL_ID='7p4yxrzwwuybt';

PLAN_HASH_VALUE
---------------
      236868917 

As shown above, the PLAN_HASH_VALUE has a value of 236868917 in V$SQLAREA, which is the same value (shown in the execution plan) of the most recently executed child number.

Let’s repeat the previous SQL statements, this time with a different bind variable value:

EXEC :V1:=0

SELECT /*+ BIND_AWARE */
  C1,
  C2,
  C3
FROM
  T1
WHERE
  C2=:V1;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));

SQL_ID  7p4yxrzwwuybt, child number 1
-------------------------------------
SELECT /*+ BIND_AWARE */   C1,   C2,   C3 FROM   T1 WHERE   C2=:V1

Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |    33 (100)|          |
|*  1 |  TABLE ACCESS FULL| T1   |  9999 |  1327K|    33   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("C2"=:V1)

SELECT
  PLAN_HASH_VALUE
FROM
  V$SQLAREA
WHERE
  SQL_ID='7p4yxrzwwuybt';

PLAN_HASH_VALUE
---------------
     3617692013 

As shown above, the execution plan changed, thus the Plan hash value in the execution plan changed to 3617692013, and that change corresponded with the PLAN_HASH_VALUE for the SQL_ID in V$SQLAREA changing to the value 3617692013 – the same value shown in the execution plan for the most recently executed child number.

Let’s trying again without changing the bind variable value to see what happens:

SELECT /*+ BIND_AWARE */
  C1,
  C2,
  C3
FROM
  T1
WHERE
  C2=:V1;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));

SQL_ID  7p4yxrzwwuybt, child number 1
-------------------------------------
SELECT /*+ BIND_AWARE */   C1,   C2,   C3 FROM   T1 WHERE   C2=:V1

Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |    33 (100)|          |
|*  1 |  TABLE ACCESS FULL| T1   |  9999 |  1327K|    33   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("C2"=:V1)

SELECT
  PLAN_HASH_VALUE
FROM
  V$SQLAREA
WHERE
  SQL_ID='7p4yxrzwwuybt';

PLAN_HASH_VALUE
---------------
     3617692013 

As shown above, the PLAN_HASH_VALUE in V$SQLAREA remained at the value 3617692013, which is the PLAN_HASH_VALUE of the most recently executed child number.

Let’s switch back to the original bind variable value to see what happens in V$SQLAREA:

EXEC :V1:=1

SELECT /*+ BIND_AWARE */
  C1,
  C2,
  C3
FROM
  T1
WHERE
  C2=:V1;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));

SQL_ID  7p4yxrzwwuybt, child number 0
-------------------------------------
SELECT /*+ BIND_AWARE */   C1,   C2,   C3 FROM   T1 WHERE   C2=:V1

Plan hash value: 236868917

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1        |     1 |   136 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_T1_C2 |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C2"=:V1)

SELECT
  PLAN_HASH_VALUE
FROM
  V$SQLAREA
WHERE
  SQL_ID='7p4yxrzwwuybt';

PLAN_HASH_VALUE
---------------
      236868917 

As shown above, the PLAN_HASH_VALUE for the SQL_ID found in V$SQLAREA switched back to the original value – it is again showing the PLAN_HASH_VALUE of the most recently executed child cursor.

But wait, there’s more information.  Pete Finnigan recently reminded me of a problem that I had several years ago when I tried to untangle the string of synonyms and views to see the definition of various Oracle Database performance views.  He not only reminded me of the problem that I had years ago, but showed me the process of untangling the (evoke suitable picture from above) that I learned and forgot several times over the subsequent years.

Let’s get started by determining what the V$SQLAREA synonym points at:

SET LONG 9000
COLUMN TABLE_OWNER FORMAT A11

SELECT
  TABLE_OWNER,
  TABLE_NAME
FROM
  DBA_SYNONYMS
WHERE
  SYNONYM_NAME='V$SQLAREA';

TABLE_OWNER TABLE_NAME
----------- ----------
SYS         V_$SQLAREA 

A viewed named V_$SQLAREA – now what?  Let’s see the definition of that view:

SELECT
  TEXT
FROM
  DBA_VIEWS
WHERE
  VIEW_NAME='V_$SQLAREA';

select "SQL_TEXT","SQL_FULLTEXT","SQL_ID","SHARABLE_MEM","PERSISTENT_MEM","RUNTI
...
D_TOTAL","PINNED_TOTAL","IO_CELL_UNCOMPRESSED_BYTES","IO_CELL_OFFLOAD_RETURNED_B
YTES" from v$sqlarea 

So, the synonym V$SQLAREA points to the view V_$SQLAREA which selects from V$SQLAREA … that name seems oddly familiar.

SELECT
  VIEW_DEFINITION
FROM
  V$FIXED_VIEW_DEFINITION
WHERE
  VIEW_NAME='V$SQLAREA';

select    SQL_TEXT,           SQL_FULLTEXT,           SQL_ID,           SHARABLE
_MEM,           PERSISTENT_MEM,           RUNTIME_MEM,           SORTS,
...
SICAL_WRITE_BYTES,            OPTIMIZED_PHY_READ_REQUESTS,            LOCKED_TOT
AL,             PINNED_TOTAL,            IO_CELL_UNCOMPRESSED_BYTES,
IO_CELL_OFFLOAD_RETURNED_BYTES from GV$SQLAREA where inst_id = USERENV('Instance
') 

So, the view V_$SQLAREA selects from V$SQLAREA which selects from GV$SQLAREA.

SELECT
  VIEW_DEFINITION
FROM
  V$FIXED_VIEW_DEFINITION
WHERE
  VIEW_NAME='GV$SQLAREA';

select inst_id,kglnaobj,kglfnobj,kglobt03,kglobhs0+kglobhs1+kglobhs2+kglobhs3+kg
lobhs4+kglobhs5+kglobhs6,kglobt08+kglobt11,kglobt10,kglobt01,kglobccc,kglobclc,k
...
t58,kglobt23,kglobt24,kglobt59,kglobt53 - ((kglobt55+kglobt57) - kglobt52)from
x$kglcursor_child_sqlid where kglobt02 != 0

So, the view V_$SQLAREA selects from V$SQLAREA which selects from GV$SQLAREA which selects from X$KGLCURSOR_CHILD_SQLID where KGLOBT02 != 0 (COMMAND_TYPE column in V$SQLAREA). (Note, must be logged in as the SYS user for the following SQL statement.)

SELECT
  KQFDTNAM,
  KQFDTEQU
FROM
  X$KQFDT
ORDER BY
  KQFDTNAM;

KQFDTNAM                       KQFDTEQU
----------------------------- -------------
...
X$KGLBODY                      X$KGLOB
X$KGLCLUSTER                   X$KGLOB
X$KGLCURSOR                    X$KGLOB
X$KGLCURSOR_CHILD              X$KGLOB
X$KGLCURSOR_CHILD_SQLID        X$KGLOB
X$KGLCURSOR_CHILD_SQLIDPH      X$KGLOB
X$KGLINDEX                     X$KGLOB
X$KGLTABLE                     X$KGLOB
X$KGLTRIGGER                   X$KGLOB 
...

So, in summary the synonym V$SQLAREA points to the view V_$SQLAREA which selects from V$SQLAREA which selects from GV$SQLAREA which selects from X$KGLCURSOR_CHILD_SQLID where KGLOBT02 != 0, which has as a base table of X$KGLOB just like 8 other fixed tables.  If you repeat the above steps for V$SQL, you will find that it is based on X$KGLCURSOR_CHILD, which also has X$KGLOB as its base table.

Here is a piece of SQL that joins the underlying fixed table for V$SQLAREA with V$SQL to hopefully determine if the PLAN_HASH_VALUE for the most recently executed child cursor for each SQL_ID is always what appears in V$SQLAREA (I do not suggest running this SQL statement in a production environment – the test database instance in my case was bounced a couple of hours ago).  The word DIFFERENT is output if the LAST_ACTIVE_TIME from V$SQLAREA does NOT match the LAST_ACTIVE_TIME for a specific child in V$SQL (Note, must be logged in as the SYS user for the following SQL statement.):

SELECT
  X.KGLOBT03 SQL_ID,
  X.KGLOBT30 PLAN_HASH_VALUE,
  TO_CHAR(X.KGLOBCLA,'HH24:MI:SS') LAST_ACTIVE_TIME,
  S.CHILD_NUMBER,
  S.PLAN_HASH_VALUE S_PLAN_HASH_VALUE,
  TO_CHAR(S.LAST_ACTIVE_TIME,'HH24:MI:SS') S_LAST_ACTIVE_TIME,
  DECODE(X.KGLOBCLA,S.LAST_ACTIVE_TIME,NULL,'DIFFERENT') TIMESTAMP_DIFFERS
FROM
  X$KGLCURSOR_CHILD_SQLID X,
  V$SQL S
WHERE
  X.KGLOBT02 != 0
  AND X.KGLOBT03=S.SQL_ID
ORDER BY
  S.SQL_ID,
  S.CHILD_NUMBER;

SQL_ID        PLAN_HASH_VALUE LAST_ACT CHILD_NUMBER S_PLAN_HASH_VALUE S_LAST_A TIMESTAMP
------------- --------------- -------- ------------ ----------------- -------- ---------
00fx7adv5q5gm      2256887934 22:03:44            0        2256887934 22:03:44
00yp7w9j0yqma      3600061239 20:28:45            0        3600061239 20:28:45
...
07pcqtmt58zv9      1964643588 20:44:48            0        1964643588 20:28:45 DIFFERENT
07pcqtmt58zv9      1964643588 20:44:48            1        1964643588 20:44:48
...
0fr8zhn4ymu3v      1231101765 21:03:43            0        3815847153 20:28:45 DIFFERENT
0fr8zhn4ymu3v      1231101765 21:03:43            1        1231101765 21:03:43
...
0kugqg48477gf       643665366 21:03:43            0         828554155 20:28:45 DIFFERENT
0kugqg48477gf       643665366 21:03:43            1         643665366 21:03:43
...
0m78skf1mudnb      3506511888 20:28:47            0        3506511888 20:28:45 DIFFERENT
0m78skf1mudnb      3506511888 20:28:47            1        3506511888 20:28:47
...
15w1t8qpdgg5k      2628186673 21:33:45            0        2628186673 20:28:45 DIFFERENT
15w1t8qpdgg5k      2628186673 21:33:45            1        2628186673 21:33:45
...
1gu8t96d0bdmu      2035254952 21:03:43            0        2035254952 21:03:43
1gu8t96d0bdmu      2035254952 21:03:43            1        3526770254 20:28:45 DIFFERENT
1gu8t96d0bdmu      2035254952 21:03:43            2        2035254952 20:48:45 DIFFERENT
...
2jr8c42qx700h      2445831428 20:28:55            0        3034582372 20:28:45 DIFFERENT
2jr8c42qx700h      2445831428 20:28:55            1        2445831428 20:28:45 DIFFERENT
2jr8c42qx700h      2445831428 20:28:55            2        2445831428 20:28:55
...
2q93zsrvbdw48      2874733959 21:03:43            0        2874733959 21:03:43
2q93zsrvbdw48      2874733959 21:03:43            1        2874733959 20:28:45 DIFFERENT
2q93zsrvbdw48      2874733959 21:03:43            2        2874733959 20:48:45 DIFFERENT
2qqqjzkhmsbgv      1308273333 20:28:45            0        1308273333 20:28:45
2syvqzbxp4k9z      1423211129 20:28:47            0        1423211129 20:28:45 DIFFERENT
2syvqzbxp4k9z      1423211129 20:28:47            1        1423211129 20:28:47
2tkw12w5k68vd      1457651150 21:03:43            0        1457651150 20:28:45 DIFFERENT
2tkw12w5k68vd      1457651150 21:03:43            1        1457651150 21:03:43
2xgubd6ayhyb1      3418045132 21:03:43            0        3418045132 21:03:43
2xyb5d6xg9srh       785096182 20:28:47            0         785096182 20:28:45 DIFFERENT
2xyb5d6xg9srh       785096182 20:28:47            1         785096182 20:28:47
2ysccdanw72pv      3801013801 20:28:55            0        3801013801 20:28:55
32bhha21dkv0v      3765558045 21:03:43            0        3765558045 20:28:45 DIFFERENT
32bhha21dkv0v      3765558045 21:03:43            1        3765558045 21:03:43
...
38fffx897xs0v      1042772069 21:33:45            0        1042772069 20:28:45 DIFFERENT
38fffx897xs0v      1042772069 21:33:45            1        1042772069 21:33:45
...
39m4sx9k63ba2      2317816222 21:03:43            0        2317816222 20:28:45 DIFFERENT
39m4sx9k63ba2      2317816222 21:03:43            1        2317816222 21:03:43
...
3k0c6241uw582      1964643588 20:44:48            0        1964643588 20:28:45 DIFFERENT
3k0c6241uw582      1964643588 20:44:48            1        1964643588 20:44:48
3ktacv9r56b51      4184428695 21:03:43            0        4184428695 20:28:45 DIFFERENT
3ktacv9r56b51      4184428695 21:03:43            1        4184428695 20:28:45 DIFFERENT
3ktacv9r56b51      4184428695 21:03:43            2        4184428695 21:03:43
3nhfzxjzx2btx      1043815174 20:28:45            0        1043815174 20:28:45
3nkd3g3ju5ph1      2853959010 21:03:43            0        2853959010 21:03:43
3nkd3g3ju5ph1      2853959010 21:03:43            1        2853959010 20:28:45 DIFFERENT
3nkd3g3ju5ph1      2853959010 21:03:43            2        2853959010 20:48:45 DIFFERENT
3np8cptn6uzn6      1754305749 20:28:46            0        1754305749 20:28:46
3nzv2smdzzbsf      2731876963 21:33:45            0        2731876963 20:28:45 DIFFERENT
3nzv2smdzzbsf      2731876963 21:33:45            1        2731876963 21:33:45
...
3rw49yhahg984      3808094885 20:28:55            0        3808094885 20:28:45 DIFFERENT
3rw49yhahg984      3808094885 20:28:55            1        3808094885 20:28:55
...
3w4qs0tbpmxr6      1224215794 21:03:43            0        1224215794 21:03:43
3w4qs0tbpmxr6      1224215794 21:03:43            1        1224215794 20:28:45 DIFFERENT
3w4qs0tbpmxr6      1224215794 21:03:43            2        1224215794 20:48:45 DIFFERENT
...
459f3z9u4fb3u       415205717 20:44:48            0         415205717 20:28:45 DIFFERENT
459f3z9u4fb3u       415205717 20:44:48            1         415205717 20:44:48
...
4cvqvs489pd6k      2300756036 20:28:55            0        2300756036 20:28:45 DIFFERENT
4cvqvs489pd6k      2300756036 20:28:55            1        2300756036 20:28:55
...
4zzxr8rvht74z      3368685730 20:44:48            0        3368685730 20:28:45 DIFFERENT
4zzxr8rvht74z      3368685730 20:44:48            1        3368685730 20:44:48
...
53saa2zkr6wc3      3954488388 21:03:43            0        3954488388 21:03:43
53saa2zkr6wc3      3954488388 21:03:43            1        1514015273 20:28:45 DIFFERENT
53saa2zkr6wc3      3954488388 21:03:43            2        3954488388 20:33:45 DIFFERENT
...
5n1fs4m2n2y0r       299250003 21:03:43            0         299250003 20:28:45 DIFFERENT
5n1fs4m2n2y0r       299250003 21:03:43            1         299250003 21:03:43
5n1fs4m2n2y0r       299250003 21:03:43            2         299250003 20:48:45 DIFFERENT
...
5wxyshspv54v4      3009292138 20:44:48            0        3009292138 20:28:45 DIFFERENT
5wxyshspv54v4      3009292138 20:44:48            1        3009292138 20:44:48
...
6aq34nj2zb2n7      2874733959 21:03:43            0        2874733959 21:03:43
6aq34nj2zb2n7      2874733959 21:03:43            1        2874733959 20:28:45 DIFFERENT
6aq34nj2zb2n7      2874733959 21:03:43            2        2874733959 20:48:45 DIFFERENT
6b7hj70p170j1      2605232930 20:28:45            0        2605232930 20:28:45
6c9wx6z8w9qpu       785096182 20:28:47            0         785096182 20:28:45 DIFFERENT
6c9wx6z8w9qpu       785096182 20:28:47            1         785096182 20:28:47
...
6qz82dptj0qr7      2819763574 21:03:43            0        2819763574 20:28:45 DIFFERENT
6qz82dptj0qr7      2819763574 21:03:43            1        2819763574 21:03:43
...
79w2cqu2gmjm8      4145101951 20:28:55            0        2645993454 20:28:45 DIFFERENT
79w2cqu2gmjm8      4145101951 20:28:55            1        4145101951 20:28:45 DIFFERENT
79w2cqu2gmjm8      4145101951 20:28:55            2        4145101951 20:28:55
7akvnu9t168d3      1964643588 20:44:48            0        1964643588 20:28:45 DIFFERENT
7akvnu9t168d3      1964643588 20:44:48            1        1964643588 20:44:48
...
7jpt4cpfvcy1k       284504113 20:28:46            0         284504113 20:28:45 DIFFERENT
7jpt4cpfvcy1k       284504113 20:28:46            1         284504113 20:28:46
7mgr3uwydqq8j       293268181 22:13:45            0         293268181 22:13:45
7ng34ruy5awxq      3992920156 21:03:43            0        3992920156 21:03:43
7ng34ruy5awxq      3992920156 21:03:43            1         306576078 20:28:45 DIFFERENT
7ng34ruy5awxq      3992920156 21:03:43            2        3992920156 20:48:45 DIFFERENT
7nuw4xwrnuwxq      1720483994 21:03:43            0        1720483994 20:28:45 DIFFERENT
7nuw4xwrnuwxq      1720483994 21:03:43            1        1720483994 21:03:43
...
83taa7kaw59c1      3765558045 21:03:43            0        3765558045 21:03:43
83taa7kaw59c1      3765558045 21:03:43            1        3765558045 20:28:45 DIFFERENT
83taa7kaw59c1      3765558045 21:03:43            2        3765558045 20:48:45 DIFFERENT
85sxp7kypwbx6      1395584798 20:28:46            0        1395584798 20:28:46
87gaftwrm2h68      1218588913 21:03:43            0        1218588913 20:28:45 DIFFERENT
87gaftwrm2h68      1218588913 21:03:43            1        1218588913 21:03:43
87gaftwrm2h68      1218588913 21:03:43            2        1218588913 20:48:45 DIFFERENT
...
8swypbbr0m372       893970548 21:03:43            0         893970548 20:28:45 DIFFERENT
8swypbbr0m372       893970548 21:03:43            1         893970548 20:28:45 DIFFERENT
8swypbbr0m372       893970548 21:03:43            2         893970548 21:03:43
...
9g485acn2n30m      2544153582 21:03:43            0        2544153582 20:28:45 DIFFERENT
9g485acn2n30m      2544153582 21:03:43            1        2544153582 21:03:43
9gkq7rruycsjp      3362549386 20:28:46            0        3362549386 20:28:45 DIFFERENT
9gkq7rruycsjp      3362549386 20:28:46            1        3362549386 20:28:46
...
9rfqm06xmuwu0       832500465 21:03:43            0         832500465 20:28:45 DIFFERENT
9rfqm06xmuwu0       832500465 21:03:43            1         832500465 21:03:43
9rvqpun1x1xjd      2760275752 20:28:46            0        2760275752 20:28:46
9tgj4g8y4rwy8      3755742892 21:03:43            0        3755742892 20:28:45 DIFFERENT
9tgj4g8y4rwy8      3755742892 21:03:43            1        3755742892 21:03:43
...
axmdf8vq7k1rh      2203911306 20:44:48            0        2203911306 20:28:45 DIFFERENT
axmdf8vq7k1rh      2203911306 20:44:48            1        2203911306 20:44:48
...
b1wc53ddd6h3p      1637390370 21:03:43            0        1637390370 20:28:45 DIFFERENT
b1wc53ddd6h3p      1637390370 21:03:43            1        1637390370 21:03:43
...
bsa0wjtftg3uw      1512486435 22:23:19            0        2020579421 20:28:45 DIFFERENT
bsa0wjtftg3uw      1512486435 22:23:19            1        1512486435 22:23:19
bsa0wjtftg3uw      1512486435 22:23:19            2        1512486435 20:48:45 DIFFERENT
...
c4nhd1ntptxq7      3477319146 20:28:46            0        3477319146 20:28:46
c4nhd1ntptxq7      3477319146 20:28:46            1        3477319146 20:28:46
...
cb21bacyh3c7d      3452538079 21:03:43            0        3452538079 20:28:45 DIFFERENT
cb21bacyh3c7d      3452538079 21:03:43            1        3452538079 20:28:46 DIFFERENT
cb21bacyh3c7d      3452538079 21:03:43            2        3452538079 21:03:43
...
cjk1ffy5kmm5s      1964104430 20:28:46            0        1964104430 20:28:45 DIFFERENT
cjk1ffy5kmm5s      1964104430 20:28:46            1        1964104430 20:28:46
...
cvn54b7yz0s8u      3246118364 21:03:43            0        3246118364 20:28:45 DIFFERENT
cvn54b7yz0s8u      3246118364 21:03:43            1        3246118364 21:03:43
d00a21h5ybffr       959325123 20:44:48            0        2829621105 20:28:45 DIFFERENT
d00a21h5ybffr       959325123 20:44:48            1         959325123 20:44:48
...
f3g84j69n0tjh      2335623859 21:03:43            0         914792125 20:28:45 DIFFERENT
f3g84j69n0tjh      2335623859 21:03:43            1        2335623859 21:03:43
...
fzrshwabvtwc0      3637245398 22:23:45            0        3637245398 20:28:45 DIFFERENT
fzrshwabvtwc0      3637245398 22:23:45            1        3637245398 22:23:45
...
g3wrkmxkxzhf2       749386351 21:03:43            0         749386351 20:28:45 DIFFERENT
g3wrkmxkxzhf2       749386351 21:03:43            1         749386351 21:03:43
...
g7smmy8ybh3gv        43085360 20:28:55            0          43085360 20:28:46 DIFFERENT
g7smmy8ybh3gv        43085360 20:28:55            1          43085360 20:28:55
...
ga9j9xk5cy9s0      1697022209 21:03:43            0        1697022209 20:28:45 DIFFERENT
ga9j9xk5cy9s0      1697022209 21:03:43            1        1697022209 21:03:43
...
grwydz59pu6mc      3684871272 21:03:43            0        3684871272 20:28:45 DIFFERENT
grwydz59pu6mc      3684871272 21:03:43            1        3684871272 21:03:43
...
gx4mv66pvj3xz      1932954096 21:03:43            0        1932954096 21:03:43
gx4mv66pvj3xz      1932954096 21:03:43            1        2570921597 20:28:45 DIFFERENT
gx4mv66pvj3xz      1932954096 21:03:43            2        1932954096 20:48:45 DIFFERENT
... 

I feel that I need some more suitable pictures now.  :-)





Monitoring Changes to Table Data

22 03 2012

March 22, 2012

Suppose that you receive a request stating that a particular table in one of your databases must be monitored for changes.  One such table might be the one that lists the ERP system’s suppliers and their addresses – you would not want someone to be able to set up a legitimate supplier, and later have the billing remit to name and address changed without being detected.

What approach would you take to solve the above problem?  A question similar to the above arrived recently in an ERP mailing list email – the original poster (OP) is using a SQL Server RDBMS, so that might change the proposed solution just a bit.

Here is the “How simple is too simple?” suggestion that I offered:

Periodically, create a VENDOR_SAVE table (drop it if it already exists, or delete all rows and re-insert from the original source table):

CREATE TABLE VENDOR_SAVE AS
SELECT
  *
FROM
  VENDOR;

Now, just wait a while.

The question then becomes, how do we detect:

  • A new row (record) added to the original source (VENDOR) table.
  • An old row (record) deleted from the original source (VENDOR) table.
  • A change to any column (program field) in the original source (VENDOR) table since the last time the VENDOR_SAVE table was created/refreshed.

Let’s start with the first two bullet points.  I will write the SQL statements so that the statements should work with Oracle Database 9.0.1 and above, and SQL Server with very few changes, even if I feel a bit ANSI about doing so.  I will use the COALESCE function, rather than the NVL function, and CASE syntax rather than the equivalent DECODE syntax.

To identify cases where a row has appeared in, or disappeared from the original source (VENDOR) table, we can simply perform a full outer join between the original source table and the historical mirror image of the original table (VENDOR_SAVE).  We are only interested in cases where the primary key column (ID) is found in exactly one of the two tables:

SELECT
  COALESCE(V.ID,V2.ID) AS ID,
  COALESCE(V.NAME,V2.NAME) AS NAME,
  COALESCE(V.ADDR_1,V2.ADDR_1) AS ADDR_1,
  CASE WHEN V.ID IS NULL THEN 'VENDOR ADDED' ELSE 'VENDOR DELETED' END AS PROBLEM
FROM
  VENDOR V
FULL OUTER JOIN
  VENDOR_SAVE V2
ON (V.ID=V2.ID)
WHERE
  V.ID IS NULL
  OR V2.ID IS NULL;

So, the above SQL statement satisfies the first two bullet points.  The third bullet point is a little more challenging to accomplish… unless of course we employ UNION labor.  If we have two row sources with identical columns, and UNION the row sources together, the resulting row source will be absent of any entirely duplicated rows from the two original row sources (two rows will be reduced to a single row).  If there were no changes to any of the column values (or if the row was added to or deleted from the original source table), there will be a single row for the primary key column value.  If any columns were changed, there will be two rows containing the primary key column value.

Let’s build a SQL statement that UNIONs the rows from the two tables together, and counts the number of rows for each primary key value:

SELECT
  COUNT(*) OVER (PARTITION BY VL.ID) AS CNT,
  VL.ID,
  VL.NAME,
  VL.ADDR_1
FROM
  (SELECT
    *
  FROM
    VENDOR
  UNION
  SELECT
    *
  FROM
    VENDOR_SAVE) VL;

To complete the requirement for bullet point 3 above, we need to eliminate all rows from the result set where there is a single row for the primary key value:

SELECT DISTINCT
  VL2.ID,
  VL2.NAME,
  VL2.ADDR_1,
  'VALUE CHANGE' PROBLEM
FROM
  (SELECT
    COUNT(*) OVER (PARTITION BY VL.ID) AS CNT,
    VL.ID,
    VL.NAME,
    VL.ADDR_1
  FROM
    (SELECT
      *
    FROM
      VENDOR
    UNION
    SELECT
      *
    FROM
      VENDOR_SAVE) VL) VL2
WHERE
  VL2.CNT>1;

As a final step, we should join the two resultsets into a single resultset using UNION ALL:

SELECT
  COALESCE(V.ID,V2.ID) AS ID,
  COALESCE(V.NAME,V2.NAME) AS NAME,
  COALESCE(V.ADDR_1,V2.ADDR_1) AS ADDR_1,
  CASE WHEN V.ID IS NULL THEN 'VENDOR ADDED' ELSE 'VENDOR DELETED' END AS PROBLEM
FROM
  VENDOR V
FULL OUTER JOIN
  VENDOR_SAVE V2
ON (V.ID=V2.ID)
WHERE
  V.ID IS NULL
  OR V2.ID IS NULL
UNION ALL
SELECT DISTINCT
  VL2.ID,
  VL2.NAME,
  VL2.ADDR_1,
  'VALUE CHANGE' PROBLEM
FROM
  (SELECT
    COUNT(*) OVER (PARTITION BY VL.ID) AS CNT,
    VL.ID,
    VL.NAME,
    VL.ADDR_1
  FROM
    (SELECT
      *
    FROM
      VENDOR
    UNION
    SELECT
      *
    FROM
      VENDOR_SAVE) VL) VL2
WHERE
  VL2.CNT>1
ORDER BY
  ID;

While somewhat limited in usefulness, the above approach will indicate which rows in the original source table should be examined because the column values in those rows changed (or were added or deleted).

Another, potentially more useful approach involves setting up a logging trigger and logging table.  I previously shared a VBS script that helps to automate and standardize the process of creating the logging trigger and logging table.  A very similar feature is built into my Hyper-Extended Oracle Performance Monitor program – but, much like the VBS script, this solution is useless for the OP who uses a SQL Server backend.

What are the other options?  Oracle Database’s built-in auditing capabilities.  Auditing built into the ERP system (this particular ERP system writes all audit/change records to a single table that uses a VARCHAR2 column to capture the before/after images of the values stored in columns).  Any other options?  (The mess that someone can create with a bit of idle time on their hands…)





Hyper-Extended Oracle Performance Monitor 6.0 Beta

15 03 2012

March 15, 2012 (Modified March 16, 2012)

Several people expressed an interest in using the Beta version of my Hyper-Extended Oracle Performance Monitor 6.0 that has been under development for about a decade.  Rather than trying to find a way to deliver the Beta version of the program to those people who left comments in the earlier thread, it seemed to be much easier to just post the Beta version to this blog.

The  Hyper-Extended Oracle Performance Monitor tool runs from a Windows client PC (XP with ADO 2.8+ installed, Vista, Windows 7 32/64 bit, Server 2003, Server 2008) and for some tasks, such as report generation, requires Microsoft Excel (2000, XP, 2003, 2007, or 2010) to be present on the PC.  Everything that is logged is written to a C:\OracleLog folder on the client computer, and unfortunately that likely means that User Access Control (UAC) in Vista and Windows 7 will either need to be turned down or turned off completely (UAC will prevent programs from writing in folders that are located directly in the root of the C:\ drive).  It is important to make certain that the Windows interfaces (all except MTS) are installed with the Oracle Client software, which should add the OraOLEDB functionality that is used by the program for connectivity to the databases.

An unfortunate side effect of using OraOLEDB functionality rather than ODBC is that the SYS user is not able to log in AS SYSDBA for certain tasks such as accessing the X$ structures (specifically X$BH, and the X$ structures (X$KSPPI, X$KSPPSV) needed for viewing the hidden initialization parameters). Setting the O7_DICTIONARY_ACCESSIBILITY initialization parameter to TRUE will allow the program to connect as the SYS user (without AS SYSDBA), but doing so may represent a security risk for the database.

The program writes nothing to the Oracle database that is monitored, although it might try to automatically adjust the MAX_DUMP_FILE_SIZE parameter if the user attempts to enable a 10046 trace using the program, and the program determines that the MAX_DUMP_FILE_SIZE parameter is set far too small.  The user that logs into the program will need proper permissions to access the various V$ views (GV$ views are not accessed) and also access the various packages that enable 10046/10053 traces (enabling a 10046 (or other trace) within the program’s interface requires that the user logging into the program have EXECUTE permission on the DBMS_SYSTEM and DBMS_MONITOR packages).

The 10046 trace file parser is still a bit stuck in the land of Oracle Database 8.1 – it still expects to find p1, p2, and p3 on WAIT event lines if the Table and Object Lookup option is selected for trace file parsing (and for certain wait event analysis).  Later versions of Oracle Database emit obj parameters on the WAIT lines, and the program should use the obj value rather than trying to look up the OBJECT_ID value using the p1, p2, and p3 parameters. The 10046 trace file parser performs a trick to handle the extremely long cursor numbers found in Oracle Database 11.2.0.2 and later.  The Hyper-Extended Oracle Performance Monitor is intended to work fully on Oracle Database 10.2, and (hopefully) gracefully degrade when an older version of Oracle Database is encountered.

The program supports several command line parameters, most of which are used to configure performance logging capabilities:

-D      The Database instance SID to which the program should connect.
-U      The user name to be used for connecting to the database instance.
-P      The password to be used for connecting to the database instance.

-LC 20  Specifies Force a Log Capture when CPU Usage Exceeds value to 20%
-LI 30  Specifies Force a Log Capture if No Log Captured in Minutes value to 30 minutes
-LB     Specifies the Force a Log Capture when a Blocking Lock is Detected value to checked
-LW     Specifies the Force a Log Capture when a Wait Reason is Detected value to checked
-LR     Specifies the Capture SQL Execution Statistics for Wait Reasons value to checked
-LD     Specifies the Capture Segment Change Statistics value to checked
-LO     Specifies the Capture Operating System and Time Model Statistics value to checked
-LH     Specifies the Capture High Load SQL Statement Statistics value to checked
-LT     Specifies the Capture High Load SQL Statement Text value to checked
-LP     Specifies the Capture High Load SQL Statement Plan value to checked
-LHC 60 Species the minimum CPU time that is considered high load to 60 seconds accum.
-LHE 90 Species the minimum elapsed time that is considered high load to 90 seconds accum.
-LS     Specifies that Smart Logging should begin as soon as the login completes
-LE 240 Specifies that Smart Logging should end after 240 minutes
-LQ     Specifies that the program should quit (end) when logging ends

-

Important: Keep in mind that there is an overhead, primarily server CPU utilization, associated with performance monitoring.  This overhead will be greatest when the program’s performance logging feature is utilized.  This overhead, while typically minor, might negatively impact the performance of other database sessions.  Under no circumstances should this program run directly on a Windows-based Oracle Database server’s console – doing so with performance logging enabled will significantly impact the performance of other database sessions.

This program does not phone home, nor does it collect any information that is not found in the C:\OracleLog folder on the client computer.  The C:\OracleLog folder could prove to provide additional information that is not presented directly in the program interface.  For example, when real-time performance is monitored, every 30 minutes the program will write one or more text files into the C:\OracleLog folder that show a crosstab style report of statistics and wait events (open the file with Microsoft Excel to aid readability).  The performance logging feature creates a Microsoft Access compatible database (named to correspond to the logging date and time) in the C:\OracleLog folder – various information, such as in-effect initialization parameters, are written in that Access database, even though that information is not displayed in the program’s user interface.

———————————————————————————————–
———————————————————————————————–
———————————————————————————————–

DOWNLOAD:

The documentation for the program is at least four years out of date.  You may download the program instructions for the Hyper-Extended Oracle Performance Monitor 3.0 Beta here: Hyper-ExtendedOraclePerformanceMonitor3Docs

The Beta version of the program is time limited, however it should continue functioning for the next 12 months.  You may download the program by right clicking the file and saving it as “Hyper-ExtendedOraclePerformanceMonitor6.zip” (the .zip extension must be specified): Hyper-ExtendedOraclePerformanceMonitor6.zip

The program is compressed using WinZip – Windows XP and later are able to directly open .zip files.  To install the program, simply extract the two files into the same folder; to uninstall, delete that folder and the C:\OracleLog folder.

If you find the program useful, feel free to leave a comment here.  If you find that this program is the biggest waste of a decade’s worth of free time, I would be happy to hear that too.  The program has a couple of known bugs – I know that they exist, but I do not know where they are in the program, nor do I yet know what the bugs affect.

———————————————————————————————–
———————————————————————————————–
———————————————————————————————–

Added March 16, 2012:

I thought that I would show a couple of screen captures from my program that are not necessarily performance tuning specific.

The Advanced Initialization Parameters Viewer (currently only works if the O7_DICTIONARY_ACCESSIBILITY initialization parameter is set to TRUE , but I am considering a couple of work around methods – note that the program’s description of the CURSOR_SHARING parameter does not yet mention that the SIMILAR value for the CURSOR_SHARING parameter is deprecated):

Keyword Search Viewer:

Lock/Wait Monitor:

Configure Data Change Log (showing one of the logging tables that was created by the program’s script generator):

DBMS_XPLAN and Trace:





Thoughts on a Hyper-Extended Oracle Performance Monitor Beta

12 03 2012

March 12, 2012

As long time readers of this blog might know, in my free time during roughly the last 10 years I have been working on a program named “Hyper-Extended Oracle Performance Monitor”.  Since 2005 or 2006 I have permitted a few people to try the beta versions of the program, thinking that I might obtain a bit of feedback about what works well, and what needs a lot of work.  I was recently informed of a couple of situations where one or two features in the program were extremely useful – I would much rather hear that kind of feedback, rather than “I forgot about that program.” :-)

What started as a simple 10046 trace file parser, easy method to execute a handful of scripts, and a V$ performance view logger has certainly grown over the years.  I have not updated the documentation for the program in almost four years, and some suggestions offered by the program seem to be Oracle Database 8.1 specific… one of these days I might have some time to address those issues.

Over the last couple of days I found a couple of unplanned features (bugs) in the program – some of those features have been in the program for a couple of years, others were added just last week.  I am currently debating whether or not to open up the beta of the program to a wider audience.  Are any readers of this blog interested?

The main screen in the program probably looks unlike any program that you have seen in the past – menus, who needs menus:

-

If you drag and drop an Oracle 10046 trace file on the picture in the main screen, you will see a daunting list of options:

-

If you have followed along with the six part series on building an Oracle Database Time Model Viewer, you might recognize this screen in my program:

-

One of the original purposes of the program was to log the various statistics found in certain V$ performance views.  Over the years I added additional information that the program is able to optionally capture, and set up the logging capabilities so that certain events will force more frequent logging of statistics: 

 

-

With logging enabled, statistics are written to an dynamically created Microsoft Access compatible database, and as the statistics are captured, a summary of the statistics is written to the main program window:

-

Once you have logged something interesting, you can go back and review the information using a variety of interfaces in the program (or just stare blankly at the Microsoft Access database that was created).  Among other things, the below screen capture shows that one session spent roughly 24 seconds of the roughly 60 second time period in the wait event enq: TX – row lock contention.

-

We can easily take a look at the system level wait events and statistics for this time period:

-

Or drill-down to the session level waits and statistics from the table at the bottom of the Review Time Model Statistics window.  There is the session and its wait event, but what caused the wait event?

-

Maybe we should investigate… there’s a button for that.  Blocker and Blocked near the bottom left of the window – I wonder if that is a clue?

-

Let’s double-click one of those rows to see what happens:

-

Nice start, but let’s ask for more information by clicking Yes.

We now have the SQL statement the blocked session was attempting to execute, and possibly the SQL statement that the blocker executed which caused the  enq: TX – row lock contention wait event (the SQL statement is actually the most recent SQL statement executed by the blocker in the time period).

-

We are also able to take a quick tour of some of the SQL statements executed in the capture period and an extended version of the execution plans for those SQL statements:

-

There are another eight year’s worth of development in the program…








Follow

Get every new post delivered to your Inbox.

Join 141 other followers