Oracle Veda

Author Archive: Gopal

About Gopal

My first job was for Century Rayon, performing operations research studies similar to those which I did for them during Practice School. My boss left to join Wipro and offered me a job for the same kind of work. While presenting study findings, I always estimated and compared the costs of current and proposed solutions. Although quite basic, this was not part of the culture at that time. Mr. Premji loved it, and it quickly became a de-facto company-wide standard. This was before Wipro got into computers. A few years later, I switched careers to Information Technology, and soon got an opportunity to move to the US. After gaining some experience, I left my job as a project manager to become an independent consultant. On the side, I developed and sold productivity tools for Oracle database programmers. This slowly morphed into an IT staffing business. After the dotcom bubble burst, I fell back on my technical skills as an Oracle DBA. My last job was as a Vice President for an investment bank in New York city. During this time, I also developed an interest in investing, and taught myself about technical and fundamental analysis, options, and forex. After a seemingly endless period of trial and error, I learned enough to manage my own portfolio of aggressive growth stocks. Along the way, I got involved in what Ayn Rand would have called ‘intellectual activism’. After India’s nuclear tests in 1998, a commentator in the US media quipped, ‘as we found out this week, there is no India lobby.’ He was referring to the utter ineptitude of the Indian government in articulating its case. I decided to change that. I figured that public policy follows public opinion, which in turn is shaped largely by the media. So, I set out to change the way India was portrayed in the media. I attracted almost a hundred talented and dedicated volunteers with diverse skills. We set up a website and created content designed to resonate with the US public. We compiled a list of 1500 email addresses of editors and government officials; and developed software to automate broadcasting our positions to this list. At the time, this was quite rare, and hence very effective in getting our message across. In November 2001, the US Library of Congress decided to archive our website, as part of an effort to preserve for future scholars a sense of the times leading up to the September 11 attacks. My rationale for starting this project is included this archive, and can be found by googling ‘American Friends of India’, or at this URL: https://webarchive.loc.gov/legacy/20011114002835/http://www.americanfriends.org/msg4u.html The dominant influence in my career/life arc has been my education at BITS. I believe what I gained was not just a degree, but something far more valuable. I graduated with the conviction that I had been given the tools needed to acquire whatever knowledge I needed. This site was originally intended to provide guidance on common infra-DBA tasks. The scope has now expanded to include some of my hobbies. LinkedIn: https://www.linkedin.com/in/gopalsaraswat/

Big Bang Creation

The tendency to fit the data to support a pre-conceived idea is strong; so, strong that it can even affect core science. The redshift of distant galaxies provides an example of this. It was interpreted in a manner which endorsed the creationist view of cosmology. An alternative explanation is based on attenuation of the energy of light which supports the belief that the cosmos is infinite and eternal. Philosophers have debated these options for millennia and provided guidance for choosing between them.

To start with, the modern version of the creationist view of cosmology is only a century old. Edwin Hubble discovered that the Andromeda nebulae, as it was then called, was actually a galaxy in its own right, separate from the Milky Way. Soon millions more galaxies were discovered, thanks to advanced telescopes. They all had one thing in common – the light from them exhibited redshift. Hubble theorized that this was caused by their receding velocity and formulated Hubble’s Law of Cosmological Expansion. Georges Lemaitre, a cosmologist and a Catholic priest, extrapolated this to conclude that the universe was initially a single “primeval atom.” At some point it exploded to create the universe which has been expanding ever since. This is what has come to be known as the Big Bang Theory of cosmology. When Pope Pious welcomed this theory as scientific validation of the Biblical account of creation, Lamaitre was alarmed. Unwilling to contradict the Pope, he worried about appearing to conflate science with religion.

In contrast, consider a different interpretation of the redshift of light. Over cosmic distances, the energy of light attenuates; its frequency decreases while its wavelength increases. Observed data shows that the rate of this attenuation is 2.75 percent over every billion light years. In conjunction with the difference between the maximum and minimum wavelengths of visible light, this rate has been used to determine that the maximum distance which light can travel is 13.4 billion light years. Even the brightest galaxies located beyond this range cannot be seen because the light they emit falls below the low red end of the visible spectrum by the time it reaches us. This is the radius of the observable universe. While it might be tempting to believe that we are at the center of the universe, there is no reason to support this. No matter where an observer is located, the visual universe is always a sphere of this radius, and the observer is at its center. This is because the universe is infinite. And since matter can neither be created nor destroyed, it can be inferred that it is also eternal.

Finally, the task of choosing between competing theories can sometimes be helped by the Law of Parsimony, also known as Occam’s Razor. This principle states that the simplest explanation is more likely to be true. Applying this to the Big Bang Theory of creation is quite informative. This is because the expansion which begins with an explosion slows down in due course. However, the redshift of distant galaxies is more pronounced than that of nearer ones. This means that the expansion is accelerating! This contradicts the explosion hypothesis. Many explanations have been proffered for this anomaly, but they all create more problems than they solve. The alternative theory based on the attenuation of the energy of light does not involve such a contradiction and is therefore preferable.

To sum up, the simplest explanation for the observed redshift of cosmic light is attenuation. This supports the view that the universe is infinite, with no beginning, and no end. In contrast, there is the Big Bang Theory which parallels ancient creationist beliefs and holds that the universe is finite and started with an enormous explosion. Applying Occam’s Razor to these two options, the former is clearly simpler.

 

