Monthly Archive: May 2023

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.

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.

 

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.