Tables/Indexes

Here are some scripts related to Tables/Indexes .
Tabs w/ Questionable Inds
TABLES WITH QUESTIONABLE INDEX(ES) NOTES:

• Owner - Owner of the table
• Table Name - Name of the table
• Column - Name of the column in question
• The above query shows all tables that have more than one index with the same leading column. These indexes can cause queries to use an inappropriate indexes; in other words, Oracle will use the index that was created most recently if two indexes are of equal ranking. This can cause different indexes to be used from one environment to the next (e.g., from DEV to TEST to PROD).
• The information does not automatically indicate that an index is incorrect; however, you may need to justify the existence of each of the indexes above.

select TABLE_OWNER,
TABLE_NAME,
COLUMN_NAME
from dba_ind_columns
where COLUMN_POSITION=1
and TABLE_OWNER not in ('SYS','SYSTEM')
group by TABLE_OWNER, TABLE_NAME, COLUMN_NAME
having count(*) > 1

Tabs With More Than 5 Inds
TABLES WITH MORE THAN 5 INDEXES NOTES:
• Owner - Owner of the table
• Table Name - Name of the table
• Index Count - Number of indexes

select OWNER,
TABLE_NAME,
COUNT(*) index_count
from dba_indexes
where OWNER not in ('SYS','SYSTEM')
group by OWNER, TABLE_NAME
having COUNT(*) > 5
order by COUNT(*) desc, OWNER, TABLE_NAME

Tables With No Indexes
TABLES WITHOUT INDEXES NOTES:
• Owner - Owner of the table
• Table Name - Name of the table

select OWNER,
TABLE_NAME
from
(
select OWNER,
TABLE_NAME
from dba_tables
minus
select TABLE_OWNER,
TABLE_NAME
from dba_indexes
)
orasnap_noindex
where OWNER not in ('SYS','SYSTEM')
order by OWNER,TABLE_NAME

Tables With No PK
NO PRIMARY KEY NOTES:
• Table Owner - Owner of the table
• Table Name - Name of the table

select OWNER,
TABLE_NAME
from dba_tables dt
where not exists (
select 'TRUE'
from dba_constraints dc
where dc.TABLE_NAME = dt.TABLE_NAME
and dc.CONSTRAINT_TYPE='P')
and OWNER not in ('SYS','SYSTEM')
order by OWNER, TABLE_NAME

Disabled Constraints
DISABLED CONSTRAINT NOTES:
• Owner - Owner of the table
• Table Name - Name of the table
• Constraint Name - Name of the constraint
• Constraint Type - Type of constraint
• Status - Current status of the constraint

select OWNER,
TABLE_NAME,
CONSTRAINT_NAME,
decode(CONSTRAINT_TYPE, 'C','Check',
'P','Primary Key',
'U','Unique',
'R','Foreign Key',
'V','With Check Option') type,
STATUS
from dba_constraints
where STATUS = 'DISABLED'
order by OWNER, TABLE_NAME, CONSTRAINT_NAME

FK Constraints
FOREIGN KEY CONSTRAINTS NOTES:
• Table Owner - Owner of the table
• Table Name - Name of the table
• Constraint Name - Name of the constraint
• Column Name - Name of the column
• Referenced Table - Name of the referenced table
• Reference Column - Name of the referenced column
• Position - Position of the column

select c.OWNER,
c.TABLE_NAME,
c.CONSTRAINT_NAME,
cc.COLUMN_NAME,
r.TABLE_NAME,
rc.COLUMN_NAME,
cc.POSITION
from dba_constraints c,
dba_constraints r,
dba_cons_columns cc,
dba_cons_columns rc
where c.CONSTRAINT_TYPE = 'R'
and c.OWNER not in ('SYS','SYSTEM')
and c.R_OWNER = r.OWNER
and c.R_CONSTRAINT_NAME = r.CONSTRAINT_NAME
and c.CONSTRAINT_NAME = cc.CONSTRAINT_NAME
and c.OWNER = cc.OWNER
and r.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
and r.OWNER = rc.OWNER
and cc.POSITION = rc.POSITION
order by c.OWNER, c.TABLE_NAME, c.CONSTRAINT_NAME, cc.POSITION

FK Index Problems
FK CONSTRAINTS WITHOUT INDEX ON CHILD TABLE NOTES:
• Owner - Owner of the table
• Constraint Name - Name of the constraint
• Column Name - Name of the column
• Position - Position of the index
• Problem - Nature of the problem
• It is highly recommended that an index be created if the Foreign Key column is used in joining, or often used in a WHERE clause. Otherwise a table level lock will be placed on the parent table.

select acc.OWNER,
acc.CONSTRAINT_NAME,
acc.COLUMN_NAME,
acc.POSITION,
'No Index' Problem
from dba_cons_columns acc,
dba_constraints ac
where ac.CONSTRAINT_NAME = acc.CONSTRAINT_NAME
and ac.CONSTRAINT_TYPE = 'R'
and acc.OWNER not in ('SYS','SYSTEM')
and not exists (
select 'TRUE'
from dba_ind_columns b
where b.TABLE_OWNER = acc.OWNER
and b.TABLE_NAME = acc.TABLE_NAME
and b.COLUMN_NAME = acc.COLUMN_NAME
and b.COLUMN_POSITION = acc.POSITION)
order by acc.OWNER, acc.CONSTRAINT_NAME, acc.COLUMN_NAME, acc.POSITION

Inconsistent Column Names
INCONSISTENT COLUMN DATATYPE NOTES:
• Owner - Owner of the table
• Column - Name of the column
• Table Name - Name of the table
• Datatype - Datatype of the column

select OWNER,
COLUMN_NAME,
TABLE_NAME,
decode(DATA_TYPE, 'NUMBER', DATA_PRECISION, DATA_LENGTH) datatype
from dba_tab_columns
where (COLUMN_NAME, OWNER) in
(select COLUMN_NAME,
OWNER
from dba_tab_columns
group by COLUMN_NAME, OWNER
having min(decode(DATA_TYPE, 'NUMBER', DATA_PRECISION, DATA_LENGTH)) <
max(decode(DATA_TYPE, 'NUMBER', DATA_PRECISION, DATA_LENGTH)) )
and OWNER not in ('SYS', 'SYSTEM')
order by COLUMN_NAME,DATA_TYPE

Object Extent Warning
TABLES THAT CANNOT EXTEND NOTES:
• Owner - Owner of the object
• Object Name - Name of the object
• Object Type - Type of object
• Tablespace - Name of the tablespace
• Next Extent - Size of next extent (bytes)

select OWNER,
SEGMENT_NAME,
SEGMENT_TYPE,
TABLESPACE_NAME,
NEXT_EXTENT
from (
select seg.OWNER,
seg.SEGMENT_NAME,
seg.SEGMENT_TYPE,
seg.TABLESPACE_NAME,
t.NEXT_EXTENT
from dba_segments seg,
dba_tables t
where (seg.SEGMENT_TYPE = 'TABLE'
and seg.SEGMENT_NAME = t.TABLE_NAME
and seg.owner = t.OWNER
and NOT EXISTS (
select TABLESPACE_NAME
from dba_free_space free
where free.TABLESPACE_NAME = t.TABLESPACE_NAME
and BYTES >= t.NEXT_EXTENT))
union
select seg.OWNER,
seg.SEGMENT_NAME,
seg.SEGMENT_TYPE,
seg.TABLESPACE_NAME,
c.NEXT_EXTENT
from dba_segments seg,
dba_clusters c
where (seg.SEGMENT_TYPE = 'CLUSTER'
and seg.SEGMENT_NAME = c.CLUSTER_NAME
and seg.OWNER = c.OWNER
and NOT EXISTS (
select TABLESPACE_NAME
from dba_free_space free
where free.TABLESPACE_NAME = c.TABLESPACE_NAME
and BYTES >= c.NEXT_EXTENT))
union
select seg.OWNER,
seg.SEGMENT_NAME,
seg.SEGMENT_TYPE,
seg.TABLESPACE_NAME,
i.NEXT_EXTENT
from dba_segments seg,
dba_indexes i
where (seg.SEGMENT_TYPE = 'INDEX'
and seg.SEGMENT_NAME = i.INDEX_NAME
and seg.OWNER = i.OWNER
and NOT EXISTS (
select TABLESPACE_NAME
from dba_free_space free
where free.TABLESPACE_NAME = i.TABLESPACE_NAME
and BYTES >= i.NEXT_EXTENT))
union
select seg.OWNER,
seg.SEGMENT_NAME,
seg.SEGMENT_TYPE,
seg.TABLESPACE_NAME,
r.NEXT_EXTENT
from dba_segments seg,
dba_rollback_segs r
where (seg.SEGMENT_TYPE = 'ROLLBACK'
and seg.SEGMENT_NAME = r.SEGMENT_NAME
and seg.OWNER = r.OWNER
and NOT EXISTS (
select TABLESPACE_NAME
from dba_free_space free
where free.TABLESPACE_NAME = r.TABLESPACE_NAME
and BYTES >= r.NEXT_EXTENT))
)
orasnap_objext_warn
order by OWNER,SEGMENT_NAME

Segment Fragmentation
OBJECTS WITH MORE THAN 50% OF MAXEXTENTS NOTES:
• Owner - Owner of the object
• Tablespace Name - Name of the tablespace
• Segment Name - Name of the segment
• Segment Type - Type of segment
• Size - Size of the object (bytes)
• Extents - Current number of extents
• Max Extents - Maximum extents for the segment
• Percentage - Percentage of extents in use
• As of v7.3.4, you can set MAXEXTENTS=UNLIMITED to avoid ORA-01631: max # extents (%s) reached in table $s.%s.
• To calculate the MAXEXTENTS value on versions < 7.3.4 use the following equation: DBBLOCKSIZE / 16 - 7
• Here are the MAXEXTENTS for common blocksizes: 1K=57, 2K=121, 4K=249, 8K=505, and 16K=1017
• Multiple extents in and of themselves aren't bad. However, if you also have chained rows, this can hurt performance.

select OWNER,
TABLESPACE_NAME,
SEGMENT_NAME,
SEGMENT_TYPE,
BYTES,
EXTENTS,
MAX_EXTENTS,
(EXTENTS/MAX_EXTENTS)*100 percentage
from dba_segments
where SEGMENT_TYPE in ('TABLE','INDEX')
and EXTENTS > MAX_EXTENTS/2
order by (EXTENTS/MAX_EXTENTS) desc

Extents reaching maximum
TABLES AND EXTENTS WITHIN 3 EXTENTS OF MAXIMUM :
• Owner - Owner of the segment
• Segment Name - Name of the segment
select owner "Owner",
segment_name "Segment Name",
segment_type "Type",
tablespace_name "Tablespace",
extents "Ext",
max_extents "Max"
from dba_segments
where ((max_extents - extents) <= 3)
and owner not in ('SYS','SYSTEM')
order by owner, segment_name

Analyzed Tables
ANALYZED TABLE NOTES:
• Owner - Owner of the table
• Analyzed - Number of analyzed tables
• Not Analyzed - Number of tables that have not be analyzed
• Total - Total number of tables owned by user
• The ANALYZE statement allows you to validate and compute statistics for an index, table, or cluster. These statistics are used by the cost-based optimizer when it calculates the most efficient plan for retrieval. In addition to its role in statement optimization, ANALYZE also helps in validating object structures and in managing space in your system. You can choose the following operations: COMPUTER, ESTIMATE, and DELETE. Early version of Oracle7 produced unpredicatable results when the ESTIMATE operation was used. It is best to compute your statistics.
• A COMPUTE will cause a table-level lock to be placed on the table during the operation.

select OWNER,
sum(decode(nvl(NUM_ROWS,9999), 9999,0,1)) analyzed,
sum(decode(nvl(NUM_ROWS,9999), 9999,1,0)) not_analyzed,
count(TABLE_NAME) total
from dba_tables
where OWNER not in ('SYS', 'SYSTEM')
group by OWNER

Recently Analyzed Tables
LAST ANALYZED TABLE NOTES:
• Owner - Owner of the table
• Table Name - Name of the table
• Last Analyzed - Last analyzed date/time
select OWNER,
TABLE_NAME,
to_char(LAST_ANALYZED,'MM/DD/YYYY HH24:MI:SS') last_analyzed
from dba_tab_columns
where OWNER not in ('SYS','SYSTEM')
and LAST_ANALYZED is not null
and COLUMN_ID=1
and (SYSDATE-LAST_ANALYZED) < 30
order by (SYSDATE-LAST_ANALYZED)