References:
How Far Visual Light Can Travel
https://www.ospublishers.com/How-Far-Visual-Light-Can-Travel.html

Georges Lemaître, Father of the Big Bang
https://www.amnh.org/learn-teach/curriculum-collections/cosmic-horizons-book/georges-lemaitre-big-bang

 

 

November 13, 2023 Philosophy, , Read more >

Using ChatGPT

Is ChatGPT a glorified parrot? To find out I gave it a series of prompts.

Does Plato’s Ethics recognize the existence of a conscience?
Does Aristotle’s Ethics recognize the existence of a conscience?
Is Atman and conscience the same?
Is there any relationship between Atman and conscience?

It provided useful information from which I was able to give words to my own opinion on the subject: When you quieten the discursive mind, you can see what the Atman sees, devoid of your attachments which may otherwise cloud your objectivity. And what you hear, that little voice, is the voice of your conscience telling you what the Atman sees.

I tested my theory with the following prompt:
Does conscience give voice to what the Atman sees?

ChatGPT responded that this is “not a common or traditional interpretation.” Then it went a step further and tried to gaslight me into thinking it is a bad idea 🙂

So, here is my conclusion. ChatGPT is a high-speed scanner. It is a boon for people who need/want to read more than is humanly possible. The concise summaries it provides can aid in connecting the big ideas, and in turn, inform business strategy and public policy. When it comes to normative questions, however, beware that its trainers have configured it to protect orthodoxy.

August 15, 2023 Philosophy, Technology Read more >

Philosophy, Religion, and Civilization

Rationalists fail to appreciate the role of religion in disseminating philosophy. These doctrines become the bedrock of a civilization. While there are many similarities, there are also significant dissensions between the teachings of different religions.

Philosophy is the foundation on which all our knowledge is built. It has several major branches. Metaphysics deals with the nature of reality. How we know this reality is the domain of epistemology. Deciding what is right and wrong is the sphere of ethics. How we live together under a system of laws is the field of politics.

These subjects are closely interrelated. Our ideas about the nature of reality determine what we believe to be true, which in turn determines our notions of right and wrong. The laws governing our interactions with each other are the result of our system of ethics.

Human beings are social and depend upon cooperation. This is possible only on the basis of trust. At an individual level, we must know someone well enough to determine if trust is warranted. Sociologists have theorized that a person can know about 150 people to this degree. During primitive times, this was also the typical size of a clan, which was basically a group of related families.

To trust people beyond this group, we need a system of ethics. Religions serve this purpose. At a fundamental level, there are two ways to accomplish this. The Abrahamic religions expect their ethics to be enforced through fear of a god. To make this system work, there can be only one god, and he needs to be kept as unknowable as possible.

Dharmic religions, on the other hand, teach that our sense of right and wrong is innate. It is the result of our cooperative nature. Collaboration works best under harmony. This is fostered by teaching the law of consequences, also known as karma.

In modern times, most nations accommodate people of many different religions. Often, the underlying ethical systems of these religions are quite antipodal. To overcome this problem, laws are made without regard to religion, to the extent possible. There is an implicit agreement that everyone will abide by these secular laws, even when they do not align with their religion. Further, these laws are expected to not favor one religion over another.

This is somewhat self-contradictory and impractical. Some differences cannot be resolved through compromise. There is no middle ground between supremacy and equality. Civics classes in school don’t help much. No one gets their sense of right and wrong by studying ethics. By the time we are ten years old, our morality is pretty much set in stone. And it is based on the religion followed by those who influenced us most during this formative period.

Religion is the basis of civilization. There can be no civilization without religion. It is what teaches us our philosophy of life. Religion is to philosophy what engineering is to science.

 

July 2, 2023 Philosophy Read more >

About ChatGPT


A language is a set of sounds for which there is a social pact governing meaning and grammar. There are two ways to learn a language. The first is as an adult learns a foreign language by studying its grammar and vocabulary. The second is as a child learns by imitating others.

ChatGPT uses the latter method. It scans a humongous amount of literature on the web, and takes iterative feedback from its trainers. Each of the 50,000 commonly used words in English are allotted a number. At any given point, it looks for the most common word which usually follows the text it has already generated. These words each have a context-based probability of occurrence. The next word is chosen using a degree of randomness. This process continues until a complete essay has been generated.

For creative work, the randomness must be higher. To get more precision, a lower level of randomness is needed. If ChatGPT were to always take the word with the highest probability, then its output would be monotonous and not have any human-like quality. Note that the essay is not reviewed. The process used is strictly unidirectional. Also, instead of actual words, ChatGPT uses word tokens, or parts of words. This is why it can sometimes create new words.

On a technical level, ChatGPT is basically a giant neural network. This technology is not new; it has been around since the 1940s. Advances in computing power have made it practical to use neural nets in a productive manner. Note that neural nets try to mimic the workings of the human brain. Software engineers don’t give specific instructions to AI and tell it what kind of text to generate. Instead, they code the rules which govern how the network makes decisions.

When given a prompt, ChatGPT does not go out and scan the web. It has already done that once, and created a dataset which represents probabilities of occurrence of each word in a given context. These probabilities are modified during training, with outcomes favored by the trainers getting higher weights. This dataset of probabilities and weights is used by the neural net to generate word tokens in an iterative manner.

