Queries Running Very Slow

Query Running very slow:

This is the one which we often get, and the immediate answer would be, just go and take plan of the query and create some indexes if necessary. Lot of times this will not help you. There are so many possible reasons that we need to look.
If the query is running very slow collect some basic information before doing anything, this will be the first step to resolve this issue.

Information needs to collect when query is running slow

1. First execute the query and check how long it took? Is this query running very slow since it developed or it happened today? I mean yesterday or sometime before it was running fine but today it is very slow. The first thing you have to do is check the network traffic/Network errors for something when wrong in the network side, the best way to check is access some share drive which is located somewhere in network or use tnsping, traceroute, try pinging or ask network admin to check the packet size. If you notice any slow access then contacts network support. Sometimes this has been a problem. Which is out of scope of oracle database but created problem to the database, recently I faced the same kind of issue.

2. Network Issue:
Network is good no problems noticed then next step needs to check for any massive data loads done during last past night, are there any code changes, when was the last time the tables got analyzed, check the tables in the queries for proper indexes creation, check for tables, indexes and other objects for recent statistics, avoid using database functions, avoid using full table scan as much as possible, create partitions if necessary for faster access , use merge statements for huge updates and deletes, and so on. Check whether database statistics ran after database load. Simple way to run statistics is using DBMS_STAT.GATHER_TABLE_STATS

3. Statistics are fine. Still the query is slow. Next step needs to check is whether any database upgrades are done recently or any patches applied to higher version. Same queries with same data will run very slow in newer versions when upgrade from lower version. Then you might run with database CBO Bug. Contact Oracle support for more details regarding this bug.

4. I/O Issue:
Sometimes queries will be very slow due to more I/O from database to client.
Mechanism to avoid disk I/O and make the physical reads and writes more efficient
First get the largest amount of physical reads and logical reads and the simple way to get them are:

For Physical Reads:
Select disk_reads, sql_text from v$sqlarea where disk_reads > 10000 order by disk_reads desc;
For Logical Reads:
Select Buffer_gets, sql_text from v$sqlarea where buffer_gets > 200000 order by
Buffer_gets desc;
Multiple approaches must be made to cover all types of queries

5. Memory Issue:
Pin (Cache) most used PL/SQL statement into memory
Syntax: dbms_shared_Pool.keep(Object_name)
Few queries which would help you to resolve the memory related issues are:
Select name, value from gv$parameter where name in (‘db_block_buffers’, ‘db_block_size’, ‘shared_pool_size’, ‘sort_area_size’);
Check the following:
select 1-(sum(decode(name, 'physical reads', value,0))/ (sum(decode(name, 'db block gets', value,0)) + (sum(decode(name, 'consistent gets', value,0))))) * 100 "Read Hit Ratio" from gv$sysstat;

Read Hit Ratio
98.99
Hit ratio below 90-95% is signed as poor indexing;
Check the Shared Pool Size:
It is important to look at the shared_pool_size for proper sizing. With a greater amount of procedures, packages and triggers being utilized with Oracle, the shared_pool_size makes up a much greater portion of the Oracle SGA. This is the memory allocated for the library and data dictionary cache. If the shared_pool_size is set too low then you will not get the full advantage of your DB_BLOCK_BUFFERS.

How to find the data dictionary cache miss ratio?
Select sum(gets) “Gets”, sum(getmisses) “Misses”, (1-(sum(getmisses)/(sum(gets)+ sum(getmisses)))) * 100 “HitRate”
From gv$rowcache;

Gets          Misses           HitRate
11000           558            96.5

This is a good ratio does not require any further action.
How to find library Cache Hit Ratio?
select sum(pins) Executions, sum(pinhits) "Execution Hits", ((sum(pinhits)/sum(pins)) * 100)phitrat, sum(reloads)Misses, ((Sum(pins)/(sum(pins) + sum(reloads))) * 100) hitrat from gv$librarycache;

EXECUTIONS Execution Hits PHITRAT MISSES HITRAT
---------- -------------- ---------- ---------- ----------
1126456014 1121589121 99.5679465 102177 99.9909302

How much memory is left for shared_pool_size?
select to_number(gv$parameter.value) value, gv$sgastat.bytes, (gv$sgastat.bytes/gv$parameter.value)*100 “Percent Free
from gv$sgastat, gv$parameter where gv$sgastat.name = 'free memory and gv$ parameter .name = ‘shared_pool_size;

Shared Pool Size Free Bytes Percent Free

100,000,000 82,278,960 82.278


Regards,
Sowmya (OSR)

ORA-0600: Internal Code Error

 ORA-0600: Internal Code Error


Explanation: Any ORA-600 error indicates Oracle has detected an internal inconsistency or a problem which it doesn’t know how best to address. These are not necessarily bugs and can occur for reasons such as Operating System, IO Problems, and Running out of some resources and so on.

Cause: This generic internal error number for oracle program exceptions. This indicates that a process has encountered an exceptional condition.

Note: This type of errors is raised from kernel code of the Oracle RDBMS software when there is an inconsistency in detected or unexpected condition is noticed. This situation need not be considered as bug all the time; it might be caused by problems with operating system, lack of resources etc.

With the ORA-600 error comes with a list of arguments in square brackets. The first of these arguments tells us from where in the code the error was caught and this is the key information for identifying the problem. This can be either numbers or arguments.

Whenever there is an ORA-600 error is raised a trace file is generated in either Udump or Bdump depending on whether the error was caught in a user or a background process. The information is also present in the alert log.

Remedy: Contact Oracle Support.

• Report as bug if the first argument is the internal error number

The most useful items are:

• Check the alert log for the instance or any other instances in a parallel server environment. Alert log is present in Background_dump_dest

• Check the trace file for the failing session and other related trace files

• Check whether anything got changed recently in the system environment such OS been upgraded, patches, database files got restored etc.

• Make sure whether the problem is reproducible or one time occurrence

The following is partial list of the ORA-00600 known bugs and issues. You can find complete list and details about these bugs at Oracle Metalink.

• Partial list of ORA-00600 known bugs and issues:

1. An Oracle ORA-00600 [729]: indicates a memory leak in the UGA. Setting diagnostic events 10262 ignores this error, that is, no ORA-00600 is produced even if a memory leak is detected.

2. ORA-00600 [16365]: indicates a Half Duplex Violation. Shared servers and dispatchers cannot both be writing to the same virtual circuit at the same time because they communicate with a half-duplex protocol (TTC protocol).

3. ORA-00600 [kghpih:ds] : and ORA-00600 [17175] may occur in oracle 9.2.0.6 when two processes work on the same library cache object. Fixed in oracle 9.2.0.7... Note: 300604.1

4. An Oracle ORA-00600 [3020]: Bug.2322620 - RECOVERY HALTED ON PHYSICAL STANDBY WITH ORA-600 AND ORA-10564 Details: ORA-600 [3020] can occur on recovery of LOB data. Fixed in 8.1.7.5 Patch available. This error occurred on our database recovery on the primary server.

5. ora-00600 [12333]: Most probably a network error that may have caused bad data to be received or the client application may have sent the wrong data in the network buffer.

6. Oracle ORA-00600 [12700]: Indicates corruption in index, table or the mapping between them. Upon this discovery, oracle marks the whole block as corrupt and users will not have normal access to the data from the corrupted block. This can also break referential integrity and should be referred to oracle.

7. ORA-00600 [lxhcsn]: Shutdown may dump ORA-7445. Known Bug 2311529

Conclusion: This must be taken as a serious issue and contact Oracle support or raise a SR for unknown issues

Regards,
Sowmya (OSR)

Common Oracle Errors