According to Oracle documentation “Oracle Database In-Memory (Database In-Memory) is a suite of features, first introduced in Oracle Database 12c Release 1 (12.1.0.2), that greatly improves performance for real-time analytics and mixed workloads”. Therefore, in this post I will explain how to enable your Oracle Database to use the In-Memory option : 1- Be sure that your database is running on version level 12.1.0 or higher: 2- Set the parameter INMEMORY_SIZE to any value at about 100M (minimum setting): 3- Restart the database: brunorsHi! I am Bruno, a Brazilian born…
Year: 2019
[OCM 12C] Tuning SQL statements
DBMS_STATS.CREATE_EXTENDED_STATSDBMS_STATS.GATHER_TABLE_STATS PARAMETER OPTIMIZER_MODE Database SQL Tuning Guide -> 14 Influencing the Optimizer brunorsHi! I am Bruno, a Brazilian born and bred, and I am also a naturalized Swedish citizen. I am a former Oracle ACE and, to keep up with academic research, I am a Computer Scientist with an MSc in Data Science and another MSc in Software Engineering. I have over ten years of experience working with companies such as IBM, Epico Tech, and Playtech across three different countries (Brazil, Hungary, and Sweden), and I have joined projects remotely…
[OCM 12C] Managing SQL Plan Baselines
“SQL plan management is a preventative mechanism that enables the optimizer to automatically manage execution plans, ensuring that the database uses only known or verified plans.” SPM has three main components: • Plan Capture: OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE (Automatic Initial Plan Capture), OPTIMIZER_USE_SQL_PLAN_BASELINE=TRUE • Plan Selection • Plan Verification/Evolution dba_sql_plan_baselines DBA_SQL_PLAN_BASELINE Database Administration -> Database SQL Tuning Guide -> 23 Managing SQL Plan Baselines brunorsHi! I am Bruno, a Brazilian born and bred, and I am also a naturalized Swedish citizen. I am a former Oracle ACE and, to keep up…
[OCM 12C] Configuring the Resource Manager
Good to user with Enterprise Manager Cloud Control. “Oracle Database Resource Manager (the Resource Manager) enables you to manage multiple workloads within a database that are contending for system and database resources.” “You can examine these statistics using real-time SQL monitoring and the Resource Manager dynamic performance views (the V$RSRC_* views).” “Oracle Database provides a procedure (CREATE_SIMPLE_PLAN) that enables you to quickly create a simple resource plan.” Views: V$SESSION V$RSRC_* views) Steps to create a complex plan: Step 1: Create a pending area. Step 2: Create, modify, or delete consumer…
Retrieving a dropped table on Oracle 12c
Sometimes developers need to recover some tables and we as Oracle DBAs are asked to recover them. So in this post I am going to show you how do to that. I will be creating the table named TECHDATABASKET under the SH user using the dba_tables as example and afterwards I am going to use the syntax “FLASHBACK TABLE SH.TECHDATABASKET TO BEFORE DROP;” in order to recover this table. In the meantime, we need to execute a SELECT key on the table dba_recyclebin or simply the usage of the command…
Optimizing star queries
DBMS_XPLAN.DISPLAY ALTER SESSION SET star_transformation_enabled=TEMP_DISABLE; Database Administration -> Database PL/SQL Packages and Types Reference -> 202 DBMS_XPLAN Database Administration -> Oracle Database Reference -> STAR_TRANSFORMATION_ENABLED brunorsHi! I am Bruno, a Brazilian born and bred, and I am also a naturalized Swedish citizen. I am a former Oracle ACE and, to keep up with academic research, I am a Computer Scientist with an MSc in Data Science and another MSc in Software Engineering. I have over ten years of experience working with companies such as IBM, Epico Tech, and Playtech across…
How was the Workshop: Become a Hero with Oracle Autonomous Database in Stockholm, Sweden ( March 6, 2019) ?
Hello, Hello, As I announced a few weeks ago, I confirmed that I will join the Workshop: Become a Hero with Oracle Autonomous Database in Stockholm, Sweden ( March 6, 2019) . Besides the knowledge I gained attending this event I would really like to highlight Jan Tjernberg for his participation in my Youtube video regarding this event. The language spoken is Portuguese but from the minutes 3:22, it is possible to see my interview in English with him. The video is below: I hope everybody likes it and I…
Oracle Code and Oracle ACE program dinner – Rome, Italy 2019
Hello, I am glad to announce that I am going to attend the Oracle Code Rome 2019 and I will join as a guest in the Oracle ACE dinner. The dinner will be on the 03rd of April and the conference on the 04th of April. This will be my first dinner as a guest at the Oracle ACE Program after my award in Hungary. I am excited for this and I hope to see all of you guys in Rome, Italy on the 04th of April. If you have…
Managing partitioned tables
Partition: Range Partition Interval Partition (extension of Range Partition) List Partition Hash Partition Reference Partition Range-Hash Partition (Subpartition) USER_TAB_PARTITIONS USER_SEGMENTS USER_PART_TABLES DBA_TAB_PARTITIONS USER_LOB_PARTITIONS Database VLDB and Partitioning Guide -> 4 Partition Administration https://www.youtube.com/watch?v=VBkpI4Ki49Q – Range Partition https://www.youtube.com/watch?v=0YYXfFcbC-A – List Partition https://www.youtube.com/watch?v=PrjI-aZrNZE – Hash Partitionhttps://www.youtube.com/watch?v=TT7aDwNFzC4 – Reference Partition https://www.youtube.com/watch?v=BESXA-k5EJU – Virtual column brunorsHi! I am Bruno, a Brazilian born and bred, and I am also a naturalized Swedish citizen. I am a former Oracle ACE and, to keep up with academic research, I am a Computer Scientist with an MSc in…
Workshop: Become a Hero with Oracle Autonomous Database in Stockholm, Sweden ( March 6, 2019)
Hej, I was invited to join in the workshop “Become a Hero with Oracle Autonomous Database” on March 06th at Sheraton Stockholm Hotel (Tegelbacken 6 101 23 Stockholm Sweden ) from March 6, 2019 – 09:00 – 16:30 . Here is the official link to this event: : https://eventreg.oracle.com/profile/web/index.cfm?PKwebID=0x614233abcd Here is the agenda: Besides this, I was selected for an Oracle Cloud promotion in complimentary credits, valid for 30 days where I will be testing the Cloud Platform and Infrastructure services. After this event I’m going to write another post…