The generic dataset of ChatGPT can be customized by adding a layer representing organizational policies, such as HR, Legal, IT, etc. The resulting product can be used by employees to get answers to frequently asked questions. To get consistent results, a lower degree of randomness must be used. This customized dataset would represent the key to an organization’s business secrets and would need to be protected.

ChatGPT, or AI in general, is not going to replace humans. It is not going to take over the world and destroy humanity. The reasons for this are partly technological, and partly philosophical. Dwelling on them in more detail here would take us off course. However, this should not lull us into underestimating the impact of AI. It is going to geometrically increase productivity and create whole new industries.

In the short term, this will mean job losses to some extent. We should not fear this. To put this in perspective, the advent of computers did not lead to large scale job losses, destitution, and riots. We simply transitioned, quite smoothly, into a far more productive world. This has been going on for a very long time, from the invention of power looms hundreds of years ago. Our best approach would be to embrace the new technology, and avail ourselves of the opportunities it provides.

May 24, 2023 Technology, Read more >

Use ChatGPT to Tune SQL

Using ChatGPT to tune a SQL statement is easy. Here’s an example.

 

Prompt: Write an Oracle SQL script to tune a SQL statement based on SQL ID.
ChatGPT: Here’s an Oracle SQL script that can be used to tune a SQL statement based on SQL ID:

Here’s an Oracle SQL script that can be used to tune a SQL statement based on SQL ID:

DECLARE
l_sql_text CLOB;
l_sql_id VARCHAR2(13) := ‘<SQL_ID>’; — replace with the SQL ID of the statement you want to tune
BEGIN
SELECT sql_fulltext INTO l_sql_text FROM v$sql WHERE sql_id = l_sql_id;

DBMS_SQLTUNE.create_tuning_task(sql_text => l_sql_text, task_name => l_sql_id);

DBMS_SQLTUNE.execute_tuning_task(task_name => l_sql_id);

DBMS_SQLTUNE.drop_tuning_task(task_name => l_sql_id);
END;

This script creates a SQL tuning task for the SQL statement with the specified SQL ID, executes the task, and then drops the task. The sql_fulltext column in the v$sql view contains the complete text of the SQL statement, which is used as input to the create_tuning_task procedure. The task_name parameter is set to the SQL ID to ensure that the task is associated with the correct statement. The execute_tuning_task procedure executes the task, which can take several minutes to complete depending on the complexity of the statement. The drop_tuning_task procedure removes the task from the database once it is completed.

After executing this script, you can view the tuning results by querying the dba_advisor_log table. This table contains information about all tuning tasks that have been executed on the database. To view the results for the task created by this script, you can use a query like this:

SELECT dbms_sqltune.report_tuning_task(‘<SQL_ID>’) FROM dual;

This will generate an HTML report that provides recommendations for tuning the SQL statement.

 

May 14, 2023 Oracle DBA, , , Read more >

Use ChatGPT to Find Top SQL

You can now use ChatGPT to rapidly create scripts. You still need to know what to do. The process of finding how to do it is now much simpler. You don’t need to use Google to get a list of urls and browse them. And you certainly don’t need to remember commands and syntax. Here’s an example.

 

Prompt: Write an Oracle SQL script to identify most expensive queries in the last hour.
ChatGPT: Here’s an Oracle SQL script that can identify the most expensive queries in the last hour:

SELECT sql_text, executions, elapsed_time, cpu_time
FROM (
SELECT sql_id, sql_text, executions,
elapsed_time_delta/1000000 AS elapsed_time,
cpu_time_delta/1000000 AS cpu_time,
disk_reads_delta, buffer_gets_delta,
ROW_NUMBER() OVER (ORDER BY elapsed_time_delta DESC) AS row_num
FROM v$sql
WHERE last_active_time > SYSDATE – (1/24) — filter for queries executed in the last hour
ORDER BY elapsed_time_delta DESC
)
WHERE row_num <= 10; — adjust this number to show more or fewer queries

This script uses the v$sql view to retrieve information about SQL statements that have been executed recently. The elapsed_time_delta and cpu_time_delta columns provide information about the execution time and CPU time for each query. The script sorts the queries by the elapsed_time_delta column in descending order, and then uses the ROW_NUMBER function to assign a row number to each query. Finally, the script selects the top 10 queries based on the row number. You can adjust the row_num condition to show more or fewer queries.

 

 

May 14, 2023 Oracle DBA, , , Read more >

Free Will

It is believed that people’s behavior is largely determined by forces not of their own making. To examine this premise, we need to first identify the major factors which determine behavior. These are genes, environment, and mind. The genetic material we inherit is completely out of our control and plays a major part in deciding who we are. Our environment is also largely out of our control, although we do interact with it, and in the process change it. Our mind is the most nebulous factor. Science can deal with the brain, but the mind, which by virtue of being conscious, defies the limits of our double-blind experiments. We need to examine how these forces interact, and see what agency, if any, they leave us for navigating our lives.

First, and perhaps the most transparent element which is not under our volition, is our genes. They determine our raw physical and mental capabilities, which in turn, will lead us to choose our careers and hobbies. Athletes have unusual physiques, strength, and speed. The keenness of our senses is also a function of our genes. Golfers have an extraordinary sense of touch. Exceptional hearing and voice are assets for musicians and singers, as is sight for painters. Rare mental skills open up avenues in a variety of fields. At a more subtle level, our hormonal make-up, which determines our temperament, is also dependent on our genes. Granted, many could have these traits, but not choose to develop them, but still, winning the genetic lottery is a prerequisite.

