Analytic Functions: A Savior

Hi,

Yesterday, I have come across yet another performance issue. I received a complaint from one of our developers that they have a business report which is running extremely slow.

I received his mail and attached was the query:

SELECT   
  XLT.VOU_DET.VOU_DATE,
  XLT.VOU_DET.VALUE_DATE,
  XLT.VOU_DET.DESCRIPTION,
  XLT.VOU_DET.PIX_CODE,
  VOU_TYPE.DESCR,
  XLT.VOU_DET.PIX_BRN,
  XLT.VOU_DET.DR_BAL_ORIG,
  XLT.VOU_DET.CR_BAL_ORIG,
  XLT.VOU_DET.CLOSING_BAL_ORIG,
  XLT.VOU_LOOKUP.NAME,
  XLT.VOU_DET.VOU_SEQ,
  (SELECT TO_CHAR(X.OPEN_BAL_ORIG) 
     FROM XLT.VOU_DET X 
    WHERE X.ASOF_DATE BETWEEN to_date('01-05-2007', 'dd-mm-yyyy') 
                          AND to_date('30-09-2008', 'dd-mm-yyyy')
      AND X.VOU_CODE  = '9900016WXYRT01'
      AND X.VOU_SEQ = 1
      AND ROWNUM = 1) OPEN_BAL_ORIG,
  (SELECT count(  XLT.VOU_DET.DR_BAL_ORIG) 
     FROM (select DR_BAL_ORIG 
              From XLT.VOU_DET X 
             WHERE X.ASOF_DATE BETWEEN to_date('01-05-2007', 'dd-mm-yyyy') 
                                   AND to_date('30-09-2008', 'dd-mm-yyyy')
               AND X.VOU_CODE  = '9900016WXYRT01'
               AND X.DR_BAL_ORIG <>0)) DR_BAL_ORIG_CNT,
  (SELECT count(  XLT.VOU_DET.CR_BAL_ORIG) 
     FROM (select CR_BAL_ORIG 
             From XLT.VOU_DET X 
            WHERE X.ASOF_DATE BETWEEN to_date('01-05-2007', 'dd-mm-yyyy') 
                                  AND to_date('30-09-2008', 'dd-mm-yyyy')
              AND X.VOU_CODE  = '9900016WXYRT01'
              AND X.CR_BAL_ORIG <>0)) CR_BAL_ORIG_CNT 
FROM
  XLT.VOU_DET,
  XLT.X_VOU_TYPE  VOU_TYPE,
  XLT.VOU_LOOKUP
WHERE XLT.VOU_DET.VOU_TYPE_ID=VOU_TYPE.VOU_TYPE_ID
  AND XLT.VOU_DET.VOU_REF(+)=XLT.VOU_LOOKUP.CUST_CODE_WNG
  AND XLT.VOU_DET.ASOF_DATE  BETWEEN  to_date('01-05-2007', 'dd-mm-yyyy') AND to_date('30-09-2008', 'dd-mm-yyyy')
  AND XLT.VOU_DET.VOU_CODE  =  '9900016WXYRT01'
ORDER BY
  XLT.VOU_DET.VOU_SEQ,
  XLT.VOU_DET.ASOF_DATE;

This looks like a case of missing Analytic functions.

The three SELECT statements within the query can be easily replaced with simple Analytic functions. The first one is to fetch the opening balance:

  (SELECT TO_CHAR(X.OPEN_BAL_ORIG) 
     FROM XLT.VOU_DET X 
    WHERE X.ASOF_DATE BETWEEN to_date('01-05-2007', 'dd-mm-yyyy') 
                          AND to_date('30-09-2008', 'dd-mm-yyyy')
      AND X.VOU_CODE  = '9900016WXYRT01'
      AND X.VOU_SEQ = 1
      AND ROWNUM = 1) OPEN_BAL_ORIG,

and could be easily rewritten as:

first_value(OPEN_BAL_ORIG) 
      over (partition by VOU_CODE 
                Order by ASOF_DATE, decode(VOU_SEQ, 0, 99, VOU_SEQ)) VOU_SEQ,

The second and third are the number of Debit and Credit transactions respectively and can also be written as:

  count(decode(DR_BAL_ORIG, null, null, 0, null, 1)) 
      over (partition by  VOU_CODE) DR_BAL_ORIG_cnt,

