I really thought I’d blogged about this before, but it appears not!

First of all, this isn’t something I’ve worked out, I’m just recycling something that John Beresniewicz (who seems to be better known as JB) shared during one of his presentations at the UKOUG Conference 2011. This wasn’t a big part of the presentation, but it jumped out at me as a gem of knowledge that I was unlikely to get from anywhere else. In fact, as I recall, John described how he only became aware of the functionality as a result of discussing what he was working on, and the problem he had, to a member of the database server development team at Oracle.

That nicely brings me to the usual, and very important, point about Oracle functionality that isn’t documented: It is not supported (unless someone from Oracle tells you otherwise).

So, did you know that as well as the GV$ views in an Oracle database there is a GV$ function?

Keeping it really simple to start with…

Selecting INSTANCE_NAME for all instances via GV$INSTANCE

SQL> select instance_name from gv$instance;

INSTANCE_NAME
----------------
ORCL1
ORCL2

SQL>

Selecting INSTANCE_NAME for all instances via V$INSTANCE with the GV$ Function

SQL> select instance_name from TABLE(GV$(CURSOR(select instance_name from v$instance)));

INSTANCE_NAME
----------------
ORCL1
ORCL2

SQL>

So, apart from being cool because it’s undocumented, what good is the GV$ function? Well, in the above example it is no use at all apart from allowing you to type more characters and confuse anyone that is looking over your shoulder!

However, what it is doing is executing the statement in CURSOR( ) on each instance in the cluster database and returning the results to the Query Coordinator (QC) session.

I don’t remember the specifics of what John was working on, but as I recall the issue was that, on the basis of performance, he didn’t want to retrieve all the relevant rows from two GV$ views in order to join them locally. I’ll try to demonstrate the point below using a join between [G]V$SQL and [G]V$SESSION

Preparation

The first step was to execute the same statement a number of times in each of my two instances.

Having obtained the SQL_ID for the statement the next step was to query the GV$ views in order to get some output – Note this is an artificially simple statement compared with where you would get a major benefit from this approach.

Querying GV$ Views

I’ve used autotrace as it’s a nice way to get the statement, results and execution plan without having to type/run too many commands…

SQL> select se.inst_id
  2  	  , se.sql_id
  3  	  , sq.executions
  4    from gv$sql sq
  5  	  , gv$session se
  6   where se.sql_id = sq.sql_id
  7  	and se.inst_id = sq.inst_id
  8  	and se.sql_id = 'cw1knhbvzvdbf'
  9  /

   INST_ID SQL_ID        EXECUTIONS
---------- ------------- ----------
         1 cw1knhbvzvdbf         15
         2 cw1knhbvzvdbf         10

Execution Plan
----------------------------------------------------------
Plan hash value: 3376983457

----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                           |     1 |    55 |     1 (100)| 00:00:01 |        |      |            |
|*  1 |  HASH JOIN                    |                           |     1 |    55 |     1 (100)| 00:00:01 |        |      |            |
|   2 |   PX COORDINATOR              |                           |     1 |    34 |     0   (0)| 00:00:01 |        |      |            |
|   3 |    PX SEND QC (RANDOM)        | :TQ10000                  |     1 |    34 |     0   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|*  4 |     VIEW                      | GV$SQL                    |       |       |            |          |  Q1,00 | PCWP |            |
|*  5 |      FIXED TABLE FIXED INDEX  | X$KGLCURSOR_CHILD (ind:2) |     1 |    34 |     0   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|   6 |   PX COORDINATOR              |                           |     1 |    21 |     0   (0)| 00:00:01 |        |      |            |
|   7 |    PX SEND QC (RANDOM)        | :TQ20000                  |     1 |    99 |     0   (0)| 00:00:01 |  Q2,00 | P->S | QC (RAND)  |
|*  8 |     VIEW                      | GV$SESSION                |       |       |            |          |  Q2,00 | PCWP |            |
|   9 |      NESTED LOOPS             |                           |     1 |    99 |     0   (0)| 00:00:01 |  Q2,00 | PCWP |            |
|  10 |       NESTED LOOPS            |                           |     1 |    86 |     0   (0)| 00:00:01 |  Q2,00 | PCWP |            |
|* 11 |        FIXED TABLE FULL       | X$KSUSE                   |     1 |    60 |     0   (0)| 00:00:01 |  Q2,00 | PCWP |            |
|* 12 |        FIXED TABLE FIXED INDEX| X$KSLWT (ind:1)           |     1 |    26 |     0   (0)| 00:00:01 |  Q2,00 | PCWP |            |
|* 13 |       FIXED TABLE FIXED INDEX | X$KSLED (ind:2)           |     1 |    13 |     0   (0)| 00:00:01 |  Q2,00 | PCWP |            |
----------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("SQL_ID"="SQL_ID" AND "INST_ID"="INST_ID")
   4 - filter("SQL_ID"='cw1knhbvzvdbf')
   5 - filter("KGLOBT03"='cw1knhbvzvdbf')
   8 - filter("SQL_ID"='cw1knhbvzvdbf')
  11 - filter("S"."KSUSESQI"='cw1knhbvzvdbf' AND BITAND("S"."KSSPAFLG",1)<>0 AND BITAND("S"."KSUSEFLG",1)<>0)
  12 - filter("S"."INDX"="W"."KSLWTSID")
  13 - filter("W"."KSLWTEVT"="E"."INDX")

Note
-----
   - statement not queuable: gv$ statement

Statistics
----------------------------------------------------------
         12  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        753  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

SQL>

The next part was to use the GV$ funtion.

Querying V$ Views with GV$ Function

Running effectively the same statement with the GV$ function.

SQL> select * from TABLE(GV$(CURSOR(select i.instance_number inst_id
  2                                                        , se.sql_id
  3                                                        , sq.executions
  4                                                     from v$sql sq
  5                                                        , v$session se
  6                                                        , v$instance i
  7                                                    where se.sql_id = sq.sql_id
  8                                                      and se.sql_id = 'cw1knhbvzvdbf')))
  9  /

   INST_ID SQL_ID        EXECUTIONS
---------- ------------- ----------
         1 cw1knhbvzvdbf         15
         2 cw1knhbvzvdbf         10

Execution Plan
----------------------------------------------------------
Plan hash value: 1284594253