This brings us to the role of the environment. The latest thinking is that even though we cannot change our genes, we can influence how they manifest themselves. For example, identical twins can grow up to be of different heights. One could have been subject to disease and malnutrition and hence ended up being shorter. They could also have different personalities. Our hormonal make-up is not static. It is influenced by our diet and our experiences. Someone who has faced abuse is likely to exhibit more fear and anger. In contrast, someone who had a nurturing and engaging upbringing will have more confidence and trust.

Further, while the environment plays an important role in developing our personality, the way we react to the things which happen to us is of no less important. When faced with wrong-doing, one person may react with anger and seek revenge, thus exacerbating the problem. On the other hand, another might take a more lenient approach and pardon the offence, thus minimizing its impact. Similarly, when faced with a string of misfortunes, one person might get disheartened and give up, and just live with lower expectations. The exact same circumstances might prod a more tenacious person to redouble their effort and try again and again, learning from past mistakes, and improving until achieving success.

With this in mind, let us examine the role of free will. One may argue that every atom in our body is subject to the unyielding laws of physics. How then could we possibly have free will? Add to this the fact that every atom outside our body follows those same laws, accounting for the influences of our environment. And yet, our experience tells us that we do have volition. All aspects of our society assume that we do. This includes not just the law, but also things like religion. Some might extend this argument to advocate that instead of punishing errant individuals, we should focus on educating them and providing them with better opportunities. Opponents of this way of thinking point out that under those same circumstances, a large majority of others behave in a much more positive manner, and that rewarding bad behavior is counterproductive.

At any rate, the role of the mind needs further scrutiny. Although we have no control on our genes, and limited control on our environment, we do have some control over our minds. We all get a good number of random thoughts every minute. This is something which is not under our direct control. However, we can decide what to do with a thought once it occurs. We can choose which ones to pursue, and which ones to gently set aside. The thoughts we decide to focus on more will change the mix of thoughts that come to us randomly. The ones we choose to dwell on will occur more often than the ones we ignore. Thoughts lead to action. Repeated actions become habits. Our habits build our character, which in turn determines our destiny. Yet, the fact remains that most of us are not even aware of what we think about. How then can we control our destiny by deciding what to focus on?

To that end, self-awareness is the answer, and it is a skill which can be developed through training. It has been scientifically verified that practices such as meditation increase the size of the frontal cortex which is active in self-control and decision-making. It also shrinks the amygdala and that makes us less prone to unthinking emotional reactions. It is true that most people do not do anything to increase self-awareness. They do not even know that such a thing is possible. You can go through your entire life, simply reacting to situations, and never pausing to think and examine what you are doing. Perhaps this is what Socrates meant when he said that an unexamined life is not worth living.

In summary, there is a strong case for the notion that our behavior is mostly determined by forces outside of our control. This is true for our genes, and to a lesser extent, for our environment. The argument that we are simply automatons can only be challenged by examining the workings of our minds. Understanding the role of self-awareness and how it can be developed gives us control over our behavior.

April 23, 2023 Philosophy, , Read more >

Spacetime Curvature

Spacetime is pretentious nonsense. Spacetime curvature is pretentious nonsense on steroids. To understand why, all we need is a rudimentary knowledge of commonly used terms.

According to the dictionary, space is the infinite three-dimensional realm in which everything exists. Time has been considered the fourth dimension of space. It signifies space at different times. For this extra dimension to be meaningful, space must change over time. This is not possible because space has no observable properties which can change, either physical or chemical. Space is immutable, always exactly the same, no matter what the time.

Space is infinite. If it were finite, it would have a boundary. What lies beyond that boundary? The very act of trying to visualize space as finite makes us see that it is not. Given this absence of a boundary, there is no way to identify a point in space with reference to distance from that boundary. And neither is such identification possible based on properties; space has none.

For space to curve, shrink, or expand, points in space would need to move. There is no way to prove or disprove that such a movement has occurred. If it is not possible to uniquely identify a point in space, then it follows that it is also not possible to measure the distance between two points in space.

Suppose we go to a blackboard in a classroom and mark an X on it. Can we use that point as a frame of reference? The answer is no, we cannot. That X identifies a point on a material object. It does not identify a point in space. The point in space which X occupies is constantly changing, moving several miles every second, given the spin of the Earth around its axis, its orbit around the Sun, and the Sun’s orbit around the center of the Milky Way galaxy, and so on. Material objects can curve, shrink, and expand, but not space.

Like space, time is also infinite and impossible to measure. If time had a beginning or an end, what happened before the beginning? What will happen after the end? The very act of trying to stipulate a point in time as the beginning or the end makes us see that it is not.

There is a famous story about Einstein getting on a tram and looking back at the tower of Bern. If the tram were moving at the speed of light, it is argued, then time would freeze, and the clock would continue to show the same time. There is an entire cottage industry built around this incident, with complex mathematical equations painstakingly demonstrating the significance of the frame of reference of the observer.

It would be useful here to go over a popular high school math problem. It has to do with two moving trains. Given their locations at a given point in time, and the direction and speed of their motion, the student is asked to calculate their locations at a point in time in the future.

