Oracle Documentation and Advantages of Reading Other Resources

After I published the first post of the series (#1, #2) I received a private request to talk a bit more about 11.2-to-19c upgrade. Considering that frankly speaking that's the most common upgrade case nowadays (well, one long-term release to the other one!) I can definitely understand the importance of real-world experience for anybody who plans to do the same.

So, here is the story - or to be precise two stories.

1. Production/Stage/External Test - these ones were handled by our partner DBAs, but the idea was pretty straightforward: copy cold backup to the new environment + run in-place upgrade (non-CDB). Same OS (Linux-to-Linux), preserving of character sets etc. I.e. everything is well-documented and clear. So, nothing much to write about.
2. Development and Internal Test - that one was a real head-scratcher that I'd like to share with everybody. The task was as follows:
• Starting points: Windows 2008 Server with 11.2.0.4 database and Win1252 charset, Windows 2012 Server with 19c database win Win1252 charset (non-CDB). Each DB is about 200 GB
• Required destination: Amazon AWS Linux instance with 19c database with two environments as PDBs that would allow creating ALUTF32 PDBs if needed.

As you can see, a lot of transformation and less-then-common transfer path. One of the reasons - you just can't install 19c database to run in-place upgrade using AutoUpgrade and other nice things on Windows 2008 server. So, with the help of our system administrators I came up with the plan that eventually lead to success. And that's where "other-then-documentation" resources really helped - some pieces of knowledge I took from Tim Hall's Oracle-Base, some pieces are from Mike Dietrich, some pieces from other people just posting their ideas and experiences in their blogs and various online resources. Overall, without the community I would experiment forever! And that's why I like so much to be part of this knowledge-sharing environment - and try to contribute myself.

I started from the bottom -  discussed with sysadmins a good and reliable way to moving something to AWS. The answer I've got was interesting: there was a simple  process to port existing virtual machine from our internal cloud to AWS. That made my life much simple - it meant that we could build a local VM, play with it as much as needed (with a very easy way to create multiple saverpoints/restore to any point etc.) and eventually declare it ready to be transferred. Ok, admins got Oracle Linux ISO and made me in vSphere a new server - now I've got a destination.

Next step was to see whether my understanding of OS internal was true and it was possible to move PDB from Windows to Linux without any major hustle... To do that I first converted my 19c instance from non-CDB to a PDB within newly created PDB. That was just a couple of commands:

-- non-CDB instance

shutdown;

startup open read only

/

BEGIN

  DBMS_PDB.DESCRIBE(

    pdb_descr_file => 'f:\oradata\test01.xml');

END;

/

shutdown immediate

/

-- CDB instance

CREATE PLUGGABLE DATABASE test01 

using 'f:\oradata\test01.xml'

NOCOPY 

tempfile reuse 

/

ALTER SESSION SET CONTAINER=test01;

@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql;

ALTER PLUGGABLE DATABASE OPEN;

Now it was time to move PDB to Linux box, that already had CDB configured as AL32UTF... And after not finding the answer on whether file_name_convert operator can change Windows-style file name to Linux-style I just went into moveText.xml file and manually updated all locations to which I transferred  PDB files (btw, later I tested the operator - and I couldn't make it do what I wanted, so, hint: sometimes manual approach is your choice ;) )

-- Windows CDB

ALTER PLUGGABLE DATABASE test01 CLOSE;

ALTER PLUGGABLE DATABASE test01 UNPLUG INTO 'F:\moveTest.xml';

-- Linux CDB

CREATE PLUGGABLE DATABASE test01 

using '/home/oracle/test01_updated.xml' -- Linux locations

NOCOPY 

tempfile reuse 

/

ALTER SESSION SET CONTAINER=test01;

ALTER PLUGGABLE DATABASE OPEN;

The last command finished - but with the warning. It was time to review existing violations

select *

from pdb_plug_in_violations

where name = 'TEST01'

and status !='RESOLVED'

The most only errors I found was the mismatch of patches - and that was simply fixed by running datapatch - verbose on the Linux side: the system easily rolled back all of the incorrect patches within PDB and placed what was needed. Seemingly the system was ready to operate - I could query data, run some sample procedures... But the application didn't start! After some digging I found a somewhat unexpected reason - even though you could move Windows PDB to Linux, if you are using PL/SQL objects with Native compilation - you have to recompile everything (well, makes sense, different platforms = different low-level code!). The following loop did that:

 begin

    for c in (

        select owner, name, type

        from dba_plsql_object_settings

        where plsql_code_type !='INTERPRETED'

        )

    loop

        begin

            if c.type = 'PACKAGE BODY' then

                execute immediate 'alter '||c.type||' '                                   ||c.owner||'.'||c.name||' compile body';

            else

                execute immediate 'alter '||c.type||' '                                   ||c.owner||'.'||c.name||' compile';

            end if;

        exception

            when others then null;

        end;

    end loop;

end;

That was easy enough - so I decided to replicate exactly the same pattern for the main development database (that was 11.2.0.4). So, in my Test database I dropped PDB Test01 with all datafiles (I had to do it because Dev files names overlapped with Test01 and I didn't want to shuffle anything around) and used good-old-and tried approach:

1. Original DB
• Create PFILE from SPFILE
• Run preupgrade on the source and follow its recommendations
• Shutdown the instance
• Transfer files to the new location preserving the file structure (as for the cold backup):
• ORADATA\ora11g folder
• FAST_RECOVERY AREA\ora11g folder with all corresponding directories
• PFILE
• Control files from other locations (btw, you can integrate them into the same folder by modifying PFILE)
2. Server with 19c
• Create new instance:
• orapwd file=? password=? format=12 (lower format if you want simple password for now)
• oradim
• startup upgrade
• Upgrade via dbupgrade utility
• Run post-fixes, including DST upgrade
• Recompile whatever needed and resolve obscure cases if exist (Java classes, SDO_Geometry etc.)

Now I was pretty exactly where I started with Test environment - non-CDB instance + empty container. So, the following steps are exactly above: convert instance into PDB, plug it in as Dev01, upgrade, fix PL/SQL native-compiled objects. That's all, folks - two environments became one on the different platform! After that it was just lift-and-shift to AWS - and we've got our new environment.

Summary: if you can use somebody else's experience - use it! Oracle documentation, especially about upgrades is huge, but sometimes you may be looking for something very specific and it may be hard to detect exactly your use-case in the superset of the knowledge. 

P.s. VM Snapshots are your saviors (that's why I love the cloud). You can't imagine how many various issues I've got in process - and without snapshot recovery it would've take wa-a-a-a-y longer to do anything meaningful.

Oracle Documentation and Dangers of Not Reading It

In addition to yesterday's post - a bit of old-timer knowledge share: if you see some features for years, you have a tendency to assume how they work. And often enough to your own surprise "the owls are not what they seem" (c) :)

My favorite story here is about hierarchical queries with CONNECT-BY clause. For years I was wondering why every generation of developers was having various performance issues writing such queries... Recently, while working on the presentation about recursive structures in the database I came to realization of the following: people look up code snippets without going any further!

Let's take a basic problem based on HR schema - list all employees working for the specified department in the hierarchical order starting from its manager. A lot of junior developer would write something like that:

select *
from hr.employees
where department_id = 100
start with job_id like '%MGR%' 
connect by prior employee_id = manager_id

Result of the query would be functionally correct - so, developers would start using that pattern... And eventually (when they would start working with large data sets!) that code becomes the source of the major performance degradation! Here is why - let's take a look at the explain plan:

------------------------------------------------------------------------------

| Id  | Operation                                | Name      | Rows  | Bytes |

------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                         |           |    34 |  4522 |

|*  1 |  FILTER                                  |           |       |       |

|*  2 |   CONNECT BY NO FILTERING WITH START-WITH|           |       |       |

|   3 |    TABLE ACCESS FULL                     | EMPLOYEES |   107 |  7383 |

------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

1 - filter("EMPLOYEES"."DEPARTMENT_ID"=100)

2 - access("EMPLOYEES"."MANAGER_ID"=PRIOR "EMPLOYEES"."EMPLOYEE_ID")

       filter("JOB_ID" LIKE '%MGR%')

Even though we have lots of indexes on the table, we've got FTS - and if we look closer, we will notice that our department filter was applied at the very end. It seems that Oracle first walked the whole table hierarchically - and after that threw out all rows that do not belong to the department #100!

And indeed that's exactly what Oracle documentation has been telling us for many years:

Indeed non-Join predicates are evaluated only afterwards. In a lot of cases it indeed makes sense - depending on the conditions. But in THIS case, where we know (and that's the key knowledge that Oracle is missing) that hierarchies never cross departments - although we forgot to tell Oracle about it! So, no wonder, the result is les then optimal.

How to fix it? There are two approaches to solve this problem that both significantly improve performance, but provide slightly different executions plans.

Option #1 is to use in-line views (to be fair, that's my favorite approach, because it makes code way more readable):

select *

from (select *

      from hr.employees

      where department_id = 100)

start with job_id like '%MGR%'

connect by prior employee_id = manager_id

-------------------------------------------------------------------------------------

| Id  | Operation                               | Name              | Rows  | Bytes |

-------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                        |                   |     2 |   266 |

|*  1 |  CONNECT BY NO FILTERING WITH START-WITH|                   |       |       |

|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED   | EMPLOYEES         |     6 |   414 |

|*  3 |    INDEX RANGE SCAN                     | EMP_DEPARTMENT_IX |     6 |       |

-------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - access("EMPLOYEES"."MANAGER_ID"=PRIOR "EMPLOYEES"."EMPLOYEE_ID")

       filter("EMPLOYEES"."JOB_ID" LIKE '%MGR%')

   3 - access("DEPARTMENT_ID"=100)

As you can see here, explicitly filtering the department #100 in the in-line view immediately dropped number of processed rows and bytes - exactly as needed.

Option #2 is to tell Oracle to start only from rows that belong to the department #100:

select *

from hr.employees

start with job_id like '%MGR%' and department_id = 100

connect by prior employee_id = manager_id

-----------------------------------------------------------------------------------

| Id  | Operation                             | Name              | Rows  | Bytes |

-----------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                      |                   |     7 |   931 |

|*  1 |  CONNECT BY WITH FILTERING            |                   |       |       |

|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED | EMPLOYEES         |     1 |    69 |

|*  3 |    INDEX RANGE SCAN                   | EMP_DEPARTMENT_IX |     6 |       |

|   4 |   NESTED LOOPS                        |                   |     6 |   492 |

|   5 |    CONNECT BY PUMP                    |                   |       |       |

|   6 |    TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES         |     6 |   414 |

|*  7 |     INDEX RANGE SCAN                  | EMP_MANAGER_IX    |     6 |       |

-----------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

--------------------------------------------------- 

   1 - access("EMPLOYEES"."MANAGER_ID"=PRIOR "EMPLOYEES"."EMPLOYEE_ID")

   2 - filter("JOB_ID" LIKE '%MGR%')

   3 - access("DEPARTMENT_ID"=100)

   7 - access("connect$_by$_pump$_002"."prior employee_id "="MANAGER_ID")

It has slightly higher cost an somewhat more complex execution plan - but that way you still have better performance than FTS alternative.

Summary: please, beware - solutions that work may not scale if you don't understand what is going under the hood! Read those manuals, please!

Oracle Documentation and Dangers of the AI

Earlier today I presented a webinar for NYOUG on one of my favorite topics - building SQL queries on the fly (btw, if anybody interested - slides and video will recording will be available a bit later):
https://nyoug.org/event/dynamic-mechanisms-of-query-building/

And during the presentation a recent in-house story came to my mind - based on my own remark. I said something like: "If you are using advanced functionality, you really need to know the features you are planning to use - not just copy/paste code snippets from ChatGPT or any other preferred AI tool" 

The statement above is perfectly true - and I even got an illustration (kind-of) for it based on the true "mini-scare" that happened in our office.

Here is the story. For many years a lot of my friends were laughing at me when I was telling that one of the biggest (government) projects that is being supported by Dulcian is still on Oracle 11.2.0.4. Finally this year our partners responsible for the administering the system got it to 19c. All is good - but for some internal reasons the production environment is still running with  "compatible=11.2.0.4.0"!

Of course, for us (i.e. development shop) it is a major restriction - and I've been very vocal about finally moving that parameter to "19.0.0". Couple of weeks ago in the internal communication I've got a bit panicky email from one of my colleagues: "Depending on how I ask an AI get different answers whether downgrade is possible! Help!" + a couple of screenshots:

#1 Gemini (Google)

#2 ChatGPT

As you can see, one platform says clear "NO" and the other one says that it is clearly possible, but there may be some impact that needs to be evaluated.

After hours of research I can say the following - Gemini is closer to the real answer than ChatGPT. Even though there are empirical evidences that it is possible in some circumstances (different combinations of versions) to lower compatibility parameter, it is strongly not recommended. So much not recommended that in 19c docs it is explicitly written that you cannot go back to 11.2.0.4 at all:

Summary: be VERY careful when you use various AI tools - it could be a good starting point, but without going to the core references you cannot be sure that you are getting real answers. Buyers, beware! :)

 

The Power of Proactive Monitoring. Part 6

 And talking about benefits... Sometimes if you get advanced monitoring features you can use provided information in a less-then-expected way. Here is one of my favorite examples.

As a lot of people know, some vendors provide their PL/SQL packages in a wrapped style. Also, as a lot of people know, if is often hard to get vendors to accept that something is wrong with their code (rather than with the way this code is being used).

Imagine, you have a slow-running module that takes tables/columns and converts it into formatted CLOB - and before talking to the vendor I want to collect as much data as I can:

SQL> DECLARE
2    v_CL CLOB;
3 BEGIN
4    dbms_hprof.start_profiling ('IO','HProf_Check.txt');
5    v_cl :=wrapped_pkg.f_getdata_cl('object_name','test_tab');
6    dbms_hprof.stop_profiling;
7 END;
8 /

I know that the table has 50k rows - and I see that there is 50k calls to DBMS_LOB.WRITEAPPEND! Yes, I can see what happens inside of the wrapped code - big surprise! Now I have a real evidence that the wrapped code is not efficient: whoever wrote it uses row-by-row approach.

As I wrote long-time ago, LOB operations are expensive (even LOB variables above 32K size are not real variables, but a segment in TEMP tablespace) and causing a lot of resource to be used. I.e. original code is something like that:

FUNCTION f_getData_cl(i_column_tx VARCHAR2, i_table_tx VARCHAR2) RETURN CLOB IS

    v_cl CLOB;

    v_tx VARCHAR2(32767);

    v_cur SYS_REFCURSOR;

BEGIN

    dbms_lob.createTemporary(v_cl,false,dbms_lob.call);

    OPEN v_cur FOR 'SELECT '||

                   dbms_assert.simple_sql_name(i_column_tx)||' field_tx'||

                   ' FROM '||dbms_assert.simple_sql_name(i_table_tx);

    LOOP

        FETCH v_cur into v_tx;

        EXIT WHEN v_cur%notfound;

        dbms_lob.writeAppend(v_cl,length(v_tx)+1,v_tx||'|');

    END LOOP;    

    CLOSE v_cur;

    RETURN v_cl;

END;

But if you use PL/SQL variable as a buffer, you can drastically decrease the number of LOB operations. True, it complicates your code - but gains are huge!

FUNCTION f_getData_cl(i_column_tx VARCHAR2, i_table_tx VARCHAR2) RETURN CLOB IS  

    v_cur SYS_REFCURSOR;

    v_cl CLOB;

    

    v_tx VARCHAR2(32767);

    v_buffer_tx VARCHAR2(32767);

    

    PROCEDURE p_flush IS

    BEGIN

        dbms_lob.writeappend(v_cl,length(v_buffer_tx), v_buffer_tx);

        v_buffer_tx:=null;

    END;

    PROCEDURE p_add (i_tx VARCHAR2) IS

    BEGIN

        IF length(i_tx)+length(v_buffer_tx)>32767 THEN

            p_flush;

            v_buffer_tx:=i_tx;

        ELSE

            v_buffer_tx:=v_buffer_tx||i_tx;

        END IF;

    END;    

BEGIN

    dbms_lob.createTemporary(v_cl,true,dbms_lob.call);

    OPEN v_cur FOR 

        'SELECT '||

            dbms_assert.simple_sql_name(i_column_tx)||' field_tx'||

        ' FROM '||dbms_assert.simple_sql_name(i_table_tx);        

    LOOP

        FETCH v_cur into v_tx;

        EXIT WHEN v_cur%notfound;

        p_add(v_tx);        

    END LOOP;

    p_flush;

    

    CLOSE v_cur;

    RETURN v_cl;

END;

On a reasonably large data set that second code will buy back a lot of IO operations - really a lot! I strongly recommend you to run those cases by yourself - only in that case you will believe the performance gains :) But if you are too, well, busy - I will get stats in the next post of these series :)

The Power of Proactive Monitoring. Part 5

Something worth mentioning while talking about Oracle Hierarchical Profiler is that its scope is a database session. So, if your middle-tier logic has multiple database calls within established connection, you can benefit from the profiler data. To be fair, it has a number of restrictions as I show further down, but still it can be useful.

Here is a basic example that shows "both sides of the coin":

String sql = 
  "begin dbms_hprof.start_profiling(location=>'IO',filename=>'Case1a.txt'); end;";
CallableStatement stmt = conn.prepareCall(sql);
stmt.execute();

PreparedStatement stmt =
conn.prepareStatement("select listagg(owner_tx,',') within group(order by 1) r1 \n" +
  "from (select distinct  scott.f_change_tx(owner) owner_tx\n" +
  "    from scott.test_tab) A ");
stmt.execute();

stmt =
conn.prepareStatement("select listagg(owner_tx,',') within group(order by 1) \n" +
   "from (select distinct  scott.f_change_tx(owner) owner_tx\n" +
   "    from scott.test_tab) B ");
stmt.execute();

sql = "begin dbms_hprof.stop_profiling; end;";
stmt = conn.prepareCall(sql);
stmt.execute();

As you can see, the code is running between start and stop functionally same queries with a slight modification (so SQL IDs wouldn't match). Both of those SQL statements would cause 50k of function calls. And that scenario is more probable in the real life - reasonably few development environments have established PL/SQL APIs as their communication mechanisms between layers (to my huge sorrow, I am a big fan of "smart DB" approach). 

But would they be shown correctly? As I spoilered above - not exactly:

Since there is no "root" PL/SQL entry point, HProf gets confused and merges both SQL calls into a single one - you see 100k function calls - i.e. you can get at least some stats on what is going on after the root object is being called, but no SQL ID, no clear info about the root element.

What is interesting, the same behavior can be replicated via SQL*Plus:

SQL> exec dbms_hprof.start_profiling 

  2          (location=>'IO',filename=>'Case1b_SQLPlus.txt');

SQL> select listagg(owner_tx,',') within group (order by 1) r1

  2  from (select distinct  scott.f_change_tx(owner) owner_tx

  3      from scott.test_tab a);

...

SQL> select listagg(owner_tx,',') within group (order by 1)

  2  from (select distinct  scott.f_change_tx(owner) owner_tx

  3      from scott.test_tab b);

...

SQL> exec dbms_hprof.stop_profiling;

As you can see, no SQL ID, and all function calls are being merged.

Summary of this part: unless you have PL/SQL APIs you may not enjoy the whole power of HProf, but still you can benefit from some of its provided information

The Power of Proactive Monitoring. Part 4

 In the previous post I introduced one of my favorite performance debugging tools - Hierarchical Profiler. Since Oracle engineers were so proactive that instrumented SQL and PL/SQL engines with a clear mechanism to understand who calls whom inside of the database, it is just great that we (as developers) can have access to the same level of internal information. That information indeed tells us complete and true story of what is happening - not guesses, not estimates, real fingerprints of every involved element.

Now let's get a bit more real and look at the problem that I bet a lot of people encounter if they start using user-defined PL/SQL functions within SQL - it is a very powerful technique, but it also can cause a lot of damage if used inappropriately/without understanding of the internals.

Imagine the following scenario:

1. Our help-desk started to get a lot of performance complains related to the same module 
2. Corresponding developer checked 10046 trance and couldn't find anything suspicious
3. The ticket is elevated to a senior performance expect (well, me :) )

And the very first thing I notice about the core query is that is contains a user-defined PL/SQL function. I immediately ask the developer - do you KNOW how many times that function is executed during the normal activity? The answer I've got was: "Well, it shouldn't be too much..."

Let's pause for a sec. We are soo accustomed to Cost-Based optimizer to do various miracles - but the database engine works only as good as data we provided. And it is important to check whether its logic matches ours. Here was the clear case of my developer "assume" instead of "check". So, the right solution is to get some hard statistics: I wrapped the suspect SQL call into HProf start/stop (below is a simplified case):

SQL> exec dbms_hprof.start_profiling ('IO', 'HProf_Case1.txt');
SQL> declare
  2      v_tx varchar2(32767);
  3  begin
  4      select listagg(owner_tx,',') within group (order by 1)
  5      into v_tx
  6      from (
  7          select distinct scott.f_change_tx(owner) owner_tx
  8          from scott.test_tab
  9          );
 10  end;
 11  /
SQL> exec dbms_hprof.stop_profiling;

Just to be clear - the table TEST_TAB has 50000 rows with only 26 distinct owners, and the function F_CHANGE_TX does basic formatting, nothing special... The HPROF report surprised everybody - 50K calls to the function!

This means that time was wasted on a very cheap function which was just fired lots and lots of times, because original developer just guessed the query behavior. He knew that the function would do basic formatting of the repeated value, so the output always will be the same - but he forgot to tell that to Oracle. And without that information the database fired mentioned above function for every row!

The solution was pretty simple - the query was rewritten to help CBO:

SQL> exec dbms_hprof.start_profiling ('IO', 'HProf_Case1_fix.txt');

SQL> declare
  2      v_tx varchar2(32767);

  3  begin

  4      select listagg(owner_tx,',') within group (order by 1)

  5      into v_tx

  6      from (

  7          select  scott.f_change_tx(owner) owner_tx

  8          from (select distinct owner 
  9                 from scott.test_tab)

 10           );

 11  end;

 12  /

SQL> exec dbms_hprof.stop_profiling

And here is a proof: 26 function calls, overall - 28 times faster!

IMHO, that was simple enough and straightforward enough illustration of how simple it is to find a problem if you have the right tool! And it is also a great illustration of the power of knowing something (rather than guessing)
 

The Power of Proactive Monitoring. Part 3

After talking for a while about some abstract ideas - let's get a bit more technical and a bit closer to Oracle. Before we really talk about the "proactive" part - we should be a bit more realistic. In about 90% of cases the need for monitoring is only being recognized after the fact that something is wrong. As a result the problem of finding "who"who's-done-it" is complicated by not having that much forensic data to work with.

Luckily, in Oracle technology space some of the things are being recorded by Oracle background processes no matter what, and ASH / AWR could help at least to narrow down the area to dig (sometimes). But let's for now not get into the DBA weeds and stay in the developer's realm.

Assume, you can replicate the problem (that's often a big "if") and assume that you can have access to the top level of the spaghetti code you inherited from 30+ years of development. From my experience, the entry point is usually detectable - but nobody living knows what exactly happens inside of the "black box".

Luckily #2, Oracle engineers left us instrumentation to access their internal mechanisms - in my case, calls to SQL and PL/SQL as one program units call the other. That access in implemented via one of the most powerful (and, unfortunately, underutilized) features - Oracle Hierarchical Profiler (HProf). That feature is available since Oracle 11 R1 and supersedes Oracle Profiler (notice the omission of the word "hierarchical"!) . In the las couple of years I made a number of presentations about that product, but I feel that sometimes articles are a bit more helpful (yes, I still can't say the podcast - sorry, I just cant adjust to the video style). This and the following post(s?) will cover some basic ideas why YOU should care about HProf.

Let's first define a use-case: the database is doing "something" and that "something" takes too much time. And key caveat: somebody indeed has proven that it is the database that is slow - not data transmission, not the middle tier, not the client. Remember the classical image that my esteemed guru Dr. Paul Dorsey came up years ago?

Users are always complaining (that's their nature) - and it is your responsibility as a system architect to understand that there are soooo many places where you can lose some critical resources, including time (well, and money!) But only when you think in terms of a logical action (rather than technical call) you can really tune your system:

• it doesn't matter how fast is your database if there are tens of thousands of calls from the middle-tier to accomplish a single activity - the time will be spent closing/opening sessions. And only if you can hierarchically step up to the point where the activity is initiated you can see the "big picture"
• it doesn't matter how slow is your database if the time is spent on the client side because of various JavaScript performance issues

But let's assume that you found out that you need to dive into the internals of the database. The problem that is that the database calls are rarely something simple - usually you have a combination of PL/SQL units-calling SQL-calling-PL/SQL units-calling other PL/SQL units-calling SQL etc. I.e. you have a hierarchy of database calls. And that's where HProf comes to rescue. 

I will start with the simplest module and simplest logging implementation (writing logs to the file system rather than storing it in the database):

SQL> CREATE DIRECTORY IO AS 'C:\IO';
SQL> exec dbms_hprof.start_profiling 
                   (location=>'IO',filename=>'HProf.txt');

SQL> DECLARE
2      PROCEDURE p_doSomething (pi_empno NUMBER) IS
3      BEGIN
4          dbms_lock.sleep(0.1);
5      END;
6      PROCEDURE p_main IS
7      BEGIN
8          dbms_lock.sleep(0.5);
9          FOR c IN (SELECT * FROM emp) LOOP
10             p_doSomething(c.empno);
11         END LOOP;
12     END;
13 BEGIN
14     p_main();
15 END;
16 /

SQL> exec dbms_hprof.stop_profiling;

As you can see, nothing fancy: (1) anonymous block calls a procedure (2) procedure wastes half-second and walks a loop based on EMP table (3) for every row in the EMP table another procedure is called that wastes 0.1 sec. All of the processing is started/stopped via DBMS_HPROF calls, and results are written into the file, as I said earlier.

If you want to read the raw file - it is pretty simple, but still doesn't provide any insights:

P#V PLSHPROF Internal Version 1.0
P#! PL/SQL Timer Started
P#C PLSQL."".""."__plsql_vm"
P#X 8
P#C PLSQL."".""."__anonymous_block"
P#X 6
P#C PLSQL."".""."__anonymous_block.P_MAIN"#980980e97e42f8ec #6
P#X 63
P#C PLSQL."SYS"."DBMS_LOCK"::9."__pkg_init"
P#X 7
P#R
P#X 119
P#C PLSQL."SYS"."DBMS_LOCK"::11."SLEEP"#e17d780a3c3eae3d #197
P#X 500373
P#R
P#X 586
P#C SQL."".""."__sql_fetch_line9" #9."4ay6mhcbhvbf2"
P#! SELECT * FROM SCOTT.EMP
P#X 3791
P#R
P#X 17
<<… and so on …>>

P#C is a call, P#R - return from the subprogram, P#X - elapsed time etc. But you need an aggregator - PLSHPROF routine:

C:\Utl_File\IO>plshprof -output hprof_intro HProf.txt
PLSHPROF: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[8 symbols processed]
[Report written to 'hprof_intro.html']

Now the raw data finally makes sense (the resulting report has a lot of sorting options, I picked up the most explicit one):

All of the steps exactly as I described above - including SQL ID of executed query. So, if needed you have a way to link that report with V$SQLAREA and/or DBMS_XPLAN analysis.

To be continued...

The Power of Proactive Monitoring. Part 2

Observing contemporary home devices is entertaining - I didn't even expect that much, since I didn't buy any major appliances until recently. And that washing machine continues to feed my thoughts with ideas/lessons applicable to the IT industry. So, this poste will be about those snippets of wisdom (more technical content will eventually follow, but now I am a bit more philosophical :) ):

Snippet #1

If you can monitor something pretty easily without significant impact on the overall system performance - just do it! Because if you debug a complex problem you just never know what can lead you to the right solution. Example: washing machines obviously have huge rotating drum inside that auto-compensates for uneven weight distribution and/or uneven installation. Ooook... If you have compensating mechanism that adjusts the drum in real-time, you can collect all of that information - and suddenly you have a very interesting pieces of data that you can aggregate and step into something new: (a) by running a number of drum tests in different directions you can understand what is inside and use AI to auto-setup washing cycles (b) by running a number of wash cycles you can figure out potential uneven floors and suggest to the owner that the washing machine needs level adjustment. Definitely useful feature!

Let's port that story into the database realm. Assume, you have a process that runs some kind of batch in a multithreaded format. That process dynamically adjusts a number of threads depending on the workload and the current speed of processing. And prudent solution here will be to record any change that happens to the system (thread added/number of threads shrunk/etc), because in the aggregated form it will paint you a pretty good picture of the system well-being. For example, you can detect previously unknown workload patterns! And from my own experience with a similar logging mechanism by comparing behavioral changes we were able to detect changes to the system antivirus configuration that were causing seemingly random slowdowns. Only with enough logs we were able to correlate system slowdowns with OS-level activity.

Snippet #2

More I think about IT as a part of humanity, more I understand that safety best practices MUST be forces from the top (sorry, my libertarian friends, people are too lazy for their own good). Manufactures of home appliances understood that maxima pretty well (warranty claims are pricey!) and now try to make the maintenance process (a) easy (b) inevitable. Rather that suggesting what should be done to prolong the life of their device, they added built-in counter of washing cycles + a special cleanup cycle directly in the machine itself. The user will be simply notified more or less in the following way: "you run N cycles, now it is maintenance time. To continue press any button". A bit annoying? Yes. Way more productive in terms of the devices longevity? Big YES!

Translating into IT terms... I understand that I am stepping into a minefield here, but I think that we need to embrace the paradigm shift: up to very recently the job of DB people was to accumulate and safely store ALL pieces of information, but now the most critical part of DB functionality is to separate useful information from white noise. Our systems are too bloated and to overwhelmed with data elements. I.e. one of the most critical challenges now is to configure proper data archival process - maintaining only what is critical and taking all of the garbage (old/unused/retired data). And unless you create that vision from the very beginning, adding that type of self-healing mechanism afterwards is either cumbersome or plain painful for either users or the maintenance team (or both). So, that self-presevation process has to be as explicit and as visible from the very beginning as possible - this way users will be accustomed to it from the system inception. And we all know how difficult it is to change existing behavioral patterns...

The Power of Proactive Monitoring. Part 1

This weekend I had a seemingly unrelated to IT incident that forced me to think a lot about the system development. Here is a story.  I bought a new washing from the large store (let's keep it anonymous for now), obviously with the delivery and installation. O-o-ok, on Friday that machine was delivered and installed by the official crew from the store. After that we run a number of cycles to wash whatever has accumulated (since the moment our old washer was declared dead), didn't even sort out the clean laundry (Friday...), and left on Saturday morning for the outdoors festival.

But I am proud of doing one right thing: that washer had "smart" features, so I installed the corresponding app and did all the required registration processes. To be fair, just for fun - I never had a chance to communicate with the washer.

And suddenly while walking miles away from home I am getting a notification: "Sir, it's you washer. I found  a quiet moment and decided to run self-check... And you know... I have a bad feeling that my hot and cold water hoses are swapped. Maybe I am wrong, but could you check for me? Because otherwise I can damage your clothes!" It was a very polite message, I was impressed!

Lo and behold, my family comes back from the daytrip - and I indeed detect that hoses are swapped! That means that during the previous cycles it rinsed everything with hot water... I check our laundry I find a lot of things shrunk and color-damaged! Good new: it was mostly mine and my son's items (not wife's or daughter's) . Bad news: it was a lot of them, so I am planning tomorrow to have a little chat with the store managers (will keep you posted on the results).

But key point - the importance of the self-diagnostics! That regular check saved me really a lot of money and troubles. And here is where IT industry comes back - let's be frank and answer the following question: "how much does an regular developer like doing code instrumentation?" The answer is "he/she hates it UNLESS he/she is also doing long-term maintenance!" And in how many offices people care about long-term? Everybody is on the tight schedule, everybody runs like crazy to meet deadlines...

I've seen lots and lots environments - and the only ones that have well-instrumented code are those that have direct pressure (+corresponding support and resources!) to do it from the top management. Reasons are pretty obvious: code instrumentation is very time- and resouce-consuming, especially during early stages on the project (when lots of things are changing). As a result even the best developers sometimes forget that somebody (a) needs to look at their code years from now (b) needs to find that piece of code in case something went wrong inside of it (c) understand what is normal functioning of any part of the system and what is not.

If the first two items have been covered for years - yes, comments, yes, proper error logging and tracing - the last one is way more obscure and requires to step at least one (maybe more) levels up from your cubical. Because the question is not about your code - question is about the system as a whole. And let's not forget, errare humahum est - people sometimes do very strange thing just because they can...

I am very thankful to the engineers of that washer that decided to embed hot/cold check into the self-diagnostics, even they very clearly marked what should be connected where. If you can't prevent some common mistakes - at least you can make sure that you detect them early enough!

Summary: IT engineers still have a lot to learn from their mechanical colleagues!

End of August Thoughts

For the last couple of year the end of August means one extra thing (other than sign of relief that soon kids will be back to school :) ): it is time to start thinking about conferences abstracts! Because sooner than later various user groups would start to collect them - and having to come up with something at the very last minute usually a really bad idea.

And this year is a really strange one - the first post-COVID conferences brought mixed feelings: it was great to see a lot of familiar faces, but... I started to ask myself - what is my audience? whom to I need to talk? You know, I have multiple "hats" - kind-of-DBA, architect, developer, performance tuning specialist... How do I position myself nowadays?

And you know... I looked back at my kids - and realized something: there will always be a new batch of kids just coming to IT industry! "Buzzwords" may change, "best practices" may mutate... But overall ways of building systems efficiently, being frank and open with yourself and your colleagues - stay the same.

So, back to basics... I will try to reexamine what I know and what I don't know - and will start sharing here various snippets and ideas. Maybe it will be useful to anybody else - maybe it will just help me thinking clearly... Who knows?!

P.s. Today is a deadline for RMOUG-2023 :) - please, don't miss it! That's a really great event!

Generic Search Screen (and thoughts about blogs vs slides)

Talking about things that I missed here while not blogging... Years ago I wrote the whole series of posts about ways we can implement the most common part of any application - generic search screen (I even got some of those examples into my "PL/SQL Performance Tuning" book). That was a lot of fun, because it involved a lot of advanced features - object types, Dynamic SQL, XML, pipelined function etc.

Years later I finally decided to make a single-point-of-reference, where I aggregate all of the thoughts. And you know, I understand why people like PowerPoint - in presentation format it feels much more understandable. At least that's what I've heard from a number of my listeners. 

To be fair, I was surprised - personally I can much quicker go through textual format (book/article) comparing to various visually-enhanced content sources. I can still understand live webinar where you have a chance to ask the author direct questions... But listening to webcasts?! I even tried to do it on higher speed - still not my format. Summary: people are REALLY different :-)

Anyways, here is a link (if you still care):

https://www.slideshare.net/MishaRosenblum/building-a-generic-search-screen-using-dynamic-sql

Long-term IT survivability - educational impact?

Even though Memorial day is a big holiday here in the USA, I am still doing my tech-editing (quiet! I didn't name the book :) ) - got the new chapter a couple of days ago, and the publishing process should run no matter what is going on around the world. And of course, got a couple of thoughts that I wouldn't mind sharing ;)

When you talk about Oracle as a development platform, you can cleanly see two major patterns - either people are covering extremely advanced topics that only other gurus would understand, or people provide very light walk-throughs without providing real depth. As a result, in the IT you often see either over-utilization of Oracle platform or its under-utilization (depending on the type of available workforce).

Being educator myself for the last 20 years I often ask myself: how much knowledge is really needed to build systems efficiently? The current trend is that lots of mediocre developers is better, because they are predictable, and because if needed you can get more of them. As a result, wide knowledge of various platforms/tools is valued more than in-depth knowledge of smaller number of tools. I.e. IT education should be focusing on widening of the base + ease of switching from one environment to the other - but IMHO, that's a trap!

You cannot constantly jump from one solution to another - eventually you need to be able to provide-long term support and maintenance of existing systems (yes, rewrite is an option, but how often you can find THAT level of budgets?!)  And you, know - recently I made a presentation covering that long-term survivability of IT platforms, and I completely forgot to add it to this blog.

Here it is:
https://www.slideshare.net/MishaRosenblum/managing-the-earthquake-surviving-major-database-architecture-changes-rev2022

Back to live conferences

One of the reasons I decided to resurrect this blog is, to be fair, refresh my professional story-telling skills in English. As you may know, my native languages are Russian and Ukrainian, same for my family - and for the last two years I've been working from home. So, my English started to noticeably degrade. 

If you work online - everybody is more or less on their own schedule, so you would rather send email instead of calling, there is no coffee breaks etc. As a result, I started to write a bit more eloquently, but technology conversations lag what I would call "the natural flow": I have to search for correct words instead of searching for the correct intonations.

And here comes the key topic: why would I care, considering that nowadays significant number of IT people just work from whatever they call their base (home/hotel/summer house/winter house etc)? My answer - because I learn from inter-person communication significantly more than I learn from written text! The way people tell their stories is much more than "just content" - it is all of their feelings/struggles/discoveries. It is a lot of extras that you cannot just dig our from the article or a blog post.

I've been speaking at various IT conferences for the last 20 years - and it always amazes me how much people undervalue live contact. I understand convenience of your own chair - but stepping out of it could bring you so much more!

In about a week I will be flying to Las Vegas for the first in-person conference since the beginning of COVID pandemia - BLUEPRINT 4D (June 6/June 9). I will have my own presentation there on Wednesday at 9 am - "Through the Looking-Glass: Database Views". As you can guess from the title, it will be SQL and PL/SQL-heavy - well, that's my normal style. 

Hopefully, I will not stumble the same way I did at my first Oracle OpenWorld presentation (I had to simulate mic malfunction to buy some time and get back my voice :-) ). Two decades of experience are still two decades! And I really miss conversations with people, I really miss that great feeling of one big community that I had (and to be fair, that kept me within Oracle technology space).

