Мой блог: 14 янв. 2015 г.

среда, 14 января 2015 г.

Загрузка SQL statements в SQL Tuning Set из CURSOR_CACHE

Параметры функции:

DBMS_SQLTUNE.SELECT_CURSOR_CACHE (
basic_filter        IN   VARCHAR2 := NULL,
object_filter       IN   VARCHAR2 := NULL,
ranking_measure1    IN   VARCHAR2 := NULL,
ranking_measure2    IN   VARCHAR2 := NULL,
ranking_measure3    IN   VARCHAR2 := NULL,
result_percentage   IN   NUMBER   := 1,
result_limit        IN   NUMBER   := NULL,
attribute_list      IN   VARCHAR2 := NULL)
RETURN sys.sqlset PIPELINED;

sqlset_name   The SQL tuning set name

basic_filter The SQL predicate to filter the SQL from the cursor cache defined on attributes of the
ELAPSED_TIME
CPU_TIME
BUFFER_GETS
DISK_READS
DIRECT_WRITES
ROWS_PROCESSED

object_filter Specifies the objects that should exist in the object list of selected SQL from the cursor cache

ranking_measure(n) An order-by clause on the selected SQL

result_percentage A filter which picks the top N% according to the ranking measure given. Note that this applies only if one ranking measure is given.

result_limit The top L(imit) SQL from the (filtered) source ranked by the ranking measure

attribute_list List of SQL statement attributes to return in the result. The possible values are:

    BASIC (default) -all attributes (such as execution statistics and binds) are returned except the plans The execution context is always part of the result.

    TYPICAL - BASIC + SQL plan (without row source statistics) and without object reference list

    ALL - return all attributes

    Comma separated list of attribute names this allows to return only a subset of SQL attributes:
    EXECUTION_STATISTICS, BIND_LIST, OBJECT_LIST, SQL_PLAN,SQL_PLAN_STATISTICS: similar to SQL_PLAN + row source statistics

Return Values

This function returns a one SQLSET_ROW per SQL_ID or PLAN_HASH_VALUE pair found in each data source.

Примеры запросов:

SELECT * FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE);


SELECT * FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
                             'parsing_schema_name <> ''SYS''' ));


SELECT sql_id, sql_text
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('buffer_gets > 500'))
ORDER BY sql_id;

SELECT *
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('sql_id = ''bc688uy91wzka'''));

SELECT sql_id, plan_hash_value
FROM table(dbms_sqltune.select_cursor_cache('sql_id = ''bc688uy91wzka'''))
ORDER BY sql_id, plan_hash_value;

SELECT *
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('module = ''MMON_SLAVE'''));

-- all statements that ran for at least five seconds
SELECT *
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('elapsed_time > 5000000'));


-- select all statements that pass a simple buffer_gets threshold and
-- are coming from an SCOTT user
SELECT *
FROM table(
DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
'buffer_gets > 100 and parsing_schema_name = ''SCOTT'''));


-- select all statements exceeding 5 seconds in elapsed time, but also
-- select the plans (by default we only select execution stats and binds
-- for performance reasons - in this case the SQL_PLAN attribute of sqlset_row
-- is NULL)
SELECT *
FROM table(dbms_sqltune.select_cursor_cache(
'elapsed_time > 5000000', NULL, NULL, NULL, NULL, 1, NULL,
'EXECUTION_STATISTICS, SQL_BINDS, SQL_PLAN'));

-- Select the top 100 statements in the cursor cache ordering by elapsed_time.
SELECT *
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
NULL, NULL, 'ELAPSED_TIME', NULL, NULL, 1, 100));


SELECT sql_id, substr(sql_text,1,20)
,disk_reads, cpu_time, elapsed_time
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('disk_reads > 1000000'))
ORDER BY sql_id;

SELECT sql_id, substr(sql_text,1,20), disk_reads
,cpu_time, elapsed_time
,buffer_gets, parsing_schema_name
FROM table(
DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
basic_filter => 'parsing_schema_name <> "SYS"'
,ranking_measure1 => 'cpu_time'
,result_limit => 10
));