--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                           |   100 |  3400 |     1 (100)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR                   |                           |   100 |  3400 |     1 (100)| 00:00:01 |        |      |            |
|   2 |   PX SEND QC (RANDOM)             | :TQ10000                  |   100 |  4200 |     1 (100)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    VIEW                           |                           |       |       |            |          |  Q1,00 | PCWP |            |
|   4 |     MERGE JOIN CARTESIAN          |                           |   100 |  4200 |     1 (100)| 00:00:01 |  Q1,00 | PCWP |            |
|*  5 |      HASH JOIN                    |                           |     1 |    29 |     1 (100)| 00:00:01 |  Q1,00 | PCWP |            |
|   6 |       VIEW                        | V_$SQL                    |     1 |    21 |     0   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|   7 |        VIEW                       | V$SQL                     |     1 |    21 |     0   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|   8 |         VIEW                      | GV$SQL                    |     1 |    34 |     0   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|*  9 |          FIXED TABLE FIXED INDEX  | X$KGLCURSOR_CHILD (ind:2) |     1 |    34 |     0   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|  10 |       VIEW                        | V_$SESSION                |     1 |     8 |     0   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|  11 |        VIEW                       | V$SESSION                 |     1 |     8 |     0   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|  12 |         VIEW                      | GV$SESSION                |     1 |    21 |     0   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|  13 |          NESTED LOOPS             |                           |     1 |    99 |     0   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|  14 |           NESTED LOOPS            |                           |     1 |    86 |     0   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|* 15 |            FIXED TABLE FULL       | X$KSUSE                   |     1 |    60 |     0   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|* 16 |            FIXED TABLE FIXED INDEX| X$KSLWT (ind:1)           |     1 |    26 |     0   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|* 17 |           FIXED TABLE FIXED INDEX | X$KSLED (ind:2)           |     1 |    13 |     0   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|  18 |      BUFFER SORT                  |                           |   100 |  1300 |     1 (100)| 00:00:01 |  Q1,00 | PCWP |            |
|  19 |       VIEW                        | V_$INSTANCE               |   100 |  1300 |     0   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|  20 |        VIEW                       | V$INSTANCE                |   100 |  1300 |     0   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|  21 |         VIEW                      | GV$INSTANCE               |   100 |  2600 |     0   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|  22 |          MERGE JOIN CARTESIAN     |                           |   100 |  6000 |     0   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|  23 |           MERGE JOIN CARTESIAN    |                           |     1 |    60 |     0   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|* 24 |            FIXED TABLE FULL       | X$KSUXSINST               |     1 |    26 |     0   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|  25 |            BUFFER SORT            |                           |     1 |    34 |     0   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|* 26 |             FIXED TABLE FULL      | X$KVIT                    |     1 |    34 |     0   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|  27 |           BUFFER SORT             |                           |   100 |       |     0   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|  28 |            FIXED TABLE FULL       | X$QUIESCE                 |   100 |       |     0   (0)| 00:00:01 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("SE"."SQL_ID"="SQ"."SQL_ID")
   9 - filter("KGLOBT03"='cw1knhbvzvdbf' AND "INST_ID"=USERENV('INSTANCE'))
  15 - filter("S"."KSUSESQI"='cw1knhbvzvdbf' AND "S"."INST_ID"=USERENV('INSTANCE') AND BITAND("S"."KSSPAFLG",1)<>0 AND
              BITAND("S"."KSUSEFLG",1)<>0)
  16 - filter("S"."INDX"="W"."KSLWTSID")
  17 - filter("W"."KSLWTEVT"="E"."INDX")
  24 - filter("KS"."INST_ID"=USERENV('INSTANCE'))
  26 - filter("KVITTAG"='kcbwst')

Note
-----
   - statement not queuable: gv$ statement

Statistics
----------------------------------------------------------
          6  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        753  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          2  rows processed

SQL>

As you can see from the executions plans above:

1. The GV$ view statement involves the rows from both GV$SQL and GV$SESSION being sent to the QC (line IDs 3 and 7) with the join between the two views being performed by the QC (line ID 1).
2. The GV$ function statement involves all joins happening before any rows are sent to the QC (line ID 2).

I, for one, think that’s pretty cool

Note – In both cases the statements had previously been executed so the execution statistics don’t include the initial parse recursive calls… Just in case you were wondering.

Recently at work I was engaged by one of our application support teams because of application calls failing with “ORA-00020: maximum number of processes (300) exceeded”. We quickly identified that these errors were the symptom of another problem and not a concern in themselves, the real issue was slow database calls due to an infrastructure issue. The ORA-00020 errors were because the application was spawning new connections to try to work around the slow connections. Now I don’t know much about application servers but I know this is not uncommon. The application team however could not find any indication of this in their logs so it was up to me to prove it.

As far as I am aware there are 2 options for retrospectively proving this at a granular level (please share any other options I’ve missed):

1) Query the database audit trail – DBA_AUDIT_SESSION
2) Mine the listener log

On this database no auditing was enabled so I was left with the listener log as my data source.

I knocked up a quick KSH/AWK script to get my data and then massaged it via various means. The output was quite pleasing so over the recent long UK Easter holiday I Googled some Perl commands and had some fun with it, this post is to share the script and an example chart from Microsoft Excel based on the incident I described above.

The script can be downloaded from – llog-miner.pl

Below is an example of how to call the script – it works on *nix and Windows. The second parameter can be used to filter log entries on the date, this example is matching any minute between “11:00″ and “12:59″.

C:\>perl llog-miner.pl
Usage: llog-miner.pl  [DD-MON-YYYY]
C:\>perl llog-miner.pl listener.log "04-APR-2012 1[12]" > llog.csv

This is the chart showing the logon spike – click to enlarge. You can see the connection spike at 12:56 of over 250 connection attempts.

Some caveats and improvements I may make in the future.

• By default the CSV file is fudged so Microsoft Excel won’t interfere with dates. To use with other tools you need to tweak this line “my $excelOutput = 1;” to use a value of “0″.
• Minutes where there are no connection attempts are not included in the output. I have drafted some code to fill in gaps but have kept it to myself for now as it’s too ugly to share (at the moment).
• There ought to be an option to exclude the client host to condense attempts from various application servers into a single column. I need to Google “Perl getopt” next I think.
• There ought to be an option to run from STDIN which would allow multiple listener logs to be processed in one hit.

