Hi Tom,
I had 1 sql file which was running perfectly fine in 10G but after migration to 12 c performance is very poor.
The query logic is :
A table has around 550 columns and data is getting inserted into this table from XL Sheet. Once loaded then we need to check :
first, which column has value (ignoring 10-15 columns which we will be used for reference).
And if value is present then identify those columns which can be used later.

For Example, below query was working good in 10G but running very poorly in 12 C.

CURSOR C_MASTER_CLAIM IS
SELECT a.OWNER || '.' || A.TABLE_NAME, a.Column_Name
FROM all_tab_cols a
WHERE A.owner = v_owner
AND a.table_name IN (v_master_clm_tbl)
AND a.hidden_column = 'NO'
AND user_generated = 'YES'
AND A.column_name NOT IN
(REFERENCE COLUMN);
BEGIN

v_master_clm := v_owner || '.' || v_master_clm_tbl;

OPEN C_MASTER_CLAIM;
LOOP
FETCH C_MASTER_CLAIM BULK COLLECT
INTO V_MASTER_CLAIM LIMIT 1000;
FOR i IN 1 .. V_MASTER_CLAIM.count LOOP


V_EXISTS_RECORD_CLAIM := 0;

EXECUTE IMMEDIATE 'SELECT COUNT(1) FROM '|| v_master_clm ||'
WHERE NVL(submitted, ''NO'') = ''NO''
AND NVL(action, ''X'') NOT IN (''ADJ'', ''M'')
AND state = ' || '''' ||v_state || '''' || '
AND type_of_claim = ' || '''' || v_type || '''' || '
AND TTID = ' || '''' || v_tt || '''' || '
AND '|| V_MASTER_CLAIM(J).COLUMN_NAME||' IS NOT NULL '
INTO V_EXISTS_RECORD_CLAIM;

IF V_EXISTS_RECORD_CLAIM > 0 THEN

--To mark data which have VALUES in COLUMNS and hence fetching value from different table to update in main table.

V_SQL1:= 'UPDATE '||v_master_clm||' m
SET UPDATE_LEVEL = UPDATE_LEVEL||'',''||(SELECT COLUMN_NO FROM ' || v_special_update_tbl || ' t
WHERE COLUMN_NAME = ' || '''' ||V_MASTER_CLAIM(i).COLUMN_NAME ||'''' ||'
AND t.COLUMN_IN IN (''HEADER'', ''DETAIL'')
AND t.TABLE_CATEGORY = '||''''||V_TABLE_CATEGORY ||''''||'
AND ROWNUM=1)
WHERE m.TTID = :p_tt
AND '||V_MASTER_CLAIM(i).COLUMN_NAME||' IS NOT NULL
AND (DATA_LEVEL IS NULL OR NVL(DATA_LEVEL,''EMPTY'') LIKE ''%IcnNull%'')
AND m.state = :p_state
AND m.type_of_claim = :p_type
';
EXECUTE IMMEDIATE V_SQL1
USING v_tt, v_state, v_type; -- These value will be passed as parameter

END IF;

END LOOP;
EXIT WHEN C_MASTER_CLAIM%notfound;

END LOOP;
IF C_MASTER_CLAIM%isopen THEN
CLOSE C_MASTER_CLAIM;
END IF;

END;


Please let me know your suggestions to improve performance or other way of writing logic.