How to check previous hash value based on old_hash_value from statspack report.

You can check the SQL's execution plan for a given old hash value  by

@?/rdbms/admin/sprepsql

You may want to compare SQL Statistics section such as buffer gets, disk reads and rows processed as well.

select * from stats$sql_summary where old_hash_value='4249085830';

You would need to have gathered snapshots at level 6 or 7, then you could see all the plans for a statement. Did you do that? Level 5 isn't good enough.

http://www.dba-oracle.com/t_sprepsql_sql.htm

To fix the good PHV or plan, you can use : 


1) COE CUSTOM PROFILE 

Refer Correcting Optimizer Cost Estimates to Encourage Good Execution Plans Using the COE XFR SQL Profile Script ( Doc ID 1955195.1 ) 

/*NO NEED OF ANY SQLT INSTALLATION. WE JUST NEED THAT ZIP FILE*/ 


OR 


2) How to Load SQL Plans into SQL Plan Management (SPM) from the Automatic Workload Repository (AWR) ( Doc ID 789888.1 ) 


Adding hints to a SQL statement require more than just a copy of the statement. 

Please upload SQLTXPLAIN report for the SQL using Method 2 (XTRACT). 
SQLTXPLAIN reports provide a great deal of supporting information including schema definitions, statistics, parameters, an optimizer 10053 trace, and much more that are essential to the diagnostic process. 

Please do the following: 
(i) Please read Note SQLT (SQLTXPLAIN) - Tool that helps to diagnose a SQL statement performing poorly ( Doc ID 1614107.1 ) 
and Note FAQ: SQLT (SQLTXPLAIN) Frequently Asked Questions ( Doc ID 1454160.1 ) carefully. 
(ii) Download and unzip file sqlt.zip provided on Note 
Please download the latest version. 
(iii) Install SQLTXPLAIN under direction of INSTRUCTIONS.TXT file 
(iv) Run SQLTXPLAIN (use XTRACT method) for the SQL statements that we are focus under direction of INSTRUCTIONS.TXT 
(v) SQLTXPLAIN normally generates a *.zip file for upload. If it does not, please upload all files generated. 

For SQL Hints, https://docs.oracle.com/database/121/SQLRF/sql_elements006.htm#SQLRF51101 

For Statistics, Support Note: How to Restore Oracle Optimizer Statistics ( Doc ID 452011.1 ) 
I could not find if this feature is available in SR, but you can try. 

Unfortunately SQL Plan Management (SPM) is not available in Standard Edition (SE) Oracle. Additionally, there is no history of old execution plans available because they are stored in the Oracle Diagnostic Pack (AWR) which is also not available on SE Oracle. Your options are: 1. Manual tuning of the SQL statement. 2. Using hints to force a specific execution plan. 3. Possible restore old statistics from before the plan changed (May not be available on SE Oracle) 4. Change the way statistics are gathered.

Notes from Heroes of autonomous Oracle

Sandesh Rao
sql developer 18.3
Download anaconda and check zupitor notebook

Find next best window when maintenance can be performed with minimal service impact
from average active sessions: AI application
Re-start nodes one by one---May be killing and re-starting nodes


Can we create a data lake with all customers data and generate usefull reports from them that can benifits the customers?

Why we need divers to search sea level but not Drones?

Write a algoritm when a query changes explain plan and impact of the same?

Can we utilize oracle 12.2 algoritms by populating 12.1 and previous databases metadata to them like AWR data etc?

Remove clutter from database logs?


Can we enable all enterprize features on at least test machines and generate best data out of it and use for prod? For example, tunning advisor, segment advisor , index monitoring etc.?

Write sql against dbms_datamining packages to get ML data

Malay Kumar Khawas, Sr ETA
Autonomous transaction processing

https://myservices.us.oraclecloud.com/mycloud/signup?sourceType&sourceType=:eng:ip:ie::RC_PDMK190401P00011:IndiaRSBlloreATPHOLMeetupMay_04_19

Name:anandcloud1

LAB doc:
https://oracle.github.io/learning-library/workshops/autonomous-transaction-processing/?page=LabGuide200SecureConnectivityAndDataAccess.mds

https://oracle.github.io/learning-library/workshops/autonomous-transaction-processing/?page=README.md


https://console.eu-frankfurt-1.oraclecloud.com/a/db/adb/ocid1.autonomousdatabase.oc1.eu-frankfurt-1.abtheljtqhfph2igflv4paewxx6wsnwsrx5oy7ztmw7terl7mm66732ufb3a

anand.yadav@jda.com
password:Kuchbhi@12345

DBName:
DB201905041220
Kuchbhi@12345



https://console.eu-frankfurt-1.oraclecloud.com/object-storage/buckets/frcxommztxdd/ATPLab/customers.csv

https://swiftobjectstorage.eu-frankfurt-1.oraclecloud.com/v1/frcxommztxdd/ATPLab/customers.csv


#S0[hRpve6;QXRiBhQDc


set define off
begin
  DBMS_CLOUD.create_credential(
    credential_name => 'OBJ_STORE_CRED1',
    username => 'anand.yadav@jda.com',
    password => '#S0[hRpve6;QXRiBhQDc'
  );
end;
/
set define on


/* Specify the real base_URL for your OCI Object Storage in the define base_URL line below*/
/* change gse00014623 to your real tenancy name. The name is case-sensitive. */
/* change ATPDB to your real bucket name. The name is case-sensitive. */
/* change us-phoenix-1 to your real region name. The name is case-sensitive. */
/* you can find these values on the OCI Console .. Storage .. Object Storage screen */

