Avoid checking bind values in SQL


As SQL query developers, there should always be try to write and design simple sqls and not complex ones . Checking bind values in PL/SQL layer is one such good practice. Today’s post will try to explain and implement this

Bind values are the input values passed to the sql in the WHERE clause as filtering conditions. As general user practice, user never passes all the values in the filter boxes on search screen

Checking all the binds in the sql is way of writing generic code.

Theory behind NULL checks

Passing NULL inside SQL means that optimizer has to create the execution plan for both cases, when the Bind value is NULL and when Bind value is not NULL as optimizer being machine is not sure, what input user will give at runtime. So optimizer will give plan which caters to both use cases, thus larger execution plan(and this larger cost). In such cases, in one case, it will Perform Full Table scan ( for case of NULL bind value) and in another will perform Index Scan ( for case when value is passed)

But as user who is running SQL , user is sure whether he will pass the bind, so if user is not going to give the value to bind variable, the variable is not passed , thus optimizer will not create the path of Full Table scan, this smaller plan so smaller cost

Below is problem sql taken from Production environment of one of large Public Sector enterprise.

As seen from below, for single execution, it is using more than 5 million buffers per execution

Buffer                    Gets                  Elapsed
 Gets        Executions   per Exec      %Total  Time (s)    %CPU  %IO     SQL Id
 ----------- ----------- ------------   ------  ---------- -----  ----- -------------
 50,714,912  10            5,071,491.2   1.4     3,519.9     91.7  0     drtt6o6z013

Problem SQL

Bind values which are passed in SQL are marked as bold.

SELECT CRETD_BY,LOV_VALUE,ROLE_GROUP_ID,USER_ID,FIRST_NAME
FROM
(SELECT
	UM.CRETD_BY, LV.LOV_VALUE, UM.ROLE_GROUP_ID, UM.USER_ID,UM.FIRST_NAME
  FROM
	XXX_USER_MASTR_TB UM,
	XXX_ROLE_GROUP_TB RG,
	XXX_LOV_MASTR_TB LV
  WHERE
		LV.LOV_KEY = UM.DESGNTN
	AND UM.ROLE_GROUP_ID = RG.ROLE_GROUP_ID(+)
	AND UM.COMSNRT_ID = NVL (:1,UM.COMSNRT_ID)
	AND UM.DIVSN_ID = NVL (:2, UM.DIVSN_ID)
	AND UM.RANGE_ID = NVL (:3, UM.RANGE_ID)
	AND UM.SCOPE = :4
	AND UPPER (UM.FIRST_NAME) LIKE UPPER (:5)
	AND UM.DESGNTN = NVL (:6, UM.DESGNTN)
	AND UM.LOGIN_NAME != :7
	AND SSO_ID = NVL (:8, SSO_ID)
	AND SSO_ID <> 11111111
   UNION
	SELECT
		DISTINCT AUMT.CRETD_BY,LV.LOV_VALUE,AUMT.ROLE_GROUP_ID,AUMT.USER_ID,AUMT.FIRST_NAME
	FROM
		XX_USER_MASTR_TB AUMT,
		XX_ROLE_GROUP_TB
		ARGT,XX_ROLE_GROUP_ROLES_TB ARGRT,
		XX_ROLE_TB ART,
		XX_ROLE_JURSDCTNS_TB ARJT,
		XX_ROLE_ACTVTY_TB ARAT,
		XX_USER_DESGNTN_DTLS_TB AUDDT,
		COM_LOV_MASTR_TB LV
	WHERE
		LV.LOV_KEY = AUMT.DESGNTN AND 
                 ART.ROLE_ID IN
				(
			          SELECT   SART.ROLE_ID
				  FROM
					XX_ROLE_TB SART,
					XX_ROLE_JURSDCTNS_TB SARJT,
					XX_ROLE_ACTVTY_TB  SARAT
				   WHERE 
                                       SART.ROLE_ID = SARJT.ROLE_ID AND 
                                       SART.ROLE_ID = SARAT.ROLE_ID AND 
                                       SARJT.RANGE_ID IN
						      (
                                                        SELECT RANGE_ID 
                                                        FROM 
                                                            COM_RANGE_MASTR_TB CRMT, 
                                                            COM_DIVSN_MASTR_TB CDMT 
                                                        WHERE CRMT.DIVSN_ID = 
                                                                     CDMT.DIVSN_ID
               						 AND CDMT.COMSNRT_ID = :9
						        )
					AND AUMT.DESGNTN = NVL (:10,AUMT.DESGNTN)
					AND AUMT.COMSNRT_ID=0
					AND SSO_ID <> 11111111
					GROUP BY SART.ROLE_ID
				)
		AND AUMT.SCOPE = :11
		AND UPPER (AUMT.FIRST_NAME) LIKE UPPER (:12)
		AND SSO_ID = NVL (:13, SSO_ID)
		AND AUMT.ROLE_GROUP_ID = ARGT.ROLE_GROUP_ID
		AND ARAT.ROLE_ID = ART.ROLE_ID AND AUMT.USER_TYPE = 'USER'
		AND ARGT.ROLE_GROUP_ID = ARGRT.ROLE_GROUP_ID
		AND ARGRT.ROLE_ID = ART.ROLE_ID
		AND ART.ROLE_ID = ARJT.ROLE_ID
		AND ART.ROLE_ID = ARAT.ROLE_ID
		AND AUDDT.USER_ID = AUMT.USER_ID
  ) ORDER BY UPPER (LOGIN_NAME)