and

  count(decode(CR_BAL_ORIG, null, null, 0, null, 1)) 
      over (partition by  VOU_CODE) CR_BAL_ORIG_cnt

Now, its time to execute the modified query and (1) compare the result set, (2) measure time taken, (3) and compare statistics with the original query.

Well, the result set was compared and was convincing. The enhanced query completes in less than 20 seconds while the original took just over 17 minutes. So, the new query is 173 times faster than the original one.

Lastly, let’s compare statistics.

Statistics of Original query:

SQL> set autotrace traceonly stat
SQL> @tuneme

661 rows selected.

Elapsed: 00:17:26.91

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
    2066944  consistent gets
     753812  physical reads
        116  redo size
      68506  bytes sent via SQL*Net to client
        842  bytes received via SQL*Net from client
         46  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        661  rows processed

SQL> 

Statistics of Modified query:

SQL> set autotrace traceonly exp stat
SQL> @tunedquery

661 rows selected.

Elapsed: 00:00:17.35
Statistics
----------------------------------------------------------
          2  recursive calls
          0  db block gets
      22514  consistent gets
       2580  physical reads
          0  redo size
      68003  bytes sent via SQL*Net to client
        547  bytes received via SQL*Net from client
         46  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
        661  rows processed

SQL>

Instead of “2,066,944” consistent gets and “753,812” physical reads, it took merely “22,514” consistent gets and “2,580” physical reads. That’s 98% reduction in LIO’s and 99%+ reduction in PIO.

Again Analytics Functions is the winner over traditional query writing style.

How to read Data between different Characterset Databases - Part 2

Hazem Ameen has left a comment on my previous post, where he says he is able to read/write data over a database link between two databases having different charactersets.

We have your same environment and reading/writing over db link is fine. Oracle does character-set conversion between the 2 Arabic character-set as expected. Client NLS_LANG: America_America.ar8mswin1256 Server1: ar8mswin1256 Server 2: ar8iso8859p6 I can only suspect your client NLS_LANG Our version is 10.2.0.4 on Linux. Client is MS Windows Vista 10.2.0.4

reading/writing over db link is fine

What I would assert from this is, although you have databases with two different charactersets, your client characterset is SAME. This is the reason you are able to read/write over the db link. But, in our case, clients connecting to database A have same characterset as A, while clients connecting to database B have same characterset as B. Here's a test case with my client characterset set to "AR8MSWIN1256".

Create tables in both the databases:

Create database links in both databases and query data from other side:

In the above example, we were able to read data over db links meaning client characterset's are playing the vital role.

This means, all your client's characterset is set to "AR8MSWIN1256" for both the types of databases. This is the reason you are able to read/write over a db link.

I hope I was clear in my examples. Thanks for leaving your comment.

Regards

Regards Asif Momen

How to read Data between different Characterset Databases

We have two 24x7 production databases with different charactersets. Yes, both charactersets are related to Arabic language. Database A’s characterset is AR8MSWIN1256 while database B’s characterset is AR8ISO8859P6.

We had a requirement wherein database A will read Arabic data from database B. If you create a database link and try to query data from database B it will appear to be garbage, as shown below:

whereas when you run the same query on database B, this is how the output looks:

Even you are unaware of Arabic language, you may visually compare and see the output is a garbage when queried from database A.

DUMP function comes handy under these situations. It returns internal representation of an expression. When used to against the “name” failed, I get this output.

SQL> select dump(name) from test_iso@testdb;

DUMP(NAME)
--------------------------------------------------------------------
Typ=1 Len=13: 227,205,227,207,32,194,213,221,32,227,196,227,228

where, “Typ” refers to datatype, “Len” means the length of the string, and everything after “:” is comma separated ASCII values of all the characters in the “name” field.

I wrote a small procedure where convert all the comma separated ASCII values to character string.

create or replace function read_name (p_id in number) return varchar2 is
  t_str varchar2(2000);
  l_name varchar2(2000);
  l_output varchar2(2000);
begin
  select 'select '||'chr('||replace(substr(dump(name, 1010), instr(dump(name, 1010), ':')+2), ',', ')|| chr(')||') from dual' 
    into t_str from test_iso@testdb
   where id = p_id;
 
--  dbms_output.put_line(t_str);
  execute immediate t_str into l_output;  
  return l_output;
end;
/

Now upon using this function, I am able to query remote table in a readable format.

Here’s the output: