Home » RDBMS Server » Performance Tuning » PLSQL taking long time (11.2.0.2.5)
PLSQL taking long time [message #554142] Thu, 10 May 2012 15:23 Go to next message
getzeeshan
Messages: 65
Registered: July 2008
Member
HI There,

I have a query to tune and this query needs to be re-written can anyone please help me

SELECT /*+ parallel (score 8) */  trim(GUEST.GPM_GUEST_ID)||'|'||
trim(SCORE.SCORE_DATE)||'|'||
trim(SCORE.SOURCE)||'|'||
trim(SCORE.SCORE_VERSION)||'|'||
trim(SCORE.PART)||'|'||
trim(SCORE.SCORE_VALUE)||'|'||
trim(SCORE.STATUS)||'|'||
trim(SCORE.CREATE_DATE)||'|'||
trim(SCORE.CREATE_USER)||'|'||
trim(SCORE.UPDATE_DATE)||'|'||
trim(SCORE.UPDATE_USER)
FROM T_CUST_SCORE SCORE, T_GPM_GUEST GUEST
WHERE SCORE.CUSTOMER_ID = GUEST.CUSTOMER_ID
UNION
SELECT  /*+ parallel (score 8) */  trim(GUEST.GPM_GUEST_ID)||'|'||
trim(SCORE.SCORE_DATE)||'|'||
trim(SCORE.SOURCE)||'|'||
trim(SCORE.SCORE_VERSION)||'|'||
trim(SCORE.PART)||'|'||
trim(SCORE.SCORE_VALUE)||'|'||
trim(SCORE.STATUS)||'|'||
trim(SCORE.CREATE_DATE)||'|'||
trim(SCORE.CREATE_USER)||'|'||
trim(SCORE.UPDATE_DATE)||'|'||
trim(SCORE.UPDATE_USER)
FROM T_HHONORS_SCORE SCORE, T_HHONORS_MEM MEM, T_GPM_GUEST GUEST
WHERE SCORE.ID_MEMBER = MEM.ID_MEMBER
AND MEM.CUSTOMER_ID = GUEST.CUSTOMER_ID;


Explain plan is

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2919488539

----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name            | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                 |   378M|    35G|       |   266K (16)| 00:40:38 |        |      |            |
|   1 |  PX COORDINATOR                 |                 |       |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)           | :TQ10007        |   378M|    35G|       |   266K (16)| 00:40:38 |  Q1,07 | P->S | QC (RAND)  |
|   3 |    SORT UNIQUE                  |                 |   378M|    35G|    46G|   266K (16)| 00:40:38 |  Q1,07 | PCWP |            |
|   4 |     PX RECEIVE                  |                 |       |       |       |            |          |  Q1,07 | PCWP |            |
|   5 |      PX SEND HASH               | :TQ10006        |       |       |       |            |          |  Q1,06 | P->P | HASH       |
|   6 |       BUFFER SORT               |                 |   378M|    35G|       |   266K (16)| 00:40:38 |  Q1,06 | PCWP |            |
|   7 |        UNION-ALL                |                 |       |       |       |            |          |  Q1,06 | PCWP |            |
|*  8 |         HASH JOIN               |                 |   309M|    27G|  4256K|   208K  (8)| 00:31:52 |  Q1,06 | PCWP |            |
|   9 |          PX RECEIVE             |                 |    21M|   286M|       |   228   (3)| 00:00:03 |  Q1,06 | PCWP |            |
|  10 |           PX SEND HASH          | :TQ10002        |    21M|   286M|       |   228   (3)| 00:00:03 |  Q1,02 | P->P | HASH       |
|  11 |            PX BLOCK ITERATOR    |                 |    21M|   286M|       |   228   (3)| 00:00:03 |  Q1,02 | PCWC |            |
|  12 |             TABLE ACCESS FULL   | T_GPM_GUEST     |    21M|   286M|       |   228   (3)| 00:00:03 |  Q1,02 | PCWP |            |
|  13 |          PX RECEIVE             |                 |  1659M|   125G|       |   183K  (9)| 00:28:01 |  Q1,06 | PCWP |            |
|  14 |           PX SEND HASH          | :TQ10003        |  1659M|   125G|       |   183K  (9)| 00:28:01 |  Q1,03 | P->P | HASH       |
|  15 |            PX BLOCK ITERATOR    |                 |  1659M|   125G|       |   183K  (9)| 00:28:01 |  Q1,03 | PCWC |            |
|  16 |             TABLE ACCESS FULL   | T_CUST_SCORE    |  1659M|   125G|       |   183K  (9)| 00:28:01 |  Q1,03 | PCWP |            |
|* 17 |         HASH JOIN               |                 |    68M|  8457M|  7424K| 16133   (6)| 00:02:28 |  Q1,06 | PCWP |            |
|  18 |          PX RECEIVE             |                 |    24M|   648M|       |  1363   (3)| 00:00:13 |  Q1,06 | PCWP |            |
|  19 |           PX SEND HASH          | :TQ10004        |    24M|   648M|       |  1363   (3)| 00:00:13 |  Q1,04 | P->P | HASH       |
|* 20 |            HASH JOIN BUFFERED   |                 |    24M|   648M|  4256K|  1363   (3)| 00:00:13 |  Q1,04 | PCWP |            |
|  21 |             PX RECEIVE          |                 |    21M|   286M|       |   228   (3)| 00:00:03 |  Q1,04 | PCWP |            |
|  22 |              PX SEND HASH       | :TQ10000        |    21M|   286M|       |   228   (3)| 00:00:03 |  Q1,00 | P->P | HASH       |
|  23 |               PX BLOCK ITERATOR |                 |    21M|   286M|       |   228   (3)| 00:00:03 |  Q1,00 | PCWC |            |
|  24 |                TABLE ACCESS FULL| T_GPM_GUEST     |    21M|   286M|       |   228   (3)| 00:00:03 |  Q1,00 | PCWP |            |
|  25 |             PX RECEIVE          |                 |    32M|   433M|       |   898   (2)| 00:00:09 |  Q1,04 | PCWP |            |
|  26 |              PX SEND HASH       | :TQ10001        |    32M|   433M|       |   898   (2)| 00:00:09 |  Q1,01 | P->P | HASH       |
|  27 |               PX BLOCK ITERATOR |                 |    32M|   433M|       |   898   (2)| 00:00:09 |  Q1,01 | PCWC |            |
|  28 |                TABLE ACCESS FULL| T_HHONORS_MEM   |    32M|   433M|       |   898   (2)| 00:00:09 |  Q1,01 | PCWP |            |
|  29 |          PX RECEIVE             |                 |    91M|  8818M|       | 12947   (7)| 00:01:59 |  Q1,06 | PCWP |            |
|  30 |           PX SEND HASH          | :TQ10005        |    91M|  8818M|       | 12947   (7)| 00:01:59 |  Q1,05 | P->P | HASH       |
|  31 |            PX BLOCK ITERATOR    |                 |    91M|  8818M|       | 12947   (7)| 00:01:59 |  Q1,05 | PCWC |            |
|  32 |             TABLE ACCESS FULL   | T_HHONORS_SCORE |    91M|  8818M|       | 12947   (7)| 00:01:59 |  Q1,05 | PCWP |            |
----------------------------------------------------------------------------------------------------------------------------------------

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

   8 - access("SCORE"."CUSTOMER_ID"="GUEST"."CUSTOMER_ID")
  17 - access("SCORE"."ID_MEMBER"="MEM"."ID_MEMBER")
  20 - access("MEM"."CUSTOMER_ID"="GUEST"."CUSTOMER_ID")



