Forum Stats

  • 3,853,698 Users
  • 2,264,256 Discussions
  • 7,905,434 Comments

Discussions

Search for Results Hangs

While waiting for results, a 10.3 dashboard connects and disconnects from a Oracle 9i database server okay, causing free memory on the BI server to change from 500MB to 100MB and back up to 400MB while connected. Plenty of swap available on both database server and BI server. Disk space is not an issue on either database or BI servers. However, the client search for results never ends/returns after database connection ends. Expecting about 120K rows. The related database queries take under a minute to return results in sqlplus. No Out of Memory errors are displayed on the dashboard page which shows the search is still in progress. There is some limited casting and substantial ordering in the query criteria. The IE 7 browser client is using about 700MB. Query is manually cancelled after an hour without any results. No errors logged to NQServer.log or NQQuery.log files.

Answers

  • Dimitris
    Dimitris Member Posts: 454
    hi,
    except from technical infos////
    Expecting about 120K rows
    ////
    client pc can accept some amount from values...

    BI means few results in order to make desicion...place more prompts(for dimensions) to filter your results.Your approach for BI results is not desired...

    hope i helped....

    ///////////////////////////////////////
    http://greekoraclebi.blogspot.com/
    ///////////////////////////////////////
  • 609043
    609043 Member Posts: 46
    Thanks for the input. The dashboard has a prompt where the end user can select almost ten different search parameters. I am testing the worst case scenario where the end user can request an unrestricted result set, but the dashboard is not returning any results in the unrestricted scenario. I suspect a memory or similar issue, but have not found any definitive evidence of the problem.
  • 609043
    609043 Member Posts: 46
    The following errors (?) were found in our sawserver.out.log file:

    Running job 'CleanupXMLDiskCache' took 111101 milliseconds, 92.5% of job's frequency (120 seconds).
    ---------------------------------------
    Type: Error
    Severity: 40
    Time: Mon Aug 17 14:54:51 2009
    File: project/webthreads/webscheduler.cpp Line: 262
    Properties: ThreadID-11
    Location:
    saw.taskScheduler.processJob
    saw.threadPool
    saw.threads

    Running job 'CleanupXMLDiskCache' took 111128 milliseconds, 92.6% of job's frequency (120 seconds).
    ---------------------------------------
    Type: Warning
    Severity: 40
    Time: Mon Aug 17 15:39:06 2009
    File: project/webodbcaccess/odbcstatementimpl.cpp Line: 132
    Properties: ConnId-1;StmtId-26;ThreadID-20
    Location:
    saw.odbc.statement.execute.timings
    saw.odbc.statement.execute
    saw.querycache.executeQuery
    saw.threadPool
    saw.threads

    SQLExecDirectW returned in 2390542 msec(s).
    ---------------------------------------
    Type: Warning
    Severity: 40
    Time: Mon Aug 17 15:57:24 2009
    File: project/webthreads/webscheduler.cpp Line: 262
    Properties: ThreadID-14
    Location:
    saw.taskScheduler.processJob
    saw.threadPool
    saw.threads

    Running job 'CleanupQueryCache' took 3530 milliseconds, 11.7% of job's frequency (30 seconds).
    ---------------------------------------
    Type: Warning
    Severity: 40
    Time: Mon Aug 17 16:16:49 2009
    File: project/webodbcaccess/odbcstatementimpl.cpp Line: 132
    Properties: ConnId-1;StmtId-31;ThreadID-20
    Location:
    saw.odbc.statement.execute.timings
    saw.odbc.statement.execute
    saw.querycache.executeQuery
    saw.threadPool
    saw.threads

    SQLExecDirectW returned in 2261588 msec(s).
    ---------------------------------------
    Type: Error
    Severity: 40
    Time: Mon Aug 17 16:16:49 2009
    File: project/webodbcaccess/odbcstatementimpl.cpp Line: 196
    Properties: ConnId-1;StmtId-31;ThreadID-20
    Location:
    saw.odbc.statement.execute
    saw.querycache.executeQuery
    saw.threadPool
    saw.threads

    What condition(s) do these errors represent? More importantly, how can the server be reconfigured to eliminate them?
  • Dimitris
    Dimitris Member Posts: 454
    hi,
    it is good to prevent results from all cases....
    so,in your case you can put some javascript to prevent this scenario.(check forum + blogs)
    Otherwise,i suppose you pass from dashboard prompts the "filters" to your answers...
    you can add there a case when statement in your measure or dimensions columns where you address your settings...
    *ie,you have 10 prompts,specify at any collumn(measure one - which is configured by the filters)that at least 4-5 filters must not be something(ie = 0) so to prevent bi server to execute the query..(not forget to pass in dashboard prompt a default value where this will be the criteria at the report..),so you drive the report to bring back an error message...(check blog of configuring messages)...



    hope i helped....

    ///////////////////////////////////////
    http://greekoraclebi.blogspot.com/
    ///////////////////////////////////////


    (please if possible make an other thread for your second question..))
  • 609043
    609043 Member Posts: 46
    I do not know why, but a client with a Firefox browser retrieves the result set while a client with an IE 7 browser does not.
  • Dimitris
    Dimitris Member Posts: 454
    Generally i prefer firefox,
    even when Oracle recommend Explorer for Bi suite...
    try to check the same report at the same machine...1.with firefox and 2.with explorer....
    probably the memory hangs out....
    and the possibilities of each client pc..


    hope i helped....

    ///////////////////////////////////////
    http://greekoraclebi.blogspot.com/
    ///////////////////////////////////////
This discussion has been closed.