Home » RDBMS Server » Performance Tuning » Query running long time (Oracle ,10.2.0.4.0 , SunOS)
Query running long time [message #537037] Mon, 26 December 2011 05:38 Go to next message
Jack14
Messages: 497
Registered: December 2011
Location: INDIA
Senior Member
Hi,

The below query is running for a long time.

SELECT MIN(minbkt),
       maxbkt,
       Substrb(Dump(MIN(val), 16, 0, 32), 1, 120) minval,
       Substrb(Dump(MAX(val), 16, 0, 32), 1, 120) maxval,
       SUM(rep)                                   sumrep,
       SUM(repsq)                                 sumrepsq,
       MAX(rep)                                   maxrep,
       COUNT(*)                                   bktndv,
       SUM(CASE
             WHEN rep = 1 THEN 1
             ELSE 0
           END)                                   unqrep
FROM   (SELECT val,
               MIN(bkt)                minbkt,
               MAX(bkt)                maxbkt,
               COUNT(val)              rep,
               COUNT(val) * COUNT(val) repsq
        FROM   (SELECT
/*+ parallel(t,128) parallel_index(t,128) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring */ Substrb("CLAIM_NO", 1, 32) val,
               Ntile(254) over (ORDER BY Nlssort(Substrb("CLAIM_NO", 1, 32),
               'NLS_SORT = binary'))      bkt
                FROM   "DBO"."TAB_VERSION" t
                WHERE
       Tbl$or$idx$part$num("DBO"."CLAIM_VERSION", 0, 4, 0, "ROWID") =
       :objn
       AND Substrb("CLAIM_NO", 1, 32) IS NOT NULL)
        GROUP  BY val)
GROUP  BY maxbkt
ORDER  BY maxbkt  



Explain plan for the above query is

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1862590303

--------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name              | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                   | 65184 |  7511K|       |   747  (13)| 00:00:09 |        |      |            |
|   1 |  PX COORDINATOR                         |                   |       |       |       |         |     |        |      |            |
|   2 |   PX SEND QC (ORDER)                    | :TQ10004          | 65184 |  7511K|       |   747  (13)| 00:00:09 |  Q1,04 | P->S | QC (ORDER) |
|   3 |    SORT ORDER BY                        |                   | 65184 |  7511K|       |   747  (13)| 00:00:09 |  Q1,04 | PCWP |            |
|   4 |     PX RECEIVE                          |                   | 65184 |  7511K|       |   747  (13)| 00:00:09 |  Q1,04 | PCWP |            |
|   5 |      PX SEND RANGE                      | :TQ10003          | 65184 |  7511K|       |   747  (13)| 00:00:09 |  Q1,03 | P->P | RANGE      |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------
|   6 |       SORT GROUP BY                     |                   | 65184 |  7511K|       |   747  (13)| 00:00:09 |  Q1,03 | PCWP |            |
|   7 |        PX RECEIVE                       |                   | 65184 |  7511K|       |   747  (13)| 00:00:09 |  Q1,03 | PCWP |            |
|   8 |         PX SEND HASH                    | :TQ10002          | 65184 |  7511K|       |   747  (13)| 00:00:09 |  Q1,02 | P->P | HASH       |
|   9 |          SORT GROUP BY                  |                   | 65184 |  7511K|       |   747  (13)| 00:00:09 |  Q1,02 | PCWP |            |
|  10 |           VIEW                          |                   | 65184 |  7511K|       |   746  (13)| 00:00:09 |  Q1,02 | PCWP |            |
|  11 |            HASH GROUP BY                |                   | 65184 |  5028K|       |   746  (13)| 00:00:09 |  Q1,02 | PCWP |            |
|  12 |             PX RECEIVE                  |                   | 65184 |  5028K|       |   746  (13)| 00:00:09 |  Q1,02 | PCWP |            |
|  13 |              PX SEND HASH               | :TQ10001          | 65184 |  5028K|       |   746  (13)| 00:00:09 |  Q1,01 | P->P | HASH       |
|  14 |               HASH GROUP BY             |                   | 65184 |  5028K|       |   746  (13)| 00:00:09 |  Q1,01 | PCWP |            |
|  15 |                VIEW                     |                   | 65184 |  5028K|       |   745  (13)| 00:00:09 |  Q1,01 | PCWP |            |
|  16 |                 WINDOW SORT             |                   | 65184 |  1591K|  4632K|   745  (13)| 00:00:09 |  Q1,01 | PCWP |            |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------
|  17 |                  PX RECEIVE             |                   | 65184 |  1591K|       |   744  (13)| 00:00:09 |  Q1,01 | PCWP |            |
|  18 |                   PX SEND RANGE         | :TQ10000          | 65184 |  1591K|       |   744  (13)| 00:00:09 |  Q1,00 | P->P | RANGE      |
|  19 |                    PX BLOCK ITERATOR    |                   | 65184 |  1591K|       |   744  (13)| 00:00:09 |  Q1,00 | PCWC |            |
|* 20 |                     INDEX FAST FULL SCAN| XIF1TAB_VERSION | 65184 |  1591K|       |   744  (13)| 00:00:09 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------------------------------------------------

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

  20 - filter(SUBSTRB("CLAIM_NO",1,32) IS NOT NULL AND TBL$OR$IDX$PART$NUM("JACK"."TAB_VERSION",0,4,0,ROWID)=TO_NUMBER(:OBJN))


This query is actually gathering table stats whose count of rows is 130459996and the size is 110 GB.
This stats is running for more than 6 hours and activity percentage is 46.55% which comes in Top SQL activity.

Ww are gathering this particular table statistics manually.

Can anybody advice to tune the above query as per explain plan?

Thank you



Re: Query running long time [message #537091 is a reply to message #537037] Mon, 26 December 2011 09:12 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
PLAN shows time of about 9 seconds ("00:00:09").
why the discrepancy?
Re: Query running long time [message #537224 is a reply to message #537091] Tue, 27 December 2011 05:37 Go to previous messageGo to next message
Jack14
Messages: 497
Registered: December 2011
Location: INDIA
Senior Member
< why the discrepancy?

Does it mean that we dont need to worry about the query as it has taken only 9 seconds ("00:00:09") to execute ??

Thank you
Re: Query running long time [message #537349 is a reply to message #537224] Tue, 27 December 2011 19:43 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
1) what is "LONG TIME"

2) the plan seems quite reasonable assuming you are looking at all rows in the table.

3) I think you are lieing to us via a cut/paster error. It seems to me that if you are using the alias "t" for the table DBO then the expression "DBO"."CLAIM_VERSION" should raise an INVALID IDENTIFIER error. This is further witness by line 20 in your plan:

20 - filter(SUBSTRB("CLAIM_NO",1,32) IS NOT NULL AND TBL$OR$IDX$PART$NUM("JACK"."TAB_VERSION",0,4,0,ROWID)=TO_NUMBER(:OBJN))

Who is JACK?

4) the query seems flawed. The expression Substrb("CLAIM_NO", 1, 32) IS NOT NULL is only valid if claim_no is null in its entirity. Not sure what you are getting at with that. Did you really mean to use the byte version of substr()? Maybe I am mis-reading this or my head is dense tonight, but I suggest you rethink what you are looking for in the query.

This stuff aside, assuming there is an actual query behind all this that contains the parts we see, then I suspect your time is being lost in the table function Tbl$or$idx$part$num().

Again, if you are expecting to visit more that 1% of the rows in the table, then this plan seems quite reasonable to me.

Kevin
Re: Query running long time [message #537365 is a reply to message #537349] Wed, 28 December 2011 00:07 Go to previous messageGo to next message
Jack14
Messages: 497
Registered: December 2011
Location: INDIA
Senior Member
Kevin,

Its not like i am lieing to you. I did change the name of the actual table we are using ,
just for privacy reason.Rest all whatever i pasted in the thread is correct.

Re: Query running long time [message #537462 is a reply to message #537365] Wed, 28 December 2011 09:48 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Yes, I understand. I really only used the term loosely. I did not mean to imply you were intentionally trying to decieve anyone. Only to emphasize the need to make sure any code posted actually works before you post it.

What did you think of my commentary otherwise. Are you looking at your table function as the source of the slowdown? Do you accept the idea that your plan may actually be OK?

Kevin
Previous Topic: Performance Tuning
Next Topic: Bitmap Conversion
Goto Forum:
  


Current Time: Fri Sep 24 20:30:00 CDT 2021