Bad Plan

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                     | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                              |                           |       |       |   357K(100)|          |
|   1 |  SORT ORDER BY                                |                           |     3 |  1032 |   357K  (3)| 01:11:28 |
|   2 |   VIEW                                        |                           |     3 |  1032 |   357K  (3)| 01:11:28 |
|   3 |    SORT UNIQUE                                |                           |     3 |   457 |   357K  (3)| 01:11:28 |
|   4 |     UNION-ALL                                 |                           |       |       |            |          |
|   5 |      CONCATENATION                            |                           |       |       |            |          |
|   6 |       FILTER                                  |                           |       |       |            |          |
|   7 |        NESTED LOOPS OUTER                     |                           |     1 |   144 |   170   (2)| 00:00:03 |
|   8 |         NESTED LOOPS                          |                           |     1 |   122 |   169   (2)| 00:00:03 |
|   9 |          TABLE ACCESS FULL                    | XX_USER_MASTR_TB         |     1 |    79 |   168   (2)| 00:00:03 |
|  10 |          INDEX RANGE SCAN                     | LOV_KEY_IDX               |     1 |    43 |     1   (0)| 00:00:01 |
|  11 |         TABLE ACCESS BY INDEX ROWID           | XX_ROLE_GROUP_TB         |     1 |    22 |     1   (0)| 00:00:01 |
|  12 |          INDEX UNIQUE SCAN                    | XX_ROLE_GROUP_PK         |     1 |       |     0   (0)|          |
|  13 |       FILTER                                  |                           |       |       |            |          |
|  14 |        NESTED LOOPS OUTER                     |                           |     1 |   144 |   169   (2)| 00:00:03 |
|  15 |         NESTED LOOPS                          |                           |     1 |   122 |   168   (2)| 00:00:03 |
|  16 |          TABLE ACCESS FULL                    | XX_USER_MASTR_TB         |     1 |    79 |   167   (2)| 00:00:03 |
|  17 |          INDEX RANGE SCAN                     | LOV_KEY_IDX               |     1 |    43 |     1   (0)| 00:00:01 |
|  18 |         TABLE ACCESS BY INDEX ROWID           | XX_ROLE_GROUP_TB         |     1 |    22 |     1   (0)| 00:00:01 |
|  19 |          INDEX UNIQUE SCAN                    | XX_ROLE_GROUP_PK         |     1 |       |     0   (0)|          |
|  20 |      NESTED LOOPS                             |                           |    10 |  1690 |   178   (2)| 00:00:03 |
|  21 |       NESTED LOOPS                            |                           |     1 |   164 |   176   (2)| 00:00:03 |
|  22 |        NESTED LOOPS                           |                           |     1 |   159 |   174   (2)| 00:00:03 |
|  23 |         NESTED LOOPS                          |                           |     1 |   116 |   173   (2)| 00:00:03 |
|  24 |          NESTED LOOPS                         |                           |     1 |   110 |   172   (2)| 00:00:03 |
|  25 |           NESTED LOOPS                        |                           |     1 |   105 |   172   (2)| 00:00:03 |
|  26 |            NESTED LOOPS                       |                           |     1 |    95 |   169   (2)| 00:00:03 |
|  27 |             TABLE ACCESS FULL                 | XX_USER_MASTR_TB         |     1 |    73 |   168   (2)| 00:00:03 |
|  28 |             TABLE ACCESS BY INDEX ROWID       | XX_ROLE_GROUP_TB         |     1 |    22 |     1   (0)| 00:00:01 |
|  29 |              INDEX UNIQUE SCAN                | XX_ROLE_GROUP_PK         |     1 |       |     0   (0)|          |
|  30 |            TABLE ACCESS BY INDEX ROWID        | XX_ROLE_GROUP_ROLES_TB   |     1 |    10 |     3   (0)| 00:00:01 |
|  31 |             INDEX RANGE SCAN                  | RG_ID                     |     2 |       |     1   (0)| 00:00:01 |
|  32 |           INDEX UNIQUE SCAN                   | XX_ROLE_PK               |     1 |     5 |     0   (0)|          |
|  33 |            FILTER                             |                           |       |       |            |          |
|  34 |             HASH GROUP BY                     |                           |     1 |    40 |  3646   (3)| 00:00:44 |
|  35 |              FILTER                           |                           |       |       |            |          |
|  36 |               HASH JOIN                       |                           |   343K|    13M|  3622   (3)| 00:00:44 |
|  37 |                NESTED LOOPS                   |                           |  1789 | 62615 |   540   (2)| 00:00:07 |
|  38 |                 HASH JOIN                     |                           |  1789 | 53670 |   539   (2)| 00:00:07 |
|  39 |                  NESTED LOOPS                 |                           |    56 |  1120 |    35   (0)| 00:00:01 |
|  40 |                   NESTED LOOPS                |                           |    56 |  1120 |    35   (0)| 00:00:01 |
|  41 |                    TABLE ACCESS BY INDEX ROWID| COM_DIVSN_MASTR_TB        |     8 |    80 |     5   (0)| 00:00:01 |
|  42 |                     INDEX RANGE SCAN          | DD                        |     8 |       |     1   (0)| 00:00:01 |
|  43 |                    INDEX RANGE SCAN           | DIV                       |     7 |       |     1   (0)| 00:00:01 |
|  44 |                   TABLE ACCESS BY INDEX ROWID | COM_RANGE_MASTR_TB        |     7 |    70 |     4   (0)| 00:00:01 |
|  45 |                  TABLE ACCESS FULL            | XX_ROLE_JURSDCTNS_TB     |   276K|  2700K|   502   (2)| 00:00:07 |
|  46 |                 INDEX UNIQUE SCAN             | XX_ROLE_PK               |     1 |     5 |     0   (0)|          |
|  47 |                INDEX FAST FULL SCAN           | IDX_ARAT_RID              |  3437K|    16M|  3055   (2)| 00:00:37 |
|  48 |          INDEX RANGE SCAN                     | IDX_XX_USER_DESGNTN_DTLS |     1 |     6 |     1   (0)| 00:00:01 |
|  49 |         INDEX RANGE SCAN                      | LOV_KEY_IDX               |     1 |    43 |     1   (0)| 00:00:01 |
|  50 |        INDEX RANGE SCAN                       | DDDD                      |    15 |    75 |     2   (0)| 00:00:01 |
|  51 |       INDEX RANGE SCAN                        | IDX_ARAT_RID              |   192 |   960 |     2   (0)| 00:00:01 |

 Continue reading "Avoid checking bind values in SQL" 