SELECT sql_id, substr(sql_text,1,20)
,disk_reads, cpu_time, elapsed_time
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('parsing_schema_name <> "SYS"
AND elapsed_time > 1000000'))
ORDER BY sql_id;

-- Запрос к CURSOR_CACHE
SELECT *
FROM TABLE( DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
          basic_filter      => 'parsing_schema_name <> ''SYS'' AND disk_reads > 100',
          object_filter     => NULL,
          ranking_measure1 => NULL,
          ranking_measure2 => NULL,
          ranking_measure3 => NULL,
          result_percentage => 1,
          result_limit      => NULL,
          attribute_list    => 'ALL' ));

-- Удаляем SQL Tuning Set
BEGIN
DBMS_SQLTUNE.DROP_SQLSET(
    sqlset_name => 'my_sts');
END;
/

-- Создаем SQL Tuning Set
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
    sqlset_name => 'my_sts',
    description => 'SQL Tuning Set for loading from trace file');
END;
/

-- Загружаем SQL statements в SQL Tuning Set из CURSOR_CACHE
DECLARE
ref_cursor sys_refcursor;
BEGIN
   OPEN ref_cursor FOR
   SELECT value(p)
     FROM TABLE(
        DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
          basic_filter      => 'parsing_schema_name <> ''SYS'' AND disk_reads > 100',
          object_filter     => NULL,
          ranking_measure1 => NULL,
          ranking_measure2 => NULL,
          ranking_measure3 => NULL,
          result_percentage => 1,
          result_limit      => NULL,
          attribute_list    => 'ALL' )) p;
   DBMS_SQLTUNE.LOAD_SQLSET(
     sqlset_name => 'my_sts',
     populate_cursor => ref_cursor,
     sqlset_owner => 'SCOTT'
     );
CLOSE ref_cursor;
END;
/

-- Просмотр STS:

SELECT * FROM DBA_SQLSET_STATEMENTS;

-- Просмотр плана из STS:
SELECT * FROM table (
   DBMS_XPLAN.DISPLAY_SQLSET(
       sqlset_name => 'my_sts',
       sql_id => 'btd7m8k25qr9h',
       plan_hash_value => 1081635106
       ));

-- Планы из CURSOR_CACHE можно посмотреть так:

-- находим sql_id:

select * from table (dbms_sqltune.select_cursor_cache('sql_text like ''select /*MY_CRITICAL_SQL*/%'''));
select * from table (dbms_sqltune.select_cursor_cache('sql_id = ''4n3pdustvb0yk'''));

-- смотрим план:

select * from table (dbms_xplan.display_cursor('4n3pdustvb0yk'));
select * from table (dbms_xplan.display_cursor('4n3pdustvb0yk',0, 'BASIC'));
select * from table (dbms_xplan.display_cursor('4n3pdustvb0yk',0, 'ALL -projection'));
select * from table (dbms_xplan.display_cursor('4n3pdustvb0yk',0, 'ALL +peeked_binds'));
select * from table (dbms_xplan.display_cursor('4n3pdustvb0yk',0,'ALLSTATS'));
select * from table (dbms_xplan.display_cursor('4n3pdustvb0yk',0,'ALLSTATS LAST'));
select * from table (dbms_xplan.display_cursor('4n3pdustvb0yk',0,'ALLSTATS LAST +alias -predicate'));
select * from table (dbms_xplan.display_cursor('4n3pdustvb0yk',0,'ALLSTATS LAST +outline'));
select * from table (dbms_xplan.display_cursor('4n3pdustvb0yk',0,'ADVANCED'));
select * from table (dbms_xplan.display_cursor('4n3pdustvb0yk',0,'ADVANCED OUTLINE ALLSTATS LAST +PEEKED_BINDS'));

dbms_xplan.display_cursor(
sql_id          IN VARCHAR2 DEFAULT NULL,
cursor_child_no IN INTEGER DEFAULT 0,
format          IN VARCHAR2 DEFAULT 'TYPICAL')
RETURN dbms_xplan_type_table PIPELINED;

-- или через SQL monitor:

SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF
SELECT DBMS_SQLTUNE.report_sql_monitor(
sql_id => '&sql_id', type=>'TEXT' , report_level => 'ALL') from dual;
/