The incident about the clock tower can be fully understood simply through this analogy. Instead of trains, we’re talking about light and a tram. If the tram started moving away at the speed of light from the clock tower when it showed 2PM, then an observer on the tram would continue to see it showing the time as 2PM. Any light which left the tower after that time would never catch up with the tram. However, what is frozen is the observer’s view of the clock tower, not time.

There have been a number of scientific experiments conducted showing that highly accurate atomic clocks run at different speeds depending on their elevation. This is taken to prove that gravity slows down time. This is erroneous; it is the clocks which run at different speeds at different elevations, not time. There is no way to measure time. We can use a clock to measure the length of an activity, or even the speed of another clock, but we cannot measure time itself.

Gravity has no effect on either space or time; this is because space and time do not have mass. Strangely, the same people who argue that gravity bends space and dilates time, implying that they both have mass, also postulate that photons have no mass. They are wrong on both counts.

There is one universal time in the entire infinite universe, and it moves forward at a constant pace. It has no beginning or end. And space is the infinite three-dimensional realm in which all matter exists. Matter moves through space and occupies different amounts of it over time. Matter reacts only with other matter, not with space or time. Space and time exist independently of each other and have no interaction.

Originally answered on Quora in 2017:

What is spacetime? What did Einstein mean when he said it was curved?

https://www.quora.com/What-is-spacetime-What-did-Einstein-mean-when-he-said-it-was-curved/answer/Gopal-Saraswat-1

April 16, 2023 Philosophy, , , Read more >

Move Database Files

In Oracle 12c, a data file can be relocated while it is online. The command updates the control file and dictionary, and moves the file at the OS level. The procedure for relocating temp files, online redo logs, and control files remains unchanged.

-- move data file

SQL> alter database move datafile '/u01/oradata/db1/db1/system01.dbf' to '/u01/oradata/db1/system01.dbf';

Here are some noteworthy facts about relocating data files:
– The datafile must be online when this command is issued.
– The file is first copied to the new location, and then deleted from the old; this requires twice the space.
– There is an option to keep the source file; the default is to delete it.
– If the destination file exits, you will get an error, unless the reuse option is specified.
– In Windows, the source file is sometimes not deleted, even if the keep option is not used.
– If you flashback the database, a moved file will not be placed back in its origianl location.
– Moving a datafile on a data guard primary DB does not move it on the standby.
– PDB datafiles cannot be moved from the root container.
– You can move files from/to ASM.
– There are no special considerations for RAC.

Demo

The example illustrates how to move database files. The demo environment consists of an Oracle 12cR1 database running on an Oracle Linux guest, using Virtual Box on Windows. The example below shows how to move data files online. It also shows one procedure for moving other database files.

Example

The online move feature introduced in 12c applies to all data files, inlcuding those belonging to the system tablespace. However, there is no change in the procedure for moving temp files, online redo logs, and control files. Temp files and online redo logs can be added and dropped while the database is running. Moving control files requires a database bounce.

Move all datafiles, including system and undo.

SQL> select file_id,file_name,status from dba_data_files order by file_id;

FILE_ID    FILE_NAME                              STATUS
---------- -------------------------------------- ---------
1          /u01/oradata/db1/db1/system01.dbf      AVAILABLE
3          /u01/oradata/db1/db1/sysaux01.dbf      AVAILABLE
4          /u01/oradata/db1/db1/undotbs01.dbf     AVAILABLE
5          /u01/oradata/db1/db1/example01.dbf     AVAILABLE
6          /u01/oradata/db1/db1/users01.dbf       AVAILABLE

SQL> alter database move datafile '/u01/oradata/db1/db1/system01.dbf' to '/u01/oradata/db1/system01.dbf';

Database altered.

SQL> alter database move datafile '/u01/oradata/db1/db1/sysaux01.dbf' to '/u01/oradata/db1/sysaux01.dbf';

Database altered.

SQL> alter database move datafile '/u01/oradata/db1/db1/undotbs01.dbf' to '/u01/oradata/db1/undotbs01.dbf';

Database altered.

SQL> alter database move datafile '/u01/oradata/db1/db1/example01.dbf' to '/u01/oradata/db1/example01.dbf';

Database altered.

SQL> alter database move datafile '/u01/oradata/db1/db1/users01.dbf' to '/u01/oradata/db1/users01.dbf';

Database altered.

Verify dictionary has been updated.

SQL> select file_id,file_name,status from dba_data_files order by file_id;

FILE_ID    FILE_NAME                        STATUS
---------- -------------------------------- ---------
1          /u01/oradata/db1/system01.dbf    AVAILABLE
3          /u01/oradata/db1/sysaux01.dbf    AVAILABLE
4          /u01/oradata/db1/undotbs01.dbf   AVAILABLE
5          /u01/oradata/db1/example01.dbf   AVAILABLE
6          /u01/oradata/db1/users01.dbf     AVAILABLE

Verify data files have been relocated to destination directory at OS level.

SQL> !ls /u01/oradata/db1
db1 example01.dbf sysaux01.dbf system01.dbf undotbs01.dbf users01.dbf

… and deleted from source location.

SQL> !ls /u01/oradata/db1/db1
control01.ctl control02.ctl redo01.log redo02.log redo03.log temp01.dbf

Temp files cannot be moved online; to avoid downtime, you can add a new file and delete the old one.

