Home » RDBMS Server » Performance Tuning » advice to rewrite a query with a bad desing
| advice to rewrite a query with a bad desing [message #631220] |
Sat, 10 January 2015 10:39  |
 |
jojoyk
Messages: 5
Registered: January 2015
Location: Romania
|
Junior Member |
|
|
Hi all,
I have the below query and the explain plan:
SELECT BAM_EVENT.EVENT_ID,
BAM_EVENT.EVENT_NAME,
BAM_EVENT.EVENT_SRC_OBJECT_ID,
BAM_EVENT.EVENT_SRC_OBJECT_NAME,
BAM_EVENT.EVENT_SRC_OBJECT_START_TIME,
BAM_EVENT.EVENT_OCCURRENCE_TIME,
BAM_EVENT.START_TIME_PIM,
BAM_EVENT.END_TIME_PIM,
BAM_EVENT.STATUS,
BAM_EVENT.ORGANIZATION,
BAM_EVENT.ACTIVITY_ID,
BAM_EVENT.ACTIVITY_NAME,
BAM_EVENT.CREATED_BY,
BAM_EVENT.READ_STATUS,
BAM_EVENT.IS_UPLOAD,
BAM_EVENT.PROCESS_TYPE,
BAM_EVENT.PROCESSING_ITERATION
FROM
(SELECT BAM_EVENT.EVENT_ID,
BAM_EVENT.EVENT_NAME,
BAM_EVENT.EVENT_SRC_OBJECT_ID,
BAM_EVENT.EVENT_SRC_OBJECT_NAME,
BAM_EVENT.EVENT_SRC_OBJECT_START_TIME,
BAM_EVENT.EVENT_OCCURRENCE_TIME,
BAM_EVENT.START_TIME_PIM,
BAM_EVENT.END_TIME_PIM,
BAM_EVENT.STATUS,
BAM_EVENT.ORGANIZATION,
BAM_EVENT.ACTIVITY_ID,
BAM_EVENT.ACTIVITY_NAME,
BAM_EVENT.CREATED_BY,
BAM_EVENT.READ_STATUS,
BAM_EVENT.IS_UPLOAD,
BAM_EVENT.PROCESS_TYPE,
BAM_EVENT.PROCESSING_ITERATION ,
ROW_NUMBER() OVER (ORDER BY EVENT_SRC_OBJECT_ID, END_TIME_PIM) RID
FROM CORDYS_BAMMDM_OWNER.BAM_EVENT
WHERE ORGANIZATION = :1 AND
READ_STATUS = :2 AND
EVENT_SRC_OBJECT_ID NOT IN
(SELECT EVENT_SRC_OBJECT_ID
FROM CORDYS_BAMMDM_OWNER.BAM_EVENT_SOURCE_INFO)
ORDER BY EVENT_SRC_OBJECT_ID, END_TIME_PIM) BAM_EVENT
============== EXPLAIN PLAN ========
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
Plan hash value: 2746979244
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1896K| 1642M| 10079 (1)| 00:03:14 |
|* 1 | VIEW | | 1896K| 1642M| 10079 (1)| 00:03:14 |
|* 2 | WINDOW SORT PUSHED RANK| | 1896K| 542M| 10079 (1)| 00:03:14 |
|* 3 | HASH JOIN RIGHT ANTI | | 1896K| 542M| 10079 (1)| 00:03:14 |
| 4 | INDEX FULL SCAN | PK_BAM_EVENT_SOURCE_INFO | 1 | 27 | 0 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | BAM_EVENT | 1896K| 493M| 10074 (1)| 00:03:14 |
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RID"<=30)
2 - filter(ROW_NUMBER() OVER ( ORDER BY "BAM_EVENT"."EVENT_SRC_OBJECT_ID","BAM_EVENT"."END
_TIME_PIM")<=30)
3 - access("EVENT_SRC_OBJECT_ID"="EVENT_SRC_OBJECT_ID")
5 - filter("ORGANIZATION"=:1 AND "READ_STATUS"=:2)
21 rows selected.
Instance version is 11.2.0.3 Standard Edition.
21 rows are returned from 1.8 milion rows that has the table.
The number of the distinct values for the columns in the where clause, organization is 1 and for the read_status 2, so a full table scan I think can not be avoid.
Every time this query is executed is reading almost 1,7 GB of data from the disks, 570Mb for sorting data (the same order by conditions in the query and his subquery).
Please advice how to rewrite the query in order to reduce the cots.
Thank you,
Best Regards,
Joey
--mod update: added [code] tags
[Updated on: Sat, 10 January 2015 10:49] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
| Re: advice to rewrite a query with a bad desing [message #631224 is a reply to message #631222] |
Sat, 10 January 2015 10:53  |
|
jojoyk
Messages: 5
Registered: January 2015
Location: Romania
|
Junior Member |
|
|
CORDYS_BAMMDM_OWNER.BAM_EVENT DEFINITION + INDEXES + CONSTRAINTS
CREATE TABLE CORDYS_BAMMDM_OWNER.BAM_EVENT
(
EVENT_ID NUMBER(18) NOT NULL,
EVENT_NAME VARCHAR2(150 BYTE) NOT NULL,
EVENT_SRC_OBJECT_ID VARCHAR2(100 BYTE) NOT NULL,
EVENT_SRC_OBJECT_NAME VARCHAR2(255 BYTE),
EVENT_SRC_OBJECT_START_TIME DATE DEFAULT SYSDATE NOT NULL,
EVENT_OCCURRENCE_TIME DATE DEFAULT SYSDATE NOT NULL,
START_TIME_PIM NUMBER(16),
END_TIME_PIM NUMBER(16),
STATUS VARCHAR2(50 BYTE),
ORGANIZATION VARCHAR2(240 BYTE) NOT NULL,
ACTIVITY_ID VARCHAR2(255 BYTE),
ACTIVITY_NAME VARCHAR2(255 BYTE),
CREATED_BY VARCHAR2(250 BYTE),
READ_STATUS VARCHAR2(10 BYTE) DEFAULT 'false' NOT NULL,
IS_UPLOAD NUMBER(1) DEFAULT 1 NOT NULL,
UNPROCESSED_BO VARCHAR2(500 BYTE),
PROCESS_TYPE NUMBER(1) DEFAULT 0 NOT NULL,
PROCESSING_ITERATION NUMBER(1)
)
TABLESPACE CORDYS_BAMMDMDSM
RESULT_CACHE (MODE DEFAULT)
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 13096K
NEXT 13096K
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE INDEX CORDYS_BAMMDM_OWNER.IND_001_BAM_EVENT ON CORDYS_BAMMDM_OWNER.BAM_EVENT
(ORGANIZATION, READ_STATUS)
LOGGING
TABLESPACE CORDYS_BAMMDMDSM
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 13096K
NEXT 13096K
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
NOPARALLEL;
CREATE INDEX CORDYS_BAMMDM_OWNER.IND_003_BAM_EVENT ON CORDYS_BAMMDM_OWNER.BAM_EVENT
(EVENT_SRC_OBJECT_ID, EVENT_NAME)
LOGGING
TABLESPACE CORDYS_BAMMDMDSM
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 13096K
NEXT 13096K
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
NOPARALLEL;
CREATE INDEX CORDYS_BAMMDM_OWNER.IND_ENDTIME_PIM ON CORDYS_BAMMDM_OWNER.BAM_EVENT
(END_TIME_PIM)
LOGGING
TABLESPACE CORDYS_BAMMDMDSM
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 13096K
NEXT 13096K
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
NOPARALLEL;
CREATE UNIQUE INDEX CORDYS_BAMMDM_OWNER.PK_BAM_EVENT ON CORDYS_BAMMDM_OWNER.BAM_EVENT
(EVENT_ID)
LOGGING
TABLESPACE CORDYS_BAMMDMDSM
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 13096K
NEXT 13096K
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
NOPARALLEL;
ALTER TABLE CORDYS_BAMMDM_OWNER.BAM_EVENT ADD (
CONSTRAINT PK_BAM_EVENT
PRIMARY KEY
(EVENT_ID)
USING INDEX CORDYS_BAMMDM_OWNER.PK_BAM_EVENT
ENABLE VALIDATE);
GRANT SELECT ON CORDYS_BAMMDM_OWNER.BAM_EVENT TO CORDYS_READ_ONLY;
CREATE TABLE CORDYS_BAMMDM_OWNER.BAM_EVENT_SOURCE_INFO
(
EVENT_SRC_OBJECT_ID VARCHAR2(50 BYTE) NOT NULL,
SERVICE_CONTAINER_DN VARCHAR2(500 BYTE) NOT NULL
)
TABLESPACE CORDYS_BAMMDMDSM
RESULT_CACHE (MODE DEFAULT)
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 13096K
NEXT 13096K
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
|
|
|
|
|
|
|
|
| Re: advice to rewrite a query with a bad desing [message #631229 is a reply to message #631225] |
Sat, 10 January 2015 12:07 |
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
jojoyk wrote on Sat, 10 January 2015 17:04Predicate RID <=30 comes from the name of the subquery. Well, yes, the RID comes from the subquery. What about the 30 ?
You need to post the execution pan for the query you provided, not for some other query. Otherwise, there is nothing one can do. As BS has suggested, the statistics make no sense: the exec plan anticipates nearly two million rows returned, but you say you get twenty one rows. So either your stats are massively wrong (is there really only one row in BAM_EVENT_SOURCE_INFO ?) or you are confusing things.
|
|
|
|
|
|
|
|
| Re: advice to rewrite a query with a bad desing [message #631234 is a reply to message #631232] |
Sat, 10 January 2015 14:59 |
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
This is very difficult, because you appear to have trouble telling the truth.
In your original post, you gave a query with a plan from a different query. This was obvious, because the predicates were different.
In your last post, you have done the same thing: your query against DBA_TAB_STATS_HISTORY cannot possibly give the result you posted. This is obvious because there are columns missing.
What's going on?
|
|
|
|
Goto Forum:
Current Time: Sun Feb 02 17:48:18 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
