SQL> SELECT tablespace_name,count(*) cnt from dba_data_files having count(*) > 0 group by tablespace_name;
TABLESPACE_NAME CNT
------------------------------ ----------
SYSTEM 1
UNDOTBS1 1
SYSAUX 1
USERS 1
It is necessary to check the number of files generated for a particular time or day in linux. If many files are generated per time, then they need to be purged at regular intervals to avoid disk space exhaust.
To check the number of trace files generated per month.
[oracle@xhydra trace]$ ls -lrt *trc*|grep 'Feb'|awk '{print $6,$7,$8}'|uniq -c|sort -hr|head
27 Feb 26 15:02
23 Feb 24 20:22
22 Feb 15 12:22
21 Feb 13 08:25
18 Feb 23 21:27
17 Feb 5 10:20
13 Feb 1 19:44
9 Feb 1 12:29
9 Feb 1 12:28
8 Feb 1 19:05
To check the number of trace files generated per day.
[oracle@xhydra trace]$ ls -lrt *trc*|grep 'Feb 24'|awk '{print $6,$7,$8}'|uniq -c|sort -hr|head
23 Feb 24 20:22
3 Feb 24 20:51
3 Feb 24 20:50
2 Feb 24 20:49
2 Feb 24 20:48
2 Feb 24 20:42
1 Feb 24 20:47
1 Feb 24 20:46
1 Feb 24 20:45
1 Feb 24 20:44
To check the number of trace files generated per time.
[oracle@xhydra trace]$ ls -lrt *trc*|grep 'Feb 24 20:4'|awk '{print $6,$7,$8}'|uniq -c|sort -hr|head
2 Feb 24 20:49
2 Feb 24 20:48
2 Feb 24 20:42
1 Feb 24 20:47
1 Feb 24 20:46
1 Feb 24 20:45
1 Feb 24 20:44
1 Feb 24 20:43
1 Feb 24 20:41
1 Feb 24 20:40
[oracle@x3dbzx36 ~]$ sudo pstack 7688
#0 0x00000000096aef0c in OCIKCallPop ()
#1 0x00000000020a7bbc in psdwc ()
#2 0x00000000038dc1e0 in pefdwctx ()
#3 0x00000000038dce64 in pefupargs ()
#4 0x00000000038d78d0 in pefccal ()
#5 0x00000000038d7066 in pefcal ()
#6 0x00000000037e1f2d in pevm_FCAL ()
#7 0x00000000037d0587 in pfrinstr_FCAL ()
#8 0x000000000989e16f in pfrrun_no_tool ()
#9 0x000000000989c843 in pfrrun ()
#10 0x00000000098a3f69 in plsql_run ()
#11 0x0000000009894f49 in peidxr_run ()
#12 0x0000000009894db9 in peidxexe ()
#13 0x000000000988c27c in kkxdexe ()
#14 0x00000000095cf6d9 in kkxmpexe ()
#15 0x0000000002fdaebd in kgmexwi ()
#16 0x000000000987a9e1 in kgmexec ()
#17 0x0000000009671903 in evapls ()
#18 0x000000000966a8ac in evaopn2 ()
#19 0x0000000002548649 in qerlt_snv ()
#20 0x0000000002547ea4 in qerltRop ()
#21 0x0000000002511147 in qersqRowProcedure ()
#22 0x0000000009726643 in qersoFetch ()
#23 0x000000000970b15d in qerjotFetch ()
#24 0x000000000251129a in qersqFetch ()
#25 0x00000000095349e3 in rwsfcd ()
#26 0x000000000254639d in qerltFetch ()
#27 0x0000000001f50e33 in insdlexe ()
#28 0x0000000009664680 in insExecStmtExecIniEngine ()
#29 0x0000000009664267 in insexe ()
#30 0x00000000095c0bf0 in opiexe ()
#31 0x0000000001ba5992 in kpoal8 ()
#32 0x00000000095bbdad in opiodr ()
#33 0x00000000097a629f in ttcpip ()
#34 0x000000000186470e in opitsk ()
#35 0x0000000001869235 in opiino ()
#36 0x00000000095bbdad in opiodr ()
#37 0x00000000018607ac in opidrv ()
#38 0x0000000001e3a48f in sou2o ()
#39 0x0000000000a29265 in opimai_real ()
#40 0x0000000001e407ad in ssthrdmain ()
#41 0x0000000000a291d1 in main ()
[oracle@x3dbzx36 ~]$ for i in `sudo pstack 7865|awk 'BEGIN { ORS="<==()"}; {print $4}'`;do echo $i; done
kksLockAllocate<==()kxsGetRuntimeLock<==()kksfbc<==()opiexe<==()opiall0<==()opikpr<==()opiodr<==()rpidrus<==()skgmstack<==()rpiswu2<==()kprball<==()kqdsnu<==()kqrcmt<==()ktcCommitTxn<==()kdnwor<==()kdnAllocN<==()kdnnxt<==()qersqPopulate<==()qersqRowProcedure<==()qersoFetch<==()qerjotFetch<==()qersqFetch<==()rwsfcd<==()qerltFetch<==()insdlexe<==()insExecStmtExecIniEngine<==()insexe<==()opiexe<==()kpoal8<==()opiodr<==()ttcpip<==()opitsk<==()opiino<==()opiodr<==()opidrv<==()sou2o<==()opimai_real<==()ssthrdmain<==()main<==()
There are two ways to insert documents into a collection.
To insert one record, use insertOne function to perform an easy insert into collection.
> show dbs
admin 0.000GB
config 0.000GB
local 0.000GB
newdb 0.000GB
> use newdb
switched to db newdb
>
>
> db.tabl.insertOne({name:'abc',num:1});
{
"acknowledged" : true,
"insertedId" : ObjectId("65d61d738aba3165734c3dc0")
}
> db.tabl.find().pretty()
{ "_id" : ObjectId("65cef4cbb2e507f33f8047ab"), "name" : "abc", "age" : 20 }
{
"_id" : ObjectId("65cef5dbb2e507f33f8047ac"),
"name" : "new",
"description" : "About me",
"hobbies" : "sports"
}
{ "_id" : ObjectId("65d61d738aba3165734c3dc0"), "name" : "abc", "num" : 1 }
To insert one record, use insertMany function to perform multiple inserts into collection at sametime.
> db.tabl.insertMany([{name:'mnc',num:2},{name:'klm',age:30,num:3}]);
{
"acknowledged" : true,
"insertedIds" : [
ObjectId("65d61dfd8aba3165734c3dc1"),
ObjectId("65d61dfd8aba3165734c3dc2")
]
}
> db.tabl.find().pretty()
{ "_id" : ObjectId("65cef4cbb2e507f33f8047ab"), "name" : "abc", "age" : 20 }
{
"_id" : ObjectId("65cef5dbb2e507f33f8047ac"),
"name" : "new",
"description" : "About me",
"hobbies" : "sports"
}
{ "_id" : ObjectId("65d61d738aba3165734c3dc0"), "name" : "abc", "num" : 1 }
{ "_id" : ObjectId("65d61dfd8aba3165734c3dc1"), "name" : "mnc", "num" : 2 }
{
"_id" : ObjectId("65d61dfd8aba3165734c3dc2"),
"name" : "klm",
"age" : 30,
"num" : 3
}
There are 3 system databases by default in MongoDB which are used for administration purpose.
They are
Admin database contains only one collection which is related to version of mongodb.
> show dbs
admin 0.000GB
config 0.000GB
local 0.000GB
newdb 0.000GB
> use admin
switched to db admin
> show collections
system.version
> db.system.version.find()
\{ "_id" : "featureCompatibilityVersion", "version" : "4.4" }
>
>
Config database contains the session information which are connected to the database currently> use config
switched to db config
> show collections
system.sessions
> db.system.sessions.find()
> db.system.sessions.find()
{ "_id" : { "id" : UUID("fd9fa8f2-05d7-4935-b946-228c3358e6cb"), "uid" : BinData(0,"47DEQpj8HBSa+/TImW+5JCeuQeRkm5NMpJWZG3hSuFU=") }, "lastUse" : ISODate("2024-02-20T03:20:05.348Z") }
{ "_id" : { "id" : UUID("540ab7eb-ca5e-4c6f-99af-129bdc7203af"), "uid" : BinData(0,"47DEQpj8HBSa+/TImW+5JCeuQeRkm5NMpJWZG3hSuFU=") }, "lastUse" : ISODate("2024-02-20T03:20:05.348Z") }Local database contains the startup log of the database.`
> use local
switched to db local
> show collections
startup_log
> db.startup_log.find()
{ “_id” : “exhydra-VirtualBox-1668693929960”, “hostname” : “exhydra-VirtualBox”, “startTime” : ISODate(“2022-11-17T14:05:29Z”), “startTimeLocal” : “Thu Nov 17 19:35:29.960”, “cmdLine” : { “config” : “/etc/mongod.conf”, “net” : { “bindIp” : “127.0.0.1”, “port” : 27017 }, “processManagement” : { “timeZoneInfo” : “/usr/share/zoneinfo” }, “storage” : { “dbPath” : “/var/lib/mongodb”, “journal” : { “enabled” : true } }, “systemLog” : { “destination” : “file”, “logAppend” : true, “path” : “/var/log/mongodb/mongod.log” } }, “pid” : NumberLong(4137), “buildinfo” : { “version” : “4.4.18”, “gitVersion” : “8ed32b5c2c68ebe7f8ae2ebe8d23f36037a17dea”, “modules” : [ ], “allocator” : “tcmalloc”, “javascriptEngine” : “mozjs”, “sysInfo” : “deprecated”, “versionArray” : [ 4, 4, 18, 0 ], “openssl” : { “running” : “OpenSSL 1.1.1 11 Sep 2018”, “compiled” : “OpenSSL 1.1.1 11 Sep 2018” }, “buildEnvironment” : { “distmod” : “ubuntu1804”, “distarch” : “x86_64”, “cc” : “/opt/mongodbtoolchain/v3/bin/gcc: gcc (GCC) 8.5.0”, “ccflags” : “-ffp-contract=off -fno-omit-frame-pointer -fno-strict-aliasing -fasynchronous-unwind-tables -ggdb -pthread -Wall -Wsign-compare -Wno-unknown-pragmas -Winvalid-pch -Werror -O2 -Wno-unused-local-typedefs -Wno-unused-function -Wno-deprecated-declarations -Wno-unused-const-variable -Wno-unused-but-set-variable -Wno-missing-braces -fstack-protector-strong -fno-builtin-memcmp”, “cxx” : “/opt/mongodbtoolchain/v3/bin/g++: g++ (GCC) 8.5.0”, “cxxflags” : “-Woverloaded-virtual -Wno-maybe-uninitialized -fsized-deallocation -std=c++17”, “linkflags” : “-pthread -Wl,-z,now -rdynamic -Wl,–fatal-warnings -fstack-protector-strong -fuse-ld=gold -Wl,–no-threads -Wl,–build-id -Wl,–hash-style=gnu -Wl,-z,noexecstack -Wl,–warn-execstack -Wl,-z,relro -Wl,-z,origin -Wl,–enable-new-dtags”, “target_arch” : “x86_64”, “target_os” : “linux”, “cppdefines” : “SAFEINT_USE_INTRINSICS 0 PCRE_STATIC NDEBUG _XOPEN_SOURCE 700 _GNU_SOURCE _FORTIFY_SOURCE 2 BOOST_THREAD_VERSION 5 BOOST_THREAD_USES_DATETIME BOOST_SYSTEM_NO_DEPRECATED BOOST_MATH_NO_LONG_DOUBLE_MATH_FUNCTIONS BOOST_ENABLE_ASSERT_DEBUG_HANDLER BOOST_LOG_NO_SHORTHAND_NAMES BOOST_LOG_USE_NATIVE_SYSLOG BOOST_LOG_WITHOUT_THREAD_ATTR ABSL_FORCE_ALIGNED_ACCESS” }, “bits” : 64, “debug” : false, “maxBsonObjectSize” : 16777216, “storageEngines” : [ “biggie”, “devnull”, “ephemeralForTest”, “wiredTiger” ] } }
{ “_id” : “exhydra-VirtualBox-1694744672089”, “hostname” : “exhydra-VirtualBox”, “startTime” : ISODate(“2023-09-15T02:24:32Z”), “startTimeLocal” : “Fri Sep 15 07:54:32.089”, “cmdLine” : { “config” : “/etc/mongod.conf”, “net” : { “bindIp” : “127.0.0.1”, “port” : 27017 }, “processManagement” : { “timeZoneInfo” : “/usr/share/zoneinfo” }, “storage” : { “dbPath” : “/var/lib/mongodb”, “journal” : { “enabled” : true } }, “systemLog” : { “destination” : “file”, “logAppend” : true, “path” : “/var/log/mongodb/mongod.log” } }, “pid” : NumberLong(978), “buildinfo” : { “version” : “4.4.18”, “gitVersion” : “8ed32b5c2c68ebe7f8ae2ebe8d23f36037a17dea”, “modules” : [ ], “allocator” : “tcmalloc”, “javascriptEngine” : “mozjs”, “sysInfo” : “deprecated”, “versionArray” : [ 4, 4, 18, 0 ], “openssl” : { “running” : “OpenSSL 1.1.1 11 Sep 2018”, “compiled” : “OpenSSL 1.1.1 11 Sep 2018” }, “buildEnvironment” : { “distmod” : “ubuntu1804”, “distarch” : “x86_64”, “cc” : “/opt/mongodbtoolchain/v3/bin/gcc: gcc (GCC) 8.5.0”, “ccflags” : “-ffp-contract=off -fno-omit-frame-pointer -fno-strict-aliasing -fasynchronous-unwind-tables -ggdb -pthread -Wall -Wsign-compare -Wno-unknown-pragmas -Winvalid-pch -Werror -O2 -Wno-unused-local-typedefs -Wno-unused-function -Wno-deprecated-declarations -Wno-unused-const-variable -Wno-unused-but-set-variable -Wno-missing-braces -fstack-protector-strong -fno-builtin-memcmp”, “cxx” : “/opt/mongodbtoolchain/v3/bin/g++: g++ (GCC) 8.5.0”, “cxxflags” : “-Woverloaded-virtual -Wno-maybe-uninitialized -fsized-deallocation -std=c++17”, “linkflags” : “-pthread -Wl,-z,now -rdynamic -Wl,–fatal-warnings -fstack-protector-strong -fuse-ld=gold -Wl,–no-threads -Wl,–build-id -Wl,–hash-style=gnu -Wl,-z,noexecstack -Wl,–warn-execstack -Wl,-z,relro -Wl,-z,origin -Wl,–enable-new-dtags”, “target_arch” : “x86_64”, “target_os” : “linux”, “cppdefines” : “SAFEINT_USE_INTRINSICS 0 PCRE_STATIC NDEBUG _XOPEN_SOURCE 700 _GNU_SOURCE _FORTIFY_SOURCE 2 BOOST_THREAD_VERSION 5 BOOST_THREAD_USES_DATETIME BOOST_SYSTEM_NO_DEPRECATED BOOST_MATH_NO_LONG_DOUBLE_MATH_FUNCTIONS BOOST_ENABLE_ASSERT_DEBUG_HANDLER BOOST_LOG_NO_SHORTHAND_NAMES BOOST_LOG_USE_NATIVE_SYSLOG BOOST_LOG_WITHOUT_THREAD_ATTR ABSL_FORCE_ALIGNED_ACCESS” }, “bits” : 64, “debug” : false, “maxBsonObjectSize” : 16777216, “storageEngines” : [ “biggie”, “devnull”, “ephemeralForTest”, “wiredTiger” ] } }
{ “_id” : “exhydra-VirtualBox-1702895606238”, “hostname” : “exhydra-VirtualBox”, “startTime” : ISODate(“2023-12-18T10:33:26Z”), “startTimeLocal” : “Mon Dec 18 16:03:26.238”, “cmdLine” : { “config” : “/etc/mongod.conf”, “net” : { “bindIp” : “127.0.0.1”, “port” : 27017 }, “processManagement” : { “timeZoneInfo” : “/usr/share/zoneinfo” }, “storage” : { “dbPath” : “/var/lib/mongodb”, “journal” : { “enabled” : true } }, “systemLog” : { “destination” : “file”, “logAppend” : true, “path” : “/var/log/mongodb/mongod.log” } }, “pid” : NumberLong(952), “buildinfo” : { “version” : “4.4.18”, “gitVersion” : “8ed32b5c2c68ebe7f8ae2ebe8d23f36037a17dea”, “modules” : [ ], “allocator” : “tcmalloc”, “javascriptEngine” : “mozjs”, “sysInfo” : “deprecated”, “versionArray” : [ 4, 4, 18, 0 ], “openssl” : { “running” : “OpenSSL 1.1.1 11 Sep 2018”, “compiled” : “OpenSSL 1.1.1 11 Sep 2018” }, “buildEnvironment” : { “distmod” : “ubuntu1804”, “distarch” : “x86_64”, “cc” : “/opt/mongodbtoolchain/v3/bin/gcc: gcc (GCC) 8.5.0”, “ccflags” : “-ffp-contract=off -fno-omit-frame-pointer -fno-strict-aliasing -fasynchronous-unwind-tables -ggdb -pthread -Wall -Wsign-compare -Wno-unknown-pragmas -Winvalid-pch -Werror -O2 -Wno-unused-local-typedefs -Wno-unused-function -Wno-deprecated-declarations -Wno-unused-const-variable -Wno-unused-but-set-variable -Wno-missing-braces -fstack-protector-strong -fno-builtin-memcmp”, “cxx” : “/opt/mongodbtoolchain/v3/bin/g++: g++ (GCC) 8.5.0”, “cxxflags” : “-Woverloaded-virtual -Wno-maybe-uninitialized -fsized-deallocation -std=c++17”, “linkflags” : “-pthread -Wl,-z,now -rdynamic -Wl,–fatal-warnings -fstack-protector-strong -fuse-ld=gold -Wl,–no-threads -Wl,–build-id -Wl,–hash-style=gnu -Wl,-z,noexecstack -Wl,–warn-execstack -Wl,-z,relro -Wl,-z,origin -Wl,–enable-new-dtags”, “target_arch” : “x86_64”, “target_os” : “linux”, “cppdefines” : “SAFEINT_USE_INTRINSICS 0 PCRE_STATIC NDEBUG _XOPEN_SOURCE 700 _GNU_SOURCE _FORTIFY_SOURCE 2 BOOST_THREAD_VERSION 5 BOOST_THREAD_USES_DATETIME BOOST_SYSTEM_NO_DEPRECATED BOOST_MATH_NO_LONG_DOUBLE_MATH_FUNCTIONS BOOST_ENABLE_ASSERT_DEBUG_HANDLER BOOST_LOG_NO_SHORTHAND_NAMES BOOST_LOG_USE_NATIVE_SYSLOG BOOST_LOG_WITHOUT_THREAD_ATTR ABSL_FORCE_ALIGNED_ACCESS” }, “bits” : 64, “debug” : false, “maxBsonObjectSize” : 16777216, “storageEngines” : [ “biggie”, “devnull”, “ephemeralForTest”, “wiredTiger” ] } }
{ “_id” : “exhydra-VirtualBox-1704990252655”, “hostname” : “exhydra-VirtualBox”, “startTime” : ISODate(“2024-01-11T16:24:12Z”), “startTimeLocal” : “Thu Jan 11 21:54:12.655”, “cmdLine” : { “config” : “/etc/mongod.conf”, “net” : { “bindIp” : “127.0.0.1”, “port” : 27017 }, “processManagement” : { “timeZoneInfo” : “/usr/share/zoneinfo” }, “storage” : { “dbPath” : “/var/lib/mongodb”, “journal” : { “enabled” : true } }, “systemLog” : { “destination” : “file”, “logAppend” : true, “path” : “/var/log/mongodb/mongod.log” } }, “pid” : NumberLong(1002), “buildinfo” : { “version” : “4.4.18”, “gitVersion” : “8ed32b5c2c68ebe7f8ae2ebe8d23f36037a17dea”, “modules” : [ ], “allocator” : “tcmalloc”, “javascriptEngine” : “mozjs”, “sysInfo” : “deprecated”, “versionArray” : [ 4, 4, 18, 0 ], “openssl” : { “running” : “OpenSSL 1.1.1 11 Sep 2018”, “compiled” : “OpenSSL 1.1.1 11 Sep 2018” }, “buildEnvironment” : { “distmod” : “ubuntu1804”, “distarch” : “x86_64”, “cc” : “/opt/mongodbtoolchain/v3/bin/gcc: gcc (GCC) 8.5.0”, “ccflags” : “-ffp-contract=off -fno-omit-frame-pointer -fno-strict-aliasing -fasynchronous-unwind-tables -ggdb -pthread -Wall -Wsign-compare -Wno-unknown-pragmas -Winvalid-pch -Werror -O2 -Wno-unused-local-typedefs -Wno-unused-function -Wno-deprecated-declarations -Wno-unused-const-variable -Wno-unused-but-set-variable -Wno-missing-braces -fstack-protector-strong -fno-builtin-memcmp”, “cxx” : “/opt/mongodbtoolchain/v3/bin/g++: g++ (GCC) 8.5.0”, “cxxflags” : “-Woverloaded-virtual -Wno-maybe-uninitialized -fsized-deallocation -std=c++17”, “linkflags” : “-pthread -Wl,-z,now -rdynamic -Wl,–fatal-warnings -fstack-protector-strong -fuse-ld=gold -Wl,–no-threads -Wl,–build-id -Wl,–hash-style=gnu -Wl,-z,noexecstack -Wl,–warn-execstack -Wl,-z,relro -Wl,-z,origin -Wl,–enable-new-dtags”, “target_arch” : “x86_64”, “target_os” : “linux”, “cppdefines” : “SAFEINT_USE_INTRINSICS 0 PCRE_STATIC NDEBUG _XOPEN_SOURCE 700 _GNU_SOURCE _FORTIFY_SOURCE 2 BOOST_THREAD_VERSION 5 BOOST_THREAD_USES_DATETIME BOOST_SYSTEM_NO_DEPRECATED BOOST_MATH_NO_LONG_DOUBLE_MATH_FUNCTIONS BOOST_ENABLE_ASSERT_DEBUG_HANDLER BOOST_LOG_NO_SHORTHAND_NAMES BOOST_LOG_USE_NATIVE_SYSLOG BOOST_LOG_WITHOUT_THREAD_ATTR ABSL_FORCE_ALIGNED_ACCESS” }, “bits” : 64, “debug” : false, “maxBsonObjectSize” : 16777216, “storageEngines” : [ “biggie”, “devnull”, “ephemeralForTest”, “wiredTiger” ] } }
{ “_id” : “exhydra-VirtualBox-1707985693848”, “hostname” : “exhydra-VirtualBox”, “startTime” : ISODate(“2024-02-15T08:28:13Z”), “startTimeLocal” : “Thu Feb 15 13:58:13.848”, “cmdLine” : { “config” : “/etc/mongod.conf”, “net” : { “bindIp” : “127.0.0.1”, “port” : 27017 }, “processManagement” : { “timeZoneInfo” : “/usr/share/zoneinfo” }, “storage” : { “dbPath” : “/var/lib/mongodb”, “journal” : { “enabled” : true } }, “systemLog” : { “destination” : “file”, “logAppend” : true, “path” : “/var/log/mongodb/mongod.log” } }, “pid” : NumberLong(971), “buildinfo” : { “version” : “4.4.18”, “gitVersion” : “8ed32b5c2c68ebe7f8ae2ebe8d23f36037a17dea”, “modules” : [ ], “allocator” : “tcmalloc”, “javascriptEngine” : “mozjs”, “sysInfo” : “deprecated”, “versionArray” : [ 4, 4, 18, 0 ], “openssl” : { “running” : “OpenSSL 1.1.1 11 Sep 2018”, “compiled” : “OpenSSL 1.1.1 11 Sep 2018” }, “buildEnvironment” : { “distmod” : “ubuntu1804”, “distarch” : “x86_64”, “cc” : “/opt/mongodbtoolchain/v3/bin/gcc: gcc (GCC) 8.5.0”, “ccflags” : “-ffp-contract=off -fno-omit-frame-pointer -fno-strict-aliasing -fasynchronous-unwind-tables -ggdb -pthread -Wall -Wsign-compare -Wno-unknown-pragmas -Winvalid-pch -Werror -O2 -Wno-unused-local-typedefs -Wno-unused-function -Wno-deprecated-declarations -Wno-unused-const-variable -Wno-unused-but-set-variable -Wno-missing-braces -fstack-protector-strong -fno-builtin-memcmp”, “cxx” : “/opt/mongodbtoolchain/v3/bin/g++: g++ (GCC) 8.5.0”, “cxxflags” : “-Woverloaded-virtual -Wno-maybe-uninitialized -fsized-deallocation -std=c++17”, “linkflags” : “-pthread -Wl,-z,now -rdynamic -Wl,–fatal-warnings -fstack-protector-strong -fuse-ld=gold -Wl,–no-threads -Wl,–build-id -Wl,–hash-style=gnu -Wl,-z,noexecstack -Wl,–warn-execstack -Wl,-z,relro -Wl,-z,origin -Wl,–enable-new-dtags”, “target_arch” : “x86_64”, “target_os” : “linux”, “cppdefines” : “SAFEINT_USE_INTRINSICS 0 PCRE_STATIC NDEBUG _XOPEN_SOURCE 700 _GNU_SOURCE _FORTIFY_SOURCE 2 BOOST_THREAD_VERSION 5 BOOST_THREAD_USES_DATETIME BOOST_SYSTEM_NO_DEPRECATED BOOST_MATH_NO_LONG_DOUBLE_MATH_FUNCTIONS BOOST_ENABLE_ASSERT_DEBUG_HANDLER BOOST_LOG_NO_SHORTHAND_NAMES BOOST_LOG_USE_NATIVE_SYSLOG BOOST_LOG_WITHOUT_THREAD_ATTR ABSL_FORCE_ALIGNED_ACCESS” }, “bits” : 64, “debug” : false, “maxBsonObjectSize” : 16777216, “storageEngines” : [ “biggie”, “devnull”, “ephemeralForTest”, “wiredTiger” ] } }
{ “_id” : “exhydra-VirtualBox-1708060957531”, “hostname” : “exhydra-VirtualBox”, “startTime” : ISODate(“2024-02-16T05:22:37Z”), “startTimeLocal” : “Fri Feb 16 10:52:37.531”, “cmdLine” : { “config” : “/etc/mongod.conf”, “net” : { “bindIp” : “127.0.0.1”, “port” : 27017 }, “processManagement” : { “timeZoneInfo” : “/usr/share/zoneinfo” }, “storage” : { “dbPath” : “/var/lib/mongodb”, “journal” : { “enabled” : true } }, “systemLog” : { “destination” : “file”, “logAppend” : true, “path” : “/var/log/mongodb/mongod.log” } }, “pid” : NumberLong(983), “buildinfo” : { “version” : “4.4.18”, “gitVersion” : “8ed32b5c2c68ebe7f8ae2ebe8d23f36037a17dea”, “modules” : [ ], “allocator” : “tcmalloc”, “javascriptEngine” : “mozjs”, “sysInfo” : “deprecated”, “versionArray” : [ 4, 4, 18, 0 ], “openssl” : { “running” : “OpenSSL 1.1.1 11 Sep 2018”, “compiled” : “OpenSSL 1.1.1 11 Sep 2018” }, “buildEnvironment” : { “distmod” : “ubuntu1804”, “distarch” : “x86_64”, “cc” : “/opt/mongodbtoolchain/v3/bin/gcc: gcc (GCC) 8.5.0”, “ccflags” : “-ffp-contract=off -fno-omit-frame-pointer -fno-strict-aliasing -fasynchronous-unwind-tables -ggdb -pthread -Wall -Wsign-compare -Wno-unknown-pragmas -Winvalid-pch -Werror -O2 -Wno-unused-local-typedefs -Wno-unused-function -Wno-deprecated-declarations -Wno-unused-const-variable -Wno-unused-but-set-variable -Wno-missing-braces -fstack-protector-strong -fno-builtin-memcmp”, “cxx” : “/opt/mongodbtoolchain/v3/bin/g++: g++ (GCC) 8.5.0”, “cxxflags” : “-Woverloaded-virtual -Wno-maybe-uninitialized -fsized-deallocation -std=c++17”, “linkflags” : “-pthread -Wl,-z,now -rdynamic -Wl,–fatal-warnings -fstack-protector-strong -fuse-ld=gold -Wl,–no-threads -Wl,–build-id -Wl,–hash-style=gnu -Wl,-z,noexecstack -Wl,–warn-execstack -Wl,-z,relro -Wl,-z,origin -Wl,–enable-new-dtags”, “target_arch” : “x86_64”, “target_os” : “linux”, “cppdefines” : “SAFEINT_USE_INTRINSICS 0 PCRE_STATIC NDEBUG _XOPEN_SOURCE 700 _GNU_SOURCE _FORTIFY_SOURCE 2 BOOST_THREAD_VERSION 5 BOOST_THREAD_USES_DATETIME BOOST_SYSTEM_NO_DEPRECATED BOOST_MATH_NO_LONG_DOUBLE_MATH_FUNCTIONS BOOST_ENABLE_ASSERT_DEBUG_HANDLER BOOST_LOG_NO_SHORTHAND_NAMES BOOST_LOG_USE_NATIVE_SYSLOG BOOST_LOG_WITHOUT_THREAD_ATTR ABSL_FORCE_ALIGNED_ACCESS” }, “bits” : 64, “debug” : false, “maxBsonObjectSize” : 16777216, “storageEngines” : [ “biggie”, “devnull”, “ephemeralForTest”, “wiredTiger” ] } }
{ “_id” : “exhydra-VirtualBox-1708064923309”, “hostname” : “exhydra-VirtualBox”, “startTime” : ISODate(“2024-02-16T06:28:43Z”), “startTimeLocal” : “Fri Feb 16 11:58:43.309”, “cmdLine” : { “config” : “/etc/mongod.conf”, “net” : { “bindIp” : “127.0.0.1”, “port” : 27017 }, “processManagement” : { “timeZoneInfo” : “/usr/share/zoneinfo” }, “storage” : { “dbPath” : “/var/lib/mongodb”, “journal” : { “enabled” : true } }, “systemLog” : { “destination” : “file”, “logAppend” : true, “path” : “/var/log/mongodb/mongod.log” } }, “pid” : NumberLong(889), “buildinfo” : { “version” : “4.4.18”, “gitVersion” : “8ed32b5c2c68ebe7f8ae2ebe8d23f36037a17dea”, “modules” : [ ], “allocator” : “tcmalloc”, “javascriptEngine” : “mozjs”, “sysInfo” : “deprecated”, “versionArray” : [ 4, 4, 18, 0 ], “openssl” : { “running” : “OpenSSL 1.1.1 11 Sep 2018”, “compiled” : “OpenSSL 1.1.1 11 Sep 2018” }, “buildEnvironment” : { “distmod” : “ubuntu1804”, “distarch” : “x86_64”, “cc” : “/opt/mongodbtoolchain/v3/bin/gcc: gcc (GCC) 8.5.0”, “ccflags” : “-ffp-contract=off -fno-omit-frame-pointer -fno-strict-aliasing -fasynchronous-unwind-tables -ggdb -pthread -Wall -Wsign-compare -Wno-unknown-pragmas -Winvalid-pch -Werror -O2 -Wno-unused-local-typedefs -Wno-unused-function -Wno-deprecated-declarations -Wno-unused-const-variable -Wno-unused-but-set-variable -Wno-missing-braces -fstack-protector-strong -fno-builtin-memcmp”, “cxx” : “/opt/mongodbtoolchain/v3/bin/g++: g++ (GCC) 8.5.0”, “cxxflags” : “-Woverloaded-virtual -Wno-maybe-uninitialized -fsized-deallocation -std=c++17”, “linkflags” : “-pthread -Wl,-z,now -rdynamic -Wl,–fatal-warnings -fstack-protector-strong -fuse-ld=gold -Wl,–no-threads -Wl,–build-id -Wl,–hash-style=gnu -Wl,-z,noexecstack -Wl,–warn-execstack -Wl,-z,relro -Wl,-z,origin -Wl,–enable-new-dtags”, “target_arch” : “x86_64”, “target_os” : “linux”, “cppdefines” : “SAFEINT_USE_INTRINSICS 0 PCRE_STATIC NDEBUG _XOPEN_SOURCE 700 _GNU_SOURCE _FORTIFY_SOURCE 2 BOOST_THREAD_VERSION 5 BOOST_THREAD_USES_DATETIME BOOST_SYSTEM_NO_DEPRECATED BOOST_MATH_NO_LONG_DOUBLE_MATH_FUNCTIONS BOOST_ENABLE_ASSERT_DEBUG_HANDLER BOOST_LOG_NO_SHORTHAND_NAMES BOOST_LOG_USE_NATIVE_SYSLOG BOOST_LOG_WITHOUT_THREAD_ATTR ABSL_FORCE_ALIGNED_ACCESS” }, “bits” : 64, “debug” : false, “maxBsonObjectSize” : 16777216, “storageEngines” : [ “biggie”, “devnull”, “ephemeralForTest”, “wiredTiger” ] } }
{ “_id” : “exhydra-VirtualBox-1708065019781”, “hostname” : “exhydra-VirtualBox”, “startTime” : ISODate(“2024-02-16T06:30:19Z”), “startTimeLocal” : “Fri Feb 16 12:00:19.781”, “cmdLine” : { “config” : “/etc/mongod.conf”, “net” : { “bindIp” : “127.0.0.1”, “port” : 27017 }, “processManagement” : { “timeZoneInfo” : “/usr/share/zoneinfo” }, “storage” : { “dbPath” : “/var/lib/mongodb”, “journal” : { “enabled” : true } }, “systemLog” : { “destination” : “file”, “logAppend” : true, “path” : “/var/log/mongodb/mongod.log” } }, “pid” : NumberLong(1470), “buildinfo” : { “version” : “4.4.18”, “gitVersion” : “8ed32b5c2c68ebe7f8ae2ebe8d23f36037a17dea”, “modules” : [ ], “allocator” : “tcmalloc”, “javascriptEngine” : “mozjs”, “sysInfo” : “deprecated”, “versionArray” : [ 4, 4, 18, 0 ], “openssl” : { “running” : “OpenSSL 1.1.1 11 Sep 2018”, “compiled” : “OpenSSL 1.1.1 11 Sep 2018” }, “buildEnvironment” : { “distmod” : “ubuntu1804”, “distarch” : “x86_64”, “cc” : “/opt/mongodbtoolchain/v3/bin/gcc: gcc (GCC) 8.5.0”, “ccflags” : “-ffp-contract=off -fno-omit-frame-pointer -fno-strict-aliasing -fasynchronous-unwind-tables -ggdb -pthread -Wall -Wsign-compare -Wno-unknown-pragmas -Winvalid-pch -Werror -O2 -Wno-unused-local-typedefs -Wno-unused-function -Wno-deprecated-declarations -Wno-unused-const-variable -Wno-unused-but-set-variable -Wno-missing-braces -fstack-protector-strong -fno-builtin-memcmp”, “cxx” : “/opt/mongodbtoolchain/v3/bin/g++: g++ (GCC) 8.5.0”, “cxxflags” : “-Woverloaded-virtual -Wno-maybe-uninitialized -fsized-deallocation -std=c++17”, “linkflags” : “-pthread -Wl,-z,now -rdynamic -Wl,–fatal-warnings -fstack-protector-strong -fuse-ld=gold -Wl,–no-threads -Wl,–build-id -Wl,–hash-style=gnu -Wl,-z,noexecstack -Wl,–warn-execstack -Wl,-z,relro -Wl,-z,origin -Wl,–enable-new-dtags”, “target_arch” : “x86_64”, “target_os” : “linux”, “cppdefines” : “SAFEINT_USE_INTRINSICS 0 PCRE_STATIC NDEBUG _XOPEN_SOURCE 700 _GNU_SOURCE _FORTIFY_SOURCE 2 BOOST_THREAD_VERSION 5 BOOST_THREAD_USES_DATETIME BOOST_SYSTEM_NO_DEPRECATED BOOST_MATH_NO_LONG_DOUBLE_MATH_FUNCTIONS BOOST_ENABLE_ASSERT_DEBUG_HANDLER BOOST_LOG_NO_SHORTHAND_NAMES BOOST_LOG_USE_NATIVE_SYSLOG BOOST_LOG_WITHOUT_THREAD_ATTR ABSL_FORCE_ALIGNED_ACCESS” }, “bits” : 64, “debug” : false, “maxBsonObjectSize” : 16777216, “storageEngines” : [ “biggie”, “devnull”, “ephemeralForTest”, “wiredTiger” ] } }
{ “_id” : “exhydra-VirtualBox-1708343603824”, “hostname” : “exhydra-VirtualBox”, “startTime” : ISODate(“2024-02-19T11:53:23Z”), “startTimeLocal” : “Mon Feb 19 17:23:23.824”, “cmdLine” : { “config” : “/etc/mongod.conf”, “net” : { “bindIp” : “127.0.0.1”, “port” : 27017 }, “processManagement” : { “timeZoneInfo” : “/usr/share/zoneinfo” }, “storage” : { “dbPath” : “/var/lib/mongodb”, “journal” : { “enabled” : true } }, “systemLog” : { “destination” : “file”, “logAppend” : true, “path” : “/var/log/mongodb/mongod.log” } }, “pid” : NumberLong(1001), “buildinfo” : { “version” : “4.4.18”, “gitVersion” : “8ed32b5c2c68ebe7f8ae2ebe8d23f36037a17dea”, “modules” : [ ], “allocator” : “tcmalloc”, “javascriptEngine” : “mozjs”, “sysInfo” : “deprecated”, “versionArray” : [ 4, 4, 18, 0 ], “openssl” : { “running” : “OpenSSL 1.1.1 11 Sep 2018”, “compiled” : “OpenSSL 1.1.1 11 Sep 2018” }, “buildEnvironment” : { “distmod” : “ubuntu1804”, “distarch” : “x86_64”, “cc” : “/opt/mongodbtoolchain/v3/bin/gcc: gcc (GCC) 8.5.0”, “ccflags” : “-ffp-contract=off -fno-omit-frame-pointer -fno-strict-aliasing -fasynchronous-unwind-tables -ggdb -pthread -Wall -Wsign-compare -Wno-unknown-pragmas -Winvalid-pch -Werror -O2 -Wno-unused-local-typedefs -Wno-unused-function -Wno-deprecated-declarations -Wno-unused-const-variable -Wno-unused-but-set-variable -Wno-missing-braces -fstack-protector-strong -fno-builtin-memcmp”, “cxx” : “/opt/mongodbtoolchain/v3/bin/g++: g++ (GCC) 8.5.0”, “cxxflags” : “-Woverloaded-virtual -Wno-maybe-uninitialized -fsized-deallocation -std=c++17”, “linkflags” : “-pthread -Wl,-z,now -rdynamic -Wl,–fatal-warnings -fstack-protector-strong -fuse-ld=gold -Wl,–no-threads -Wl,–build-id -Wl,–hash-style=gnu -Wl,-z,noexecstack -Wl,–warn-execstack -Wl,-z,relro -Wl,-z,origin -Wl,–enable-new-dtags”, “target_arch” : “x86_64”, “target_os” : “linux”, “cppdefines” : “SAFEINT_USE_INTRINSICS 0 PCRE_STATIC NDEBUG _XOPEN_SOURCE 700 _GNU_SOURCE _FORTIFY_SOURCE 2 BOOST_THREAD_VERSION 5 BOOST_THREAD_USES_DATETIME BOOST_SYSTEM_NO_DEPRECATED BOOST_MATH_NO_LONG_DOUBLE_MATH_FUNCTIONS BOOST_ENABLE_ASSERT_DEBUG_HANDLER BOOST_LOG_NO_SHORTHAND_NAMES BOOST_LOG_USE_NATIVE_SYSLOG BOOST_LOG_WITHOUT_THREAD_ATTR ABSL_FORCE_ALIGNED_ACCESS” }, “bits” : 64, “debug” : false, “maxBsonObjectSize” : 16777216, “storageEngines” : [ “biggie”, “devnull”, “ephemeralForTest”, “wiredTiger” ] } }
{ “_id” : “exhydra-VirtualBox-1708397704973”, “hostname” : “exhydra-VirtualBox”, “startTime” : ISODate(“2024-02-20T02:55:04Z”), “startTimeLocal” : “Tue Feb 20 08:25:04.973”, “cmdLine” : { “config” : “/etc/mongod.conf”, “net” : { “bindIp” : “127.0.0.1”, “port” : 27017 }, “processManagement” : { “timeZoneInfo” : “/usr/share/zoneinfo” }, “storage” : { “dbPath” : “/var/lib/mongodb”, “journal” : { “enabled” : true } }, “systemLog” : { “destination” : “file”, “logAppend” : true, “path” : “/var/log/mongodb/mongod.log” } }, “pid” : NumberLong(961), “buildinfo” : { “version” : “4.4.18”, “gitVersion” : “8ed32b5c2c68ebe7f8ae2ebe8d23f36037a17dea”, “modules” : [ ], “allocator” : “tcmalloc”, “javascriptEngine” : “mozjs”, “sysInfo” : “deprecated”, “versionArray” : [ 4, 4, 18, 0 ], “openssl” : { “running” : “OpenSSL 1.1.1 11 Sep 2018”, “compiled” : “OpenSSL 1.1.1 11 Sep 2018” }, “buildEnvironment” : { “distmod” : “ubuntu1804”, “distarch” : “x86_64”, “cc” : “/opt/mongodbtoolchain/v3/bin/gcc: gcc (GCC) 8.5.0”, “ccflags” : “-ffp-contract=off -fno-omit-frame-pointer -fno-strict-aliasing -fasynchronous-unwind-tables -ggdb -pthread -Wall -Wsign-compare -Wno-unknown-pragmas -Winvalid-pch -Werror -O2 -Wno-unused-local-typedefs -Wno-unused-function -Wno-deprecated-declarations -Wno-unused-const-variable -Wno-unused-but-set-variable -Wno-missing-braces -fstack-protector-strong -fno-builtin-memcmp”, “cxx” : “/opt/mongodbtoolchain/v3/bin/g++: g++ (GCC) 8.5.0”, “cxxflags” : “-Woverloaded-virtual -Wno-maybe-uninitialized -fsized-deallocation -std=c++17”, “linkflags” : “-pthread -Wl,-z,now -rdynamic -Wl,–fatal-warnings -fstack-protector-strong -fuse-ld=gold -Wl,–no-threads -Wl,–build-id -Wl,–hash-style=gnu -Wl,-z,noexecstack -Wl,–warn-execstack -Wl,-z,relro -Wl,-z,origin -Wl,–enable-new-dtags”, “target_arch” : “x86_64”, “target_os” : “linux”, “cppdefines” : “SAFEINT_USE_INTRINSICS 0 PCRE_STATIC NDEBUG _XOPEN_SOURCE 700 _GNU_SOURCE _FORTIFY_SOURCE 2 BOOST_THREAD_VERSION 5 BOOST_THREAD_USES_DATETIME BOOST_SYSTEM_NO_DEPRECATED BOOST_MATH_NO_LONG_DOUBLE_MATH_FUNCTIONS BOOST_ENABLE_ASSERT_DEBUG_HANDLER BOOST_LOG_NO_SHORTHAND_NAMES BOOST_LOG_USE_NATIVE_SYSLOG BOOST_LOG_WITHOUT_THREAD_ATTR ABSL_FORCE_ALIGNED_ACCESS” }, “bits” : 64, “debug” : false, “maxBsonObjectSize” : 16777216, “storageEngines” : [ “biggie”, “devnull”, “ephemeralForTest”, “wiredTiger” ] } }
It is crucial to check the status of mongodb periodically to maintain the uptime of the instance for application connectivity.
High level status can be identified by checking the daemon which is responsible to make the instance functioning. “mongod” is the daemon which should be running in the server.
exhydra@exhydra-VirtualBox:~$ ps -ef|grep mongod|grep -v 'grep'
mongodb 961 1 0 08:24 ? 00:00:03 /usr/bin/mongod --config /etc/mongod.conf
The status can also be checked with systemctl or service command and the status should be Active: active (running). If it is inactive, then there is some issue with the config file or may be resource issue.
exhydra@exhydra-VirtualBox:~$ service mongod status
● mongod.service - MongoDB Database Server
Loaded: loaded (/lib/systemd/system/mongod.service; enabled; vendor preset: enabled)
Active: active (running) since Tue 2024-02-20 08:24:34 IST; 19min ago
Docs: https://docs.mongodb.org/manual
Main PID: 961 (mongod)
CGroup: /system.slice/mongod.service
└─961 /usr/bin/mongod --config /etc/mongod.conf
Feb 20 08:24:34 exhydra-VirtualBox systemd[1]: Started MongoDB Database Server.
exhydra@exhydra-VirtualBox:~$ systemctl status mongod
● mongod.service - MongoDB Database Server
Loaded: loaded (/lib/systemd/system/mongod.service; enabled; vendor preset: enabled)
Active: active (running) since Tue 2024-02-20 08:24:34 IST; 19min ago
Docs: https://docs.mongodb.org/manual
Main PID: 961 (mongod)
CGroup: /system.slice/mongod.service
└─961 /usr/bin/mongod --config /etc/mongod.conf
Feb 20 08:24:34 exhydra-VirtualBox systemd[1]: Started MongoDB Database Server.
Mongodb server listens on port 27017 or 27018 probably with 270* ports which are reserved for this purpose. If the port 27017 is opened and listening to tcp connection, then there is no firewall issues.
exhydra@exhydra-VirtualBox:~$ netstat -tupln|grep 2701
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
tcp 0 0 127.0.0.1:27017 0.0.0.0:* LISTEN -
Also the port authority can be verified by telnet command
exhydra@exhydra-VirtualBox:~$ telnet 127.0.0.1 27017
Trying 127.0.0.1...
Connected to 127.0.0.1.
Escape character is '^]'.
exhydra@exhydra-VirtualBox:~$ mongo
MongoDB shell version v4.4.18
connecting to: mongodb://127.0.0.1:27017/?compressors=disabled&gssapiServiceName=mongodb
Implicit session: session { "id" : UUID("e9c79944-aa26-4224-9e85-8decd51346ba") }
MongoDB server version: 4.4.18
---
The server generated these startup warnings when booting:
2024-02-16T10:52:32.882+05:30: Using the XFS filesystem is strongly recommended with the WiredTiger storage engine. See http://dochub.mongodb.org/core/prodnotes-filesystem
2024-02-16T10:52:36.814+05:30: Access control is not enabled for the database. Read and write access to data and configuration is unrestricted
---
---
Enable MongoDB's free cloud-based monitoring service, which will then receive and display
metrics about your deployment (disk utilization, CPU, operation statistics, etc).
The monitoring data will be available on a MongoDB website with a unique URL accessible to you
and anyone you share the URL with. MongoDB may use this information to make product
improvements and to suggest MongoDB products and deployment options to you.
To enable free monitoring, run the following command: db.enableFreeMonitoring()
To permanently disable this reminder, run the following command: db.disableFreeMonitoring()
---
> show dbs
admin 0.000GB
config 0.000GB
local 0.000GB
>
>
> use newdb
switched to db newdb
> db.tabl.insertOne({name: "abc",age: 20})
{
"acknowledged" : true,
"insertedId" : ObjectId("65cef4cbb2e507f33f8047ab")
}
> show collections
tabl
> db.tabl.find()
{ "_id" : ObjectId("65cef4cbb2e507f33f8047ab"), "name" : "abc", "age" : 20 }
> db.tabl.find().pretty()
{ "_id" : ObjectId("65cef4cbb2e507f33f8047ab"), "name" : "abc", "age" : 20 }> db.tabl.insertOne({name: "new",description:"About me",hobbies:"sports"}
... )
{
"acknowledged" : true,
"insertedId" : ObjectId("65cef5dbb2e507f33f8047ac")
}
> db.tabl.find().pretty()
{ "_id" : ObjectId("65cef4cbb2e507f33f8047ab"), "name" : "abc", "age" : 20 }
{
"_id" : ObjectId("65cef5dbb2e507f33f8047ac"),
"name" : "new",
"description" : "About me",
"hobbies" : "sports"
}I recently faced an issue where application team was complaining about a performance issue. They were executing a batch job which was not executing as per expected timelines and response time was doubled from current run.
High enqueue waits observed due to ‘SELECT FOR UPDATE’ query.
In AWR report, this query shows up on the top in elapsed time and cpu time.
| Elapsed Time (s) | Executions | Elapsed Time per Exec (s) | %Total | %CPU | %IO | SQL Id | SQL Module | SQL Text |
|---|---|---|---|---|---|---|---|---|
| 10,822.56 | 38 | 284.80 | 98.91 | 0.00 | 17.65 | 99yaq3qcnc74t | sqlplus@x3dbzx36 (TNS V1-V3) | select salary from xtbl for update … |
Also the segments of the query show on top of row lock waits
Interesting fact is that, the enqueue was waiting on an idle inactive event ‘SQLNet message from client’ session which is doing nothing.
time in wait: 0.255645 sec
timeout after: never
wait id: 565
blocking: 2 sessions
current sql_id: 0
wait history:
* time between current wait and wait #1: 0.000015 sec
1. event: 'SQLNet message to client'
time waited: 0.000001 sec
wait id: 564 p1: 'driver id'=0x74637000
p2: '#bytes'=0x1
* time between wait #1 and #2: 0.000052 sec
2. event: 'SQLNet message from client'
time waited: 9.999950 sec
wait id: 563 p1: 'driver id'=0x74637000
p2: '#bytes'=0x1
* time between wait #2 and #3: 0.000013 sec
3. event: 'SQL*Net message to client'
time waited: 0.000002 sec
wait id: 562 p1: 'driver id'=0x74637000
p2: '#bytes'=0x1
}
Chain 1 Signature: 'SQL*Net message from client'<='enq: TX - row lock contention'
Chain 1 Signature Hash: 0x38c48850Below was the inactive session which is holding the query on enqueue row lock for 90% of AWR time.
USERNAM MACHINE COUNT(*) SQL_ID TERMINAL BLOCKED_SID BLOCKING EVENT
------- ---------- ---------- ---------- ------------------------------ ----------- ---------- ----------
auser devdb 2 99yaq3qcnc74tunknown 6474 enq: TX -
row lock c
ontention
auser devdb 2 89ywufj73m 4938 PX Deq: Pa
emodb1 bm0 rse Reply
Solution:
Since the wait chain and Chain 1 Signature exactly matches similar to the document below,
Doc ID 2921839.1
After applying the workaround in the document both from application and database, the issue no longer occurred.
expire parameter is used to make the session break out after the threshold of idleness is met.
inbound parameter will timeout the session after x seconds.
Set sqlnet.expire_time=10 and sqlnet.inbound_connect_timeout=180
[oracle@xhydra ~]$ cat /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/sqlnet.ora
sqlnet.expire_time=10
sqlnet.inbound_connect_timeout=180
[oracle@xhydra ~]$ lsnrctl reload
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 05-FEB-2024 15:18:02
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
The command completed successfully
What exactly happens when a sql query contain column with null value in the predicate clause and the index is restricted to be used due to the null in it?
Oracle does not use an index on any predicate if the multiple columns are cascaded with a null predicate like ‘IS NULL’ or ‘IS NOT NULL’ etc..
Example predicates like ‘col1 =123 and col2 is null‘.
Hence, the query should be written in such a way that it leverages the benefits of oracle by using an index.
I was working on this issue where there was an argument between application and dba. Application team has this understanding that every query which has a ‘WHERE’ clause and comparison operator should use ‘INDEX’ no matter whatever they specify. Since, oracle is a product which works as per how it is designed ,need to be used as per its mechanisms and the code should be written to make use of an index.
Create a sample table for testing.
SQL> create table xtbl (
ycol varchar(100),
xid number(20),
city varchar(100),
salary number(38),
primary key(xid),
nullcol varchar(100))
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
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 KEEP)
TABLESPACE "USERS" ; 2 3 4 5 6 7 8 9 10 11 12 13
Table created.
SQL> alter table xtbl nologging;
Table altered.
SQL> create sequence xid increment by 1 start with 1;
create sequence xid increment by 1 start with 1
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
SQL> insert /*+append parallel(8)*/ into xtbl
select dbms_random.string('a','10'),
xid.nextval,
decode(round(dbms_random.value(0,10)),0,'Chennai',1,'Canberra',2,'Berlin',3,'London',4,'Paris',5,'Washington',6,'Lisbon',7,'Madrid',8,'Mumbai',9,'Moscow',10,'Rome'),
floor(dbms_random.value(1,1000000)),
null
from (select level from dual connect by level <= 100),
(select level from dual connect by level <= 100),
(select level from dual connect by level <= 100); 2 3 4 5 6 7 8 9
1000000 rows created.
SQL> commit;
Commit complete.
SQL> SELECT count(*) from xtbl where nullcol is not null;
COUNT(*)
----------
0
Since the table does not contain any null value, reserve 5 to 10% of the rows for null in nullcol column
SQL> select min(xid),max(xid) from xtbl;
MIN(XID) MAX(XID)
---------- ----------
2000001 3000000
SQL> update xtbl set nullcol='Not null' Where xid between 2000001 and 2990000;
990000 rows updated.
SQL> commit;
Commit complete.
Initially, when we query the null column, the optimizer goes for full table scan because oracle ignores an index when a null predicate is used. This is expected because oracle does not store null values in an index.
So ‘IS NULL’ is equivalent to “NVL(col,’null’)=’null’“. Think alternatively where oracle can simulate the null as a value to make use of an index. Query is rewritten to make use of an index.
SQL> SELECT city,salary from xtbl where nullcol is null;
10000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1420452506
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 800K| 12M| 1429 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| XTBL | 800K| 12M| 1429 (1)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("NULLCOL" IS NULL)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
9041 consistent gets
0 physical reads
173844 redo size
264230 bytes sent via SQL*Net to client
7738 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed
Since, oracle does not have flexibility to use an index with null, the query is rewritten with NVL function. On top of it, a functional based index is created. Still oracle does not use the index after creating an index and goes for full table scan.
SQL> CREATE index nullidx on xtbl(nvl(nullcol,'null'));
Index created.
SQL> SELECT city,salary from xtbl where nvl(nullcol,'null')='null';
10000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1420452506
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 800K| 14M| 1436 (2)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| XTBL | 800K| 14M| 1436 (2)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NVL("NULLCOL",'null')='null')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
7066 consistent gets
0 physical reads
0 redo size
264230 bytes sent via SQL*Net to client
7749 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed
Once the index hint is used to force an index scan, oracle goes for functional index scan as expected.
SQL> SELECT /*+ first_rows(1000) index(xtbl nullidx) */ city,salary from xtbl where nvl(nullcol,'null')='null';
10000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1680561852
--------------------------------------------------------------------------------
---------------
| Id | Operation | Name | Rows | Bytes | Cost (%C
PU)| Time |
--------------------------------------------------------------------------------
---------------
| 0 | SELECT STATEMENT | | 800K| 14M| 6052
(1)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| XTBL | 800K| 14M| 6052
(1)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | NULLIDX | 800K| | 2231
(1)| 00:00:01 |
--------------------------------------------------------------------------------
---------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(NVL("NULLCOL",'null')='null')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1405 consistent gets
0 physical reads
0 redo size
264230 bytes sent via SQL*Net to client
7776 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed
This is how , we can adapt ourself to make oracle to use an index in a different dimension.
Another way of making the index usable is to create an index with a sample value ‘n’ on null column and can be used without rewriting the query.
here n = 3
SQL> drop index nullidx;
Index dropped.
SQL> CREATE index nullidx on xtbl(nullcol,3);
Index created.
SQL> SELECT /*+ first_rows(1000) index(xtbl nullidx) */ city,salary from xtbl where nullcol is null;
10000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1680561852
--------------------------------------------------------------------------------
---------------
| Id | Operation | Name | Rows | Bytes | Cost (%C
PU)| Time |
--------------------------------------------------------------------------------
---------------
| 0 | SELECT STATEMENT | | 1000 | 16000 | 11
(0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| XTBL | 1000 | 16000 | 11
(0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | NULLIDX | 800K| | 6
(0)| 00:00:01 |
--------------------------------------------------------------------------------
---------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("NULLCOL" IS NULL)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1403 consistent gets
22 physical reads
0 redo size
264230 bytes sent via SQL*Net to client
7782 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed
Problem
ORA-00060: Deadlock detected. See Note 60.1 at My Oracle Support for Troubleshooting ORA-60 Errors. More i
DEADLOCK DETECTED (id=0x696f807a)
Chain Signature: 'row cache lock' (cycle)
Chain Signature Hash: 0x483ef671
The following deadlock is not an Oracle error. Deadlocks of
this type can be expected if certain SQL statements are
issued. The following information may aid in determining the
cause of the deadlock.
current sql_id: 2680986294
current sql: <none: error encountered - unable to get kgl lock no-wait> Solution
alter system flush shared_pool;
drop type "sch"."typ" force;