Июнь 2015 — bushmelev-aa.ru

virtualbox shared storage create on linux

25.06.2015 Bushmelev Anton Комментариев нет

create shared storage:
data:

for i in {1..10};do vboxmanage createhd --filename asm_$i --size 10240 --format VDI --variant Fixed; done

OCR:

for i in {1..3};do vboxmanage createhd --filename vote_$i --size 1200 --format VDI --variant Fixed; done

make disk shareable:

for i in `ls -1 *.vdi`; do vboxmanage modifyhd $i --type shareable; done

add disk to virtual machine

c=1; for i in `ls rac*.vdi -1` ; do c=$((c+=1)) echo vboxmanage storageattach oel6-1 --storagectl "SATA" --port $c --type hdd --medium $i --mtype shareable; done
c=1; for i in `ls rac*.vdi -1` ; do c=$((c+=1))  vboxmanage storageattach oel6-1 --storagectl "SATA" --port $c --type hdd --medium $i --mtype shareable; done

make disk partitions:
1. generate create partion table :

fdisk -l | grep GB | awk '{ print "parted -s " $2 " mklabel  gpt"}' | sed 's/://g

need to automate create partition script

 echo -e "-1" | (parted -a optimal /dev/sdb mkpart primary 1 )

no free vm to test

2. generate create asm disk:

[root@oel6-1 ~]# fdisk -l | grep 10.7 | awk '{ print "oracleasm createdisk RAC_ASM"NR " " $2 "1"  }' | sed 's/://g'
oracleasm createdisk RAC_ASM1 /dev/sdb1
oracleasm createdisk RAC_ASM2 /dev/sdc1
oracleasm createdisk RAC_ASM3 /dev/sdd1
oracleasm createdisk RAC_ASM4 /dev/sde1
oracleasm createdisk RAC_ASM5 /dev/sdf1
oracleasm createdisk RAC_ASM6 /dev/sdg1
oracleasm createdisk RAC_ASM7 /dev/sdh1
oracleasm createdisk RAC_ASM8 /dev/sdi1
oracleasm createdisk RAC_ASM9 /dev/sdj1
oracleasm createdisk RAC_ASM10 /dev/sdk1
[root@oel6-1 ~]# oracleasm createdisk RAC_ASM1 /dev/sdb1
Writing disk header: done
Instantiating disk: done
[root@oel6-1 ~]# oracleasm createdisk RAC_ASM2 /dev/sdc1
Writing disk header: done
Instantiating disk: done
[root@oel6-1 ~]# oracleasm createdisk RAC_ASM3 /dev/sdd1
Writing disk header: done
Instantiating disk: done
[root@oel6-1 ~]# oracleasm createdisk RAC_ASM4 /dev/sde1
Writing disk header: done
Instantiating disk: done
[root@oel6-1 ~]# oracleasm createdisk RAC_ASM5 /dev/sdf1
Writing disk header: done
Instantiating disk: done
[root@oel6-1 ~]# oracleasm createdisk RAC_ASM6 /dev/sdg1
Writing disk header: done
Instantiating disk: done
[root@oel6-1 ~]# oracleasm createdisk RAC_ASM7 /dev/sdh1
Writing disk header: done
Instantiating disk: done
[root@oel6-1 ~]# oracleasm createdisk RAC_ASM8 /dev/sdi1
Writing disk header: done
Instantiating disk: done
[root@oel6-1 ~]# oracleasm createdisk RAC_ASM9 /dev/sdj1
Writing disk header: done
Instantiating disk: done
[root@oel6-1 ~]# oracleasm createdisk RAC_ASM10 /dev/sdk1
Writing disk header: done
Instantiating disk: done

Normal storage how to:

 for i in {1..5};do vboxmanage createhd --filename stdb1_asm_$i --size 10240 --format VDI ; done
 c=1; for i in `ls stdb1_asm*.vdi -1` ; do c=$((c+=1))  vboxmanage storageattach stdb1 --storagectl "SATA" --port $c --type hdd --medium $i ; done

offline oracle agent install on windows

25.06.2015 Bushmelev Anton 2 комментария

get list of supported platform:

[oracle@monitoring tmp]$  emcli get_supported_platforms
-----------------------------------------------
Version = 12.1.0.3.0
 Platform = Linux x86-64
-----------------------------------------------
Version = 12.1.0.4.0
 Platform = Microsoft Windows x64 (64-bit)
-----------------------------------------------
Version = 12.1.0.3.0
 Platform = Oracle Solaris on SPARC (64-bit)
-----------------------------------------------
Version = 12.1.0.4.0
 Platform = Linux x86-64
-----------------------------------------------
Version = 12.1.0.3.0
 Platform = Microsoft Windows (32-bit)
-----------------------------------------------
Version = 12.1.0.3.0
 Platform = Microsoft Windows x64 (64-bit)
-----------------------------------------------
Platforms list displayed successfully.

get agent :

[oracle@monitoring tmp]$ emcli get_agentimage -destination=/tmp/win_agent -platform="Microsoft Windows x64 (64-bit)" -version=12.1.0.4.0
 === Partition Detail ===
Space free : 1 GB
Space required : 1 GB
Check the logs at /oracle/.emcli/get_agentimage_2015-04-13_18-42-53-PM.log
Downloading /tmp/win_agent/12.1.0.4.0_AgentCore_233.zip
File saved as /tmp/win_agent/12.1.0.4.0_AgentCore_233.zip
Downloading /tmp/win_agent/12.1.0.4.0_PluginsOneoffs_233.zip
File saved as /tmp/win_agent/12.1.0.4.0_PluginsOneoffs_233.zip
Downloading /tmp/win_agent/unzip
File saved as /tmp/win_agent/unzip
ERROR: Command /tmp/win_agent/unzip /tmp/win_agent/12.1.0.4.0_AgentCore_233.zip agentcoreimage.zip -d /tmp/win_agent execution failed.
RECOMMENDATION: If your agent image platform and the emcli client setup is on a different platform then you need to set the environment variable ZIP_LOC to absolute path to the zip utility which is greater than or equal to version 3.0.
Example: setenv ZIP_LOC /usr/local/bin/zip
[oracle@monitoring tmp]$

copy to destination & install:

C:\Users\sql> C:\oracle_agent\distrib\agentDeploy.bat AGENT_BASE_DIR=c:\oracle_agent\agent12c4 RESPONSE_FILE=C:\oracle_agent\distrib\agent3.rsp -ignorePrereqs


C:\Users\sql> C:\oracle_agent\distrib\agentDeploy.bat AGENT_BASE_DIR=c:\oracle_agent\agent12c4 RESPONSE_FILE=C:\oracle_agent\distrib\agent3.rsp -ignorePrereqs
C:\oracle_agent\distrib
Present working directory:C:\oracle_agent\distrib
Archive location:C:\oracle_agent\distrib  directory
AGENT_BASE_DIR
AGENT_BASE_DIR
c:\oracle_agent\agent12c4
Agent base directory:c:\oracle_agent\agent12c4
c:\oracle_agent\agent12c4
RESPONSE_FILE
C:\oracle_agent\distrib\agent3.rsp
-ignorePrereqs
Режим вывода команд на экран (ECHO) отключен.
Agent base directory:c:\oracle_agent\agent12c4
OMS Host:
Agent image loc :"C:\oracle_agent\distrib"
c:\oracle_agent\agent12c4 configonlyfalse
Reading the properties file: "C:\oracle_agent\distrib"\agentimage.properties
Скопировано файлов:         1.
This is the version 12.1.0.4.0
This is the type core
This is the aru id 233
"Validating the OMS_HOST &amp; EM_UPLOAD_PORT"
"c:\oracle_agent\agent12c4\core\12.1.0.4.0"
"Executing command : c:\oracle_agent\agent12c4\core\12.1.0.4.0\jdk\bin\java -classpath c:\oracle_agent\agent12c4\core\12.1.0.4.0\jlib\agentInstaller.jar:c:\oracle_agent\agent12c4\core\12.1.0.4.0\oui\jlib\OraInstaller.jar oracle.sysman.agent.installer.AgentInstaller c:\or