I hope someone finds this useful and if not, well I found it entertaining if nothing else

Recently I was asked to make some investigation about the PL/SQL procedure running slowly. PL/SQL procedure does not contain any SQL or complicate math operation but for some reasons have unexpected delays in processing. After small investigation I localize the problematic statement as containing the join of two nested tables – “multiset union”.

I was not able to explain it and crate test case to get to the bottom of this issue.

CREATE OR REPLACE PACKAGE epani.multicast_pkg
  IS
     TYPE t_number_nt IS TABLE OF NUMBER; -- test nested table type
       v_new_number  t_number_nt; --second nested table
       v_number t_number_nt; --first nested table
     PROCEDURE init (num_init1 number
                                  , num_init2 number); -- procedure that populate first nested table
     PROCEDURE add_number_old; -- procedure that join nested table in old fashion
     PROCEDURE add_number_new; -- procedure that join nested tables with MULTISET
END;
/

CREATE OR REPLACE PACKAGE BODY epani.multicast_pkg
 IS
    PROCEDURE  init (num_init1 number  -- number of elements in the first table
                                  , num_init2 number) -- number of elements in the second table
    is
    begin
        SELECT num BULK COLLECT INTO v_number   FROM (
                              SELECT  LEVEL num  FROM DUAL CONNECT BY LEVEL <= num_init1);
        SELECT num BULK COLLECT INTO v_new_number  FROM (
                              SELECT  LEVEL num  FROM DUAL CONNECT BY LEVEL <= num_init2);
    end;

    PROCEDURE  add_number_old 
     IS
     BEGIN
          v_number.EXTEND(v_new_number.COUNT); -- allocate nested table 
        FOR i IN v_new_number.FIRST .. v_new_number.LAST
        LOOP
          v_number(v_number.LAST) := v_new_number(i);
        END LOOP;
     END add_number_old;

    PROCEDURE  add_number_new
     IS
     BEGIN
       v_number:=v_number multiset union v_new_number;
     END add_number_new;
END;
/

I have prepared few test cases
First : We join the small table to the big one

---We initialise the first nested table by significant number of rows and second by small number
SQL> exec EPANI.multicast_pkg.init(356000,5);
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.33
-- Run the old fashion join procedure
SQL> exec EPANI.multicast_pkg.add_number_old;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
-- the procedure does not really takes so long
SQL> exec EPANI.multicast_pkg.add_number_new;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.41
-- the procedure takes almost ½ of time to initially generate the table

Second: we join two relatively small tables but do it repeatedly 1000 times to get the meaningful timing

---We initialise the nested tables by small number of rows
SQL> exec EPANI.multicast_pkg.init(1000,100);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.05
-- We run pl/sql block using old fashion method
SQL> begin
  for i in 1..1000 LOOP
    EPANI.multicast_pkg.add_number_old;
  END LOOP;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.15
-- We run pl/sql block using new method
SQL> begin
  for i in 1..1000 LOOP
     EPANI.multicast_pkg.add_number_new;
  END LOOP;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:04:29.75

Third: We join two big tables

---We initialise the nested tables by significant number of rows
SQL> exec EPANI.multicast_pkg.init(100000, 100000);
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.97
-- Run the old fashion join procedure
SQL> exec EPANI.multicast_pkg.add_number_old;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.15
-- the procedure does not  takes measurable time
---We reinitialise the nested tables by significant number of rows
SQL> exec EPANI.multicast_pkg.init(100000, 100000);
PL/SQL procedure successfully completed.
Elapsed: 00:00:03.11
SQL> exec EPANI.multicast_pkg.add_number_new;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.28
-- the procedure takes almost extra 50% above the old method

Forth: We join big tables to the small one

---We initialise the nested tables by significant number of rows
SQL> exec EPANI.multicast_pkg.init(5, 356000);
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.08
-- Run the old fashion join procedure
SQL> exec EPANI.multicast_pkg.add_number_old;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.62
-- the procedure does takes measurable time
---We reinitialise the nested tables in the same fashion
SQL> exec EPANI.multicast_pkg.init(5, 356000);
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.27
SQL> exec EPANI.multicast_pkg.add_number_new;
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.07
-- the procedure takes almost extra 50% above the old method

We have proved that for all cases we got better performance on old fashion method in all cases, but why. What stands behind this degradation? The answer can be found if we rewrite our procedures in slightly different way.

CREATE OR REPLACE PACKAGE BODY epani.multicast_pkg
 IS
    PROCEDURE  init (num_init1 number  -- number of elements in the first table
                                  , num_init2 number) -- number of elements in the second table
    is
    begin
        SELECT num BULK COLLECT INTO v_number   FROM (
                              SELECT  LEVEL num  FROM DUAL CONNECT BY LEVEL <= num_init1);
        SELECT num BULK COLLECT INTO v_new_number  FROM (
                              SELECT  LEVEL num  FROM DUAL CONNECT BY LEVEL <= num_init2);
    end;

    PROCEDURE  add_number_old 
     IS
	v_result t_number_nt:= t_number_nt(); --resulting nested table
     BEGIN
        v_result.EXTEND(v_number.COUNT); -- allocate nested table 
        FOR i IN v_number.FIRST .. v_number.LAST
        LOOP
          v_result(v_number.LAST) := v_number(i);
        END LOOP;
        v_result.EXTEND(v_new_number.COUNT); -- allocate nested table 
        FOR i IN v_new_number.FIRST .. v_new_number.LAST
        LOOP
          v_result(v_number.LAST) := v_new_number(i);
        END LOOP;
     END add_number_old;

    PROCEDURE  add_number_new
     IS
	v_result t_number_nt:= t_number_nt(); --resulting nested table
     BEGIN
       v_result:=v_number multiset union v_new_number;
     END add_number_new;
END;
/

And repeat our tests

First : We join the small table to the big one

---We initialise the first nested table by significant number of rows and second by small number
SQL> exec EPANI.multicast_pkg.init(356000,5);
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.16
-- Run the old fashion join procedure
SQL> exec EPANI.multicast_pkg.add_number_old;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.93
-- the procedure does takes significantly longer
SQL> exec EPANI.multicast_pkg.add_number_new;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.64
-- faster by new method

Second: we join two relatively small tables but do it repeatedly 1000 times to get the meaningful timing

