Installing Oracle Database is a very common activity to every DBA. In this process, DBA would try to configure all the pre-requisites that Oracle installation document will guide, respective to the version and OS architecture. In which the very common configuration on UNIX platforms is setting up LIMITS.CONF file from /etc/security directory. But why should […]
Oracle Database BUFFER CACHE is one of the core important architectural memory component which holds the copies of data blocks read from datafiles. In my journey of Oracle DBA this memory component played major role in handling Performance Tuning issues. In this Blog, I will demonstrate a case study and analyze the behavior of BUFFER […]
One of the major player in the SGA is SHARED_POOL, without which we can say that there are no query executions. During some performance tuning trials, you would have used ALTER SYSTEM command to flush out the contents in SHARED_POOL. Do you really know what exactly this command cleans out? As we know that internally […]
Every user session on Oracle database will be able to access default table PLAN_TABLE to collect execution plan of a query from EXPLAIN PLAN command. But, how is this table available to all the users in the database and gets empty once you disconnect? Is this table available and created for every user logging into […]
We must have known already that block sizes of non-system tablespaces can be controlled and altered, but did you ever think of block sizes of Control files and Redo log files in the database? How to check their block sizes if they have so, can we alter them when required? Well, it is really very […]
Installing Oracle Database software is one of our regular activities as DBA. There might be supporting notes in each and every project we support, to complete it quicker and more efficiently. One of the pre-requisites we implement is to set up appropriate Kernel parameters at the operating system level on UNIX platforms. But, it would […]
Oracle DBA’s often look at SQL_ID in the process of tuning an SQL statement that performs bad. Most of the DBA’s have a myth of “New SQL_ID of a SQL statement results in hard parsing generating new execution plan”. It is true that if the SQL statement is not found in the shared pool it […]
Tuning SQL statement is one of the major activities in performance tuning for every Oracle DBA. We would refer execution plan, SQL tuning advisor, SQL access advisor, Object statistics etc… to start identifying the issue with the SQL statement. In this process we often come across SQL_ID, HASH_VALUE and PLAN_HASH_VALUE details and use them for […]
Generating EXECUTION PLAN from explain plan is the best method of reading the plan that optimizer defined for executing a SQL statement in Oracle Database. There are multiple ways of generating such execution plans, one is by running EXPLAIN PLAN and other from V$SQL_PLAN. In this blog, I am going to cover in detail about […]
EXPLAIN PLAN is often used to generate execution plan of SQL statement or set of SQL statements. But we would always prefer to run EXPLAIN PLAN for each SQL statement and generate its execution plan using DBMS_XPLAN package and then pick up next SQL statement. But we can also run EXPLAIN PLAN for multiple SQL […]
