nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

OBIEE vs. PostgreSQL database - low performance

sailorman

Hello,

I use OBIEE 11.1.1.7. I get data from PostgreSQL database (EnterpriseDB 9.2.9.21 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-52), 64-bit).

OBIEE get data from database using ODBC driver.

I have performance problem, because OBIEE execute reports one by one.

Example:

Report01 - get list of continents from database (6 records). This report retun results by 2 seconds.

Report02 - get list of continents and count people for every continent.This report retun results by 8 minutes.

Test1

1. Execute Report01 by user_B.

2. Execute Report02 by user_A.

On this test case Report01 return results by 2 second and Report02 return results by 8 minutes.

Test2

1. Execute Report02 by user_A.

2. Execute Report01 by user_B.

On this test case Report01 return results by 8 minutes. It looks like Report01 is waiting to finish Report02.

How could I resolve this issue?

Reports should be executes simultaneously not one by one.

Best regards,

Krzysztof

Find more posts tagged with

Accepted answers

All comments

rmoff

One possibility is that your connection pool in the RPD is not set to pool connections, and/or you've not set the number high enough. If that happens, OBIEE will indeed queue up requests to the database until there is an available connection.

You can validate this by using the DMS metrics available within OBIEE. There's various ways to get these out but as a quick check look at http://<your server>:7001/dms (assuming admin server is running on port 7001). Go to http://<your server>:7001/dms/index.html?format=metrictable&cache=false&prefetch=false&table=Oracle_BI_DB_Connection_Pool and look at the Peak Queued Requests metric.

sailorman

I have checked "Enabled connection pooling" in connection pool in RPD ("Maximum connections" = 30000).

Peak_Queued_Requests is equal 0.

OBIEE still execute reports one by one.

rmoff

Can you share your nqquery.log?

sailorman

nqquery.log

First query with f_population table get huge amount of data. It's comes from Report02.

Second query with d_country table only get smal amount of data. It's comes from Report01.

Both query takes more than 558 seconds.

[2016-02-16T10:04:07.000+01:00] [OracleBIServerComponent] [TRACE:2] [USER-0] [] [ecid: 4156c90199239c20:-3c4cdf85:152cc23c960:-8000-000000000001cf57,0:1:9:3] [tid: 7e1cf700] [requestid: b9760018] [sessionid: b9760000] [username: weblogic] ############################################## [[

-------------------- SQL Request, logical request hash:

d2521141

SET VARIABLE QUERY_SRC_CD='Report';SELECT

0 s_0,

"TEST - Population"."d_country"."continent" s_1,

"TEST - Population"."f_population"."population" s_2

FROM "TEST - Population"

ORDER BY 1, 2 ASC NULLS LAST

FETCH FIRST 65001 ROWS ONLY

]]

[2016-02-16T10:04:07.000+01:00] [OracleBIServerComponent] [TRACE:2] [USER-23] [] [ecid: 4156c90199239c20:-3c4cdf85:152cc23c960:-8000-000000000001cf57,0:1:9:3] [tid: 7e1cf700] [requestid: b9760018] [sessionid: b9760000] [username: weblogic] -------------------- General Query Info: [[

Repository: Star, Subject Area: TEST_hurtownia_bm, Presentation: TEST - Population

]]

[2016-02-16T10:04:07.000+01:00] [OracleBIServerComponent] [TRACE:2] [USER-18] [] [ecid: 4156c90199239c20:-3c4cdf85:152cc23c960:-8000-000000000001cf57,0:1:9:5] [tid: 7e1cf700] [requestid: b9760018] [sessionid: b9760000] [username: weblogic] -------------------- Sending query to database named TEST_hurtownia_db (id: <<30802>>), connection pool named TEST_hurtownia_cp, logical request hash d2521141, physical request hash e4c35726: [[

select sum(T151821."people") as c1,

T151816."continent" as c2

from

"canarddw"."dw"."d_country" T151816,

"canarddw"."dw"."f_population" T151821

where ( T151816."id" = T151821."country_id" )

group by T151816."continent"

order by 2

]]

