RichVRed

#!/bin/bash
PATH=/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin:~/bin
export PATH

# Check if user is root
if [ $(id -u) != "0" ]; then
    printf "Error: You must be root to run this script!\n"
    exit 1
fi

RichVRed

#!/bin/bash
# ----------
# Install MariaDB Client 10.1 for Debian8, Ubuntu 16.04
# curl -L https://gist.githubusercontent.com/mamemomonga/2823ecc2b20b886e90d1b8d523515724/raw/MariaDBInstall.sh | sudo bash -eux
# ----------
set -eux

if [ `id --user` -ne 0 ]; then
	echo "rootでの実行が必要"
	exit 255

RichVRed

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, dba_segments s
where ix.owner = s.owner and ix.index_name = s.segment_name
order by ix.table_name, ix.index_name;

RichVRed

select column_name, avg_col_len, num_distinct, num_nulls
from all_tab_columns
where table_name = '<<table name>>'
order by column_id desc;

RichVRed

-- table info is only as up to date as last time stats were gathered (last_analyzed)
select t.owner, t.table_name, t.num_rows, t.avg_row_len,
    t.blocks as blocks_below_hwm, t.empty_blocks,
    s.blocks as segment_blocks,
    s.bytes / 1048576 as size_in_mb,
    to_char(t.last_analyzed, 'YYYY-MM-DD HH24:MI') as last_analyzed
from all_tables t, dba_segments s
where t.owner = s.owner and t.table_name = s.segment_name
and t.owner = 'SCHEMA_NAME';

RichVRed

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

RichVRed

-- Value is in microseconds
-- Run this query at a couple regular intervals and take the delta to get
-- an idea of how much hard parsing is going on
select sn.statistic#, sn.name, s.value
from v$sysstat s, v$statname sn
where s.statistic# = sn.statistic#(+)
and sn.name in ('parse time cpu', 'parse count (hard)');

RichVRed

-- if an index isn't being used or is lightly used, can it be dropped to free up resources?
-- only reflective of statements in shared SQL area, to get a more accurate reflection on
-- indexes used in the DB, run this query several times a day and dump all the output into
-- a spreadhseet or something.
with index_usage as
(
    select pl.sql_id, pl.object_owner, pl.object_name, pl.operation,
        pl.options, count(1) as use_count
    from v$sql_plan pl

RichVRed

-- REPLACE <<sql id>> with actual sql id
select plan_table_output from table(dbms_xplan.display_cursor('<<sql id>>', null, 'typical'));

RichVRed

select pl.object_owner, pl.object_name,
    pl.sql_id, q.sql_text, q.module,
    pl.operation, pl.options, pl.cost, pl.cpu_cost, pl.io_cost,
    q.executions
from v$sql_plan pl, v$sql q
where pl.sql_id = q.sql_id
and ((pl.operation = 'TABLE ACCESS' and pl.options = 'FULL')
or (pl.operation = 'INDEX' and pl.options = 'FAST FULL SCAN')
or (pl.operation = 'INDEX' and pl.options = 'FULL SCAN (MIN/MAX)')
or (pl.operation = 'INDEX' and pl.options = 'FULL SCAN'))