SQL> alter tablespace temp add tempfile '/u01/oradata/db1/temp01.dbf' size 32m autoextend on next 16m maxsize 1024m;

Tablespace altered.

-- drop temp file; if there are active temp segments in it, you will get an error.

SQL> alter database tempfile '/u01/oradata/db1/db1/temp01.dbf' drop including datafiles;

Database altered.

Redo logs also cannot be moved online; to avoid downtime, you can add a new file and then delete the old one.

SQL> select f.group#,member,bytes,l.status from v$log l,v$logfile f where l.group#=f.group# order by 1;

GROUP#     MEMBER                   BYTES        STATUS
---------- ------------------------ ------------ -----------
1 /u01/oradata/db1/db1/redo01.log     52,428,800 CURRENT
2 /u01/oradata/db1/db1/redo02.log     52,428,800 INACTIVE
3 /u01/oradata/db1/db1/redo03.log     52,428,800 INACTIVE

Drop ORLs in /u01/oradata/db1/db1 and recreate them in /u01/oradata/db1.

Start with group 2 which is inactive.

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database add logfile group 2 '/u01/oradata/db1/redo02.log' size 50m reuse;

Database altered.

Group 3 is also inactive and can be dropped & recreated.

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database add logfile group 3 '/u01/oradata/db1/redo03.log' size 50m reuse;

Database altered.

Switch logfile to make group 2 current.

SQL> alter system switch logfile;

System altered.

SQL> select f.group#,member,bytes,l.status from v$log l,v$logfile f where l.group#=f.group# order by 1;

GROUP#     MEMBER                             BYTES        STATUS
---------- ---------------------------------- ------------ ----------
1          /u01/oradata/db1/db1/redo01.log      52,428,800 ACTIVE
2          /u01/oradata/db1/redo02.log          52,428,800 CURRENT
3          /u01/oradata/db1/redo03.log          52,428,800 UNUSED

Group 1 is still active; perform a checkpoint to make it inactive.

SQL> alter system checkpoint;

System altered.

SQL> select f.group#,member,bytes,l.status from v$log l,v$logfile f where l.group#=f.group# order by 1;

GROUP#     MEMBER                                    BYTES STATUS
———- ———————————- ———— ———–
1          /u01/oradata/db1/db1/redo01.log      52,428,800 INACTIVE
2          /u01/oradata/db1/redo02.log          52,428,800 CURRENT
3          /u01/oradata/db1/redo03.log          52,428,800 UNUSED

Now group 1 is inactive and can be dropped & recreated.

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database add logfile group 1 '/u01/oradata/db1/redo01.log' size 50m reuse;

Database altered.

Verify all ORLs are now in /u01/oradata/db1.

SQL> select f.group#,member,bytes,l.status from v$log l,v$logfile f where l.group#=f.group# order by 1;

GROUP#     MEMBER                        BYTES        STATUS
---------- ----------------------------- ------------ ----------
1          /u01/oradata/db1/redo01.log     52,428,800 UNUSED
2          /u01/oradata/db1/redo02.log     52,428,800 CURRENT
3          /u01/oradata/db1/redo03.log     52,428,800 UNUSED

Delete ORLs in /u01/oradata/db1/db1.

SQL> !rm /u01/oradata/db1/db1/redo0?.log

 

Moving control files requires downtime. They are currently in /u01/oradata/db1/db1 and will be moved to /u01/oradata/db1.

SQL> select name from v$controlfile;

NAME
-----------------------------------
/u01/oradata/db1/db1/control01.ctl
/u01/oradata/db1/db1/control02.ctl

Change their location in the spfile to /u01/oradata/db1.

SQL> alter system set control_files='/u01/oradata/db1/control01.ctl','/u01/oradata/db1/control02.ctl' scope=spfile;

System altered.

Shut down the database.

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Move the files using OS commands.

SQL> !mv /u01/oradata/db1/db1/control01.ctl /u01/oradata/db1/control01.ctl

SQL> !mv /u01/oradata/db1/db1/control02.ctl /u01/oradata/db1/control02.ctl

Startup the database.

SQL> startup 
ORACLE instance started.

Total System Global Area 3607101440 bytes
Fixed Size 2930608 bytes
Variable Size 419432528 bytes
Database Buffers 3170893824 bytes
Redo Buffers 13844480 bytes
Database mounted.
Database opened.

Verify control files are in /u01/oradata/db1/.

SQL> select name from v$controlfile;

NAME
-----------------------------------
/u01/oradata/db1/control01.ctl
/u01/oradata/db1/control02.ctl

 

August 19, 2018 Oracle DBA, Read more >

Full Database Caching

Oracle decides what to cache in the buffer pool. Full scans of large tables are not cached. This has changed in 12c – if the database is smaller than the buffer, then Oracle automatically caches everything, except NOCACHE LOBs.

It is also possible to force the database to cache everything, including NOCACHE LOBs. This can be done even if the database is larger than the buffer. This is called force full database caching mode. The setting is stored in the control file, not the parameter file. Enabling and disabling force full database caching requires a database bounce.

-- database must be in mounted state
startup mount

-- enable
alter database force full database caching;

-- disable
alter database no force full database caching;

-- open
alter database open;

-- verify
select force_full_db_caching from v$database;