Advertisements

Avoid dbms_output.put_line


dbms_output.put_line is common method to put the debugging messages in PL/SQL code.It works fine as expected and display the log messages to check the flow of program.

Many time the pl/sql code with these messages are put into production without comment, thus leading to slowness of pl/sql code.

When pl/sql code is executed from backend, oracle write these messages on to the console, so main code waits until this message is put up on console.

Since this is “additional work” which needs to be completed, although it is not required from application logic , it causes slowness.

This is test case to see how much slowness it causes

With dbms_output.put_line

DECLARE

CURSOR c is select table_name, column_name from dba_tab_columns ;

v1 varchar2(30);

v2 varchar2(30);

begin

for c_rec in c

loop

dbms_output.put_line(c_rec.table_name );

NULL;

end loop;

dbms_output.put_line(‘test’);

end;

/

..

GV_$SQL_CURSOR

PS_EP_NOM_LANG_VW

PS_GPMX_NRP_FSW_VW

PS_PA_I_OFRM_VW

SCHEDULER$_CDB_COMB_LW_JOB

PSBCDEFN_VW3

PS_FACIL_CMPNT_VW

test

PL/SQL procedure successfully completed.

Elapsed: 00:00:27.65

 

Without dbms_output.put_line

SQL> DECLARE

CURSOR c is select table_name, column_name from dba_tab_columns ;

