Ferhat's Blog

There will be only one database

Calculating TPS from ASH via SQL_EXEC_ID

Posted by fsengonul on February 7, 2016

After a problem happened in an application server  running to a 11.2.0.4 database , we were arguing about how many queries per second had been running at the time of the problem for a sqlid . The dba_hist_sqlstat was actually not satisfactory because the snapshot period was 1 hour. The problem started at 23:45 and peaked at 23:55.

PLAN_HASH_VALUE PARSING_SCHEMA_NAME            BEGIN_INTERVAL_TIME                      EXECUTIONS_DELTA BUFFER_GETS DISK_READS ELAPSED_SN     CPU_SN

————— —————————— —————————————- —————- ———– ———- ———- ———- ——-

2579161228 TIMS                           02/02/2016 20:00:21,028                            358698     18,9402    10,5922      ,0435      ,0027

2579161228 TIMS                           02/02/2016 21:00:28,454                            350244      17,587     9,5744      ,0425      ,0022

2579161228 TIMS                           02/02/2016 22:00:33,139                            278166     11,1771     7,0515      ,0294      ,0016

2579161228 TIMS                           02/02/2016 23:00:37,352                            512135     15,4872    11,3624      ,0507      ,0021

2579161228 TIMS                           03/02/2016 00:00:41,781                            381670     22,1958    17,1788      ,0784      ,0029

And the visualization via excel:

from_dba_hist_sqlstat

So the 500K per hour (138 tps)  does not seem as an outlier when compared to the other hours statistics. But when we looked it closely from the ASH :


SELECT TO_CHAR (sample_time, 'HH24:MI'),
       MAX (sql_exec_id) - MIN (sql_exec_id) EXECUTIONS_PER_MINUTE
  FROM v$active_Session_history
 WHERE     sample_time BETWEEN TO_DATE ('2016-02-02 23:00',
                                        'YYYY-MM-DD HH24:MI')
                           AND TO_DATE ('2016-02-02 23:59',
                                        'YYYY-MM-DD HH24:MI')
       AND sql_id = 'd9q0btbtvr5bv'
group by TO_CHAR (sample_time, 'HH24:MI')
order by 1 asc;

So the tps has reached up to 600  when calculated from minutely averages.

And the visualization via excel :

from_ash

For more info about SQL_EXEC_ID  you may check Tanel’s post.

Posted in oracle | Leave a Comment »

ORA_HASH and NLS CHARACTERSET to compare code

Posted by fsengonul on November 15, 2015

Task :  Find out whether a system trigger or procedure is same in 100+ databases.
First idea : Let’s use ORA_HASH for the source code.
Action 1: Use LISTAGG to create a single line of data from dba_source. Get rid of the spaces.

SELECT LISTAGG (REPLACE (text, ' ', ''), '') WITHIN GROUP (ORDER BY line) AS text
FROM dba_source WHERE name = 'NAME_OF_TRIGGER_OR_SP';

Action 2: Then use it input to ORA_HASH .


--DB Number 1;

