If longer execution times occur when you run segments or tasks executed by SAS Customer Intelligence 360, complete the following steps to investigate the issue:

1. Open the onprem_direct.log, which is located at <dm-agent-dir>/logs/onprem_direct.log.
2. Search for Time taken executing query, as shown in the example below:
   
   Time taken executing query: 48 minutes 48.596 seconds
   
   
3. After you identify the query, search for it in the log file. The query is always displayed right beneath the associated message:
   
   2025-03-26 11:25:17,940 INFO [CIExec-15] [05f686594946c243:7bdc796a:16260f91903:2eef] [sasdemo] com.sas.analytics.crm.custdata.sql       - TID[CIExec-15]
   sassched----- Execute Sql in MAIQService.execute() -----
   
   libname MAData oracle sql_function=all path="path-to-your-database" schema=datamart user=user-ID password=your-password; 
   
   proc sql;
      create table MATables.TAACGHZH24RHZUNCGsassched as
             select distinct
                ( table0.Customer ) as ID_Customer label='ID Customer' format=$64.,
                ( table0.Contract )as Contract label='Num Contract' format=F5.0
             from
                MAdata.Customer table0 left join MAData.Contract table1 on      
                  (table0.ID_Customer= table1.ID_Customer AND table0.Contract=table1.Contract) 
                   left join MAData.CONTRACT table2 on  (table2.ID_Customer=table0.ID_Customer                  and table2.Contract=table0.Contract)
             where
                table1.CONTACT_90_D>=5.0 and
                table1.CAMPAIGN_CD='CAMPxxx' and
                table2.COMMUNICATION_CD IN ('COMM12345', 'COMM6789');
   quit;
   
   
4. Ideally, you can just take this query and run it in either SAS^® Enterprise Guide^® or the Base SAS^® software. Add the following OPTIONS statements to the query before you execute it:

options sastrace=(,,,dq) sastraceloc=saslog nostsuffix fullstimer;
options sql_ip_trace=(note,source) debug=dbms_select;
options mprint;

These OPTIONS statements provide an overview of the time. More importantly, they show whether the SQL statement is passed to the database, as shown in the following example:

ACCESS ENGINE:  SQL statement was passed to the DBMS for fetching data.
NOTE: Table WORK.CHC88BC5C722B945CD20AA0_002 created, with 2 rows and 1 columns.
Summary Statistics for ORACLE are:
Total row fetch seconds were:                      
Total SQL execution seconds were:                  
Total SQL prepare seconds were:                    
Total SQL describe seconds were:                   
Total seconds used by the ORACLE ACCESS engine were 

If you cannot run the query in SAS Enterprise Guide or in the Base SAS software, you can enable trace, as shown below:

1. In [config]/Lev1/SASApp/PooledWorkspaceServer/logconfig.xml, change the following logger to DEBUG:
   
    <logger name="App.Program" immutability="false">
         <level value="Debug"/>
    </logger>
   
   
2. In [config]/Lev1/SASApp/appserver_autoexec_usermods.sas, add the following option:
   
   options sql_ip_trace=(note,source) debug=dbms_select;
   options mprint;
   
   Note: Take a backup of the files before making changes to them.
   
   
3. Restart the SAS Object Spawner for the two above changes to take effect.
   
   
4. Replicate the issue by executing the segment or task. Write down the time at which the replication was made.
   
   
5. Collect the PWS logs that are active during the timespan of the execution: [config]/Lev1/Logs/SASApp_PooledWSServer_YYYY-MM-DD_Machine_PID.log.
   
   
6. Collect the onprem_direct.log that is active during the timespan of the execution.
   
   
7. Share the time at which the Task was executed.