Queries Run by the Database Module
The specific queries run during the course of inventory and performance analysis are specific to the DBMS.
| • | MySQL |
| • | MS SQL Server |
| • | Oracle Database |
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
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
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