Skip to content

Oracle 12c Adaptive Query Optimization overhead

Oracle 12c Adaptive Query Optimization overhead published on Комментариев к записи Oracle 12c Adaptive Query Optimization overhead нет

Trying to find out overhead of new Adaptive Query Optimization feature

use perf to find out process cpu usage ( eg:perf stat -e task-clock -p 9576 sleep 5 )
Screen Shot 2016-08-24 at 21.45.07

Screen Shot 2016-08-24 at 21.46.40
to disable adaptive features I used

alter system set optimizer_features_enable='11.2.0.1';

so the result is:

3000 hpares

3000 hpares

3000 hpares

3000 hpares

3000 hpares

avg

adaptive off

1724,46

1786,04

1696,22

1687,89

1690,89

1717,10

adaptive on

1803,79

1815,98

1966,07

1804,35

1819,35

1841,91

overhead

4,40

1,65

13,73

6,45

7,06

6,78

ps: lotshparses.sql link

How to resolve SQL object and column names all the way to base tables and columns in Oracle?

How to resolve SQL object and column names all the way to base tables and columns in Oracle? published on Комментариев к записи How to resolve SQL object and column names all the way to base tables and columns in Oracle? нет

original from Tanel Poder

was looking for source of v$restore point view, helped article from Tanel =)

 alter session set "_dump_qbc_tree"=1;

result is :

 SELECT  "ADDR","INDX","INST_ID","CON_ID","NRSNAME","NRSINCARN","NRSSCN","NRSTIME","NRSFLAGS","NRSRSPTIME","NRSRID","NRSSTM" FROM "SYS"."X$KCCNRS"   "X$KCCNRS" WHERE (con_id IN (0, 3) );

upd: also it can be done with:

 select view_definition from v$fixed_view_definition where view_name='GV$RESTORE_POINT';

script to find tables with nonindexed FK

script to find tables with nonindexed FK published on Комментариев к записи script to find tables with nonindexed FK нет

need to pass schema name as firs script argument

select table_name, column_name
              from ( select c.table_name, cc.column_name, cc.position column_position
                      from   all_constraints c, all_cons_columns cc
                      where  c.constraint_name = cc.constraint_name
                        and c.constraint_type = 'R' and c.owner=cc.owner and c.owner='&1'
                     minus
                     select i.table_name, ic.column_name, ic.column_position
                      from all_indexes i, all_ind_columns ic
                      where i.index_name = ic.index_name and i.owner=ic.table_owner and i.owner='&1');

Troubleshooting ‘enq: TX — index contention’ Waits (Doc ID 873243.1)

Troubleshooting ‘enq: TX — index contention’ Waits (Doc ID 873243.1) published on Комментариев к записи Troubleshooting ‘enq: TX — index contention’ Waits (Doc ID 873243.1) нет

today faced with ‘enq: TX — index contention’

Screen Shot 2016-08-05 at 21.08.15

awr shows

Screen Shot 2016-08-05 at 20.01.41

helped to make partition index on nonpartition table:

drop index ARADMIN.IT3060;
CREATE UNIQUE INDEX ARADMIN.IT3060_P ON "ARADMIN"."T3060" ("C1") global partition by hash (C1) partitions 16 parallel 16 ;
drop index ARADMIN.T3060_IDX_12;
CREATE index ARADMIN.T3060_IDX_12 ON "ARADMIN"."T3060"  ("C536870937", "C7", "C536870938", "C1") global partition by hash ("C536870937", "C7", "C536870938", "C1")  partitions 16 parallel 16 TABLESPACE "ARSYSTEM" ;
drop index ARADMIN.T3060_IDX_11;
CREATE index ARADMIN.T3060_IDX_11 ON "ARADMIN"."T3060"  ("C536870922", "C7", "C536870941") global partition by hash ("C536870922", "C7", "C536870941")  partitions 16 parallel 16 TABLESPACE "ARSYSTEM" ;

CRS-6706: Oracle Clusterware Release patch level (‘2222840392’) does not match Software patch level (‘0’). Oracle Clusterware cannot be started.

CRS-6706: Oracle Clusterware Release patch level (‘2222840392’) does not match Software patch level (‘0’). Oracle Clusterware cannot be started. published on Комментариев к записи CRS-6706: Oracle Clusterware Release patch level (‘2222840392’) does not match Software patch level (‘0’). Oracle Clusterware cannot be started. нет

