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

About OBIEE Usage Tracking

dilek

Hello,

I have a question about usage tracking on obiee. So, when I look at S_NQ_ACCT table, I see entries about reports usage, but not all of them are inserted the table.

In my case, I tested clicking many reports but only a few of them are seen on the table. Is there a problem or bug here? If you know, can you help me please.

OBIEE Version is: 11.1.1.7.140114 (Build 140126.2205.JANRECUT 64-bit)

Regards,

Dilek

Find more posts tagged with

Accepted answers

All comments

[Deleted User]

I'd say jack up the log and see what's written in the file system logs vs what arrives in the UT tables.

dilek

Which log file should I look for comparing usage tracking entries? I already see nqserver.log and bi server logs. No related entry :S

[Deleted User]

obis1-query.log. Run some analyses and see what gets registered in the .log vs what's in the table.

dilek

I could not find this file :S Is it normal?

Here is my log folder; I searched all of them, but could not find!

[Deleted User]

Oh right, you're on 11, my bad. So then it's nqquery.log

dilek

I compared nqquery.log file and s_nq_acct table simultaneously. When I click a dashboard, all running queries are shown on the log file, however, only one entry written s_nq_acct table.

S_NQ_ACCT table shows only one query;

SELECT "Consumer Data"."Consumer Type Tr" FROM "Portal" ORDER BY 1 FETCH FIRST 65001 ROWS ONLY

NQQUERY.LOG file shows all running queries; (a little bit long sorry )

2017-05-18T09:05:11.000+03:00] [OracleBIServerComponent] [TRACE:5] [USER-0] [] [ecid: f9f346b6bdcdc990:350023a:15bf84fad0f:-8000-00000000000362be,0:1:33:3] [tid: 451ec940] [requestid: abb10007] [sessionid: abb10000] [username: dilek] ############################################## [[

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

21ea607c

SET VARIABLE QUERY_SRC_CD='DashboardPrompt';SELECT "Consumer Data"."Consumer Type Tr" FROM "Portal" ORDER BY 1

FETCH FIRST 65001 ROWS ONLY

]]

[2017-05-18T09:05:11.000+03:00] [OracleBIServerComponent] [TRACE:5] [USER-23] [] [ecid: f9f346b6bdcdc990:350023a:15bf84fad0f:-8000-00000000000362be,0:1:33:3] [tid: 451ec940] [requestid: abb10007] [sessionid: abb10000] [username: dilek] -------------------- General Query Info: [[

Repository: Star, Subject Area: Portal, Presentation: Portal

]]

[2017-05-18T09:05:11.000+03:00] [OracleBIServerComponent] [TRACE:5] [USER-2] [] [ecid: f9f346b6bdcdc990:350023a:15bf84fad0f:-8000-00000000000362be,0:1:33:3] [tid: 451ec940] [requestid: abb10007] [sessionid: abb10000] [username: dilek] -------------------- Logical Request (before navigation): [[

RqList distinct

Consumer Data.Consumer Type Tr as c1 GB

OrderBy: c1 asc

]]

[2017-05-18T09:05:11.000+03:00] [OracleBIServerComponent] [TRACE:5] [USER-48] [] [ecid: f9f346b6bdcdc990:350023a:15bf84fad0f:-8000-00000000000362be,0:1:33:5] [tid: 451ec940] [requestid: abb10007] [sessionid: abb10000] [username: dilek] -------------------- The logical query block fail to hits or seed the cache in subrequest level due to [[

only one subrequest

]]

[2017-05-18T09:05:11.000+03:00] [OracleBIServerComponent] [TRACE:5] [USER-50] [] [ecid: f9f346b6bdcdc990:350023a:15bf84fad0f:-8000-00000000000362be,0:1:33:5] [tid: 451ec940] [requestid: abb10007] [sessionid: abb10000] [username: dilek] -------------------- The logical query seed the plan cache [[

plan

RqList <<17263735>> [for database 3023:43794:EXADATA,74] distinct /* FETCH FIRST 65001 ROWS ONLY */

case when EC_CONSUMER_DATA.CONSUMER_TYPE_ID in ([ 1, 5] ) then 'Gerçek' when EC_CONSUMER_DATA.CONSUMER_TYPE_ID in ([ 2, 3, 4] ) then 'Tüzel' else 'Bilinmiyor' end as c1 GB [for database 3023:43794,74]

Child Nodes (RqJoinSpec): <<17263753>> [for database 3023:43794:EXADATA,74]

RqJoinNode <<17263754>> []

EC_CONSUMER_DATA T263703

OrderBy: c1 asc [for database 3023:43794,74]

]]