v1 varchar2(30);

v2 varchar2(30);

begin

for c_rec in c

loop

—              dbms_output.put_line(c_rec.table_name );

NULL;

end loop;

dbms_output.put_line(‘test’);

end;

/

2    3    4    5    6    7    8    9   10   11   12   13   14   15

test

PL/SQL procedure successfully completed.

Elapsed: 00:00:08.81

SQL> SQL> SQL>

Conclusion

Remove / Comment the calls from Production code to have better performance of PL/SQL applications

Audit Files are still generated after setting audit_trail =off ;


If you are hitting this issue, then you are hitting the bug as mentioned in Oracle Support note 21133343.8

Bug 21133343 is there.

*.aud File is Generated in Unified Auditing Environment Even When AUDIT_TRAIL=NONE

This issue can be encountered in all versions of Oracle Database below 12.2

There is no solution 🙂 but to manually delete the files as of now

Increase recovery speed with _cleanup_rollback_entries


Issue

While upgrading Oracle Apps upgrade project for big telecom giant where upgrade scripts were playing the game 🙂
me and my team mate Sunil Yadav, Apps DBA were playing with mass updation script.

Mass update of 400 million records out of 700 million records on big apps table “AP_INVOICE_DISTRIBUTIONS_ALL” after it ran for more than 15 hours
was killed (by pressing Ctrl-C 🙂 ) . It was spawned by adding Parallel hints to the simple update.

It lead to recovery of table which was very slow and showed us timings that it will recover by 2020 !!!

Identify the Time for transaction recovery to complete
select usn, state, undoblockstotal “Total”, undoblocksdone “Done”, undoblockstotal-undoblocksdone “ToDo”,
decode(cputime,0,’unknown’,sysdate+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400)) “Estimated time to complete”
from v$fast_start_transactions;

Solution

The answer to this was hidden in the underscore parameter “_cleanup_rollback_entries”.

CLEANUP_ROLLBACK_ENTRIES is basically the number of undo entries PMON
will process at a time for a TX.

Its default value is 100, which is too low for this kind of mass recovery of table.

Changed it firstly to 400 and it  increased the speed of recovery . Further increased it to 800 and it improved further and helping to finally rollback it in next 10 hours!!