please can anyone help on this?

Regards
Zee
Re: PLSQL taking long time [message #554143 is a reply to message #554142] Thu, 10 May 2012 15:58 Go to previous messageGo to next message
BlackSwan
Messages: 23134
Registered: January 2009
Senior Member
Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) output from SQL_TRACE & tkprof
Re: PLSQL taking long time [message #554147 is a reply to message #554143] Thu, 10 May 2012 16:13 Go to previous messageGo to next message
getzeeshan
Messages: 65
Registered: July 2008
Member
Here you go with the DDL's



SELECT /*+ parallel (score 8) */  trim(GUEST.GPM_GUEST_ID)||'|'||
trim(SCORE.SCORE_DATE)||'|'||
trim(SCORE.SOURCE)||'|'||
trim(SCORE.SCORE_VERSION)||'|'||
trim(SCORE.PART)||'|'||
trim(SCORE.SCORE_VALUE)||'|'||
trim(SCORE.STATUS)||'|'||
trim(SCORE.CREATE_DATE)||'|'||
trim(SCORE.CREATE_USER)||'|'||
trim(SCORE.UPDATE_DATE)||'|'||
trim(SCORE.UPDATE_USER)
FROM T_CUST_SCORE SCORE, T_GPM_GUEST GUEST
WHERE SCORE.CUSTOMER_ID = GUEST.CUSTOMER_ID
UNION
SELECT  /*+ parallel (score 8) */  trim(GUEST.GPM_GUEST_ID)||'|'||
trim(SCORE.SCORE_DATE)||'|'||
trim(SCORE.SOURCE)||'|'||
trim(SCORE.SCORE_VERSION)||'|'||
trim(SCORE.PART)||'|'||
trim(SCORE.SCORE_VALUE)||'|'||
trim(SCORE.STATUS)||'|'||
trim(SCORE.CREATE_DATE)||'|'||
trim(SCORE.CREATE_USER)||'|'||
trim(SCORE.UPDATE_DATE)||'|'||
trim(SCORE.UPDATE_USER)
FROM T_HHONORS_SCORE SCORE, T_HHONORS_MEM MEM, T_GPM_GUEST GUEST
WHERE SCORE.ID_MEMBER = MEM.ID_MEMBER
AND MEM.CUSTOMER_ID = GUEST.CUSTOMER_ID;

================


CREATE TABLE T_CUST_SCORE
(
  SCORE_DATE     TIMESTAMP(6),
  CUSTOMER_ID    NUMBER(20),
  SOURCE         VARCHAR2(40 BYTE),
  SCORE_VERSION  VARCHAR2(40 BYTE),
  PART           VARCHAR2(40 BYTE),
  SCORE_VALUE    VARCHAR2(80 BYTE),
  STATUS         VARCHAR2(1 BYTE),
  CREATE_DATE    TIMESTAMP(6),
  CREATE_USER    VARCHAR2(14 BYTE),
  UPDATE_DATE    TIMESTAMP(6),
  UPDATE_USER    VARCHAR2(14 BYTE)
)

--> No Indexes on this

-----------------------------

CREATE TABLE T_GPM_GUEST
(
  GPM_GUEST_ID     NUMBER(20),
  CUSTOMER_ID      NUMBER(20),
  LAST_NAME        VARCHAR2(32 BYTE),
  FIRST_NAME       VARCHAR2(24 BYTE),
  MIDDLE_INIT      VARCHAR2(18 BYTE),
  TITLE            VARCHAR2(20 BYTE),
  GENDER           VARCHAR2(1 BYTE),
  MOD_NUM          NUMBER(20),
  PCRS_ID          NUMBER(20),
  INTRIGGER        VARCHAR2(64 BYTE),
  HILSTAR_ID       NUMBER(20),
  FREQ_GUEST_FLAG  NUMBER(20),
  GUEST_FLAG       NUMBER(20),
  CREATED_AT       VARCHAR2(16 BYTE),
  STATUS           VARCHAR2(1 BYTE),
  GPM_CREATE_DATE  DATE,
  CREATE_DATE      TIMESTAMP(6),
  CREATE_USER      VARCHAR2(25 BYTE),
  UPDATE_DATE      TIMESTAMP(6),
  UPDATE_USER      VARCHAR2(25 BYTE),
  GPM_UPDATE_DATE  DATE
)

--> Indexes are


CREATE UNIQUE INDEX PK_T_GPM_GUEST1 ON T_GPM_GUEST
(GPM_GUEST_ID)
TABLESPACE HILT_CDW_INDEX_16K
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          20M
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           );