[2017-05-18T09:05:11.000+03:00] [OracleBIServerComponent] [TRACE:5] [USER-16] [] [ecid: f9f346b6bdcdc990:350023a:15bf84fad0f:-8000-00000000000362be,0:1:33:5] [tid: 451ec940] [requestid: abb10007] [sessionid: abb10000] [username: dilek] -------------------- Execution plan: [[

RqList <<17263705>> [for database 3023:43794:EXADATA,74] distinct /* FETCH FIRST 65001 ROWS ONLY */

Child Nodes (RqJoinSpec): <<17263708>> [for database 3023:43794:EXADATA,74]

RqJoinNode <<17263707>> []

EC_CONSUMER_DATA T263703

OrderBy: c1 asc [for database 3023:43794,74]

]]

[2017-05-18T09:05:11.000+03:00] [OracleBIServerComponent] [TRACE:5] [USER-18] [] [ecid: f9f346b6bdcdc990:350023a:15bf84fad0f:-8000-00000000000362be,0:1:33:5] [tid: 451ec940] [requestid: abb10007] [sessionid: abb10000] [username: dilek] -------------------- Sending query to database named EXADATA (id: <<17263705>>), connection pool named pool_bidb_connection, logical request hash 21ea607c, physical request hash 5796f884: [[

select D1.c1 as c1 from ( select distinct case when T263703.CONSUMER_TYPE_ID in (1, 5) then 'Gerçek' when T263703.CONSUMER_TYPE_ID in (2, 3, 4) then 'Tüzel' else 'Bilinmiyor' end as c1

from

STPORTAL.EC_CONSUMER_DATA T263703

order by c1 ) D1 where rownum <= 65001

]]

[2017-05-18T09:05:11.000+03:00] [OracleBIServerComponent] [TRACE:5] [USER-0] [] [ecid: f9f346b6bdcdc990:350023a:15bf84fad0f:-8000-00000000000362be,0:1:34:3] [tid: 45df8940] [requestid: abb10008] [sessionid: abb10000] [username: dilek] ############################################## [[

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

c17ca36e

SET VARIABLE QUERY_SRC_CD='Report',SAW_DASHBOARD='/shared/Kayıt Yönetmenliği/_portal/Portal',SAW_DASHBOARD_PG='page 1',SAW_SRC_PATH='/shared/Kayıt Yönetmenliği/ST Portal/Reports/Tüketici Sayfası';SELECT

0 s_0,

"Portal"."Consumer Data"."Authorized Person Number1" s_1,

"Portal"."Consumer Data"."Authorized Person Number2" s_2,

"Portal"."Consumer Data"."Authorized Person Title1" s_3,

"Portal"."Consumer Data"."Authorized Person Title2" s_4,

"Portal"."Consumer Data"."Consumer Id" s_5,

"Portal"."Consumer Data"."Consumer Title" s_6,

"Portal"."Consumer Data"."Contract Power" s_7,

"Portal"."Consumer Data"."Distribution Meter Code" s_8,

"Portal"."Consumer Data"."Eic" s_9,

"Portal"."Consumer Data"."Main Tariff Group Id" s_10,

"Portal"."Consumer Data"."Main Tariff Group" s_11,

"Portal"."Consumer Data"."Metering Point Address" s_12,

"Portal"."Consumer Data"."Metering Point City" s_13,

"Portal"."Consumer Data"."Reading Organization Id" s_14,

"Portal"."Consumer Data"."Reading Organization" s_15,

"Portal"."Consumer Data"."Tariff Class" s_16,

"Portal"."Consumer Data"."Tariff Description" s_17

FROM "Portal"

WHERE

((("Consumer Data"."Consumer Id" IN (100000)) OR ("Consumer Data"."Authorized Person Number1" IN ('100000')) OR ("Consumer Data"."Authorized Person Number2" IN ('100000'))))

ORDER BY 1, 9 ASC NULLS LAST, 13 ASC NULLS LAST, 14 ASC NULLS LAST, 6 ASC NULLS LAST, 7 ASC NULLS LAST, 15 ASC NULLS LAST, 16 ASC NULLS LAST, 8 ASC NULLS LAST, 17 ASC NULLS LAST, 18 ASC NULLS LAST, 11 ASC NULLS LAST, 12 ASC NULLS LAST, 10 ASC NULLS LAST, 4 ASC NULLS LAST, 5 ASC NULLS LAST, 2 ASC NULLS LAST, 3 ASC NULLS LAST

