Dave Webster's Blog

Tinkering with Oracle

Archive for the ‘Uncategorized’ Category

SUBPROGRAM IDs for private procedures

Posted by anotherdavewebster on December 22, 2014

*** SPOILER ALERT : This Blog post ends with a question (it’s a story of failure) ***

I’ve been looking into some Active Session History data for a problem we encountered in some PL/SQL code in our production environment.  ASH is wonderful at telling us what SQL is running but not great at showing us which PL/SQL code is running.  There is a little hope by way of the data in PLSQL_ENTRY_OBJECT_ID, PLSQL_ENTRY_SUBPROGRAM_ID, PLSQL_ENTRY_OBJECT_ID, PLSQL_OBJECT_ID and PLSQL_SUBPROGRAM_ID.

The first two, the “ENTRY” values show the initial PL/SQL call, the top of the call stack.  They’re easy to interpret:

select * from dba_objects
where object_id = <PLSQL_ENTRY_OBJECT_ID>

shows the package (specification, not body) and:

select * from dba_procedures
where object_id = <PLSQL_ENTRY_OBJECT_ID> and subprogram_id = <PLSQL_ENTRY_SUBPROGRAM_ID>

shows the procedure.  Obviously the second query shows you the package and the procedure, so, for the ENTRY columns, the former query isn’t really necessary.

My problem came when I wanted to find out which procedures were being referenced by PLSQL_SUBPROGRAM_ID as these could be private procedures and THEY DON”T EXIST IN DBA_PROCEDURES.  So the question is, how do I find out which private procedure is being executed?

Read the rest of this entry »

Posted in Uncategorized | 2 Comments »

UK OUG TECH 2013

Posted by anotherdavewebster on September 30, 2013

Well ladies and gentlemen, it seems that I’ll be sharing my thoughts on different ways of streaming data from the Oracle database.  The material I’ll be presenting will include and extend on my posts Polling for new data and Polling for new data: Part 2.  Come and see me here and we can have a good old chat on the subject.

Posted in Uncategorized | Leave a Comment »

Wakey Wakey Mr Webster

Posted by anotherdavewebster on August 1, 2013

Has it really been two and a half years since I last posted? In that time I have changed roles a few times and travelled the world a little. I know it’s no excuse but there you have it. The good news is that while I’ve been idle, we’ve got ourselves onto Oracle 11 which means Nigel and I have better toys to play with. I have a few posts in mind and I reckon I’ll kick off with a general ramble about getting data from Oracle in to Java. Let’s see how long it takes me to do the first real post.

Posted in Uncategorized | Leave a Comment »

“What’s a bind variable?”

Posted by anotherdavewebster on August 26, 2010

It would be reasonable to assume that everything that could ever be written about bind variables has already been written. Certainly regarding the concept of them and why they are a good thing anyway. A little while back, I was digging around on a system which was suffering from very high parse rates. I did the normal stuff and took a look in v$sql:

SELECT   ( SELECT username
            FROM dba_users
           WHERE user_id = parsing_user_id ) username
       , parsing_user_id
       , SUBSTR( sql_text, 1, 30 )
       , COUNT( * )
       , MAX( TO_DATE( first_load_time, 'YYYY-MM-DD/HH24:MI:SS' ) )
       , MAX( last_active_time )
    FROM v$sql
GROUP BY parsing_user_id, SUBSTR( sql_text, 1, 30 )
  HAVING COUNT( * ) > 5
ORDER BY 4 DESC

Sure enough, I found a couple of statements with many thousands of variations, differing only by a handful of source table names and many different literal values. A quick look at the code confirmed that we had a piece of dynamic SQL (to manage a few different source tables) which concatenated a literal value into the SQL string. Something along the lines of:

execute immediate 'insert into table_a select * from ' || l_tab_name || ' where id = ' || l_id;

“Great stuff” I thought, I’ll go and find the developer who wrote that piece of code and ask him to switch to using a bind variable for the id but obviously retain the dynamic nature of the source table name. So I wrote him a quick eMail asking him to use a bind variable and the response rather surprised me. When I tell people the story, I ask them what they think the most worrying answer would be. To a man, they all go for “What’s a bind variable?”. That would indeed be a worrying response, but not unprecedented. The response I actually got was “I have”.