Summary: person-to-person conversions are fun! Live events forever! :-)

Joys and Nightmares of a Tech Editor

Obviously, there was a lot of interesting things I did during all of those years since I more or less abandoned this blog. And filling in all holes would be more or less useless (since time had definitely changed). But there are some exceptions (as usual :-) ) - and one of those exceptions is that in 2019 I was pulled back to the book-publishing process! I am still to lazy to write something on my own (although, I have a couple of good drafts, so, well, "never say never") - but I don't mind looking over somebody's shoulder, especially if I care about the topic.

Frankly speaking, over the years I had mixed experiences - people are different, and not all authors take comments/critique in a friendly manner. But this time I lucked out - up to the point that I feel proud that I participated in the first book by Joh Heller (I bet, you may recognize this name from various extremely useful posts on StackOverflow)

But why I decided to write a post about my experience... Because working as a tech editor in Oracle technology space is a very tricky job, and lessons that I learned may be useful to other people. Let's do bullet points (prepping up for conferences!):

1. Anybody who've spent at least some time with Oracle tech stack knows that the most common answer on any question - "It depends!" I.e. there are different answers that could be valid within specific ranges of applicability. So, one of the main tasks for the tech editor is to keep the author from providing "universal" answers - only answers+conditions. 

2. Tech editor must always remember that it is not his book! The author has full rights to have his/her own opinion/style/favorites. As long as they are not factually incorrect, it is OK to not agree with the author - your role is to keep the author honest in front of the audience. I.e. you can point that something doesn't add up (or in some cases there are other approaches), but whether to follow this route - not your choice!