Validating oms host &amp; port with url: http://192.168.226.20:4900/empbs/genwallet
Validating oms host &amp; port with url: https://192.168.226.20:4900/empbs/genwallet
Return status:3-oms https port is passed
"C:\oracle_agent\distrib"\12.1.0.4.0_PluginsOneoffs_233.zip
"Executing command : c:\oracle_agent\agent12c4\core\12.1.0.4.0\jdk\bin\java -classpath c:\oracle_agent\agent12c4\core\12.1.0.4.0\jlib\OraInstaller.jar:c:\oracle_agent\agent12c4\core\12.1.0.4.0\sysman\jlib\emInstaller.jar:c:\oracle_agent\agent12c4\core\12.1.0.4.0\jlib\xml

Cloning the agent home...
Executing command: cmd /c C:\oracle_agent\agent12c4\core\12.1.0.4.0\oui\bin\setup.exe -debug -noconsole -ignoreSysPrereqs   -clone -forceClone -silent -waitForCompletion -nowait ORACLE_HOME=C:\oracle_agent\agent12c4\core\12.1.0.4.0 -responseFile C:\oracle_agent\distrib\a

Cloning of agent home completed successfully

Attaching sbin home...
Executing command: cmd /c C:\oracle_agent\agent12c4\core\12.1.0.4.0\oui\bin\setup.exe -debug -noconsole -ignoreSysPrereqs   -attachHome -waitForCompletion -nowait ORACLE_HOME=C:\oracle_agent\agent12c4\sbin ORACLE_HOME_NAME=sbin12c1 -force

Attach home for sbin home completed successfully.

Updating home dependencies...
Executing command: cmd /c C:\oracle_agent\agent12c4\core\12.1.0.4.0\oui\bin\setup.exe -debug -noconsole -ignoreSysPrereqs   -updateHomeDeps -waitForCompletion HOME_DEPENDENCY_LIST={C:\oracle_agent\agent12c4\sbin:C:\oracle_agent\agent12c4\core\12.1.0.4.0} -invPtrLoc C:\or

