optimizer parameters in oracle 19c db

begin

for x in (select name from v$parameter where name like 'optimizer%')

loop

dbms_output.put_line(x.name);

end loop;

end;

-------------------------------------------------------
optimizer_features_enable
optimizer_mode
optimizer_index_cost_adj
optimizer_index_caching
optimizer_dynamic_sampling
optimizer_ignore_hints
optimizer_secure_view_merging
optimizer_use_pending_statistics
optimizer_capture_sql_plan_baselines
optimizer_use_sql_plan_baselines
optimizer_use_invisible_indexes
optimizer_adaptive_reporting_only
optimizer_adaptive_plans
optimizer_inmemory_aware
optimizer_adaptive_statistics
optimizer_ignore_parallel_hints

----------------------------------------------------------

-- Enable timing of SQL statements

SET TIMING ON;

-- Enable autotrace for detailed execution plans

SET AUTOTRACE ON;

-- Set the threshold for identifying slow queries (adjust as needed)

ALTER SESSION SET STATISTICS_LEVEL = ALL;

ALTER SESSION SET TIMED_STATISTICS = TRUE;

ALTER SESSION SET SQL_TRACE = TRUE;

-- Your SQL query goes here

SELECT *

FROM your_table

WHERE your_condition;

-- Reset session settings

ALTER SESSION SET STATISTICS_LEVEL = TYPICAL;

ALTER SESSION SET TIMED_STATISTICS = FALSE;

ALTER SESSION SET SQL_TRACE = FALSE;

-- Disable timing of SQL statements

SET TIMING OFF;

-- Display execution plan

-- Uncomment the line below if you want to see the execution plan

-- SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

> /dev/null 2>&1 to prevent errors

> /dev/null : Redirects standard output to /dev/null, discarding it.

2>&1 : Redirects standard error to the same location as standard output. This ensures that both

standard output and standard error are discarded.

script.sh > script.log 2>&1

to prevent errors :-

command or script > /dev/null 2>&1

echo -e "something" > filename enable backlash escape

 echo -e "india\nmaharashtra" > india.txt

cat india.txt

india

maharashtra 

#!/usr/bin/expect to automate Linux commands ?

 #!/usr/bin/expect

spawn some_command   # Replace some_command with the command you want to automate

expect "ExpectedPrompt"   # Replace ExpectedPrompt with the prompt you expect

send "your_input\r"   # Replace your_input with the input you want to provide

interact   # Allows you to interact with the spawned process manually if needed

chmod 777 example.exp 

./example.exp

example.: 

#!/usr/bin/expect

spawn su - goldengate

expect "Password: \r"

send "your_password\r"

expect eof    # wait for spawn processes to finish

\r :- carriage return simulate "Enter" 

\e or \x1B :- for escape key

Ctrl-a : - control plus a 

[root@localhost ~]# cat send.sh

#!/bin/bash

 

 

expect<<!!

spawn vim ./file02.txt

expect "insert mode:\r"

send "i\r"

expect "type some content:\r"

send "I am living in Pune.\r"

expect "save exit\r"

send "\x1B"

send ":wq\r"

expect eof

!!

grep command

 grep -v "^#|^$" filename 

^# :- line start with comment #

^$ :- empty lines 

[a-zA-Z0-9_] :- alpha numeric with _ 

grep -o "^.\{1,10\}" filename  :- find first 10 character word at the beginning of line.

grep -o ".\{1,10\}$" filename :- find last 10 character word at the end of line.

grep -o "^." filename  :- find one character for beginning of each line.

grep -o ".$" filename  :- find one character for end of the each line.

grep -e "^$" filename :- find the empty lines in the file.

grep -e "$" filename :- end of the line.

grep -e "^[a-zA-Z\]\{0,255\}\+\@\+[a-zA-Z\]\{0,255\}\+\:\+\~\+\\$" fun.txt : kayyum@AbdulKayyum:~$

grep -v "^[a-zA-Z\]\{0,255\}\+\@\+[a-zA-Z\]\{0,255\}\+\:\+\~\+\\$" fun.txt  : kayyum@AbdulKayyum:~$