3. Even the best authors are sometimes very sensitive. So, treat them the same way you would talk to your teenagers :) - no direct orders, no harsh comments! Only suggestions, only very polite hints that this-and-that could be better. 

4. As you all know, teenagers can be VERY irritating from time to time - in that case you speak directly with the publisher (luckily, in this project I didn't have to use that "big hammer", but I had different experience too). Tech editors are hired by the publishing house, not by the author - so, your role is to be a safety check against bad quality books with technical errors.

Anyways, that was definitely fun - and here is a link on the current edition (yes, it is a hint :) ) 

Jon Heller, Pro Oracle SQL Development: Best Practices for Writing Advanced Queries

Return of a blogger (plus a bit of ORA-7445 [LpxMemFree()+200] )

Considering that "conference life" is picking up (two in-person events in June!), it is time to start writing something meaningful (yes, I am back to blogging after years of ignorance)! 

To cover event previews I need a bit more prep time, while today I would like to share a small story (narrow, but very practical!)

At Dulcian we have a couple of systems still on Oracle 11g (yeah, I understand that it is outdated). And today one of my support people started to complain about ORA-07445. To be precise - ORA-7445  [LpxMemFree()+200]. And you know how much fun is to sort out those errors, especially considering my gut feeling ("I bet, XMLtype is misbehaving!")

