Home » SQL & PL/SQL » SQL & PL/SQL » can we tune this query without creating any objects like indexes etc. (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi)
can we tune this query without creating any objects like indexes etc. [message #574612] Mon, 14 January 2013 03:31 Go to next message
msol25
Messages: 234
Registered: June 2011
Location: Mumbai
Senior Member
Dear Experts,

Can we tune below mentioned query without creating any indexes :


SELECT /*+ PARALLEL(a,64) */x_abc_41, x_abc_44, CALLED_FROM_NUM, X_abc_25, created, evt_stat_cd, last_date, x_abc_number_from,
       x_abc_complete_date
  FROM table a
 WHERE a.todo_cd in('MNPIN','MNPOUT') 
 AND   a.x_abc_25 = 'NP RFS'
 AND   a.created BETWEEN sysdate-180 AND sysdate



Explain Plan :


PLAN_TABLE_OUTPUT
Plan hash value: 3718629559
 
----------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name      | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |    11 |  2376 |  9462   (4)| 00:02:51 |        |      |            |
|*  1 |  PX COORDINATOR       |           |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM) | :TQ10000  |    11 |  2376 |  9462   (4)| 00:02:51 |  Q1,00 | P->S | QC (RAND)  |
|*  3 |    FILTER             |           |       |       |            |          |  Q1,00 | PCWC |            |
|   4 |     PX BLOCK ITERATOR |           |    11 |  2376 |  9462   (4)| 00:02:51 |  Q1,00 | PCWC |            |
|*  5 |      TABLE ACCESS FULL| TABLE     |    11 |  2376 |  9462   (4)| 00:02:51 |  Q1,00 | PCWP |            |
----------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(SYSDATE@!-180<=SYSDATE@!)
   3 - filter(SYSDATE@!-180<=SYSDATE@!)
   5 - filter(("A"."TODO_CD"='MNPIN' OR "A"."TODO_CD"='MNPOUT') AND "A"."X_ABC_25"='NP RFS' 
              AND "A"."CREATED"<=SYSDATE@! AND "A"."CREATED">=SYSDATE@!-180)



Note : Currently this query is taking 3 Minutes for retrieving 8000 records,But should be executed in less than 1 min.

[Updated on: Mon, 14 January 2013 03:33]

Report message to a moderator

