Query Performing Very Bad In Oracle

[BadBoy23]

Hi Guys,

Any idea what all things to be considered or checked when a query starts executing for very long time in Oracle.

[cherlapalli_jailer]

trim functions vunte teeseyyi
if u have functions on columns then it will be slow

if u don't have indexes then it will be dead dead slow

so try to add indexes

[krisomania2043]

[quote name='cherlapalli_jailer' timestamp='1374845790' post='1304012883']
trim functions vunte teeseyyi
if u have functions on columns then it will be slow

if u don't have indexes then it will be dead dead slow

so try to add [color=#ff0000]indexes[/color]
[/quote]
+1

[cherlapalli_jailer]

also check inner select stmts
if they can be avoided then fine tune ur Query

if possible past ur query

[BillMarkSteve]

Indexes are concern while selecting and deleting.
For inserting records into tables indexes are not an issue.

Check for any loops and their cursors.
Replace cursors with temp tables if possible.
Drop the temp tables if any created at the end.

Check for parts by parts of code bu executing in pieces and record the time for max time taking code and optimize that first.

'Where' clause inside a query takes time too...

[worstandhra]

[quote name='BadBoy23' timestamp='1374845603' post='1304012875']
Hi Guys,

Any idea what all things to be considered or checked when a query starts executing for very long time in Oracle.
[/quote]

1. Decide the most frequently used columns in 'where' clause for that table in any of the SQLs performedo on that table.
2. Create index for that column list.
3. DO NOT create more indexes unnecessarily as they would end up with lot of duplication and slow down of table access.

Some other things like:
1. Optimize the query. Look for proper join operations. Remove unnecessary joins
2. Do not go for too much of normalization, it would be a over kill.

[Headmaster]

Nuvvu Toad vaduthunte.. okkasari aa query meeda explain plan kottu.. will give you the complete details on how the query is getting executed..

[MeetFriendz]

Check stats on the objects which query is using first, then have the explain of the query and see where exaclty the cost is more and what access paths its using based on accesspaths you can analyze which part of the code might be impacting like functions on columns etc and try to change them with out chaging the functionality of the code

[ronitreddy]

gp

[simham1]

gp

[Uppi]

SQL tuning commonly involves finding more efficient ways to process the same
workload. It is possible to change the execution plan of the statement without altering
the functionality to reduce the resource consumption.
Two examples of how resource usage can be reduced are:

1. If a commonly executed query needs to access a small percentage of data in the
table, then it can be executed more efficiently by using an index. By creating such
an index, you reduce the amount of resources used.

2. If a user is looking at the first twenty rows of the 10,000 rows returned in a specific
sort order, and if the query (and sort order) can be satisfied by an index, then the
user does not need to access and sort the 10,000 rows to see the first 20 rows.


Identifying High-Load SQL

This section describes the steps involved in identifying and gathering data on
high-load SQL statements. High-load SQL are poorly-performing, resource-intensive
SQL statements that impact the performance of the Oracle database. High-load SQL
statements can be identified by:

¦ V$SQL view
¦ SQL Trace


If it is not possible to identify the SQL (for example, the SQL is generated
dynamically), then use SQL_TRACE to generate a trace file that contains the SQL
executed, then use TKPROF to generate an output file.
The SQL statements in the TKPROF output file can be ordered by various parameters,
such as the execution elapsed time (exeela), which usually assists in the
identification by ordering the SQL statements by elapsed time (with highest elapsed
time SQL statements at the top of the file). This makes the job of identifying the poorly
performing SQL easier if there are many SQL statements in the file.




Tuning WAys:

Often, rewriting an inefficient SQL statement is easier than modifying it. If you
understand the purpose of a given statement, then you might be able to quickly and
easily write a new statement that meets the requirement.

Compose Predicates Using AND and =

To improve SQL efficiency, use equijoins whenever possible. Statements that perform
equijoins on untransformed column values are the easiest to tune.


Avoid Transformed Columns in the WHERE Clause
Use untransformed column values. For example, use:
WHERE a.order_no = b.order_no
rather than:
WHERE TO_NUMBER (SUBSTR(a.order_no, INSTR(b.order_no, '.') - 1))
= TO_NUMBER (SUBSTR(a.order_no, INSTR(b.order_no, '.') - 1))

Do not use SQL functions in predicate clauses or WHERE clauses. Any expression using
a column, such as a function having the column as its argument, causes the optimizer
to ignore the possibility of using an index on that column, even a unique index, unless
there is a function-based index defined that can be used.

Avoid mixed-mode expressions, and beware of implicit type conversions. When you
want to use an index on the VARCHAR2 column charcol, but the WHERE clause looks
like this:
AND charcol = numexpr
where numexpr is an expression of number type (for example, 1,
USERENV('SESSIONID'), numcol, numcol+0,...), Oracle translates that expression into:
AND TO_NUMBER(charcol) = numexpr


Avoid the following kinds of complex expressions:
¦ col1 = NVL (:b1,col1)
¦ NVL (col1,-999) = ….
¦ TO_DATE(), TO_NUMBER(), and so on
These expressions prevent the optimizer from assigning valid cardinality or selectivity
estimates and can in turn affect the overall plan and the join method.
Add the predicate versus using NVL() technique.
For example:
SELECT employee_num, full_name Name, employee_id
Developing Efficient SQL Statements
11-8 Oracle Database Performance Tuning Guide
FROM mtl_employees_current_view
WHERE (employee_num = NVL (:b1,employee_num)) AND (organization_id=:1)
ORDER BY employee_num;


Also:
SELECT employee_num, full_name Name, employee_id
FROM mtl_employees_current_view
WHERE (employee_num = :b1) AND (organization_id=:1)
ORDER BY employee_num;
When you need to use SQL functions on filters or join predicates, do not use them on
the columns on which you want to have an index; rather, use them on the opposite
side of the predicate, as in the following statement:
TO_CHAR(numcol) = varcol
rather than
varcol = TO_CHAR(numcol)




*)))


Use of EXISTS versus IN for Subqueries
In certain circumstances, it is better to use IN rather than EXISTS. In general, if the
selective predicate is in the subquery, then use IN. If the selective predicate is in the
parent query, then use EXISTS.

[CheGuevara]

Gidantha kadu gani....

First aa query execute chesi real time ADDM report run chey.... Adi ichina report lo problem with the query abd recommendations cheptadi..... Aa recommendations follow avvu.... Simple ga resolve aitadi issue...sodi antha check cheyyalsina avasaram ledu

Aa report mostly tuning advisor run cheyyamantafi adi run chey sql meeda, adi inka konni recommendations istai, avi kuda follow avvu... Picha picha ga tune aipotadi query

[juvenile]

See the execution plan !!


Ekkada ekkuva time teeskuntundo .. ee part daggara cost ekkuva undo chudu ..

Query lo unna where clause lo columns meeda Index unda leda anedi chudu ..

If you have Oracle Enterprise Manager -- Run SQL Tuning Adivisor ..

It tells you what to do and how much gain you will get ..

Check CBO stats are up to date ..

[url="http://www.oracle-base.com/articles/10g/automatic-sql-tuning-10g.php"]http://www.oracle-base.com/articles/10g/automatic-sql-tuning-10g.php[/url]