Wasn't sure whether to post this here or in the ArcGIS Server forums. But considering it's about Oracle tracing related to an ST_GEOMETRY full table scan problem, I chose this one.
I want to trace ArcGIS Server 9.3.1 SP2 activity in Oracle 11.2.0.2.0. We suffer from inappropriate full table scans when sde.st_EnvIntersects is used. We get full table scans even when the geographic extent is quite small. Unfortunately, 9.3.1 SP2 failed to 'DISASSOCIATE STATISTICS FROM PACKAGES st_domain_operators'. The sdesetup -o upgrade logfile reports this SQL statement as having executed. But the results from DBA_ASSOCIATIONS told another story (e.g. it either didn't execute or something else reassociated it). Oddly, 9.3.1 SP2 also nuked our SDE_LOGPOOL_<integer> tables and emptied table SDE_LOGFILE_POOL. But that's another issue, resolved by running sdeconfig and tweaking the parameter that governs the number of logfile tables (doing so recreated them).
Anyway, I want to perform a trace to see if this operation solved the full table scan problem.
But our ArcSOC.exe processes use shared server connections in Oracle. So when I initiate a trace for the module (ArcSOC.exe) in OEM, I can see a trace file start to grow larger. But it seems to be a trace file that already exists (example: <instance>_s000_22493.trc). To isolate activity to just the duration of time I'm interested in, I'd like to start with a fresh trace file. Is there any way to do that? Must I delete currently active trace files or would that cause a failure?
When tracing a dedicated server session, I've noticed that a new trace file will be generated with "ora" in its name. Evidently not so with shared server stuff, whether it's tracing a module or a particular session. When the shared server model is used, it seems Oracle wants to reuse existing trace files. And since trace files an get quite large, it would be difficult to open the trace file in an editor and remove the pre-existing, older activity that I don't want to work with.
So what's the recommend approach to get appropriate tracing information in a shared server environment?
Hi Dana, if you are using 10g or later you should be able to use this:
DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(...)
according to the oracle documentation:
The SERV_MOD_ACT_TRACE_ENABLE procedure enables SQL tracing for a given combination of service name, module, and action globally for a database, unless an instance name is specified in the procedure.
After tracing information is written to files, the information can be consolidated by the trcsess utility and diagnosed with an analysis utility such as TKPROF.
