Frequent and Fast Refresh Materialized Views has a Big Problem! mustafakalayci, 2020-07-25 Hi, While I was checking V$SQL_PLAN view for a whole different reason, I realized that some of the cursors has incredibly high child cursors. I started to dig and finally I find the problem. My customer have a few frequently fast refreshed materialized views. One of them is refreshed every… Continue Reading
Is It Possible to Change SQL Statement in the Database? mustafakalayci, 2020-07-04 YES! Hi, today I want to write about something which has been asked for many times to me. Let’s say you have an application and it is running very poorly designed sql statements in your database and you are really bored having fights with development teams to ask a change… Continue Reading
V$SqlStats vs V$SqlStats_Plan_Hash, different columns? mustafakalayci, 2020-07-01 Hi there, V$SQLSTATSis a beautiful view for information about sql statements. you might say v$sql has that information too which is correct but V$SQLSTATSis much more faster than v$sql and keeps data about sql statements for a longer time than v$sql. https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/V-SQLSTATS.html However, the V$SQLSTATS view differs from V$SQL and V$SQLAREA in that it is faster,… Continue Reading
Native Compilation of a PLSQL Object mustafakalayci, 2020-06-17 Hi, you’ve probably heard about “NATIVE” compilation about plsql objects. I wanted to demonstrate how it is work and whether it really does matter or not. By default Oracle compiles every plsql object as “INTERPRETED” which means, your plsql code transform into a intermediate form which interpreted every time when… Continue Reading
What is the Max Size of an Oracle Database? mustafakalayci, 2020-06-05 Hi, sometimes I search for “what is the biggest” or “what is the highest” of things and I just realized that I never checked for the max size of an Oracle Database. so based on the limitations ( https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/physical-database-limits.html#GUID-939CB455-783E-458A-A2E8-81172B990FE9 ) and using default settings; default tablespace type is “smallfile” which… Continue Reading
PL/SQL package SYS.DBMS_BACKUP_RESTORE version 19.03.00.00 in TARGET database is not current after upgrade mustafakalayci, 2020-04-06 Hi there, Yesterday I upgraded a 19.3 database to 19.6 on Windows 10 Pro and after upgrade Rman starts to give two warnings: PL/SQL package SYS.DBMS_BACKUP_RESTORE version 19.03.00.00 in TARGET database is not current PL/SQL package SYS.DBMS_RCVMAN version 19.03.00.00 in TARGET database is not current this didn’t happen when I… Continue Reading
Using “User” function in a scheduled Job mustafakalayci, 2020-03-17 Hi there, I wanted to talk about a problem that I faced recently. Also I thought about the subject because this post is also related with “Session user vs Schema”. Whenever I use the term of “Job” I will be talking about Dbms_Scheduler Jobs not Dbms_Job! When you created a… Continue Reading
CLOB size matters! mustafakalayci, 2019-07-31 Hi, Many developer and DBA has an idea about the storing clobs. Basic rule is “if length of clob is higher tan 4000 then it will be stored in lob segment not in table segment” which is correct but sometimes misinterpreted! if you have a clob column in your table,… Continue Reading
Using Temp instead of Undo for GTTs mustafakalayci, 2019-06-26 Hello everyone, with 12c we have a very nice new option; Temp Undo for GTTs (Global Temporary Tables). as you know temporary tables are used for storing temporary data. those tables data are stored in TEMP tablespace so they are not vital. Temporary tables can generates very little redo data… Continue Reading
EM Express Basic Troubleshooting mustafakalayci, 2019-06-09 After 12c, unfortunately, we lost Enterprise Manager Console but we have pre-installed EM Express now. if you remember EM console you would install it with emca utility with many parameters. EM express save you from this because it is embedded. you just need to set a few things in your… Continue Reading