OraFAQ Forum: Performance Tuning » performance problem

Home » RDBMS Server » Performance Tuning » performance problem (Oracle 9i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

performance problem [message #534533]

Wed, 07 December 2011 01:53

ajaykumarkona
Messages: 399
Registered: August 2010

Senior Member

select /*+ ordered */
emp.empno,emp.ename,dept.deptno
FROM dept ,emp
WHERE emp.sal>1000
AND emp.deptno=dept.deptno;

12 rows selected.

The above query returning 12 rows.
If I remove the condition emp.sal>1000 it's returning 14 rows.

In the execution plan TABLE ACCESS FULL| EMP | 14 ,it should show 12 as per the query.
Could you please help me to how to get it.
And please help me how to reduce the cost of this query
without creating any indexes only by rewriting the query.
.
Please find the below execution plan.

Execution Plan
----------------------------------------------------------
Plan hash value: 1093152308

-----------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost  |
-----------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |   420 |     5 |
|*  1 |  HASH JOIN         |      |    14 |   420 |     5 |
|   2 |   TABLE ACCESS FULL| DEPT |     4 |    52 |     2 |
|*  3 |   TABLE ACCESS FULL| EMP  |    14 |   238 |     2 |
-----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
   3 - filter("EMP"."SAL">1000)

Note
-----
   - cpu costing is off (consider enabling it)
   - dynamic sampling used for this statement

Thanks in advance

Report message to a moderator

Re: performance problem [message #534534 is a reply to message #534533]

Wed, 07 December 2011 02:25

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

Cost, whilst not really a good metric, is 5....doesn't get much lower.

The row counts are estimated by the way.

Report message to a moderator

Re: performance problem [message #534535 is a reply to message #534533]

Wed, 07 December 2011 02:38

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

In the execution plan TABLE ACCESS FULL| EMP | 14 ,it should show 12 as per the query

No, it is an estimation not an acual number.

Quote:

dynamic sampling used for this statement

Oracle has no information about the values in your column unless you gather histogram statistics on it.
Try it.

Regards
Michel

[Updated on: Wed, 07 December 2011 02:40]

Report message to a moderator

Re: performance problem [message #534536 is a reply to message #534533]

Wed, 07 December 2011 02:39

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

And please help me how to reduce the cost of this query
without creating any indexes only by rewriting the query.

No way.
It is the simplest query so anything you will do to rewrite it will lead the optimizer to internally rewrite it back.

Regards
Michel

Report message to a moderator

Previous Topic:	How to improve the performance of export job(expdp)?
Next Topic:	create index

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Nov 24 12:57:12 CST 2024