Re: can we tune this query without creating any objects like indexes etc. [message #574614 is a reply to message #574612] Mon, 14 January 2013 03:54 Go to previous messageGo to next message
Roachcoach
Messages: 1233
Registered: May 2010
Location: UK
Senior Member
Is that really the problem instance plan...?

Check the DOP is appropriate and the hardware can cope - 64 is huge. For that kind of DOP I'd be hitting objects upwards of 500gig in size and even then its well into diminishing returns/overkill.
Re: can we tune this query without creating any objects like indexes etc. [message #574615 is a reply to message #574614] Mon, 14 January 2013 04:09 Go to previous messageGo to next message
msol25
Messages: 234
Registered: June 2011
Location: Mumbai
Senior Member
Dear Roachcoach,

Trying the same but till now not getting any expected result from Degree of Parallelism...Please help and my table size is 64GB only

[Updated on: Mon, 14 January 2013 04:10]

Report message to a moderator

Re: can we tune this query without creating any objects like indexes etc. [message #574617 is a reply to message #574615] Mon, 14 January 2013 04:36 Go to previous messageGo to next message
msol25
Messages: 234
Registered: June 2011
Location: Mumbai
Senior Member
Dear Experts,

Please anybody can help me?
Re: can we tune this query without creating any objects like indexes etc. [message #574620 is a reply to message #574617] Mon, 14 January 2013 05:55 Go to previous messageGo to next message
Roachcoach
Messages: 1233
Registered: May 2010
Location: UK
Senior Member
When looking at DoP things to consider are

Object(s) size
Operation types
CPU power (physical/virtual count and speed)
Disk subsystem capabilities.

Without knowing or understanding these getting the 'right' DoP is not with the realms of practical possibility (trial and error excluded).

Find out the CPU capabilities of the host, the disk i/o capabilities; additionally check what you're getting and the instance is already doing. Parallel is a great way to run a box into the ground.


Also there's the obligatory, why no new objects/partitioning?
Re: can we tune this query without creating any objects like indexes etc. [message #574621 is a reply to message #574620] Mon, 14 January 2013 06:00 Go to previous messageGo to next message
msol25
Messages: 234
Registered: June 2011
Location: Mumbai
Senior Member
Thanks Roachcoach !!!
Re: can we tune this query without creating any objects like indexes etc. [message #574622 is a reply to message #574621] Mon, 14 January 2013 06:08 Go to previous messageGo to next message
Roachcoach
Messages: 1233
Registered: May 2010
Location: UK
Senior Member
By way of example:

  1* select /*+ full(t) */ count(*) from 60somethinggigtable t
11:53:58 SQL> /

  COUNT(*)
----------
 474283314

Elapsed: 00:08:14.42
12:02:13 SQL> select /*+ full(t) parallel(t 8) */ count(*) from 60somethinggigtable t
12:02:40   2  /

  COUNT(*)
----------
 474283319

Elapsed: 00:01:06.40
12:03:48 SQL> select /*+ full(t) parallel(t 64) */ count(*) from 60somethinggigtable t
12:04:25   2  /

  COUNT(*)
----------
 474283319

Elapsed: 00:00:43.71


Note how even going to 64 DoP doesnt offer much more than 8?

Also at 8, the HBAs were basically maxed out so throwing parallel more wont offer much in the way of storage access gains.

As I say - understand your platform and it's capabilities - do not wield parallel as a fast=true button.

[Updated on: Mon, 14 January 2013 06:08]

Report message to a moderator

Re: can we tune this query without creating any objects like indexes etc. [message #574627 is a reply to message #574622] Mon, 14 January 2013 06:41 Go to previous messageGo to next message
msol25
Messages: 234
Registered: June 2011
Location: Mumbai
Senior Member
Dear Roachcoach,

I can't use DEGREE MORE THAN 4 as well,as per our production envtt.
Re: can we tune this query without creating any objects like indexes etc. [message #574628 is a reply to message #574627] Mon, 14 January 2013 07:04 Go to previous messageGo to next message
Michel Cadot
Messages: 59988
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So why:
Quote:
/*+ PARALLEL(a,64) */

with a degree of 64?

Regards
Michel
Re: can we tune this query without creating any objects like indexes etc. [message #574629 is a reply to message #574628] Mon, 14 January 2013 07:07 Go to previous messageGo to next message
msol25
Messages: 234
Registered: June 2011
Location: Mumbai
Senior Member
Actually that time my collegue not confirmed and now we check impact for Degree=64,As its not recommended as per our system requirement due to heavy load.
Re: can we tune this query without creating any objects like indexes etc. [message #574662 is a reply to message #574629] Mon, 14 January 2013 14:01 Go to previous messageGo to next message
xtender
Messages: 33
Registered: January 2011
Location: Russia
Member

Please, execute this commands in sql*plus and show output:
sho parameter db_file_multiblock_read_count;
sho parameter parallel;
sho parameter cpu;
select * from v$version;
Re: can we tune this query without creating any objects like indexes etc. [message #574664 is a reply to message #574662] Mon, 14 January 2013 14:04 Go to previous message
xtender
Messages: 33
Registered: January 2011
Location: Russia
Member

Also could you show trace or real time sql monitor report(it's more preferable if your version is 11g)
Previous Topic: how to handle null in date time
Next Topic: Bulk collect and for loop iteration error
Goto Forum:
  


Current Time: Thu Dec 18 15:16:11 CST 2014

Total time taken to generate the page: 0.10491 seconds