[2016-02-16T10:04:13.000+01:00] [OracleBIServerComponent] [TRACE:2] [USER-0] [] [ecid: 4156c90199239c20:-3c4cdf85:152cc23c960:-8000-000000000001cf63,0:1:9:3] [tid: 7e0cb700] [requestid: b9740004] [sessionid: b9740000] [username: weblogic] ############################################## [[

-------------------- SQL Request, logical request hash:

35e2c90a

SET VARIABLE QUERY_SRC_CD='Report';SELECT

0 s_0,

"TEST - Population"."d_country"."continent" s_1

FROM "TEST - Population"

ORDER BY 1, 2 ASC NULLS LAST

FETCH FIRST 65001 ROWS ONLY

]]

[2016-02-16T10:04:13.000+01:00] [OracleBIServerComponent] [TRACE:2] [USER-23] [] [ecid: 4156c90199239c20:-3c4cdf85:152cc23c960:-8000-000000000001cf63,0:1:9:3] [tid: 7e0cb700] [requestid: b9740004] [sessionid: b9740000] [username: weblogic] -------------------- General Query Info: [[Repository: Star, Subject Area: TEST_hurtownia_bm, Presentation: TEST - Population]]

[2016-02-16T10:04:13.000+01:00] [OracleBIServerComponent] [TRACE:2] [USER-18] [] [ecid: 4156c90199239c20:-3c4cdf85:152cc23c960:-8000-000000000001cf63,0:1:9:5] [tid: 7e0cb700] [requestid: b9740004] [sessionid: b9740000] [username: weblogic] -------------------- Sending query to database named TEST_hurtownia_db (id: <<31090>>), connection pool named TEST_hurtownia_cp, logical request hash 35e2c90a, physical request hash 857e69af: [[

select distinct T151816."continent" as c1

from

"canarddw"."dw"."d_country" T151816

]]

[2016-02-16T10:13:32.000+01:00] [OracleBIServerComponent] [TRACE:2] [USER-34] [] [ecid: 4156c90199239c20:-3c4cdf85:152cc23c960:-8000-000000000001d1ff,0:1:1] [tid: 7e1cf700] [requestid: b9760018] [sessionid: b9760000] [username: weblogic] -------------------- Query Status: Successful Completion [[]]

[2016-02-16T10:13:32.000+01:00] [OracleBIServerComponent] [TRACE:2] [USER-26] [] [ecid: 4156c90199239c20:-3c4cdf85:152cc23c960:-8000-000000000001d1ff,0:1:1] [tid: 7e1cf700] [requestid: b9760018] [sessionid: b9760000] [username: weblogic] -------------------- Rows 7, bytes 7280 retrieved from database query id: <<30802>> [[]]

[2016-02-16T10:13:32.000+01:00] [OracleBIServerComponent] [TRACE:2] [USER-28] [] [ecid: 4156c90199239c20:-3c4cdf85:152cc23c960:-8000-000000000001d1ff,0:1:1] [tid: 7e1cf700] [requestid: b9760018] [sessionid: b9760000] [username: weblogic] -------------------- Physical query response time 565 (seconds), id <<30802>> [[]]

[2016-02-16T10:13:32.000+01:00] [OracleBIServerComponent] [TRACE:2] [USER-29] [] [ecid: 4156c90199239c20:-3c4cdf85:152cc23c960:-8000-000000000001d1ff,0:1:1] [tid: 7e1cf700] [requestid: b9760018] [sessionid: b9760000] [username: weblogic] -------------------- Physical Query Summary Stats: Number of physical queries 1, Cumulative time 565, DB-connect time 0 (seconds) [[]]

[2016-02-16T10:13:32.000+01:00] [OracleBIServerComponent] [TRACE:2] [USER-24] [] [ecid: 4156c90199239c20:-3c4cdf85:152cc23c960:-8000-000000000001d1ff,0:1:1] [tid: 7e1cf700] [requestid: b9760018] [sessionid: b9760000] [username: weblogic] -------------------- Rows returned to Client 7 [[]]

[2016-02-16T10:13:32.000+01:00] [OracleBIServerComponent] [TRACE:2] [USER-33] [] [ecid: 4156c90199239c20:-3c4cdf85:152cc23c960:-8000-000000000001d1ff,0:1:1] [tid: 7e1cf700] [requestid: b9760018] [sessionid: b9760000] [username: weblogic] -------------------- Logical Query Summary Stats: Elapsed time 565, Response time 565, Compilation time 0 (seconds) [[]]