Here are some noteworthy facts about force full database caching:
– Oracle recommends it should only be used when the buffer cache is larger than the logical db size
– setting stored in control file, not parameter file
– in RAC, it is enabled either for all instances, or none of them
– in RAC, buffer cache of each instance should be larger than db
– if RAC instances are ‘well partitioned’, then it will suffice if combined buffer is larger than db
– in a multi-tenant configuration, it applies to the CDB and all PDBs
– when using ASMM, the buffer size should be assumed to be 60% of sga_target
– when using AMM, the buffer size should be assumed to be 36% of memory_target
– performance is most likely to improve when db is I/O bound, and there are repeated large table scans, and LOB reads

Keep in mind that the above apply only to force full database caching mode. The default full database caching mode is automatic and is triggered when Oracle detects that the buffer cache is larger than the database.

 

Demo

Two examples demonstrate the use of force full database caching. The first one uses a buffer cache which is larger than the logical database size. The second example explores the use of this feature when the buffer is a little smaller than the database.

Example 1

Force full database caching is enabled for a 4219 MB database with a 4500 MB buffer cache. A full table scan is run against a table with a 1309 MB data segment. As expected, a query against V$BH shows that table data is in the buffer cache.

Set environment and display contents of parameter file.

$ . oraenv
ORACLE_SID = [db1] ? db1
The Oracle base remains unchanged with value /u01/app/oracle
oracle@d12c1:/home/oracle [db1]

$ cat $ORACLE_HOME/dbs/initdb1.ora
audit_file_dest='/u01/app/oracle/admin/db1/adump'
audit_trail='db'
compatible='12.1.0.2.0'
control_files='/u01/oradata/db1/db1/control01.ctl','/u01/oradata/db1/db1/control02.ctl'
db_block_size=8192
db_domain=''
db_name='db1'
diagnostic_dest='/u01/app/oracle'
dispatchers='(PROTOCOL=TCP) (SERVICE=db1XDB)'
local_listener='LISTENER_DB1'
open_cursors=300
processes=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
db_cache_size=4500m
shared_pool_size=384m

Mount the database, enable force full database caching, and verify setting.

SQL> startup mount
ORACLE instance started.

Total System Global Area 5167382528 bytes
Fixed Size 2935080 bytes
Variable Size 419432152 bytes
Database Buffers 4731174912 bytes
Redo Buffers 13840384 bytes
Database mounted.

SQL> alter database force full database caching;

Database altered.

SQL> alter database open;

Database altered.

SQL> select force_full_db_caching from v$database;

FOR
---
YES

SQL>

Run a full table scan against table TEST.T.

SQL> select cust_income_level,count(*) from test.t group by cust_income_level order by 1;

CUST_INCOME_LEVEL                COUNT(*)
------------------------------ ----------
A: Below 30,000                    336640
B: 30,000 - 49,999                 353792
C: 50,000 - 69,999                 544640
D: 70,000 - 89,999                 667776
E: 90,000 - 109,999               1015808
F: 110,000 - 129,999              1348736
G: 130,000 - 149,999               699520
H: 150,000 - 169,999               699520
I: 170,000 - 189,999               584448
J: 190,000 - 249,999               384768
K: 250,000 - 299,999               247552
L: 300,000 and above               215552
                                     5248

13 rows selected.

Display data segment size of table TEST.T, against which a full table scan was just run.

SQL> select owner,segment_name,segment_type,round(bytes/1024/1204) from dba_segments where segment_name='T';

OWNER                 SEGMENT_NAME                   SEGMENT_TYPE       ROUND(BYTES/1024/1204)
--------------------- ------------------------------ ------------------ ----------------------
TEST                  T                              TABLE                                1306


Check contents of buffer cache by querying V$BH. Of the 202027 blocks in the buffer, 193381 are occupied by table TEST.T data.

SQL> SELECT o.OBJECT_NAME, o.OBJECT_TYPE, o.OWNER, COUNT(*) NUMBER_OF_BLOCKS, round(count(*) * 8/1024) Tot_mb
       FROM DBA_OBJECTS o, V$BH bh WHERE o.DATA_OBJECT_ID = bh.OBJD AND o.OBJECT_NAME = 'T'
      GROUP BY o.OBJECT_NAME, o.OWNER, o.OBJECT_TYPE;

OBJECT_NAME  OBJECT_TYPE     OWNER      NUMBER_OF_BLOCKS     TOT_MB
------------ --------------- ---------- ---------------- ----------
T            TABLE           TEST                 193381       1511


SQL> select count(*) from v$bh;

COUNT(*)
----------
202027

Example 2

Force full database caching is enabled for a 4219 MB database with a smaller buffer cache of only 3000 MB. A full table scan is run against a table with a 1309 MB data segment. As in the previous example, the scan populates the buffer, demonstrating that this feature can be enabled even if the buffer is smaller than the DB. However, this is not a desirable situation, unless we can exclude this large table from the buffer. To do this a small recycle pool is configured, and the table modified to use it.

Display contents of parameter file.

$ cat $ORACLE_HOME/dbs/initdb1.ora
audit_file_dest='/u01/app/oracle/admin/db1/adump'
audit_trail='db'
compatible='12.1.0.2.0'
control_files='/u01/oradata/db1/db1/control01.ctl','/u01/oradata/db1/db1/control02.ctl'
db_block_size=8192
db_domain=''
db_name='db1'
diagnostic_dest='/u01/app/oracle'
dispatchers='(PROTOCOL=TCP) (SERVICE=db1XDB)'
local_listener='LISTENER_DB1'
open_cursors=300
processes=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
db_cache_size=3000m
db_recycle_cache_size=16M
shared_pool_size=384m