WITH prc
AS (SELECT LISTAGG (REPLACE (text, ' ', ''), '') WITHIN GROUP (ORDER BY line) AS text
FROM dba_source WHERE name = ''NAME_OF_TRIGGER_OR_SP ')
SELECT ORA_HASH (text) FROM prc;

ORA_HASH(TEXT)
--------------
1418146976

--DB Number 2;

WITH prc
AS (SELECT LISTAGG (REPLACE (text, ' ', ''), '') WITHIN GROUP (ORDER BY line) AS text
FROM dba_source WHERE name = ''NAME_OF_TRIGGER_OR_SP ')
SELECT ORA_HASH (text) FROM prc;

ORA_HASH(TEXT)
--------------
4280078353

And here comes the problem; even though the code is the same , the hash values are grouped into two separate sets.

A little investigation shows that the NLS_CHARACTERSET parameter is different for the two sets of dbs  as AL32UTF8 and WE8ISO8859P9. For the Turkish characters the first characterset uses 2 bytes and the latter uses 1 byte only; and this changes the ora_hash result !

So let’s use CONVERT to  reach a final point:


--DB Number 1:

WITH prc
AS (SELECT LISTAGG (REPLACE (text, ' ', ''), '') WITHIN GROUP (ORDER BY line) AS text
FROM dba_source WHERE name = 'NAME_OF_TRIGGER_OR_SP')
SELECT ORA_HASH (CONVERT (text,'AL32UTF8',(SELECT VALUE FROM v$nls_parameters WHERE parameter = 'NLS_CHARACTERSET'))) hash,
(SELECT VALUE FROM v$nls_parameters WHERE parameter = 'NLS_CHARACTERSET') nls
FROM prc;

HASH         NLS
---------- ----------------------------------------------------------------
1418146976 AL32UTF8

--DB Number 2:

WITH prc
AS (SELECT LISTAGG (REPLACE (text, ' ', ''), '') WITHIN GROUP (ORDER BY line) AS text
FROM dba_source WHERE name = 'NAME_OF_TRIGGER_OR_SP')
SELECT ORA_HASH (CONVERT (text,'AL32UTF8',(SELECT VALUE FROM v$nls_parameters WHERE parameter = 'NLS_CHARACTERSET'))) hash,
(SELECT VALUE FROM v$nls_parameters WHERE parameter = 'NLS_CHARACTERSET') nls
FROM prc;
HASH        NLS
---------- ----------------------------------------------------------------
1418146976 WE8ISO8859P9

The SP used to test:

create or replace  procedure helloworld as
begin
    DBMS_OUTPUT.PUT_LINE(q'[Hell'o World ç]');
end;
/

Posted in oracle | Tagged: , , | Leave a Comment »

External tables with symbolic links (DISABLE_DIRECTORY_LINK_CHECK)

Posted by fsengonul on November 12, 2015

The first question that was popped up in my mind when I started to use external tables in 10g days was: “Why doesn’t oracle let us use symbolic links?”

Everybody was mentioning about security issues. If the DBA is the only one to have access to the oracle machine, there may be a pardon for this restriction.

It seems, I’m not alone. When searching for something else I’ve seen DISABLE_DIRECTORY_LINK_CHECK in the 11.2 documentation.

And of course I have that itchy feeling whether it was always there in 10g, also. :)

Below is the example continuing from the skeleton from psoug.


SYSTEM@L11R 20151008 14:31:47 SQL> sta demo/external_tables/5_change_it_to_symbolic_link.sql

SYSTEM@L11R 20151008 14:32:20 SQL>

SYSTEM@L11R 20151008 14:32:20 SQL> select * from ext_tab;



EMPN ENAME                JOB                  DE

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

1111 MORGAN               DIRECTOR             10

2222 HARDIE               MANAGER              30

3333 HAVEMEYER            VP MKTG              10

4444 LOFSTROM             MANAGER              10

5555 ALLEN                SECURITY             30

7369 SMITH                CLERK                20

7499 ALLEN                SALESMAN             30

7521 WARD                 SALESMAN             30

7566 JONES                MANAGER              20

7654 MARTIN               SALESMAN             30



10 rows selected.



Elapsed: 00:00:00.03

SYSTEM@L11R 20151008 14:32:20 SQL> --Now create sybolic links in OS.

SYSTEM@L11R 20151008 14:32:20 SQL> --

SYSTEM@L11R 20151008 14:32:20 SQL> --mv demo1.dat real_demo1.dat

SYSTEM@L11R 20151008 14:32:20 SQL> --mv demo2.dat real_demo2.dat

SYSTEM@L11R 20151008 14:32:20 SQL> --ln -s real_demo1.dat demo1.dat

SYSTEM@L11R 20151008 14:32:20 SQL> --ln -s real_demo2.dat demo2.dat

SYSTEM@L11R 20151008 14:32:20 SQL>



[oracle@ol6-112-rac1 ext_dir]$ ls -altr

total 16

-rw-r--r--.  1 oracle oinstall  111 Oct  8 13:43 real_demo1.dat

-rw-r--r--.  1 oracle oinstall  121 Oct  8 13:43 real_demo2.dat

drwxr-xr-x. 34 oracle oinstall 4096 Oct  8 13:51 ..

lrwxrwxrwx.  1 oracle oinstall   14 Oct  8 14:33 demo2.dat -> real_demo2.dat

lrwxrwxrwx.  1 oracle oinstall   14 Oct  8 14:33 demo1.dat -> real_demo1.dat

drwxr-xr-x.  2 oracle oinstall 4096 Oct  8 14:37 .

[oracle@ol6-112-rac1 ext_dir]$







SYSTEM@L11R 20151008 14:32:20 SQL> --let's query again to see the error

SYSTEM@L11R 20151008 14:32:20 SQL> pause;



SYSTEM@L11R 20151008 14:33:22 SQL>

SYSTEM@L11R 20151008 14:33:22 SQL> select * from ext_tab;

select * from ext_tab

*

ERROR at line 1:

ORA-12801: error signaled in parallel query server P000, instance ol6-112-rac1.localdomain:RAC1 (1)

ORA-29913: error in executing ODCIEXTTABLEFETCH callout

ORA-29400: data cartridge error

KUP-04027: file name check failed: /home/oracle/ext_dir/demo1.dat





Elapsed: 00:00:00.01

SYSTEM@L11R 20151008 14:34:03 SQL>

SYSTEM@L11R 20151008 14:34:03 SQL> --This is the error

SYSTEM@L11R 20151008 14:34:03 SQL> --now drop and recreate with DISABLE_DIRECTORY_LINK_CHECK

SYSTEM@L11R 20151008 14:34:03 SQL> pause;



SYSTEM@L11R 20151008 14:34:04 SQL>

SYSTEM@L11R 20151008 14:34:04 SQL> drop table ext_tab;



Table dropped.



Elapsed: 00:00:00.03

SYSTEM@L11R 20151008 14:34:04 SQL>

SYSTEM@L11R 20151008 14:34:04 SQL> CREATE TABLE ext_tab (

2  empno  CHAR(4),

3  ename  CHAR(20),

4  job    CHAR(20),

5  deptno CHAR(2))

6  ORGANIZATION EXTERNAL (

7    TYPE oracle_loader

8    DEFAULT DIRECTORY ext_dir

9      ACCESS PARAMETERS (

10      RECORDS DELIMITED BY NEWLINE

11      DISABLE_DIRECTORY_LINK_CHECK

12      FIELDS TERMINATED BY ','

13      MISSING FIELD VALUES ARE NULL

14      REJECT ROWS WITH ALL NULL FIELDS

15      (empno, ename, job, deptno))

16      LOCATION ('demo1.dat','demo2.dat')

17    )

18  PARALLEL

19  REJECT LIMIT 0

20  NOMONITORING;



Table created.



Elapsed: 00:00:00.01

SYSTEM@L11R 20151008 14:34:04 SQL>

SYSTEM@L11R 20151008 14:34:04 SQL>

SYSTEM@L11R 20151008 14:34:04 SQL> pause;



SYSTEM@L11R 20151008 14:34:05 SQL> --let's query again

SYSTEM@L11R 20151008 14:34:05 SQL>

SYSTEM@L11R 20151008 14:34:05 SQL> SELECT * FROM ext_tab;



EMPN ENAME                JOB                  DE

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

1111 MORGAN               DIRECTOR             10

2222 HARDIE               MANAGER              30

3333 HAVEMEYER            VP MKTG              10

4444 LOFSTROM             MANAGER              10

5555 ALLEN                SECURITY             30

7369 SMITH                CLERK                20

7499 ALLEN                SALESMAN             30

7521 WARD                 SALESMAN             30

7566 JONES                MANAGER              20

7654 MARTIN               SALESMAN             30



10 rows selected.



Elapsed: 00:00:00.05

SYSTEM@L11R 20151008 14:34:05 SQL>

SYSTEM@L11R 20151008 14:34:05 SQL>

SYSTEM@L11R 20151008 14:34:05 SQL>

SYSTEM@L11R 20151008 14:34:05 SQL>

<b> </b>



/* What if we put the files in another folder */



[oracle@ol6-112-rac1 ext_dir]$ ls -altr

total 24

drwxr-xr-x. 35 oracle oinstall  4096 Oct 20 09:11 ..

-rw-r--r--.  1 oracle dba      14504 Oct 20 09:12 log.log

lrwxrwxrwx.  1 oracle oinstall    26 Oct 20 09:13 demo1.dat -> ../real_dir/real_demo1.dat

lrwxrwxrwx.  1 oracle oinstall    26 Oct 20 09:13 demo2.dat -> ../real_dir/real_demo2.dat

drwxr-xr-x.  2 oracle oinstall  4096 Oct 20 09:13 .





SYSTEM@L11R 20151020 09:12:34 SQL> SELECT * FROM EXT_TAB;



EMPN ENAME                JOB                  DE

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

1111 MORGAN               DIRECTOR             10

2222 HARDIE               MANAGER              30

3333 HAVEMEYER            VP MKTG              10

4444 LOFSTROM             MANAGER              10

5555 ALLEN                SECURITY             30

7369 SMITH                CLERK                20

7499 ALLEN                SALESMAN             30

7521 WARD                 SALESMAN             30

7566 JONES                MANAGER              20

7654 MARTIN               SALESMAN             30



10 rows selected.





/* What if we send to /dev/null */



[oracle@ol6-112-rac1 ext_dir]$ ln -s /dev/null demo1.dat

[oracle@ol6-112-rac1 ext_dir]$ ln -s /dev/null demo2.dat

[oracle@ol6-112-rac1 ext_dir]$



SYSTEM@L11R 20151020 09:14:19 SQL> SELECT * FROM EXT_TAB;



no rows selected



Elapsed: 00:00:00.38

SYSTEM@L11R 20151020 09:26:19 SQL>

Posted in oracle | Tagged: , , | Leave a Comment »

Pivoting Sqlplus output of a single row in multiple columns with column command

Posted by fsengonul on December 10, 2014

When there are so many columns to fit in a screen for a single row , you may experience difficulties like this one:

 

13:23:53 SYSTEM@NODS 10-DEC-14 SQL> select * from gv$session where inst_id=1 and sid=479;                                                                                                                                                  

   INST_ID SADDR                   SID    SERIAL#     AUDSID PADDR                 USER# USERNAME           COMMAND    OWNERID TADDR            LOCKWAIT         STATUS   SERVER       SCHEMA#
---------- ---------------- ---------- ---------- ---------- ---------------- ---------- --------------- ---------- ---------- ---------------- ---------------- -------- --------- ----------
SCHEMANAME                     OSUSER          PROCESS                  MACHINE                                                                PORT TERMINAL
------------------------------ --------------- ------------------------ ---------------------------------------------------------------- ---------- ---------------
PROGRAM                                          TYPE       SQL_ADDRESS      SQL_HASH_VALUE SQL_ID         SQL_CHILD_NUMBER SQL_EXEC_ SQL_EXEC_ID PREV_SQL_ADDR    PREV_HASH_VALUE PREV_SQL_ID
------------------------------------------------ ---------- ---------------- -------------- -------------- ---------------- --------- ----------- ---------------- --------------- -------------
PREV_CHILD_NUMBER PREV_EXEC PREV_EXEC_ID PLSQL_ENTRY_OBJECT_ID PLSQL_ENTRY_SUBPROGRAM_ID PLSQL_OBJECT_ID PLSQL_SUBPROGRAM_ID MODULE
----------------- --------- ------------ --------------------- ------------------------- --------------- ------------------- ----------------------------------------------------------------
MODULE_HASH ACTION                                                           ACTION_HASH CLIENT_INFO                      FIXED_TABLE_SEQUENCE ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK#
----------- ---------------------------------------------------------------- ----------- -------------------------------- -------------------- ------------- -------------- ---------------
ROW_WAIT_ROW# TOP_LEVEL_CALL# LOGON_TIM LAST_CALL_ET PDM FAILOVER_TYPE FAILOVER_M FAI RESOURCE_CONSUMER_GROUP          PDML_STA PDDL_STA PQ_STATU CURRENT_QUEUE_DURATION
------------- --------------- --------- ------------ --- ------------- ---------- --- -------------------------------- -------- -------- -------- ----------------------
CLIENT_IDENTIFIER                                                BLOCKING_SE BLOCKING_INSTANCE BLOCKING_SESSION FINAL_BLOCK FINAL_BLOCKING_INSTANCE FINAL_BLOCKING_SESSION       SEQ#     EVENT#
---------------------------------------------------------------- ----------- ----------------- ---------------- ----------- ----------------------- ---------------------- ---------- ----------
EVENT                                                            P1TEXT                                                                   P1 P1RAW
---------------------------------------------------------------- ---------------------------------------------------------------- ---------- ----------------
P2TEXT                                                                   P2 P2RAW            P3TEXT                                                                   P3 P3RAW            WAIT_CLASS_ID
---------------------------------------------------------------- ---------- ---------------- ---------------------------------------------------------------- ---------- ---------------- -------------
WAIT_CLASS# WAIT_CLASS                                                        WAIT_TIME SECONDS_IN_WAIT STATE               WAIT_TIME_MICRO TIME_REMAINING_MICRO TIME_SINCE_LAST_WAIT_MICRO SERVICE_NA
----------- ---------------------------------------------------------------- ---------- --------------- ------------------- --------------- -------------------- -------------------------- ----------
SQL_TRAC SQL_T SQL_T SQL_TRACE_ SESSION_EDITION_ID CREATOR_ADDR     CREATOR_SERIAL# ECID
-------- ----- ----- ---------- ------------------ ---------------- --------------- ----------------------------------------------------------------
         1 000000055D16C498        479      11661  223581723 0000000548D68818       1482 xxxxxxxxxxx                0 2147483644                                   INACTIVE DEDICATED       1482
xxxxxxxxxxxxx                      extusr          1234                     xxxxxxxx                                                              53940 unknown
JDBC Thin Client                                 USER       00                            0                                                       00000004F220CFA0      1234116758 d5j84z54sy84q
                1 10-DEC-14     16780596                                                                                     JDBC Thin Client
 2546894660                                                                            0                                               3536945      69354518             32        79591555
            0              94 10-DEC-14           44 NO  NONE          NONE       NO  DEFAULT_CONSUMER_GROUP           DISABLED ENABLED  ENABLED                       0
                                                                 NO HOLDER                                      NO HOLDER                                                       29823        354
SQL*Net message from client                                      driver id                                                        1413697536 0000000054435000
#bytes                                                                    1 0000000000000001                                                                           0 00                  2723168908
          6 Idle                                                                      0              44 WAITING                    44030050                   -1                          0 xxxx
DISABLED FALSE FALSE FIRST EXEC                100 0000000548D68818              47


Elapsed: 00:00:00.01
13:24:11 SYSTEM@NODS 10-DEC-14 SQL> 

 

I’ve been using Tanel Poder’s printtab script for years in order to pivot the output.
And that script uses Tom Kyte’s code.
Yet , there is no end for being lazy. It becomes tedious to scroll up and down for finding the column(or row after pivot).
So I’ve combined Tanel Poder’s script with linux column function and here is the result:

 

13:32:56 SYSTEM@NODS 10-DEC-14 SQL> sta sesone 1 479                                                                                                                                                                                       
INST_ID                       : 1					PREV_EXEC_ID                  :						SEQ#                          : 121
SADDR                         : 000000055D16C498			PLSQL_ENTRY_OBJECT_ID         :						EVENT#                        : 325
SID                           : 479					PLSQL_ENTRY_SUBPROGRAM_ID     :						EVENT                         : PX Deq: Execution Msg
SERIAL#                       : 11745					PLSQL_OBJECT_ID               :						P1TEXT                        : sleeptime/senderid
AUDSID                        : 224545295				PLSQL_SUBPROGRAM_ID           :						P1                            : 268632063
PADDR                         : 0000000548D60258			MODULE                        : SQL*Plus				P1RAW                         : 000000001002FFFF
USER#                         : 1189					MODULE_HASH                   : 3669949024				P2TEXT                        : passes
USERNAME                      : xxxxxxx					ACTION                        :						P2                            : 54
COMMAND                       : 2					ACTION_HASH                   : 0					P2RAW                         : 0000000000000036
OWNERID                       : 131650					CLIENT_INFO                   :						P3TEXT                        :
TADDR                         :						FIXED_TABLE_SEQUENCE          : 3550139					P3                            : 23005726872
LOCKWAIT                      :						ROW_WAIT_OBJ#                 : -1					P3RAW                         : 000000055B3F8898
STATUS                        : ACTIVE					ROW_WAIT_FILE#                : 0					WAIT_CLASS_ID                 : 2723168908
SERVER                        : DEDICATED				ROW_WAIT_BLOCK#               : 0					WAIT_CLASS#                   : 6
SCHEMA#                       : 1481					ROW_WAIT_ROW#                 : 0					WAIT_CLASS                    : Idle
SCHEMANAME                    : xxxxxxxx				TOP_LEVEL_CALL#               : 59					WAIT_TIME                     : 0
OSUSER                        : odi					LOGON_TIME                    : 10-DEC-14				SECONDS_IN_WAIT               : 1
PROCESS                       : 32843					LAST_CALL_ET                  : 140					STATE                         : WAITING
MACHINE                       : xxxxxxxx				PDML_ENABLED                  : YES					WAIT_TIME_MICRO               : 574404
PORT                          : 42373					FAILOVER_TYPE                 : NONE					TIME_REMAINING_MICRO          : -1
TERMINAL                      :						FAILOVER_METHOD               : NONE					TIME_SINCE_LAST_WAIT_MICRO    : 0
PROGRAM                       : oracle@xxxxx.bis. (P015)		FAILED_OVER                   : NO					SERVICE_NAME                  : xxxx
TYPE                          : USER					RESOURCE_CONSUMER_GROUP       : xxx_GROUP				SQL_TRACE                     : DISABLED
SQL_ADDRESS                   : 00000003DF2E0A00			PDML_STATUS                   : ENABLED					SQL_TRACE_WAITS               : FALSE
SQL_HASH_VALUE                : 1379380956				PDDL_STATUS                   : ENABLED					SQL_TRACE_BINDS               : FALSE
SQL_ID                        : df2qwvt93gbqw				PQ_STATUS                     : ENABLED					SQL_TRACE_PLAN_STATS          : FIRST EXEC
SQL_CHILD_NUMBER              : 0					CURRENT_QUEUE_DURATION        : 0					SESSION_EDITION_ID            : 100
SQL_EXEC_START                : 10-DEC-14				CLIENT_IDENTIFIER             :						CREATOR_ADDR                  : 0000000548D60258
SQL_EXEC_ID                   : 33554432				BLOCKING_SESSION_STATUS       : UNKNOWN					CREATOR_SERIAL#               : 1
PREV_SQL_ADDR                 : 00					BLOCKING_INSTANCE             :						ECID                          :
PREV_HASH_VALUE               : 0					BLOCKING_SESSION              :						-----------------
PREV_SQL_ID                   :						FINAL_BLOCKING_SESSION_STATUS : UNKNOWN					PL/SQL procedure successfully completed.
PREV_CHILD_NUMBER             : 0					FINAL_BLOCKING_INSTANCE       :						Elapsed: 00:00:00.01
PREV_EXEC_START               :						FINAL_BLOCKING_SESSION        :
13:33:01 SYSTEM@NODS 10-DEC-14 SQL>      

 

 

I’ve copied the printtab.sql into printtabcol.sql and made little additions.

$ cat printtabcol.sql
@@saveset
set serverout on size 1000000

-- This is modified Tom Kyte's printtab code ( http://asktom.oracle.com )
set serverout on size 1000000

-- Modified part by FS
set term off
spool tmp/col.tmp

-- Tom Kyte's printtab code ( http://asktom.oracle.com )

declare
    l_theCursor     integer default dbms_sql.open_cursor;
    l_columnValue   varchar2(4000);
    l_status        integer;
    l_descTbl       dbms_sql.desc_tab;
    l_colCnt        number;
    procedure execute_immediate( p_sql in varchar2 )
    is
    BEGIN
        dbms_sql.parse(l_theCursor,p_sql,dbms_sql.native);
        l_status := dbms_sql.execute(l_theCursor);
    END;
begin
--    execute_immediate( 'alter session set nls_date_format=
--                        ''dd-mon-yyyy hh24:mi:ss'' ');
    dbms_sql.parse(  l_theCursor,
                     replace( '&amp;1', '&quot;', ''''),
                     dbms_sql.native );
    dbms_sql.describe_columns( l_theCursor,
                               l_colCnt, l_descTbl );
    for i in 1 .. l_colCnt loop
        dbms_sql.define_column( l_theCursor, i,
                                l_columnValue, 4000 );
    end loop;
    l_status := dbms_sql.execute(l_theCursor);
    while ( dbms_sql.fetch_rows(l_theCursor) &gt; 0 ) loop
        for i in 1 .. l_colCnt loop
            dbms_sql.column_value( l_theCursor, i,
                                   l_columnValue );
            dbms_output.put_line
                ( rpad( l_descTbl(i).col_name,
                  30 ) || ': ' || l_columnValue );
        end loop;
        dbms_output.put_line( '-----------------' );
    end loop;
 --   execute_immediate( 'alter session set nls_date_format=
 --                          ''dd-MON-yy'' ');
exception
    when others then
        execute_immediate( 'alter session set
                         nls_date_format=''dd-MON-yy'' ');
        raise;
end;
/

-- Modified part by FS
spool off
host column  tmp/col.tmp
host rm tmp/col.tmp
set termout on
--
@@loadset
$ 

And the code to call is pretty simple:

$cat sesone.sql
@@printtabcol "select * from gv$session where inst_id=&1 and sid=&2"
set timing on
undef 1 2 3 4 5
 

Posted in Uncategorized | Leave a Comment »

ipmitool to measure electricity usage of Exadata

Posted by fsengonul on September 16, 2013

If you’re curious about the electricity usage of an exadata rack  ( or have shortage of power in your data center) you may try to use a smart PDU.
But there is a better and cheaper way to measure it by using the ipmitool. After the collection, it’s so easy to create a graph and compare different exadata versions.

exadata_electricity_compare
In this graph , 2 X3-2 HC(High Capacity) / 2 X2-2 HP(High Performance) and 1 v2 SATA racks are compared. The electricity usage of the HP disks seems much more than the HC ones. It would be interesting to compare the relationship between the throughput, cpu usage and electricity.The details are below:

[root@xxx01 ~]# ipmitool sensor | grep -i vps
VPS_CPUS         | 50.000     | Watts      | ok    | na
VPS_MEMORY       | 12.000     | Watts      | ok    | na
VPS_FANS         | 42.000     | Watts      | ok    | na
/SYS/VPS         | 370.000    | Watts      | ok    | na

Our sysadmin Mustafa Altuğ Kamacı has coded a nice script to collect this info from all compute and storage cells. The script is triggered from the crontab.

[root@xxx01 ~]# cat /usr/bin/pwrstat
#!/bin/ksh
PATH=$PATH:/usr/bin:/usr/sbin:/bin
export PATH
d=`date '+%d%m%y'`
t=`date '+%H:%M'`
integer P1=0
integer p1=0
for i in `cat /root/group_all`
do
p1=`ssh -q $i "ipmitool sensor get /SYS/VPS|grep 'Sensor Reading'"|awk '{a=a+$4}END{print a }'`
P1=$P1+$p1
done
echo $t " " $P1 "Watt"  >> /home/pwrstat/pwrstat_$d.log
[root@xxx01 ~]#
root@maxdb01 pwrstat]# ls -al
total 376
drwxr-xr-x  2 root root 4096 Sep 16 00:00 .
drwxr-xr-x 23 root root 4096 Sep 10 15:26 ..
-rw-r--r--  1 root root 3173 Aug  1 23:55 pwrstat_010813.log
-rw-r--r--  1 root root 5472 Sep  1 23:55 pwrstat_010913.log
-rw-r--r--  1 root root 5472 Aug  2 23:55 pwrstat_020813.log
-rw-r--r--  1 root root 5472 Sep  2 23:55 pwrstat_020913.log
-rw-r--r--  1 root root 5472 Aug  3 23:55 pwrstat_030813.log
-rw-r--r--  1 root root 5472 Sep  3 23:55 pwrstat_030913.log
-rw-r--r--  1 root root 5472 Aug  4 23:55 pwrstat_040813.log
.
.
.
[root@xxx01 pwrstat]# cat pwrstat_010913.log
00:00   17580 Watt
00:05   17890 Watt
00:10   17350 Watt
00:15   17510 Watt
00:20   17990 Watt
00:25   17800 Watt
00:30   17640 Watt
00:35   17720 Watt
00:40   17780 Watt
00:45   17830 Watt
00:50   17950 Watt
00:55   17410 Watt
01:00   17970 Watt
01:05   17510 Watt
01:10   17600 Watt

Posted in Exadata | Tagged: , , | 2 Comments »

using pivot fnc to mimic grid active session graph

Posted by fsengonul on January 2, 2012

version 2:
With Yasin’s suggestion, I have changed the script. Added the total ash_secs for the system and get the percentage for the cases when the machine is not fully utilized.
Now the first row shows the rollup of the data and the values are percents with respect to the ash seconds.
From the example below we may roughly say that machines cpu is 28 % utilized and sqlid cqkr2d84qxt6p has used 11% of the cpu for the last 60 seconds.

00:33:01 SQL> @ash2 60                                                                                                                                     
ash_counts for last 60 seconds

NUMBER_OF_NODES NUMBER_OF_THREADS SAMPLE_SECS   ASH_SECS ASH_SECS_PERCENT
--------------- ----------------- ----------- ---------- ----------------
             10                24          60      14400              144

Elapsed: 00:00:00.03

SQL_ID        ON_CPU CONC  UIO  SIO  ADM  OTH CONF SCHE CLST  APP  QUE IDLE  NTW  CMT TOTAL
------------- ------ ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- -----
                  28    0    4    0    0    0    0    0    1    0    0    0    0    0    34
cqkr2d84qxt6p     11    0    0    0    0    0    0    0    0    0    0    0    0    0    11
913cu8k9858rp      6    0    0    0    0    0    0    0    0    0    0    0    0    0     7
9dmq476mc247s      7    0    0    0    0    0    0    0    0    0    0    0    0    0     7
84k1xtr2aj9fd      2    0    0    0    0    0    0    0    0    0    0    0    0    0     2
bfsy799japxd4      0    0    0    0    0    0    0    0    0    0    0    0    0    0     1
7ar015kr4jny2      0    0    0    0    0    0    0    0    0    0    0    0    0    0     1
cgsangykrg375      1    0    0    0    0    0    0    0    0    0    0    0    0    0     1
13r0r59cjc9qy      0    0    0    0    0    0    0    0    0    0    0    0    0    0     1
5tmsa82zrcbnr      0    0    0    0    0    0    0    0    0    0    0    0    0    0     1
9g4s5ycuz6x10      0    0    0    0    0    0    0    0    0    0    0    0    0    0     1
7guv13r4psz4k      0    0    0    0    0    0    0    0    0    0    0    0    0    0     1
cg1s0gh2xn49n      0    0    0    0    0    0    0    0    0    0    0    0    0    0     1
frff0g83ud57d      0    0    0    0    0    0    0    0    0    0    0    0    0    0     1
fvxc1zqs1y3ah      0    0    0    0    0    0    0    0    0    0    0    0    0    0     0
1ytrv77gunsz1      0    0    0    0    0    0    0    0    0    0    0    0    0    0     0
0g1zt3sb3y5yz      0    0    0    0    0    0    0    0    0    0    0    0    0    0     0
bakdmp8pnc8a5      0    0    0    0    0    0    0    0    0    0    0    0    0    0     0
f1y8kbhh6v9sv      0    0    0    0    0    0    0    0    0    0    0    0    0    0     0
6pw8uk8k0dv0q      0    0    0    0    0    0    0    0    0    0    0    0    0    0     0
5k2b3qsy3b30r      0    0    0    0    0    0    0    0    0    0    0    0    0    0     0
81ky0n97v4zsg      0    0    0    0    0    0    0    0    0    0    0    0    0    0     0
3jvj0zbkak9h6      0    0    0    0    0    0    0    0    0    0    0    0    0    0     0

23 rows selected.

Elapsed: 00:00:02.15
00:34:44 SQL> 
prompt ash_counts for last &1 seconds
undef ASH_SECS_PERCENT
col ASH_SECS_PERCENT new_value ASH_SECS_PERCENT

column ON_CPU format 999
column Conc format 999
column UIO format 999
column SIO format 999
column Adm format 999
column Oth format 999
column Conf format 999
column Sche format 999
column CLST format 999
column App format 999
column Que format 999
column Idle format 999
column Ntw format 999
column Cmt format 999
column TOTAL format 999

select count(*) number_of_nodes,avg(value) number_of_threads,
       &1 sample_secs, sum(value)*&1 ash_Secs,sum(value)*&1/100 AS ASH_SECS_PERCENT from gv$parameter where name='cpu_count';

WITH ASH_SECS AS
(select sql_id,
ON_CPU,CONC,UIO,SIO, ADM, OTH, CONF, SCHE, CLST, APP, QUE, IDLE, NTW, CMT,
ON_CPU+CONC+UIO+ SIO+ ADM+ OTH+ CONF+ SCHE+ CLST+ APP+ QUE+ IDLE+ NTW+ CMT total
from
(select ash.sql_id,nvl(EN.WAIT_CLASS,'ON_CPU') class from gv$active_Session_history ash, v$event_name en
where ash.sample_time > sysdate - interval '&1' second
and ash.SQL_ID is not NULL and en.event# (+)=ash.event#
)
PIVOT (count(*) FOR class IN ('ON_CPU' ON_CPU,'Concurrency' Conc,'User I/O' UIO,'System I/O' SIO,'Administrative' Adm,'Other' Oth,
'Configuration' Conf ,'Scheduler' Sche,'Cluster' "CLST",'Application' App,'Queueing' Que,'Idle' Idle,'Network' Ntw,'Commit' Cmt )))
    select sql_id,sum(ON_CPU)/&&ASH_SECS_PERCENT ON_CPU,sum(CONC)/&&ASH_SECS_PERCENT CONC,sum(UIO)/&&ASH_SECS_PERCENT UIO,sum(SIO)/&&ASH_SECS_PERCENT SIO, 
    sum(ADM)/&&ASH_SECS_PERCENT ADM, sum(OTH)/&&ASH_SECS_PERCENT OTH , sum(CONF)/&&ASH_SECS_PERCENT CONF, sum(SCHE)/&&ASH_SECS_PERCENT SCHE,
     sum(CLST)/&&ASH_SECS_PERCENT CLST , sum(APP)/&&ASH_SECS_PERCENT APP,sum(QUE)/&&ASH_SECS_PERCENT QUE, sum(IDLE)/&&ASH_SECS_PERCENT IDLE, 
     sum(NTW)/&&ASH_SECS_PERCENT NTW, sum(CMT)/&&ASH_SECS_PERCENT CMT,sum(TOTAL)/&&ASH_SECS_PERCENT TOTAL from ash_secs
    group by rollup(sql_id)
    order by TOTAL desc;

version 1:
I had been using the decode function in order to summarize the last minute activity from ash.
It seems much more tidy and “new fashioned” to use pivot instead.
I wonder if there is a way to get the TOTAL column without rescanning the data.

prompt ash_counts for last 1 minute
column ON_CPU format 99999
column Conc format 9999
column UI/O format 9999
column SI/O format 9999
column Adm format 9999
column Oth format 9999
column Conf format 9999
column Sche format 9999
column CLST format 9999
column App format 9999
column Que format 9999
column Idle format 9999
column Ntw format 9999
column Cmt format 9999
column TOTAL format 99999

select * from
(select  ash.sql_id,nvl(EN.WAIT_CLASS,'ON_CPU') class from gv$active_Session_history ash, v$event_name en
where ash.sample_time > sysdate - interval '60' second
and  ash.SQL_ID is not NULL  and en.event# (+)=ash.event#
UNION ALL
select ash.sql_id,'TOTAL' from gv$active_Session_history ash
where ash.sample_time > sysdate - interval '60' second
and ash.sql_id is not null
 )
PIVOT (count(*)   FOR class IN ('ON_CPU' ON_CPU,'Concurrency' Conc,'User I/O' "UI/O",'System I/O' "SI/O",'Administrative' Adm,'Other' Oth,
'Configuration' Conf ,'Scheduler' Sche,'Cluster' "CLST",'Application' App,'Queueing' Que,'Idle' Idle,'Network' Ntw,'Commit' Cmt ,'TOTAL' TOTAL))
order by  TOTAL desc;

16:42:20 SQL> @ash                                                                                                                                         
ash_counts for last 1 minute

SQL_ID        ON_CPU  CONC  UI/O  SI/O   ADM   OTH  CONF  SCHE  CLST   APP   QUE  IDLE   NTW   CMT  TOTAL
------------- ------ ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ------
g42mvgbnmf5ws   1585     0     4     0     0    32     0     0     0     0     0     0     0     0   1672
0vjyvrybmtt1h    746     0     2     0     0     8     0     0     0     0     0     0     0     0    787
bjutwyympyn2c    233     0     0     0     0     0     0     0    11     0     0     0     0     0    251
6pm37uuabk94w    176     0     0     0     0     2     0     0     0     0     0     0     0     0    185
5k2b3qsy3b30r     43     0     7     0     0     0     0     0     0     0     0     0     0     0     52
3tp2fqk9wp4c0     45     0     1     0     0     0     0     0     0     0     0     0     3     0     51
7t3bfrr7fpjsr     41     0     0     0     0     0     0     0     0     0     0     0     0     0     41
dw0bx0cr2aacz     14     0     2     0     0     0     0     0     0     0     0     0     1     0     17
atmwkzu6u8prw      4     0     8     0     0     2     0     0     0     0     0     0     0     0     14
c9q76xrxyyv1t      3     0     4     0     0     2     0     0     0     0     0     0     0     0      9
bnvar23frxtaa      4     0     1     0     0     0     0     0     0     0     0     0     4     0      9
f1y8kbhh6v9sv      3     0     0     0     0     0     0     0     0     0     0     0     0     0      3
84p5drb647ptj      1     0     0     0     0     0     0     0     0     0     0     0     0     0      1
fvp75qx959tu5      1     0     0     0     0     0     0     0     0     0     0     0     0     0      1
1uyp1pq4w60h7      1     0     0     0     0     0     0     0     0     0     0     0     0     0      1
aanxb0917spa9      0     0     0     1     0     0     0     0     0     0     0     0     0     0      1
08z984tgg4rqu      1     0     0     0     0     0     0     0     0     0     0     0     0     0      1
3ubt3k76mva3k      1     0     0     0     0     0     0     0     0     0     0     0     0     0      1
576kqgucy5v1q      0     0     0     0     0     1     0     0     0     0     0     0     0     0      1
4cyx7sg5hd6wn      0     0     0     0     0     0     0     0     0     0     0     0     0     0      1

Posted in oracle | 2 Comments »

find a way to time travel in MOS

Posted by fsengonul on October 14, 2011

Posted in Uncategorized | 1 Comment »

OOW 2011 sessions

Posted by fsengonul on September 19, 2011

My sessions/forums in Oracle Open World 2011:

04561 – Turkcell’s Oracle Exadata Journey Continues: Three Full Racks Running Six Databases
13803 – Oracle Exadata Hybrid Columnar Compression: Next-Generation Compression
14048 – Maximize Your ROI with Oracle Database Cloud
Data Warehouse Global Leaders Annual Meeting

 

You may use the following link and search for Sengonul,  to add  the sessions

https://oracleus.wingateweb.com/scheduler/speakers.do

 

And the details:

Title: Turkcell’s Oracle Exadata Journey Continues: Three Full Racks Running Six Databases
Time Monday, 11:00 AM, Moscone South – 302
Length 1 Hour
Abstract: Turkcell, the leading telco operator in Turkey, with more than 33 million subscribers, started its Oracle Exadata journey a little more than a year ago with one full machine and achieved tremendous success. After it experienced a tenfold improvement in performance, storage, and datacenter footprint for its 100 TB data warehouse database, it was a no-brainer to continue on this route, so it added two new Exadata Database Machine X2-2s and consolidated all of its six databases in its data warehouse domain on three full racks. In this session, it shares its experience in this episode of the journey.

Title: Oracle Exadata Hybrid Columnar Compression: Next-Generation Compression
Time Tuesday, 11:45 AM, Moscone South – 304
Length 1 Hour
Abstract: Is your data warehouse growing faster than your storage budget? Is the size of your data warehouse slowing down your users’ queries? Are you convinced that there isn’t a way to archive your OLTP data and keep it accessible to users? If you answered yes to any of these questions, your attendance at this session is mandatory! You will learn how Oracle Exadata hybrid columnar compression can shrink your data warehouse to as little as 1/15 of its original size and improve query performance by drastically reducing I/O. You will also learn how Oracle Exadata hybrid columnar compression, with up to 20x compression for archive data, lets you keep your historical data available for users, and your storage administrator won’t even care that it’s there.

Title: Maximize Your ROI with Oracle Database Cloud
Time Monday, 03:30 PM, Moscone South – 308
Length 1 Hour
Abstract: Database cloud deployments provide the best ROI for deploying databases in a cloud environment. They are based on and leverage advanced database capabilities, and many customers are already benefiting from the capex and opex savings enabled by database cloud deployments. This session presents best practices for maximizing ROI when implementing database consolidation and deploying database as a service (DaaS) to improve overall business agility and significantly reduce database deployment times. It includes specific customer use cases and shows how the customers are maximizing the ROI of database cloud environments.

 

 

Posted in Exadata, oracle | Leave a Comment »

ORA_HASH to compare two tables/(sub)partitions

Posted by fsengonul on July 28, 2011

When you suggest a new method to move the data from one db to another db (previous post) , you should prove that every row is migrated successfully.
There are lots of examples for ORA_HASH implementation on the net. This is yet another one:
The sp uses listagg and ora_hash together.
The input may include the owner,table_name,partition or subpartition.

create or replace procedure GET_ORA_HASH_TABLE
        (owner in varchar2, table_name in varchar2,partition_name in varchar2 default NULL,sub_partition_name in varchar2 default NULL,hash_value out varchar2 ) is
        l_all_columns varchar2(4000);
        v_dyntask   varchar2(20000);
        CURSOR get_columns(p_owner varchar2,p_table_name varchar2) IS
            select listagg(column_name,'||') WITHIN GROUP (order by column_id) all_columns from dba_tab_columns where owner=p_owner and table_name=p_table_name;
BEGIN
   open get_columns(owner,table_name);
   fetch get_columns into l_all_columns;
   close get_columns;
   v_dyntask := 'select sum(ora_hash('|| l_all_columns ||'))  from '|| owner ||'.'|| table_name ;
   if sub_partition_name is  NOT NULL
   THEN
            v_dyntask := v_dyntask || ' subpartition ('|| sub_partition_name ||')';
   else if partition_name is  NOT NULL
            THEN
                                     v_dyntask := v_dyntask || ' partition ('|| partition_name ||')';
         end if;
  end if;
   execute immediate v_dyntask into hash_value ;
END;

Usage on subpartitions :

SQL> SET SERVEROUTPUT ON;
SQL> declare
  2  hash_value varchar2(4000);
  3  BEGIN
  4      GET_ORA_HASH_TABLE('OWNER','TABLE_NAME','','SP2011JAN01_01',hash_value);
  5      DBMS_OUTPUT.PUT_LINE(hash_value);
  6  END;
  7  /
43437576967369636

PL/SQL procedure successfully completed.

And on partitions:

SQL> r
  1  declare
  2  hash_value varchar2(4000);
  3  BEGIN
  4      GET_ORA_HASH_TABLE('OWNER','TABLE_NAME','P2011JAN01','',hash_value);
  5      DBMS_OUTPUT.PUT_LINE(hash_value);
  6* END;
695708730528399811

PL/SQL procedure successfully completed.

SQL> 

And on table:

 1  declare
  2  hash_value varchar2(4000);
  3  BEGIN
  4      GET_ORA_HASH_TABLE('OWNER','TABLE_NAME','','',hash_value);
  5      DBMS_OUTPUT.PUT_LINE(hash_value);
  6* END;

Posted in oracle | 1 Comment »

impdp via dblink on partitions and tbl$or$idx$part$num (It’s not a curse, just a function)

Posted by fsengonul on July 24, 2011

/* ALL THE BELOW IS JUST FOR TESTING, DO NOT USE on PRODUCTION , YET */

I hate restrictions.
The worst one is ORA-14100 :

SQL> select * from "TABLE_OWNER"."TABLE_NAME"@XLK partition(P2011JAN01);
select * from "TABLE_OWNER"."TABLE_NAME"@XLK partition(P2011JAN01)
                          *
ERROR at line 1:
ORA-14100: partition extended table name cannot refer to a remote object

You may either define the borders of the partition or create a view on the target and select on the view via dblink. Defining the borders will only work with range and list partitions. For the hash ones the only way is to define a view. But there has to be another way.

I have seen that impdp over dblink can get partition or subpartition name as a parameter. Such that:

impdp ddsbase/xxxxx DIRECTORY=DPE1 NETWORK_LINK=XLK tables=TABLE_OWNER.TABLE_NAME:P2011JAN01 job_name=DPE1_P2011JAN01 LOGFILE=DPE1:P2011JAN01.log CONTENT=DATA_ONLY QUERY=\" order by contract_sk,content_sk \" 

. . imported "TABLE_OWNER"."TABLE_NAME":"P2011JAN01"."SP2011JAN01_02" 20303431 rows
. . imported "TABLE_OWNER"."TABLE_NAME":"P2011JAN01"."SP2011JAN01_14" 20303020 rows
. . imported "TABLE_OWNER"."TABLE_NAME":"P2011JAN01"."SP2011JAN01_01" 20222512 rows
. . imported "TABLE_OWNER"."TABLE_NAME":"P2011JAN01"."SP2011JAN01_03" 20215302 rows
. . imported "TABLE_OWNER"."TABLE_NAME":"P2011JAN01"."SP2011JAN01_04" 20261746 rows
. . imported "TABLE_OWNER"."TABLE_NAME":"P2011JAN01"."SP2011JAN01_05" 20225309 rows
. . imported "TABLE_OWNER"."TABLE_NAME":"P2011JAN01"."SP2011JAN01_06" 20301212 rows
. . imported "TABLE_OWNER"."TABLE_NAME":"P2011JAN01"."SP2011JAN01_07" 20252840 rows
. . imported "TABLE_OWNER"."TABLE_NAME":"P2011JAN01"."SP2011JAN01_08" 20254043 rows
. . imported "TABLE_OWNER"."TABLE_NAME":"P2011JAN01"."SP2011JAN01_10" 20220187 rows
. . imported "TABLE_OWNER"."TABLE_NAME":"P2011JAN01"."SP2011JAN01_13" 20237208 rows
. . imported "TABLE_OWNER"."TABLE_NAME":"P2011JAN01"."SP2011JAN01_15" 20218603 rows
. . imported "TABLE_OWNER"."TABLE_NAME":"P2011JAN01"."SP2011JAN01_16" 20225260 rows
. . imported "TABLE_OWNER"."TABLE_NAME":"P2011JAN01"."SP2011JAN01_09" 20267115 rows
. . imported "TABLE_OWNER"."TABLE_NAME":"P2011JAN01"."SP2011JAN01_11" 20239070 rows
. . imported "TABLE_OWNER"."TABLE_NAME":"P2011JAN01"."SP2011JAN01_12" 20203805 rows

So impdp has a way to do this, even on subpartitionwise. But even on impdp , if you define parallel=16 , only one partition is loaded at a time, because of the locking issues on the target table.
When I check the query running on the source side, I have seen the strange “undocumented” tbl$or$idx$part$num function.


FROM "TABLE_OWNER"."TABLE_NAME"@XLK KU$
WHERE   tbl$or$idx$part$num("TABLE_OWNER"."TABLE_NAME"@XLK,0,3,0,KU$.ROWID)=4791815

So oracle has a way to select a single subpartition without the subpartition keyword. As you may guess 4791815 is the object_id for the subpartition in dba_objects.

SQL> col owner format a10
SQL> col object_name format a15
SQL> col suboject_name format a15
SQL> select owner,object_name,subobject_name  from dba_objects where object_id=4791815;

OWNER      OBJECT_NAME     SUBOBJECT_NAME
---------- --------------- ------------------------------
TABLE_OWNER  TABLE_NAME SP2011JAN01_02

Let’s mimic impdp and try to create a 16 way parallel movement from one db to another based on subpartitions.


SQL> select  count(*) from
  2   TABLE_OWNER.TABLE_NAME@XLK 
  3   WHERE TBL$OR$IDX$PART$NUM ("TABLE_OWNER"."TABLE_NAME", 0,3,0,ROWID) = 4791815;
select /*+ OPAQUE_TRANSFORM NESTED_TABLE_GET_REFS NESTED_TABLE_GET_REFS */ count(*) from
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 28728
Session ID: 1430 Serial number: 1865

maxdb04: 160363               ORA 7445 [qesmaGetPam()+4]                                  2011-07-24 18:20:07.398000 +03:00

adrci> show incident -mode detail -p "incident_id=160363"

ADR Home = /u01/app/oracle/diag/rdbms/dbname/dbname_4:
*************************************************************************

**********************************************************
INCIDENT INFO RECORD 1
**********************************************************
   INCIDENT_ID                   160363
   STATUS                        ready
   CREATE_TIME                   2011-07-24 18:20:07.398000 +03:00
   PROBLEM_ID                    8
   CLOSE_TIME                    <NULL>
   FLOOD_CONTROLLED              none
   ERROR_FACILITY                ORA
   ERROR_NUMBER                  7445
   ERROR_ARG1                    qesmaGetPam()+4
   ERROR_ARG2                    SIGSEGV
   ERROR_ARG3                    ADDR:0x8
   ERROR_ARG4                    PC:0x13D9310
   ERROR_ARG5                    Address not mapped to object
   ERROR_ARG6                    <NULL>
   ERROR_ARG7                    <NULL>
   ERROR_ARG8                    <NULL>
   ERROR_ARG9                    <NULL>
   ERROR_ARG10                   <NULL>
   ERROR_ARG11                   <NULL>
   ERROR_ARG12                   <NULL>
   SIGNALLING_COMPONENT          PART
   SIGNALLING_SUBCOMPONENT       <NULL>
   SUSPECT_COMPONENT             <NULL>
   SUSPECT_SUBCOMPONENT          <NULL>
   ECID                          <NULL>
   IMPACTS                       0
   PROBLEM_KEY                   ORA 7445 [qesmaGetPam()+4]
   FIRST_INCIDENT                160386
   FIRSTINC_TIME                 2011-07-24 17:06:21.071000 +03:00
   LAST_INCIDENT                 160363
   LASTINC_TIME                  2011-07-24 18:20:07.398000 +03:00
   IMPACT1                       0
   IMPACT2                       0
   IMPACT3                       0
   IMPACT4                       0
   KEY_NAME                      PQ
   KEY_VALUE                     (0, 1311520807)
   KEY_NAME                      Client ProcId
   KEY_VALUE                     oracle@hostname (TNS V1-V3).6051_47869439584576
   KEY_NAME                      SID
   KEY_VALUE                     2708.15225
   KEY_NAME                      ProcId
   KEY_VALUE                     41.95
   OWNER_ID                      1
   INCIDENT_FILE                 /u01/app/oracle/diag/rdbms/dbname/dbname_4/trace/dbname_4_ora_6051.trc
   OWNER_ID                      1
   INCIDENT_FILE                 /u01/app/oracle/diag/rdbms/dbname/dbname_4/incident/incdir_160363/dbname_4_ora_6051_i160363.trc



Ups, it creates a dump on the source db. :(
Don’t give up,yet.
When I check the dump file it mentions about a dblink named “!”
TBL$OR$IDX$PART$NUM(“TABLE_OWNER”.”TABLE_NAME”@!

so this time change the sql to :

SQL> select  count(*) from TABLE_OWNER.TABLE_NAME@XLK
  2  WHERE TBL$OR$IDX$PART$NUM ("TABLE_OWNER"."TABLE_NAME"@XLK, 0,3,0,ROWID) = 4791815;

  COUNT(*)
----------
  17760539

SQL> 

When I add the db_link name to the TBL$OR$IDX$PART$NUM function , it is working.

And finally it’s easy to create 16 parallel insert /*+APPEND */ statements running parallel on the same partition which has 16 subpartitions. And combine them with our in-house code to run them as seperate jobs.

SQL> set pagesize 100
SQL> r
  1  select subobject_name,object_id from dba_objects@XLK
  2* where  owner='TABLE_OWNER' and object_name='TABLE_NAME'  and subobject_name like 'SP2011JAN01%'

SUBOBJECT_NAME                  OBJECT_ID
------------------------------ ----------
SP2011JAN01_01                    4769085
SP2011JAN01_02                    4769086
SP2011JAN01_03                    4769087
SP2011JAN01_04                    4769088
SP2011JAN01_05                    4769089
SP2011JAN01_06                    4769090
SP2011JAN01_07                    4769091
SP2011JAN01_08                    4769092
SP2011JAN01_09                    4769093
SP2011JAN01_10                    4769094
SP2011JAN01_11                    4769095
SP2011JAN01_12                    4769096
SP2011JAN01_13                    4769097
SP2011JAN01_14                    4769098
SP2011JAN01_15                    4769099
SP2011JAN01_16                    4769100

16 rows selected.

SQL> 

SQL> insert /*+ APPEND  */ into TABLE_OWNER.TABLE_NAME subpartition(SP2011JAN01_01)
  2  select * from "TABLE_OWNER"."TABLE_NAME"@XLK "KU$"
  3  WHERE TBL$OR$IDX$PART$NUM ("TABLE_OWNER"."TABLE_NAME"@XLK, 0, 3, 0,ROWID) = 4769085
  4  order by contract_sk,content_sk;

in another 15 sessions

SQL> insert /*+ APPEND  */ into TABLE_OWNER.TABLE_NAME subpartition(SP2011JAN01_02)
  2  select * from "TABLE_OWNER"."TABLE_NAME"@XLK "KU$"
  3  WHERE TBL$OR$IDX$PART$NUM ("TABLE_OWNER"."TABLE_NAME"@XLK, 0, 3, 0,ROWID) = 4769086
  4  order by contract_sk,content_sk;
.
.
.

It took 3 hours with impdp to move a single day, while it take only 10 minutes with this method.
I’m not planning to use it yet, but wondering about the comments.

Posted in oracle | 5 Comments »

 
Follow

Get every new post delivered to your Inbox.

Join 310 other followers