Posted in Uncategorized | 2 Comments »

Deterministic functions sometimes are Deterministic, even on 10.2

Posted by anotherdavewebster on August 24, 2010

A few years back, some colleagues of mine were bemoaning the fact that functions created as DETERMINISTIC weren’t actually implemented as such in 10.2. Just to be clear, this is no longer the case with Oracle 11. DETERMINISTIC functions actually are deterministic:

CREATE OR REPLACE FUNCTION non_det_fn( i_parameter IN NUMBER )
   RETURN NUMBER
IS
BEGIN
   DBMS_OUTPUT.put_line( 'Called non_det_fn with i_parameter = ' || i_parameter );
   RETURN i_parameter;
END;

CREATE OR REPLACE FUNCTION det_fn( i_parameter IN NUMBER )
   RETURN NUMBER DETERMINISTIC
IS
BEGIN
   DBMS_OUTPUT.put_line( 'Called det_fn with i_parameter = ' || i_parameter );
   RETURN i_parameter;
END;

DECLARE
   l_result   PLS_INTEGER;
BEGIN
   dbms_output.put_line('Standard function');
   FOR i IN 1 .. 5 LOOP
      l_result    := non_det_fn( 1 );
   END LOOP;

   dbms_output.put_line('Deterministic function');
   FOR i IN 1 .. 5 LOOP
      l_result    := det_fn( 1 );
   END LOOP;
END;

You get pretty much what you would expect from dbms_output:

Standard function
Called non_det_fn with i_parameter = 1
Called non_det_fn with i_parameter = 1
Called non_det_fn with i_parameter = 1
Called non_det_fn with i_parameter = 1
Called non_det_fn with i_parameter = 1
Deterministic function
Called det_fn with i_parameter = 1

On 10.2, Oracle doesn’t yet implement the deterministic functionality and we get:

Standard function
Called non_det_fn with i_parameter = 1
Called non_det_fn with i_parameter = 1
Called non_det_fn with i_parameter = 1
Called non_det_fn with i_parameter = 1
Called non_det_fn with i_parameter = 1
Deterministic function
Called det_fn with i_parameter = 1
Called det_fn with i_parameter = 1
Called det_fn with i_parameter = 1
Called det_fn with i_parameter = 1
Called det_fn with i_parameter = 1

Now this is not news of course but I did stumble on a rather surprising case where Oracle did implement the functionality in 10.2:

SELECT non_det_fn( mod_number )
     , det_fn( mod_number )
  FROM ( SELECT    MOD( ROWNUM, 3 ) mod_number
              FROM DUAL
        CONNECT BY ROWNUM <= 10 );

dbms_output gives us:

Called non_det_fn with i_parameter = 1
Called det_fn with i_parameter = 1
Called non_det_fn with i_parameter = 2
Called det_fn with i_parameter = 2
Called non_det_fn with i_parameter = 0
Called det_fn with i_parameter = 0
Called non_det_fn with i_parameter = 1
Called non_det_fn with i_parameter = 2
Called non_det_fn with i_parameter = 0
Called non_det_fn with i_parameter = 1
Called non_det_fn with i_parameter = 2
Called non_det_fn with i_parameter = 0
Called non_det_fn with i_parameter = 1

It’s not the same as the PL/SQL implementation in 11.1 but it’s certainly more than I was expecting and may well come in useful at some point. Of course, you can make all functions “deterministic” by calling them from Scalar Subqueries but that’s another matter…

Posted in Uncategorized | Leave a Comment »

Starter for 10

Posted by anotherdavewebster on August 9, 2010

The pressure of what to open with!  I won’t lie to you, I have a list of topics I reckon would make interesting reading as I wanted to get some idea as to whether or not I should even start this blog. Should I start with the first topic I thought of or the one I consider to be the best?  I want the first one to be fairly interesting but I don’t want to start with the best I have and then fail to maintain that level.  Something in the middle then.  Something which doesn’t take too long to write.  But now the pressure is even more intense.  You’re all going to know that I gave this real thought.  I have to go and lie down….

Posted in Uncategorized | Leave a Comment »