OBIEE 11g - Bytes Retrieved From Database is High Compared to the Data in the Database (Doc ID 2063559.1)

Last updated on OCTOBER 13, 2015

Applies to:

Business Intelligence Suite Enterprise Edition - Version 11.1.1.7.0 and later
Information in this document applies to any platform.

Symptoms

When an Analysis is executed with LOGLEVEL = 5, see the NQServer.log file registers the Row Count and Row Width, and the "bytes retrieved from database" (Row Count * Row Width) for the SQL statement / query that is executed by the database.  For example:

-------------------- Execution Node: <<15900>>, Close Row Count = 20, Row Width = 552 bytes [[
-------------------- Rows 20, bytes 11040 retrieved from database query id: <<15900>> [[

However, the bytes value appears to be "high" given the size of the actual data in the database. For example:

1. Locate the SQL query in the log, see:

-------------------- Sending query to database named 01 - Sample App Data (ORCL) (id: <<15900>>)

2. Modify the SQL query to include the VSIZE Oracle Database function and execute it against the database (via SQL Plus / SQL Developer etc.):

WITH
SAWITH0 AS (select T42409.Prod_Dsc as c1, T42409.Prod_Key as c2, T42409.Sequence as c3
            from SAMP_PRODUCTS_D T42409 /* D10 Product (Dynamic Table) */ )
select SUM( VSIZE(D1.c1)  + VSIZE(D1.c2) + VSIZE(D1.c3)  + VSIZE(D1.c4) ) "total bytes"
from ( select distinct 0 as c1, D1.c1 as c2, D1.c2 as c3, D1.c3 as c4
       from SAWITH0 D1 order by c4, c3 ) D1
where rownum <= 65001;

   See the query returns "total bytes" of 401 (bytes) ... whereas the NQServer log reports a value of 11,040 bytes.

3. See the SAMP_PRODUCTS_D columns are defined as below:-

           PROD_DSC       VARCHAR2(255 CHAR)
           PROD_KEY       NUMBER
           SEQUENCE       NUMBER

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms