How to tell origin of SQL from Content Server database?

I have identified some apparently inefficient queries that have executed against the Content Server database (using Oracle SQL Developer and its DBA reports). Is there a way to tell whether the queries originate from Live Reports vs internal Content Server functions? Either based on some property in the SQL logs or on the style of the SQL? All of them are identified by Oracle as originating from the same llserverworker account regardless.

Find more posts tagged with

Note:

If there are Accepted Answers, those will be shown by default. You can switch to 'All Replies' by selecting the tab below.

Accepted answers

Stephane Genoux

The details of the reports being stored inline in DTREE, you can run the following SQL statement to get a decent summary of what is hiding in there:
(Note: works here with Oracle / CS 16.2.5+. Can certainly be improved using a WR on top the dataset)

with reports as ( select DATAID, NAME, REGEXP_SUBSTR(EXTENDEDDATA,'databaseSelect''=''([^'']+)'',',1,1,null,1) as Database, REGEXP_SUBSTR(EXTENDEDDATA,'databaseSelect''=''&\*([^'']+)'',',1,1,null,1) as remoteDB, REGEXP_SUBSTR(EXTENDEDDATA,'sql''=''(.+?)'',''',1,1,null,1) as sqlStatement, REGEXP_SUBSTR(EXTENDEDDATA,'usecursor''=(true|false)',1,1,null,1) as usecursor, REGEXP_SUBSTR(EXTENDEDDATA,'usetransact''=(true|false)',1,1,null,1) as usetransact, EXTENDEDDATA from dtreecore where subtype = 299 ) select reports.DATAID, reports.name, nvl(nvl(dtreecore.name,reports.Database),'currentlivelink') as database, usecursor, usetransact, replace(replace(reports.sqlStatement,'\r\n',' '),'\''','''') as sqlStatement from reports left join dtreecore on to_number(reports.remotedb) = dtreecore.dataid order by database, name, dataid

If the SQL statement you are looking for is not in the list, it is likely to be an "internal" one.

All comments

Appu_Nair1

If you were "splunking" Timings .csv then your LR's would say ?func=runreport you could co relate the time that way some how.If you were doing thread logs and connect logs the connect logs have a "marker" in them to co relate with threadlogs . Understanding logs published by OT and maybe Ryan Long has some info in them although things have changed in many ways in 16 versions and I am not keeping abreast.. I have also looked at SQL that it produces then ran brute searches in LL code to see if I can find the SQL.

I am not sure if just seeing the HOST info that is one of the threads of Livelink you can say positively did it happen because of API or a report . Also all LR's SQL is in the extended data of that node so you can take it and just use a matching program to rule out if it indeed is coming out of a LR.
Just some thoughts I had maybe the gurus will say better ways....

John Simon

Patrick, another thought. Very few people write sql and get the correct case of the OT table names but the code always does (in case it executes against a case sensitive database). For example most users would write "select * from dtree" but OT code would write "select * from DTree". Definitely not 100% fool proof but may be worth a shot.
Cheers...

Stephane Genoux

If the SQL statement you are looking for is not in the list, it is likely to be an "internal" one.

Patrick_McManus

@Stephane Genoux said:
The details of the reports being stored inline in DTREE, you can run the following SQL statement to get a decent summary of what is hiding in there

If the SQL statement you are looking for is not in the list, it is likely to be an "internal" one.

I ran this query against tow of my Content Server databases, and compared the SQL from the LiveReports against some queries I was trying to trace from the database logs, and they don't match any of the Live Reports. Apart from LiveReports and core Content Server, are there any other features that could be making these queries? My concern as that they are doing full table scans against tables with millions of rows.

Here is an example of a query that did a full scan of DAuditNew, origin unknown but not from a Live Report:
SELECT
DataID,
AuditDate,
Value1,
Value2,
PerformerID,
RowNum
FROM
(
SELECT
A.DataID DataID,
A.AuditDate AuditDate,
A.Value1 Value1,
A.Value2 Value2,
A.PerformerID PerformerID,
ROW_NUMBER() OVER(ORDER BY A.AuditDate desc) RowNum
FROM
DAuditNew A
inner join DTree D on A.DataID = D.DataID
WHERE
A.AuditStr = 'Fetch'
) ResultSet
WHERE
RowNum BETWEEN 1 AND ( 1 + 99)
;

Appu_Nair1

do you think other applications RAW SQL tools Toad ,SQLdeveloper Studio ,Home grown programs etc.. at the CAPI layer and DBWIZapi level it will show you all the stuff that a Livelink server receives but it cannot account for external tools you need to go by the host of that. Also if this is coming from Oscript just turn on connect logs find the thread marker in the connect log and there you go.

Patrick_McManus

Here's another example:
-- THIS DOES A FULL TABLE SCAN ACROSS WEBNODESMETA_EN_CA (Over 100 M rows)
-- Function-based indexes on WEBNODESMETA_EN_CA and WEBNODESMETA_FR_CA
-- Oracle Cost = 1335329

SELECT /+ BIND_AWARE NOPARALLEL */
OwnerID,
DataID ,
Catalog,
GroupID,
PermID
FROM
(
SELECT
a.,
rownum r__
FROM
(
SELECT /*+ BIND_AWARE */
OwnerID,
dt.DataID,
Catalog,
GroupID,
PermID
FROM
DTree dt
INNER JOIN WebNodesMeta_en_CA wnm ON wnm.DataID = dt.DataID
WHERE dt.SubType=:A1
ORDER BY
NLSSORT( wnm.Name, 'NLS_SORT=GENERIC_M_AI' ) ASC
) a
WHERE
rownum <= :B2
)
WHERE
r__ >= :B1
;

@Appu_Nair1 said:
do you think other applications RAW SQL tools Toad ,SQLdeveloper Studio ,Home grown programs etc.. at the CAPI layer and DBWIZapi level it will show you all the stuff that a Livelink server receives but it cannot account for external tools you need to go by the host of that. Also if this is coming from Oscript just turn on connect logs find the thread marker in the connect log and there you go.

Patrick_McManus

All of these queries are originating from llserverworker, which I assume means from within Content Server, not external tools.

Appu_Nair1

I still am not sure the reason why you are asking are you trying to bring to the attention of OT that there are bad queries. If so the proper way we all do this is raise a ticket with OT showing in connect logs a bad thing occurs. OT most often will give you a enhancement number. Now if I am better at writing the query I may include that in my ticket and probably patch it in my place knowing that OT does not like people like us changing code. Have I done it most certainly yes.
For what its worth I ran the two queries you posted in CSIDE(Builder) on 16.2.6 so it is not there so possibilities are
1. You are running higher or lower Llserver
2. You could have modules that I don't have.
3. Maybe there is a patch

Patrick_McManus

I am asking because I am trying to determine if the queries have been written by our clients' users or whether they are intrinsic to Content Server. That will tell me whether I need to open a ticket with OpenText or whether I can push back to the client.

Patrick_McManus

BTW the platform is CS 10.5, not 16.2.x.

Greg Griffiths

I've seen similar code in the Container Browse process, but the object names etc are added dynamically depending on the locale for the user, config etc, so you may be better searching for snippets of the SQL rather than all of it