So this underscore parameter _cleanup_rollback_entries should be changed to 2000 to have good rollback speed , in case of rollback scenario like this for some big tables

After recovery, the script was written in ad_parallel_scripts mode(Oracle Application application level parallelism framework)  by spawning different SQL Plus sessions – with update being performed on subset of data by adding the “rownum < 1000 ” clause to query.
Updation completed in 6 hours , which is still on higher side which further needs to cut down

It was not possible to perform CTAS kind of technique here as it was leading to huge rework done .

Nested Loop join hiding bad data


Last Friday , got interesting issue where Oracle Applications developer , showed that with same data in tables, it is running fine in one instance while it is giving error on another.

As per query design, the developer was doing TO_DATE(PUCIF.VALUE), which expects data in column of date  datatype although PUCIF.VALUE is declared varchar2.

SQL> desc HR.PAY_USER_COLUMN_INSTANCES_F
Name Null? Type
—————————————————– ——– ————————————
USER_COLUMN_INSTANCE_ID NOT NULL NUMBER(15)
EFFECTIVE_START_DATE NOT NULL DATE
EFFECTIVE_END_DATE NOT NULL DATE
USER_ROW_ID NOT NULL NUMBER(15)
USER_COLUMN_ID NOT NULL NUMBER(9)
BUSINESS_GROUP_ID NUMBER(15)
LEGISLATION_CODE VARCHAR2(30)
LEGISLATION_SUBGROUP VARCHAR2(30)
VALUE VARCHAR2(80)

Data of Value column is as below. But things are otherwise, it has both Date and other VARCHAR data. So TO_DATE(PUCIF.VALUE) will give error on value other than date

select PUCIF.VALUE from HR.PAY_USER_COLUMN_INSTANCES_F PUCIF where rownum < 6;

22-JUL-2009
15-AUG-2009
CBH.*
CHRH.*
CHRH.HQ

Plan and Result with “Hash Join”

Below sql is using Hash join operation and so performing Full Scan on table PAY_USER_COLUMN_INSTANCES_F. When it was hitting the bad data in this table, it was error

SQL> select PURF.ROW_LOW_RANGE_OR_NAME||’-‘||PUC.USER_COLUMN_NAME,PUCIF.USER_COLUMN_INSTANCE_ID
from
HR.PAY_USER_TABLES PUT,HR.PAY_USER_COLUMNS PUC,HR.PAY_USER_ROWS_F PURF,HR.PAY_USER_COLUMN_INSTANCES_F PUCIF
WHERE 1=1
AND PUT.USER_TABLE_NAME = ‘BIL_PIS_PAYROLL_CUTOFF_DATE’
AND PUT.USER_TABLE_ID = PUC.USER_TABLE_ID
AND PUT.BUSINESS_GROUP_ID = PUC.BUSINESS_GROUP_ID
AND PUC.USER_COLUMN_ID = PUCIF.USER_COLUMN_ID
AND PUT.BUSINESS_GROUP_ID = PUCIF.BUSINESS_GROUP_ID
AND TO_DATE(PUCIF.VALUE) < ADD_MONTHS(SYSDATE,1)
AND TRUNC(SYSDATE) BETWEEN PUCIF.EFFECTIVE_START_DATE AND PUCIF.EFFECTIVE_END_DATE
AND PUT.USER_TABLE_ID = PURF.USER_TABLE_ID
AND PUCIF.USER_ROW_ID = PURF.USER_ROW_ID
ND TRUNC(SYSDATE) BETWEEN PURF.EFFECTIVE_START_DATE AND PURF.EFFECTIVE_END_DATE
AND PUT.BUSINESS_GROUP_ID = FND_PROFILE.VALUE( ‘PER_BUSINESS_GROUP_ID’);