CREATE INDEX T_GPM_GUEST_IDX ON T_GPM_GUEST
(CUSTOMER_ID)
TABLESPACE HILT_CDW_INDEX_16K
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           );
           
==========================================

CREATE TABLE T_HHONORS_SCORE
(
  SCORE_DATE     TIMESTAMP(6),
  ID_MEMBER      NUMBER(10),
  SOURCE         VARCHAR2(40 BYTE),
  SCORE_VERSION  VARCHAR2(40 BYTE),
  PART           VARCHAR2(40 BYTE),
  SCORE_VALUE    VARCHAR2(80 BYTE),
  STATUS         VARCHAR2(1 BYTE),
  CREATE_DATE    TIMESTAMP(6),
  CREATE_USER    VARCHAR2(14 BYTE),
  UPDATE_DATE    TIMESTAMP(6),
  UPDATE_USER    VARCHAR2(14 BYTE)
)

--> Indexes are


CREATE UNIQUE INDEX PK_T_HHONORS_SCORE_1 ON T_HHONORS_SCORE
(SCORE_DATE, ID_MEMBER, SOURCE, SCORE_VERSION, PART)
TABLESPACE HILT_CDW_INDEX_16K
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          20M
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           );
           
==========================================

CREATE TABLE T_HHONORS_MEM
(
  ID_MEMBER               NUMBER(10),
  CUSTOMER_ID             NUMBER(20),
  HASH_KEY                VARCHAR2(18 BYTE),
  CODE_NAME_PREFIX        VARCHAR2(10 BYTE),
  CODE_NAME_SUFFIX        VARCHAR2(4 BYTE),
  FIRST_NAME              VARCHAR2(30 BYTE),
  MIDDLE_INIT             VARCHAR2(1 BYTE),
  LAST_NAME               VARCHAR2(30 BYTE),
  SHORT_NAME              VARCHAR2(15 BYTE),
  TITLE                   VARCHAR2(45 BYTE),
  SEX                     VARCHAR2(1 BYTE),
  MARITAL_STAT            VARCHAR2(1 BYTE),
  BIRTH_DATE              DATE,
  COMPANY_NAME            VARCHAR2(40 BYTE),
  CODE_ADDR_TYPE          VARCHAR2(1 BYTE),
  ADDR1                   VARCHAR2(35 BYTE),
  ADDR2                   VARCHAR2(35 BYTE),
  ADDR3                   VARCHAR2(35 BYTE),
  CITY                    VARCHAR2(30 BYTE),
  CODE_STATE              VARCHAR2(4 BYTE),
  ZIP                     VARCHAR2(5 BYTE),
  CODE_POSTAL             VARCHAR2(9 BYTE),
  CODE_COUNTRY            VARCHAR2(3 BYTE),
  CODE_LANGUAGE           VARCHAR2(2 BYTE),
  STATUS                  VARCHAR2(1 BYTE),
  SALUTATION_ID           VARCHAR2(10 BYTE),
  MAIL_STATUS             VARCHAR2(1 BYTE),
  SPEC_CORR_IND           VARCHAR2(1 BYTE),
  PRODUCT_IND             VARCHAR2(1 BYTE),
  HOME_PHONE_NUM          VARCHAR2(16 BYTE),
  BUS_PHONE_NUM           VARCHAR2(16 BYTE),
  BUS_PHONE_EXT           VARCHAR2(5 BYTE),
  FAX_NUM                 VARCHAR2(16 BYTE),
  EMAIL_ADDR              VARCHAR2(80 BYTE),
  PIN_NO                  VARCHAR2(4 BYTE),
  CODE_NCOA_IND           VARCHAR2(1 BYTE),
  MAIL_PROFILE            VARCHAR2(2 BYTE),
  STMT_CYCLE              VARCHAR2(1 BYTE),
  CARD_TYPE               VARCHAR2(4 BYTE),
  CARD_NO                 VARCHAR2(20 BYTE),
  IND_CARD_EXPIRE         VARCHAR2(1 BYTE),
  IND_MEM_GROUP           VARCHAR2(3 BYTE),
  IND_CHILD               VARCHAR2(1 BYTE),
  IND_PURGE               VARCHAR2(3 BYTE),
  PURGE_DATE              DATE,
  REACT_DATE              DATE,
  PASSPORT                VARCHAR2(15 BYTE),
  USERID                  VARCHAR2(14 BYTE),
  AUDIT_TIME              DATE,
  IND_LANGUAGE_REQUESTED  VARCHAR2(1 BYTE),
  COMM_PROFILE_STATUS     VARCHAR2(1 BYTE),
  NO_PROMPT_EMAIL         VARCHAR2(1 BYTE),
  NO_PROMPT_FAX           VARCHAR2(1 BYTE),
  NO_PROMPT_PHONE         VARCHAR2(1 BYTE),
  REACT_USERID            VARCHAR2(14 BYTE),
  CREATE_DATE             TIMESTAMP(6),
  CREATE_USER             VARCHAR2(14 BYTE),
  UPDATE_DATE             TIMESTAMP(6),
  UPDATE_USER             VARCHAR2(14 BYTE),
  DB_FLAG                 VARCHAR2(1 BYTE),
  IND_FORFEIT             CHAR(1 BYTE),
  REINST_DATE             DATE
)


