Current Login Sessions Contributors: David Berry, David Stoll.<\/p>\n\n\n\n Size matters and these hunting queries can provide insight to getting light weight and fast data sets when your SQL performance matters. It is detailed work but it is worth it to Pay the PRICE for PERFORMANCE<\/p>\n\n\n\n The hardest thing about performance is knowing what you need to measure, that must be tied back to your mission statement — Peter Drucker<\/p>\n\n\n\n — Current Login Sessions — Where are my logins coming from — Currently executing SQL — Session Resource Consumption — Statements consuming the most resources — Statements performing full table scans — Displaying an execution plan — Index Usage — Hard Parsing Statistics — Queries Using Literal Values — Table Information — Column Information — Index Information — if statistics are stale (haven’t been refreshed in a long time). Summary of List of Oracle queries that every Analytics Developer should know. Current Login SessionsWhere are my logins coming from Currently executing SQL Session Resource ConsumptionStatements consuming the most resourcesStatements performing full table scansDisplaying an execution planIndex UsageHard Parsing StatisticsQueries Using Literal ValuesTable InformationColumn InformationIndex InformationStale Stats or Missing Stats Contributors: David Berry, David Stoll. […]<\/p>\n","protected":false},"author":2,"featured_media":5865,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[32,31],"tags":[],"class_list":["post-5862","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-bi-dashboards-analytics","category-bi-data-warehouse"],"jetpack_featured_media_url":"https:\/\/www.designandexecute.com\/designs\/wp-content\/uploads\/2019\/07\/cheetah-hunting-springbok-Etosha.jpg","_links":{"self":[{"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/posts\/5862","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/comments?post=5862"}],"version-history":[{"count":5,"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/posts\/5862\/revisions"}],"predecessor-version":[{"id":15704,"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/posts\/5862\/revisions\/15704"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/media\/5865"}],"wp:attachment":[{"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/media?parent=5862"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/categories?post=5862"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/tags?post=5862"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
Where are my logins coming from
Currently executing SQL
Session Resource Consumption
Statements consuming the most resources
Statements performing full table scans
Displaying an execution plan
Index Usage
Hard Parsing Statistics
Queries Using Literal Values
Table Information
Column Information
Index Information
Stale Stats or Missing Stats<\/strong><\/p>\n\n\n\n
\nSELECT
\n username, osuser, program, module,
\n machine, terminal, process,
\n to_char(logon_time, ‘YYYY-MM-DD HH24:MI:SS’)
\n AS logon_time,
\n status,
\n CASE status
\n WHEN ‘ACTIVE’ THEN NULL
\n ELSE last_call_et
\n END as idle_time
\n FROM v$session
\n WHERE type = ‘USER’;<\/p>\n\n\n\n
\nSELECT
\n username, osuser, program, module,
\n machine, process,
\n count(1) as login_count
\n FROM v$session
\n WHERE type = ‘USER’
\n GROUP BY
\n username, osuser, program, module,
\n machine, process;<\/p>\n\n\n\n
\nSELECT
\n s.sid, s.username, s.osuser,
\n s.machine, s.process, s.program, s.module,
\n q.sql_text, q.optimizer_cost,
\n s.blocking_session, bs.username as blocking_user,
\n bs.machine as blocking_machine, bs.module as blocking_module,
\n bq.sql_text AS blocking_sql, s.event AS wait_event,
\n q.sql_fulltext
\n FROM v$session s
\n INNER JOIN v$sql q
\n ON s.sql_id = q.sql_id
\n LEFT OUTER JOIN v$session bs — blocking sessions
\n ON s.blocking_session = bs.sid
\n LEFT OUTER JOIN v$sql bq — blocking queries
\n ON bs.sql_id = bq.sql_id
\n WHERE s.type = ‘USER’;<\/p>\n\n\n\n
\nSELECT
\n s.sid, s.username, s.osuser,
\n to_char(sm.begin_time, ‘HH24:MI:ss’) AS interval_start,
\n to_char(sm.end_time, ‘HH24:MI:ss’) AS interval_end,
\n s.machine, s.process, s.program, s.module,
\n sm.cpu, sm.pga_memory, sm.logical_reads, sm.physical_reads,
\n sm.hard_parses, sm.soft_parses,
\n s.logon_time
\n FROM v$session s
\n INNER JOIN v$sessmetric sm
\n ON sm.session_id = s.sid
\n WHERE s.type = ‘USER’
\n ORDER BY sm.cpu DESC;<\/p>\n\n\n\n
\nSELECT * FROM
\n(
\n SELECT sql_id, sql_text, executions,
\n elapsed_time, cpu_time, buffer_gets, disk_reads,
\n elapsed_time \/ executions AS avg_elapsed_time,
\n cpu_time \/ executions AS avg_cpu_time,
\n buffer_gets \/ executions as avg_buffer_gets,
\n disk_reads \/ executions as avg_disk_reads
\n FROM v$sqlstats
\n WHERE executions > 0
\n ORDER BY elapsed_time \/ executions DESC
\n)
\nWHERE rownum <= 25;<\/p>\n\n\n\n
\nSELECT
\n pl.object_owner, pl.object_name,
\n pl.sql_id, q.sql_text, q.module,
\n pl.operation, pl.options, pl.cost, pl.cpu_cost, pl.io_cost,
\n q.executions
\n FROM v$sql_plan pl
\n INNER JOIN v$sql q
\n ON pl.sql_id = q.sql_id
\n WHERE
\n (pl.operation = ‘TABLE ACCESS’ AND pl.options = ‘FULL’)
\n OR (pl.operation = ‘INDEX’ AND pl.options = ‘FAST FULL SCAN’)
\n OR (pl.operation = ‘INDEX’ AND pl.options = ‘FULL SCAN (MIN\/MAX)’)
\n OR (pl.operation = ‘INDEX’ AND pl.options = ‘FULL SCAN’)
\n ORDER BY pl.object_owner, pl.object_name;<\/p>\n\n\n\n
\nSELECT plan_table_output
\n FROM
\n table(dbms_xplan.display_cursor(\u2018<>’,
\n null,’typical’));<\/p>\n\n\n\n
\nWITH index_usage AS
\n(
\n SELECT pl.sql_id, pl.object_owner, pl.object_name, pl.operation,
\n pl.options, count(1) as use_count
\n FROM v$sql_plan pl
\n WHERE pl.operation = ‘INDEX’
\n GROUP BY pl.sql_id, pl.object_owner, pl.object_name, pl.operation,
\n pl.options
\n)
\nSELECT
\n ix.table_owner, ix.table_name, ix.index_name, iu.operation,
\n iu.options, ss.sql_text, ss.executions
\n FROM all_indexes ix
\n LEFT OUTER JOIN index_usage iu
\n ON ix.owner = iu.object_owner
\n AND ix.index_name = iu.object_name
\n LEFT OUTER JOIN v$sqlstats ss
\n ON iu.sql_id = ss.sql_id
\n WHERE ix.owner = ‘<>’
\n ORDER BY ix.table_name, ix.index_name;<\/p>\n\n\n\n
\nSELECT sn.statistic#, sn.name, s.value
\n FROM v$sysstat s
\n INNER JOIN v$statname sn
\n ON s.statistic# = sn.statistic#
\n WHERE sn.name in (
\n ‘parse time cpu’,
\n ‘parse count (hard)’)<\/p>\n\n\n\n
WITH statements AS
(
SELECT force_matching_signature,
count(1) OVER (PARTITION BY force_matching_signature) AS statement_count,
row_number() OVER (PARTITION BY force_matching_signature
ORDER BY last_load_time DESC) AS row_index,
parsing_schema_name,
sql_text
FROM v$sql
WHERE force_matching_signature > 0
AND force_matching_signature <> exact_matching_signature
)
SELECT
force_matching_signature, parsing_schema_name,
sql_text, statement_count
FROM statements
WHERE row_index = 1
AND statement_count >= 5;<\/p>\n\n\n\n
\nSELECT
\n t.owner, t.table_name, t.num_rows, t.avg_row_len,
\n t.blocks AS blocks_below_hwm, t.empty_blocks,
\n s.blocks AS segment_blocks,
\n s.bytes \/ 1048576 AS size_in_mb,
\n to_char(t.last_analyzed, ‘YYYY-MM-DD HH24:MI’)
\n AS last_analyzed
\n FROM all_tables t
\n INNER JOIN dba_segments s
\n ON t.owner = s.OWNER AND t.table_name = s.segment_name
\n WHERE t.owner = \u2018<>’;<\/p>\n\n\n\n
\nSELECT column_name, avg_col_len,
\n num_distinct, num_nulls,
\n FROM all_tab_columns
\n WHERE table_name = ‘<>’
\n ORDER BY column_id DESC<\/p>\n\n\n\n
SELECT ix.owner, ix.index_name, ix.table_name,
ix.distinct_keys, ix.leaf_blocks,
s.blocks AS segment_blocks,
s.bytes \/ 1048576 AS size_in_mb,
to_char(ix.last_analyzed, ‘YYYY-MM-DD HH24:MI’)
AS last_analyzed
FROM all_indexes ix
INNER JOIN dba_segments s
ON ix.owner = s.OWNER
AND ix.index_name = s.segment_name
ORDER BY ix.table_name, ix.index_name<\/p>\n\n\n\n
SELECT table_name, sysdate, last_analyzed, sysdate – last_analyzed
FROM sys.all_tables
ORDER by 4 desc;<\/p>\n","protected":false},"excerpt":{"rendered":"