| Cost of Query is brought down, but taking same time.(merged) [message #473951] |
Wed, 01 September 2010 11:00  |
oraranjangmailcom
Messages: 67
Registered: April 2010
|
Member |
|
|
Hi,
For an query, cost was 16Lakhs and was taking 30min,
I brought down the cost to 1.5lakhs, but still it is taking 30min.
Can I have some comments or sugessions on above point.
There were many outer joins and same table has been Used(FROM clause) 5 times in the query.
I have introduced WITH clause, and brougt down the cost.
Thanks,
Ranjan
|
|
|
|
|
|
|
|
| Re: Cost of Query is brought down, but taking same time. [message #474005 is a reply to message #473957] |
Thu, 02 September 2010 00:17  |
oraranjangmailcom
Messages: 67
Registered: April 2010
|
Member |
|
|
How do you execute the query?
> set timing on;
> set autotrace traceonly;
> <run the sql query> ex: select ......from ....;
Elapsed Time: ...
Execution Plan: ...
Statistic: ...
------
What is your client?
Telecom Domain.
--------------
How many rows it returns?
90000 records
--------------------
How do you get them?
--------------------------
Are you sure the query time is spent executing the query and not getting the result through the network?
I am not sure, I am following below method to get the time.
> set timing on;
> set autotrace traceonly;
> <run the sql query> ex: select ......from ....;
If better approach of getting execution time of query, please let me know.
Thanks,
Ranjan
[Updated on: Thu, 02 September 2010 00:18] Report message to a moderator |
|
|
|
| How to get the time taken by the query. [message #474007 is a reply to message #473951] |
Thu, 02 September 2010 00:44 |
oraranjangmailcom
Messages: 67
Registered: April 2010
|
Member |
|
|
Hi,
May I know, how to get the time taken by the query.
>SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
Plan hash value: 1405256389
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 40434 (1)| 00:08:06 |
| 1 | SORT AGGREGATE | | 1 | 19 | | |
|* 2 | HASH JOIN RIGHT OUTER| | 2772K| 50M| 40434 (1)| 00:08:06 |
| 3 | TABLE ACCESS FULL | SALES_VIEW_TARGET | 1 | 13 | 2 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | LEGAL_VIEW_TARGET | 2772K| 15M| 40404 (1)| 00:08:05 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SLS"."BILLING_ACCOUNT_ID"(+)="LEGAL"."BILLING_ACCOUNT_ID")
16 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=24 Card=8168 Bytes
=16336)
1 0 COLLECTION ITERATOR (PICKLER FETCH) OF 'DISPLAY' (PROCEDUR
E)
Statistics
----------------------------------------------------------
16 recursive calls
12 db block gets
69 consistent gets
0 physical reads
0 redo size
1590 bytes sent via SQL*Net to client
323 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
16 rows processed
It will be great, if the time taken by the above analysis is mentioned.
Thanks,
Ranjan
|
|
|
|
| Re: Cost of Query is brought down, but taking same time. [message #474010 is a reply to message #474005] |
Thu, 02 September 2010 00:56 |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Quote:How do you execute the query?
I read your post, what is your array size in both case? what are roundtrip numbers?
Quote:What is your client?
I meant ORACLE client? I don't care of your customer. Here, SQL*Plus version? Is 9.2.0.8 your database or your client version or both?
Quote:How many rows it returns?
90000 records
Quote:Are you sure the query time is spent executing the query and not getting the result through the network?
I am not sure, I am following below method to get the time.
So if you use default array size (15) you have 90000/15=6000 roundtrips + overhead to handle it at server side, this part depends on the execution plab. Use array size 100, then you will have 900 fetches instead.
Quote:If better approach of getting execution time of query, please let me know.
alter session set sql_trace=true;
<query>
alter session set sql_trace=false;
and get the trace file and use tkprof to analyze it.
Regards
Michel
|
|
|
|
|
|
| Re: Cost of Query is brought down, but taking same time.(merged) [message #474135 is a reply to message #473951] |
Fri, 03 September 2010 01:29 |
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
|
Senior Member |
|
|
Quote:
There were many outer joins and same table has been Used(FROM clause) 5 times in the query.
I have introduced WITH clause, and brougt down the cost.
Is your statistics upto date?
Is it possible for you to post the query?
can you think of rewriting the query?
is it the plan that you are providing for your sql?
Quote:
How many rows it returns?
90000 records
0 | SELECT STATEMENT | | 1 | 19 | 40434 (1)| 00:08:06 |
...
Regards
Ved
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