--> Indexes are


CREATE INDEX IX_HHONORS_MEM_CUSTOMER_ID ON T_HHONORS_MEM
(CUSTOMER_ID)
TABLESPACE HILT_CDW_INDEX_16K
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           );


CREATE UNIQUE INDEX PK_T_HHONORS_MEM_1 ON T_HHONORS_MEM
(ID_MEMBER)
TABLESPACE HILT_CDW_INDEX_16K
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          20M
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           );
           
=========================================


Im working on the Trace
Re: PLSQL taking long time [message #554154 is a reply to message #554142] Thu, 10 May 2012 20:54 Go to previous messageGo to next message
Kevin Meade
Messages: 1967
Registered: December 1999
Location: Connecticut USA
Senior Member
Not sure what you think the problem is. But I offer these comments:

1) TRIM is expensive, why are you using it? I see this a lot with apps that don't need it but for some reason they think they do. Please check with your app team and ask why the applications that input this data are so stupid that you are required to use TRIM on data elements when you select them. This should never be required and is a clear indicator of a bad app design, or a bad database design.

2) UNION implies that the sets of rows you are producing are not mutually exclusive. This may be true. But then again I often see people use UNION instead of UNION ALL because they don't know any better. Ask someone who knows the data if the rows provided by each subquery are overlapping or not. Then use UNION or UNION ALL accordingly. If UNION ALL will suffice, you can skip a sort and duplicates removal step which could mean little or lots depending upon the size of your result set.