faced with

COMMAND EXECUTION FAILURE :
Using configuration parameter file: /ora01/app/grid/12.1.0.2/OPatch/opatchautotemp_rac2/patchwork/crs/install/crsconfig_params
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'rac2'
CRS-2673: Attempting to stop 'ora.crf' on 'rac2'
CRS-2673: Attempting to stop 'ora.mdnsd' on 'rac2'
CRS-2673: Attempting to stop 'ora.gpnpd' on 'rac2'
CRS-2677: Stop of 'ora.mdnsd' on 'rac2' succeeded
CRS-2677: Stop of 'ora.gpnpd' on 'rac2' succeeded
CRS-2677: Stop of 'ora.crf' on 'rac2' succeeded
CRS-2673: Attempting to stop 'ora.gipcd' on 'rac2'
CRS-2677: Stop of 'ora.gipcd' on 'rac2' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'rac2' has completed
CRS-4133: Oracle High Availability Services has been stopped.
CRS-6706: Oracle Clusterware Release patch level ('2222840392') does not match Software patch level ('0'). Oracle Clusterware cannot be started.
CRS-4000: Command Start failed, or completed with errors.
2016/08/01 10:21:54 CLSRSC-117: Failed to start Oracle Clusterware stack

helped solution from:
CRS-1153: There was an error setting Oracle Clusterware to rolling patch mode. (Doc ID 1943498.1)
CRS-6706: Oracle Clusterware Release patch level (‘nnn’) does not match Software patch level (‘mmm’) (Doc ID 1639285.1)

first one:

[root@rac2 ~]# crsctl stop rollingpatch
CRS-1171: Rejecting rolling patch mode change because the patch level is not consistent across all nodes in the cluster. The patch level on nodes rac1 is not the same as the patch level [2369764000] found on nodes rac2.
CRS-4000: Command Stop failed, or completed with errors.

with no luck

second is successfull:

[root@rac1 ~]# kfod op=patchlvl
-------------------
Current Patch level
===================
2369764000
[root@rac1 ~]# kfod op=patches
---------------
List of Patches
===============
19396455
19769480
20299023
20831110
21359755
21436941
21948341
21948344
21948354


[root@rac2 ~]# kfod op=patches
---------------
List of Patches
===============
19396455
[root@rac2 ~]# kfod op=patchlvl
-------------------
Current Patch level
===================
2222840392

[root@rac1 ~]# sh /ora01/app/grid/12.1.0.2/crs/install/rootcrs.sh -patch
Using configuration parameter file: /ora01/app/grid/12.1.0.2/crs/install/crsconfig_params
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'rac1'
...
2016/08/01 10:57:54 CLSRSC-4015: Performing install or upgrade action for Oracle Trace File Analyzer (TFA) Collector.

2016/08/01 10:59:46 CLSRSC-4005: Failed to patch Oracle Trace File Analyzer (TFA) Collector. Grid Infrastructure operations will continue.
.....
Oracle Clusterware active version on the cluster is [12.1.0.2.0]. The cluster upgrade state is [NORMAL]. The cluster active patch level is [2369764000].

postgresql cat effective non default parameters from config file

postgresql cat effective non default parameters from config file published on Комментариев к записи postgresql cat effective non default parameters from config file нет
 cat postgresql.conf |egrep -v "^[[:blank:]]*#" | grep -v '^$'