---We initialise the nested tables by small number of rows
SQL> exec EPANI.multicast_pkg.init(1000,100);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.04
-- We run pl/sql block using old fashion method
SQL> begin
  for i in 1..1000 LOOP
    EPANI.multicast_pkg.add_number_old;
  END LOOP;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.30
-- We run pl/sql block using new method
SQL> begin
  for i in 1..1000 LOOP
     EPANI.multicast_pkg.add_number_new;
  END LOOP;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.96
-- Faster by new method

Third: We join two big tables

---We initialise the nested tables by significant number of rows
SQL> exec EPANI.multicast_pkg.init(100000, 100000);
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.56
-- Run the old fashion join procedure
SQL> exec EPANI.multicast_pkg.add_number_old;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.48
-- the procedure does not  takes measurable time
---We reinitialise the nested tables by significant number of rows
SQL> exec EPANI.multicast_pkg.init(100000, 100000);
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.08
SQL> exec EPANI.multicast_pkg.add_number_new;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.40
-- slightly faster by new method

Forth: We join big tables to the small one

---We initialise the nested tables by significant number of rows
SQL> exec EPANI.multicast_pkg.init(5, 356000);
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.31
-- Run the old fashion join procedure
SQL> exec EPANI.multicast_pkg.add_number_old;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.58
-- the procedure does takes measurable time
---We reinitialise the nested tables in the same fashion
SQL> exec EPANI.multicast_pkg.init(5, 356000);
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.38
SQL> exec EPANI.multicast_pkg.add_number_new;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.50
-- slightly faster by new method

It looks like that for new set of test MULTICAST shows better performance than the handmade one. The answer is lighting in a scope of functionality. When we add one collection to the already existing the old one works perfectly but when we create the new collection as join of two old one and later assign it to the one of them the MULTICAST shows better efficiency. Multicast is able to resolve the vide scope of tasks. The developers should clearly see the difference between operating two collections or three collections and choose the appropriate method based on application requirements.

There is no shortage of information regarding how to install Oracle GoldenGate Monitor both in the Administrator’s Guide and on various websites. If you want a see screenshots of the GUI installation then Michael Verzijl has a step-by-step on his blog. However, when I came to perform my first installation I didn’t find complete instructions on how to set up HTTPS. If you’re a Java person then I imagine that the information provided in the instructions is sufficient, but if you’re not and have found yourself responsible for setting up Oracle GoldenGate Monitor because: 1) It’s called “Oracle…” and you know Oracle; and 2) Other people aren’t interested it doing it, then I hope you’ll find the information below useful.

You are going to need to use keytool and the steps below cover creating a “self-signed” certificate because in my case I simply want SSL/HTTPS rather than any guarantee that the site is what it claims to be.

Creating a Self-Signed Certificate

The suggested command is:

$ keytool -genkey -keyalg RSA -alias selfsigned -keystore keystore.jks -storepass <password> -keysize 2048

Sample output:

$ keytool -genkey -keyalg RSA -alias selfsigned -keystore keystore.jks -storepass easy_password -keysize 2048
What is your first and last name?
  [Unknown]:  GoldenGate Monitor
What is the name of your organizational unit?
  [Unknown]:  GoldenGate Support
What is the name of your organization?
  [Unknown]:  ORAsavon Limited
What is the name of your City or Locality?
  [Unknown]:  Leeds
What is the name of your State or Province?
  [Unknown]:  West Yorkshire
What is the two-letter country code for this unit?
  [Unknown]:  GB
Is CN=GoldenGate Monitor, OU=GoldenGate Support, O=ORAsavon Limited, L=Leeds, ST=West Yorkshire, C=GB correct?
  [no]:  yes

Enter key password for <selfsigned>
        (RETURN if same as keystore password):
$

At this point you will have a file name keystore.jks in your current directory and you are ready to install Oracle GoldenGate Monitor and configure it to only use SSL/HTTPS.

HTTPS Option During Installation

During the GUI installation you will get to a screen were you can select HTTP and/or HTTPS as well as the corresponding port number (see this image from Michael Verzijl’s step-by-step)

If you’re using the command line install (via -c option) then you’ll see the output below:

Enter ports for use by Oracle GoldenGate Monitor











      Select HTTP or HTTPS or both, and enter the ports that Oracle GoldenGate
      Monitor will listen on




Configure Monitor HTTP server?
Yes [y, Enter], No [n]
n
Configure Monitor HTTPS(secured) server?
Yes [y], No [n, Enter]
y
HTTPS port:
[5505]

Shutdown port:
[5501]

Please select the valid keystore you want use for Monitor server
To enable SSL, please use the java keystore utility to create a keystore, and then import the SSL certificate to the keystore. The installer copies the keystore to the Tomcat conf directory. Tomcat uses it for SSL authentication.
Keystore file:
[/<path to where you unpacked the installer>/Oracle_GoldenGate_Monitor_solaris_sparc_11_1_1_1_0.sh.29202.dir]
/<path to where you created the keystore>/keystore.jks

After this carry on with the installation, but at the end deselect “Start Oracle GoldenGate Monitor”.

If you’re using the GUI installer then it’s on this screen at this point in Michael Verzijl’s step-by-step.

If you’re using the command line install then the output below applies:

      To start Oracle GoldenGate Monitor manually, deselect Start Oracle
      GoldenGate Monitor, or accept the default to allow automatic startup.
      (Optional)




Start Oracle GoldenGate Monitor?
Yes [y, Enter], No [n]
n
Launch Oracle GoldenGate Monitor Web?
Yes [y], No [n, Enter]
n
View Readme?
Yes [y], No [n, Enter]
n
Finishing installation...

Setting Keystore Password in Tomcat Configuration

Once the installation has completed you need to modify server.xml (/tomcat/conf/server.xml) to add “keystorePass” to the following line:

<Connector SSLEnabled="true" clientAuth="false" keystoreFile="${catalina.base}/conf/monitor.jks" maxThreads="150" port="5505" protocol="HTTP/1.1" scheme="https" secure="true" sslProtocol="TLS"/>

Becomes:

<Connector SSLEnabled="true" clientAuth="false" keystoreFile="${catalina.base}/conf/monitor.jks" keystorePass="<java keystore password>" maxThreads="150" port="5505" protocol="HTTP/1.1" scheme="https" secure="true" sslProtocol="TLS"/>