Загрузка SQL statements в SQL Tuning Set из AWR

-- Запрос к AWR
SELECT *
FROM TABLE(
     DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(
      begin_snap   => 415,
      end_snap     => 420,
      basic_filter => 'SQL_ID = ''0r1gq4aapgnxd''' )) p
WHERE parsing_schema_name = 'SCOTT';

-- Удаляем SQL Tuning Set
BEGIN
DBMS_SQLTUNE.DROP_SQLSET(
    sqlset_name => 'my_sts');
END;
/

-- Создаем SQL Tuning Set
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
    sqlset_name => 'my_sts',
    description => 'SQL Tuning Set for loading from trace file');
END;
/

-- Загружаем SQL statements в SQL Tuning Set из AWR
DECLARE
ref_cursor sys_refcursor;
BEGIN
   OPEN ref_cursor FOR
   SELECT value(p)
     FROM TABLE(
        DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(
         begin_snap   => 415,
         end_snap     => 420,
         basic_filter => 'SQL_ID = ''0r1gq4aapgnxd''' )) p;
   DBMS_SQLTUNE.LOAD_SQLSET(
     sqlset_name => 'my_sts',
     populate_cursor => ref_cursor,
     sqlset_owner => 'SCOTT'
     );
CLOSE ref_cursor;
END;
/

-- Просмотр STS:

SELECT * FROM DBA_SQLSET_STATEMENTS;

SELECT * FROM table (
   DBMS_XPLAN.DISPLAY_SQLSET(
       sqlset_name => 'my_sts',
       sql_id => '0r1gq4aapgnxd',
       plan_hash_value => 426858176
       ));

Список параметров функции select_workload_repository:

dbms_sqltune.select_workload_repository(
            BEGIN_SNAP        => 415,
            END_SNAP          => 420,
            BASIC_FILTER      => 'SQL_ID = ''64qdks642mqt2'' AND PLAN_HASH_VALUE = 3406458838',
            OBJECT_FILTER     => NULL,
            RANKING_MEASURE1 => 'disk_reads',
            RANKING_MEASURE2 => NULL,
            RANKING_MEASURE3 => NULL,
            RESULT_PERCENTAGE => 1,
            RESULT_LIMIT      => 250,
            ATTRIBUTE_LIST    => 'ALL',
            RECURSIVE_SQL     => 'Y'
)

Вместо BEGIN_SNAP и END_SNAP, можно указать BASELINE_NAME.

BEGIN_SNAP          Non-inclusive beginning snapshot ID

END_SNAP            Inclusive ending snapshot ID

BASELINE_NAME       Name of AWR baseline

BASIC_FILTER        SQL predicate to filter SQL statements from workload; if not set, then only SELECT, INSERT, UPDATE, DELETE, MERGE, and CREATE TABLE statements are captured.

OBJECT_FILTER       Not currently used

RANKING_MEASURE(n) Order by clause on selected SQL statement(s), such as elapsed_time, cpu_time, buffer_gets, disk_reads, and so on;
N can be 1, 2, or 3. The elapsed_time and cpu_time are measured in seconds.

RESULT_PERCENTAGE   Filter for choosing top N% for ranking measure

RESULT_LIMIT        Limit of the number of SQL statements returned in the result set

ATTRIBUTE_LIST      List of SQL statement attributes (TYPICAL, BASIC, ALL, and so on)

RECURSIVE_SQL       Include/exclude recursive SQL (HAS_RECURSIVE_SQL or NO_RECURSIVE_SQL)

cpu_time : Number of seconds
elapsed_time : Number of seconds
disk_reads : Number of reads from disk
buffer_gets : Number of reads from memory
rows_processed : Average number of rows
optimizer_cost : Calculated optimizer cost
executions : Total execution count of SQL statement

Примеры использования параметров:

BASIC_FILTER   => 'parsing_schema_name <> "SYS"'
( 'parsing_schema_name not in (''DBSNMP'',''SYS'',''ORACLE_OCM'')',)

RANKING_MEASURE1 => 'cpu_time'
( 'elapsed_time', buffer_gets, disk_reads, )

