Does performance of a SQL change when a new column is added to the existing script ?

 

 

Recently, I have noticed that there are numerous developers who struggle to comprehend how SQL functions when even the slightest modification is made to it. 

I collaborated with a Developer who provided me with a SQL script consisting of over 1800 lines and multiple joins. My team of DBAs and I dedicated nearly 3 days to fine-tune the query, resulting in a significant improvement from 2 minutes to just 20 milliseconds per execution. 

However, a week later, the same developer returned claiming that the SQL performance had deteriorated once again. Upon testing it with the optimized code I had, the results remained consistent. Nevertheless, the developer insisted that it was not functioning as expected. 

Upon further investigation during a call, we discovered that they were using a slightly altered query, although it appeared nearly identical. 

When questioned, the Developer mentioned, "We simply added 3 columns to the script after it was optimized. Why would that make a difference? They are additional columns from the same table," expressing confusion. 

 I had to clarify that even the smallest alteration, whether it involves adding a new column from existing tables or modifying a condition, will impact the entire SQL plan. 

 Although it was challenging for the developer to accept, it is crucial for them to understand this fundamental concept.

An Insight about TKPROF

What is TKPROF?

TKProf is an Oracle database utility used to format SQL Trace output into human readable format. The TKProf executable is located in the $ORACLE HOME/bin directory.

TKPROF output can be generated from a raw SQL Trace. It formats and summarizes the diagnostic information from the raw SQL Trace.TKPROF allows you to analyse a trace file in easy way to determine where time is being spent and what query plans are being used on SQL statements.

What does a TKPROF output file contains?

TKPROF contains :

SQL ID along with SQL Text that were executed for the traced session.

• Timing information for the execution count, CPU Time, Elapsed Time, Physical Reads, Logical Reads and the total number of rows returned.
• Wait Information such as the times waited, maximum wait and total waited for each database wait event, both for each SQL and for the whole period of the trace.
• Execution Plan -  The execution plan will also contain the actual tables accessed if the SQL uses views or synonyms.

Syntax of TKPROF:

tkprof tracefile outputfile [explain= ] [table= ] [print= ] [insert= ] [sys= ] [sort= ]

explain=user/password    Connect to ORACLE and issue EXPLAIN PLAN.

table=schema.tablename   Use 'schema.tablename' with 'explain=' option.

print=integer    List only the first 'integer' SQL statements.

insert=filename  List SQL statements and data inside INSERT statements.

sys=no           TKPROF does not list SQL statements run as user SYS.

sort=option      Set of zero or more of the sort options

Out of all available options, most widely used options for performance tuning are exeela, fchela, prsela.

How to enable trace for a database session

This article lists the commands required to trace SQL statements executed by a user or an application.

Tracing a SQL session

Start session trace

To start a SQL trace for the current session, execute:

ALTER SESSION SET sql_trace = true;

You can also add an identifier to the trace file name for later identification:

ALTER SESSION SET sql_trace = true;
ALTER SESSION SET tracefile_identifier = sampletrace;

Stop session trace

To stop SQL tracing for the current session, execute:

ALTER SESSION SET sql_trace = false;

Tracing other user's sessions

We can use DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION to trace problematic database sessions.

In order to enable trace for other session, first thing we need is to identify their session sid and serial#.

Enable tracing for your selected process:

ALTER SYSTEM SET timed_statistics = true;
execute dbms_system.set_sql_trace_in_session(<sid>, <serial#>, true);

Now ask the user to reproduce the problem

Disable tracing for your selected process:

execute dbms_system.set_sql_trace_in_session(<sid>, <serial#>, false);

Location of Trace File:

We can identify the trace file for a specific session using the V$SESSION and V$PROCESS views.

SELECT p.tracefile
FROM   v$session s
       JOIN v$process p ON s.paddr = p.addr
WHERE  s.sid = <sid>;

Script to verify UNDO usage of active database sessions

This post provides with a script to verify the UNDO usage by active database sessions.

select se.saddr,se.inst_id,se.sid,se.module, sum(tr.used_ublk*8192/1024/1024)"Used(MB)"
from gv$session  se, gV$transaction   tr
where tr.ses_addr = se.saddr and se.inst_id=tr.inst_id
group by se.saddr,se.module,se.inst_id,se.sid
/

 

The Sample Output of the script be like:

 

How to check if a patch is applied in Oracle Applications without logging into the server / database / application?

If we want to check the patches applied and their date without logging into the server, not even connect to database nor login into application, the process is very simple and straight forward.

Just open the Oracle Applications login page for the instance which you desire to check.

On the login page, at Extreme left bottom, we will notice a link "About this page".

Click on "About this page" as shown in above picture.

We will not be required to enter any username/password. It will simply take us to the below page.

select the "Patches" tab as shown in above picture.

Finally query the patch you wanted to search for.

NID-00600: Internal Error - [30] [3113] [0] [0] and ORA-00600: internal error code, arguments: [control file RSBY_RDI/IC mismatch], [3], []

Issue:

Below errors are encountered when a renaming a standby database to use as cloned instance.

NID-00600: Internal Error - [30] [3113] [0] [0]
ORA-00600: internal error code, arguments: [control file RSBY_RDI/IC mismatch], [3], []


Symptoms:

Trying to convert standby to clone instance.

Cause:

Controlfile was created for standby and same controlfile is used during rename.

Fix:

Take a trace from the database.

Rename existing controlfiles.

Create fresh controlfiles from the trace.

Open the database with resetlogs.

 

ORA-19566: exceeded limit of 0 corrupt blocks for file "/u01/db/oracle/db/apps_st/data/system09.dbf" while taking rman backup for fresh install of 12.1.1.

Issue:

After a fresh Install of 12.1.1, trying to take RMAN backup which failed with below errors.

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 05/12/2013 11:49:40
ORA-19566: exceeded limit of 0 corrupt blocks for file /u01/db/oracle/db/apps_st/data/system09.dbf


Fix:

alter database datafile "/u01/db/oracle/db/apps_st/data/system09.dbf" resize 1000m;

dbv file=/u01/db/oracle/db/apps_st/data/system09.dbf

Re-initiate the backup.

Procedure to take backup and recover OCR and Voting Disks in RAC

To take backup of OCR Disk:

Execute below command to take backup of OCR Disks as root user:

10g:

# ocrconfig -export <OCR export_filename> -s online

11g:

# ocrconfig -manualbackup

To Take backup Voting Disk:

Pre-11g:

Execute below command as root user:

# dd if=voting_disk_name of=backup_file_name

11g:

For 11.2+, it is no longer required to back up the voting disk. The voting disk data is automatically
backed up in OCR as part of any configuration change. The voting disk files are backed up
automatically by Oracle Clusterware if the contents of the files have changed in the following ways:

Configuration parameters, for example misscount, have been added or modified
After performing voting disk add or delete operations
The voting disk contents are restored from a backup automatically when a new voting disk is added or
replaced.

To List the backup:

# ocrconfig -showbackup

To Recover using backup:

# ocrconfig -import <OCR export_filename>

To check the health of OCR:

# ocrcheck

Collect Shipment and Booking History failed with ORA-24247: network access denied by access control list (ACL) error

Issue:

"Collect Shipment and Booking History" in ASCP completed with below errors in the log file:

ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1722
ORA-24247: network access denied by access control list (ACL)

Cause:

Demantra schema in ASCP does not have "connect" grant on the network ACL.

Verification:


SYS-UAT> select * from dba_network_acls;

HOST  LOWER_PORT UPPER_PORT ACL                        ACLID
----- ---------- ---------- -------------------------- -------------------------
*                           /sys/acls/OracleEBS.xml    71572A92FE0405D0B59100B2A

SYS-UAT> SELECT DECODE(DBMS_NETWORK_ACL_ADMIN.check_privilege('OracleEBS.xml', 'MSDEM', 'connect'),1, 'GRANTED', 0, 'DENIED', NULL) privilege FROM dual;

PRIVILE

-------

Solution:

Grant connect on ACL to demantra schema (MSDEM in this case)

BEGIN

DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl=>'/sys/acls/OracleEBS.xml',

   principal => 'MSDEM',

   is_grant  => true,

   privilege => 'connect');

COMMIT;

END;

/

Verfication:

SYS-UAT> SELECT DECODE(DBMS_NETWORK_ACL_ADMIN.check_privilege('OracleEBS.xml', 'MSDEM', 'connect'),1, 'GRANTED', 0, 'DENIED', NULL) privilege FROM dual;

PRIVILE

-------

GRANTED

Now, rerun the program and it completes successfully without any issues.

How to find whether CPU patch is applied to RDBMS Oracle Home

Problem:

I want to find out what all CPU patches/Patch set updates have been applied to my RDBMS Oracle Home.

Answer:

Use the below SQL command to see all the PSUs/CPUs applied to your database.

select * from dba_registry_history ;

Alternately, we can check successful CPU patch installation logs under $ORACLE_HOME/cfgtoollogs/catbundle directory.