3) Your queries do not filter any data. Thus your query plan should say FULL TABLE SCAN and HASH JOIN everywhere. Seems to me that is what it is doing.

4) Read up about COVERING INDEXES. I see that two of your tables (GUEST/MEM) have only a few columns referenced by the query. You can gain some small reduction in I/O by creating a COVERING INDEX so that you do an INDEX FAST FULL SCAN rather than a TABLE SCAN.

5) Make sure your statistics correctly reflect the number of rows in the tables. This will help the optimizer decide on the best join order which will help it to reduce the intermediary rowset sizes and thus join costs. Do this by checking NUM_ROWS in DBA_TABLES. If you find the counts are off by more than 5 times, then you need to re-collect stats on these tables.

** as a side note, has anyone else notice how crappy this new interface is on ORAFAQ. I sit here typing in these messages and I can only see have the characters on the line I am typing in. Whoever updates our software needs to fix this or back out the update please. I know the "scrollable window" seems cool but it is really just annyoing for those of us typing more than a window's worth of information.

Kevin

[Updated on: Thu, 10 May 2012 20:55]

Report message to a moderator

Re: PLSQL taking long time [message #554238 is a reply to message #554154] Fri, 11 May 2012 10:44 Go to previous messageGo to next message
getzeeshan
Messages: 65
Registered: July 2008
Member
Hi Kevin. Thanks! very much for such a wonderful notes.

The First thing I told the developer is the TRIM thing and about UNION ALL but I dont know why they need that. I suggested him exactly the same thing yesterday.
And I removed the Parallel hint and asked him to use the hint 'USE_NL' and the whole execution plan has changed.

I will have to wait for him and see if he is ok with the modifications of removing the TRIM and using the UNION ALL if that works then it will ease up developers life and mine too Smile

One quick question ... Does the Overall Cost that matters the query performance or the execution path?
--> why Im asking this is when I changed the above query the execution path to me looks very nice but costs seems to be increasing. The Hints worked in removing the full table scans at many places but overall cost shows high in Explain plan.
Hope you got my questions?

--Zee
Re: PLSQL taking long time [message #554251 is a reply to message #554238] Fri, 11 May 2012 11:34 Go to previous message
Kevin Meade
Messages: 1967
Registered: December 1999
Location: Connecticut USA
Senior Member
I pay no attention to COST as reported by the CBO. It is for the CBO to compare its plans at the time it generates them. Still, you can generally assume that a higher cost is a more costly plan.

Please re-read what I said. I noted that your plan should be FULL TABLE SCAN and HASH JOINS (which it is), not NESTED LOOP (NL) with INDEX LOOKUPS. Changing it to NL will likely result in very high costs.

In the end though, my best methods for cost are

1) wall clock
2) buffer gets
3) CPU seconds

Use AUTOTRACE and V$SQLPLAN to check for acutal consumed resources in order to compare various plans. Yes, this means you need to run the plan. For a query, run a CREATE TABLE AS... in order to avoid network costs that will skew the numbers.

Kevin
Previous Topic: filesystemio_options=SETALL is slower than ASYNCH, why?
Next Topic: query slow in application, fast on SQL*Plus executing
Goto Forum:
  


Current Time: Thu Dec 18 06:54:43 CST 2014

Total time taken to generate the page: 0.11424 seconds