We have a problem in our 10.1.3.4 Prod environment. When users login to Analytics it's taking 5+ minutes between entering credentials and actually pulling up a dashboard. It appears that the below SQL is what's causing the delay (note that if I run it directly against the DB via TOAD it also runs extremely slow):
SELECT 'GROUP', R.RESP
FROM NQ_LOGIN_GROUP R
WHERE R.LOGIN = 'e12345' OR UPPER (R.LOGIN) = 'e12345'
WHEN 0 THEN 'Hierarchy Level (Base)'
WHEN 1 THEN 'Hierarchy Level 1'
WHEN 2 THEN 'Hierarchy Level 2'
WHEN 3 THEN 'Hierarchy Level 3'
WHEN 4 THEN 'Hierarchy Level 4'
WHEN 5 THEN 'Hierarchy Level 5'
WHEN 6 THEN 'Hierarchy Level 6'
WHEN 7 THEN 'Hierarchy Level 7'
WHEN 8 THEN 'Hierarchy Level 8'
WHEN 9 THEN 'Hierarchy Level (Top)'
I'm not fully up-to-speed on Usage Tracking, but I understand that's the primary purpose of S_NQ_ACCT. We have about 470k rows in that table now, which doesn't seem like a lot. I also verified that the 3 default indexes are in place.
Any thoughts or suggestions on how to remedy?
SQL was edited by: JRH
select * from all_tables where table_name='S_NQ_ACCT' check when the table is analyzed using coumn last_analyzed whether the stats are taken properly or not , if tables is not propelry anlayzing ask your DBA to collect stats properly
look at nqquery.log file and observer which query is taking long time other than this(disable above intilization block and test)
NQ_LOGIN_GROUP -- verify this table also
Message was edited by: SaichandVaranasi
Thanks Saichand. Stats hadn't been updated on S_NQ_ACCT since April. I had our DBA run stats but unfortunately there was no performance improvement. Stats for NQ_LOGIN_GROUP were last gathered this morning.
In looking at the ngquery.log, I don't see anything with abnormal execution times, but I do see the following message frequently:
-------------------- Cluster-Aware Query Result Cache: [nQSError: 52047] Can not remove file, from global storage.
[nQSError: 46073] Operation 'DeleteFile()' on file '' failed with error: (3) The system cannot find the path specified..
Anyone have any thoughts on it?
I have searched in google with the same error code it gives me below URL it is applicable for 10g too please check
Did you try to disable the init block which is taking time after Stats ? use 'Union all' retsest it again
Disk space looks OK across the farm, both BI servers and WFEs have 20+ GB free.
Regarding the init block... How do I know which one to disable? And where can I change the SQL from union > union all?
Sorry for the rudimentary questions, OBIEE is not my primary skillset. I'm filling in for someone.
In Edit datasource of your variable you can change SQL as shown below
Right click on same Initilization block --> Disable
Sorry for the tardy reply. I haven't yet been able to locate that particular query in any of the init blocks, but I did run the SQL using UNION ALL via Toad and it still takes 8-10 minutes to complete. Do you know if there would be any adverse application impact if I were to truncate or delete a range of data from S_NQ_ACCT?