I am using SQL Passthru in my WebFOCUS code. In the SQL query of the passthru, I am joining 6 tables and the WebFOCUS engine does not generate a result set. It keeps churning and churning, but no result set.

However, when I run this code directly in SQL Developer on the Oracle database, it produces a result set in 2 to 3 minutes. One of things I've been doing is debugging my joins in the passthru query and I found the table in the join that seems to be causing the performance bottleneck in WebFOCUS, which is a table I need in the query.

Please let me know if anyone has any suggestions on this issue.

This message has been edited. Last edited by: Kerry,

The first thing I would do is determine what Oracle is doing while you are waiting on WebFocus. You may not be waiting on Oracle, but for the answer set to travel over your network. There are several different ways you can determine this. One is watching the network traffic on the FOCUS reporting server, or use the built in Oracle Monitors.

Tuning the query I like to start with an explain. Good luck it sounds like an "interesting" tuning challenge,

This is the error message I finally got when it timed out.....still troubleshooting. Any more thougts, please let me know.

FOC198) FATAL ERROR IN DATABASE I/O. FOCUS TERMINATING ERROR WRITING FOCSORT
-* , PAGE 11471016, CODE 0xffffffff

Hi Sashanka,

Thanks for the comments and suggestion. I am holding 25 fields in the hold file from the SQLOUT passthru. I ended up commenting out up to 11 of those fields including one that was defined as a 2000 varchar field and I still get the same thing....timing out with the same error after executing.

How do you increase the space in the temp folders for writing the internal focus file for sorting? Do you know if this a setting in the Adminstrator Console or Reporting Server? Console?

It simply depends on the disk space of the Reporting server temp folders.
But the real issue here might be somthing else.
Can you post your code.

Try using TABLEF instead of TABLE...

As I said if you post your code it might be helpful...

I had already tried using TABLEF and I got the same. I put a recordlimit on the query and I was able to get records returned in the relative amount of time.

Here is the code:


ENGINE SQLORA SET DEFAULT_CONNECTION &CONNECTION.EVAL
SQL SQLORA PREPARE SQLOUT FOR
SELECT
T1.RELEASE_ID,
T1.RELEASE_LABEL,
T2.SGL_PART,
T3.STMT_GRP,
T3.STMT_TITLE,
T3.TAS_CONTROL_ATTR,
T3.TAS_CONTROL_DOM_VAL,
T5.RELEASE_ID,
T5.SGL_FY,
T5.STMT_ID,
T5.STMT_DETAIL_TYPE,
T5.STMT_DETAIL_SEQ,
T5.STMT_DETAIL_ID,
T5.STMT_DETAIL_DESCR,
T5.STMT_DETAIL_NORM_BAL ,
T5.STMT_DETAIL_LABEL,
T5.STMT_DETAIL_BAL_VIEW,
T5.BREAK_OUT_SGL_ATTR_ID,
T5.BREAK_OUT_SGL_ATTR_ID_2,
T7.SGL_DOM_VALUE AS SGL_DOM_VAL,
T7.SGL_ACCT AS USSGL_SGL_ACCT,
T7.STMT_SGL_XWALK_GRP_ID,
T7.SGL_ATTR_ID,

T8.SGL_ATTR_TYP,
T8.SGL_ATTR_VALIDATE_TYPE

-*T9.SGL_DOM_VALUE_DESCR

FROM SGL_RELEASE T1
INNER JOIN SGL_RELEASE_PART T2
ON T1.RELEASE_ID = T2.RELEASE_ID

INNER JOIN STMT T3
ON T2.RELEASE_ID = T3.RELEASE_ID
AND T2.SGL_FY = T3.SGL_FY

-*I added this join for now. - 5/12/11
LEFT
OUTER JOIN STMT_ATTR T4
ON T3.RELEASE_ID = T4.RELEASE_ID
AND T3.SGL_FY = T4.SGL_FY
AND T3.STMT_ID = T4.STMT_ID

LEFT
OUTER JOIN STMT_DETAIL T5
ON T3.RELEASE_ID = T5.RELEASE_ID
AND T3.SGL_FY = T5.SGL_FY
AND T3.STMT_ID = T5.STMT_ID

LEFT
OUTER JOIN STMT_SGL_XWALK T7
ON T5.RELEASE_ID = T7.RELEASE_ID
AND T5.SGL_FY = T7.SGL_FY
AND T5.STMT_DETAIL_ID = T7.STMT_DETAIL_ID

LEFT
OUTER JOIN SGL_ATTR T8
ON T7.RELEASE_ID = T8.RELEASE_ID
AND T7.SGL_FY = T8.SGL_FY
AND T4.SGL_ATTR_ID = T8.SGL_ATTR_ID

-*Already commented out
-*INNER JOIN SGL_ATTR_DOMAIN_VALUES T9
-*ON T8.RELEASE_ID = T9.RELEASE_ID
-*AND T8.SGL_FY = T9.SGL_FY
-*AND T8.SGL_ATTR_ID = T9.SGL_ATTR_ID

WHERE T5.RELEASE_ID = '&RELEASE.EVAL'
AND T2.SGL_PART = '&PART.EVAL'
AND T3.STMT_ID = '&STMT_ID.EVAL'
;
END
-RUN

-*
-*------------------------------------------
-* Put the data into a hold file *
-*------------------------------------------
-*TABLE FILE SQLOUT
-*PRINT
-**
-*END
-*-RUN
-*-EXIT