After this you will/should be able to start Oracle GoldenGate Monitor with it only listening on HTTPS using:

$ <installation directory>/bin/monitor.sh start

It’s probably a little late in the day to do a post for an event that starts on Wednesday this coming week, but just in case…

Martin Bach and I will be doing a RAC Attack session at the Oracle User Group Norway’s Spring Seminar.

If you’re there and want some assistance setting up a RAC database on your laptop then we’ll be more than happy to help.

Oracle database processes are to many systems administrators just another service that runs on the machines they look after. Understandably sys admins like to be able to put some controls in to prevent these processes from consuming too many CPU resources, filling up the disks or hogging all the memory on the system. This can be done on UNIX and Linux systems with ulimits which control the resources available to your shell and the programs it starts.

You can check your ulimits that currently apply to you by running the command ulimit -a. On Linux it looks like this and it’s similar with AIX:

[ben@linux ~]$ ulimit -a
core file size          (blocks, -c) 0
data seg size           (kbytes, -d) 32768
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 23551
max locked memory       (kbytes, -l) 64
max memory size         (kbytes, -m) unlimited
open files                      (-n) 1024
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 32768
cpu time               (seconds, -t) unlimited
max user processes              (-u) 23551
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

You’ll note that I ran the above commands as my own user. Oracle makes some recommendations for the settings for the oracle user, generally found in the release notes or prerequisites for your platform. Because Oracle makes no recommendations about shell limits on a DBA account, the limits applying to you personally will often be much lower than those applying to the Oracle user.

So when might this be a problem? Let’s take how you start up your Oracle service. Here are two ways it’s possible to do it:

• Using srvctl, e.g. srvctl start database -d dbname
• Using SQL*Plus, e.g. sqlplus / as sysdba and then issue command startup.

The problem comes with the second method. When invoking Oracle using SQL*Plus, the shell limits that apply to all the Oracle processes are inherited from your shell. Therefore you need to make sure you’re logged in as the Oracle user. There are some subtleties around this: what happens if you use sudo, e.g. sudo -s -u oracle to invoke an Oracle user’s shell? On some systems you might find that your shell limits don’t change and you’re still stuck with the lower limits of your own shell: you can check by running ulimit -a. You may be setting yourself up for ORA-04030 errors; limiting the PGA memory area allowed for sorting data; and imposing other operating system limits.

This problem doesn’t exist if you invoke sudo su - oracle to change to the oracle user.

What happens if Oracle is already running and you’re not sure if it was started correctly? With Linux it is quite easy to find out, provided you have the right privileges. First get the process id of your smon process, in this case 4057:

[ben@linux ~]$ ps -ef | grep smon
root      3790     1  1 Feb05 ?        08:04:14 /u01/app/grid/11203/home_1/bin/osysmond.bin
grid      4057     1  0 Feb05 ?        00:02:02 asm_smon_+ASM1
oracle    4495     1  0 Feb05 ?        00:04:50 ora_smon_beverley1
ben       7468  7438  0 11:17 pts/0    00:00:00 grep smon

And then take a look in /proc/4057/limits:

[ben@linux 4057]$ sudo cat limits
Limit                     Soft Limit           Hard Limit           Units
Max cpu time              unlimited            unlimited            seconds
Max file size             unlimited            unlimited            bytes
Max data size             unlimited            unlimited            bytes
Max stack size            33554432             unlimited            bytes
Max core file size        unlimited            unlimited            bytes
Max resident set          unlimited            unlimited            bytes
Max processes             23551                23551                processes
Max open files            65536                65536                files
Max locked memory         unlimited            unlimited            bytes
Max address space         unlimited            unlimited            bytes
Max file locks            unlimited            unlimited            locks
Max pending signals       23551                23551                signals
Max msgqueue size         819200               819200               bytes
Max nice priority         0                    0
Max realtime priority     0                    0
Max realtime timeout      unlimited            unlimited            us

You can compare the above with the ulimits set for the oracle user.

With AIX it’s harder since there is no /proc filesystem. You have to run a debugger against the process. There’s a nice blog post on one method here, although it is not suitable for a production system because it interrupts the Oracle process and kills it afterwards.

So is that everything? Unfortunately not, ulimits don’t just affect the server processes like smon; they also affect server processes for connections using SQL*Plus. Below are two server processes running, each serving a connection from SQL*Plus, one for my ben user and another for the oracle user:

SQL> select spid, username, program from v$process where spid in (9351, 9798);

SPID			 USERNAME	 PROGRAM
------------------------ --------------- ------------------------------------------------
9798			 oracle 	 oracle@o112-c2n1.ora.pwk (TNS V1-V3)
9351			 ben		 oracle@o112-c2n1.ora.pwk (TNS V1-V3)

The below shows these processes:

SQL> !ps -ef | egrep '(9350|9351|9797|9798)'
ben       9350  7438  0 13:08 pts/0    00:00:00 sqlplus
oracle    9351  9350  0 13:09 ?        00:00:00 oraclebeverley1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle    9797  9762  0 13:34 pts/0    00:00:00 sqlplus
oracle    9798  9797  0 13:34 ?        00:00:00 oraclebeverley1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

As I am running Linux, I can easily compare the ulimits applying to processes 9351 and 9798:

[ben@linux 9351]$ sudo cat limits
Limit                     Soft Limit           Hard Limit           Units
Max cpu time              unlimited            unlimited            seconds
Max file size             unlimited            unlimited            bytes
Max data size             131072               131072               bytes
Max stack size            33554432             unlimited            bytes
Max core file size        0                    unlimited            bytes
Max resident set          unlimited            unlimited            bytes
Max processes             23551                23551                processes
Max open files            1024                 1024                 files
Max locked memory         65536                65536                bytes     
Max address space         unlimited            unlimited            bytes
Max file locks            unlimited            unlimited            locks
Max pending signals       23551                23551                signals
Max msgqueue size         819200               819200               bytes
Max nice priority         0                    0
Max realtime priority     0                    0
Max realtime timeout      unlimited            unlimited            us        