data_directory='/u01/postgres/9.5/main/pgdata'
hba_file='/u01/postgres/9.5/main/conf/pg_hba.conf'
ident_file='/u01/postgres/9.5/main/conf/pg_ident.conf'
listen_addresses = '*'		# what IP address(es) to listen on;
max_connections = 300			# (change requires restart)
shared_buffers = 4096MB			# min 128kB
work_mem = 256MB				# min 64kB
maintenance_work_mem = 512MB		# min 1MB
dynamic_shared_memory_type = posix	# the default is the first option
checkpoint_timeout = 15min		# range 30s-1h
max_wal_size = 10GB
min_wal_size = 1GB
checkpoint_completion_target = 0.9	# checkpoint target duration, 0.0 - 1.0
archive_mode = on		# enables archiving; off, on, or always
archive_command = 'cp -i %p /u01/postgres/9.5/main/recovery/walarchive/%f </dev/null'		# command to use to archive a logfile segment
max_wal_senders = 3		# max number of walsender processes
max_replication_slots = 2	# max number of replication slots
hot_standby = on			# "on" allows queries during recovery
effective_cache_size = 8GB
log_destination = 'stderr'		# Valid values are combinations of
logging_collector = on			# Enable capturing of stderr and csvlog
log_directory = 'pg_log'		# directory where log files are written,
log_filename = 'postgresql-%Y-%m-%d.log'	# log file name pattern,
log_truncate_on_rotation = on		# If on, an existing log file with the
log_rotation_age = 1d			# Automatic rotation of logfiles will
log_rotation_size = 0			# Automatic rotation of logfiles will
log_min_duration_statement = 300	# -1 is disabled, 0 logs all statements
log_checkpoints = on
log_connections = on
log_disconnections = on
log_line_prefix = '%t [%p]: [%l-1] db=%d,appname=%a,user=%u,client=%h '			# special values:
log_lock_waits = on			# log lock waits >= deadlock_timeout
log_temp_files = 0			# log temporary files equal or larger
log_timezone = 'Europe/Moscow'
autovacuum_max_workers = 5		# max number of autovacuum subprocesses
autovacuum_vacuum_scale_factor = 0.01	# fraction of table size before vacuum
autovacuum_analyze_scale_factor = 0.05	# fraction of table size before analyze
datestyle = 'iso, mdy'
timezone = 'Europe/Moscow'
lc_messages = 'en_US.utf8'			# locale for system error message
lc_monetary = 'en_US.utf8'			# locale for monetary formatting
lc_numeric = 'en_US.utf8'			# locale for number formatting
lc_time = 'en_US.utf8'				# locale for time formatting
default_text_search_config = 'pg_catalog.english'

how to find which traces I can run

how to find which traces I can run published on Комментариев к записи how to find which traces I can run нет

from oracle-l@freelists.org

Here’s how you may find some of these yourself.

In this case you were looking for information on tracing locks, so this grep shows matching info from the messages file
(linux assumed here, with Gnu grep)

grep -E «^1[0-9]{4}.*\block» $ORACLE_HOME/rdbms/mesg/oraus.msg

10028, 00000, «Dump trace information during lock / resource latch cleanup»
10219, 00000, «monitor multi-pass row locking»
10621, 00000, «specify retry count for online index build cleanup DML lock get»
10623, 00000, «test synchronized flow of SORT rows into FOR UPDATE lock phase»
10629, 00000, «force online index build to backoff and retry DML lock upgrade»
10988, 00000, «event to get exclusive lock during materialized view refresh in IAS»
12766,00000, «ACMS failed to acquire share-mode mount lock»
12843, 00000, «pdml lock not held properly on the table»
13771, 00000, «cannot obtain exclusive lock %s on \»SQL Tuning Set\» \»%s\» owned by user \»%s\»»
14403, 00000, «cursor invalidation detected after getting DML partition lock»
14700, 00000, «Object(s) owned by SYS cannot be locked by non-SYS user»
15003, 00000, «diskgroup \»%s\» already mounted in another lock name space»
15069, 00000, «ASM file ‘%s’ not accessible; timed out waiting for lock»
15150, 00000, «instance lock mode ‘%s’ conflicts with other ASM instance(s)»
16040, 00000, «Destination archive log file is locked.»
16415, 0000, «Event to enable lock dumping»
16641, 0000, «failure to acquire broker configuration metadata lock»

The output may be restricted to show only ‘lock’, and not include ‘locked’ or ‘locking’

grep -wE «^1[0-9]{4}.*\block» $ORACLE_HOME/rdbms/mesg/oraus.msg
10028, 00000, «Dump trace information during lock / resource latch cleanup»
10621, 00000, «specify retry count for online index build cleanup DML lock get»
10623, 00000, «test synchronized flow of SORT rows into FOR UPDATE lock phase»
10629, 00000, «force online index build to backoff and retry DML lock upgrade»
10988, 00000, «event to get exclusive lock during materialized view refresh in IAS»
12766,00000, «ACMS failed to acquire share-mode mount lock»
12843, 00000, «pdml lock not held properly on the table»
13771, 00000, «cannot obtain exclusive lock %s on \»SQL Tuning Set\» \»%s\» owned by user \»%s\»»
14403, 00000, «cursor invalidation detected after getting DML partition lock»
15003, 00000, «diskgroup \»%s\» already mounted in another lock name space»
15069, 00000, «ASM file ‘%s’ not accessible; timed out waiting for lock»
15150, 00000, «instance lock mode ‘%s’ conflicts with other ASM instance(s)»
16415, 0000, «Event to enable lock dumping»
16641, 0000, «failure to acquire broker configuration metadata lock»