Update home dependency completed successfully.
Executing command: C:\oracle_agent\agent12c4\core\12.1.0.4.0\oui\bin\runConfig.bat ORACLE_HOME=C:\oracle_agent\agent12c4\core\12.1.0.4.0 RESPONSE_FILE=c:\oracle_agent\agent12c4\core\12.1.0.4.0\agent.rsp ACTION=configure MODE=perform COMPONENT_XML={oracle.sysman.top.agent

ERROR: Agent Configuration Failed SEVERE:emctl secure agent command has failed with status=1SEVERE:emctl secure agent command has failed with status=1SEVERE:emctl secure agent command has failed with status=1

Agent Deploy Log Location:c:\oracle_agent\agent12c4\core\12.1.0.4.0\cfgtoollogs\agentDeploy\agentDeploy_2015-06-25_12-40-40-PM.log

fix local host file to include hostname for CC12 host

after


C:\Users\sql> c:\oracle_agent\agent12c4\core\12.1.0.4.0\bin\emctl status agent
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
Agent is Not Running

C:\Users\sql> ;c:\oracle_agent\agent12c4\core\12.1.0.4.0\bin\emctl start agent
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.
Служба "Oracleagent12cAgent" запускается.............
Служба "Oracleagent12cAgent" успешно запущена.

C:\Users\sql> c:\oracle_agent\agent12c4\core\12.1.0.4.0\bin\emctl secure agent truepasswd
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.
Agent successfully stopped...   Done.
Securing agent...   Started.
Agent successfully restarted...   Done.
Securing agent...   Successful.


C:\Users\sql>C:\oracle_agent\agent12c4\core\12.1.0.4.0\bin\emctl config agent addinternaltargets
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.

C:\Users\sql>;

extract from rsp file:


RESPONSEFILE_VERSION=2.2.1.0.0
s_agentHomeName="agent12gR1"
OMS_HOST=monitoring.sys.local
EM_UPLOAD_PORT=4900
AGENT_REGISTRATION_PASSWORD=truepasswd
AGENT_INSTANCE_HOME=C:\oracle_agent\agent12c4\
AGENT_PORT=3872
b_startAgent=true
s_agentHomeName=agent12c4_home
s_agentSrvcName="OracleAgent12c"
EM_INSTALL_TYPE="AGENT"

if faced with

Validating the OMS_HOST EM_UPLOAD_PORT
ERROR: OMS_HOST cannot be null.

do the same in one string

C:\Users\sql> C:\oracle_agent\distrib\agentDeploy.bat AGENT_BASE_DIR=C:\oraclea_agent\agent12c4 OMS_HOST=192.168.226.20 EM_UPLOAD_PORT=4900 AGENT_INSTANCE_HOME=C:\oracle_agent\agent12c4\ s_agentHomeName=agent12c4_home s_agentSrvcName=Oracleagent12cAgent b_forceInstCheck=true AGENT_PORT=3872 EM_INSTALL_TYPE=AGENT AGENT_REGISTRATION_PASSWORD=xxxxxx -ignorePrereqs

upload ms sql agent plugin to agent


[oracle@monitoring ~]$ emcli get_plugin_deployment_status
Displaying status for the latest deployment activity(deployment ID 260)
Plug-in Deployment/Undeployment Status

Destination          : Management Agent - MKSSQL1:3872
Plug-in Name         : Microsoft SQLServer Database
Version              : 12.1.0.5.0
ID                   : oracle.em.smss
Content              : Plug-in
Action               : Deployment
Status               : Deploying
Steps Info:
---------------------------------------- ------------------------- ------------------------- ----------
Step                                     Start Time                End Time                  Status
---------------------------------------- ------------------------- ------------------------- ----------
Submit job for deployment                6/25/15 12:04:41 PM MSK   6/25/15 12:04:41 PM MSK   Success

Initialize                               6/25/15 12:04:43 PM MSK   6/25/15 12:04:52 PM MSK   Success

Validate Environment                     6/25/15 12:04:52 PM MSK   N/A                       Running

---------------------------------------- ------------------------- ------------------------- ----------

Deploy MS SQL SERVER PLUGIN

Oracle: latch: cache buffers chains

24.06.2015 Bushmelev Anton Комментариев нет

save at my blog, original

Waits on the cache buffer chains latch, ie the wait event «latch: cache buffers chains» happen when there is extremely high and concurrent access to the same block in a database. Access to a block is normally a fast operation but if concurrent users access a block fast enough, repeatedly then simple access to the block can become an bottleneck. The most common occurance of cbc (cache buffer chains) latch contention happens when multiple users are running nest loop joins on a table and accessing the table driven into via an index. Since the NL join is basically a

  For all rows in i
     look up a value in j  where j.field1 = i.val
  end loop

then table j’s index on field1 will get hit for every row returned from i. Now if the lookup on i returns a lot of rows and if multiple users are running this same query then the index root block is going to get hammered on the index j(field1).

In order to solve a CBC latch bottleneck we need to know what SQL is causing the bottleneck and what table or index that the SQL statement is using is causing the bottleneck.

From ASH data this is fairly easy:

    select
          count(*),
          sql_id,
          nvl(o.object_name,ash.current_obj#) objn,
          substr(o.object_type,0,10) otype,
          CURRENT_FILE# fn,
          CURRENT_BLOCK# blockn
    from  v$active_session_history ash
        , all_objects o
    where event like 'latch: cache buffers chains'
      and o.object_id (+)= ash.CURRENT_OBJ#
    group by sql_id, current_obj#, current_file#,
                   current_block#, o.object_name,o.object_type
    order by count(*)
    /

From the out put it looks like we have both the SQL (at least the id, we can get the text with the id) and the block:

    CNT SQL_ID        OBJN     OTYPE   FN BLOCKN
    ---- ------------- -------- ------ --- ------
      84 a09r4dwjpv01q MYDUAL   TABLE    1  93170

But the block actually is probably left over from a recent IO and not actually the CBC hot block though it might be.
We can investigate further to get more information by looking at P1, P2 and P3 for the CBC latch wait. How can we find out what P1, P2 and P3 mean? by looking them up in V$EVENT_NAME:

    select * from v$event_name
    where name = 'latch: cache buffers chains'

    EVENT#     NAME                         PARAMETER1 PARAMETER2 PARAMETER3
    ---------- ---------------------------- ---------- ---------- ----------
            58 latch: cache buffers chains     address     number      tries

So P1 is the address of the latch for the cbc latch wait.
Now we can group the CBC latch waits by the address and find out what address had the most waits:

    select
        count(*),
        lpad(replace(to_char(p1,'XXXXXXXXX'),' ','0'),16,0) laddr
    from v$active_session_history
    where event='latch: cache buffers chains'
    group by p1
    order by count(*);

    COUNT(*)  LADDR
    ---------- ----------------
          4933 00000004D8108330

In this case, there is only one address that we had waits for, so now we can look up what blocks (headers actually) were at that address

   select o.name, bh.dbarfil, bh.dbablk, bh.tch
    from x$bh bh, obj$ o
    where tch > 5
      and hladdr='00000004D8108330'
      and o.obj#=bh.obj
    order by tch

    NAME        DBARFIL DBABLK  TCH
    ----------- ------- ------ ----
    EMP_CLUSTER       4    394  120

We look for the block with the highest «TCH» or «touch count». Touch count is a count of the times the block has been accesses. The count has some restrictions. The count is only incremented once every 3 seconds, so even if I access the block 1 million times a second, the count will only go up once every 3 seconds. Also, and unfortunately, the count gets zeroed out if the block cycles through the buffer cache, but probably the most unfortunate is that this analysis only works when the problem is currently happening. Once the problem is over then the blocks will usually get pushed out of the buffer cache.

In the case where the CBC latch contention is happening right now we can run all of this analysis in one query

   select
            name, file#, dbablk, obj, tch, hladdr
    from x$bh bh
        , obj$ o
     where
           o.obj#(+)=bh.obj and
          hladdr in
    (
        select ltrim(to_char(p1,'XXXXXXXXXX') )
        from v$active_session_history
        where event like 'latch: cache buffers chains'
        group by p1
        having count(*) > 5
    )
       and tch > 5
    order by tch

example output

    NAME          FILE# DBABLK    OBJ TCH HLADDR
    ------------- ----- ------ ------ --- --------
    BBW_INDEX         1 110997  66051  17 6BD91180
    IDL_UB1$          1  54837     73  18 6BDB8A80
    VIEW$             1   6885     63  20 6BD91180
    VIEW$             1   6886     63  24 6BDB8A80
    DUAL              1   2082    258  32 6BDB8A80
    DUAL              1   2081    258  32 6BD91180
    MGMT_EMD_PING     3  26479  50312 272 6BDB8A80

This can be misleading, as TCH gets set to 0 every rap around the LRU and it only gets updated once every 3 seconds, so in this case DUAL was my problem table not MGMT_EMD_PING

Deeper Analysis from Tanel Poder

http://blog.tanelpoder.com/2009/08/27/latch-cache-buffers-chains-latch-contention-a-better-way-for-finding-the-hot-block/comment-page-1/#comment-2437

Using Tanel’s ideas here’s a script to get the objects that we have the most cbc latch waits on

    col object_name for a35
    col cnt for 99999

    SELECT
      cnt, object_name, object_type,file#, dbablk, obj, tch, hladdr
    FROM (
      select count(*) cnt, rfile, block from (
        SELECT /*+ ORDERED USE_NL(l.x$ksuprlat) */
          --l.laddr, u.laddr, u.laddrx, u.laddrr,
          dbms_utility.data_block_address_file(to_number(object,'XXXXXXXX')) rfile,
          dbms_utility.data_block_address_block(to_number(object,'XXXXXXXX')) block
        FROM
           (SELECT /*+ NO_MERGE */ 1 FROM DUAL CONNECT BY LEVEL <= 100000) s,
           (SELECT ksuprlnm LNAME, ksuprsid sid, ksuprlat laddr,
           TO_CHAR(ksulawhy,'XXXXXXXXXXXXXXXX') object
            FROM x$ksuprlat) l,
           (select  indx, kslednam from x$ksled ) e,
           (SELECT
                        indx
                      , ksusesqh     sqlhash
       , ksuseopc
       , ksusep1r laddr
                 FROM x$ksuse) u
        WHERE LOWER(l.Lname) LIKE LOWER('%cache buffers chains%')
         AND  u.laddr=l.laddr
         AND  u.ksuseopc=e.indx
         AND  e.kslednam like '%cache buffers chains%'
        )
       group by rfile, block
       ) objs,
         x$bh bh,
         dba_objects o
    WHERE
          bh.file#=objs.rfile
     and  bh.dbablk=objs.block
     and  o.object_id=bh.obj
    order by cnt
    ;

    CNT  OBJECT_NAME       TYPE  FILE#  DBABLK    OBJ   TCH  HLADDR
    ---- ----------------- ----- ----- ------- ------ ----- --------
       1 WB_RETROPAY_EARNS TABLE     4   18427  52701  1129 335F7C00
       1 WB_RETROPAY_EARNS TABLE     4   18194  52701  1130 335F7C00
       3 PS_RETROPAY_RQST  TABLE     4   13253  52689  1143 33656D00
       3 PS_RETROPAY_RQST  INDEX     4   13486  52692   997 33656D00
       3 WB_JOB            TABLE     4   14443  52698   338 335B9080
       5 PS_RETROPAY_RQST  TABLE     4   13020  52689   997 33656D00
       5 WB_JOB            TABLE     4   14676  52698   338 335B9080
       5 WB_JOB            TABLE     4   13856  52698   338 335F7C00
       6 WB_JOB            TABLE     4   13623  52698   338 335F7C00
       7 WB_JOB            TABLE     4   14909  52698   338 335B9080
     141 WB_JOB            TABLE     4   15142  52698   338 335B9080
    2513 WB_JOB            INDEX     4   13719  52699   997 33656D00

Why do we get cache buffers chains latch contention?

In order to understand why we get CBC latch contention we have to understand what the CBC latch protects. The CBC latch protects information controlling the buffer cache. Here is a schematic of computer memory and the Oracle processes, SGA and the main components of the SGA:

The buffer cache holds in memory versions of datablocks for faster access. Can you imagine though how we find a block we want in the buffer cache? The buffer cache doesn’t have a index of blocks it contains and we certainly don’t scan the whole cache looking for the block we want (though I have heard that as a concern when people increase the size of there buffer cache). The way we find a block in the buffer cache is by taking the block’s address, ie it’s file and block number and hashing it. What’s hashing? A simple example of hashing is the «Modulo» function

1 mod 4 = 1
2 mod 4 = 2
3 mod 4 = 3
4 mod 4 = 0
5 mod 4 = 1
6 mod 4 = 2
7 mod 4 = 3
8 mod 4 = 0

Using «mod 4» as a hash funtion creates 4 possible results. These results are used by Oracle as «buckets» or identifiers of locations to store things. The things in this case will be block headers.

Block headers are meta data about data block including pointers to the actual datablock as well as pointers to the other headers in the same bucket.

The block headers in the hash buckets are connected via a doubly linked list. One link points forward the other points backwards

The resulting layout looks like

the steps to find a block in the cache are

If there are a lot of sessions concurrently accessing the same buffer header (or buffer headers in the same bucket) then the latch that protects that bucket will get hot and users will have to wait getting «latch: cache buffers chains» wait.

Two ways this can happen (among probably several others)

For the nested loops example, Oracle will in some (most?) cases try and pin the root block of the index because Oracle knows we will be using it over and over. When a block is pinned we don’t have to use the cbc latch. There seem to be cases (some I think might be bugs) where the root block doesn’t get pinned. (I want to look into this more — let me know if you have more info)

One thing that can make CBC latch contention worse is if a session is modifying the data block that users are reading because readers will clone a block with uncommitted changes and roll back the changes in the cloned block:

all these clone copies will go in the same bucket and be protected by the same latch:

How many copies of a block are in the cache?

    select
           count(*)
         , name
         , file#
         , dbablk
         , hladdr
    from   x$bh bh
              , obj$ o
    where
          o.obj#(+)=bh.obj and
          hladdr in
    (
        select ltrim(to_char(p1,'XXXXXXXXXX') )
        from v$active_session_history
        where event like 'latch: cache%'
        group by p1
    )
    group by name,file#, dbablk, hladdr
    having count(*) > 1
    order by count(*);

    CNT NAME        FILE#  DBABLK HLADDR
    --- ---------- ------ ------- --------
     14 MYDUAL          1   93170 2C9F4B20

Notice that the number of copies, 14, is higher the the max number of copies allowed set by «_db_block_max_cr_dba = 6» in 10g. The reason is this value is just a directive not a restriction. Oracle tries to limit the number of copies.

Solutions

Find SQL ( Why is application hitting the block so hard? )
Possibly change application logic
Eliminate hot spots
Nested loops, possibly
Hash Partition the index with hot block
Use Hash Join instead of Nested loop join
Use Hash clusters
Look up tables (“select language from lang_table where …”)
Change application
Use plsql function
Spread data out to reduce contention, like set PCTFREE to 0 and recreate the table so that there is only one row per block
Select from dual
Possibly use x$dual
Note starting in 10g Oracle uses the «fast dual» table (ie x$dual) automatically when executing a query on dual as long as the column «dummy» is not accessed. Accessing dummy would be cases like
select count(*) from dual;
select * from dual;
select dummy from dual;
an example of not accessing «dummy» would be:
select 1 from dual;
select sysdate from dual;

Updates, inserts , select for update on blocks while reading those blocks
Cause multiple copies and make things worse

What would OEM do?

In DB Optimizer:

Other References
http://blog.tanelpoder.com/2009/08/27/latch-cache-buffers-chains-latch-contention-a-better-way-for-finding-the-hot-block
http://www.pythian.com/news/1135/tuning-latch-contention-cache-buffers-chain-latches/
http://www.oaktable.net/content/latch-cache-buffer-chains-small-index-primary-key-caused-concurrent-batch-scripts-select-sta#comment-6

Index Rebuild 10g

http://www.programering.com/a/MzNzkzMwATM.html

perf-tools linux

23.06.2015 Bushmelev Anton Комментариев нет

perf-tools
looks awesome
example :

[root@nb-abushmelev perf-tools] # ./iosnoop -Q -ts -Qp 28951
Tracing block I/O issued by PID 28951. Ctrl-C to end.
STARTs          ENDs            COMM         PID    TYPE DEV      BLOCK        BYTES     LATms
439471.905014   439471.909309   ora_ckpt_orc 28951  WS   8,0      89272384     16384      4.29
439471.909341   439471.914798   ora_ckpt_orc 28951  WS   8,0      0            0          5.46
439471.914874   439471.914930   ora_ckpt_orc 28951  WS   8,0      99987520     16384      0.06
439471.914948   439471.918243   ora_ckpt_orc 28951  WS   8,0      0            0          3.30
439474.919898   439474.919962   ora_ckpt_orc 28951  WS   8,0      89272384     16384      0.06
439474.920002   439474.925042   ora_ckpt_orc 28951  WS   8,0      0            0          5.04
439474.925099   439474.925156   ora_ckpt_orc 28951  WS   8,0      99987520     16384      0.06
439474.925170   439474.927063   ora_ckpt_orc 28951  WS   8,0      0            0          1.89
439477.928636   439477.928700   ora_ckpt_orc 28951  WS   8,0      89272384     16384      0.06
439477.928717   439477.932240   ora_ckpt_orc 28951  WS   8,0      0            0          3.52
439477.932309   439477.932365   ora_ckpt_orc 28951  WS   8,0      99987520     16384      0.06
439477.932391   439477.934258   ora_ckpt_orc 28951  WS   8,0      0            0          1.87

rman catalog sbt_tape backup piece

18.06.2015 Bushmelev Anton Комментариев нет

RMAN> configure channel device type 'SBT_TAPE' parms='ENV=(NB_ORA_SERVER=dc1-nb01,NB_ORA_CLIENT=DC1-DWHODI)';

new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS  'ENV=(NB_ORA_SERVER=dc1-nb01,NB_ORA_CLIENT=DC1-DWHODI)';
new RMAN configuration parameters are successfully stored

now catalog works fine:

RMAN> catalog device type 'SBT_TAPE'  backuppiece 'al_11820_1_834711630';

allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=3 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Veritas NetBackup for Oracle - Release 7.5 (2012020807)
cataloged backup piece
backup piece handle=al_11820_1_834711630 RECID=11820 STAMP=834950531

PRCD-1229 : after manual upgrade of oracle rac

17.06.2015 Bushmelev Anton 1 комментарий

[oracle@p00xxxdb01 ~]$ srvctl status database -d p00xxx
PRCD-1027 : Failed to retrieve database p00xxx
PRCD-1229 : An attempt to access configuration of database p00xxx was rejected because its version 11.2.0.2.0 differs from the program version 11.2.0.4.0. Instead run the program from /oracle/app/base/db/11.2.0.2.
[oracle@p00xxxdb01 ~]$ srvctl upgrade database -d p00xxx -o /oracle/app/base/db/11.2.0.4
[oracle@p00xxxdb01 ~]$

Query optimization:

16.06.2015 Bushmelev Anton Комментариев нет

awr_top2 script

18:44:12 (3)system@some_database> @awr_top2

 In |               |                |                                |     Top |        | Samp |       |   Wai |  User |  Sys |      |      |   Con |       |      |
 st | Time          | Top SQL        | Top event                      | blocker |    AAS |  les |   CPU |    ts |    IO |   IO | Conf |  Net |   cur |   App | LGWR |  RAC
--- | ------------- | -------------- | ------------------------------ | ------- | ------ | ---- | ----- | ----- | ----- | ---- | ---- | ---- | ----- | ----- | ---- | ----
  1 | 16-JUN 18:14  | c9p3ns1cusnmx  | ON CPU                         |     570 |    1.5 |   40 |    56 |     4 |     5 |    3 |    0 |    0 |     0 |     0 |    2 |    3
    | 16-JUN 18:15  | 3fp6nwg71dbna  | ON CPU                         |     570 |    1.3 |   59 |    74 |     4 |     3 |    4 |    0 |    0 |     0 |     0 |    4 |    0
    | 16-JUN 18:16  | 3fp6nwg71dbna  | ON CPU                         | <NULL>  |    1.7 |   60 |   101 |     0 |     0 |    0 |    0 |    0 |     1 |     0 |    0 |    0
    | 16-JUN 18:17  | 3fp6nwg71dbna  | ON CPU                         | <NULL>  |    1.2 |   60 |    74 |     0 |     0 |    1 |    0 |    0 |     0 |     0 |    0 |    0
    | 16-JUN 18:18  | c9p3ns1cusnmx  | ON CPU                         | <NULL>  |    1.3 |   60 |    75 |     3 |     2 |    1 |    0 |    1 |     0 |     0 |    0 |    1
    | 16-JUN 18:19  | 3fp6nwg71dbna  | ON CPU                         |     570 |    1.8 |   60 |   102 |     4 |     1 |    2 |    0 |    0 |     0 |     0 |    2 |    1
    | 16-JUN 18:20  | 3fp6nwg71dbna  | ON CPU                         |     570 |    1.3 |   60 |    75 |     2 |     0 |    5 |    0 |    0 |     0 |     0 |    2 |    0
    | 16-JUN 18:21  | <NULL>         | ON CPU                         | <NULL>  |    1.0 |   60 |    61 |     0 |     0 |    0 |    0 |    0 |     0 |     0 |    0 |    0
    | 16-JUN 18:22  | 3fp6nwg71dbna  | ON CPU                         | <NULL>  |    1.7 |   60 |    95 |     4 |     1 |    4 |    0 |    0 |     0 |     0 |    0 |    0
    | 16-JUN 18:23  | 3fp6nwg71dbna  | ON CPU                         |     570 |    1.5 |   59 |    87 |     3 |     0 |    3 |    0 |    0 |     0 |     0 |    2 |    0
    | 16-JUN 18:24  | f711myt0q6cma  | ON CPU                         |     570 |    1.1 |   60 |    64 |     1 |     0 |    2 |    0 |    0 |     0 |     0 |    1 |    0
    | 16-JUN 18:25  | 3fp6nwg71dbna  | ON CPU                         | <NULL>  |    1.2 |   60 |    72 |     0 |     0 |    0 |    0 |    0 |     0 |     0 |    0 |    0
    | 16-JUN 18:26  | 3fp6nwg71dbna  | ON CPU                         | <NULL>  |    1.6 |   60 |    92 |     1 |     0 |    2 |    0 |    0 |     0 |     0 |    0 |    1
    | 16-JUN 18:27  | 3fp6nwg71dbna  | ON CPU                         |     570 |    1.5 |   60 |    78 |     9 |     1 |    8 |    0 |    0 |     0 |     0 |    2 |    1
    | 16-JUN 18:28  | gjfr89gj2ngfu  | ON CPU                         |     570 |    1.1 |   60 |    62 |     5 |     1 |    5 |    0 |    0 |     0 |     0 |    2 |    0
    | 16-JUN 18:29  | 3fp6nwg71dbna  | ON CPU                         |     570 |    1.3 |   60 |    77 |     3 |     0 |    6 |    0 |    0 |     0 |     0 |    1 |    0
    | 16-JUN 18:30  | 3fp6nwg71dbna  | ON CPU                         |     570 |    1.4 |   59 |    80 |     2 |     0 |    2 |    0 |    0 |     0 |     0 |    2 |    1
    | 16-JUN 18:31  | dqrrfrf2d0h8q  | ON CPU                         | <NULL>  |    1.0 |   60 |    62 |     0 |     0 |    2 |    0 |    0 |     0 |     0 |    0 |    0
    | 16-JUN 18:32  | f1y8kbhh6v9sv  | ON CPU                         |     570 |    1.2 |   60 |    69 |     1 |     0 |    2 |    0 |    0 |     0 |     0 |    1 |    0
    | 16-JUN 18:33  | c9p3ns1cusnmx  | ON CPU                         |     570 |    1.4 |   60 |    75 |    10 |     3 |    5 |    0 |    0 |     0 |     0 |    4 |    2
    | 16-JUN 18:34  | cwd64da6cnm9j  | ON CPU                         | <NULL>  |    1.1 |   60 |    64 |     3 |     0 |    2 |    0 |    0 |     0 |     0 |    2 |    0
    | 16-JUN 18:35  | cwd64da6cnm9j  | ON CPU                         |     570 |    1.2 |   60 |    65 |     9 |     1 |    4 |    0 |    0 |     0 |     0 |    9 |    0
    | 16-JUN 18:36  | 5s7umwd3ppb7w  | ON CPU                         |     789 |    1.9 |   60 |    95 |    19 |     2 |    1 |    0 |    0 |     0 |     0 |    1 |   15
    | 16-JUN 18:37  | c9p3ns1cusnmx  | ON CPU                         |     570 |    1.2 |   59 |    67 |     3 |     1 |    1 |    0 |    0 |     0 |     0 |    2 |    2
    | 16-JUN 18:38  | d56fs2a7v6rkq  | ON CPU                         |     570 |    1.1 |   60 |    64 |     1 |     0 |    3 |    0 |    0 |     0 |     0 |    1 |    0
    | 16-JUN 18:39  | <NULL>         | ON CPU                         |     570 |    1.0 |   60 |    61 |     1 |     0 |    3 |    0 |    0 |     0 |     0 |    1 |    0
    | 16-JUN 18:40  | c9p3ns1cusnmx  | ON CPU                         | <NULL>  |    1.4 |   60 |    82 |     3 |     1 |    2 |    0 |    0 |     0 |     0 |    0 |    2
    | 16-JUN 18:41  | c9p3ns1cusnmx  | ON CPU                         |     570 |    1.2 |   60 |    66 |     5 |     0 |    5 |    0 |    0 |     0 |     0 |    3 |    1
    | 16-JUN 18:42  | 3fp6nwg71dbna  | ON CPU                         |     570 |    1.5 |   60 |    78 |    14 |     0 |   10 |    0 |    0 |     0 |     0 |   10 |    4
    | 16-JUN 18:43  | c9p3ns1cusnmx  | ON CPU                         |     570 |    1.1 |   60 |    63 |     3 |     0 |    7 |    0 |    0 |     0 |     0 |    3 |    0
    | 16-JUN 18:44  | c9p3ns1cusnmx  | ON CPU                         |     570 |    1.5 |   21 |    26 |     6 |     0 |    5 |    0 |    0 |     1 |     0 |    2 |    2

  3 | 16-JUN 18:14  | d3tcbyxaa65ku  | ON CPU                         | <NULL>  |    5.3 |   40 |   188 |    24 |    23 |    1 |    0 |    0 |     0 |     0 |    0 |    0
    | 16-JUN 18:15  | d3tcbyxaa65ku  | ON CPU                         | <NULL>  |    1.8 |   60 |    98 |    12 |    15 |    0 |    0 |    0 |     0 |     0 |    0 |    0
    | 16-JUN 18:16  | f5b45ajkg4rdd  | enq: TA - contention           |     854 |     .3 |   59 |    18 |     1 |     0 |    2 |    0 |    0 |     0 |     0 |    1 |    0
    | 16-JUN 18:17  | d3tcbyxaa65ku  | direct path read               |     570 |    2.3 |   60 |    81 |    59 |    52 |    2 |    0 |    0 |     0 |     0 |    4 |    1
    | 16-JUN 18:18  | d3tcbyxaa65ku  | ON CPU                         |     854 |    3.2 |   60 |   145 |    48 |    44 |    2 |    0 |    0 |     0 |     0 |    2 |    0
    | 16-JUN 18:19  | d3tcbyxaa65ku  | ON CPU                         |     854 |    3.9 |   59 |   209 |    21 |    20 |    1 |    0 |    0 |     0 |     0 |    0 |    1
    | 16-JUN 18:20  | d3tcbyxaa65ku  | ON CPU                         |     570 |    4.5 |   60 |   222 |    46 |    44 |    6 |    0 |    0 |     0 |     0 |    2 |    0
    | 16-JUN 18:21  | d3tcbyxaa65ku  | ON CPU                         |     854 |    4.6 |   60 |   245 |    30 |    30 |    0 |    0 |    0 |     0 |     0 |    0 |    0
    | 16-JUN 18:22  | d3tcbyxaa65ku  | ON CPU                         |     570 |    4.7 |   60 |   249 |    33 |    26 |    6 |    0 |    0 |     0 |     0 |    5 |    1
    | 16-JUN 18:23  | d3tcbyxaa65ku  | ON CPU                         |     854 |    4.2 |   60 |   178 |    71 |    69 |    2 |    0 |    0 |     0 |     0 |    1 |    0
    | 16-JUN 18:24  | d3tcbyxaa65ku  | ON CPU                         |     854 |    4.7 |   60 |   231 |    53 |    50 |    2 |    0 |    0 |     0 |     0 |    0 |    0
    | 16-JUN 18:25  | d3tcbyxaa65ku  | ON CPU                         | <NULL>  |    4.4 |   60 |   227 |    34 |    31 |    1 |    0 |    0 |     0 |     0 |    0 |    3
    | 16-JUN 18:26  | d3tcbyxaa65ku  | ON CPU                         |     854 |    4.5 |   59 |   228 |    37 |    36 |    2 |    0 |    0 |     0 |     0 |    1 |    0
    | 16-JUN 18:27  | d3tcbyxaa65ku  | ON CPU                         |     570 |    4.7 |   60 |   229 |    50 |    47 |    4 |    0 |    0 |     0 |     0 |    3 |    0
    | 16-JUN 18:28  | d3tcbyxaa65ku  | ON CPU                         | <NULL>  |    4.6 |   60 |   250 |    25 |    24 |    1 |    0 |    0 |     0 |     0 |    0 |    0
    | 16-JUN 18:29  | d3tcbyxaa65ku  | ON CPU                         |     854 |    4.7 |   60 |   242 |    37 |    32 |    3 |    0 |    0 |     0 |     0 |    3 |    2
    | 16-JUN 18:30  | d3tcbyxaa65ku  | ON CPU                         |     570 |    4.4 |   60 |   219 |    45 |    45 |    3 |    0 |    0 |     0 |     0 |    2 |    0
    | 16-JUN 18:31  | d3tcbyxaa65ku  | ON CPU                         |     854 |    4.8 |   60 |   259 |    27 |    26 |    0 |    0 |    0 |     0 |     0 |    0 |    1
    | 16-JUN 18:32  | d3tcbyxaa65ku  | ON CPU                         | <NULL>  |    4.9 |   60 |   249 |    44 |    43 |    0 |    0 |    0 |     0 |     0 |    0 |    1
    | 16-JUN 18:33  | d3tcbyxaa65ku  | ON CPU                         |     854 |    4.7 |   60 |   257 |    27 |    26 |    0 |    0 |    0 |     0 |     0 |    0 |    1
    | 16-JUN 18:34  | d3tcbyxaa65ku  | ON CPU                         |     854 |    4.7 |   59 |   236 |    42 |    41 |    4 |    0 |    0 |     0 |     0 |    1 |    0
    | 16-JUN 18:35  | d3tcbyxaa65ku  | ON CPU                         |     570 |    4.8 |   60 |   221 |    68 |    63 |    4 |    0 |    0 |     0 |     0 |    1 |    4
    | 16-JUN 18:36  | d3tcbyxaa65ku  | ON CPU                         |     854 |    5.5 |   60 |   280 |    47 |    42 |    1 |    0 |    0 |     0 |     0 |    0 |    4
    | 16-JUN 18:37  | d3tcbyxaa65ku  | ON CPU                         | <NULL>  |    4.7 |   60 |   257 |    24 |    24 |    2 |    0 |    0 |     0 |     0 |    0 |    0
    | 16-JUN 18:38  | d3tcbyxaa65ku  | ON CPU                         | <NULL>  |    4.5 |   60 |   244 |    26 |    26 |    0 |    0 |    0 |     0 |     0 |    0 |    0
    | 16-JUN 18:39  | d3tcbyxaa65ku  | ON CPU                         |     854 |    5.1 |   60 |   267 |    37 |    36 |    3 |    0 |    0 |     0 |     0 |    1 |    0
    | 16-JUN 18:40  | d3tcbyxaa65ku  | ON CPU                         |     570 |    4.9 |   60 |   247 |    45 |    41 |    3 |    0 |    0 |     0 |     0 |    1 |    1
    | 16-JUN 18:41  | d3tcbyxaa65ku  | ON CPU                         |     854 |    4.6 |   59 |   244 |    29 |    26 |    2 |    0 |    0 |     0 |     0 |    0 |    0
    | 16-JUN 18:42  | d3tcbyxaa65ku  | ON CPU                         |     570 |    4.4 |   60 |   196 |    69 |    56 |   16 |    0 |    0 |     0 |     0 |   10 |    2
    | 16-JUN 18:43  | d3tcbyxaa65ku  | ON CPU                         |     570 |    5.5 |   60 |   260 |    72 |    60 |   10 |    0 |    0 |     1 |     0 |    6 |    4
    | 16-JUN 18:44  | d3tcbyxaa65ku  | ON CPU                         |     854 |    4.9 |   21 |    85 |    17 |    16 |    1 |    0 |    0 |     0 |     0 |    1 |    0


62 rows selected.

18:46:33 (3)system@some_database> @plan_id d3tcbyxaa65ku

SQL_ID  d3tcbyxaa65ku, child number 2
-------------------------------------
select * from ( select row_.*, rownum rownum_ from ( select this_.id as
y0_, this_.status as y1_, this_.service as y2_, this_.request_date as
y3_, this_.response_timestamp as y4_, substr(this_.response_content, 0,
5) responseContent, this_.labels as y6_, this_.sign_subj as y7_,
this_.sign_org as y8_, this_.author as y9_ from SOME_TABLE
this_ left outer join SOME_TABLE2 authoruser1_ on
this_.author=authoruser1_.id where ((this_.author is null or
this_.author=:1  or authoruser1_.owner=:2  or this_.author in (select
user_.id as y0_ from SOME_TABLE2 user_ where user_.region=:3 )) and
this_.author is not null and this_.service in (:4 , :5 , :6 , :7 , :8 ,
:9 , :10 , :11 , :12 , :13 , :14 , :15 , :16 , :17 , :18 , :19 , :20 ,
:21 , :22 , :23 , :24 , :25 , :26 , :27 , :28 , :29 , :30 , :31 , :32 ,
:33 , :34 , :35 , :36 , :37 , :38 , :39 , :40 , :41 , :42 , :43 , :44 ,
:45 , :46 , :47 , :48 , :49 , :50 , :51 , :52 , :53 , :54 , :55 , :56 ,
:57 , :58 , :59 , :60 , :61 , :62 , :63 , :64 , :6


Plan hash value: 1496851868

-------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                           | E-Rows | Cost (%CPU)|
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                                |        |    79 (100)|
|*  1 |  VIEW                            |                                |      2 |    79   (0)|
|*  2 |   COUNT STOPKEY                  |                                |        |            |
|   3 |    VIEW                          |                                |      2 |    79   (0)|
|*  4 |     FILTER                       |                                |        |            |
|   5 |      NESTED LOOPS OUTER          |                                |      2 |    79   (0)|
|*  6 |       TABLE ACCESS BY INDEX ROWID| SOME_TABLE                     |      2 |    77   (0)|
|   7 |        INDEX FULL SCAN DESCENDING| SOME_TABLE_REDIF_IDX           |    574K|     3   (0)|
|   8 |       TABLE ACCESS BY INDEX ROWID| SOME_TABLE2                    |      1 |     1   (0)|
|*  9 |        INDEX UNIQUE SCAN         | SYS_C00288211                  |      1 |     0   (0)|
|* 10 |      TABLE ACCESS BY INDEX ROWID | SOME_TABLE2                    |      1 |     2   (0)|
|* 11 |       INDEX UNIQUE SCAN          | SYS_C00288211                  |      1 |     1   (0)|
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ROWNUM_">:199)
   2 - filter(ROWNUM<=:198)
   4 - filter(("SYS_ALIAS_1"."AUTHOR"=:1 OR "AUTHORUSER1_"."OWNER"=:2 OR  IS NOT NULL))
   6 - filter(("SYS_ALIAS_1"."AUTHOR" IS NOT NULL AND
              SYS_EXTRACT_UTC("LAST_UPDATED")>SYS_EXTRACT_UTC(:197) AND
              INTERNAL_FUNCTION("SYS_ALIAS_1"."SERVICE") AND "SYS_ALIAS_1"."PGU_CONV_ID" IS NULL))
   9 - access("SYS_ALIAS_1"."AUTHOR"="AUTHORUSER1_"."ID")
  10 - filter("USER_"."REGION"=:3)
  11 - access("USER_"."ID"=:B1)

 (3)system@some_database> l
  1* select * from table (dbms_xplan.display_cursor('&1',NULL,'ALLSTATS PREDICATE COST LAST +ALLSTATS' ))

18:44:21 (3)system@some_database> @http://blog.tanelpoder.com/files/scripts/sqlid.sql d3tcbyxaa65ku % %
Show SQL text, child cursors and execution stats for SQLID d3tcbyxaa65ku child %
old   9:        sql_id = ('&1')
new   9:        sql_id = ('d3tcbyxaa65ku')
old  10: and child_number like '&2'
new  10: and child_number like '%'

HASH_VALUE | PLAN_HASH_VALUE |  CH# | SQL_TEXT
---------- | --------------- | ---- | ------------------------------------------------------------------------------------------------------------------------------------------------------
1419974234 |      1496851868 |    2 | select * from ( select row_.*, rownum rownum_ from ( select this_.id as y0_, this_.status as y1_, this_.service as y2_, this_.request_date as y3_,
           |                 |      | this_.response_timestamp as y4_, substr(this_.response_content, 0, 5) responseContent, this_.labels as y6_, this_.sign_subj as y7_, this_.sign_org as
           |                 |      | y8_, this_.author as y9_ from SOME_TABLE this_ left outer join SOME_TABLE2 authoruser1_ on this_.author=authoruser1_.id where ((this_.author
           |                 |      | is null or this_.author=:1  or authoruser1_.owner=:2  or this_.author in (select user_.id as y0_ from SOME_TABLE2 user_ where user_.region=:3 )) and
           |                 |      | this_.author is not null and this_.service in (:4 , :5 , :6 , :7 , :8 , :9 , :10 , :11 , :12 , :13 , :14 , :15 , :16 , :17 , :18 , :19 , :20 , :21 ,
           |                 |      | :22 , :23 , :24 , :25 , :26 , :27 , :28 , :29 , :30 , :31 , :32 , :33 , :34 , :35 , :36 , :37 , :38 , :39 , :40 , :41 , :42 , :43 , :44 , :45 , :46 ,
           |                 |      | :47 , :48 , :49 , :50 , :51 , :52 , :53 , :54 , :55 , :56 , :57 , :58 , :59 , :60 , :61 , :62 , :63 , :64 , :6



 CH# | SQL_ID        | PARENT_HANDLE    | OBJECT_HANDLE    |  PLAN_HASH |     PARSES |   H_PARSES | EXECUTIONS |    FETCHES | ROWS_PROCESSED | ROWS_PER_FETCH |    CPU_SEC | CPU_SEC_EXEC |    ELA_SEC |       LIOS |       PIOS |      SORTS | USERS_EXECUTING | B_Sens | B_Aware
---- | ------------- | ---------------- | ---------------- | ---------- | ---------- | ---------- | ---------- | ---------- | -------------- | -------------- | ---------- | ------------ | ---------- | ---------- | ---------- | ---------- | --------------- | ------ | -------
   2 | d3tcbyxaa65ku | 00000004A07EB2B8 | 0000000555482BF8 | 1496851868 |      18565 |          2 |      18566 |     541935 |        5415140 |     9.99223154 | 81859.0228 |   4.40908234 | 145650.218 | 3694696752 |  244581883 |          0 |               5 | N      | N

Elapsed: 00:00:00.14

ASH_SQLMON

18:44:29 (3)system@some_database> @ash_sqlmon d3tcbyxaa65ku 1496851868 %
old   8:    where sql_id = '&1'
new   8:    where sql_id = 'd3tcbyxaa65ku'
old   9:      and sql_plan_hash_value = nvl('&2', sql_plan_hash_value)
new   9:      and sql_plan_hash_value = nvl('1496851868', sql_plan_hash_value)
old  10:    -- and NVL(sql_exec_id, 0) = nvl('&3', NVL(sql_exec_id, 0))
new  10:    -- and NVL(sql_exec_id, 0) = nvl('%', NVL(sql_exec_id, 0))

        ID | PLAN_OPERATION                                                                   | OBJECT_OWNER         | OBJECT_NAME                    | WAIT_PROFILE
---------- | -------------------------------------------------------------------------------- | -------------------- | ------------------------------ | --------------------------------------------------------------------------------
         0 |   SELECT STATEMENT                                                               | <NULL>               | <NULL>                         | ON CPU(351); ON CPU(135);
         1 |     VIEW                                                                         | <NULL>               | <NULL>                         | ON CPU(97);
         2 |       COUNT STOPKEY                                                              | <NULL>               | <NULL>                         | ON CPU(2);
         3 |         VIEW                                                                     | <NULL>               | <NULL>                         | ON CPU(29901); direct path read(26326); buffer exterminate(1);
         4 |           FILTER                                                                 | <NULL>               | <NULL>                         | ON CPU(1464); latch: cache buffers chains(2);
         5 |             NESTED LOOPS OUTER                                                   | <NULL>               | <NULL>                         | ON CPU(63);
         6 |               TABLE ACCESS BY INDEX ROWID                                        | some_user            | SOME_TABLE                     | ON CPU(7201); db file sequential read(67); read by other session(54); gc buffer
           |                                                                                  |                      |                                | busy acquire(4);
         7 |                 INDEX FULL SCAN DESCENDING                                       | some_user            | SOME_TABLE_REDIF_IDX           | ON CPU(642); read by other session(6); db file sequential read(4); gc buffer bus
           |                                                                                  |                      |                                | y acquire(1);

         8 |               TABLE ACCESS BY INDEX ROWID                                        | some_user            | SOME_TABLE2                    | ON CPU(1403);
         9 |                 INDEX UNIQUE SCAN                                                | some_user            | SYS_C00288211                  | ON CPU(2507);
        10 |             TABLE ACCESS BY INDEX ROWID                                          | some_user            | SOME_TABLE2                    | ON CPU(473);
        11 |               INDEX UNIQUE SCAN                                                  | some_user            | SYS_C00288211                  | ON CPU(1239); latch: cache buffers chains(5);

look at plan line 3 in ash:


(3)system@some_database> @ash_obj d3tcbyxaa65ku  1496851868 sysdate-1/24 sysdate
old   4:   where a.sql_id='&1' and a.sql_plan_hash_value='&2' and a.SAMPLE_TIME between &3 and nvl(&4,sysdate)
new   4:   where a.sql_id='d3tcbyxaa65ku' and a.sql_plan_hash_value='1496851868' and a.SAMPLE_TIME between sysdate-1/24 and nvl(sysdate,sysdate)

  COUNT(*) | SQL_PLAN_LINE_ID | CURRENT_OBJ# | OBJECT_NAME                    | OWNER                          | SESSION | Event
---------- | ---------------- | ------------ | ------------------------------ | ------------------------------ | ------- | ----------------------------------------
      6366 |                3 |       669478 | SYS_LOB0000669470C00005$$      | some_user                        | ON CPU  | <NULL>
      2446 |                3 |       669478 | SYS_LOB0000669470C00005$$      | some_user                        | WAITING | direct path read
      1511 |                6 |       669478 | SYS_LOB0000669470C00005$$      | some_user                        | ON CPU  | <NULL>
       474 |                9 |       669478 | SYS_LOB0000669470C00005$$      | some_user                        | ON CPU  | <NULL>
       298 |                8 |       669478 | SYS_LOB0000669470C00005$$      | some_user                        | ON CPU  | <NULL>
       290 |                4 |       669478 | SYS_LOB0000669470C00005$$      | some_user                        | ON CPU  | <NULL>
       212 |               11 |       669478 | SYS_LOB0000669470C00005$$      | some_user                        | ON CPU  | <NULL>
       140 |                7 |       669478 | SYS_LOB0000669470C00005$$      | some_user                        | ON CPU  | <NULL>
        95 |               10 |       669478 | SYS_LOB0000669470C00005$$      | some_user                        | ON CPU  | <NULL>
        72 | <NULL>           |       669478 | SYS_LOB0000669470C00005$$      | some_user                        | ON CPU  | <NULL>
        25 |                0 |       669478 | SYS_LOB0000669470C00005$$      | some_user                        | ON CPU  | <NULL>
        13 |                1 |       669478 | SYS_LOB0000669470C00005$$      | some_user                        | ON CPU  | <NULL>
        11 |                5 |       669478 | SYS_LOB0000669470C00005$$      | some_user                        | ON CPU  | <NULL>
         2 |                4 |       669478 | SYS_LOB0000669470C00005$$      | some_user                        | WAITING | latch: cache buffers chains
         1 |                2 |       669478 | SYS_LOB0000669470C00005$$      | some_user                        | ON CPU  | <NULL>

 (3)system@some_database> l
  1  select count(*),a.SQL_PLAN_LINE_ID,current_obj#,o.object_name,o.owner,session_state,event
  2    from gv$active_session_history a
  3       left join dba_objects o on a.current_obj#=o.object_id
  4    where a.sql_id='&1' and a.sql_plan_hash_value='&2' and a.SAMPLE_TIME between &3 and nvl(&4,sysdate)
  5    group by a.SQL_PLAN_LINE_ID,a.current_obj#,o.object_name,o.owner,session_state,event
  6*   order by 1 desc

select * from ( select row_.*, rownum rownum_ from ( select this_.id as y0_, this_.status as y1_, this_.service as y2_, this_.request_date as y3_, this_.response_timestamp as y4_, 
substr(this_.response_content, 0, 5) responseContent,
 this_.labels as y6_, this_.sign_subj as y7_, this_.sign_org as y8_, this_.author as y9_ from adp_conversation_out this_ left outer join adp_user authoruser1_ on this_.author=authoruser1_.id where ((this_.author is null or this_.author=:1 or authoruser1_.owner=:2 or this_.author in (select user_.id as y0_ from adp_user user_ where user_.region=:3 )) and this_.author is not null and this_.service in (:4 , :5 , :6 , :7 , :8 , :9 , :10 , :11 , :12 , :13 , :14 , :15 , :16 , :17 , :18 , :19 , :20 , :21 , :22 , :23 , :24 , :25 , :26 , :27 , :28 , :29 , :30 , :31 , :32 , :33 , :34 , :35 , :36 , :37 , :38 , :39 , :40 , :41 , :42 , :43 , :44 , :45 , :46 , :47 , :48 , :49 , :50 , :51 , :52 , :53 , :54 , :55 , :56 , :57 , :58 , :59 , :60 , :61 , :62 , :63 , :64 , :65 , :66 , :67 , :68 , :69 , :70 , :71 , :72 , :73 , :74 , :75 , :76 , :77 , :78 , :79 , :80 , :81 , :82 , :83 , :84 , :85 , :86 , :87 , :88 , :89 , :90 , :91 , :92 , :93 , :94 , :95 , :96 , :97 , :98 , :99 , :100 , :101 , :102 , :103 , :104 , :105 , :106 , :107 , :108 , :109 , :110 , :111 , :112 , :113 , :114 , :115 , :116 , :117 , :118 , :119 , :120 , :121 , :122 , :123 , :124 , :125 , :126 , :127 , :128 , :129 , :130 , :131 , :132 , :133 , :134 , :135 , :136 , :137 , :138 , :139 , :140 , :141 , :142 , :143 , :144 , :145 , :146 , :147 , :148 , :149 , :150 , :151 , :152 , :153 , :154 , :155 , :156 , :157 , :158 , :159 , :160 , :161 , :162 , :163 , :164 , :165 , :166 , :167 , :168 , :169 , :170 , :171 , :172 , :173 , :174 , :175 , :176 , :177 , :178 , :179 , :180 , :181 , :182 , :183 , :184 , :185 , :186 , :187 , :188 , :189 , :190 , :191 , :192 , :193 , :194 , :195 , :196 )) and this_.pgu_conv_id is null and this_.last_updated>:197 order by y3_ desc ) row_ where rownum <= :198 ) where rownum_ > :19

this substr(this_.response_content, 0, 5) query high compressed clob field

after talking with the developers on the subject, why would they need these first 5 characters, they agreed to remove them from the query. After editing query the server load is 3 times lower

alter system set events

05.06.2015 Bushmelev Anton Комментариев нет

cool =) does not know that it can be done such way
alter system set events ‘ xxx trace name context level 12, lifetime 1’;
more at :
blog.tanelpoder.com

oracle install to do

03.06.2015 Bushmelev Anton Комментариев нет

groupadd --gid 54321 oinstall;
groupadd --gid 54322 dba;
groupadd --gid 54323 asmdba;
groupadd --gid 54324 asmoper;
groupadd --gid 54325 asmadmin;
groupadd --gid 54326 oper;
groupadd --gid 54327 backupdba;
groupadd --gid 54328 dgdba;
groupadd --gid 54329 kmdba;
useradd --uid 54321 --gid oinstall --groups 'dba,oper,asmdba,asmoper,backupdba,dgdba,kmdba' oracle;
useradd --uid 54322 --gid oinstall --groups 'dba,asmadmin,asmdba,asmoper' grid;
## set default passwords for these users?
echo  -e "aNYlbpdQKzBacjOg" | (passwd --stdin grid)
echo  -e "aNYlbpdQKzBacjOg" | (passwd --stdin oracle)

On Linux, Automatic Storage Management uses asynchronous Input-Output by default.
if database files are not on asm:

alter system set filesystemio_options=asynch scope=spfile;
alter system set disk_asynch_io=true scope=spfile;

effect of disk async onoff:

[свернуть]

save a lot of time later, need for logminer:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; 
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

alter system set resumable_timeout=3600;
alter system set ddl_lock_timeout=60;

setup awr snapshot retention:

in case with ADG: Installing and Using Standby Statspack in 11g (Doc ID 454848.1)

Primary Sidebar