—————————————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 134 | 9 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 134 | 9 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 8 | 134 | 9 (0)| 00:00:01 |
|* 3 | HASH JOIN | | 1 | 101 | 8 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 6 | 348 | 3 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 7 | 348 | 3 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| PAY_USER_TABLES | 1 | 33 | 2 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | PAY_USER_TABLES_UK2 | 1 | | 1 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | PAY_USER_COLUMNS_FK1 | 7 | | 0 (0)| 00:00:01 |
|* 9 | TABLE ACCESS BY INDEX ROWID | PAY_USER_COLUMNS | 6 | 150 | 1 (0)| 00:00:01 |
|* 10 | TABLE ACCESS FULL | PAY_USER_COLUMN_INSTANCES_F | 16 | 688 | 5 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | PAY_USER_ROWS_F_FK1 | 8 | | 0 (0)| 00:00:01 |
|* 12 | TABLE ACCESS BY INDEX ROWID | PAY_USER_ROWS_F | 1 | 33 | 1 (0)| 00:00:01 |
—————————————————————————————————————-

Quarter
——-
APR-2011 701
JAN-2016 933
ORA-01858: a non-numeric character was found where a numeric was expected

Plan and Result with “Nested Loop join”

select /*+ use_nl(PUCIF) index(PUCIF) */ PURF.ROW_LOW_RANGE_OR_NAME||’-‘||PUC.USER_COLUMN_NAME,PUCIF.USER_COLUMN_INSTANCE_ID
from
HR.PAY_USER_TABLES PUT,HR.PAY_USER_COLUMNS PUC,HR.PAY_USER_ROWS_F PURF,HR.PAY_USER_COLUMN_INSTANCES_F PUCIF
WHERE 1=1
AND PUT.USER_TABLE_NAME = ‘XX_PAYROLL_CUTOFF_DATE’
AND PUT.USER_TABLE_ID = PUC.USER_TABLE_ID
AND PUT.BUSINESS_GROUP_ID = PUC.BUSINESS_GROUP_ID
AND PUC.USER_COLUMN_ID = PUCIF.USER_COLUMN_ID
AND PUT.BUSINESS_GROUP_ID = PUCIF.BUSINESS_GROUP_ID
AND TO_DATE(PUCIF.VALUE) < ADD_MONTHS(SYSDATE,1)
AND TRUNC(SYSDATE) BETWEEN PUCIF.EFFECTIVE_START_DATE AND PUCIF.EFFECTIVE_END_DATE
AND PUT.USER_TABLE_ID = PURF.USER_TABLE_ID
AND PUCIF.USER_ROW_ID = PURF.USER_ROW_ID
AND TRUNC(SYSDATE) BETWEEN PURF.EFFECTIVE_START_DATE AND PURF.EFFECTIVE_END_DATE
AND PUT.BUSINESS_GROUP_ID = FND_PROFILE.VALUE( ‘PER_BUSINESS_GROUP_ID’);

—————————————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 134 | 6 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 134 | 6 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 134 | 6 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 91 | 4 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 58 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| PAY_USER_TABLES | 1 | 33 | 2 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | PAY_USER_TABLES_UK2 | 1 | | 1 (0)| 00:00:01 |
|* 7 | TABLE ACCESS BY INDEX ROWID| PAY_USER_COLUMNS | 6 | 150 | 1 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | PAY_USER_COLUMNS_FK1 | 7 | | 0 (0)| 00:00:01 |
|* 9 | TABLE ACCESS BY INDEX ROWID | PAY_USER_ROWS_F | 7 | 231 | 1 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | PAY_USER_ROWS_F_FK1 | 8 | | 0 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | PAY_USER_COLUMN_INSTANCES_N1 | 1 | | 1 (0)| 00:00:01 |
|* 12 | TABLE ACCESS BY INDEX ROWID | PAY_USER_COLUMN_INSTANCES_F | 1 | 43 | 2 (0)| 00:00:01 |
—————————————————————————————————————-

