SQL to check Stale statistics

SQL to check Stale statistics

---Using Anonymous PL/SQL block 

SQL> SET SERVEROUTPUT ON

  DECLARE

ObjList dbms_stats.ObjectTab;

BEGIN

dbms_stats.gather_database_stats(objlist=>ObjList, options=>'LIST STALE');

FOR i in ObjList.FIRST..ObjList.LAST

LOOP

dbms_output.put_line(ObjList(i).ownname || '.' || ObjList(i).ObjName || ' ' || ObjList(i).ObjType || ' ' || ObjList(i).partname);

END LOOP;

END;

/

---Oracle 10g Onwards. 
col TABLE_NAME for a30
TABLE_NAME        STALE_STATS


BEGIN
DBMS_STATS.SET_GLOBAL_PREFS(‘AUTOSTATS_TARGET’,’ ORACLE’);
BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL); 

col PARTITION_NAME for a20

col SUBPARTITION_NAME for a20

select OWNER,TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,NUM_ROWS,LAST_ANALYZED from dba_TAB_STATISTICS where STALE_STATS='YES';

-- Oracle 12c

Statistics on a table are considered stale when more than STALE_PERCENT (default 10%) of the rows are changed (total number of inserts, deletes, updates) in the table. Oracle monitors the DML activity for all tables and records it in the SGA. The monitoring information is periodically flushed to disk, and is exposed in the *_TAB_MODIFICATIONS view. 

select table_name,inserts,updates,deletes

from USER_TAB_MODIFICATIONS

where table_name='PRODUCTS2';

TABLE_NAME   INSERTS  UPDATES  DELETES

PRODUCTS2      766             1532             32

Querying USER_TAB_MODIFICATIONS view to check DML activity on the PRODUCTS2 table

It is possible to manually flush this data by calling the procedure 

DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO if you want to get up-to-date information at query time (internally the monitoring data is flushed before all statistics collection operations). You can then see which tables have stale statistics by querying the STALE_STATS column in the USER_TAB_STATISTICS view.

sql> conn hr/hr
Connected
sql> select table_name,stale_stats
from user_tab_statistics;

COUNTRIES            NO
DEPARTMENTS       NO
JOBS                           NO
EMPLOYEES
PRODUCTS2              YES
LOCATIONS              NO

Querying USER_TAB_STATISTICS to see if any tables have stale statistics

Tables where STALE_STATS is set to NO, have up to date statistics. Tables where STALE_STATS is set toYES,havestalestatistics.Tables where STALE_STATS is not  set are missing statistics altogether. 

If you already have a well-established statistics gathering procedure, or if for some other reason you want to disable automatic statistics gathering for your main application schema, consider leaving it on for the dictionary tables. You can do this by changing the value of AUTOSTATS_TARGET to ORACLE instead of AUTO using DBMS_STATS.SET_GLOBAL_PREFS procedure.
END; /
To disable the task altogether:

END; / 

Check if profile option is enabled or not

Query to check the value of any profile option
like FND: Diagnostics or FND: Debug log etc.


login as apps user and run the below query. upon prompt enter the profile option name

SELECT fpo.profile_option_name SHORT_NAME,
         fpot.user_profile_option_name NAME,
         DECODE (fpov.level_id,
                 10001, 'Site',
                 10002, 'Application',
                 10003, 'Responsibility',
                 10004, 'User',
                 10005, 'Server',
                 'UnDef')
            LEVEL_SET,
         DECODE (TO_CHAR (fpov.level_id),
                 '10001', '',
                 '10002', fap.application_short_name,
                 '10003', frsp.responsibility_key,
                 '10005', fnod.node_name,
                 '10006', hou.name,
                 '10004', fu.user_name,
                 'UnDef')
            "CONTEXT",
         fpov.profile_option_value VALUE
    FROM fnd_profile_options fpo,
         fnd_profile_option_values fpov,
         fnd_profile_options_tl fpot,
         fnd_user fu,
         fnd_application fap,
         fnd_responsibility frsp,
         fnd_nodes fnod,
         hr_operating_units hou
   WHERE     fpo.profile_option_id = fpov.profile_option_id(+)
         AND fpo.profile_option_name = fpot.profile_option_name
         AND fu.user_id(+) = fpov.level_value
         AND frsp.application_id(+) = fpov.level_value_application_id
         AND frsp.responsibility_id(+) = fpov.level_value
         AND fap.application_id(+) = fpov.level_value
         AND fnod.node_id(+) = fpov.level_value
         AND hou.organization_id(+) = fpov.level_value
         AND fpot.user_profile_option_name like('&User_Profile_Option_Name')
ORDER BY short_name;

Check trace enabled at concurrent program level

How to check if trace is enabled for Concurrent program

Option 1:

you can login to oracle Ebusiness Suite
go to System Administrator responsibility.
Concurrent--> Program
Query for the concurrent pogram name
check if the check box enable trace for trace enabled or not.


Option 2:

login as apps user. Run the below query . I will show all concurrent programs that has trace enabled.

SELECT A.CONCURRENT_PROGRAM_NAME "Program Name",
SUBSTR(A.USER_CONCURRENT_PROGRAM_NAME,1,40) "User Program Name",
SUBSTR(B.USER_NAME,1,15) "Last Updated By",
SUBSTR(B.DESCRIPTION,1,25) DESCRIPTION
FROM APPS.FND_CONCURRENT_PROGRAMS_VL A, APPLSYS.FND_USER B
WHERE A.ENABLE_TRACE='Y'
AND A.LAST_UPDATED_BY=B.USER_ID;