OraFAQ Forum: Performance Tuning » Tuning for select query

Home » RDBMS Server » Performance Tuning » Tuning for select query (oracle 10g,win xp)

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

Tuning for select query [message #605804]

Tue, 14 January 2014 23:56

Xandot
Messages: 235
Registered: January 2014
Location: India

Senior Member

Hi,

I want to select records from my view (this view contains more than 1 million records).
The problem is when i select records form view so it took in 15.945 secs
The view contains the three union all statements and group by clause.
Please suggest me how to reduce the execution time of the particular select query.
My Query:----
select A.item_code,
IM.SHORTNAME,
IM.ITEM_NAME,
A.input_item_code,
SUBSTR(lhs_utility.GET_NAME('item_code', A.INPUT_ITEM_CODE), 1, 200) input_item_name,
(select i.um from item_mast i where i.item_code=A.input_item_code) input_um,
IM.um,
avg(a.BALANCE_TO_JOB) BALANCE_TO_JOB
FROM view_pord_job_balance A, ITEM_MAST IM
WHERE A.ENTITY_CODE = 'AS'
AND A.DIV_CODE = NVL('U1', A.DIV_CODE)
AND A.ITEM_CODE = IM.ITEM_CODE
AND nvl(A.BALANCE_TO_JOB, 0) > 0
group by a.item_code, IM.ITEM_NAME, a.input_item_code, im.um, IM.SHORTNAME
ORDER BY ITEM_CODE;

Explain plan is attached in image..

Thanks

Attachment: expain plan.GIF
(Size: 16.77KB, Downloaded 1767 times)

[Updated on: Tue, 14 January 2014 23:58]

Report message to a moderator

Re: Tuning for select query [message #605827 is a reply to message #605804]

Wed, 15 January 2014 03:12

cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester

Senior Member

Please read and follow How to use [code] tags and make your code easier to read?

Explain plans as images aren't particularly helpful, some people won't/can't download them.
Explain plans without objects are basically useless since we have no idea which operation is against which table.
Also you haven't expanded all the nodes in the explain, making it even more useless.

Use the following method to generate the explain plan and post it here in code tags:

SQL> explain plan for select * from dual;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3543395131

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

8 rows selected.

SQL>

We'll also need the view text.

Report message to a moderator

Previous Topic:	something similar to mysqlslap (Load Emulation Client) for Oracle DB?
Next Topic:	DB in snail's pace or bad SQL query or bad DB design?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 23 20:19:53 CST 2024