Keeping in mind that in Oracle lock has synonyms, such as enqueue

grep -wE «^1[0-9]{4}.*\benqueue» $ORACLE_HOME/rdbms/mesg/oraus.msg
10425, 00000, «enable global enqueue operations event trace»
10427, 00000, «enable global enqueue service traffic controller event trace»
10428, 00000, «enable tracing of global enqueue service cached resource»
10429, 00000, «enable tracing of global enqueue service IPC calls»
10433, 00000, «global enqueue service testing event»
10434, 00000, «enable tracing of global enqueue service multiple LMS»
10435, 00000, «enable tracing of global enqueue service deadlock detetction»
10437, 00000, «enable trace of global enqueue service S optimized resources»
10440, 00000, «enable global enqueue service inquire resource modes trace»
10666, 00000, «Do not get database enqueue name»
10706, 00000, «Print out information about global enqueue manipulation»
10862, 00000, «resolve default queue owner to current user in enqueue/dequeue»
10868, 00000, «event to enable interop patch for AQ enqueue options»
10999, 00000, «do not get database enqueue name»
12806, 00000, «could not get background process to hold enqueue»
16146, 00000, «%scontrol file enqueue unavailable»
19573, 00000, «cannot obtain %s enqueue for datafile %s»
19725, 00000, «can not acquire plug-in enqueue»

Still not finding anything useful, the -w parameter can be removed from grep to allow variations, such as ‘enqueued’ and ‘enqueues’

grep -E «^1[0-9]{4}.*\benqueue» $ORACLE_HOME/rdbms/mesg/oraus.msg
10425, 00000, «enable global enqueue operations event trace»
10427, 00000, «enable global enqueue service traffic controller event trace»
10428, 00000, «enable tracing of global enqueue service cached resource»
10429, 00000, «enable tracing of global enqueue service IPC calls»
10433, 00000, «global enqueue service testing event»
10434, 00000, «enable tracing of global enqueue service multiple LMS»
10435, 00000, «enable tracing of global enqueue service deadlock detetction»
10437, 00000, «enable trace of global enqueue service S optimized resources»
10440, 00000, «enable global enqueue service inquire resource modes trace»
10666, 00000, «Do not get database enqueue name»
10704, 00000, «Print out information about what enqueues are being obtained»
10706, 00000, «Print out information about global enqueue manipulation»
10862, 00000, «resolve default queue owner to current user in enqueue/dequeue»
10868, 00000, «event to enable interop patch for AQ enqueue options»
10999, 00000, «do not get database enqueue name»
12806, 00000, «could not get background process to hold enqueue»
16146, 00000, «%scontrol file enqueue unavailable»
19573, 00000, «cannot obtain %s enqueue for datafile %s»
19725, 00000, «can not acquire plug-in enqueue»

Since you are looking for trace (debug) specific information, a perusal of the oraus.msg file reveals:

/ Pseudo-error debugging events:
/ Error codes 10000 .. 10999 are reserved for debug event codes that are
/ not really errors.

So searches can be further restricted by changing the range of numbers to search for, showing only debug events:

grep -E «^10[0-9]{3}.*\benqueue» $ORACLE_HOME/rdbms/mesg/oraus.msg
10425, 00000, «enable global enqueue operations event trace»
10427, 00000, «enable global enqueue service traffic controller event trace»
10428, 00000, «enable tracing of global enqueue service cached resource»
10429, 00000, «enable tracing of global enqueue service IPC calls»
10433, 00000, «global enqueue service testing event»
10434, 00000, «enable tracing of global enqueue service multiple LMS»
10435, 00000, «enable tracing of global enqueue service deadlock detetction»
10437, 00000, «enable trace of global enqueue service S optimized resources»
10440, 00000, «enable global enqueue service inquire resource modes trace»
10666, 00000, «Do not get database enqueue name»
10704, 00000, «Print out information about what enqueues are being obtained»
10706, 00000, «Print out information about global enqueue manipulation»
10862, 00000, «resolve default queue owner to current user in enqueue/dequeue»
10868, 00000, «event to enable interop patch for AQ enqueue options»
10999, 00000, «do not get database enqueue name»