FETCH FIRST 10000001 ROWS ONLY

]]

[2017-05-18T09:05:11.000+03:00] [OracleBIServerComponent] [TRACE:5] [USER-23] [] [ecid: f9f346b6bdcdc990:350023a:15bf84fad0f:-8000-00000000000362be,0:1:34:3] [tid: 45df8940] [requestid: abb10008] [sessionid: abb10000] [username: dilek] -------------------- General Query Info: [[

Repository: Star, Subject Area: Portal, Presentation: Portal

]]

[2017-05-18T09:05:11.000+03:00] [OracleBIServerComponent] [TRACE:5] [USER-2] [] [ecid: f9f346b6bdcdc990:350023a:15bf84fad0f:-8000-00000000000362be,0:1:34:3] [tid: 45df8940] [requestid: abb10008] [sessionid: abb10000] [username: dilek] -------------------- Logical Request (before navigation): [[

RqList distinct

0 as c1 GB,

Consumer Data.Authorized Person Number1 as c2 GB,

Consumer Data.Authorized Person Number2 as c3 GB,

Consumer Data.Authorized Person Title1 as c4 GB,

Consumer Data.Authorized Person Title2 as c5 GB,

Consumer Data.Consumer Id as c6 GB,

Consumer Data.Consumer Title as c7 GB,

Consumer Data.Contract Power as c8 GB,

Consumer Data.Distribution Meter Code as c9 GB,

Consumer Data.Eic as c10 GB,

Consumer Data.Main Tariff Group Id as c11 GB,

Consumer Data.Main Tariff Group as c12 GB,

Consumer Data.Metering Point Address as c13 GB,

Consumer Data.Metering Point City as c14 GB,

Consumer Data.Reading Organization Id as c15 GB,

Consumer Data.Reading Organization as c16 GB,

Consumer Data.Tariff Class as c17 GB,

Consumer Data.Tariff Description as c18 GB

DetailFilter: Consumer Data.Authorized Person Number1 in ([ '100000'] ) or Consumer Data.Consumer Id in ([ 100000] ) or Consumer Data.Authorized Person Number2 in ([ '100000'] )

OrderBy: c1 asc, c9 asc NULLS LAST, c13 asc NULLS LAST, c14 asc NULLS LAST, c6 asc NULLS LAST, c7 asc NULLS LAST, c15 asc NULLS LAST, c16 asc NULLS LAST, c8 asc NULLS LAST, c17 asc NULLS LAST, c18 asc NULLS LAST, c11 asc NULLS LAST, c12 asc NULLS LAST, c10 asc NULLS LAST, c4 asc NULLS LAST, c5 asc NULLS LAST, c2 asc NULLS LAST, c3 asc NULLS LAST

]]

[2017-05-18T09:05:11.000+03:00] [OracleBIServerComponent] [TRACE:5] [USER-21] [] [ecid: f9f346b6bdcdc990:350023a:15bf84fad0f:-8000-00000000000362be,0:1:34:5] [tid: 45df8940] [requestid: abb10008] [sessionid: abb10000] [username: dilek] -------------------- Cache Hit on query: [[

Matching Query: SET VARIABLE QUERY_SRC_CD='Report',SAW_DASHBOARD='/shared/Kayıt Yönetmenliği/_portal/Portal',SAW_DASHBOARD_PG='page 1',SAW_SRC_PATH='/shared/Kayıt Yönetmenliği/ST Portal/Reports/Tüketici Sayfası';SELECT

0 s_0,

"Portal"."Consumer Data"."Authorized Person Number1" s_1,

"Portal"."Consumer Data"."Authorized Person Number2" s_2,

"Portal"."Consumer Data"."Authorized Person Title1" s_3,

"Portal"."Consumer Data"."Authorized Person Title2" s_4,

"Portal"."Consumer Data"."Consumer Id" s_5,

"Portal"."Consumer Data"."Consumer Title" s_6,

"Portal"."Consumer Data"."Contract Power" s_7,

"Portal"."Consumer Data"."Distribution Meter Code" s_8,

"Portal"."Consumer Data"."Eic" s_9,

"Portal"."Consumer Data"."Main Tariff Group Id" s_10,

"Portal"."Consumer Data"."Main Tariff Group" s_11,

"Portal"."Consumer Data"."Metering Point Address" s_12,

"Portal"."Consumer Data"."Metering Point City" s_13,

"Portal"."Consumer Data"."Reading Organization Id" s_14,

"Portal"."Consumer Data"."Reading Organization" s_15,

"Portal"."Consumer Data"."Tariff Class" s_16,

"Portal"."Consumer Data"."Tariff Description" s_17

FROM "Portal"

WHERE

((("Consumer Data"."Consumer Id" IN (100000)) OR ("Consumer Data"."Authorized Person Number1" IN ('100000')) OR ("Consumer Data"."Authorized Person Number2" IN ('100000'))))

FETCH FIRST 10000001 ROWS ONLY

Created by: dilek

]]

