FULL Table Scan(DB file Scattered read) Vs INDEX Scan(DB file sequential read)

DBAs and Developers often look at FULL Table Scans or INDEX scans happening within their database and code respectively. It is often assumed that FULL Table Scan would make the SQL statement perform slower.

Let us try and clear this misconception. 
FULL Table Scan(DB file scattered read) is not necessarily an expensive operation as compared to INDEX scan. It all depends on what the SQL is trying to do. If the SQL statement is returning more than 10% of the total volume in the table, FULL Table Scan is actually faster than Index Scan. This is because an index is scanned one block at a time whereas in-case of FULL Table Scan, multiple blocks, as determined by 'DB_file_multiblock_read_count, are fetched and read into the memory.

In a nutshell, if the SQL statement serves more as a batch query rather than OLTP, FTS(FULL Table Scan) is actually faster than Index read.

The reverse is true in case of Online Transactions because your SQL statement is expected to return only a few rows.

Have a specific case to discuss, please leave a comment or reach me at munish07@gmail.com. I would be glad to answer. 

SQL running long?

If you are an IT person, I am sure you know what I am talking about. You have a SQL statement or a program/job that you run every day and it completes in a few minutes but today it has been running for hours. Nothing has changed as far as you know. It is still supposed to process the similar amount of volume. No change in the code yet the SQL does not seem to be finishing.

The answer is simple. It's execution plan has changed. Your DBA should be able to confirm that or you could check it out yourself provided you know the hashvalue or the SQL_ID of the SQL in question.

Run the below SQL for the sql_id in question. Oracle optimizer generates a plan hash value for every execution plan it generates and executes. If you see different plan hash values for the same SQL hashvalue or SQL_ID, this will confirm that SQL statement plan has changed and hence it is running long.


select
inst_id,sql_id,plan_hash_value,child_number,executions,round(buffer_gets/executions,2)
"buffer_gets/exec" , cpu_time/1000000 "Cpu Time(s)",round(rows_processed/executions,2) "Rows",
elapsed_time/1000000 "Elpased Time(s)"  from gv$sql where
sql_id='&sql_id'

Once you know that SQL plan did change, you would obviously want to know, what are the new and the old execution plans.
Run the below SQL Statement from the SQL_ID in question and for each child_cursor taken from above sql.


select * from TABLE(dbms_xplan.display_cursor('&SQL_ID',&child_cursor,'PEEKED_BINDS'))

Have a question? Please leave a comment and I would be glad to answer.

Database Stats

Has it every happened to you that a given database table has thousands or millions or rows but dba_tables shows 0 rows once you generated the statistics on the table?
Does it ring a bell?

Here is a simple scenario to test.

Like it happens it most data conversions, records get inserted into a staging table from a flat file. However, if we generate statistics on this table before a commit happens, the statistics will get generated but it would show 0 rows.

Hence, we should generate database statistics only after commit has been issued on the table after massive data inserts/deletes/updates.

So, go back to your code and review the position of commit and the command to generate the statistics. Commit should happen prior to generating statistics.

Have a question? Please leave a comment and I would be glad to answer.

CPU utilization at 100%; What is killing the database

More often than not as a System Administrator or as a DBA, we run into situations where the host machine is running at 100% used CPU or 0% idle cpu.

It is vital to find the processes rather quickly that are chewing up the CPU. On Solaris, we can use
prstat command that is excellent to report the processes in the sorted order.
prstat -s cpu -n 10
The above command shows the top 10 CPU consuming processes.

If indeed it is the memory that is running out, you can tweak the prstat command
prstat -s size -n 10
The above command shows the top 10 memory consuming processes.
Once we find the top CPU or memory consuming processes, we can map those processes to the actual database session to see what really is happening in those sessions.

select inst_id,module,status,sid,serial#,username,last_call_et
from gv$session where paddr in ( select addr from gv$process
where spid = &spid and inst_id=&inst_id)
The above RAC enabled command shows what those sessions are, given the process id from prstat command.

This is a quick method to get a handle on the processes that are killing the host machine.

Have a question? Please leave a comment and I would be glad to answer.

Why is my SQL query taking forever to run?

All of us must have been in this situation before.
Why is SQL taking forever to run today? Until today, it was taking minutes and today it is running forever.

This is the worst nightmare for a DBA. In this situation, the most likely scenario is that SQL is running with a different execution plan as opposed to when it was taking minutes.
How do we find it out?
The below SQL will give you all the child cursor for the SQL in question. Just pass the SQL id of the problem SQL. If you see multiple PLAN_HASH_VALUE for this given SQL, that means this SQL indeed has multiple execution plans. One of them being the efficient one that it had been using. 

select
inst_id,sql_id,plan_hash_value,child_number,executions,round(buffer_gets/executions,2)
"buffer_gets/exec" , cpu_time/1000000 "Cpu Time(s)",round(rows_processed/executions,2) "Rows",
elapsed_time/1000000 "Elpased Time(s)"  from gv$sql where
sql_id='&sql_id'
 
Once we know all the child cursors, run the below SQL to get the execution plan for all the child cursor and see why it is taking forever for this run.

select * from TABLE(dbms_xplan.display_cursor('&SQL_ID',&child_cursor,'PEEKED_BINDS'))

Just Pass the SQL id and the child cursor and you will be able to the execution plan.

What is running in my database?

Have you ever wondered what is running in the database at any given time and how to find it?

Simply run the below SQL command on your Oracle Database. It will give tell who is running what. Yes, this is RAC friendly. You may need to format the output. This is very handy to have for a DBA to see the snapshot of what is running in the database. It is a good first step to diagnose any Performance issue.

select  s.inst_id "Inst",s.sid||','||s.serial# sidserial,osuser,username,
   sql_id sql_id,
   module,
   substr(s.event,1,27) event,
   s.last_call_et "Wait", s.machine
from gv$session s
where status = 'ACTIVE'
and event not like 'Streams AQ: waiting%'
and event not like 'Streams AQ:%idle%'
and s.event not like 'LNS%'
and (s.event not like '%idle wait%')
and  s.event not in ('PL/SQL lock timer','SQL*Net message from client','queue messages',
'rdbms ipc message','Streams AQ: waiting for messages in the queue','smon timer','wakeup time manager','async disk IO',
'pipe get','pmon timer','gcs remote message','ges remote message','log file sync','jobq slave wait','slave wait')
order by module,sql_hash_value