[ben@linux 9798]$ sudo cat limits
Limit                     Soft Limit           Hard Limit           Units
Max cpu time              unlimited            unlimited            seconds
Max file size             unlimited            unlimited            bytes
Max data size             131072               131072               bytes
Max stack size            33554432             unlimited            bytes
Max core file size        0                    unlimited            bytes
Max resident set          unlimited            unlimited            bytes
Max processes             131072               131072               processes
Max open files            131072               131072               files
Max locked memory         51200000000          51200000000          bytes     
Max address space         unlimited            unlimited            bytes
Max file locks            unlimited            unlimited            locks
Max pending signals       23551                23551                signals
Max msgqueue size         819200               819200               bytes
Max nice priority         0                    0
Max realtime priority     0                    0
Max realtime timeout      unlimited            unlimited            us

The differences are highlighted.

All this brings me onto the ORA-04030 problem, which can be related to the value of max data size. This parameter limits the amount of PGA memory that your session can use. Checking the ulimits is quick and easy, at least on Linux, so if you encounter such a problem this is one place you can easily check. It may save you from having to experiment with undocumented Oracle parameters like _pga_max_size which may not be the cause.

I won’t go into any details on ADRCI as others have already done a better job than I could. The key piece of information for this post is that the MMON process shoulders the responsibility for ADR purging. So what if there isn’t an MMON process?

Note: All ADRCI “show control” output on this page has been edited for readability

Below is the ADR purge details for my local listener. These settings were defined using “set control (SHORTP_POLICY = 168,LONGP_POLICY = 336)”. “168″ = 7 days and “336″ = 14 days.

adrci
set home diag/tnslsnr/n01/listener
show control

ADRID      SHORTP_POLICY LONGP_POLICY LAST_MOD_TIME              LAST_AUTOPRG_TIME LAST_MANUPRG_TIME
---------- ------------- ------------ -------------------------- ----------------- ----------------------------
1794992421 168           336          2012-01-25 20:41:09.052526

adrci> host
$ date

Thu Feb 16 21:26:16 GMT 2012

$ cd  /u01/app/oracle/diag/tnslsnr/n01/listener/alert
$ ls -ltr

total 30260
-rw-r----- 1 grid oinstall 10485934 Jan 25 03:45 log_1.xml
-rw-r----- 1 grid oinstall 10485779 Feb  4 03:11 log_2.xml
-rw-r----- 1 grid oinstall  9954308 Feb 16 21:43 log.xml

Notice that the “LAST_AUTOPRG_TIME” column is blank. It looks like this system is not being purged automatically. Also notice that the date of this test was Feb 16th and the oldest file in the “alert” directory is dated “Jan 25″. According to M.O.S note “Which Files Are Part Of SHORTP_POLICY And LONGP_POLICY In ADR? [ID 975448.1]” the “alert” directory is under LONGP_POLICY which is 14 days so the file “log_1.xml” should have been purged.

Below I force a manual purge

adrci> purge
adrci> host

$ cd  /u01/app/oracle/diag/tnslsnr/n01/listener/alert
$ ls -ltr

total 20004
-rw-r----- 1 grid oinstall 10485779 Feb  4 03:11 log_2.xml
-rw-r----- 1 grid oinstall  9960304 Feb 16 21:53 log.xml

It has now gone. And LAST_MANUPRG_TIME has been set to record the fact.

ADRID      SHORTP_POLICY LONGP_POLICY LAST_MOD_TIME              LAST_AUTOPRG_TIME LAST_MANUPRG_TIME
---------- ------------- ------------ -------------------------- ----------------- --------------------------
1794992421 168           336          2012-01-25 20:41:09.052526                   2012-02-16 21:53:51.761639

So my recommendation is that alongside ADR’s auto purging and your own maintenance on the alert.log & listener.log files in the “trace” directories you should also routinely call something like below:

for i in `adrci exec="show homes"|grep listener`;do
 echo "adrci exec=\"set home $i;purge\""
 adrci exec="set home $i;purge";
 # maybe check $? here
done

I noticed this M.O.S note recently too. I’ve never seen the symptoms but it’s worth taking a look.

ADR Auto Purge Does Not Work On All RAC Instances, ASM and Standby Databases [ID 1326983.1]

I was lucky enough to attend UKOUG Conference 2011 last year. I saw several great presentations, one of which was a presentation on troubleshooting RAC by Julian Dyke. During this presentation Julian commented that we need to watch out for SCAN listeners having their ADR home inside the Grid Infrastructure home instead of with the Database listeners in the ADR home under $ORACLE_BASE.

Well this was one of the few things in his presentation I was fit to comment on so I tried to attract Julian’s attention by waving my arm, sadly my timid nature meant I went unnoticed. So this blog post is me going public with my attempted interruption.

First an example of how the log location for SCAN listeners typically differs to that of Database listeners. I personally do not like this behaviour as I like my Grid Infrastructure home to have resonably stable size, which it can have once the log directory’s rotation is mature, however the ADR home for listeners is not cleared down automatically so you can end up with a directory growing unchecked.

[grid@n01 n01]$ $GRID_HOME/bin/lsnrctl status LISTENER|grep Log
Listener Log File         /u01/app/oracle/diag/tnslsnr/n01/listener/alert/log.xml
[grid@n01 n01]$ $GRID_HOME/bin/lsnrctl status LISTENER_SCAN3|grep Log
Listener Log File         /u01/app/grid/11203/ghome_1/log/diag/tnslsnr/n01/listener_scan3/alert/log.xml

And we know from the Oracle documentation that ADR_BASE is defined as:

Purpose
To specify the base directory into which tracing and logging incidents are stored when ADR is enabled.

Default
The default on the server side is ORACLE_BASE, or ORACLE_HOME/log, if ORACLE_BASE is not defined.

So… let’s check ORACLE_BASE for CRS resources:

[grid@n01 ~]$ $GRID_HOME/bin/crsctl stat res ora.LISTENER.lsnr -p|grep -i base
USR_ORA_ENV=ORACLE_BASE=/u01/app/oracle
[grid@n01 ~]$ $GRID_HOME/bin/crsctl stat res ora.LISTENER_SCAN3.lsnr -p|grep -i base
[grid@n01 ~]$

Notice above that the Database listener has $ORACLE_BASE set and the Scan listener does not. Hence the log location.

This can be controlled for Database listeners by using the SETENV switch for SRVCTL but this does not work for Scan listeners.

