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  |
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 #554147 is a reply to message #554143] |
Thu, 10 May 2012 16:13  |
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 |
 |
Kevin Meade
Messages: 2103
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 #554251 is a reply to message #554238] |
Fri, 11 May 2012 11:34 |
|
Kevin Meade
Messages: 2103
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
|
|
|
|
Goto Forum:
Current Time: Wed Feb 19 14:33:36 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