kayyum@AbdulKayyum:~$ cat new.txt | grep -e "grep\|#" | grep -o "grep.*\|#.*"

# grep :- remove empty lines

grep -v "^$" sample.txt

# grep :- remove commented lines.

grep -v "^#" sample.txt

# grep :- to find oracle datafiles

grep -o "[a-zA-Z0-9_\]\{0,255\}\.dbf" sample.txt

# grep :- find first 10 characters each line

grep -o "^.\{1,10\}" sample.txt

# grep :- find last 10 characters each line

grep -o ".\{1,10\}$" sample.txt

# grep :- remove commented and empty lines

grep -v "^#\|^$" sample.txt

# grep :- to find ' " ' double quotes in the file

grep -e "\"" file.txt

# grep :- to find '{ ' and '}' curly braces

grep -e "\{" file.txt

grep -e "\}" file.txt

# combine

grep -e "\{\|\}" file.txt

grep -e "\"\.\"" file.txt

grep -e "[a-zA-Z\]\{0,255\}" file.txt

SED COMMAND ON LINUX

to view file content with line number:-

cat -n file.txt 

-- to delete last line in the file :-

sed -i '$d' filename 

-- to delete first line in the file :-

sed -i '1d' filename 

-- to append something 

[root@localhost ~]# sed -i '$a\

> export ORACLE_HOME=/u02/ogg19c\

> export PATH=$ORACLE_HOME/bin:$PATH\

> export LD_LIBRARY_PATH=$ORACLE_HOME/lib

> ' bash_profile

[root@localhost ~]# cat bash_profile

india

is my country

export ORACLE_HOME=/u02/ogg19c

export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

in above :- $ taken for last line of the file so append in the last.

-- to delete lines from file using sed command 

[root@localhost ~]# cat -n bash_profile

     1  india

     2  is my country

     3  export ORACLE_HOME=/u02/ogg19c

     4  export PATH=$ORACLE_HOME/bin:$PATH

     5  export LD_LIBRARY_PATH=$ORACLE_HOME/lib

[root@localhost ~]# # delete line number ---> 3 and 5 using below 

[root@localhost ~]# sed -i '3,5d' bash_profile

[root@localhost ~]#

[root@localhost ~]# cat -n bash_profile

     1  india

     2  is my country

SCHEMAS procedure and functions

set lines 400;
col host for a20;
col owner for a20;
col instance for a20;
col grantee for a20;
col grantor for a20;
col table_name for a40;
select (select host_name from v$instance) "host", (select instance_name from v$instance) "instance", owner, object_type, count(*), to_char(sysdate,'DD/MM/YYYY HH:MI:SS AM') "sysdate" from dba_objects where owner=upper('&owner') group by object_type,owner;

select (select host_name from v$instance) "host", (select instance_name from v$instance) "instance", owner, count(*) schema_obj_count from dba_objects where owner=upper('&owner') group by owner'

select * from dba_tab_privs where grantee=upper('grantee') and table_name in ('DBMS_LOCK','DBMS_JOB');

-- List all tables in all schemas
SELECT OWNER
	,table_name
FROM dba_tables;

-- List all views in all schemas
SELECT OWNER
	,view_name
FROM dba_views;

-- List all indexes in all schemas
SELECT OWNER
	,index_name
	,table_name
FROM dba_indexes;

-- List all columns of a specific table in all schemas
SELECT OWNER
	,table_name
	,column_name
	,data_type
FROM dba_tab_columns
WHERE table_name = 'your_table_name';

-- List all procedures in all schemas
SELECT OWNER
	,object_name
FROM dba_objects
WHERE object_type = 'PROCEDURE';

-- List all triggers in all schemas
SELECT OWNER
	,trigger_name
	,table_name
FROM dba_triggers;

-- List all sequences in all schemas
SELECT sequence_owner
	,sequence_name
FROM dba_sequences;

-- List all synonyms in all schemas
SELECT OWNER
	,synonym_name
	,table_name
FROM dba_synonyms;

-- List all constraints in all schemas
SELECT OWNER
	,constraint_name
	,constraint_type
	,table_name
FROM dba_constraints;

