Difference between scattered read and sequential read

A db file sequential read is an event that shows a wait for a foreground process while doing a sequential read from the database. 

This is an o/s operation, most commonly used for single block reads. Single block reads are mostly commonly seen for index block access or table block access by a rowid (Eg: to access a table block after an index entry has been  seen)

This can also be a multiblock read. Then it will usually be a read from a SORT (TEMPORARY) segment as multiblock reads for table scans (and index fast full scans) usually show up as waiting on "db file scattered read"

A db file scattered read is the same type of event as "db file sequential read", except  that Oracle will read multiple data blocks. Multi-block reads are typically used on full  table scans. The name "scattered read" may seem misleading but it refers to the fact that multiple blocks are read into DB block buffers that are 'scattered' throughout memory.

if a query plan is "index range scan, table access by index rowid", it will use db file sequential reads, read index, read table block, read index, read table block - all single block IO. 

There are many blocks being read - sequentially from index to table, index to table. That is what db file sequential read 'means', sequentially from index to table. db file sequential read is the wait even for SINGLE BLOCK IO. A block at a time, block by block, from index to table. 

If We have a query on a table like 'select * from t where a=10', and we don't have an index on column a. Further, the FTS resulting from this query just returns 1 row.. is it a scattered read because all the blocks were scanned & read into the buffer to find that 1 block..?.. 

It would probably employ multiblock reads - which if done using physical IO would wait on db file scattered reads - meaning read a bunch of blocks and SCATTER them in the buffer cache.

Source: Ask Tom Site of Oracle

Terminator Termina Shortcut in Ubuntu

Its very useful to use shortcut than moving cursor operate terminal. Here is the shortcut for operating Terminator terminal in Ubuntu :

Excuting local shell script to remote Server

System admin needs to get different information from different servers for various purposes at different times. For the case of collecting informartion from many different servers, its best practise to make a script with the required query and execute that on those servers and collect the output. And that output may be needed to transfer to location PC for further annalysis.

The above task is so much time consuming for the case of different severs and gatthering the inforamtion in the local PC.  To get rid from the fatigue, we can perfrom the task with a little tricks.

Step 1: Wer can write down the scirpt with all the commands to execute on those servers.

Step 2: We can execute the script on the remote servers from local machine and forward the output to the output file.

We are focusing here the process of executing the local script to remote servers.

Suppose, we have a script[dbhostcheck.sh] as below for checking the system information:

#! /bin/bash

echo "Host Name:"|hostname

echo
echo "||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||"
echo "Disk Checking"
echo "||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||"
df

echo
echo "||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||"
echo "CPU Utilization"
echo "||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||"
sar 1 10

echo
echo "||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||"
echo "Memory Utilization"
echo "||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||"

vmstat 1 10

NO we want to execute this script on a remote server. For this purpose we can excute it with the below command:

ssh user_name@name_of_host "/usr/local/bin/bash -s" -- <./dbhostcheck.sh >output.txt

Through the ssh user_name@name_of_host we are remotely accessing the host
"/usr/local/bin/bash -s": we are making the the bash for collecting standard output.-- <./dbhostcheck.sh:  Providing script information to the bash
 >output.txt redirecting the output to the file names  output.txt

Finding jobs currently running or history about the jobs

SELECT job_name, session_id, running_instance, elapsed_time, cpu_used FROM dba_scheduler_running_jobs;

Also one can use the following view to find the history details of job that has run.

SELECT job_name, log_date, status, actual_start_date, run_duration, cpu_used FROM dba_scheduler_job_run_details;

To find the jobs that haven’t succeeded
SELECT job_name, log_date, status, actual_start_date, run_duration, cpu_used FROM dba_scheduler_job_run_details where status ‘SUCCEEDED’;

ORA-01720: grant option does not exist for a table

We need users of specific role, need select privileges on tables and views owned by schema owners.


However while We try to grant select privileges on some views, I come accross a somewhat particular error as:

paul @ agentdb-live > grant select on AGENTBIP.VIEW_CUST_AGENT_DIST_INFO to ROLE_ITDD_OT;
grant select on AGENTBIP.VIEW_CUST_AGENT_DIST_INFO to ROLE_ITDD_OT
                         *
ERROR at line 1:
ORA-01720: grant option does not exist for 'MMUSER_GW.DBBL_AGENT_DATA'

The reason for it is that the view VIEW_CUST_AGENT_DIST_INFO owned by AGENTBIP is built on top of the table DBBL_AGENT_DATA owned by someone else ie MMUSER_GW.

AGENTBIP cannot give privileges on these kind of views to someone else ie USER_1 as long as OWNER_VIEW has not the privileges WITH GRANT OPTION for the underlying tables

The solution

paul @ agentdb-live > Grant select on MMUSER_GW.DBBL_CONSUMER_ACCOUNT to AGENTBIP with grant option;

Grant succeeded.

SYSTEM @ agentdb-live > grant select on AGENTBIP.VIEW_CUST_AGENT_DIST_INFO to ROLE_ITDD_OT;

Grant succeeded.