how a developer can troubleshoot a sql performing poorly on a production db

How a Developer Can Troubleshoot a SQL Performing

Poorly on a Production DBCarlos Sierra

Enkitec (c) 2014 2

• Oracle Performance and SQL Tuning• Consultant/Developer/DBA• eDB360 and eAdam• SQLT and SQLHC• Exadata

Carlos Sierra

04/18/2023 Enkitec © 3

Motivation• Developers might need to do SQL Tuning• Diagnostics Collection is key for SQL Tuning• Developers have limited access to DB server– OEM is usually off limits

• Developers need reliable SQL Tuning tools• Developers need to learn about these tools

04/18/2023 Enkitec © 4

Common SQL Troubleshooting Steps

1. Implement proper code instrumentation2. Find application SQL performing poorly3. Gather diagnostics on SQL of concern4. Determine root cause of poor performance5. Fix root cause

04/18/2023 Enkitec © 5

Code Instrumentation• Data elements available– Service– Client Identifier– Client Info– Module– Action

• Data type and size: VARCHAR2(64)

04/18/2023 Enkitec © 6

Query Session Values

04/18/2023 Enkitec © 7

Service (1)• Database logical name(s) set per instance• GV$SYSTEM_PARAMETER2 “service_names”

04/18/2023 Enkitec © 8

Service (2)• Service SYS$BACKGROUND– Background Processes

• Service SYS$USERS– Default when session is not associated to Service

• Package DBMS_SERVICE– Start, stop, create, delete, modify, disconnect

04/18/2023 Enkitec © 9

PL/SQL Instrumentation APIs• DBMS_SESSION.SET_IDENTIFIER– Sets CLIENT_IDENTIFIER

• DBMS_APPLICATION_INFO.SET_CLIENT_INFO– Sets CLIENT_INFO

• DBMS_APPLICATION_INFO.SET_MODULE– Sets MODULE and ACTION

04/18/2023 Enkitec © 10

API usage Example for PL/SQL• Set before application code• Reset after application code and error

handling

04/18/2023 Enkitec © 11

Query V$ to finding SQL• GV$SESSION– Session Status– Service, Module, Action, Client, User

• GV$SQL– SQL Text– Elapsed Time

04/18/2023 Enkitec © 12

Active SQL Statements

04/18/2023 Enkitec © 13

Some “safe” Diagnostics Tools• SQL Trace and TKPROF• Execution Plan and DBMS_XPLAN• planx.sql, sqlmon.sql and sqlash.sql• snapper.sql• SQLHC and SQLTXPLAIN

04/18/2023 Enkitec © 14

Tracing Methods• ALTER SESSION– SQL_TRACE parameter– Event 10046

• DBMS_SESSION• DBMS_MONITOR (preferred)• ORADEBUG– Event 10046

04/18/2023 Enkitec © 15

SQL_TRACE Syntax• ALTER SESSION SET SQL_TRACE = TRUE;• ALTER SESSION SET SQL_TRACE = FALSE;• Own Session

04/18/2023 Enkitec © 16

Event 10046 Syntax• ALTER SESSION SET EVENTS '10046 TRACE

NAME CONTEXT FOREVER, LEVEL N';• ALTER SESSION SET EVENTS '10046 TRACE

NAME CONTEXT OFF';• Own Session

04/18/2023 Enkitec © 17

Event 10046 Levels• 1: Performance metrics and Execution Plan metrics

– 10g: Execution Plan metrics for all executions– 11g: Execution Plan metrics for 1st execution

• 4: Binds• 8: Waits• 16: Execution Plan metrics for each execution (11.1+)• 64: Execution Plan metrics for 1st then each with DB

Time > 1min (11.2.0.2+)

04/18/2023 Enkitec © 18

Event 10046 Level Examples• Execution Plan metrics + Binds + Waits– Level 4 + 8 = 12 • 11.1+: Execution Plan metrics for 1st execution • 10g: Execution Plan metrics for all executions

• Binds + Waits + 1st Exec + >1m Execs– Level 4 + 8 + 64 = 76• 11.2.0.2+

04/18/2023 Enkitec © 19

DBMS_SESSION APIs• SESSION_TRACE_ENABLE– waits (TRUE|FALSE)– binds (FALSE|TRUE)– plan_stat (FIRST_EXECUTION|ALL_EXECUTIONS)

• SESSION_TRACE_DISABLE• Own Session

04/18/2023 Enkitec © 20

DBMS_MONITOR APIs• Enable SQL Trace– SERV_MOD_ACT_TRACE_ENABLE– CLIENT_ID_TRACE_ENABLE– SESSION_TRACE_ENABLE

• ENABLE and DISABLE versions• TRACE and STAT versions

04/18/2023 Enkitec © 21

SERV_MOD_ACT_TRACE_ENABLE• service_name (required)• module_name (optional)• action_name (optional)• waits (TRUE|FALSE)• binds (FALSE|TRUE)• plan_stat (FIRST_EXECUTION|

ALL_EXECUTIONS)

04/18/2023 Enkitec © 22

CLIENT_ID_TRACE_ENABLE• client_id (required)• waits (TRUE|FALSE)• binds (FALSE|TRUE)• plan_stat – FIRST_EXECUTION|ALL_EXECUTIONS