Cached Tables
CACHED TABLE NOTES:
• Owner - Owner of the table
• Table Name - Name of the table
• Cache - Cached?
• Oracle 7.1+ provides a mechanism for caching table in the buffer cache. Caching tables will speed up data access and improve performance by finding the data in memory and avoiding disk reads.

select OWNER,
TABLE_NAME,
CACHE
from dba_tables
where OWNER not in ('SYS','SYSTEM')
and CACHE like '%Y'
order by OWNER,TABLE_NAME;

Some Importatnt Concurrent Requests & Manager Related Queries

The concurrent request was hold on by whom


SQL> select REQUEST_ID,To_char(LAST_UPDATE_DATE,'DD-MON-YYYY=>hh24:mi:ss') LAST_UPDATE_DATE,LAST_UPDATED_BY,REQUEST_DATE,REQUESTED_BY,PROGRAM_APPLICATION_ID,CONCURRENT_PROGRAM_ID,LAST_UPDATE_LOGIN from fnd_concurrent_requests where REQUEST_ID='&reqid';

SQL> select USER_ID,USER_NAME from fnd_user where LAST_UPDATED_BY=9852;

script to find out NLS patches applied or nor


SELECT driver.driver_file_name
,TO_CHAR(run.start_date,'DD-Mon HH24:MI:SS') start_date
,TO_CHAR(run.end_date ,'DD-Mon HH24:MI:SS') end_date
,lang.language
FROM ad_patch_runs run
,ad_patch_driver_langs lang
,ad_patch_drivers driver
,ad_applied_patches applied
WHERE run.patch_driver_id = driver.patch_driver_id
AND driver.applied_patch_id = applied.applied_patch_id
AND applied.patch_name = '&p_patch_number'
AND lang.patch_driver_id = driver.patch_driver_id
ORDER BY 1,2,3;

Running concurrent request

set lines 132
col os form A7 head AppProc
col spid form a6 head DBProc
col program form A43 trunc
set pages 38
col time form 9999.99 head Elapsed
col "Req Id" form 9999999
col "Parent" form a7
col "Prg Id" form 999999
col qname head "Concurrent Manager Queue" format a25 trunc
col sid format 99999 head SID
set recsep off
select q.concurrent_queue_name || ' - ' || target_node qname
,a.request_id "Req Id"
,decode(a.parent_request_id,-1,NULL,a.parent_request_id) "Parent"
,a.concurrent_program_id "Prg Id"
,a.phase_code,a.status_code
,nvl(a.os_process_id,b.os_process_id) "OS"
,vs.sid
,vp.spid
,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 "Time"
,c.concurrent_program_name||' - '||
c2.user_concurrent_program_name "program"
from APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs_tl c2
,APPLSYS.fnd_concurrent_programs c
,gv$session vs
,gv$process vp
where a.controlling_manager = b.concurrent_process_id
and a.concurrent_program_id = c.concurrent_program_id
and a.program_application_id = c.application_id
and c2.concurrent_program_id = c.concurrent_program_id
and a.phase_code in ('I','P','R','T')
and b.queue_application_id = q.application_id
and b.concurrent_queue_id = q.concurrent_queue_id
and c2.language = 'US'
and vs.process (+) = b.os_process_id
and vs.paddr = vp.addr (+)
and vs.inst_id = vp.inst_id
order by 1,2
/


concurrent request status


