Ferhat's Blog

There will be only one database

Finding out the tables in a query: a free SQLParser

Posted by fsengonul on July 27, 2016

We have hundreds of free-hand ,’ quick and dirty’ ,   MicroStrategy reports and we need to find out the tables that are accessed from them.   Most of the queries exceed hundreds of lines , with clauses,views, inline views  ,etc… The db is 11.2.0.4.

So, the ‘quick and dirty’ way seems as using oracle dbms_sql.parse   for parsing  and then querying the v$sql_plan table. Using the test environment would be the best practice, yet there is a security issue in the code that I’ll explain in the end.

The metadata table of MicroStrategy (derived as sql_source)  keeps the sqls in multiple rows because of the varchar limitation. I need to concat them but  listagg  also suffers from the infamous ORA-01489 error . Therefore I’ve used XMLAGG to concat  the rows in  c1 cursor.  Sqlstatements in varchar2 or CLOB format is the only input, so c1 would be changed as desired.

I’ve created 2 tables for keeping the sql’s and the execution plans for further analysis.


drop table my_sql;
create table my_sql (sql_id VARCHAR2(13),sql_fulltext CLOB);

drop table my_sql_plan;
create table my_sql_plan as select * from v$sql_plan where 1=0;

The rest is getting the sqlstatements from the table, parsing them (not executing) and getting the sqlids and execution plans into our final tables.


DECLARE
   CURSOR c1
   IS
        SELECT is_rep_name,
               DBMS_XMLGEN.CONVERT (
                  REPLACE (
                     REPLACE (
                        XMLAGG (XMLELEMENT ("a", sqlstatement)
                        ORDER BY sqlstatementseq ASC NULLS LAST).getClobVal (),
                        '<a>',
                        ''),
                     '</a>',
                     ''),
                  1)
                  AS sql_text
          FROM SQL_SOURCE
         WHERE IS_DB_INST1_NAME LIKE '%ODS%'
      GROUP BY is_rep_name;

   cursor_name   INTEGER;
   my_sql_id     VARCHAR2 (32);

BEGIN
   FOR sql_rec IN c1
   LOOP

      cursor_name := DBMS_SQL.open_cursor;
      DBMS_SQL.PARSE (cursor_name, sql_rec.sql_text, DBMS_SQL.NATIVE);

      SELECT sql_id
        INTO my_sql_id
        FROM v$sql
       WHERE DBMS_LOB.compare (sql_fulltext, sql_rec.sql_text) = 0;

      INSERT INTO my_sql_plan
         SELECT sp.*
           FROM v$sql_plan sp
          WHERE sp.sql_id = my_sql_id;

      INSERT INTO my_sql values(my_sql_id,sql_rec.sql_text);
      COMMIT;

     -- DBMS_OUTPUT.PUT_LINE (my_sql_id);
      DBMS_SQL.CLOSE_CURSOR (cursor_name);
   END LOOP;
END;

Finally we’ve got the resulting table to query in anyway we like.  FILTER and PROJECTION predicates may also be helpful for further lineage analysis. We may also check the indexes for fast full index scan.

MY_SQL table is useful for joining the tables to the sql queries.


-- Get all the tables accessed:
select  distinct object_owner,object_name from my_sql_plan where object_type = 'TABLE' ;
--return to the sql_statement.
select  to_Char(substr(sql_fulltext,1,30)),object_owner,object_name from my_sql_plan msp,my_Sql ms where msp.sql_id=ms.sql_id and msp.object_type = 'TABLE'

Finaly, dbms_sql.parse is great for the queries and DML , but unfortunately the DDL  is executed even you only parse it. If there is any DDL in the input table , it is executed! So the code is open for sql injection .

I will search  the way to understand or restrict  whether the sql is ddl or dml  before the parse?  Any ideas?

Until then, stay on the test🙂

 

Posted in oracle | Leave a Comment »

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 »