04/18/2023 Enkitec © 23

SESSION_TRACE_ENABLE• session_id (if null then own else sid)• serial_num (if null then use sid only)• waits (TRUE|FALSE)• binds (FALSE|TRUE)• plan_stat – FIRST_EXECUTION|ALL_EXECUTIONS

04/18/2023 Enkitec © 24

Some related Views• Active Tracing– DBA_ENABLED_TRACES

• Collected Statistics– V$SERV_MOD_ACT_STATS– V$CLIENT_STATS– V$SESSTAT

04/18/2023 Enkitec © 25

Trace Location• 11g– V$DIAG_INFO– Name = 'Diag Trace'

• 10g– V$PARAMETER2– Name = 'user_dump_dest'

04/18/2023 Enkitec © 26

TKPROF Syntax• tkprof tracefile outputfile [sort=option]• sort=prsela fchela exeela

04/18/2023 Enkitec © 27

TKPROF Sample

04/18/2023 Enkitec © 28

TKPROF Execution Plan Sample

04/18/2023 Enkitec © 29

DBMS_XPLAN Table Functions• DISPLAY• DISPLAY_AWR• DISPLAY_CURSOR• DISPLAY_PLAN• DISPLAY_SQL_PLAN_BASELINE• DISPLAY_SQLSET

frequently used

also used

04/18/2023 Enkitec © 30

DBMS_XPLAN.DISPLAY_CURSOR• sql_id (default: last executed session cursor)• cursor_child_no (default: 0)• format (default: typical)– allstats (io and mem stats for all executions)– allstats last (ditto for last execution)– advanced (includes outline) both undocumented

04/18/2023 Enkitec © 31

advanced

sql_idchild number

SQL text

Execution Plan

04/18/2023 Enkitec © 32

Query Blocks

Outline

Bind Variables

04/18/2023 Enkitec © 33

Predicates

Column Projection

04/18/2023 Enkitec © 34

allstats last

actual rows

04/18/2023 Enkitec © 35

planx.sql• Installs nothing• 10g and 11g• Parameters– Oracle Diagnostics Pack License? Y | N– SQL_ID

• Free

04/18/2023 Enkitec © 36

planx.sql Execution Plan• DBMS_XPLAN.DISPLAY– GV$SQL_PLAN_STATISTICS_ALL– ADVANCED ALLSTATS LAST

• DBMS_XPLAN.DISPLAY_AWR– ADVANCED

04/18/2023 Enkitec © 37

planx.sql Views (1)• GV$SQLSTATS (extended retention)• GV$SQLSTATS_PLAN_HASH• GV$SQL• GV$ACTIVE_SESSION_HISTORY

04/18/2023 Enkitec © 38

planx.sql Views (2)• DBA_HIST_SQLSTAT• DBA_HIST_SQL_PLAN• DBA_HIST_ACTIVE_SESS_HISTORY

04/18/2023 Enkitec © 39

planx.sql Views (3)• DBA_TABLES• DBA_INDEXES• DBA_TAB_COLS• DBA_IND_COLS

04/18/2023 Enkitec © 40

04/18/2023 Enkitec © 41

04/18/2023 Enkitec © 42

04/18/2023 Enkitec © 44

sqlmon.sql• Installs nothing• 10g and 11g• Parameters– Oracle Tuning Pack (required) License? Y | N– SQL_ID

• Free

04/18/2023 Enkitec © 49

sqlash.sql• Installs nothing• 10g and 11g• Parameters– Oracle Diagnostics Pack (required) License? Y | N– SQL_ID

• Free

Enkitec © 2014 52

Snapper• Session Centric• Installs nothing• 10g and 11g• 4 Parameters• Does not require Diagnostics or Tuning Pack• Free

04/18/2023 Enkitec © 53

Snapper Syntax• Scope (‘all’ includes ASH and Stats) • seconds_in_snap (30 is common)• snap_count (between 1 and 5 are common)• sid (session id)• Execution Sample– @snapper.sql all 30 1 50

04/18/2023 Enkitec © 55

SQLHC 1366133.1• Installs nothing• 10g and 11g• Parameters– Oracle Pack License? T | D | N– SQL_ID

• Free (requires MOS account)

04/18/2023 Enkitec © 57

SQLTXPLAIN (SQLT) 215187.1• Installs two schemas and many objects• 10g and 11g• Methods– SQLT XTRACT (inputs SQL_ID)– SQLT XECUTE (inputs SQL Text)

• Free (requires MOS account)

04/18/2023 Enkitec © 59

Summary1. Implement proper code instrumentation2. Find application SQL performing poorly3. Gather diagnostics on SQL of concern4. Determine root cause of poor performance5. Fix root cause

04/18/2023 Enkitec © 60

References• Oracle Documentation• http://blog.tanelpoder.com/files/scripts/

snapper.sql• http://carlos-sierra.net/ (look for cscripts)• SQL Health-Check (SQLHC): MOS 1366133.1• SQLXPLAIN (SQLT): MOS 215187.1

Enkitec (c) 2014 61

Contact Information• [email protected]• carlos-sierra.net• @csierra_usa

how a developer can troubleshoot a sql performing poorly on a production db

Software