Notice the use of -i now to ignore case

> grep -iE «^10[0-9]{3}.*sql\*net» $ORACLE_HOME/rdbms/mesg/oraus.msg
10079, 00000, «trace data sent/received via SQL*Net»

That particular event was one I found in the messages file a couple years ago, and it was instrumental in discovering and issue with SqlNet, thin clients and lobs.
The issue was far too much network traffic, proved out by this trace event.
The solution was to use a 12.1.0.2 thick client.

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
Principal Consultant at Pythian
Pythian Blog http://www.pythian.com/blog/author/still/
Oracle Blog: http://jkstill.blogspot.com
Home Page: http://jaredstill.com

Oracle 11g – How to force a sql_id to use a plan_hash_value using SQL Baselines (saved from google cache )

Oracle 11g – How to force a sql_id to use a plan_hash_value using SQL Baselines (saved from google cache ) published on Комментариев к записи Oracle 11g – How to force a sql_id to use a plan_hash_value using SQL Baselines (saved from google cache ) нет




Oracle 11g – How to force a sql_id to use a plan_hash_value using SQL Baselines | rmoff















rmoff

June 28, 2011

Oracle 11g – How to force a sql_id to use a plan_hash_value using SQL Baselines

Filed under: etl, oracle, performance, plan management, sql plan baseline — rmoff @ 14:13

Here’s a scenario that’ll be depressingly familiar to most reading this: after ages of running fine, and no changes to the code, a query suddenly starts running for magnitudes longer than it used to.

In this instance it was an ETL step which used to take c.1 hour, and was now at 5 hours and counting. Since it still hadn’t finished, and the gods had conspired to bring down Grid too (unrelated), I generated a SQL Monitor report to see what was happening:

select DBMS_SQLTUNE.REPORT_SQL_MONITOR(
   type=>'HTML',
   report_level=>'ALL',sql_id=>'939abmqmvcc4d') as report
FROM dual;

(h/t to Martin Berger for this)

It showed a horrendous explain plan:

A very naughty plan

Using Kerry Osborne’s script to look at the plan_hash_value over time from AWR, it was clear that the CBO had picked a new, bad, explain plan.

So we knew the sql_id, and we knew the plan_hash_value of the plan which we wanted the CBO to use. But how to do this?

Back to Kerry Osborne again, and his article about SQL Plan Baselines. He (and others) write in detail about how and what SQL Plan Baselines are, but in essence it lets you tell Oracle which plan to use (or optionally, prefer) for a given sql_id.

Since the desired plan_hash_value was no longer in the cursor cache, we could get it back from AWR, loaded in via a SQL Tuning Set. Here’s the code with in-line comments explaining the function of each block:

/* 
Set up a SQL Baseline using known-good plan, sourced from AWR snapshots
https://rnm1978.wordpress.com/

In this example, sql_id is 939abmqmvcc4d and the plan_hash_value of the good plan that we want to force is 1239572551
*/

-- Drop SQL Tuning Set (STS)
BEGIN
  DBMS_SQLTUNE.DROP_SQLSET(
    sqlset_name => 'MySTS01');
END;

-- Create SQL Tuning Set (STS)
BEGIN
  DBMS_SQLTUNE.CREATE_SQLSET(
    sqlset_name => 'MySTS01',
    description => 'SQL Tuning Set for loading plan into SQL Plan Baseline');
END;

-- Populate STS from AWR, using a time duration when the desired plan was used
--  List out snapshot times using :   SELECT SNAP_ID, BEGIN_INTERVAL_TIME, END_INTERVAL_TIME FROM dba_hist_snapshot ORDER BY END_INTERVAL_TIME DESC;
--  Specify the sql_id in the basic_filter (other predicates are available, see documentation)
DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur FOR
    SELECT VALUE(P)
    FROM TABLE(
       dbms_sqltune.select_workload_repository(begin_snap=>22673, end_snap=>22710,basic_filter=>'sql_id = ''939abmqmvcc4d''',attribute_list=>'ALL')
              ) p;
     DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name=> 'MySTS01', populate_cursor=>cur);
  CLOSE cur;
END;
/