[2017-05-18T09:05:11.000+03:00] [OracleBIServerComponent] [TRACE:5] [USER-55] [] [ecid: f9f346b6bdcdc990:350023a:15bf84fad0f:-8000-00000000000362be,0:1:34:5] [tid: 45df8940] [requestid: abb10008] [sessionid: abb10000] [username: dilek] -------------------- Order By Safely Removed [[

'OrderBy: D1.c9 asc NULLS LAST, D1.c13 asc NULLS LAST, D1.c14 asc NULLS LAST, D1.c6 asc NULLS LAST, D1.c7 asc NULLS LAST, D1.c15 asc NULLS LAST, D1.c16 asc NULLS LAST, D1.c8 asc NULLS LAST, D1.c17 asc NULLS LAST, D1.c18 asc NULLS LAST, D1.c11 asc NULLS LAST, D1.c12 asc NULLS LAST, D1.c10 asc NULLS LAST, D1.c4 asc NULLS LAST, D1.c5 asc NULLS LAST, D1.c2 asc NULLS LAST, D1.c3 asc NULLS LAST' above the cache hit plan is safely dropped due to the preserved order by ' OrderBy: c9 asc NULLS LAST, c13 asc NULLS LAST, c14 asc NULLS LAST, c6 asc NULLS LAST, c7 asc NULLS LAST, c15 asc NULLS LAST, c16 asc NULLS LAST, c8 asc NULLS LAST, c17 asc NULLS LAST, c18 asc NULLS LAST, c11 asc NULLS LAST, c12 asc NULLS LAST, c10 asc NULLS LAST, c4 asc NULLS LAST, c5 asc NULLS LAST, c2 asc NULLS LAST, c3 asc NULLS LAST' from cache entry

]]

0 s_0,

"Portal"."Consumer Data"."Authorized Person Number1" s_1,

"Portal"."Consumer Data"."Authorized Person Number2" s_2,

"Portal"."Consumer Data"."Authorized Person Title1" s_3,

"Portal"."Consumer Data"."Authorized Person Title2" s_4,

"Portal"."Consumer Data"."Consumer Id" s_5,

"Portal"."Consumer Data"."Consumer Title" s_6,

"Portal"."Consumer Data"."Contract Power" s_7,

"Portal"."Consumer Data"."Distribution Meter Code" s_8,

"Portal"."Consumer Data"."Eic" s_9,

"Portal"."Consumer Data"."Main Tariff Group Id" s_10,

"Portal"."Consumer Data"."Main Tariff Group" s_11,

"Portal"."Consumer Data"."Metering Point Address" s_12,

"Portal"."Consumer Data"."Metering Point City" s_13,

"Portal"."Consumer Data"."Reading Organization Id" s_14,

"Portal"."Consumer Data"."Reading Organization" s_15,

"Portal"."Consumer Data"."Tariff Class" s_16,

"Portal"."Consumer Data"."Tariff Description" s_17

FROM "Portal"

WHERE

((("Consumer Data"."Consumer Id" IN (100000)) OR ("Consumer Data"."Authorized Person Number1" IN ('100000')) OR ("Consumer Data"."Authorized Person Number2" IN ('100000'))))

FETCH FIRST 10000001 ROWS ONLY

Created by: dilek

]]