col os form A6
col program form A40
set pages 38
set verify off
col time head Elapsed form 9999.99
col "Req Id" form 9999999
col "Prg Id" form 999999
col "Started On" format a10
col "Finished On" format a10
col "Submitted By" format a30 trunc
col argument_text head "Arguments" format a40
col statustxt head Status format a10 trunc
col phasetxt head Phase format a10 trunc
set recsep off
accept cmreqid number prompt 'What is the concurrent request id : '
select l2.meaning phasetxt
,l1.meaning statustxt
,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 "Time"
,to_char(a.actual_start_date,'mm/dd/yy hh:mi:ssAM') "Started On"
,to_char(a.actual_completion_date,'mm/dd/yy hh:mi:ssAM') "Finished On"
,u.user_name || ' - ' || u.description "Submitted By"
,a.argument_text
from APPLSYS.fnd_Concurrent_requests a
,applsys.fnd_user u
,applsys.fnd_lookup_values l1
,applsys.fnd_lookup_values l2
where u.user_id = a.requested_by
and a.request_id = &cmreqid
and l1.lookup_type = 'CP_STATUS_CODE'
and l1.lookup_code = a.status_code
and l1.language = 'US'
and l1.enabled_flag = 'Y'
and (l1.start_date_active <= sysdate and l1.start_date_active is not null)
and (l1.end_date_active > sysdate or l1.end_date_active is null)
and l2.lookup_type = 'CP_PHASE_CODE'
and l2.lookup_code = a.phase_code
and l2.language = 'US'
and l2.enabled_flag = 'Y'
and (l2.start_date_active <= sysdate and l2.start_date_active is not null)
and (l2.end_date_active > sysdate or l2.end_date_active is null)
/


query to find the details of long running and wating requests details

select description, request_date, requested_start_date, actual_start_date ,
actual_completion_date,request_id , cprogram, argument_text ,user_name,phase_code,status_code
,to_number(substr(run_time,9,2))*24+to_number(substr(run_time,12,2))||'.'||(decode(length(substr(run_time,15,2)),1,'0'||substr(run_time,15,2),substr(run_time,15,2))) RUN_HHMI_NEW
,to_number(substr(wait_time,9,2))*24+to_number(substr(wait_time,12,2))||'.'||(decode(length(substr(wait_time,15,2)),1,'0'||substr(wait_time,15,2),substr(wait_time,15,2))) WAIT_HHMI_NEW
from (
select
p.user_concurrent_program_name description, r.request_date, r.requested_start_date, r.actual_start_date ,
r.actual_completion_date,r.request_id,
p.concurrent_program_name cprogram,r.argument_text ,r.cancel_or_hold, fu.user_name,lup1.meaning phase_code,lup2.meaning status_code,
rtrim((cast(r.actual_completion_date as timestamp) - cast (r.actual_start_date as timestamp)),'000000') run_time,
rtrim((cast(r.actual_start_date as timestamp) - cast (r.request_date as timestamp)),'000000') wait_time
from apps.fnd_concurrent_queues fcq,
apps.fnd_concurrent_queue_content fcqc,
apps.fnd_concurrent_request_class c,
apps.fnd_application f,
apps.fnd_concurrent_programs_vl p,
apps.fnd_concurrent_requests r,
apps.fnd_user fu,
apps.fnd_lookups lup1,
apps.fnd_lookups lup2
where r.program_application_id = p.application_id
and r.concurrent_program_id = p.concurrent_program_id
and r.phase_code <> 'P'
and r.requested_by = fu.user_id
and trunc(request_date) between :P_FDATE and :P_TDATE
and p.application_id = f.application_id
and r.program_application_id = f.application_id
and r.request_class_application_id = c.application_id(+)
and r.concurrent_request_class_id = c.request_class_id(+)
and r.request_class_application_id = fcqc.type_application_id(+)
and r.concurrent_request_class_id = fcqc.type_id(+)
and fcqc.queue_application_id = fcq.application_id(+)
and fcqc.concurrent_queue_id = fcq.concurrent_queue_id(+)
and lup1.lookup_code = r.phase_code
and lup1.lookup_type = 'CP_PHASE_CODE'
and lup2.lookup_code = r.status_code
and lup2.lookup_type = 'CP_STATUS_CODE'
group by
c.request_class_name,
f.application_short_name,
p.concurrent_program_name,lup1.meaning ,lup2.meaning ,
p.user_concurrent_program_name,r.request_date,r.requested_start_date,
r.priority,r.actual_start_date ,r.request_id,r.actual_completion_date,fu.user_name,
r.argument_text,r.cancel_or_hold
order by 6 )

most executed concurrent requests day wise with node details

