Disclaimer All information is offered in good faith and in the hope that it may be of use for educational purpose and for Database community purpose, but is not guaranteed to be correct, up to date or suitable for any particular purpose. db.geeksinsight.com accepts no liability in respect of this information or its use.
This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content and if notify any such I am happy to remove.
Product and company names mentioned in this website may be the trademarks of their respective owners and published here for informational purpose only.
This is my personal blog. The views expressed on these pages are mine and learnt from other blogs and bloggers and to enhance and support the DBA community and this web blog does not represent the thoughts, intentions, plans or strategies of my current employer nor the Oracle and its affiliates or any other companies. And this website does not offer or take profit for providing these content and this is purely non-profit and for educational purpose only.
If you see any issues with Content and copy write issues, I am happy to remove if you notify me.
Contact Geek DBA Team, via geeksinsights@gmail.com
|
Chatbots, an intelligent assistants which/who can interact with opposite parties and address the concerns or respond to simple questions or questions that are generic or non-generic in nature where we can put pre-determined answers and actions, widely popular in lot of industries especially in customer care support to do some mundane stuff like providing information, redirecting customer requests to respective teams and also call out actions to respond to customer query on behalf of support teams.
I will be writing a series of posts how to create your own chatbots (as I practice) and do some operational stuff we do as DBA.
General Architecture of Chatbot
In this post, I will be explaining how to create a simple chatbot with python script using interactive text and speech way.
Requirements
- Install Python 3+ on your machine
- Install pycharm
- Install ChatterBot , PyAudi0, SpeechRecognition, chatterbot-corpus, chatterbot-voice using PIP
Once installed, run a simple snippet below, before that we need to train our bot with set of questions, look at this chats.txt file some random sample questions and answers we have created.
ChatBot : Interactive Text
from chatterbot import ChatBot
from chatterbot.trainers import ListTrainer
bot = ChatBot('MyChatBot')
bot.set_trainer(ListTrainer)
conversation = open('chats.txt', 'r').readlines()
bot.train(conversation)
while True: message = input('You:')
if message.strip() != 'Bye':
reply = bot.get_response(message)
print('ChatBot:', reply)
if message.strip() == 'Bye':
print('ChatBot:Bye')
break
Let me explain,
- Lines in red, to call out chatterbot module given name for my chat as MyChatBot and put that in bot variable.
- Lines in blue, Train our chatbot using the pre determined Questions & Answers and train the conversation bot.train.
- Lines in gree, some logic section
- Lines in purple, message input i.e customer, bot will respond to our message.
- Once received Bye from us the chat will terminate. Simple isnt it.
Chatbot: Speech Recognition
import speech_recognition as sr # import the library r = sr.Recognizer() # initialize recognizer
with sr.Microphone() as source: # mention source it will be either Microphone or audio
files. print("Speak Anything :")
audio = r.listen(source) # listen to the source
try:
text = r.recognize_google(audio) # use recognizer to convert our audio into text
part. print("You said : {}".format(text))
except: print("Sorry could not recognize your voice") # In case of voice not recognized clearly
Let me explain,
- Lines in red, to call out speech recognition module and initialize it.
- Lines in green, some logic to loop into the interaction.
- Lines in Purple, listen your voice and respond/print the same in the chat window.
Watch out the chatbot actions in this videos.
Simple isn't it, but not that simple there's too much to learn and do, I will write more posts as series as I progress.
Hope you enjoyed it.
-Suresh
Cloud Feature - Not working in On Prem installation.
Oracle Database 12c introduced online statistics gathering for CREATE TABLE AS SELECT statements and direct-path inserts.
Oracle Database 19c introduces real-time statistics, which extend online support to conventional DML statements. Because statistics can go stale between DBMS_STATS jobs, real-time statistics helps the optimizer generate more optimal plans.Whereas bulk load operations gather all necessary statistics, real-time statistics augment rather than replace traditional statistics.
Oracle introduced new parameters
- "_optimizer_gather_stats_on_conventional_dml" and "_optimizer_use_stats_on_conventional_dml" which are true by default
- "_optimizer_stats_on_conventional_dml_sample_rate" at 100%
How does real time statistics works?
- By default the "_optimizer_gather_stats_on_conventional_dml" is true so its automatically kicks off
- When a DML operation is currently modifying a table (conventional), Oracle Database dynamically computes values for the most essential statistics if the above parameter is on.
- Consider a example of table that is having lot of inserts and rows are increasing. Real-time statistics keep track of the increasing row count as rows are being inserted. If the optimizer performs a hard parse of a new query, then the optimizer can use the real-time statistics to obtain a more accurate cost estimate.
- DBA_TAB_COL_STATISTICS and DBA_TAB_STATISITICS has columns NOTES tell real time statistics have been used. STATS_ON_CONVENTIONAL_DML
SELECT NVL(PARTITION_NAME, 'GLOBAL') PARTITION_NAME, NUM_ROWS, BLOCKS, NOTES
FROM USER_TAB_STATISTICS
WHERE TABLE_NAME = 'SALES'
ORDER BY 1, 4;
PARTITION_NAM NUM_ROWS BLOCKS NOTES
------------- ---------- ---------- -------------------------
GLOBAL 1837686 3315 STATS_ON_CONVENTIONAL_DML
---------------------------------------------------------------------------------------
|Id| Operation | Name|Rows|Bytes|Cost (%CPU)|Time| Pstart|Pstop|
---------------------------------------------------------------------------------------
| 0| INSERT STATEMENT | | | |910 (100)| | | |
| 1| LOAD TABLE CONVENTIONAL |SALES| | | | | | |
| 2| OPTIMIZER STATISTICS GATHERING | |918K| 25M|910 (2)|00:00:01| | |
| 3| PARTITION RANGE ALL | |918K| 25M|910 (2)|00:00:01| 1 | 28 |
| 4| TABLE ACCESS FULL |SALES|918K| 25M|910 (2)|00:00:01| 1 | 28 |
---------------------------------------------------------------------------------------
- Also the explain plan in the query used will tell in note section
Note
-----
- dynamic statistics used: stats for conventional DML
Thanks
Suresh
Statistics gathering until 18c was on daily/weekly basis based on the maintainance tasks defined. Say 10PM every day with 8 hour window on weekdays and 24 hrs window on week ends. And how many times we have seen statistics have become stale between two collections and DBA's has to manually intervene and collect stats.
From 19c onwards, High-frequency automatic optimizer statistics collection complements the standard statistics collection job. By default, when set to on, the collection occurs every 15 minutes, meaning that statistics have less time in which to be stale.
To Enable high frequency stats collection
- EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_STATUS','ON’);
For, Maximum Run duration of each run,
- EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_MAX_RUN_TIME','600’);
To Specify the frequency of auto stats collection in seconds
- EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_INTERVAL','240')
Happy Learning
Suresh
Oracle’s Simple Oracle Document Store is an interface to manage Document Store type work loads in Oracle Database. Leveraging JSON Object Support with in Oracle Database. SchemaLess & NOSQL access but fully acid complaint. Collection Management through various implementations like Java, Rest, Python, Native SQL etc.
Store and manage JSON and XML documents in Oracle Database
- Accessible via REST and all major programming languages
- Full query capabilities using JSON Path, XQuery and SQL
- Comprehensive, path-aware indexing
- No need to learn SQL or require DBA when developing applications
- Fits into the DevOPS paradigm
Built on Foundation on Oracle Database
- Transactions and consistency
- Advanced SQL engine
- Enterprise-Grade High Availability
- Enterprise-Grade Security
- Scalability and Performance: Exadata and Real Application Clusters
- Supports Oracle Public Cloud Infrastructure
Oracle SODA Implementations Patterns
SODA over REST
SODA for REST URI Patterns
SODA for NodeJs Implementation
SODA With SQLCL
All Practice Videos on Oracle SODA
[embedyt] https://www.youtube.com/embed?listType=playlist&list=PLR6rN4cTV4BTm9lWJUm0VbZO9rvuzxUBz&v=s3CfV90J9VM&layout=gallery[/embedyt]
Some New wait events that I learnt today,
acknowledge over PGA limit (12.1)
The "Acknowledge Over PGA limit" is a new wait event that was introduced with PGA_AGGREGATE_LIMIT in 12.1, and it will force a process that wants more PGA to wait a bit if the instance is getting close to hitting the limit. Set or increase PGA_AGGREGATE_TARGET to non zero value and also try to increase "_pga_limit_target_perc" or set PGA_AGGREGATE_LIMIT to zero to disable automatic pga memory management.
enq: IV - contention (No idea)
2028503.1 12c RAC DDL Performance Issue: High “enq: IV – contention” etc if CPU Count is Different “
Not more info found but apparently when impdp run's there's too much of DDL happening and we saw this issue.
Failed Logon Delay (11g)
In Oracle Database 11G there is a new 'feature' related to hacking prevention:
Logon Delays:
A hacker may attempt a brute force hack to break into your Oracle Database. In this case they try constant logons to the database using some form of a word list. Oracle 11g includes a logon delay that takes effect after the third failed password entry attempt. This makes the brute force hacking more difficult. After the third failed logon attempt, Oracle will incrementally delay subsequent logon or password prompts up to a maximum of 10 seconds. No delay will occur if the logon is successful. This is in conjunction with parameters sec_protocol_error_further_action and sec_protocol_error_trace_action
BUT there is a problem/bug , until OracleDatabase version 11.2.0.2 , You can encounter this bug and experience high library cache contention , when you have both users connecting with good passwords and users connecting with bad passwords
Thanks
Suresh
Edited this post on 30-Apr, to correct my command in the test.
Update the post on 22-May, not working in on-prem as expected.
Whilst the documentation say's the auto indexing feature is not available on-prem ones, I tried to test it out.
Command to enable auto index mode is below, we have options "Implement", "Report-Only", "OFF".
exec dbms_auto_index.configure('auto_index_mode','implement'); --> does not work use internal feature. is this something for cloud only or have to verify.
exec dbms_auto_index_internal.configure('auto_index_mode','implement');
We can verify then
select * from dba_auto_index_config order by 1;
And see advisor tasks for auto index is enabled,
select * from dba_advisor_tasks where owner='SYS' order by task_id;
To enable just for a schema,
dbms_auto_index_internal.configure(parameter_name => 'AUTO_INDEX_SCHEMA', parameter_value => '&SCHEMANAME', allow => FALSE);
Inside in a pdb, just in case.
All it works in this fashion (from juliandontcheff blog)
Capture --> Identify --> Verify --> Decide --> Monitor --> (Cycle repeats)
– The auto index candidates are created as invisible auto indexes
– If the performance of SQL statements is not improved from the auto indexes, then the indexes are marked as unusable and the corresponding SQL statements are blacklisted
– Auto indexes cannot be used for any first time SQL run against the database
– Auto indexes are created as either single, concatenated indexes or function-based indexes and they all use advanced low compression
– The unused auto indexes are deleted after 373 days (can be changed)
– The unused non-auto indexes (manual indexes) are never deleted by the automatic indexing process but can be deleted automatically if needed
Some important dynamic views and queries regarding auto index features. (From Frank Pachot blog)
select * from sys.smb$config where parameter_name like '%AUTO_INDEX%' order by 1;
select * from dba_advisor_tasks where owner='SYS' order by task_id;
select * from dba_advisor_executions where task_name='SYS_AUTO_INDEX_TASK' order by execution_id;
select * from dba_auto_index_executions order by execution_start;
select * from dba_advisor_objects where task_name='SYS_AUTO_INDEX_TASK' and type='AUTO INDEX INFORMATION'order by object_id;
select attr7,sys.dbms_auto_index_internal.finding_name(attr7) from (select rownum attr7 from xmltable('1 to 51')) order by 1;
select * from sys."_auto_index_log" order by log_id;
select * from dba_auto_index_statistics where value>0 order by 1;
select * from sys."_auto_index_ind_objects" order by object_id;
select auto,count(*) from dba_indexes group by auto;
I will post more details once the task running and show recommendations.
Thanks
Suresh
Starting Oracle 19c, SQL Quarantine features helps to prevent reuse of same execution plan which was terminated by resource managers due to resource limits. This will help not to run the rouge queries again on the database if the resource manager is active.
SQL Quarantine will need to setup configuration and define thresholds for specific execution plan for given sql_id or for all plans for sql_id. The thresholds are similar like resource manager threshold ex: cpu limit, elapsed_time limit etc. To define the thresholds you can use DBMS_SQLQ package.quarantine configuration for an execution plan for a SQL statement.
Please note, the quarantine feature does not kill the session itself, it's just flush out the plan and quarantine that SQL and its plan for ever until it configuration is in enabled state.
This feature is only available in the Cloud and EE-ES database only, not on on-prem standard/enterprise editions as per documentation, however I could create , enable and create some plan on it.
In order to create a SQL Quarantine let's first configure it.
# For SQL_ID and one of its execution plan
DECLARE
quarantine_config VARCHAR2(30);
BEGIN
quarantine_config := DBMS_SQLQ.CREATE_QUARANTINE_BY_SQL_ID(SQL_ID => '&sql_id',
PLAN_HASH_VALUE => '&PLAN_HASH_VALUE');
END;
/
# For SQL_ID and all of its executions plans
DECLARE
quarantine_config VARCHAR2(30);
BEGIN
quarantine_config := DBMS_SQLQ.CREATE_QUARANTINE_BY_SQL_ID(SQL_ID => '&sql_id')
END;
/
# For SQL_TEXT Only
DECLARE
quarantine_config VARCHAR2(30);
BEGIN
quarantine_config := DBMS_SQLQ.CREATE_QUARANTINE_BY_SQL_TEXt(SQL_TEXT => to_clob('select count(*) from emp'));
END;
/
Secondly, add threshold to it. Note the values are in seconds, even for cpu its cpu_time not percentage
BEGIN
DBMS_SQLQ.ALTER_QUARANTINE(
QUARANTINE_NAME => '&SQL_QUARANTINE_ID,
PARAMETER_NAME => 'CPU_TIME',
PARAMETER_VALUE => '20');
DBMS_SQLQ.ALTER_QUARANTINE(
QUARANTINE_NAME => '&SQL_QUARANTINE_ID,
PARAMETER_NAME => 'ELAPSED_TIME',
PARAMETER_VALUE => '10');
END;
/
And finally, enable it
BEGIN
DBMS_SQLQ.ALTER_QUARANTINE(
QUARANTINE_NAME => '&SQL_QUARANTINE_ID,
PARAMETER_NAME => 'ENABLED',
PARAMETER_VALUE => 'YES');
END;
/
To test it, enable the Resource manager plan and
conn hr/hrpw@ORCLPDB
@create_resourcemanagerplan.sql (check oracle document for example)
Execute the statement.
select count(*) from emp
To view which plans got quarantined etc, v$SQL view has new columns as below
select sql_text, plan_hash_value, avoided_executions, sql_quarantine
from v$sql
where sql_quarantine is not null;
select sql_text, name, plan_hash_value, last_executed, enabled
from dba_sql_quarantine;
Thanks
Suresh
Hi
In this post we will see how to install Oracle 19c Database using RPM method with Vagrant Virtual box and via ansible playbook.
Pre-reqs
You need to have vagrant installed in your machine.
Steps:
- Download the repository for Vagrant and Ansible playbook here
- Download 19c database rpm from Oracle downloads and copy into your working folder
- simply run "vagrant up", this will create a virtual box and install oracle, create/configure a database
Here is the vagrant log file that build virtualbox for you.
C:\Users\gandhi\Documents\Ansible-Workshop\vagrantansibleoracle19c>vagrant up
Bringing machine 'oracledb' up with 'virtualbox' provider...
==> oracledb: Checking if box 'elastic/oraclelinux-7-x86_64' is up to date...
==> oracledb: Clearing any previously set forwarded ports...
==> oracledb: Clearing any previously set network interfaces...
==> oracledb: Preparing network interfaces based on configuration...
oracledb: Adapter 1: nat
==> oracledb: Forwarding ports...
oracledb: 22 (guest) => 2222 (host) (adapter 1)
==> oracledb: Running 'pre-boot' VM customizations...
==> oracledb: Booting VM...
==> oracledb: Waiting for machine to boot. This may take a few minutes...
oracledb: SSH address: 127.0.0.1:2222
oracledb: SSH username: vagrant
oracledb: SSH auth method: private key
==> oracledb: Machine booted and ready!
[oracledb] GuestAdditions 5.2.16 running --- OK.
==> oracledb: Checking for guest additions in VM...
==> oracledb: Setting hostname...
==> oracledb: Mounting shared folders...
oracledb: /vagrant => C:/Users/gandhi/Documents/Ansible-Workshop/vagrantansibleoracle19c
==> oracledb: Machine already provisioned. Run `vagrant provision` or use the `--provision`
==> oracledb: flag to force provisioning. Provisioners marked to run always will still run.
Playbook Log, run "vagrant provision" if you want to provision only.
C:\Users\gandhi\Documents\Ansible-Workshop\vagrantansibleoracle19c>vagrant provision
==> oracledb: Running provisioner: ansible_local...
Vagrant has automatically selected the compatibility mode '2.0'
according to the Ansible version installed (2.3.2.0).
Alternatively, the compatibility mode can be specified in your Vagrantfile:
https://www.vagrantup.com/docs/provisioning/ansible_common.html#compatibility_mode
oracledb: Running ansible-playbook...
PLAY [oracledb] ****************************************************************
TASK [Gathering Facts] *********************************************************
ok: [oracledb]
TASK [Update /etc/hosts from inventory] ****************************************
ok: [oracledb] => (item=oracledb)
TASK [Create Disk Partion] *****************************************************
ok: [oracledb]
TASK [Format Partition] ********************************************************
ok: [oracledb]
TASK [Mount the Partition] *****************************************************
ok: [oracledb]
TASK [copy rpm file to server] *************************************************
changed: [oracledb]
TASK [download pre-reqs] *******************************************************
changed: [oracledb]
TASK [install pre-reqs rpm] ****************************************************
changed: [oracledb]
TASK [provide permission to create folder /opt/oracle] *************************
changed: [oracledb]
TASK [install oracle database software rpm] ************************************
[WARNING]: Consider using file module with owner rather than running chown
changed: [oracledb]
TASK [create database using configure script] **********************************
changed: [oracledb]
PLAY RECAP *********************************************************************
oracledb : ok=10 changed=5 unreachable=0 failed=0
Configure Oracle Database using pre-defined script that comes with RPM
[root@oracledb19c init.d]# /etc/init.d/oracledb_ORCLCDB-19c configure
Configuring Oracle Database ORCLCDB.
Prepare for db operation
8% complete
Copying database files
31% complete
Creating and starting Oracle instance
32% complete
36% complete
40% complete
43% complete
46% complete
Completing Database Creation51% complete
54% complete
Creating Pluggable Databases
58% complete
77% complete
Executing Post Configuration Actions
100% complete
Database creation complete. For details check the logfiles at:
/opt/oracle/cfgtoollogs/dbca/ORCLCDB.
Database Information:
Global Database Name:ORCLCDB
System Identifier(SID):ORCLCDB
Look at the log file "/opt/oracle/cfgtoollogs/dbca/ORCLCDB/ORCLCDB1.log" for further details.
Oracle 19c database is ready and log on to it and practice .
Thanks
Suresh
Hi
While I am exploring the Oracle 19c Database, I found that docker images are already available in https://github.com/oracle/docker-images/tree/master/OracleDatabase/SingleInstance/dockerfiles/19.3.0
Along side, here is the excerpt from documentation , automatic indexing , quarantine, real time statistics are only available on Cloud versions not on normal EE on prem databases. So , Hail cloud 🙂
https://docs.oracle.com/en/database/oracle/oracle-database/19/dblic/Licensing-Information.html#GUID-AB354617-6614-487E-A022-7FC9A5A08472
Nice feature comparison app https://apex.oracle.com/database-features/
Thanks
Suresh
Hello All,
Oracle 19c is available officially for download , cant wait to test automatic indexing feature. Keep you posted.
https://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html
Thanks
Suresh
|
Oracle 23c New Features for DBA's
Follow Me!!!