[grid@n01 ~]$ $GRID_HOME/bin/srvctl setenv listener -l listener_scan1 -t "ORACLE_BASE=/u01/app/oracle"
PRKO-3162 : The actions setenv, getenv, and unsetenv are not suppported for Single Client Access Name listener listener_scan1
PRCN-2066 : Failed to retrieve cluster listener for listener_scan1 because it has the type of Single Client Access Name Listener

There is another way of setting environment variables for cluster resources and that is via crsctl modify resource.

[grid@n01 ~]$ $GRID_HOME/bin/crsctl modify resource ora.LISTENER_SCAN3.lsnr -attr USR_ORA_ENV=ORACLE_BASE=/u01/app/oracle
[grid@n01 ~]$ $GRID_HOME/bin/crsctl stat res ora.LISTENER_SCAN3.lsnr -p|grep -i base
USR_ORA_ENV=ORACLE_BASE=/u01/app/oracle
[grid@n01 ~]$ $GRID_HOME/bin/srvctl stop scan_listener -i 3
[grid@n01 ~]$ $GRID_HOME/bin/srvctl start scan_listener -i 3
[grid@n01 ~]$ lsnrctl status  listener_scan3 | grep Log
Listener Log File         /u01/app/oracle/diag/tnslsnr/n01/listener_scan3/alert/log.xml

To my mind the whole thing would seem a little cleaner if there was an “SRVCTL SETENV SCAN_LISTENER” command.

After writing this post I thought I’d check around for other information to support this post and, lo and behold, there is a My Oracle Support article covering almost exactly what I’ve written above. The article also talks about using the LISTENER.ORA attribute ADR_BASE_LISTENER_SCANn to control this.

Diagnostic Destination For Listeners Is Under Grid_home/Log Instead Of Oracle_base/Diag [ID 1269109.1]

This small post is from nice and frighten world of partitions, specifically the case of partition pruning. If you are using partitions in your application there is fair chance that your database is quite big and performance demanding. If you are not working in classic Data Warehousing manner you have to care about DML statement performance. One of the basic rules of DML performance is less indexes you have and less index columns in existing indexes then higher is the DML performance. Thus one of the architectural DBA targets is to reduce the number of indexed columns by just most critical. Another axiom statement is that smaller indexes is easier to support and manage then big one, thus local indexes usually more preferable. The conclusion from two previous axiom is that probably most indexes on partitioned tables in your application is non-prefixed local indexes. Now we came to the topic of this post how oracle optimizer deal with such indexes and what we can do with it.

Lets create list base partitioned table transactions

Column Name	Comment
SERNO	Unique identifier
PARTITIONKEY	Partition Key
SGENERAL	Some indexed Field

And create two indexes TRANSACTIONS_PK (SERNO, PARTITIONKEY) and local partitioned non-prefixed index TRANSACTIONSI (SGENERAL).

Let’s start from the most common statement

EXPLAIN PLAN FOR
 SELECT SGENERAL, count(1)
 FROM transactions
 WHERE PARTITIONKEY=1610304 AND SGENERAL is not null
 GROUP BY SGENERAL;

PLAN_TABLE_OUTPUT
———————————————————————————————————————————————-
Plan hash value: 2338610280

Id	Operation	Name	Rows	Bytes	Cost	Time	Pstart	Pstop
0	SELECT STATEMENT		5	55	69978 (1)	00:14:00
1	HASH GROUP BY		5	55	69978 (1)	00:14:00
2	PARTITION LIST SINGLE		5340K	56M	69727 (1)	00:13:57	KEY	KEY
*3	TABLE ACCESS FULL	TRANSACTIONS	5340K	56M	69727 (1)	00:13:57	19	19

Predicate Information (identified by operation id):
—————————————————
3 – filter(“STGENERAL” IS NOT NULL)

Does it look like non-optimal? Optimizer has chosen to do FULL SCAN even if it has nice looking index TRANSACTIONSI. The problem is that partition pruning works only for prefixed indexes, that means you have to pay for support extra column in your index even if you do not need it because you clearly can get the partition name based from the identical rules on a table.

But luckily we have extended syntaxes. Uses it we can manually define the partition that should be used. In our case the partition name is equal to the partition key. Getting the partition name is very simple for list partitions but can be received in all other cases too.

EXPLAIN PLAN FOR
 SELECT SGENERAL, count(1)
 FROM TRANSACTIONS PARTITION ("0001610304")
 WHERE SGENERAL is not null
 GROUP BY SGENERAL;

PLAN_TABLE_OUTPUT
———————————————————————————————————————————————-
Plan hash value: 198159339

Id	Operation	Name	Rows	Bytes	Cost	Time	Pstart	Pstop
0	SELECT STATEMENT		4	20	10 (20)	00:00:01
1	SORT GROUP BY NOSORT		4	20	10 (20)	00:00:01
2	PARTITION LIST SINGLE		8890	44450	8 (0)	00:00:01	KEY	KEY
*3	INDEX FULL SCAN	TRANSACTIONSI	8890	44450	8 (0)	00:00:01	19	19

Predicate Information (identified by operation id):

—————————————————
3 – filter(“SGENERAL” IS NOT NULL)

This plan looks much better. According to optimizer estimations we can save 14 minutes on it. But there is even more attractive syntaxes for those who do not want to spend their time calculating the partition name

 EXPLAIN PLAN FOR
 SELECT SGENERAL, count(1)
 FROM TRANSACTIONS PARTITION FOR (1610304)
 WHERE SGENERAL is not null
 GROUP BY SGENERAL;

PLAN_TABLE_OUTPUT
———————————————————————————————————————————————
Plan hash value: 198159339

Id	Operation	Name	Rows	Bytes	Cost	Time	Pstart	Pstop
0	SELECT STATEMENT		4	20	10 (20)	00:00:01
1	SORT GROUP BY NOSORT		4	20	10 (20)	00:00:01
2	PARTITION LIST SINGLE		8890	44450	8 (0)	00:00:01	KEY	KEY
*3	INDEX FULL SCAN	TRANSACTIONSI	8890	44450	8 (0)	00:00:01	19	19

Predicate Information (identified by operation id):
—————————————————
3 – filter(“SGENERAL” IS NOT NULL)

The only problem with last syntax is that it is supported only from 11.2 version. The syntax was developed to support interval partitions but can be handy for all other types.

Does it looks that with new syntax we came to the kingdom of wealth and prosperity, where we can easily avoid prefixes on local indexes and still use the effective partition pruning? But it is not. The hidden rock is that the partition keys in PARTITION FOR clause could not be defined throw the variables like in WHERE clause. Below there are examples of such attempt

