Parameter Workarounds: Changing your Entire Database’s Behavior Because of a Query

This post is just a reflection, basically based on my last post about Adaptive Query Optimization/SQL Plan Directives. Several times we find some situations that can be solved by setting a parameter (likely a “_”) and we just proceed with an “alter system” disregarding about all the impact of this.

This is specially important when talking about “optimizer” parameters even for bugs causing ORA-600’s or performance issues… Changing those parameters in system will affect not only the SQL with the error but all SQLs in database, which may cause a really bad effect in some cases.

bad-idea-sign

Ok Matheus, but what can I do?
Let’s to the obvious and generally applied methods:

Continue reading

Converting Between SQLServer, Oracle, PostgreSQL, MySQL, Sybase and others…

Hi all!
I was asked to make a conversion from T-SQL (MSSQL) Procedure to PL/PGSQL. Regarding how boring is this task, the follow link helped me:
http://www.sqlines.com/online

I highly recommend it. The site has a commercial solution to convert all database, but some code can be converted online for free. 🙂
The conversion not fixed at all, but make a good part of the work… And all help is helpful…

Continue reading

Materialized View with DBLink: ORA-00600: internal error code, arguments: [kkzuasid]

Hello guys!
Not being able to refresh you Materialized View because of this error?

bamdb> exec dbms_mview.refresh('PROD_ORABAM.MVIEW_TEST','C');
BEGIN dbms_mview.refresh('PROD_ORABAM.MVIEW_TEST','C'); END;

*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kkzuasid], [2], [0], [1], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2809
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3025
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2994
ORA-06512: at line 1

The bad new is there is no workaround (I usually prefer workaround for this, is quicker and less complicated).
But the good new is there is a patch for this: Patch 17705023 : ORA-600 [KKZUASID] ON MV REFRESH

Continue reading

Solving Simple Locks Through @lock2s and @killlocker

Hi guys!
This post is to show the most simple and most common kind of locks for objects and the simpliest way to solve it (killing the locker). 🙂
It’s so common that I scripted it. Take a look:

greporadb> @lock2s
 Inst        SID SERIAL# UserName  STATUS   LOGON_TIME          LMODE  REQUEST LC_ET TY       ID1        ID2       CTIME LOCKWAIT         EVENT                                                                                           
----- ---------- ------- --------- -------- ------------------- ------ ------- ----- -- ---------- ---------- ---------- ---------------- -----------------------------------                                                             
    1        354   18145 MATHEUS   ACTIVE   17/06/2016 14:25:19 X      NONE    4032  TX     393238     424490        715 00000000DB0DF900 enq: TX - row lock contention                                                                   
    1        169   25571 GREPORA   ACTIVE   17/06/2016 14:22:48 NONE   X        714  TX     393238     424490        714 00000000DB0D5ED8 enq: TX - row lock contention                                                                   
    1        252   63517 MATHEUS   INACTIVE 17/06/2016 14:17:49 X      NONE     714  TX     655363    1550347       4195                  SQL*Net message from client                                                                     
    1        846   65011 GREPORA   ACTIVE   17/06/2016 14:20:18 NONE   X       4075  TX     655363    1550347        715 00000000DB0ECB88 enq: TX - row lock contention                                                                   
    1        354   18145 GREPORA   ACTIVE   17/06/2016 14:25:19 NONE   S       4032  TX     655363    1550347        715 00000000DB0DF900 enq: TX - row lock contention                                                                   
5 rows selected.

You can identify the Locker by LMODE column. And all his Waiters by REQUEST column marked by not ‘NONE’, below each Locker…

So, let’s kill the lockers:

greporadb> @killlocker
'ALTERSYSTEMKILLSESSION'''||SID||','||SERIAL#||'''IMMEDIATE;'                                                                                                                                                                                                                                                                                                                                          
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------                                                                                                                                                                                                                       
alter system kill session '252,63517' immediate;                                                                                                                                                                                                                                                                                                                                                       
alter system kill session '354,18145' immediate;                                                                                                                                                                                                                                                                                                                                                       
2 rows selected.
greporadb> alter system kill session '252,63517' immediate;      
System altered.
greporadb> alter system kill session '354,18145' immediate;      
System altered.
greporadb> @lock2s
no rows selected

Solved!
My magic scripts? Here it goes:

get lock2s.sql:

set lines 10000
set trimspool on
col serial# for 999999
col lc_et for 999999
col l1name for a50
col lmode for a6
col username for a25
select /*+ rule */
distinct b.inst_id,a.sid,b.serial#,b.username,b.status,
--b.audsid,
--b.module,
--b.machine,b.osuser,
b.logon_time,
decode(lmode,1,'null',2,'RS',3,'RX',4,'S',5,'SRX',6,'X',0,'NONE',lmode) lmode,
decode(request,1,'null',2,'RS',3,'RX',4,'S',5,'SRX',6,'X',0,'NONE',request) request,
b.last_call_et LC_ET,a.type TY,a.id1,a.id2,
d.name||'.'||c.name l1name,a.ctime,b.lockwait,b.event
--distinct b.inst_id,a.sid,b.username,a.type,d.name||'.'||c.name l1name,a.id1,a.id2,
--decode(lmode,1,'null',2,'RS',3,'RX',4,'S',5,'SRX',6,'X',0,'NONE',lmode) lmode,
--decode(request,1,'null',2,'RS',3,'RX',4,'S',5,'SRX',6,'X',0,'NONE',request) request,a.ctime,b.lockwait,b.last_call_et
from gv$lock a, gv$session b,sys.obj$ c,sys.user$ d,(select a.id1 from gv$lock a where a.request  0) lock1
where a.id1    = c.OBJ# (+)
and a.sid      = b.sid
and c.owner#   = d.user# (+)
and a.inst_id=b.inst_id
and b.username is not null
and a.id1 = lock1.id1
order by id1,id2, lmode desc
/

get killlocker.sql:

select 'alter system kill session '''||sid||','||serial#||''' immediate;' 
from v$session where sid in (select BLOCKING_SESSION from v$session where BLOCKING_SESSION is not null);

Now you can put in your Linkedin you are a JR DBA… 😛
haha

Matheus.