How to change SQL prompt to show connected user and database name in 12c +

In order to get the Database schema & Database name with include Time on the prompt need to change on the below Original file.

 

pwd

/u01/app/oracle/TEST/12.1.0/sqlplus/admin

[oracle@server1 admin]$ ls -lrt glogin.sql
-rw-r--r--. 1 oracle dba 342 Jan 12  2006 glogin.sql

[oracle@us04dbqa01 admin]$ cat glogin.sql
--
-- Copyright (c) 1988, 2005, Oracle.  All Rights Reserved.
--
-- NAME
--   glogin.sql
--
-- DESCRIPTION
--   SQL*Plus global login "site profile" file
--
--   Add any SQL*Plus commands here that are to be executed when a
--   user starts SQL*Plus, or uses the SQL*Plus CONNECT command.
--
-- USAGE
--   This script is automatically run
--
[oracle@server1 admin]$

 

 

 

Now Connect as oracle Database OS user and add the following line at the end of the  $ORACLE_HOME/sqlplus/admin/glogin.sql

 

 

[oracle@server1 admin]$ cat glogin.sql
--
-- Copyright (c) 1988, 2005, Oracle.  All Rights Reserved.
--
-- NAME
--   glogin.sql
--
-- DESCRIPTION
--   SQL*Plus global login "site profile" file
--
--   Add any SQL*Plus commands here that are to be executed when a
--   user starts SQL*Plus, or uses the SQL*Plus CONNECT command.
--
-- USAGE
--   This script is automatically run
--
set editfile "/tmp/a447553_1.buf"
SET FEEDBACK OFF
set head off
set linesize 600
SET TERMOUT OFF
define _editor = vi

VARIABLE v_database  VARCHAR2(10)

BEGIN
  SELECT upper(INSTANCE_NAME)
  INTO   :v_database
  FROM   v$instance;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    :v_database := 'SQL';
END;
/

SPOOL uid_temp.sql

SELECT 'SET SQLPROMPT "' || '['||upper(User) || ']' ||'['|| :v_database || ']>> "' FROM   v$database;
SELECT 'SET numw 15' from DUAL;
SELECT 'SET time on ' from DUAL;
SELECT 'SET timing on trimspool on ' from DUAL;
SELECT 'SET pages 1000' from DUAL;
SELECT 'col OWNER format a16
SELECT 'col program format a30' from DUAL;
SELECT 'col machine format a10' from DUAL;
SELECT 'col partition_name format a20' from DUAL;
SELECT 'col table_name format a33' from DUAL;
SELECT 'col tablespace_name format a20' from DUAL;
SELECT 'col TBSP_NAME format a35' from DUAL;
SPOOL OFF

@uid_temp.sql
SET TERMOUT ON
--set sqlprompt " [ _DATE _USER _CONNECT_IDENTIFIER ] >> "
SET FEEDBACK ON
set head on

 

 

 

[oracle@server1 admin]$ sqlplus "/as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jun 9 09:22:24 2020

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

 

09:22:24 [SYS][TEST1]>> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
TEST   READ WRITE

1 row selected.

Elapsed: 00:00:00.03

09:22:55 [SYS][TEST1]>> show user;
USER is "SYS"
09:22:56 [SYS][TEST1]>>

 

 

 

How To Stabilize the Execution plan using SQL PLAN Baseline

How To Stabilize the Execution plan using SQL PLAN Baseline

 

Each SQL associated with SQL_ID and based on the DBMS_STATS, the Optimizer chooses a execution plan identified with PLAN_HASH_VALUE.   Hence, SQL_ID with multiple PLAN_HASH_VALUE often causes inconsistent performance.

How to fix this?

 

To start with, need to identify the optimal PLAN_HASH_VALUE for the SQL_ID.

 

Below Query give the history of the SQL_ID with PLAN_HASH_VALUE and execution time lines to choose the optimal PLAN_HASH_VALUE.  Make a note of the instance number in case of RAC for the optimal PLAN_HASH_VALUE.

 

set lines 155

col execs for 999,999,999

col avg_etime for 999,999.999

col avg_lio for 999,999,999.9

col begin_interval_time for a30

col node for 99999

break on plan_hash_value on startup_time skip 1

select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,

nvl(executions_delta,0) execs,

(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,

(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio

from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS

where sql_id = nvl('&sql_id','XXXXXXXXX')

and ss.snap_id = S.snap_id

and ss.instance_number = S.instance_number

and executions_delta > 0

order by 1, 2, 3

/

Before creating PLAN_BASELINE, check if the SQL_ID with optimal PLAN_HASH_VALUE is in cursor cache.

 

Need to query v$SQL :  select inst_id ,SQL_ID,PLAN_HASH_VALUE from gv$SQL;

 

If the same optimal PLAN_HASH_VALUE identified from the above query, you can create Plan Baseline from the Cursor Cache using below Steps:-

 

Loading SQL Plan Baseline from CURSOR Cache:

DECLARE

my_plans pls_integer;

BEGIN

my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => ‘&SQL_ID’,PLAN_HASH_VALUE=>’&PLAN_HASH_VALUE’,fixed=>’YES’);

END;

/

commit;

 

Need to query dba_sql_plan_baselines, to check if the plan baseline created.

 

If the Optimal PLAN_HASH_VALUE does not exist in cursor cache, we need load it from AWR.

 

Loading SQL Plan Baseline from AWR

 

Step1:- Need to create SQL tuning set name, I am choosing the name as TUNING_SET1 in this document.

 

exec  dbms_sqltune.create_sqlset(sqlset_name => 'TUNING_SET1',description => 'sqlset descriptions');

 

Step2:- Load the SQL from AWR to above Tuning SET name TUNING_SET1

 

declare

baseline_ref_cur DBMS_SQLTUNE.SQLSET_CURSOR;

begin

open baseline_ref_cur for

select VALUE(p) from table(

DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(&begin_snap_id, &end_snap_id,'sql_id='||CHR(39)||'&sql_id'||CHR(39)||'',NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p;

DBMS_SQLTUNE.LOAD_SQLSET(' TUNING_SET1', baseline_ref_cur);

end;

/

COMMIT;

 

Note:- Input the SQL_ID and snapshotIDs identified from the first step .Run from the appropriate instance , where optimal PLAN_HASH_VALUE identified.

 

Ensure the SQL_SET:-

 

SELECT NAME,OWNER,CREATED,STATEMENT_COUNT FROM DBA_SQLSET where name='TUNING_SET1';

 

Note:- statement_count value in the above query should be 1

 

To ensure right PLAN_HASH_VALUE is there in Tuning set:-

 

set long 2000000

select * from table(dbms_xplan.display_sqlset('TUNING_SET1','&sql_id'));

 

Step3 :- Load SQL Plan baseline from above tuning set.

declare

my_int pls_integer;

begin

my_int := dbms_spm.load_plans_from_sqlset (

sqlset_name => 'TUNING_SET1',

sqlset_owner => 'SYS',

fixed => 'YES',

enabled => 'YES');

DBMS_OUTPUT.PUT_line(my_int);

end;

 /

commit;

 

Query dba_sql_plan_baselines  , to check if the plan baseline created.