Еще несколько примеров :

SELECT snap_id, instance_number, end_interval_time

FROM dba_hist_snapshot

ORDER BY snap_id;

SELECT sql_id

,substr(sql_text,1,20)

,disk_reads, cpu_time, elapsed_time

FROM table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(415,420,

null, null, 'disk_reads',null, null, null, 10))

ORDER BY disk_reads DESC;

==========================================================

SELECT sql_id, substr(sql_text,1,20)

,disk_reads, cpu_time, elapsed_time, parsing_schema_name

FROM table(

DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(415,420,

'parsing_schema_name <> ''SYS''',

NULL, NULL,NULL,NULL, 1, NULL, 'ALL'));

==========================================================

SELECT sql_id, substr(sql_text,1,20)

,disk_reads, cpu_time, elapsed_time, buffer_gets, parsing_schema_name

FROM table(

DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(

begin_snap => 415

,end_snap => 420

,basic_filter => 'parsing_schema_name <> ''SYS'''

,ranking_measure1 => 'buffer_gets'

,result_limit => 10

));

==========================================================

SELECT MAX(snap_id) bsnap

FROM dba_hist_snapshot

WHERE begin_interval_time < sysdate-7;

SELECT MAX(snap_id) esnap

FROM dba_hist_snapshot;

COL sql_text FORMAT A40

COL sql_id FORMAT A15

COL parsing_schema_name FORMAT A15

COL cpu_seconds FORMAT 999,999,999,999,999

SET LONG 10000 LINES 132 PAGES 100 TRIMSPOOL ON

SELECT sql_id, sql_text

,disk_reads, cpu_time cpu_seconds, elapsed_time, buffer_gets, parsing_schema_name

FROM table(

DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(

begin_snap => 415

,end_snap => 420

,basic_filter => 'parsing_schema_name <> ''SYS'''

,ranking_measure1 => 'cpu_time'

,result_limit => 10

));

-- Посмотреть менялся ли план у запроса:

select   snap_id, plan_hash_value
    from dba_hist_sqlstat
   where snap_id in (415,420) and sql_id='0r1gq4aapgnxd'
order by snap_id desc;

-- Посмотреть из AWR какие планы изменялись:

select   a.sql_id, a.plan_hash_value snap_id_1_plan, b.plan_hash_value snap_id_2_plan
    from dba_hist_sqlstat a, dba_hist_sqlstat b
   where (a.snap_id = 415 and b.snap_id = 420)
     and (a.sql_id = b.sql_id)
     and (a.plan_hash_value != b.plan_hash_value)
order by a.sql_id;

-- по всему репозиторию:

select distinct sql_id, plan_hash_value, f snapshot,
                (select begin_interval_time
                   from dba_hist_snapshot
                  where snap_id = f) snapdate
           from (select sql_id, plan_hash_value,
                        first_value (snap_id) over (partition by sql_id, plan_hash_value order by snap_id) f
                   from (select   sql_id, plan_hash_value, snap_id,
                                  count (distinct plan_hash_value) over (partition by sql_id) a
                             from dba_hist_sqlstat
                            where plan_hash_value > 0
                         order by sql_id)
                  where a > 1)
       order by sql_id, f;

-- Планы из AWR можно посмотреть так:

select * from table(dbms_xplan.display_awr('5k5207588w9ry'));
select * from table(dbms_xplan.display_awr('5k5207588w9ry', 1388734953 ));
select * from table(dbms_xplan.display_awr('5k5207588w9ry', 1388734953, null, 'BASIC'));
select * from table(dbms_xplan.display_awr('5k5207588w9ry', 1388734953, null, 'ALL -projection'));
select * from table(dbms_xplan.display_awr('5k5207588w9ry', 1388734953, null, 'ALL +peeked_binds'));
select * from table(dbms_xplan.display_awr('5k5207588w9ry', 1388734953, null, 'ALLSTATS'));
select * from table(dbms_xplan.display_awr('5k5207588w9ry', 1388734953, null, 'ALLSTATS LAST'));
select * from table(dbms_xplan.display_awr('5k5207588w9ry', 1388734953, null, 'ALLSTATS LAST +alias -predicate'));
select * from table(dbms_xplan.display_awr('5k5207588w9ry', 1388734953, null, 'ALLSTATS LAST +outline'));
select * from table(dbms_xplan.display_awr('5k5207588w9ry', 1388734953, null, 'ADVANCED'));
select * from table(dbms_xplan.display_awr('5k5207588w9ry', 1388734953, null, 'ADVANCED OUTLINE ALLSTATS LAST +PEEKED_BINDS'));