define base_URL='https://swiftobjectstorage.us-phoenix-1.oraclecloud.com/v1/gse00014623/ATPDB'

define customer_dat_URL = '&base_URL/customers.csv';





begin
 dbms_cloud.copy_data(
    table_name =>'CUSTOMERS',
    credential_name =>'OBJ_STORE_CRED1',
    file_uri_list => 'https://swiftobjectstorage.eu-frankfurt-1.oraclecloud.com/v1/frcxommztxdd/ATPLab/customers.csv',
    format => json_object('ignoremissingcolumns' value 'true', 'removequotes' value 'true', 'dateformat' value 'YYYY-MM-DD HH24:MI:SS', 'blankasnull' value 'true')
 );
end;
/


DemoVCN
CIDR Block:

10.0.0.0/16
Compartment:

PD


ssh-rsa AAAAB3NzaC1yc2EAAAABJQAAAQB7tMDR0CDLUdoe3xYgGNKMDHxmQJNYd/jeyPJTwcaQmEjQFULoSmgkVqlIbUwgz1DmDl1y1HslJ1sFxzXJRMdwsTBhhAriIHKjDB8vVF5QMXiR4qw14zHFwmRMp5WFK53xULjFGmZ2N41CMMh0GN27PMYdZsmH1jL2Eeq4Vl4IC4E1meOGOdxtEVtJJzcZdAsz1yuAyMnA54nC6Ygt2LtMvADId+hXWyqAv9iTCqgGC1bzZ75FbXIysAebdwfgjAl2wqZWmqlBqvCyUyYn4FDuHlTh5AsGMUjJXC7nUVjQ2jOYQ4WN8/FgQGu/yrM2ufGx9qN0D8EGFJNab43RpT43 rsa-key-20190504

130.61.72.31
user:opc

sudo curl --silent --location https://rpm.nodesource.com/setup_10.x | sudo bash -

sudo yum install nodejs
python --version
sudo yum install libaio
node --version
npm --version
sudo yum install git

scp -i C:\Tools\ATP/perkey.pbk C:\Tools\ATP\instantclient-basic-linux.x64-12.2.0.1.0 opc@130.61.72.31:/opt/oracle/instantclient_12_2


 git clone https://github.com/ravi041282/ATPnodeapp
 git clone https://github.com/ravi041282/ATPnodeapp


 [opc@atpmv wallet_RESTONHUBDB]$ export TNS_ADMIN=/home/opc/ATPnodeapp/wallet_RESTONHUBDB/


 Lab6:

 sudo yum install -y java-1.8.0-openjdk-devel
 cd ~

mkdir ATPJava

cd ATPJava

 sudo yum install git

 git clone https://github.com/ravi041282/ATPJava.git


 cd /home/opc/ATPJava/

mkdir lib

cd lib

wget https://github.com/sblack4/ojdbc8-full/raw/master/ojdbc8-full.tar.gz

tar xzfv ojdbc8-full.tar.gz


cd /home/opc/ATPJava/

mkdir wallet_DB

unzip /home/opc/ATPJava/Wallet_DB201905041220.zip -d /home/opc/ATPJava/wallet_DB/

cd /home/opc/ATPJava/ATPJava/src

Notes from Sangam18

What is new with Oracle 18C:

ATP:

Oracle Autonomous Transaction Processing database<read more>

Single database instance on server  is most efficient and multi-instance on single server is in-efficient model of database build.

Row format storage is ideal for OLTP and column format storage is for analytics.

18c is essentially 12.2.0.2.

Polymorphic Table Functions in Oracle Database 18c:

The return type of a Polymorphic Table Function (PTF) can be determined by input parameters. This differs from conventional table functions, where the output table type is fixed at compile time.

Qualified Expressions in PL/SQL in Oracle Database 18c

Qualified expressions provide and alternative way to define the value of complex objects, which in some cases can make the code look neater.

Scalable Sequences in Oracle Database 18c

Scalable sequences are designed to reduce problems with hot index blocks during large scale data loads into tables with sequence generated primary keys on single instance and RAC databases.

Scalable sequences have been available since the first release of Oracle 12c, but they were not documented and therefore not supported. They are included in the documentation for the first time in Oracle 18c, so they are now a supported feature.

Introduction to In-Memory External Tables in 18c:

approximate Top-N Query Processing (APPROX_RANK, APPROX_SUM, APPROX_COUNT) In Oracle Database 18c

The APPROX_RANK, APPROX_SUM and APPROX_COUNT functions were introduced in Oracle 18c to allow approximate top-n query processing. This extends the approximate query processing that was introduced in the previous two releases (12cR1 and 12cR2)

ALTER SYSTEM CANCEL SQL : Cancel a SQL Statement in a Session in Oracle Database 18c

The basic syntax of the ALTER SYSTEM CANCEL SQL statement is show below.

ALTER SYSTEM CANCEL SQL 'SID, SERIAL[, @INST_ID][, SQL_ID]';

If the INST_ID is omitted, it is assumed you mean the current instance. If the SQL_ID is omitted, it is assumed you mean the SQL that is currently running in the specified session.

Multithreaded Model using THREADED_EXECUTION in Oracle Database 12c Release 1 (12.1)

Python

https://micropyramid.com/blog/category/python/
https://onlinetrainingitguru.blogspot.com/

Statspack and dynamic views

http://satya-dba.blogspot.com/2009/08/statspack-in-oracle.html
https://docs.oracle.com/cd/B10500_01/server.920/a96533/sqlviews.htm
https://www.akadia.com/services/ora_statspack_survival_guide.html