-- List out SQL Tuning Set contents to check we got what we wanted
SELECT 
  first_load_time          ,
  executions as execs              ,
  parsing_schema_name      ,
  elapsed_time  / 1000000 as elapsed_time_secs  ,
  cpu_time / 1000000 as cpu_time_secs           ,
  buffer_gets              ,
  disk_reads               ,
  direct_writes            ,
  rows_processed           ,
  fetches                  ,
  optimizer_cost           ,
  sql_plan                ,
  plan_hash_value          ,
  sql_id                   ,
  sql_text
   FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(sqlset_name => 'MySTS01')
             );

-- List out the Baselines to see what's there
SELECT * FROM dba_sql_plan_baselines ;

-- Load desired plan from STS as SQL Plan Baseline
-- Filter explicitly for the plan_hash_value here if you want
DECLARE
my_plans pls_integer;
BEGIN
  my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
    sqlset_name => 'MySTS01', 
    basic_filter=>'plan_hash_value = ''1239572551'''
    );
END;
/

-- List out the Baselines
SELECT * FROM dba_sql_plan_baselines ;

Now when the query’s run, it will use the desired plan.

Things to note:

  • In 10g and 11gR1 the default for SELECT_WORKLOAD_REPOSITORY is to return only BASIC information, which excludes the plan! So DBMS_SPM.LOAD_PLANS_FROM_SQLSET doesn’t load any plans.
    • It doesn’t throw a warning either, which it could sensibly, since the STS has no plan, and it can see that</grumble>
    • This changes to TYPICAL in 11gR2 (thanks Surachart!)
  • Parameter “optimizer_use_sql_plan_baselines” must be set to TRUE for a baseline to be used
  • Flush the cursor cache after loading the baseline to make sure it gets picked up on next execution of the sql_id

References:

Thanks to John Hallas for his help with this problem.

2 Comments

  1. very useful
    thx !

    Comment by djeday84 — July 21, 2011 @ 08:12

  2. […] не претендую на оригинальность, все взято отсюда и отсюда 55.755786 37.617633 Leave a Comment LikeBe the first to like this post. […]

    Pingback by perfomance « djeday84 — July 21, 2011 @ 10:37

RSS feed for comments on this post.

Sorry, the comment form is closed at this time.

The Silver is the New Black Theme. Blog at WordPress.com.




Follow

Get every new post delivered to your Inbox.

Join 33 other followers

















    how to vizualize table usage by queries with awr and exel

    how to vizualize table usage by queries with awr and exel published on Комментариев к записи how to vizualize table usage by queries with awr and exel нет

    generate pivot script:

    select listagg( ''''||sql_id||'''',',') within group (order by 1 )from (select distinct (sql_id) from  dba_hist_sql_plan where object_name='OPN_HIS' ) ;
    

    use info in awr to build a graph

    select  * from (
    select s.sql_id,
    sum( nvl(s.executions_delta,0)) execs,TO_CHAR (ss.begin_interval_time, 'DD.MM.YYYY HH24') date#
    -- sum((buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta))) avg_lio
    from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS, dba_hist_sqltext st
    where ss.snap_id = S.snap_id
    and ss.instance_number = S.instance_number
    and executions_delta > 0
    and elapsed_time_delta > 0
       and st.sql_id=s.sql_id
       and st.sql_text not like '/* SQL Analyze%'
       and s.sql_id in  ( select p.sql_id from dba_hist_sql_plan p where p.object_name='OPN_HIS') 
    and ss.begin_interval_time > sysdate-7
    group by TO_CHAR (ss.begin_interval_time, 'DD.MM.YYYY HH24'),s.sql_id )
    pivot ( sum(execs)  for sql_id in (
    '04chua8g507qc','0ppqkga09s858','13nb5fgqggy5y','2y61btbkgbt99','3akp3rn03dw0m','4hwvc5b6kg9s9','5btym214ffk95','5fjnq328z8cvm','5haf8w0xm1qrz','5yrbchx0n2ww9','70qkbhpxdyhmd',
    '7459pp1x54x2g','7hap4j3ar5fu8','9ym4u1trvzcq8','aabhxfrznx8ty','b4ynhjjwwdjf9','bp0sfgzv774u4','btm5kxhrc34qy','bxfhw0z4cnmxp','by4vbkw71nrax','dpnr7csjy3657','f3n5pm0ck9yuw','f452wsrxch6js' )
      );
    
    

    result :

    Screen Shot 2016-05-31 at 12.05.45

    Primary Sidebar

    Яндекс.Метрика