[2017-05-18T09:05:11.000+03:00] [OracleBIServerComponent] [TRACE:5] [USER-50] [] [ecid: f9f346b6bdcdc990:350023a:15bf84fad0f:-8000-00000000000362be,0:1:34:5] [tid: 45df8940] [requestid: abb10008] [sessionid: abb10000] [username: dilek] -------------------- The logical query seed the plan cache [[

plan

RqList <<17264482>> [for database 0:0,0] /* FETCH FIRST 10000001 ROWS ONLY */

0 as c1 [for database 0:0,1],

D1.c2 as c2 [for database 0:0,0],

D1.c3 as c3 [for database 0:0,0],

D1.c4 as c4 [for database 0:0,0],

D1.c5 as c5 [for database 0:0,0],

D1.c6 as c6 [for database 0:0,0],

D1.c7 as c7 [for database 0:0,0],

D1.c8 as c8 [for database 0:0,0],

D1.c9 as c9 [for database 0:0,0],

D1.c10 as c10 [for database 0:0,0],

D1.c11 as c11 [for database 0:0,0],

D1.c12 as c12 [for database 0:0,0],

D1.c13 as c13 [for database 0:0,0],

D1.c14 as c14 [for database 0:0,0],

D1.c15 as c15 [for database 0:0,0],

D1.c16 as c16 [for database 0:0,0],

D1.c17 as c17 [for database 0:0,0],

D1.c18 as c18 [for database 0:0,0]

Child Nodes (RqJoinSpec): <<17264520>> [for database 0:0,0]

RqJoinNode <<17264521>> []

(

RqList <<17264524>> [for database 0:0,9]

2 as c2 [for database 0:0,9],

3 as c3 [for database 0:0,9],

4 as c4 [for database 0:0,9],

5 as c5 [for database 0:0,9],

6 as c6 [for database 0:0,9],

7 as c7 [for database 0:0,9],

8 as c8 [for database 0:0,9],

9 as c9 [for database 0:0,9],

10 as c10 [for database 0:0,9],

11 as c11 [for database 0:0,9],

12 as c12 [for database 0:0,9],

13 as c13 [for database 0:0,9],

14 as c14 [for database 0:0,9],

15 as c15 [for database 0:0,9],

16 as c16 [for database 0:0,9],

17 as c17 [for database 0:0,9],

18 as c18 [for database 0:0,9]

Child Nodes (RqJoinSpec): <<17264560>> [for database 0:0,9]

RqJoinNode <<17264561>> []

CacheTable T1

) as D1

]]

[2017-05-18T09:05:11.000+03:00] [OracleBIServerComponent] [TRACE:5] [USER-16] [] [ecid: f9f346b6bdcdc990:350023a:15bf84fad0f:-8000-00000000000362be,0:1:34:5] [tid: 45df8940] [requestid: abb10008] [sessionid: abb10000] [username: dilek] -------------------- Execution plan: [[

RqList <<17264114>> [for database 0:0,0] /* FETCH FIRST 10000001 ROWS ONLY */

0 as c1 [for database 0:0,1],

D1.c2 as c2 [for database 0:0,0],

D1.c3 as c3 [for database 0:0,0],

D1.c4 as c4 [for database 0:0,0],

D1.c5 as c5 [for database 0:0,0],

D1.c6 as c6 [for database 0:0,0],

D1.c7 as c7 [for database 0:0,0],

D1.c8 as c8 [for database 0:0,0],

D1.c9 as c9 [for database 0:0,0],

D1.c10 as c10 [for database 0:0,0],

D1.c11 as c11 [for database 0:0,0],

D1.c12 as c12 [for database 0:0,0],

D1.c13 as c13 [for database 0:0,0],

D1.c14 as c14 [for database 0:0,0],

D1.c15 as c15 [for database 0:0,0],

D1.c16 as c16 [for database 0:0,0],

D1.c17 as c17 [for database 0:0,0],

D1.c18 as c18 [for database 0:0,0]

Child Nodes (RqJoinSpec): <<17264174>> [for database 0:0,0]

RqJoinNode <<17264173>> []

(

RqList <<17263910>> [for database 0:0,9]

2 as c2 [for database 0:0,9],

3 as c3 [for database 0:0,9],

4 as c4 [for database 0:0,9],

5 as c5 [for database 0:0,9],

6 as c6 [for database 0:0,9],

7 as c7 [for database 0:0,9],

8 as c8 [for database 0:0,9],

9 as c9 [for database 0:0,9],

10 as c10 [for database 0:0,9],

11 as c11 [for database 0:0,9],

12 as c12 [for database 0:0,9],

13 as c13 [for database 0:0,9],

14 as c14 [for database 0:0,9],

15 as c15 [for database 0:0,9],

16 as c16 [for database 0:0,9],

17 as c17 [for database 0:0,9],

18 as c18 [for database 0:0,9]

Child Nodes (RqJoinSpec): <<17263970>> [for database 0:0,9]

RqJoinNode <<17263969>> []

CacheTable T1495006248:883:1253846315:0x45df3de0

) as D1

[Deleted User]

So you have one physically executed query (which I suppose is for a deferred-execution init block) and one query that's a cache hit (logical request hash c17ca36e)

You still should have any entry for S_NQ_ACCT. Search the table for HASH_ID c17ca36e

dilek

No entry for any other queries in S_NQ_ACCT table. May be this is a bug? Why I can not see missing entries? I do not know what to do