[2016-02-16T10:13:32.000+01:00] [OracleBIServerComponent] [TRACE:2] [USER-34] [] [ecid: 4156c90199239c20:-3c4cdf85:152cc23c960:-8000-000000000001d1fc,0:1:1] [tid: 7e0cb700] [requestid: b9740004] [sessionid: b9740000] [username: weblogic] -------------------- Query Status: Successful Completion [[]]

[2016-02-16T10:13:32.000+01:00] [OracleBIServerComponent] [TRACE:2] [USER-26] [] [ecid: 4156c90199239c20:-3c4cdf85:152cc23c960:-8000-000000000001d1fc,0:1:1] [tid: 7e0cb700] [requestid: b9740004] [sessionid: b9740000] [username: weblogic] -------------------- Rows 7, bytes 7196 retrieved from database query id: <<31090>> [[]]

[2016-02-16T10:13:32.000+01:00] [OracleBIServerComponent] [TRACE:2] [USER-28] [] [ecid: 4156c90199239c20:-3c4cdf85:152cc23c960:-8000-000000000001d1fc,0:1:1] [tid: 7e0cb700] [requestid: b9740004] [sessionid: b9740000] [username: weblogic] -------------------- Physical query response time 558 (seconds), id <<31090>> [[]]

[2016-02-16T10:13:32.000+01:00] [OracleBIServerComponent] [TRACE:2] [USER-29] [] [ecid: 4156c90199239c20:-3c4cdf85:152cc23c960:-8000-000000000001d1fc,0:1:1] [tid: 7e0cb700] [requestid: b9740004] [sessionid: b9740000] [username: weblogic] -------------------- Physical Query Summary Stats: Number of physical queries 1, Cumulative time 558, DB-connect time 0 (seconds) [[]]

[2016-02-16T10:13:32.000+01:00] [OracleBIServerComponent] [TRACE:2] [USER-24] [] [ecid: 4156c90199239c20:-3c4cdf85:152cc23c960:-8000-000000000001d1fc,0:1:1] [tid: 7e0cb700] [requestid: b9740004] [sessionid: b9740000] [username: weblogic] -------------------- Rows returned to Client 7 [[]]

[2016-02-16T10:13:32.000+01:00] [OracleBIServerComponent] [TRACE:2] [USER-33] [] [ecid: 4156c90199239c20:-3c4cdf85:152cc23c960:-8000-000000000001d1fc,0:1:1] [tid: 7e0cb700] [requestid: b9740004] [sessionid: b9740000] [username: weblogic] -------------------- Logical Query Summary Stats: Elapsed time 559, Response time 558, Compilation time 0 (seconds) [[]]

On this expamle (this is common situation), you can see, that get data from d_country table is faster:

Query on d_country table take about 0 second.

[2016-02-16T10:27:31.000+01:00] [OracleBIServerComponent] [TRACE:2] [USER-0] [] [ecid: 4156c90199239c20:-3c4cdf85:152cc23c960:-8000-000000000001d459,0:1:9:3] [tid: 7ddbf700] [requestid: b9770006] [sessionid: b9770000] [username: weblogic] ############################################## [[

-------------------- SQL Request, logical request hash:

45c2692

SET VARIABLE QUERY_SRC_CD='Report';SELECT

0 s_0,

"TEST - Population"."d_country"."language" s_1

FROM "TEST - Population"

ORDER BY 1, 2 ASC NULLS LAST

FETCH FIRST 65001 ROWS ONLY

]]

[2016-02-16T10:27:31.000+01:00] [OracleBIServerComponent] [TRACE:2] [USER-23] [] [ecid: 4156c90199239c20:-3c4cdf85:152cc23c960:-8000-000000000001d459,0:1:9:3] [tid: 7ddbf700] [requestid: b9770006] [sessionid: b9770000] [username: weblogic] -------------------- General Query Info: [[

Repository: Star, Subject Area: TEST_hurtownia_bm, Presentation: TEST - Population]]

