Mixing
PLSQL Performance issue multiple schemas procedure
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
0
I got two schemas , LDS and LDSI. (Oracle 11.2.0.3.0)
LDSI is interface like schema, that acts as some queue/event based schema. Theres a table that acts as a queue list, and a schedule job to process it. (Create_Splits)
PROCEDURE create_split_lots IS
CURSOR splits IS
SELECT *
FROM ldsi_v_event_changes_base
WHERE interface_name = 'LDSI_SPLITS_MERGES'
AND (event_type = 'SPLIT_START')
AND transfer_state = 'N'
AND dml_operation = 'INS'
ORDER BY event_ts ASC; --FOR UPDATE NOWAIT
l_log_id NUMBER;
l_lot_id NUMBER;
l_dependant_lot_id NUMBER;
l_event_date DATE;
BEGIN
FOR lot IN splits LOOP
BEGIN
l_lot_id := lds_owner.lds_lot.get_or_create_lot(p_lotid => lot.lotid, p_system_code => lot.system_code);
l_dependant_lot_id :=
lds_owner.lds_lot.get_or_create_lot(p_lotid => lot.dependant_lotid,
p_system_code => lot.dependant_system_code);
IF l_lot_id IS NULL OR l_dependant_lot_id IS NULL THEN
raise_application_error(-20343,'error ' );
END IF;
lds_owner.lds_event.copy_events(p_source_lot_id => l_dependant_lot_id,
p_target_lot_id => l_lot_id,
p_until_date => l_event_date,
p_deep_copy => TRUE);
ldsi_lot_container_consumer.complete_get_event_change(p_id => lot.id);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
ldsi_log.log_fatal_error(p_message => 'SQLCODE:' || SQLCODE || ' Message: ' || SQLERRM)
CONTINUE;
END;
END LOOP;
END;
Now LDS_OWNER, is the schema that contains all the data, lot information, event information etc.
This call is taking ALWAYS one minute even if it has no events ive tested the query in the cursos by itself, the performance is no problem in there.
lds_owner.lds_event.copy_events(p_source_lot_id => l_dependant_lot_id,
p_target_lot_id => l_lot_id,
p_until_date => l_event_date,
p_deep_copy => TRUE);
And it resides on the LDS_OWNER schema. The code logic is simple, Theres a event table that contains all the events of a LOT, and the procedure copies all the events from one LOT to the other.
PROCEDURE copy_events (p_source_lot_id IN lds_lots.id%TYPE,
p_target_lot_id IN lds_lots.id%TYPE,
p_until_date IN DATE DEFAULT SYSDATE,
p_deep_copy IN BOOLEAN DEFAULT FALSE)
IS
l_log_id NUMBER;
l_has_events BOOLEAN :=FALSE;
CURSOR list_of_events (p_source_lot_id IN lds_lots.id%TYPE, p_target_lot_id IN lds_lots.id%TYPE)
IS
SELECT *
FROM (
SELECT p1.id,
p1.evtt_id,
p1.state,
p1.channel_type,
p1.hist_date,
p1.hist_seqnum,
p1.postponed_ny,
p1.postponed_by,
p1.postponed_date,
p1.target_area,
p1.escalated_ny,
p1.escalated_to,
p1.escalated_by,
p1.escalated_date,
p1.released_ny,
p1.released_by,
p1.released_date,
p1.created_by,
p1.created_date,
p1.modified_by,
p1.modified_date,
p1.lot_id,
p1.copy_ny,
CAST (p2.dependant_event_type AS VARCHAR2 (30)) AS copy_reason,
p1.parent_evt_id,
p1.origin_evt_id
FROM lds_events p1
JOIN lds_v_event_changes_base p2
ON (p1.lot_id = p2.dependant_lotid
AND p2.dependant_lotid = p_source_lot_id
AND p2.lotid = p_target_lot_id) /*AND P2.EVENT_TYPE = 'SPLIT_START'*/
WHERE p1.lot_id = p_source_lot_id
AND ((p1.origin_evt_id NOT IN (SELECT origin_evt_id
FROM lds_events
WHERE lds_events.lot_id = p_target_lot_id)
OR p1.origin_evt_id IS NULL)
AND NOT EXISTS
(SELECT 1
FROM lds_owner.lds_events
WHERE lds_owner.lds_events.lot_id = p_target_lot_id
AND p1.id = origin_evt_id))
AND p1.created_date < COALESCE (NULL, SYSDATE)
AND p1.state = 'OPEN'
AND p1.created_date < p2.event_ts
);
l_temp_event lds_events%ROWTYPE;
l_event_reason_comment_rec lds_comments%ROWTYPE;
l_target_lot_varchar_id VARCHAR2 (200);
l_target_system_code VARCHAR2 (200);
BEGIN
SELECT lotid,system_code
INTO l_target_lot_varchar_id,l_target_system_code
FROM lds_lots
WHERE id = p_target_lot_id;
FOR event IN list_of_events (p_source_lot_id, p_target_lot_id)
LOOP
l_has_events:=TRUE;
l_temp_event := NULL;
l_temp_event := duplicate_event_row (event);
l_temp_event.parent_evt_id := event.id;
l_temp_event.lot_id := p_target_lot_id;
l_temp_event.origin_evt_id := COALESCE (event.origin_evt_id, event.id);
-- l_temp_event.copy_reason := event.dependant_event_type;
l_temp_event.target_area :=
COALESCE (ldsi_cspec.get_effective_event_area (p_channel_type => event.channel_type,
p_lotid => l_target_lot_varchar_id,
p_source_system => l_target_system_code,
p_area => event.target_area),
event.target_area);
INSERT INTO lds_events VALUES l_temp_event
RETURNING id INTO l_temp_event.id;
l_event_reason_comment_rec := copy_comment (event.channel_type, l_temp_event.id, event.id);
INSERT INTO lds_comments
VALUES l_event_reason_comment_rec;
-- COPY DOCUMENTS
copy_documents (p_source_event => event.id, p_target_event => l_temp_event.id);
END LOOP;
IF l_has_events THEN COMMIT; END IF;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
RAISE;
END copy_events;
FUNCTION duplicate_event_row (source_event IN lds_events%ROWTYPE)
RETURN lds_events%ROWTYPE
IS
l_temp_event lds_events%ROWTYPE;
BEGIN
l_temp_event := source_event;
l_temp_event.id := NULL;
l_temp_event.created_by := NULL;
l_temp_event.created_date := NULL;
l_temp_event.modified_by := NULL;
l_temp_event.modified_date := NULL;
l_temp_event.copy_ny := 'Y';
RETURN l_temp_event;
END duplicate_event_row;
EDIT ONE: The open cursor seams the problem after all.
Query explain plan:
Plan
SELECT STATEMENT ALL_ROWSCost: 1,084 Bytes: 167 Cardinality: 1
11 SORT ORDER BY Cost: 1,084 Bytes: 167 Cardinality: 1
10 FILTER
7 NESTED LOOPS ANTI Cost: 1,083 Bytes: 167 Cardinality: 1
4 HASH JOIN Cost: 1,080 Bytes: 160 Cardinality: 1
2 TABLE ACCESS BY INDEX ROWID TABLE LDS_OWNER.LDS_EVENTS Cost: 2 Bytes: 96 Cardinality: 1
1 INDEX RANGE SCAN INDEX LDS_OWNER.EVT_LOT_STATE_ESCALATED_I Cost: 1 Cardinality: 1
3 TABLE ACCESS FULL TABLE LCC_OWNER.LCC_CIF_EVENT_CHANGES Cost: 1,077 Bytes: 2,880 Cardinality: 45
6 TABLE ACCESS BY INDEX ROWID TABLE LDS_OWNER.LDS_EVENTS Cost: 3 Bytes: 7 Cardinality: 1
5 INDEX RANGE SCAN INDEX LDS_OWNER.EVT_LOT_FK_I Cost: 1 Cardinality: 3
9 TABLE ACCESS BY INDEX ROWID TABLE LDS_OWNER.LDS_EVENTS Cost: 3 Bytes: 7 Cardinality: 1
8 INDEX RANGE SCAN INDEX LDS_OWNER.EVT_LOT_FK_I Cost: 1 Cardinality: 3
Edit two changed cursor with bulk collect, performance not better.
oracle plsql oracle11g
asked Jan 3 at 10:39
NoizeNoize
77214
add a comment |
0
I got two schemas , LDS and LDSI. (Oracle 11.2.0.3.0)
LDSI is interface like schema, that acts as some queue/event based schema. Theres a table that acts as a queue list, and a schedule job to process it. (Create_Splits)
PROCEDURE create_split_lots IS
CURSOR splits IS
SELECT *
FROM ldsi_v_event_changes_base
WHERE interface_name = 'LDSI_SPLITS_MERGES'
AND (event_type = 'SPLIT_START')
AND transfer_state = 'N'
AND dml_operation = 'INS'
ORDER BY event_ts ASC; --FOR UPDATE NOWAIT
l_log_id NUMBER;
l_lot_id NUMBER;
l_dependant_lot_id NUMBER;
l_event_date DATE;
BEGIN
FOR lot IN splits LOOP
BEGIN
l_lot_id := lds_owner.lds_lot.get_or_create_lot(p_lotid => lot.lotid, p_system_code => lot.system_code);
l_dependant_lot_id :=
lds_owner.lds_lot.get_or_create_lot(p_lotid => lot.dependant_lotid,
p_system_code => lot.dependant_system_code);
IF l_lot_id IS NULL OR l_dependant_lot_id IS NULL THEN
raise_application_error(-20343,'error ' );
END IF;
lds_owner.lds_event.copy_events(p_source_lot_id => l_dependant_lot_id,
p_target_lot_id => l_lot_id,
p_until_date => l_event_date,
p_deep_copy => TRUE);
ldsi_lot_container_consumer.complete_get_event_change(p_id => lot.id);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
ldsi_log.log_fatal_error(p_message => 'SQLCODE:' || SQLCODE || ' Message: ' || SQLERRM)
CONTINUE;
END;
END LOOP;
END;
Now LDS_OWNER, is the schema that contains all the data, lot information, event information etc.
This call is taking ALWAYS one minute even if it has no events ive tested the query in the cursos by itself, the performance is no problem in there.
lds_owner.lds_event.copy_events(p_source_lot_id => l_dependant_lot_id,
p_target_lot_id => l_lot_id,
p_until_date => l_event_date,
p_deep_copy => TRUE);
And it resides on the LDS_OWNER schema. The code logic is simple, Theres a event table that contains all the events of a LOT, and the procedure copies all the events from one LOT to the other.
PROCEDURE copy_events (p_source_lot_id IN lds_lots.id%TYPE,
p_target_lot_id IN lds_lots.id%TYPE,
p_until_date IN DATE DEFAULT SYSDATE,
p_deep_copy IN BOOLEAN DEFAULT FALSE)
IS
l_log_id NUMBER;
l_has_events BOOLEAN :=FALSE;
CURSOR list_of_events (p_source_lot_id IN lds_lots.id%TYPE, p_target_lot_id IN lds_lots.id%TYPE)
IS
SELECT *
FROM (
SELECT p1.id,
p1.evtt_id,
p1.state,
p1.channel_type,
p1.hist_date,
p1.hist_seqnum,
p1.postponed_ny,
p1.postponed_by,
p1.postponed_date,
p1.target_area,
p1.escalated_ny,
p1.escalated_to,
p1.escalated_by,
p1.escalated_date,
p1.released_ny,
p1.released_by,
p1.released_date,
p1.created_by,
p1.created_date,
p1.modified_by,
p1.modified_date,
p1.lot_id,
p1.copy_ny,
CAST (p2.dependant_event_type AS VARCHAR2 (30)) AS copy_reason,
p1.parent_evt_id,
p1.origin_evt_id
FROM lds_events p1
JOIN lds_v_event_changes_base p2
ON (p1.lot_id = p2.dependant_lotid
AND p2.dependant_lotid = p_source_lot_id
AND p2.lotid = p_target_lot_id) /*AND P2.EVENT_TYPE = 'SPLIT_START'*/
WHERE p1.lot_id = p_source_lot_id
AND ((p1.origin_evt_id NOT IN (SELECT origin_evt_id
FROM lds_events
WHERE lds_events.lot_id = p_target_lot_id)
OR p1.origin_evt_id IS NULL)
AND NOT EXISTS
(SELECT 1
FROM lds_owner.lds_events
WHERE lds_owner.lds_events.lot_id = p_target_lot_id
AND p1.id = origin_evt_id))
AND p1.created_date < COALESCE (NULL, SYSDATE)
AND p1.state = 'OPEN'
AND p1.created_date < p2.event_ts
);
l_temp_event lds_events%ROWTYPE;
l_event_reason_comment_rec lds_comments%ROWTYPE;
l_target_lot_varchar_id VARCHAR2 (200);
l_target_system_code VARCHAR2 (200);
BEGIN
SELECT lotid,system_code
INTO l_target_lot_varchar_id,l_target_system_code
FROM lds_lots
WHERE id = p_target_lot_id;
FOR event IN list_of_events (p_source_lot_id, p_target_lot_id)
LOOP
l_has_events:=TRUE;
l_temp_event := NULL;
l_temp_event := duplicate_event_row (event);
l_temp_event.parent_evt_id := event.id;
l_temp_event.lot_id := p_target_lot_id;
l_temp_event.origin_evt_id := COALESCE (event.origin_evt_id, event.id);
-- l_temp_event.copy_reason := event.dependant_event_type;
l_temp_event.target_area :=
COALESCE (ldsi_cspec.get_effective_event_area (p_channel_type => event.channel_type,
p_lotid => l_target_lot_varchar_id,
p_source_system => l_target_system_code,
p_area => event.target_area),
event.target_area);
INSERT INTO lds_events VALUES l_temp_event
RETURNING id INTO l_temp_event.id;
l_event_reason_comment_rec := copy_comment (event.channel_type, l_temp_event.id, event.id);
INSERT INTO lds_comments
VALUES l_event_reason_comment_rec;
-- COPY DOCUMENTS
copy_documents (p_source_event => event.id, p_target_event => l_temp_event.id);
END LOOP;
IF l_has_events THEN COMMIT; END IF;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
RAISE;
END copy_events;
FUNCTION duplicate_event_row (source_event IN lds_events%ROWTYPE)
RETURN lds_events%ROWTYPE
IS
l_temp_event lds_events%ROWTYPE;
BEGIN
l_temp_event := source_event;
l_temp_event.id := NULL;
l_temp_event.created_by := NULL;
l_temp_event.created_date := NULL;
l_temp_event.modified_by := NULL;
l_temp_event.modified_date := NULL;
l_temp_event.copy_ny := 'Y';
RETURN l_temp_event;
END duplicate_event_row;
EDIT ONE: The open cursor seams the problem after all.
Query explain plan:
Plan
SELECT STATEMENT ALL_ROWSCost: 1,084 Bytes: 167 Cardinality: 1
11 SORT ORDER BY Cost: 1,084 Bytes: 167 Cardinality: 1
10 FILTER
7 NESTED LOOPS ANTI Cost: 1,083 Bytes: 167 Cardinality: 1
4 HASH JOIN Cost: 1,080 Bytes: 160 Cardinality: 1
2 TABLE ACCESS BY INDEX ROWID TABLE LDS_OWNER.LDS_EVENTS Cost: 2 Bytes: 96 Cardinality: 1
1 INDEX RANGE SCAN INDEX LDS_OWNER.EVT_LOT_STATE_ESCALATED_I Cost: 1 Cardinality: 1
3 TABLE ACCESS FULL TABLE LCC_OWNER.LCC_CIF_EVENT_CHANGES Cost: 1,077 Bytes: 2,880 Cardinality: 45
6 TABLE ACCESS BY INDEX ROWID TABLE LDS_OWNER.LDS_EVENTS Cost: 3 Bytes: 7 Cardinality: 1
5 INDEX RANGE SCAN INDEX LDS_OWNER.EVT_LOT_FK_I Cost: 1 Cardinality: 3
9 TABLE ACCESS BY INDEX ROWID TABLE LDS_OWNER.LDS_EVENTS Cost: 3 Bytes: 7 Cardinality: 1
8 INDEX RANGE SCAN INDEX LDS_OWNER.EVT_LOT_FK_I Cost: 1 Cardinality: 3
Edit two changed cursor with bulk collect, performance not better.
oracle plsql oracle11g
asked Jan 3 at 10:39
NoizeNoize
77214
add a comment |
0
0
0
I got two schemas , LDS and LDSI. (Oracle 11.2.0.3.0)
LDSI is interface like schema, that acts as some queue/event based schema. Theres a table that acts as a queue list, and a schedule job to process it. (Create_Splits)
PROCEDURE create_split_lots IS
CURSOR splits IS
SELECT *
FROM ldsi_v_event_changes_base
WHERE interface_name = 'LDSI_SPLITS_MERGES'
AND (event_type = 'SPLIT_START')
AND transfer_state = 'N'
AND dml_operation = 'INS'
ORDER BY event_ts ASC; --FOR UPDATE NOWAIT
l_log_id NUMBER;
l_lot_id NUMBER;
l_dependant_lot_id NUMBER;
l_event_date DATE;
BEGIN
FOR lot IN splits LOOP
BEGIN
l_lot_id := lds_owner.lds_lot.get_or_create_lot(p_lotid => lot.lotid, p_system_code => lot.system_code);
l_dependant_lot_id :=
lds_owner.lds_lot.get_or_create_lot(p_lotid => lot.dependant_lotid,
p_system_code => lot.dependant_system_code);
IF l_lot_id IS NULL OR l_dependant_lot_id IS NULL THEN
raise_application_error(-20343,'error ' );
END IF;
lds_owner.lds_event.copy_events(p_source_lot_id => l_dependant_lot_id,
p_target_lot_id => l_lot_id,
p_until_date => l_event_date,
p_deep_copy => TRUE);
ldsi_lot_container_consumer.complete_get_event_change(p_id => lot.id);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
ldsi_log.log_fatal_error(p_message => 'SQLCODE:' || SQLCODE || ' Message: ' || SQLERRM)
CONTINUE;
END;
END LOOP;
END;
Now LDS_OWNER, is the schema that contains all the data, lot information, event information etc.
This call is taking ALWAYS one minute even if it has no events ive tested the query in the cursos by itself, the performance is no problem in there.
lds_owner.lds_event.copy_events(p_source_lot_id => l_dependant_lot_id,
p_target_lot_id => l_lot_id,
p_until_date => l_event_date,
p_deep_copy => TRUE);
And it resides on the LDS_OWNER schema. The code logic is simple, Theres a event table that contains all the events of a LOT, and the procedure copies all the events from one LOT to the other.
PROCEDURE copy_events (p_source_lot_id IN lds_lots.id%TYPE,
p_target_lot_id IN lds_lots.id%TYPE,
p_until_date IN DATE DEFAULT SYSDATE,
p_deep_copy IN BOOLEAN DEFAULT FALSE)
IS
l_log_id NUMBER;
l_has_events BOOLEAN :=FALSE;
CURSOR list_of_events (p_source_lot_id IN lds_lots.id%TYPE, p_target_lot_id IN lds_lots.id%TYPE)
IS
SELECT *
FROM (
SELECT p1.id,
p1.evtt_id,
p1.state,
p1.channel_type,
p1.hist_date,
p1.hist_seqnum,
p1.postponed_ny,
p1.postponed_by,
p1.postponed_date,
p1.target_area,
p1.escalated_ny,
p1.escalated_to,
p1.escalated_by,
p1.escalated_date,
p1.released_ny,
p1.released_by,
p1.released_date,
p1.created_by,
p1.created_date,
p1.modified_by,
p1.modified_date,
p1.lot_id,
p1.copy_ny,
CAST (p2.dependant_event_type AS VARCHAR2 (30)) AS copy_reason,
p1.parent_evt_id,
p1.origin_evt_id
FROM lds_events p1
JOIN lds_v_event_changes_base p2
ON (p1.lot_id = p2.dependant_lotid
AND p2.dependant_lotid = p_source_lot_id
AND p2.lotid = p_target_lot_id) /*AND P2.EVENT_TYPE = 'SPLIT_START'*/
WHERE p1.lot_id = p_source_lot_id
AND ((p1.origin_evt_id NOT IN (SELECT origin_evt_id
FROM lds_events
WHERE lds_events.lot_id = p_target_lot_id)
OR p1.origin_evt_id IS NULL)
AND NOT EXISTS
(SELECT 1
FROM lds_owner.lds_events
WHERE lds_owner.lds_events.lot_id = p_target_lot_id
AND p1.id = origin_evt_id))
AND p1.created_date < COALESCE (NULL, SYSDATE)
AND p1.state = 'OPEN'
AND p1.created_date < p2.event_ts
);
l_temp_event lds_events%ROWTYPE;
l_event_reason_comment_rec lds_comments%ROWTYPE;
l_target_lot_varchar_id VARCHAR2 (200);
l_target_system_code VARCHAR2 (200);
BEGIN
SELECT lotid,system_code
INTO l_target_lot_varchar_id,l_target_system_code
FROM lds_lots
WHERE id = p_target_lot_id;
FOR event IN list_of_events (p_source_lot_id, p_target_lot_id)
LOOP
l_has_events:=TRUE;
l_temp_event := NULL;
l_temp_event := duplicate_event_row (event);
l_temp_event.parent_evt_id := event.id;
l_temp_event.lot_id := p_target_lot_id;
l_temp_event.origin_evt_id := COALESCE (event.origin_evt_id, event.id);
-- l_temp_event.copy_reason := event.dependant_event_type;
l_temp_event.target_area :=
COALESCE (ldsi_cspec.get_effective_event_area (p_channel_type => event.channel_type,
p_lotid => l_target_lot_varchar_id,
p_source_system => l_target_system_code,
p_area => event.target_area),
event.target_area);
INSERT INTO lds_events VALUES l_temp_event
RETURNING id INTO l_temp_event.id;
l_event_reason_comment_rec := copy_comment (event.channel_type, l_temp_event.id, event.id);
INSERT INTO lds_comments
VALUES l_event_reason_comment_rec;
-- COPY DOCUMENTS
copy_documents (p_source_event => event.id, p_target_event => l_temp_event.id);
END LOOP;
IF l_has_events THEN COMMIT; END IF;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
RAISE;
END copy_events;
FUNCTION duplicate_event_row (source_event IN lds_events%ROWTYPE)
RETURN lds_events%ROWTYPE
IS
l_temp_event lds_events%ROWTYPE;
BEGIN
l_temp_event := source_event;
l_temp_event.id := NULL;
l_temp_event.created_by := NULL;
l_temp_event.created_date := NULL;
l_temp_event.modified_by := NULL;
l_temp_event.modified_date := NULL;
l_temp_event.copy_ny := 'Y';
RETURN l_temp_event;
END duplicate_event_row;
EDIT ONE: The open cursor seams the problem after all.
Query explain plan:
Plan
SELECT STATEMENT ALL_ROWSCost: 1,084 Bytes: 167 Cardinality: 1
11 SORT ORDER BY Cost: 1,084 Bytes: 167 Cardinality: 1
10 FILTER
7 NESTED LOOPS ANTI Cost: 1,083 Bytes: 167 Cardinality: 1
4 HASH JOIN Cost: 1,080 Bytes: 160 Cardinality: 1
2 TABLE ACCESS BY INDEX ROWID TABLE LDS_OWNER.LDS_EVENTS Cost: 2 Bytes: 96 Cardinality: 1
1 INDEX RANGE SCAN INDEX LDS_OWNER.EVT_LOT_STATE_ESCALATED_I Cost: 1 Cardinality: 1
3 TABLE ACCESS FULL TABLE LCC_OWNER.LCC_CIF_EVENT_CHANGES Cost: 1,077 Bytes: 2,880 Cardinality: 45
6 TABLE ACCESS BY INDEX ROWID TABLE LDS_OWNER.LDS_EVENTS Cost: 3 Bytes: 7 Cardinality: 1
5 INDEX RANGE SCAN INDEX LDS_OWNER.EVT_LOT_FK_I Cost: 1 Cardinality: 3
9 TABLE ACCESS BY INDEX ROWID TABLE LDS_OWNER.LDS_EVENTS Cost: 3 Bytes: 7 Cardinality: 1
8 INDEX RANGE SCAN INDEX LDS_OWNER.EVT_LOT_FK_I Cost: 1 Cardinality: 3
Edit two changed cursor with bulk collect, performance not better.
oracle plsql oracle11g
asked Jan 3 at 10:39
NoizeNoize
77214
I got two schemas , LDS and LDSI. (Oracle 11.2.0.3.0)
LDSI is interface like schema, that acts as some queue/event based schema. Theres a table that acts as a queue list, and a schedule job to process it. (Create_Splits)
PROCEDURE create_split_lots IS
CURSOR splits IS
SELECT *
FROM ldsi_v_event_changes_base
WHERE interface_name = 'LDSI_SPLITS_MERGES'
AND (event_type = 'SPLIT_START')
AND transfer_state = 'N'
AND dml_operation = 'INS'
ORDER BY event_ts ASC; --FOR UPDATE NOWAIT
l_log_id NUMBER;
l_lot_id NUMBER;
l_dependant_lot_id NUMBER;
l_event_date DATE;
BEGIN
FOR lot IN splits LOOP
BEGIN
l_lot_id := lds_owner.lds_lot.get_or_create_lot(p_lotid => lot.lotid, p_system_code => lot.system_code);
l_dependant_lot_id :=
lds_owner.lds_lot.get_or_create_lot(p_lotid => lot.dependant_lotid,
p_system_code => lot.dependant_system_code);
IF l_lot_id IS NULL OR l_dependant_lot_id IS NULL THEN
raise_application_error(-20343,'error ' );
END IF;
lds_owner.lds_event.copy_events(p_source_lot_id => l_dependant_lot_id,
p_target_lot_id => l_lot_id,
p_until_date => l_event_date,
p_deep_copy => TRUE);
ldsi_lot_container_consumer.complete_get_event_change(p_id => lot.id);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
ldsi_log.log_fatal_error(p_message => 'SQLCODE:' || SQLCODE || ' Message: ' || SQLERRM)
CONTINUE;
END;
END LOOP;
END;
Now LDS_OWNER, is the schema that contains all the data, lot information, event information etc.
This call is taking ALWAYS one minute even if it has no events ive tested the query in the cursos by itself, the performance is no problem in there.
lds_owner.lds_event.copy_events(p_source_lot_id => l_dependant_lot_id,
p_target_lot_id => l_lot_id,
p_until_date => l_event_date,
p_deep_copy => TRUE);
And it resides on the LDS_OWNER schema. The code logic is simple, Theres a event table that contains all the events of a LOT, and the procedure copies all the events from one LOT to the other.
PROCEDURE copy_events (p_source_lot_id IN lds_lots.id%TYPE,
p_target_lot_id IN lds_lots.id%TYPE,
p_until_date IN DATE DEFAULT SYSDATE,
p_deep_copy IN BOOLEAN DEFAULT FALSE)
IS
l_log_id NUMBER;
l_has_events BOOLEAN :=FALSE;
CURSOR list_of_events (p_source_lot_id IN lds_lots.id%TYPE, p_target_lot_id IN lds_lots.id%TYPE)
IS
SELECT *
FROM (
SELECT p1.id,
p1.evtt_id,
p1.state,
p1.channel_type,
p1.hist_date,
p1.hist_seqnum,
p1.postponed_ny,
p1.postponed_by,
p1.postponed_date,
p1.target_area,
p1.escalated_ny,
p1.escalated_to,
p1.escalated_by,
p1.escalated_date,
p1.released_ny,
p1.released_by,
p1.released_date,
p1.created_by,
p1.created_date,
p1.modified_by,
p1.modified_date,
p1.lot_id,
p1.copy_ny,
CAST (p2.dependant_event_type AS VARCHAR2 (30)) AS copy_reason,
p1.parent_evt_id,
p1.origin_evt_id
FROM lds_events p1
JOIN lds_v_event_changes_base p2
ON (p1.lot_id = p2.dependant_lotid
AND p2.dependant_lotid = p_source_lot_id
AND p2.lotid = p_target_lot_id) /*AND P2.EVENT_TYPE = 'SPLIT_START'*/
WHERE p1.lot_id = p_source_lot_id
AND ((p1.origin_evt_id NOT IN (SELECT origin_evt_id
FROM lds_events
WHERE lds_events.lot_id = p_target_lot_id)
OR p1.origin_evt_id IS NULL)
AND NOT EXISTS
(SELECT 1
FROM lds_owner.lds_events
WHERE lds_owner.lds_events.lot_id = p_target_lot_id
AND p1.id = origin_evt_id))
AND p1.created_date < COALESCE (NULL, SYSDATE)
AND p1.state = 'OPEN'
AND p1.created_date < p2.event_ts
);
l_temp_event lds_events%ROWTYPE;
l_event_reason_comment_rec lds_comments%ROWTYPE;
l_target_lot_varchar_id VARCHAR2 (200);
l_target_system_code VARCHAR2 (200);
BEGIN
SELECT lotid,system_code
INTO l_target_lot_varchar_id,l_target_system_code
FROM lds_lots
WHERE id = p_target_lot_id;
FOR event IN list_of_events (p_source_lot_id, p_target_lot_id)
LOOP
l_has_events:=TRUE;
l_temp_event := NULL;
l_temp_event := duplicate_event_row (event);
l_temp_event.parent_evt_id := event.id;
l_temp_event.lot_id := p_target_lot_id;
l_temp_event.origin_evt_id := COALESCE (event.origin_evt_id, event.id);
-- l_temp_event.copy_reason := event.dependant_event_type;
l_temp_event.target_area :=
COALESCE (ldsi_cspec.get_effective_event_area (p_channel_type => event.channel_type,
p_lotid => l_target_lot_varchar_id,
p_source_system => l_target_system_code,
p_area => event.target_area),
event.target_area);
INSERT INTO lds_events VALUES l_temp_event
RETURNING id INTO l_temp_event.id;
l_event_reason_comment_rec := copy_comment (event.channel_type, l_temp_event.id, event.id);
INSERT INTO lds_comments
VALUES l_event_reason_comment_rec;
-- COPY DOCUMENTS
copy_documents (p_source_event => event.id, p_target_event => l_temp_event.id);
END LOOP;
IF l_has_events THEN COMMIT; END IF;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
RAISE;
END copy_events;
FUNCTION duplicate_event_row (source_event IN lds_events%ROWTYPE)
RETURN lds_events%ROWTYPE
IS
l_temp_event lds_events%ROWTYPE;
BEGIN
l_temp_event := source_event;
l_temp_event.id := NULL;
l_temp_event.created_by := NULL;
l_temp_event.created_date := NULL;
l_temp_event.modified_by := NULL;
l_temp_event.modified_date := NULL;
l_temp_event.copy_ny := 'Y';
RETURN l_temp_event;
END duplicate_event_row;
EDIT ONE: The open cursor seams the problem after all.
Query explain plan:
Plan
SELECT STATEMENT ALL_ROWSCost: 1,084 Bytes: 167 Cardinality: 1
11 SORT ORDER BY Cost: 1,084 Bytes: 167 Cardinality: 1
10 FILTER
7 NESTED LOOPS ANTI Cost: 1,083 Bytes: 167 Cardinality: 1
4 HASH JOIN Cost: 1,080 Bytes: 160 Cardinality: 1
2 TABLE ACCESS BY INDEX ROWID TABLE LDS_OWNER.LDS_EVENTS Cost: 2 Bytes: 96 Cardinality: 1
1 INDEX RANGE SCAN INDEX LDS_OWNER.EVT_LOT_STATE_ESCALATED_I Cost: 1 Cardinality: 1
3 TABLE ACCESS FULL TABLE LCC_OWNER.LCC_CIF_EVENT_CHANGES Cost: 1,077 Bytes: 2,880 Cardinality: 45
6 TABLE ACCESS BY INDEX ROWID TABLE LDS_OWNER.LDS_EVENTS Cost: 3 Bytes: 7 Cardinality: 1
5 INDEX RANGE SCAN INDEX LDS_OWNER.EVT_LOT_FK_I Cost: 1 Cardinality: 3
9 TABLE ACCESS BY INDEX ROWID TABLE LDS_OWNER.LDS_EVENTS Cost: 3 Bytes: 7 Cardinality: 1
8 INDEX RANGE SCAN INDEX LDS_OWNER.EVT_LOT_FK_I Cost: 1 Cardinality: 3
Edit two changed cursor with bulk collect, performance not better.
oracle plsql oracle11g
oracle plsql oracle11g
asked Jan 3 at 10:39
NoizeNoize
77214
asked Jan 3 at 10:39
NoizeNoize
77214
edited Jan 4 at 12:01
Noize
asked Jan 3 at 10:39
NoizeNoize
77214
asked Jan 3 at 10:39
NoizeNoize
77214
asked Jan 3 at 10:39
NoizeNoize
77214
77214
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
1
I cannot add comments, so i have to answer here - sorry.
Try to do something like this for every operation in your Code
declare
start_time timestamp;
end_time timestamp;
l_duration number(10,3);
begin
start_time := systimestamp;
do something..
end_time := systimestamp;
l_lock_duration := ROUND(EXTRACT( SECOND FROM end_time - start_time ) +
EXTRACT( MINUTE FROM end_time - start_time ) * 60 +
EXTRACT( HOUR FROM end_time - start_time ) * 60 * 60,
3);
end;
Mayby this long response is just for cursor opening. First step, You have to check time duration for every operation.
answered Jan 3 at 11:00
q4za4q4za4
31719
You are quite right, 85.868. Must be the cursos opening as you mentioned.
– Noize
Jan 3 at 11:21
SQL Query doesn't look like very complex. Can you attach explain plan for query?
– q4za4
Jan 3 at 12:29
I just did. thank you.
– Noize
Jan 3 at 14:27
Seams the cursor is not the problem afterall. still taking tons of time.
– Noize
Jan 4 at 12:01
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54020641%2fplsql-performance-issue-multiple-schemas-procedure%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
I cannot add comments, so i have to answer here - sorry.
Try to do something like this for every operation in your Code
declare
start_time timestamp;
end_time timestamp;
l_duration number(10,3);
begin
start_time := systimestamp;
do something..
end_time := systimestamp;
l_lock_duration := ROUND(EXTRACT( SECOND FROM end_time - start_time ) +
EXTRACT( MINUTE FROM end_time - start_time ) * 60 +
EXTRACT( HOUR FROM end_time - start_time ) * 60 * 60,
3);
end;
Mayby this long response is just for cursor opening. First step, You have to check time duration for every operation.
answered Jan 3 at 11:00
q4za4q4za4
31719
You are quite right, 85.868. Must be the cursos opening as you mentioned.
– Noize
Jan 3 at 11:21
SQL Query doesn't look like very complex. Can you attach explain plan for query?
– q4za4
Jan 3 at 12:29
I just did. thank you.
– Noize
Jan 3 at 14:27
Seams the cursor is not the problem afterall. still taking tons of time.
– Noize
Jan 4 at 12:01
add a comment |
1
I cannot add comments, so i have to answer here - sorry.
Try to do something like this for every operation in your Code
declare
start_time timestamp;
end_time timestamp;
l_duration number(10,3);
begin
start_time := systimestamp;
do something..
end_time := systimestamp;
l_lock_duration := ROUND(EXTRACT( SECOND FROM end_time - start_time ) +
EXTRACT( MINUTE FROM end_time - start_time ) * 60 +
EXTRACT( HOUR FROM end_time - start_time ) * 60 * 60,
3);
end;
Mayby this long response is just for cursor opening. First step, You have to check time duration for every operation.
answered Jan 3 at 11:00
q4za4q4za4
31719
You are quite right, 85.868. Must be the cursos opening as you mentioned.
– Noize
Jan 3 at 11:21
SQL Query doesn't look like very complex. Can you attach explain plan for query?
– q4za4
Jan 3 at 12:29
I just did. thank you.
– Noize
Jan 3 at 14:27
Seams the cursor is not the problem afterall. still taking tons of time.
– Noize
Jan 4 at 12:01
add a comment |
1
1
1
I cannot add comments, so i have to answer here - sorry.
Try to do something like this for every operation in your Code
declare
start_time timestamp;
end_time timestamp;
l_duration number(10,3);
begin
start_time := systimestamp;
do something..
end_time := systimestamp;
l_lock_duration := ROUND(EXTRACT( SECOND FROM end_time - start_time ) +
EXTRACT( MINUTE FROM end_time - start_time ) * 60 +
EXTRACT( HOUR FROM end_time - start_time ) * 60 * 60,
3);
end;
Mayby this long response is just for cursor opening. First step, You have to check time duration for every operation.
answered Jan 3 at 11:00
q4za4q4za4
31719
I cannot add comments, so i have to answer here - sorry.
Try to do something like this for every operation in your Code
declare
start_time timestamp;
end_time timestamp;
l_duration number(10,3);
begin
start_time := systimestamp;
do something..
end_time := systimestamp;
l_lock_duration := ROUND(EXTRACT( SECOND FROM end_time - start_time ) +
EXTRACT( MINUTE FROM end_time - start_time ) * 60 +
EXTRACT( HOUR FROM end_time - start_time ) * 60 * 60,
3);
end;
Mayby this long response is just for cursor opening. First step, You have to check time duration for every operation.
answered Jan 3 at 11:00
q4za4q4za4
31719
answered Jan 3 at 11:00
q4za4q4za4
31719
answered Jan 3 at 11:00
q4za4q4za4
31719
answered Jan 3 at 11:00
q4za4q4za4
31719
31719
You are quite right, 85.868. Must be the cursos opening as you mentioned.
– Noize
Jan 3 at 11:21
SQL Query doesn't look like very complex. Can you attach explain plan for query?
– q4za4
Jan 3 at 12:29
I just did. thank you.
– Noize
Jan 3 at 14:27
Seams the cursor is not the problem afterall. still taking tons of time.
– Noize
Jan 4 at 12:01
add a comment |
You are quite right, 85.868. Must be the cursos opening as you mentioned.
– Noize
Jan 3 at 11:21
SQL Query doesn't look like very complex. Can you attach explain plan for query?
– q4za4
Jan 3 at 12:29
I just did. thank you.
– Noize
Jan 3 at 14:27
Seams the cursor is not the problem afterall. still taking tons of time.
– Noize
Jan 4 at 12:01
You are quite right, 85.868. Must be the cursos opening as you mentioned.
– Noize
Jan 3 at 11:21
You are quite right, 85.868. Must be the cursos opening as you mentioned.
– Noize
Jan 3 at 11:21
SQL Query doesn't look like very complex. Can you attach explain plan for query?
– q4za4
Jan 3 at 12:29
SQL Query doesn't look like very complex. Can you attach explain plan for query?
– q4za4
Jan 3 at 12:29
I just did. thank you.
– Noize
Jan 3 at 14:27
I just did. thank you.
– Noize
Jan 3 at 14:27
Seams the cursor is not the problem afterall. still taking tons of time.
– Noize
Jan 4 at 12:01
Seams the cursor is not the problem afterall. still taking tons of time.
– Noize
Jan 4 at 12:01
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54020641%2fplsql-performance-issue-multiple-schemas-procedure%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