When the plan was using Nested Loops, it was hitting the table PAY_USER_COLUMN_INSTANCES_F via index and not performing Full Scan, so was not hitting the bad data, so no error

In nutshell, there can be special cases in production where Nested loops based  execution plan can hide bad data in tables, which otherwise needs to be corrected.

As a solution to this, developer added filter to avoid hitting the bad data

Missing parenthesis – SQL Tuning


Encountered the issue of slow performance of SQL coming due to missing brackets in old/legacy code. This is second time hitting the same kind of issue.

SQL Execution plans are going bad after performing the upgrade.
Database is upgraded to 11.2.0.4 from 11.2.0.3

SQL with missing parethesis

SELECT APPROVAL_HISTORY_ID,TRX_ID,SITE_ID,TRX_START_DATE,TRX_END_DATE,
 APPROVER_ID,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN,
 ORG_ID,ITERATION,RESPONSE,APPROVER_NAME,CREATION_DATE,AMOUNT_APPROVED,
 APPROVER_COMMENTS
 FROM
 apps.XX_INV_APRVL_HIST_V
 WHERE
 trx_id=9887773 AND
 SITE_ID=44178
 OR
 TRX_START_DATE='01-SEP-16' OR TRX_END_DATE='05-SEP-16'
 and 1=1 order by
 LAST_UPDATE_DATE desc;

—————————————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————————————–
| 0 | SELECT STATEMENT | | | | 173K(100)| |
| 1 | SORT ORDER BY | | 1652 | 680K| 173K (1)| 00:34:40 |
| 2 | VIEW | XX_INV_APRVL_HIST_V | 1652 | 680K| 173K (1)| 00:34:40 |
| 3 | UNION-ALL | | | | | |
|* 4 | FILTER | | | | | |
|* 5 | TABLE ACCESS FULL | XX_ELECT_INV_APRVL_HIST | 6507 | 698K| 54594 (2)| 00:10:56 |
| 6 | SORT AGGREGATE | | 1 | 33 | | |
|* 7 | TABLE ACCESS BY INDEX ROWID| XX_ELECT_INV_APRVL_HIST | 1 | 33 | 7 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | XX_ELECT_INV_APRVL_HIST_N1 | 3 | | 3 (0)| 00:00:01 |
|* 9 | TABLE ACCESS FULL | XX_ELECT_INV_APRVL_HIST | 813 | 84552 | 54156 (1)| 00:10:50 |
|* 10 | TABLE ACCESS FULL | XX_ELECT_INV_APRVL_HIST | 813 | 84552 | 54156 (1)| 00:10:50 |
—————————————————————————————————————–

Changed the code by adding parenthesis
SELECT APPROVAL_HISTORY_ID,TRX_ID,SITE_ID,TRX_START_DATE,TRX_END_DATE,
APPROVER_ID,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN,
ORG_ID,ITERATION,RESPONSE,APPROVER_NAME,CREATION_DATE,AMOUNT_APPROVED,
APPROVER_COMMENTS
FROM
apps.XX_INV_APRVL_HIST_V
WHERE
trx_id=9887773 AND
SITE_ID=44178
AND
(
TRX_START_DATE=’01-SEP-16′ OR TRX_END_DATE=’05-SEP-16′
)
and 1=1 order by
LAST_UPDATE_DATE desc;