TABLE FILE SQLOUT
PRINT
SGL_ATTR_TYP
SGL_ATTR_VALIDATE_TYPE
-*Already commented out
-*SGL_DOM_VALUE_DESCR
-*Already commented out
STMT_DETAIL_SEQ
STMT_DETAIL_LABEL
SGL_ATTR_ID
SGL_DOM_VAL
STMT_DETAIL_DESCR
STMT_DETAIL_NORM_BAL
USSGL_SGL_ACCT
STMT_SGL_XWALK_GRP_ID
STMT_GRP
STMT_TITLE
TAS_CONTROL_ATTR
TAS_CONTROL_DOM_VAL
RELEASE_ID
RELEASE_LABEL
STMT_DETAIL_BAL_VIEW
BREAK_OUT_SGL_ATTR_ID
BREAK_OUT_SGL_ATTR_ID_2
BY RELEASE_ID
BY SGL_PART
BY SGL_FY
BY STMT_ID
BY STMT_DETAIL_ID
BY STMT_DETAIL_TYPE
-*WHERE RECORDLIMIT EQ 100
-*ON TABLE HOLD AS HALL
END
-RUN
-EXIT

Hi,

I think the problem is with the number of sort fields you have and the number of rows returned...by default the focus file FOCSORT has a limit of 2GB (it might be 4 GB iam not sure)....
Can you try without the BY fields or try to do the sorting/grouping in SQL itself and bring the presorted rows into WF environ...

I am not sure if this is any helpful..but just my thoughts as I see the code..

thanks
Sashanka

I did the sorting in the SQL itself, removed the BY fields in WF query, and used TABLEF...and now I'm getting the following standard Oracle DB error relative to size/space issues in DB. I have asked the DBA to extend this tablespace.

(FOC1400) SQLCODE IS 1652 (HEX: 00000674)
: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
: ORA-27072: File I/O error
: Additional information: 2
L (FOC1406) SQL OPEN CURSOR ERROR. : SQLOUT

Permit me three observations.

1. "I had already tried using TABLEF and I got the same."

Huh? -- How could you get a FOCSORT error, when TABLEF does not use FOCSORT????



2. The error message with the string of hex f's indicates you exceeded the limit on the size (number of pages) for the internal matrix.

Are you sure you need to extract that many records? If so, I suggest you divvy up the population and process it in chunks. That should allow you to pass the sorting off to Oracle where it belongs.


3. Not surprising that your TABLE ran slow. You have a classic Modify bottleneck.

With TABLE, Focus (or WebFocus, same thing) builds an "internal matrix", which is basically a Focus database. And the "sort" process is basically a Modify taking the incoming records as transactions. (yes, there's a Modify hidden inside every Table request)

You have a single verb and six sort keys

BY RELEASE_ID
BY SGL_PART
BY SGL_FY
BY STMT_ID
BY STMT_DETAIL_ID
BY STMT_DETAIL_TYPE 

in your TABLE request. So that internal matrix/Focus file has a single, SEGTYPE=S6, segment (and longs records). [actually S7, since PRINT generates an extra key as a tie-breaker.]

If the data coming from Oracle is sorted to conform to the 6 keys, the matrix will load quickly. If not (and you have no ORDER BY in your SQL code, so not it is), then every time there is a fault in the sort order of incoming records the Modify must REPOSITION and scan through all the rows that it has already loaded, looking for the place in the chain where this record belongs. Classic Modify bottleneck.

If you cannot control the sort order of incoming transactions, you can improve the efficiency by making the internal matrix a seven segment structure. That entails using a multiple-verb request.

write nada by key1
write nada by key1 by key2
...
write nada by key1 by key2 ... by key6
print (all the dependent fields) by key1 by key2 ... by key6

When a sort fault occurs, the Modify might not need to reposition all keys; and the process of sifting through the stored data to find the match point or insert point in each of the six chains would involve segments with very compact records, rather than humongously long ones.

Hi J. Gross,

I was able to do the sorting on the 6 fields in the actual SQL and removed the by fields in the WebFOCUS query. Now I am getting the database space issue error and an I/O error:

FOC1400) SQLCODE IS 1652 (HEX: 00000674)
: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
: ORA-27072: File I/O error

The DBA extended the tablespace the maximum amount of space that he had left. Giving this added space and adding the sorting to the actual SQL, I now get the general "No query information to decode" message when it times out.

Which sounds like a time-out message.

Hey, if you're pulling a gazillion records, SQL has to sort them, they have to come over the wire to WFRS (EDA Server), which has to organize them (if you haven't switched to TABLEF) and then format them as HTML and ship them to the webserver, to ship to the browser, which will certainly choke on the sheer volume.

What do you really intend to do with this data?

This data from the passthru is in an include fex. I do another level of filtering on it. Then, I do another SQL Passthru to get another type of records (these are records are related to a financial statement report). Next, I merge these to hold files from the 2 SQL passthru steps. Basically, the end of this include fex results in a hold file with all my records in a certain format that I use as one side of a join in my main fex.

Two SQL Passthru extracts, then a WebFOCUS JOIN of the HOLD files to create another HOLD file which becomes the driver for another JOIN? - the Classic Reporting Newbie Technique!

You should be joining the tables in ONE SQL Passthru statement so that Oracle does the join and hopefully reduces the number of rows in the answer set.

By the way, it doesn't matter that one SQL Passthru is in an INCLUDE and the other is in the main fex - that makes no difference to the outcome of this report.

Another thing to try is to get your DBA to monitor performance of the query in grid and add indexes to the tables as needed. This will increase performance.
I have never had a problem with SQL passthru but we are not processing massive amounts of data.