[2016-02-16T10:27:31.000+01:00] [OracleBIServerComponent] [TRACE:2] [USER-18] [] [ecid: 4156c90199239c20:-3c4cdf85:152cc23c960:-8000-000000000001d459,0:1:9:5] [tid: 7ddbf700] [requestid: b9770006] [sessionid: b9770000] [username: weblogic] -------------------- Sending query to database named TEST_hurtownia_db (id: <<31226>>), connection pool named TEST_hurtownia_cp, logical request hash 45c2692, physical request hash cfc48fb2: [[

select distinct T151816."language" as c1

from

"canarddw"."dw"."d_country" T151816

]]

[2016-02-16T10:27:31.000+01:00] [OracleBIServerComponent] [TRACE:2] [USER-34] [] [ecid: 4156c90199239c20:-3c4cdf85:152cc23c960:-8000-000000000001d459,0:1:10] [tid: 7ddbf700] [requestid: b9770006] [sessionid: b9770000] [username: weblogic] -------------------- Query Status: Successful Completion [[]]

[2016-02-16T10:27:31.000+01:00] [OracleBIServerComponent] [TRACE:2] [USER-26] [] [ecid: 4156c90199239c20:-3c4cdf85:152cc23c960:-8000-000000000001d459,0:1:10] [tid: 7ddbf700] [requestid: b9770006] [sessionid: b9770000] [username: weblogic] -------------------- Rows 32, bytes 32896 retrieved from database query id: <<31226>> [[]]

[2016-02-16T10:27:31.000+01:00] [OracleBIServerComponent] [TRACE:2] [USER-28] [] [ecid: 4156c90199239c20:-3c4cdf85:152cc23c960:-8000-000000000001d459,0:1:10] [tid: 7ddbf700] [requestid: b9770006] [sessionid: b9770000] [username: weblogic] -------------------- Physical query response time 0 (seconds), id <<31226>> [[]]

[2016-02-16T10:27:31.000+01:00] [OracleBIServerComponent] [TRACE:2] [USER-29] [] [ecid: 4156c90199239c20:-3c4cdf85:152cc23c960:-8000-000000000001d459,0:1:10] [tid: 7ddbf700] [requestid: b9770006] [sessionid: b9770000] [username: weblogic] -------------------- Physical Query Summary Stats: Number of physical queries 1, Cumulative time 0, DB-connect time 0 (seconds) [[]]

[2016-02-16T10:27:31.000+01:00] [OracleBIServerComponent] [TRACE:2] [USER-24] [] [ecid: 4156c90199239c20:-3c4cdf85:152cc23c960:-8000-000000000001d459,0:1:10] [tid: 7ddbf700] [requestid: b9770006] [sessionid: b9770000] [username: weblogic] -------------------- Rows returned to Client 32 [[]]

[2016-02-16T10:27:31.000+01:00] [OracleBIServerComponent] [TRACE:2] [USER-33] [] [ecid: 4156c90199239c20:-3c4cdf85:152cc23c960:-8000-000000000001d459,0:1:10] [tid: 7ddbf700] [requestid: b9770006] [sessionid: b9770000] [username: weblogic] -------------------- Logical Query Summary Stats: Elapsed time 0, Response time 0, Compilation time 0 (seconds) [[]]

sailorman

Hello,

did you see my nqquery.log?

What should I do?

rmoff

Interesting. So if you run the logical SQLs (35e2c90a and d2521141) individually (eg through nqcmd or issue raw sql), do they run in the same time?

You need to take a look at the DB side of things too -- does it show each physical query (30802, 31226) as taking the same amount of time elapsed?

What we're looking for here is whether the queries take a long time on the DB in conjunction (so maybe there's an issue with the DB concurrency?) or if it's OBIEE delaying sending one request whilst the other completes (so maybe the OBIEE connection pooling isn't configured optimally).

For what it's worth, the third LSQL you show (45c2692) is not directly relevant, it's more the behaviour of 35e2c90a which we want to understand better.

DanPryke

Hi,

I'm currently using a PostgreSQL based connection/database.

This could be a red herring but, does your server autocommit when SQL is running?

Perhaps in your connection pool 'Connection Scripts' tab add an enabled line under the 'execute on connect' of

commit;

Worth a try!