Application SQL Tuning

–Existing sp
–==================
CREATE OR REPLACE
PROCEDURE fail_login_rest_proc
IS
cusername VARCHAR2(200);
CURSOR c1
IS
SELECT SUBSCRIBERIDENTITY
FROM tblmyspr
WHERE FAILUREATTEMPT!=0
AND passupdatetime   < SYSDATE - INTERVAL '30' MINUTE;
BEGIN
OPEN c1;
FETCH c1 INTO cusername;
UPDATE tblmyspr SET FAILUREATTEMPT=0 WHERE SUBSCRIBERIDENTITY=cusername;
COMMIT;
CLOSE c1;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;
/

Review SP Code and Analysis
SELECT SUBSCRIBERIDENTITY
FROM tblmyspr
WHERE FAILUREATTEMPT!=0
AND passupdatetime   < SYSDATE – INTERVAL ’30’ MINUTE;

–FULL TABLE SCAN-tblmyspr
–NO INDEX AVAILABLE

UPDATE tblmyspr SET FAILUREATTEMPT=0 WHERE SUBSCRIBERIDENTITY=cusername;
—SELECT and UPDATE Query are dublicate

Action Plan-I-
–Redesign sp
–==================
CREATE OR REPLACE
PROCEDURE fail_login_rest_proc
IS
BEGIN

UPDATE tblmyspr
SET FAILUREATTEMPT=0
WHERE FAILUREATTEMPT!=0
AND passupdatetime   < SYSDATE - INTERVAL '30' MINUTE;

COMMIT;

EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;
/

–Create Index on FAILUREATTEMPT
CREATE INDEX IDX_FAILUREATTEMPT ON tblmyspr (FAILUREATTEMPT);

SET LONG 100000
SET PAGESIZE 0
SELECT DBMS_METADATA.GET_DEPENDENT_DDL('INDEX','tblmyspr') FROM DUAL;

SQL> SET LONG 100000
SQL> SET PAGESIZE 0
SQL> SELECT DBMS_METADATA.GET_DEPENDENT_DDL('INDEX','tblmyspr') FROM DUAL;

CREATE UNIQUE INDEX "WEBIN"."PK_MDWEBINSPR" ON "WEBIN"."tblmyspr" ("SUBSCRIBERIDENTITY")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "WEBIN"
CREATE INDEX "WEBIN"."IDX_FAILUREATTEMPT" ON "WEBIN"."tblmyspr" ("FAILUREATTEMPT")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "WEBIN"

SQL> EXPLAIN PLAN FOR
2  UPDATE tblmyspr b
3  SET b.FAILUREATTEMPT=0
4  WHERE b.FAILUREATTEMPT!=0
5  AND b.passupdatetime   < SYSDATE - INTERVAL '30' MINUTE;

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 407286577
----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |             |     1 |     8 |     3   (0)| 00:00:01|
|   1 |  UPDATE            | tblmyspr |       |       |            |         |
|*  2 |   TABLE ACCESS FULL| tblmyspr |     1 |     8 |     3   (0)| 00:00:01|
----------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

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

2 - filter("B"."FAILUREATTEMPT"<>0 AND
"B"."PASSUPDATETIME"<SYSDATE@!-INTERVAL'+00 00:30:00' DAY(2) TO SECOND(0))

Action Plan-II

EXPLAIN PLAN FOR
UPDATE
/*+ index(b IDX_FAILUREATTEMPT) */
tblmyspr b
SET b.FAILUREATTEMPT=0
WHERE b.FAILUREATTEMPT!=0
AND b.passupdatetime   < SYSDATE - INTERVAL '30' MINUTE;

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Plan hash value: 436113861

---------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name               | Rows  | Bytes | Cost(%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT             |                    |     1 |     8 |2   (0)| 00:00:01 |
|   1 |  UPDATE                      | tblmyspr        |       |       |       |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| tblmyspr        |     1 |     8 |2   (0)| 00:00:01 |
|*  3 |    INDEX FULL SCAN           | IDX_FAILUREATTEMPT |     1 |       |1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("B"."PASSUPDATETIME"<SYSDATE@!-INTERVAL'+00 00:30:00' DAY(2) TO SE
COND(0))

3 - filter("B"."FAILUREATTEMPT"<>0)

16 rows selected.

Action Plan-III
–Create composite Index on FAILUREATTEMPT,PASSUPDATETIME
SQL> DROP INDEX IDX_FAILUREATTEMPT;

SQL> CREATE INDEX IDX_FAILUREATTEMPT ON tblmyspr (FAILUREATTEMPT,PASSUPDATETIME);

Index created.

SQL> EXPLAIN PLAN FOR
2  UPDATE
3  tblmyspr b
4  SET b.FAILUREATTEMPT=0
5  WHERE b.FAILUREATTEMPT!=0
6  AND b.passupdatetime   < SYSDATE - INTERVAL '30' MINUTE;

Explained.

SQL>
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 426793881
---------------------------------------------------------------------------------------
| Id  | Operation        | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT |                    |     1 |     8 |     1   (0)| 00:00:01 |
|   1 |  UPDATE          | tblmyspr        |       |       |            |          |
|*  2 |   INDEX SKIP SCAN| IDX_FAILUREATTEMPT |     1 |     8 |     1   (0)| 00:00:01 |

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

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

2 - access("B"."PASSUPDATETIME"<SYSDATE@!-INTERVAL'+00 00:30:00' DAY(2) TO
SECOND(0))
filter("B"."FAILUREATTEMPT"<>0 AND
"B"."PASSUPDATETIME"<SYSDATE@!-INTERVAL'+00 00:30:00' DAY(2) TO SECOND(0))
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s