-- List all sequences in all schemas
SELECT sequence_owner
	,sequence_name
FROM dba_sequences;

-- List all materialized views in all schemas
SELECT OWNER
	,mview_name
FROM dba_mviews;

-- List all materialized view logs in all schemas
SELECT OWNER
	,log_table
	,master
FROM dba_mview_logs;

-- List all user-defined types (UDTs) in all schemas
SELECT OWNER
	,type_name
FROM dba_types;

-- List all packages in all schemas
SELECT OWNER
	,object_name
FROM dba_objects
WHERE object_type = 'PACKAGE';

-- List all package procedures and functions in all schemas
SELECT OWNER
	,object_name
	,PROCEDURE_NAME
	,function_name
FROM dba_procedures
WHERE object_type = 'PACKAGE';

-- List all indexes on a specific table in all schemas
SELECT table_owner
	,index_name
	,column_name
FROM dba_ind_columns
WHERE table_name = 'your_table_name';

-- List all database links in all schemas
SELECT OWNER
	,db_link
FROM dba_db_links;

-----------------------------------------------------

Creating simple procedure:-

-- Create a simple procedure that prints a message
CREATE OR REPLACE PROCEDURE print_message
IS
BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello, this is a simple procedure!');
END;
/

output:-

-- Execute the procedure

BEGIN

  print_message;

END;

/

--------------------------------------------------------------------------

Creating simple functions:-

-- Create a simple function that adds two numbers

CREATE OR REPLACE FUNCTION add_numbers(a NUMBER, b NUMBER)

RETURN NUMBER

IS

  result NUMBER;

BEGIN

  result := a + b;

  RETURN result;

END;

/

output:-

-- Call the function

DECLARE

  sum_result NUMBER;

BEGIN

  sum_result := add_numbers(5, 7);

  DBMS_OUTPUT.PUT_LINE('Sum: ' || sum_result);

END;

/

for loop in shell script ?

 #!/bin/bash

start=3

end=8

for i in $(seq $start $end); do

    if test ${i} -lt ${end}

    then

           echo "${i} less than ${n} "

     fi

     if test ${i} -eq ${end}

     then

            echo "${i} equal to ${n} "

    fi 

done

EXPDP & IMPDP Automation Shell Script ?

kayyum@AbdulKayyum:~$ cat *.sh

#!/bin/bash

select com in expdp impdp