——————————————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————————————————-
| 0 | SELECT STATEMENT | | 3 | 1266 | 20 (10)| 00:00:01 |
| 1 | SORT ORDER BY | | 3 | 1266 | 20 (10)| 00:00:01 |
| 2 | VIEW | XX_INV_APRVL_HIST_V | 3 | 1266 | 19 (6)| 00:00:01 |
| 3 | UNION-ALL | | | | | |
|* 4 | FILTER | | | | | |
| 5 | HASH GROUP BY | | 1 | 143 | 11 (10)| 00:00:01 |
| 6 | NESTED LOOPS | | 1 | 143 | 10 (0)| 00:00:01 |
| 7 | NESTED LOOPS | | 3 | 143 | 10 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID| XX_ELECT_INV_APRVL_HIST | 1 | 110 | 4 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | XX_ELECT_INV_APRVL_HIST_N1 | 1 | | 3 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | XX_ELECT_INV_APRVL_HIST_N1 | 3 | | 2 (0)| 00:00:01 |
|* 11 | TABLE ACCESS BY INDEX ROWID | XX_ELECT_INV_APRVL_HIST | 1 | 33 | 6 (0)| 00:00:01 |
|* 12 | TABLE ACCESS BY INDEX ROWID | XX_ELECT_INV_APRVL_HIST | 1 | 104 | 4 (0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | XX_ELECT_INV_APRVL_HIST_N1 | 1 | | 3 (0)| 00:00:01 |
|* 14 | TABLE ACCESS BY INDEX ROWID | XX_ELECT_INV_APRVL_HIST | 1 | 104 | 4 (0)| 00:00:01 |
|* 15 | INDEX RANGE SCAN | XX_ELECT_INV_APRVL_HIST_N1 | 1 | | 3 (0)| 00:00:01 |
——————————————————————————————————————-

It seems Oracle optimizer  is taking care of brackets more in latest version as compared to previous versions 🙂

So , old codes needs to be revisited time and again to have better performance

 

SQL Server Profiling


1. Start the profiling with following settings in profiler

Start with custom profiling settings ( it will start with all columns and events)

Events Not to be included 

  • Brokers
  • CLR
  • Database
  • Depreciation
  • Locks (particularly release and accuired locks)
  • Errors and warnings (moslty)
  • OLEDB
  • Objects
  • Scans( particularly)
  • Security Audit
  • Server
  • Sessions
  • Transactions ( particularly start stop transaction)

Events to be included

  • Cursors (mostly)
  • Errors and Warnings ( Attention, Exchange Spill event, Blocked process report)
  • Performance (mostly)
  • Sessions( existing connections – for testing)
  • Stored procedures
  • TSQL

Columns( to be selected)

  • applicationname
  • clientprocessid
  • databasename
  • eventsequnce
  • spid
  • start time
  • end time
  • transaction id
  • xact sequence
  • reads
  • writes
  • rowcounts

2. Capture the trace and save the trace information

(a)  Save the trace to some trace file and then read the file from the command prompt by using the function fn_trace_gettable

(b) Save results into table

Trace related queries

3. Run the below queries to get the main problem queries

Note:-  These queries can give result of same query coming multiple times( row 1 and 2 can point to same query). Still it gives us clear idea what are top time consuming sqls with timings,

When trace file is used to store trace information

Run the below query in SQL Studio session

select textdata, duration, reads, writes, rowcounts from fn_trace_gettable(C:\trace\badsqls.trc’,DEFAULT) trace order by duration desc;

When Table is used to store trace information

In SQL Server, at a given point of multiple user sessions can be there.

a- This query is to find the timings on per connection basis. 

select          applicationname ,clientprocessid,
sum(duration)/(1000)”Total time in millisecs”
from
dbo.trace_table  — Give the table name as mentioned in step 2
where
— this is to leave the already running sqls before start of trace , if any coming
STARTTIME >=(select STARTTIME from dbo.trace_table Where ROWNUMBER=1)
group by   clientprocessid,applicationname
order by 3 desc

b- This query is to find timings of all sqls coming for particular connection

select
starttime,endtime,applicationname ,clientprocessid,
duration/(1000)”Total time in millisecs”,
transactionid,xactsequence,
objectname , textdata
from
dbo.trace_table
where
clientprocessid in (31680)  — captured above
AND STARTTIME >=(select STARTTIME from dbo.trace_table Where ROWNUMBER=1)
order by duration desc — to get the sqls in order of descending order of elapsed time

Disclaimer:- Due to Oracle background, idea came  that we should have something similar to trace/AWR.