Obviously, first two steps were obvious: Google and MOS. 

Oook... Google gave me a direct hit - and I indeed was right, it was XML parsing issue - Doc ID 2220757.1 

And in case you don't have MOS access - here is a code snippet to replicate (it should blow up with "end of communication channel" plus corresponding trace file):

 declare
  lv_text varchar2(6000);
  lv_temp_xml xmltype;
  lv_len number := 0;
begin
  lv_text := '<LPCNotes Subject="Comments for LPC " Text="'
  || to_char(sysdate, 'dd-Mon-yy hh24:mi')
  || ' '
  ||lpad('A',4033,'A')
  || '" />';
  lv_len := length(lv_text);
  dbms_output.put_line('Length of the 3rd run is '||lv_len);
  lv_temp_xml := sys.xmltype(lv_text);
 end;

The proposed solution was to enable some kind of "old parser" - you can do it on either session of system level. Considering that everything else in our system worked fine, I added it via Dynamic SQL just before the problematic code, so it will be less overall system impact:

 declare
  lv_text varchar2(6000);
  lv_temp_xml xmltype;
  lv_len number := 0;
begin
  execute immediate 'alter session set events ''31156 trace name context forever, level 1024''';
  lv_text := '<LPCNotes Subject="Comments for LPC " Text="'
  || to_char(sysdate, 'dd-Mon-yy hh24:mi')
  || ' '
  ||lpad('A',4033,'A')
  || '" />';
  lv_len := length(lv_text);
  dbms_output.put_line('Length of the 3rd run is '||lv_len);
  lv_temp_xml := sys.xmltype(lv_text);
 end;