Mount the database, enable force full database caching, and verify setting

SQL> startup mount
ORACLE instance started.

Total System Global Area 3623878656 bytes
Fixed Size 2930656 bytes
Variable Size 419432480 bytes
Database Buffers 3187671040 bytes
Redo Buffers 13844480 bytes
Database mounted.

SQL> alter database force full database caching;

Database altered.

SQL> alter database open;

Database altered.

SQL> select force_full_db_caching from v$database;

FOR
---
YES

Display size of database.

SQL> select round(sum(bytes/1024/1024)) from dba_segments;

ROUND(SUM(BYTES/1024/1024))
---------------------------
                       4219

Display size of table TEST.T, and perform full table scan on it.

SQL> select owner,segment_name,segment_type,round(bytes/1024/1204) from dba_segments where segment_name='T';

OWNER      SEGMENT_NAME     SEGMENT_TYPE    ROUND(BYTES/1024/1204)
---------- ---------------- --------------- ----------------------
TEST       T                TABLE                             1306


SQL> select cust_income_level,count(*) from test.t group by cust_income_level order by 1;

CUST_INCOME_LEVEL                COUNT(*)
------------------------------ ----------
A: Below 30,000                    336640
B: 30,000 - 49,999                 353792
C: 50,000 - 69,999                 544640
D: 70,000 - 89,999                 667776
E: 90,000 - 109,999               1015808
F: 110,000 - 129,999              1348736
G: 130,000 - 149,999               699520
H: 150,000 - 169,999               699520
I: 170,000 - 189,999               584448
J: 190,000 - 249,999               384768
K: 250,000 - 299,999               247552
L: 300,000 and above               215552
                                     5248

13 rows selected.

Query V$BH to verify that the scan did populate the buffer, as a result of force full database caching.

SQL> SELECT o.OBJECT_NAME, o.OBJECT_TYPE, o.OWNER, COUNT(*) NUMBER_OF_BLOCKS, round(count(*) * 8/1024) Tot_mb
       FROM DBA_OBJECTS o, V$BH bh WHERE o.DATA_OBJECT_ID = bh.OBJD AND o.OBJECT_NAME = 'T'
      GROUP BY o.OBJECT_NAME, o.OWNER, o.OBJECT_TYPE;

OBJECT_NAME  OBJECT_TYPE     OWNER      NUMBER_OF_BLOCKS     TOT_MB
------------ --------------- ---------- ---------------- ----------
T            TABLE           TEST                 193383       1511

Modify table TEST.T so that it uses the 16 MB recycle pool, rather than clean out the main buffer.

SQL> alter table test.t storage (buffer_pool recycle);

Table altered.

Bounce the database.

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup pfile=?/dbs/initdb1.ora
ORACLE instance started.

Total System Global Area 3607101440 bytes
Fixed Size 2930608 bytes
Variable Size 419432528 bytes
Database Buffers 3170893824 bytes
Redo Buffers 13844480 bytes
Database mounted.
Database opened.
SQL>

Verify TEST.T is not in the buffer.

SQL> SELECT o.OBJECT_NAME, o.OBJECT_TYPE, o.OWNER, COUNT(*) NUMBER_OF_BLOCKS, round(count(*) * 8/1024) Tot_mb
       FROM DBA_OBJECTS o, V$BH bh WHERE o.DATA_OBJECT_ID = bh.OBJD AND o.OBJECT_NAME = 'T'
      GROUP BY o.OBJECT_NAME, o.OWNER, o.OBJECT_TYPE;

no rows selected

Perform full table scan on TEST.T.

SQL> select cust_income_level,count(*) from test.t group by cust_income_level order by 1;

CUST_INCOME_LEVEL                COUNT(*)
------------------------------ ----------
A: Below 30,000                    336640
B: 30,000 - 49,999                 353792
C: 50,000 - 69,999                 544640
D: 70,000 - 89,999                 667776
E: 90,000 - 109,999               1015808
F: 110,000 - 129,999              1348736
G: 130,000 - 149,999               699520
H: 150,000 - 169,999               699520
I: 170,000 - 189,999               584448
J: 190,000 - 249,999               384768
K: 250,000 - 299,999               247552
L: 300,000 and above               215552
5248

13 rows selected.

Query V$BH and verify that the scan populated only the 16 MB recycle pool, and did not force out other residents of the main buffer.

SQL> SELECT o.OBJECT_NAME, o.OBJECT_TYPE, o.OWNER, COUNT(*) NUMBER_OF_BLOCKS, round(count(*) * 8/1024) Tot_mb
       FROM DBA_OBJECTS o, V$BH bh WHERE o.DATA_OBJECT_ID = bh.OBJD AND o.OBJECT_NAME = 'T'
      GROUP BY o.OBJECT_NAME, o.OWNER, o.OBJECT_TYPE;

OBJECT_NAME  OBJECT_TYPE     OWNER      NUMBER_OF_BLOCKS     TOT_MB
------------ --------------- ---------- ---------------- ----------
T            TABLE           TEST                   1967         15

SQL> select count(*) from v$bh;

COUNT(*)
----------
      9875

 

July 26, 2018 Oracle DBA Read more >