Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 15 Oracle Analytics Lounge
- 208 Oracle Analytics News
- 41 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 76 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
About OBIEE Usage Tracking
Answers
-
I'd say jack up the log and see what's written in the file system logs vs what arrives in the UT tables.
0 -
Which log file should I look for comparing usage tracking entries? I already see nqserver.log and bi server logs. No related entry :S
0 -
obis1-query.log. Run some analyses and see what gets registered in the .log vs what's in the table.
0 -
I could not find this file :S Is it normal?
Here is my log folder; I searched all of them, but could not find!
0 -
Oh right, you're on 11, my bad. So then it's nqquery.log
0 -
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 */
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): <<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'))))
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
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
]]
[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'))))
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
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
0 -
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
0 -
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
0
![[Deleted User]](https://us.v-cdn.net/6037859/uploads/defaultavatar/nT2GKTLRQJ3P1.jpg)