Home » RDBMS Server » Performance Tuning » Tuning for select query (oracle 10g,win xp)
Tuning for select query [message #605804] Tue, 14 January 2014 23:56 Go to next message
Xandot
Messages: 102
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

[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 Go to previous message
cookiemonster
Messages: 10899
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.
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:
  


Current Time: Wed Aug 20 06:09:11 CDT 2014

Total time taken to generate the page: 0.06723 seconds