Using GDB to break on kksParseCursor and print submitted SQL statements
The following steps worked on an Oracle 23c free installation in a docker environment.
First, I needed to determine the process ID of the Oracle process which handles submitted SQL statements:
select
prc.spid
from
v$session ses join
v$process prc on ses.paddr = prc.addr
where
ses.sid = sys_context('userenv', 'sid');
On the server, I then started
gdb with the id returned by the previous query:
$ gdb -p 467
…
Missing separate debuginfos, use: yum debuginfo-install oracle-database-free-23c-1.0-1.x86_64
(gdb)
gdb is now started and attached to process 467. I can now set the breakpoint on kksParseCursor. (TODO: should I or can I alternatively set a breakpoint on kksParseChildCursor?)
(gdb) b kksParseCursor
Breakpoint 1 at 0x1804da70
I also execute the following command so that SIGUSR2 does not distract me by jumping to function sspuser):
(gdb) handle SIGUSR2 nostop noprint
Signal Stop Print Pass to program Description
SIGUSR2 No No Yes User defined signal 2
Continue the execution:
(gdb) c
Continuing.
In the client, I execute an SQL statement:
select level from dual connect by level <= 100;
The breakpoint is hit and the processing of the SQL statement stops (which explains why the statement seems to hang):
Breakpoint 1, 0x000000001804da70 in kksParseCursor ()
The pointer to the text of the SQL statement is passed to the function in register rbx, so I can show the text in gdb with:
(gdb) x/s $rbx
0x7ffee20f1d60: "select level from dual connect by level <= 100"
I continue the execution so that the statement returns to the user:
(gdb) c
Filenames and functions
select
count(*),
case when nvl(lag(source_file) over (order by source_file, function_name), 'n/a') <> source_file then source_file end source_file_,
function_name
from (
select
regexp_replace(file_loc, '@.*', '') source_file,
function function_name
from
x$trace
UNION ALL
select
file_name,
function_name
from
x$dbgtfview
where
function_name is not null
UNION ALL
select distinct
ksbsrvdfile,
null
from
x$ksbsrvdt
)
group by
source_file,
function_name
order by
source_file,
function_name
;
-- apa.c apadrv
-- dbgea.c dbgea_dispatch_pack_int
-- dbgea_exec_
-- dbger.c dbgerRunActions
-- dbgex.c dbgexPhaseII
-- dbgexProcessError
-- dbgrim.c dbgrimbid_begin_incdump
-- dbgrmbl.c dbgrmblgmp_get_many_pages
-- dbgrmblgp_get_page
-- dbgrmblwp_write_page
-- dbgrmdm.c dbgrmdmdr_delete_record
-- dbgrmdmir_insert_record
-- dbgrmdmur_update_record
-- dbgrme.c dbgrme_bind
-- dbgrmqm.c dbgrmqmif_internal_fetch
-- dbgrmqmp_predicate_full
-- dbgrmqmqpk_query_pick_key
-- dbgrmqmsk_scan_key
-- dbked.c dbkedDefDump
-- dbkedSqlDmp
-- dbkedVktmDmp
-- dbkt.c dbktWriteTimestampWCdbInfo
-- dbktb.c dbktbDefaultBucketFree
-- kcb.c kcbtema
-- kcc.c kccwbp
-- kcfis.c kcfis_automem_workarea_cleanup
-- kcrp.c kcrpap
-- kcrpclaim
-- keomg.c keomgAllocDescEntry
-- keomgMakeCS
-- keomgReclaimCb
-- keomgReclaimDrv
-- kesqs.c kesqsMakeSelfBinds
-- kests.c kestsDumpMsg
-- keswx.c keswxAddPlanMonitoring
-- keswxAllocatePlanEntry
-- keswxCurEndPlanMonitoringCb
-- keswxCurPushMonitoring
-- keswxCurPushPlanMonitoring
-- keswxCurSetupPublish
-- keswxCurStartPlanMonitoring
-- keswxWriteEndInfoToStream
-- keswxWriteGlobalInfoToStream
-- kfgb.c kfgbDGResDbSPFILE
-- kfn.c kfnPrepareASM
-- kgds.c kgdsbsdmp
-- kgdsdst
-- kghsc.c kghscCopyStream
-- kghscDump
-- kgl.c kglDumpAddField
-- kglDumpOpenField
-- kjci.c kjci_action
-- kjci_enqueue_reply
-- kjci_exec_xctx
-- kjci_finish_int
-- kjci_getnext_xctx
-- kjcipctxdmi
-- kjcipctxinit
-- kjzd.c kjzd_masterloop
-- kjzdclstrc
-- kjzdcrsh
-- kjzddmp
-- kjzdlogdreq
-- kjzdmdrq
-- kjzdshn
-- kjzduptcctx
-- kjzg.c kjzgtrdump
-- kke.c kke1ro
-- kkebihsl
-- kkecdn
-- kkehsl_Int
-- kkejcd
-- kkec.c kkecComputeAPDop
-- kkecst.c kkeCostToTime
-- kkePrintIdxCostInfo
-- kkeSortCosts
-- kkecstDumpPredArrCost
-- kkecstDumpSingleLogCost
-- kkeidc
-- kkejnc
-- kkessc
-- kketac
-- kkest.c kkestGbyAdjmtFactor
-- kkestGetMCSel
-- kkestGetRelColGroup
-- kkestRCHistgrm
-- kko.c kkoCBRidEligible
-- kkoFroAddXplAnnotations
-- kkoQbcCheckOrExpansion
-- kkoipt
-- kkoiqb
-- kkooqb
-- kkoqbc
-- kkoap.c kkotap
-- kkobm.c kkobmp
-- kkodp.c kkodpComputeJoinInfpt
-- kkojm.c kkojnp
-- kkooic
-- kkojo.c kkoSaveJoinOrder
-- kkoop.c kkogcp
-- kkogenpr
-- kkopje.c kkopjedrv
-- kkopm.c kkopmDisallowed
-- kkopmPlanBaselineCheck1
-- kkopq.c kkoBloomFilter
-- kkoDMopt
-- kkopqCanUseReplication
-- kkopqComputeBloomNdv
-- kkopqGetGlobalManualDOP
-- kkopqInitDopComputation
-- kkopqIsDopComputedOrParallelHinted
-- kkopqIsSerialJoin
-- kkopqSetForceParallelProperties
-- kkopqSingleJoinBloomNdv
-- kkopqUseAffinity
-- kkost.c kkoaccsqf
-- kkotrc.c kkoDumpHeapSize
-- kkoDumpPredicate
-- kkoStateDump
-- kkoTrcBinds
-- kkoTrcPrologue
-- kkoTrcSystemStats
-- kkqbj.c kkqbjCheckValidity
-- kkqcj.c kkqcjdrv
-- kkqct.c kkqctChkCBQTvalid
-- kkqctCleanupHeap
-- kkqctccton
-- kkqctcvct
-- kkqctdrvCVM
-- kkqctdrvIT
-- kkqctdrvSU
-- kkqctdrvTD
-- kkqe.c kkqedrv
-- kkqfpp.c kkqfppDrvDescendents
-- kkqfppPreRewriteDescendants
-- kkqfppPsh
-- kkqgae.c kkqgaeElimGBvalid
-- kkqgaedrv
-- kkqgbp.c kkqgbpCheckValidity
-- kkqgbpCheckValidityDP
-- kkqgbpTravChkTran
-- kkqje.c kkqjeDriver
-- kkqjeHasConstraintJoinPred
-- kkqjf.c kkqjfChkBasicValidity
-- kkqjpd.c kkqjpdcbvnuv
-- kkqjpdctr
-- kkqjpdcvvpd
-- kkqjpddrv
-- kkqm.c kkqmcvpm
-- kkqmdrv
-- kkqobe.c kkqobedrv
-- kkqoje.c kkqojeCOIWhere
-- kkqojedrv
-- kkqore.c kkqoreCheckQBValidity
-- kkqr.c kkqrdrv
-- kkqs.c kkqdrv
-- kkqsqe.c kkqsqedrv
-- kkqst.c kkqstApproxTransDrv
-- kkqstIsReduceGrByValid
-- kkqstSQTIsValid
-- kkqstSQTdrv
-- kkqstcstdrv
-- kkqstar.c kkqstarChkValidity
-- kkqte.c kkqteCheckValidity
-- kkqu.c kkqucvusq
-- kkqudrv
-- kkquunsq
-- kkqvm.c kkqvmCheckValidSVM
-- kkqvmTrMrg
-- kkqvmValidCVM
-- kkqvmrojok
-- kkqvt.c kkqvtCheckGeneralValidity
-- kks1.c kksDumpParentCursor
-- kkscs.c kkscsDiagChildNodes
-- kml.c kmlini
-- kra.c kraalert
-- kradreserved
-- kradusage
-- krahalv
-- kraralv
-- kraq.c kraqgdbg
-- kraqopn
-- kraqtrace
-- krbi.c krbitrc
-- krbiwtrc
-- krbm.c krbmabac
-- krc.c krccall
-- krcfilesync
-- krctcp
-- ksb.c ksb_exec_action
-- ksb_req_flags_process
-- ksb_spawn_cleanup
-- ksbcif_int
-- ksbmsg
-- ksbs1p_int
-- ksbsrv_postcb_invoke
-- ksbsrv_precb_invoke
-- ksbsrvn_opt
-- ksd.c ksdtrc_new
-- ksdhng.c ksdhng_chain_dump
-- ksdhng_chain_sig_dump
-- ksdhng_diag_proc_int
-- kse.c ksedst
-- ksipc.c ksipc_set_service_env
-- ksl2.c kslws_auto_close
-- kslwtbctx
-- kslwtectx
-- ksm.c ksmdmpg
-- ksmmga.c ksm_mga_attach
-- ksm_mga_create
-- ksm_mga_delete
-- ksm_mga_detach
-- ksm_mga_heap_alloc_cbk
-- ksm_mga_pctx_seg_sync
-- ksm_mga_pctx_sync
-- ksm_mga_pseg_cbk_attach
-- ksm_mga_pseg_cbk_detach
-- ksm_mga_sctx_alloc
-- ksm_mga_sctx_broadcast_refresh
-- ksm_mga_sctx_free
-- ksm_mga_sctx_heap_assign
-- ksm_mga_sctx_heap_free
-- ksm_mga_sctx_heap_init
-- ksm_mga_sctx_seg_alloc
-- ksm_mga_sctx_seg_free
-- ksm_mga_sctx_seg_meta_alloc
-- ksm_mga_sctx_seg_meta_free
-- ksm_mga_sctx_seg_sync
-- ksm_mga_seg_create_int
-- ksm_mga_sync_int
-- kso.c kso_sched_delay_measure
-- kso_spawn_diag_end
-- ksonfy
-- ksoreq_free_int
-- ksoreq_queue_attr_check
-- ksoreq_submit_int
-- ksosp_fork
-- ksq.c ksqdiddmp
-- ksqdmc
-- ksqdmp
-- kss.c kssdch_int
-- kst.c kstipg
-- kstpsodel
-- ktsmg.c ktsmgCursorScan
-- ktu.c ktuaex1r
-- ktur.c kturRecoverUndoSegment
-- ktusm.c ktusmasp
-- kwsbg.c kwsbgshms
-- kxfp.c kxfpiqdmp
-- kxfpqddmp
-- kxfpqialo
-- kxs.c kxsDump1
-- kxsDumpCursor
-- kxsDumpCursors
-- kxsDumptoid
-- kxsbnddmp
-- kxsbndinf
-- kxsccobc
-- kxsdcbc
-- kxst.c kxstTraceBinds
-- kxstTraceClose
-- kxstTraceWait
-- kxstcol
-- kxstcol2
-- kxstper
-- kxstsql
-- kzssutl.c kzssutlCreateDirectoryRecursive
-- kzssutlGetDefaultXdbWRL
-- kzssutlGetSqlnetWRL
-- kzssutlGetXdbWRL
-- kzxu.c kzxuDumpPrincipalState
-- opiexe.c opiexe
-- opiprs.c opiParse
-- opiSem
-- opitca.c opitca
-- qkadrv.c qkaComputePlanSignature
-- qkajoi.c qkajoi
-- qkn.c qknDumpRwo
-- qkna.c qknAttemptCBRid
-- qksan.c qksanAnalyzeSql
-- qksanAnalyzeSqlConCbk
-- qksanAvgExecStats
-- qksanBindSql
-- qksanPostAnalyzeDump
-- qksbg.c qksbgCreateCursorEnv
-- qksbgCreateSessionEnv
-- qksbgDumpEnv
-- qksce.c qksceDumpEnv
-- qksctx.c qksctxDump
-- qksht.c qkshtDumpHints
-- qksop.c qksopDumpPredicate
-- qksqb.c qksqbDumpIntoStreamOrTrace
-- qksqbDumpRegistryGraph
-- qksqbDumpSig
-- qksqbDumpText
-- qksqbRegistryCompactToXML
-- qkstrc.c qkstrcInitialize
-- qmtmrc.c qmtmrc_sga_init
-- qosd.c qosdCurDSDirSetup
-- qosdDumpDSDir
-- qosdDumpDirCtxStmt
-- qosdDumpSgaFind
-- qosdGenFindId
-- qosdGetRelevantDir
-- qosdInitDirCtx
-- qosdInsObjToHt
-- qosdSetupDirCtx4QB
-- xct.c xctend
-- xpl.c xplDumpRws
-- xplDumpSqlTrace
Other filenames:
-
dbsdrv.c: Modifies SQL statements in the kernel during database creation time ("D_…"). See $ORACLE_HOME/rdbms/admin/dsec.bsq
-
exuiss.c:
-
gwm.c: somehow makes it possible to calculate database version (see dbms_gsm_fix.getDBVersion). Is also able to raise dbms_gsm_utility.err_no_param(?)
-
imptts.c: classic import. See also dbms_plugts
-
kbc0: Related to table amau$
-
kbcrv.h: Related to table amrv$
-
kdmado0.h: enum kdmado_pop_state with values for flag im_pop_state of table ado_imsegtaskdetails$
-
kea.h: valid status values for column status in wri$_adv_tasks, column type in wri$_adv_objects, column command in wri$_adv_actions
-
kelt.c: keltpost(), keltpost_bg()
-
kewr.h: Definition of KEWR_DB_RELEASE_* macros. (compare UPDATE wrm$_wr_control SET swrf_version = …)
-
knldm.h, knldm.c: Related to sys.repl_support_matrix?
-
knlu.h: Macro KNLU_APPLY_PROC, compare with dbms_streams_adm_utl.proc_type_apply and value process_type=1 of sys.streams$_process_params. Also macro KNLU_CAPTURE_PROC (corresponding to dbms_streams_adm_utl.streams_type_capture and process_type=2)
-
kkpac.h: related to partitioned objects?
-
kkxwtp.c: PL/SQL index table methods
-
kkzf.h: Refresh methods and flags (columns refmet and refflg of mvref$_stats)
-
kkzf2.c
-
kqd.h: table data filters for metafilter$? See catmettypes.sql. Compare ktt3.h
-
kqld.h, kqld.c: Table property bits (tab$.property2, tab$.property3, KQLDTVCP_*, KQLDTVCP2_*, …)
-
splatform3.h: constants for platform_id of v$database?
-
kqfv.h: v$bh, v$ping, v$cache, v$cache_lock, v$false_ping, v$cache_transfer
-
ksdp.c: Kernel Service Debug Parser (apparently used in conjunction with Unified Tracing Service (UTS))
-
ktscts.h: Segment flags (KTSSEGM_FLAG_…, column flags of ku$_storage_t)
-
ktfa.c: apparently created flashback dictionary tables before they were mofed to dfba.bsq.
-
kttm.c: replicates definition of sys_objects
-
ktt3.h
-
kux.c: utl_cxml.getHashCode, utl_xml.xslLoadFromFile
-
kzan.c: or audit/kzan.c - Definition of operations for audsys.run_aud_ddl_dml(…)
-
octdef.h: Oracle command type definitions/codes? See also audit_actions.
-
psdicd.c: implementation of raise_application_error ?
-
psdpgi.c
-
qcdl.h: Flags controlling the property column of col$ (KQLDCOP_VIR = 0x00000008, KQLDCOP_EXP = 0x00010000, KQLDCOP_HID = 0x00000020, KQLDCOP_GEN = 0x0000010, KQLDCOP_VCEXP = 0x01000000, …). See also the LOADER_COL_FLAGS view.
-
qjsng.c: XDK?
-
qmts.c: schema registration
-
qmxdp.c with function qmxdpGetColName which exposes only necessary(?) columns.
-
qmxqrs.c
-
qosp.h: User defined stats? QOS_IS_*_STATS_EXTN macros
-
qx.h: Positions and number of arguments for extensible indexing/statistics external tables, see catodci.sql
-
qxim.c, qxxm.c: Types and interfaces for extensibility (ODCI) ? See catodci.sql
-
sqldef.h: type num (data types?)