select request_id,logfile_node_name, p.user_concurrent_program_name "CONCURRENT PROGRAM NAME",
p.concurrent_program_name "PROGRAM",request_date
from apps.fnd_concurrent_queues fcq,
apps.fnd_concurrent_queue_content fcqc,
apps.fnd_concurrent_request_class c,
apps.fnd_application f,
apps.fnd_concurrent_programs_vl p,
apps.fnd_concurrent_requests r
where r.program_application_id = p.application_id
and r.concurrent_program_id = p.concurrent_program_id
--and r.status_code in ('C','G','E')
and trunc(request_date) between :P_FDATE and :P_TDATE
and p.application_id = f.application_id
and r.program_application_id = f.application_id
and r.request_class_application_id = c.application_id(+)
and r.concurrent_request_class_id = c.request_class_id(+)
and r.request_class_application_id = fcqc.type_application_id(+)
and r.concurrent_request_class_id = fcqc.type_id(+)
and fcqc.queue_application_id = fcq.application_id(+)
and fcqc.concurrent_queue_id = fcq.concurrent_queue_id(+)
--and p.user_concurrent_program_name like '%Purge%Manager%'
group by
request_id,c.request_class_name,
f.application_short_name,
p.concurrent_program_name,
p.user_concurrent_program_name,
r.priority, request_date,logfile_node_name
order by 3

most executed concurrent requests

select
p.user_concurrent_program_name "description",
p.concurrent_program_name "program",
count(*) "NUMS"
from apps.fnd_concurrent_queues fcq,
apps.fnd_concurrent_queue_content fcqc,
apps.fnd_concurrent_request_class c,
apps.fnd_application f,
apps.fnd_concurrent_programs_vl p,
apps.fnd_concurrent_requests r
where r.program_application_id = p.application_id
and r.concurrent_program_id = p.concurrent_program_id
--and r.status_code in ('C','G','E')
and trunc(request_date) between :P_FDATE and :P_TDATE
and p.application_id = f.application_id
and r.program_application_id = f.application_id
and r.request_class_application_id = c.application_id(+)
and r.concurrent_request_class_id = c.request_class_id(+)
and r.request_class_application_id = fcqc.type_application_id(+)
and r.concurrent_request_class_id = fcqc.type_id(+)
and fcqc.queue_application_id = fcq.application_id(+)
and fcqc.concurrent_queue_id = fcq.concurrent_queue_id(+)
group by
c.request_class_name,
f.application_short_name,
p.concurrent_program_name,
p.user_concurrent_program_name,
r.priority
order by count(*) desc



most executed concurrent requests day wise

select p.user_concurrent_program_name "CONCURRENT PROGRAM NAME",
p.concurrent_program_name "PROGRAM",to_char(request_date,'DD-MON-YYYY') "DATE",
count(*) "NUMS"
from apps.fnd_concurrent_queues fcq,
apps.fnd_concurrent_queue_content fcqc,
apps.fnd_concurrent_request_class c,
apps.fnd_application f,
apps.fnd_concurrent_programs_vl p,
apps.fnd_concurrent_requests r
where r.program_application_id = p.application_id
and r.concurrent_program_id = p.concurrent_program_id
--and r.status_code in ('C','G','E')
and trunc(request_date) between :P_FDATE and :P_TDATE
and p.application_id = f.application_id
and r.program_application_id = f.application_id
and r.request_class_application_id = c.application_id(+)
and r.concurrent_request_class_id = c.request_class_id(+)
and r.request_class_application_id = fcqc.type_application_id(+)
and r.concurrent_request_class_id = fcqc.type_id(+)
and fcqc.queue_application_id = fcq.application_id(+)
and fcqc.concurrent_queue_id = fcq.concurrent_queue_id(+)
group by
c.request_class_name,
f.application_short_name,
p.concurrent_program_name,
p.user_concurrent_program_name,
r.priority, to_char(request_date,'DD-MON-YYYY')
order by 3, count(*) desc


number_of requests submitted per day

select to_char(request_date,'DD-MON-YYYY') "DATE", count(request_date) "NUM_REQS"
from fnd_concurrent_requests
where trunc(request_date) between :P_FDATE and :P_TDATE
group by to_char(request_date,'DD-MON-YYYY') order by 1


query to find the number of requests submitted previous day


select to_char(sysdate-1,'DD-MON-YYYY') "DATE", count(request_date) "NUM_REQS"
from fnd_concurrent_requests
where trunc(request_date)=trunc(sysdate-1);