Firstly traditional approach

 DECLARE
 v_part number:=1610304;
 BEGIN
 FOR REC IN (SELECT SGENERAL, count(1) FROM TRANSACTIONS
 WHERE PARTITIONKEY=v_part AND SGENERAL is not null
 GROUP BY SGENERAL)
 LOOP
 NULL;
 END LOOP;
 END;
 /
 Elapsed: 00:12:42.05

It works but does not looks like very performance effective. Let’s go and try 10g approach in PL/SQL

DECLARE
 v_part varchar2(10):="0001610304";
 BEGIN
 FOR REC IN (SELECT SGENERAL, count(1) FROM TRANSACTIONS PARTITION (v_part)
 WHERE SGENERAL is not null
 GROUP BY SGENERAL)
 LOOP
 NULL;
 END LOOP;
 END;
 /

ERROR at line 2:
 ORA-06550: line 2, column 25:
 PLS-00201: identifier '0001610304' must be declared
 ORA-06550: line 2, column 10:
 PL/SQL: Item ignored

It looks like kind of expected behavior. You could not use table name as variable, why you should be able to use partition name. Personally I put a lot of hope on the last test with PARTITIONKEY reference

DECLARE<span style="color: #000000;">
v_part number:=1610304;
 BEGIN
 FOR REC IN (SELECT SGENERAL, count(1) FROM TRANSACTIONS
 PARTITION FOR (v_part)
 WHERE SGENERAL is not null
 GROUP BY SGENERAL)
 LOOP
 NULL;
 END LOOP;
 END;
 /

ERROR at line 1:
 ORA-14763: Unable to resolve FOR VALUES clause to a partition number
 ORA-06512: at line 4

It was the first time I see the error thus have a look into the documentation

ORA-14763: Unable to resolve FOR VALUES clause to a partition number

Cause: Could not determine the partition corresponding to the FOR VALUES clause.

Action: Remove bind variables and dependencies on session parameters from the values specified in the FOR VALUES clause.

Ha-Ha-Ha nothing change we still could not pass partition key values into query.

Summarizing oracle offer the mechanism that allows to use partition pruning on non-prefixed local indexes but have not support the syntaxes in pl/sql using binds. The usage of this syntaxes have sense only in case of big queries (e.g. reports) running on a table, when gains from effective execution plan prevail over the loses of dynamic sql.

This is a quick post to share a SQL statement I use a lot at work. The query mines the AWR tables (beware the licence implications) for a specific SQL ID and date/time range and shows a few choice statistics for each snapshot period.

awrsql.sql:

prompt enter start and end times in format DD-MON-YYYY [HH24:MI]
 
column sample_end format a21
select to_char(min(s.end_interval_time),'DD-MON-YYYY DY HH24:MI') sample_end
, q.sql_id
, q.plan_hash_value
, sum(q.EXECUTIONS_DELTA) executions
, round(sum(DISK_READS_delta)/greatest(sum(executions_delta),1),1) pio_per_exec
, round(sum(BUFFER_GETS_delta)/greatest(sum(executions_delta),1),1) lio_per_exec
, round((sum(ELAPSED_TIME_delta)/greatest(sum(executions_delta),1)/1000),1) msec_exec
from dba_hist_sqlstat q, dba_hist_snapshot s
where q.SQL_ID=trim('&sqlid.')
and s.snap_id = q.snap_id
and s.dbid = q.dbid
and s.instance_number = q.instance_number
and s.end_interval_time >= to_date(trim('&start_time.'),'dd-mon-yyyy hh24:mi')
and s.begin_interval_time <= to_date(trim('&end_time.'),'dd-mon-yyyy hh24:mi')
and substr(to_char(s.end_interval_time,'DD-MON-YYYY DY HH24:MI'),13,2) like '%&hr24_filter.%'
group by s.snap_id
, q.sql_id
, q.plan_hash_value
order by s.snap_id, q.sql_id, q.plan_hash_value
/

Nothing ground breaking and I’m sure many will have a similar script.

Below I have example output showing one of the scripts many successful outings, quite a dramatic plan change I’m sure you’ll agree.

SQL> @awrsql
enter start and end times in format DD-MON-YYYY [HH24:MI]
Enter value for sqlid: 1jjpo2i4b313g
Enter value for start_time: 15-NOV-2011
Enter value for end_time: 21-NOV-2011 13:00
Enter value for hr24_filter:

SAMPLE_END            SQL_ID        PLAN_HASH_VALUE EXECUTIONS PIO_PER_EXEC LIO_PER_EXEC  MSEC_EXEC
--------------------- ------------- --------------- ---------- ------------ ------------ ----------
15-nov-2011 TUE 08:00 1jjpo2i4b313g      3133159894     129629            0            5          0
16-nov-2011 WED 08:01 1jjpo2i4b313g      3133159894     115003            0            5         .1
17-nov-2011 THU 08:01 1jjpo2i4b313g      3133159894     115741            0            5          0
18-nov-2011 FRI 07:00 1jjpo2i4b313g      3133159894      30997            0            5         .1
18-nov-2011 FRI 08:00 1jjpo2i4b313g      3133159894      81034            0            5          0
21-nov-2011 MON 00:00 1jjpo2i4b313g       790865878         16     323091.6     323128.3    36905.8
21-nov-2011 MON 01:00 1jjpo2i4b313g       790865878         29     349676.2     349713.7    48387.2
21-nov-2011 MON 02:00 1jjpo2i4b313g       790865878         35     339474.6     339509.2    34057.7
21-nov-2011 MON 03:00 1jjpo2i4b313g       790865878         37     340934.6     340970.2    35899.4
21-nov-2011 MON 04:01 1jjpo2i4b313g       790865878         38     333469.1     333503.9    35450.8
21-nov-2011 MON 05:00 1jjpo2i4b313g       790865878         35     347559.3     347595.2    35231.8
21-nov-2011 MON 06:00 1jjpo2i4b313g       790865878         32     340224.8       340260    35208.3

I also like to use the query to track number of executions or LIO per execution over a longer time frame to see if either the frequency or individual impact of the SQL is changing over time. I can use the “hr24_filter” variable to do this, for example showing me all snapshots for hour “13″ over a whole month.