And the whole problem was gone! After a bit more digging I found that this kind of approach could help with a number of other similar ORA-07445 errors if you still on 11.2.0.4 (here is a doc from MOS: Bug 14822091)

Hope, it helps!

P.s. Footprint: in the doc they use hexadecimal level 0x400, but I prefer to use decimals (1024) for readability. What is funny, in the bug description whoever wrote it also used decimal - so, don't worry, it is the same stuff written differently.

RMOUG-2021 Day#4

My today's presentation - "Managing Unstructured Data: Lobs in the World of JSON"
https://www.slideshare.net/MishaRosenblum/managing-unstructured-data-lobs-in-the-world-of-json

RMOUG-2021 Day #2

 If anybody needs - here is my most recent presentation: "Hidden Gems of Performance Tuning":

https://www.slideshare.net/MishaRosenblum/hidden-gems-of-performance-tuning-hierarchical-profiler-and-dml-trigger-optimization

P.s. It was a pleasure to get back to RMOUG  - even virtually!

Collaborate'17: Slides are available

For some reasons, slides from one of my presentations are not available via the conference agenda tool. No problem, here is a direct link:
https://www.slideshare.net/MishaRosenblum/developers-approach-to-code-management

More thoughts/comments about the conference are coming! To be continued...

Collaborate'16: Slides are available

First, of all, I would like to thank everybody who came to my presentations on Collab'16! It was a pleasure talking FOR you :-) A lot of nice questions/comments/follow-ups.

Second, some SlideShare uploads (as promised):
- my talk about user-defined functions - that's where I was talking a lot about execution counts and the impact of Cost-Based Optimizer on them:
http://www.slideshare.net/MishaRosenblum/plsql-userdefined-functions-in-the-read-world

- updated version of my "Printing without Printers" talk. As usual, I was reminded that "the best live demo is dead demo". Now you've got everything covered explicitly:
http://www.slideshare.net/MishaRosenblum/printing-without-printers

Collaborate'15 A New View of Database Views

Thanks to everybody who visited my presentation! Here is a link to view and/or download the slide set: http://www.slideshare.net/MishaRosenblum/2015-458-rosenblumpptfinal

Feel free to ask questions!