dbms_xplan.display_awr(
sql_id          IN VARCHAR2,
plan_hash_value IN INTEGER DEFAULT NULL,
db_id           IN INTEGER DEFAULT NULL,
format          IN VARCHAR2 DEFAULT 'TYPICAL')
RETURN dbms_xplan_type_table PIPELINED;

Установка пакетов в Linux

Oracle Linux 7

# cd /etc/yum.repos.d
# wget http://public-yum.oracle.com/public-yum-ol7.repo

Oracle Linux 6

# cd /etc/yum.repos.d
# wget http://public-yum.oracle.com/public-yum-ol6.repo

Oracle Linux 5

# cd /etc/yum.repos.d
# wget http://public-yum.oracle.com/public-yum-el5.repo

rpm -q --qf '%{NAME}-%{VERSION}-%{RELEASE}(%{ARCH})\n' binutils \
compat-libcap1 \
compat-libstdc++-33 \
gcc \
gcc-c++ \
glibc \
glibc-devel \
ksh \
libgcc \
libstdc++ \
libstdc++-devel \
libaio \
libaio-devel \
make \
sysstat | grep not

yum install binutils -y
yum install compat-libcap1 -y
yum install compat-libstdc++-33 -y
yum install gcc -y
yum install gcc-c++ -y
yum install glibc -y
yum install glibc-devel -y
yum install ksh -y
yum install libgcc -y
yum install libstdc++ -y
yum install libstdc++-devel -y
yum install libaio -y
yum install libaio-devel -y
yum install make -y
yum install sysstat -y

rpm -q --qf '%{NAME}-%{VERSION}-%{RELEASE}(%{ARCH})\n' compat-libstdc++-33.i686 \
glibc.i686 \
glibc-devel.i686 \
libgcc.i686 \
libstdc++.i686 \
libstdc++-devel.i686 \
libaio.i686 \
libaio-devel.i686 | grep not

yum install compat-libstdc++-33.i686 -y
yum install glibc.i686 -y
yum install glibc-devel.i686 -y
yum install libgcc.i686 -y
yum install libstdc++.i686 -y
yum install libstdc++-devel.i686 -y
yum install libaio.i686 -y
yum install libaio-devel.i686 -y

Опционально можно установить:

yum install libXext -y
yum install libXext.i686 -y
yum install libXtst -y
yum install libXtst.i686 -y
yum install libX11 -y
yum install libX11.i686 -y
yum install libXau -y
yum install libXau.i686 -y
yum install libxcb -y
yum install libxcb.i686 -y
yum install libXi -y
yum install libXi.i686 -y

Для работы с источниками данных ODBC:

yum install unixODBC -y
yum install unixODBC-devel -y

OpenSSH требует, чтобы был установлен пакет zlib-devel,
который содержит заголовочные файлы и библиотеки нужные программам,
использующим библиотеки zlib компрессии и декомпрессии.

yum install zlib-devel -y

Мой блог

среда, 14 января 2015 г.

Загрузка SQL statements в SQL Tuning Set из CURSOR_CACHE

Загрузка SQL statements в SQL Tuning Set из AWR

Установка пакетов в Linux

Поиск по этому блогу

Архив блога

Ярлыки

Постоянные читатели

Обо мне

Мой блог

среда, 14 января 2015 г.

Загрузка SQL statements в SQL Tuning Set из CURSOR_CACHE

Загрузка SQL statements в SQL Tuning Set из AWR

Установка пакетов в Linux

Поиск по этому блогу

Архив блога

Ярлыки

Постоянные читатели

Обо мне

среда, 14 января 2015 г.