DBMS Gather Table Stats | #dailyDBA 16

In this episode of daily DBA, I pick up 5 important DBA related questions and give my answers! Do not forget to checkout BONUS QUESTION at the end of the video!

Questions Picked-up For This Episode:

01:17 How to avoid row lock contention?

02:39 #dba Challenge!

03:13 What is Oracle Read consistency?

06:04 Could you please explain about FRA and the errors we usually see while initiating backup through FRA?

12:31 What is buffer cache hit ratio and use of it?

20:10 What will happen internally while gathering table statistics when we execute dbms_stats.gather_table_stats?

Bonus Question
22:53 Which version of the Oracle database would you recommend me to learn whether it is 12c ,11g ,10g,18c 19c?

#dailyDBA #oracleDatabase #cloudDBA #dbaGenesis

Your comments encourage us to produce quality content, please take a second and say ‘Hi’ in the comments and let me and my team know what you thought of the video … p.s. It would mean the world to me if you hit the subscribe button ;)
DBA Genesis provides all you need to build and manage effective Oracle technology learning. We designed DBA Genesis as a simple to use yet powerful online Oracle learning system for students. Each of our courses is taught by an expert instructor, and every course is available with a challenging project to push you out of your comfort zone!!

DBA Genesis is currently the fastest & the most engaging learning platforms for DBAs across the globe. Take your database administration skills to next level by enrolling into your first course.

Start your DBA Journey Today !!

#dbms

What is GEEK

Buddha Community

DBMS Gather Table Stats | #dailyDBA 16
Ruth  Nabimanya

Ruth Nabimanya

1623170220

Database Management System | DBMS Tutorial

The collection of similar data in one place refers to a database. Let’s not confuse it with data. Data is a collection of information in the form of facts and figures. The database allows users to manipulate data according to their comfort.

This includes retrieval, insertions, and removal of data. It organizes data in tabular, graphical, and many more forms. The database management system is software to help users in managing data.

It provides an interface for the users to handle data in various forms. This may be during database creating or during the time of update. It also ensures that the data is safe all time while maintaining consistency.

Data Definition Language is the scheme that the system follows to see what data will look like in the database. Some famous database management softwares are – MySQL, Oracle, etc. The task of the database management system includes –

1. Data Updation – It is where all the changes in data are made. This may be the addition, removal, or modification of data.

2. Data Retrieval – It allows retrieval of the data from the database for application usage.

**3. User Administration **– It registers, monitors, maintains and enforces data all the time. This includes securing it, controlling it, managing its performance, and recovering information in case of failure.

History of Database Management System

  • Charles Bachman came up with the first DBMS system in 1960.
  • Codd by IBM’S Information Management System enters in 1970.
  • Peter Chen introduces the Entity-relationship model in 1976.
  • The Relational Model became a database component in 1980.
  • Object-oriented DBMS develops in 1985
  • The incorporation of object orientation in DBMS takes place in the 1990s.
  • A personal DBMS by Microsoft – MS access came out in 1991.
  • The first Internet database applications came out in 1995.
  • XML became relevant to database processing in 1997.

#computer basics tutorials #advantages of dbms #applications of a dbms #architecture of dbms #basic dbms commands #components of a dbms #database management system #database schema #dbms #dbms vs. flat file #disadvantages of dbms #features of dbms #important dbms terms #popular dbms software #types of dbms #users of dbms

DBMS Gather Table Stats | #dailyDBA 16

In this episode of daily DBA, I pick up 5 important DBA related questions and give my answers! Do not forget to checkout BONUS QUESTION at the end of the video!

Questions Picked-up For This Episode:

01:17 How to avoid row lock contention?

02:39 #dba Challenge!

03:13 What is Oracle Read consistency?

06:04 Could you please explain about FRA and the errors we usually see while initiating backup through FRA?

12:31 What is buffer cache hit ratio and use of it?

20:10 What will happen internally while gathering table statistics when we execute dbms_stats.gather_table_stats?

Bonus Question
22:53 Which version of the Oracle database would you recommend me to learn whether it is 12c ,11g ,10g,18c 19c?

#dailyDBA #oracleDatabase #cloudDBA #dbaGenesis

Your comments encourage us to produce quality content, please take a second and say ‘Hi’ in the comments and let me and my team know what you thought of the video … p.s. It would mean the world to me if you hit the subscribe button ;)
DBA Genesis provides all you need to build and manage effective Oracle technology learning. We designed DBA Genesis as a simple to use yet powerful online Oracle learning system for students. Each of our courses is taught by an expert instructor, and every course is available with a challenging project to push you out of your comfort zone!!

DBA Genesis is currently the fastest & the most engaging learning platforms for DBAs across the globe. Take your database administration skills to next level by enrolling into your first course.

Start your DBA Journey Today !!

#dbms

Fredy  Larson

Fredy Larson

1595209620

How to alter tables in production when records are in millions

As a developer, I have experienced changes in app when it is in production and the records have grown up to millions. In this specific case if you want to alter a column using simple migrations that will not work because of the following reasons:

It is not so easy if your production servers are under heavy load and the database tables have 100 million rows. Because such a migration will run for some seconds or even minutes and the database table can be locked for this time period – a no-go on a zero-downtime environment.

In this specific case you can use MySQL’s algorithms: Online DDL operations. That’s how you can do it in Laravel.

First of all create migration. For example I want to modify a column’s name the traditional migration will be:

Schema::table('users', function (Blueprint $table) {
            $table->renameColumn('name', 'first_name');
        });

Run the following command php artisan migrate –pretend this command will not run the migration rather it will print out it’s raw sql:

ALTER TABLE users CHANGE name first_name VARCHAR(191) NOT NULL

Copy that raw sql, remove following code:

Schema::table('users', function (Blueprint $table) {
            $table->renameColumn('name', 'first_name');
        });

Replace it with following in migrations up method:

\DB::statement('ALTER TABLE users CHANGE name first_name VARCHAR(191) NOT NULL');

Add desired algorithm, in my case query will look like this:

\DB::statement('ALTER TABLE users CHANGE name first_name VARCHAR(191) NOT NULL, ALGORITHM=INPLACE, LOCK=NONE;');

#laravel #mysql #php #alter heavy tables in production laravel #alter table in production laravel #alter tables with million of records in laravel #how to alter heavy table in production laravel #how to alter table in production larave #mysql online ddl operations

Julie  Donnelly

Julie Donnelly

1596495120

Beginner’s Guide to Table Partitioning In PostgreSQL

Table partitioning in SQL, as the name suggests, is a process of dividing large data tables into small manageable parts, such that each part has its own name and characteristics.

Table partitioning helps in significantly improving database server performance as less number of rows have to be read, processed and returned. We can also use partitioning techniques for dividing indexes and index-organized tables.

Table partitioning can be of two types, namely, vertical partitioning or horizontal partitioning. In vertical partitioning, we divide the table column wise. While in horizontal partitioning, we divide the table row wise on the basis of range of values in a certain column.

Syntax and parameters

The basic syntax for partitioning a table using range is as follows :

Main table creation :

CREATE TABLE main_table_name (

column_1 data type,

column_2 data type,

.

.

. ) PARTITION BY RANGE (column_2);

Partition table creation :

CREATE TABLE partition_name

PARTITION OF main_table_name FOR VALUES FROM (start_value) TO (end_value);

The parameters used in the above mentioned syntax are similar to CREATE TABLE statement, except these :

PARTITION BY RANGE (column_2) : column_2 is the field on the basis of which partitions will be created.

partition_name : name of the partition table

FROM (start_value) TO (end_value) : The range of values in column_2, which forms the part of this partition. Note that start_value is inclusive, while end_value is exclusive.

Here is an example to illustrate it further.

Example

Imagine that you are working as a data engineer for an e-com firm that gets a huge number of orders on a daily basis. You usually store data such as order_id, order_at, customer_id etc. in a SQL table called “e-transactions’’. Since, the table has a humongous amount of data in it, the low load speed and high return time etc. have become a problem for data analysts, who use this table for preparing KPIs on a daily basis.

What will you do to improvise this table, so that data analysts can run queries quickly?

A logical step would be partitioning the table into smaller parts. Let’s say we create partitions such that the partition stores data pertaining to specified order dates only. This way, we will have less data in each partition and working on it will be more fun.

We can partition the table using declarative partitioning i.e. by using a PARTITION BY RANGE (column_name) function as shown below.

#postgresql #drop-table #sql #alter-table #table-partitioning

When to go for Full Table Scan? | #dailyDBA 9

In this episode of daily DBA, I pick up 6 important DBA related questions and give my answers! Do not forget to checkout BONUS QUESTION at the end of the video!

Questions Picked-up For This Episode:

01:19 Can we directly move from 12c to 19c or we have to firstly move to 18c and then 19c.

02:57 What is the impact of IMPDP parameter transform=disable_archive_logging on physical standby?

05:18 Can we change the redundancy level of asm diskgroup if yes then how?

07:27 What is the meaning of resolving gap using primary incremental backup?

10:26 Some of the DBAs say full table scan is better than index scan. Could you please clarify in which situations its better?

15:01 Why I’m missing AWR snapshot? Once I restarted the DB its started to generate the AWR snapshot?

Bonus Question
18:48 When ever i work in real time environment and when ever i try to toch the keyboard my hands start shaking & i tend to feel nervous in dealing with oracle databases?

#oracle #dba #cloud #dailyDBA

Your comments encourage us to produce quality content, please take a second and say ‘Hi’ in the comments and let me and my team know what you thought of the video … p.s. It would mean the world to me if you hit the subscribe button ;)

DBA Genesis provides all you need to build and manage effective Oracle technology learning. We designed DBA Genesis as a simple to use yet powerful online Oracle learning system for students. Each of our courses is taught by an expert instructor, and every course is available with a challenging project to push you out of your comfort zone!!

DBA Genesis is currently the fastest & the most engaging learning platforms for DBAs across the globe. Take your database administration skills to next level by enrolling into your first course.

Start your DBA Journey Today !!

#full table scan #table