do

        case ${com} in

                "expdp")

                        command="expdp "

                        select opt in userpassword directory dumpfile schemas tables logfile exclude include show_command Quite

                        do

                                case ${opt} in

                                        "userpassword")

                                                read -p "Enter user/password: " userpassword

                                                command+="${userpassword} "

                                                ;;

                                        "directory")

                                                read -p "Enter directory: " directory

                                                command+="directory=${directory} "

                                                ;;

                                        "dumpfile")

                                                read -p "Enter dumpfile: " dumpfile

                                                command+="dumpfile=${dumpfile} "

                                                ;;

                                        "schemas")

                                                read -p "number of schemas: " n

                                                schem=""

                                                for i in $(seq 1 $n)

                                                do

                                                        read -p "Enter schemaname: " schm

                                                        if test ${i} -lt  ${n}

                                                        then

                                                                #read -p "Enter schemaname: " schm

                                                                schem+="${schm}, "

                                                        fi

                                                        if test ${i} -eq ${n}

                                                        then

                                                                schem+="${schm}"

                                                        fi

                                                done

                                                command+="schemas=${schem} "

                                                ;;

                                        "tables")

                                                read -p "number of tables: " n

                                                tabl=""

                                                for i in $(seq 1 $n)

                                                do

                                                        read -p "Enter tablename: " tbl

                                                        if test ${i} -lt ${n}

                                                        then

                                                        #       read -p "Enter tablename: " tbl

                                                                tabl+="${tbl},"

                                                        fi

                                                        if test ${i} -eq ${n}

                                                        then

                                                                tabl+="${tbl}"

                                                        fi

                                                done

                                                command+="tables=${tabl} "

                                                ;;

                                        "exclude")

                                                command+="exclude=STATISTICS "

                                                echo "exclude=STATISTICS added !"

                                                ;;

                                        "include")

                                                read -p "Enter number of tables for include: " n

                                                schemtbl=""

                                                for i in $(seq 1 $n)

                                                do

                                                      read -p "Enter schema.table: " schematable

                                                        if test ${i} -lt ${n}

                                                        then

                                                        #read -p "Enter schema.table: " schematable

                                                                schemtbl+="'${schematable}',"

                                                        fi

                                                        if test ${i} -eq ${n}

                                                        then

                                                                schemtbl+="'${schematable}'"

                                                        fi

                                                done

                                                command+='include=table:"in('${schemtbl}')"'

                                                ;;

                                        "logfile")

                                                read -p "Enter logfile: " logfile

                                                command+="logfile=${logfile} "

                                                ;;

                                        "show_command")

                                                echo ${command}

                                                ;;

                                        "Quite")

                                                break

                                                ;;

                                esac

                        done;;

                "impdp")

                        command="impdp "

                        select opt in userpassword directory dumpfile schemas tables logfile exclude include show_command remap_objects Quite

                        do

                                case ${opt} in

                                        "userpassword")

                                                read -p "Enter user/password: " userpassword

                                                command+="${userpassword} "

                                                ;;

                                        "directory")

                                                read -p "Enter directory: " directory

                                                command+="directory=${directory} "

                                                ;;

                                        "dumpfile")

                                                read -p "Enter dumpfile: " dumpfile

                                                command+="dumpfile=${dumpfile} "

                                                ;;

                                        "schemas")

                                                read -p "number of schemas: " n

                                                schem=""

                                                for i in $(seq 1 $n)

                                                do

                                                        read -p "Enter schemaname: " schm

                                                        if test ${i} -lt ${n}

                                                        then

                                                        #       read -p "Enter schemaname: " schm

                                                                schem+="${schm},"

                                                        fi

                                                        if test ${i} -eq ${n}

                                                        then

                                                                schem+="${schm}"

                                                        fi

                                                done

                                                command+="schemas=${schem} "

                                                ;;

                                        "tables")

                                                read -p "number of tables: " n

                                                tabl=""

                                                for i in $(seq 1 $n)

                                                do

                                                        read -p "Enter tablename: " tbl

                                                        if test ${i} -lt ${n}

                                                        then

                                                        #       read -p "Enter tablename: " tbl

                                                                tabl+="${tbl},"

                                                        fi

                                                        if test ${i} -eq ${n}

                                                        then

                                                                tabl+="${tbl}"

                                                        fi

                                                done

                                                command+="tables=${tabl} "

                                                ;;

                                        "logfile")

                                                read -p "Enter logfile: " logfile

                                                command+="logfile=${logfile} "

                                                ;;

                                        "exclude")

                                                command+="exclude=STATISTICS "

                                                echo "exclude=STATISTICS added !"

                                                ;;

                                        "include")

                                                read -p "Enter number of tables for include: " n

                                                schemtbl=""

                                                for i in $(seq 1 $n)

                                                do

                                                        read -p "Enter schema.table: " schematable

                                                        if test ${i} -lt ${n}

                                                        then

                                                        #read -p "Enter schema.table: " schematable

                                                                schemtbl+="'${schematable}',"

                                                        fi

                                                        if test ${i} -eq ${n}

                                                        then

                                                                schemtbl+="'${schematable}'"

                                                        fi

                                                done

                                                command+='include=table:"in('${schemtbl}')"'

                                                ;;

                                         "remap_objects")

                                             select option in remap_schema remap_table remap_tablespace remap_datafile remap_directory remap_partition main_menu

     do 

        case ${option} in

                "remap_schema")

                        content=" remap_schema="

                        read -p "Enter source_schema: " source_schema

                        read -p "Enter target_schema: " target_schema

                        content+="${source_schema}:${target_schema} "

                        echo "${content} "

                        command+="${content} "

                        ;;

                "remap_table")

                        content="remap_table="

                        read -p "Enter source_schema.source_table: " source1

                        read -p "Enter target_schema.target_table: " target

                        content+="${source1}:${target} "

                        echo "${content} "

                        command+="${content} "

                        ;;

                "remap_tablespace")

                        content="remap_tablespace="

                        read -p "Enter source_tablespace: " source_tablespace

                        read -p "Enter target_tablespace: " target_tablespace

                        content+="${source_tablespace}:${target_tablespace} "

                        echo "${content} "

                        command+="${content} "

                        ;;

                "remap_datafile")

                        content="remap_datafile="

                        read -p "Enter source_datafile: " source_datafile

                        read -p "Enter target_datafile: " target_datafile

                        content+="${source_datafile}:${target_datafile} "

                        echo  "${content} "

                        command+="${content} "

                        ;;

                "remap_directory")

                        content="remap_directory="

                        read -p "Enter source_directory: " source_directory

                        read -p "Enter target_directory: " target_directory

                        content+="${source_directory}:${target_directory} "

                        echo "${content} "

                        command+="${content} "

                        ;;

                "remap_partition")

                        content="remap_partition="

                        read -p "Enter source_schema.source_table.source_partition: " source1

                        read -p "Enter target_schema.target_table.target_partition: " target

                        content+="${source1}:${target} "

                        echo "${content} "

                        command+="${content} "

                        ;;

                                                        "main_menu")

