Queries Run by the Database Module
The specific queries run during the course of inventory and performance analysis are specific to the DBMS.
MySQL
The following queries are run by an MySQL database:
select @@hostname h, @@version v
SELECT SCHEMA_NAME FROM information_schema.schemata
SELECT host, db, command, state, time FROM information_schema.processlist
select db, count(distinct(user)) userCount from information_schema.processlist group by db
select count(distinct(user)) userCount from information_schema.processlist
SELECT *, unix_timestamp(create_time) ct, unix_timestamp(update_time) ut, unix_timestamp(check_time) cht FROM INFORMATION_SCHEMA.TABLES
MS SQL Server
The following queries are run by an MS SQL Server database:
select SERVERPROPERTY ('ProductVersion') v, SERVERPROPERTY ('MachineName') h
SELECT name, database_id, create_date FROM sys.databases
SELECT name s FROM sys.databases
SELECT conn.client_net_address,
conn.client_tcp_port,
sess.status,
sess.last_request_start_time,
DB_NAME(sess.database_id) AS db
FROM sys.dm_exec_sessions sess
LEFT JOIN sys.dm_exec_connections conn
ON sess.session_id=conn.session_id
WHERE sess.is_user_process=1
select DB_NAME(database_id) as db, count(distinct(login_name)) userCount from sys.dm_exec_sessions group by DB_NAME(database_id)
select count(distinct(login_name)) userCount from sys.dm_exec_sessions
select
t.name as tableName,
s.name as secondarySchema,
datediff(s, '1970-01-01 00:00:00', max(t.create_date)) as createDate,
datediff(s, '1970-01-01 00:00:00', max(t.modify_date)) as updateDate,
max(p.rows) as RowCounts,
sum(a.total_pages*8) as totalSpaceKB,
sum(a.used_pages*8) as usedSpaceKB,
sum(case when i.index_id < 2 then a.data_pages*8 else 0 end) as dataSpaceKB,
sum(a.used_pages*8)-sum(case when i.index_id < 2 then a.data_pages*8 else 0 end) as indexSpaceKB
from $schema.sys.tables t
inner join $schema.sys.indexes i on t.object_id = i.object_id
inner join $schema.sys.partitions p on i.object_id = p.object_id and i.index_id = p.index_id
inner join $schema.sys.allocation_units a on p.partition_id = a.container_id
inner join $schema.sys.schemas s on t.schema_id=s.schema_id
group by t.name, s.name
Oracle Database
The following queries are run by an Oracle database:
SELECT HOST_NAME H, VERSION V FROM V$INSTANCE
SELECT username FROM dba_users u WHERE EXISTS (SELECT 1 FROM dba_objects o WHERE o.owner = u.username)
SELECT MACHINE, PORT, SCHEMANAME, STATUS, COMMAND, LAST_CALL_ET FROM v$session WHERE username IS NOT NULL
select schemaname DB, count(distinct(user)) USERCOUNT from v$session group by schemaname
select count(distinct(user)) userCount from v$session
select
table_name, owner, sum(decode(type,'table',bytes))/1024 tableKB,
sum(decode(type,'index',bytes))/1024 indexKB, sum(decode(type,'lob',bytes))/1024 lobKB,
sum(bytes)/1024 totalKB, sum(num_rows) numRows, max(last_anal) last_anal,
max(created) created, max(updated) updated, max(tbs) tablespace,
sum(decode(type,'table',bytes,'lob',bytes))/1024 totalDataKB,
sum(decode(type,'index',bytes,'lobidx',bytes))/1024 totalIdxKB
from (
select t.table_name table_name, 'table' type, t.owner, s.bytes, t.num_rows,
t.last_analyzed last_anal, o.created created, o.last_ddl_time updated, t.tablespace_name tbs
from dba_tables t left join dba_segments s
on s.segment_name=t.table_name and s.owner=t.owner
left join dba_objects o on t.table_name=o.object_name and t.owner=o.owner
where s.segment_type in ('TABLE','TABLE PARTITION','TABLE SUBPARTITION') or s.segment_type is null
union all select i.table_name table_name, 'index' type, i.owner, s.bytes, 0 num_rows,
null last_anal, null created, null updated, null tbs
from dba_segments s inner join dba_indexes i
on i.index_name = s.segment_name and s.owner = i.owner
where s.segment_type in ('INDEX','INDEX PARTITION','INDEX SUBPARTITION')
union all select l.table_name, 'lob' type, l.owner, s.bytes, 0 num_rows, null last_anal,
null created, null updated, null tbs
from dba_lobs l inner join dba_segments s on l.segment_name = s.segment_name and l.owner = s.owner
where s.segment_type in ('LOBSEGMENT','LOB PARTITION')
union all select l.table_name, 'lobidx' type, l.owner, s.bytes, 0 num_rows, null last_anal,
null created, null updated, null tbs
from dba_lobs l inner join dba_segments s on l.index_name = s.segment_name and s.owner = l.owner
where s.segment_type = 'LOBINDEX' )
group by table_name, owner