Performance Issue and Solution

Dear DBA-Mates,

Recently, we received an email from users that they are facing performance issues.

They are not able to run SELECT query on a particulat schema's table.

When they run the Query as shown below:

select * from abc where rownum=5;

This query got hanged, neither error nor any output. Users waited for long time but neither output nor error. Even we also tried but same issue.

Note: Actually, I suggested them for GSS in starting only but they want some investigation before GSS that. How to run GSS for table in Oracle we can see below.

Then we Checked the below General Points and thought may helpful but didn’t work…

1. Is there any high CPU or Memory usage by using "top" command?

2. Is there any Blocking session?

3. Is there any Lock session?

4. Checked alert log

After checking all above steps we didn't get any things, everything was fine. There was no High CPU or memory usage, no lock and no blocking sessions.

Then we decided to investigate more... We checked alert logs we found some archive was failed but that was not related to it.

Then we checked the Tablespace and mount point file system spaces. It was also fine.

Now we checked the last analyzed date/time for that schema's table. Then we were surprised.

Then we suggested them that we need to run GSS for that particular Schema's table.

We ran the GSS and issues got resolved.

Before:

SQL>select substr(OWNER,1,8) OWNER

, TABLE_NAME

, to_char(LAST_ANALYZED,'DD-MON-RR HH24:MI:SS') LAST_ANALYZED

from dba_tables

where OWNER = 'ABC' and table_name='XYZ';  2    3    4    5

OWNER                            TABLE_NAME

-------------------------------- ------------------------------

LAST_ANALYZED

---------------------------

ABC                           XYZ

SQL> exec dbms_stats.gather_table_stats('ABC','XYZ',estimate_percent => dbms_stats.auto_sample_size);

PL/SQL procedure successfully completed.

SQL>

After:

SQL> set pages 200

SQL> set lines 200

SQL> /

OWNER                            TABLE_NAME                     LAST_ANALYZED

-------------------------------- ------------------------------ ---------------------------

ABC                                            XYZ               19-NOV-17 18:20:58

Some more useful links:

Why we enable Maintenance Mode in R12.1.3

RMAN Issues and Solutions - (ORA-00020) and (ORA-00245)

Concurrent Managers are down error insert_fcp failed due toORA-01403 no data found, ORA-06512

R12.2 Cloning Steps

Regards,