break

;;

        esac

done

;;

                                               

                                        "show_command")

                                                echo ${command}

                                                ;;

"Quite")

                                                break

                                                ;;

                                esac

                        done;;

        esac

done

tomcat config script

 #!/bin/bash

# Variables

TOMCAT_VERSION="9.0.59"  # Adjust to the desired Tomcat version

TOMCAT_INSTALL_DIR="/opt/tomcat"

TOMCAT_USER="tomcat"

TOMCAT_PASSWORD="your_password"  # Change this to a secure password

TOMCAT_MANAGER_USER="admin"

TOMCAT_MANAGER_PASSWORD="your_manager_password"  # Change this to a secure password

# Update and install necessary packages

sudo yum update -y

sudo yum install -y java wget

# Download and extract Tomcat

cd /tmp

wget https://downloads.apache.org/tomcat/tomcat-9/v${TOMCAT_VERSION}/bin/apache-tomcat-${TOMCAT_VERSION}.tar.gz

tar -xf apache-tomcat-${TOMCAT_VERSION}.tar.gz

sudo mv apache-tomcat-${TOMCAT_VERSION} ${TOMCAT_INSTALL_DIR}

# Create Tomcat user and group

sudo groupadd ${TOMCAT_USER}

sudo useradd -g ${TOMCAT_USER} -d ${TOMCAT_INSTALL_DIR} -s /bin/nologin ${TOMCAT_USER}

# Set ownership and permissions

sudo chown -R ${TOMCAT_USER}:${TOMCAT_USER} ${TOMCAT_INSTALL_DIR}

sudo chmod +x ${TOMCAT_INSTALL_DIR}/bin/*.sh

# Configure Tomcat users for Manager and Admin roles

echo -e "  <user username=\"${TOMCAT_MANAGER_USER}\" password=\"${TOMCAT_MANAGER_PASSWORD}\" roles=\"manager-gui,admin-gui\"/>\n" | sudo tee -a ${TOMCAT_INSTALL_DIR}/conf/tomcat-users.xml

# Set Tomcat environment variables (optional)

# You may customize this section based on your requirements

# Create systemd service for Tomcat

cat <<EOL | sudo tee /etc/systemd/system/tomcat.service

[Unit]

Description=Apache Tomcat Web Application Container

After=syslog.target network.target

[Service]

Type=forking

Environment=CATALINA_PID=${TOMCAT_INSTALL_DIR}/temp/tomcat.pid

Environment=CATALINA_HOME=${TOMCAT_INSTALL_DIR}

Environment=CATALINA_BASE=${TOMCAT_INSTALL_DIR}

ExecStart=${TOMCAT_INSTALL_DIR}/bin/startup.sh

ExecStop=${TOMCAT_INSTALL_DIR}/bin/shutdown.sh

User=${TOMCAT_USER}

Group=${TOMCAT_USER}

[Install]

WantedBy=multi-user.target

EOL

# Reload systemd and start Tomcat

sudo systemctl daemon-reload

sudo systemctl start tomcat

sudo systemctl enable tomcat

# Open firewall port 8080

sudo firewall-cmd --add-port=8080/tcp --permanent

sudo firewall-cmd --reload

# Cleanup

rm -rf /tmp/apache-tomcat-${TOMCAT_VERSION}.tar.gz

echo "Tomcat installation and configuration completed successfully."

echo "Tomcat Manager URL: http://localhost:8080/manager/html"

echo "Tomcat Admin URL: http://localhost:8080/host-manager/html"

Explain plan concepts cardinality & selectivity

Cardinality is the estimated number of rows the step will return. cost is the estimated amount of work the plan will do. A higher cardinality => you're going to fetch more rows => you're going to do more work => the query will take longer. Thus the cost is (usually) higher. 

Selectivity  = No. of rows returning from the query / total no of rows 

i.e. Selectivity = (select * from table where rownum<40) / select * from table 

Cardinality = total no of rows * Selectivity 

 

Simple SQL tunning statistics

 EXPLAIN PLAN FOR

-- Your SQL query goes here;

SELECT * FROM your_table WHERE your_condition;

-- Display the execution plan

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- Gather statistics for tables involved in the query

EXEC DBMS_STATS.GATHER_TABLE_STATS('your_schema', 'your_table', cascade => TRUE, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);

-- Create a SQL Tuning Task

DECLARE

  task_name VARCHAR2(30);

BEGIN

    task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(

    sql_id => 'your_sql_id_here', 

    scope => DBMS_SQLTUNE.SCOPE_COMPREHENSIVE, 

    time_limit => 60);

    DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name);

END;

/

-- Display recommendations from the SQL Tuning Advisor

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('your_task_name_here') AS recommendations FROM DUAL;

DECLARE 

  l_sql_tune_task_id  VARCHAR2(100); 

BEGIN 

  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task ( 

                          sql_id      => '<sql_id>', 

                          scope       => DBMS_SQLTUNE.scope_comprehensive, 

                          time_limit  => 500, 

                          task_name   => 'tuning_task_name', 

                          description => 'Tuning task1 for tuning_task_name'); 

  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id); 

END; 

/ 

 

 

 

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'tuning_task_name'); 

 

 

set long 65536 

set longchunksize 65536 

set linesize 100 

select dbms_sqltune.report_tuning_task('tuning_task_name') from dual;

In Oracle Database, the "EXPLAIN PLAN" statement is used to display the execution plan that the database optimizer has chosen for a SQL statement. The execution plan outlines the steps and operations that the database will perform to retrieve the requested data. If a query is running slow and its cost is high in the explain plan, it indicates that the optimizer has chosen a suboptimal plan for execution, resulting in poor performance.

Here are some common reasons for a high-cost execution plan:

1. Missing or Outdated Statistics: The optimizer relies on statistics about the tables and indexes involved in the query to make informed decisions. If these statistics are outdated or missing, the optimizer may choose an inefficient plan.

2. Complex Queries: Complex queries with multiple joins, subqueries, or large result sets can result in high costs. Optimizing such queries may involve restructuring them or creating appropriate indexes.

3. Lack of Indexes: If there are no indexes or if existing indexes are not being utilized efficiently, it can lead to full table scans and increased query costs. Adding or modifying indexes may improve performance.

4. Inefficient Joins: The optimizer may choose an inefficient join method, such as nested loops instead of hash joins, leading to increased query cost. Adjusting join orders or using hints may help in optimizing the plan.

5. Insufficient Hardware Resources: If the server doesn't have enough resources, such as CPU or memory, the database may choose plans that are less resource-intensive but slower. Adequate hardware resources should be provisioned.

6. Optimizer Parameters: The database optimizer has various parameters that control its behavior. In some cases, adjusting these parameters or using hints can influence the chosen execution plan.

To address these issues, you can take the following steps:

• Update statistics on tables and indexes.
• Review and optimize the query itself.
• Ensure that appropriate indexes are present and being used.
• Consider using hints to guide the optimizer.
• Monitor system resources and address any deficiencies.