dba   2967

« earlier    

All about headroom and mandatory patching before June 2019 | Frits Hoogland Weblog
However, the most important statistic is to see how close the current SCN is to the scheme 1 soft limit. This can be done using the following SQL (this SQL does not need a newer version of the database, and is tested with version and higher):

col "RSL scheme 1" format 9,999,999,999,999,999
col "current value" format 9,999,999,999,999,999
select dbms_flashback.get_system_change_number "current value",
((((to_number(to_char(sysdate,'YYYY'))-1988)*12*31*24*60*60) +
((to_number(to_char(sysdate,'MM'))-1)*31*24*60*60) +
(((to_number(to_char(sysdate,'DD'))-1))*24*60*60) +
(to_number(to_char(sysdate,'HH24'))*60*60) +
(to_number(to_char(sysdate,'MI'))*60) +
(to_number(to_char(sysdate,'SS')))) * (16*1024)) "RSL scheme 1",
round(dbms_flashback.get_system_change_number/((((to_number(to_char(sysdate,'YYYY'))-1988)*12*31*24*60*60) +
((to_number(to_char(sysdate,'MM'))-1)*31*24*60*60) +
(((to_number(to_char(sysdate,'DD'))-1))*24*60*60) +
(to_number(to_char(sysdate,'HH24'))*60*60) +
(to_number(to_char(sysdate,'MI'))*60) +
(to_number(to_char(sysdate,'SS')))) * (16*1024))*100,5) "% to RSL scheme 1"
from dual;
oracle  dba  troubleshoot  patch 
10 hours ago by some_hren
Python SQLite: database is locked - Stack Overflow
Got this message when trying to use my 'ffhist' Python script while Firefox was open.
python  sqlite  dba  database  locking  errormessage 
4 days ago by kme
Getting the most out of Oracle SQL Monitor | SQLMaria
By default, a SQL statement that either runs in parallel or has consumed at least 5 seconds of combined CPU and I/O time in a single execution will be monitored.
It is also possible to force monitoring to occur for any SQL statement by simply adding the MONITOR hint to the statement.

SELECT /*+ MONITOR */ col1, col2, col3 FROM t1 WHERE col1=5;

If however, you can’t modify the SQL statement because it’s coming for a third part application etc. you can still force monitoring to occur by setting the event “sql_monitor” with a list of SQL_IDs for the statements you want to monitor at the system level.

ALTER SYSTEM SET EVENTS 'sql_monitor [sql: 5hc07qvt8v737|sql: 9ht3ba3arrzt3] force=true';


When SQL Monitor was originally introduced in Oracle Database 11g, the information required to generate the reports was only available in the dynamic performance view V$SQL_MONITOR_* (a size-constrained in-memory buffer) and not persisted to disk. So, there were no guarantees that the information would be retained beyond one minute after the statement completed. But in reality it’s often there a lot longer.

If you are on 11g, I strongly recommend you manually save any SQL Monitor reports you are interested in (see details on how below).

In Oracle Database 12c SQL Monitor reports are persisted in the data dictionary table DBA_HIST_REPORTS. By default, Oracle will retain SQL Monitor reports for 8 days.

Sound familiar?

It should, as it’s the AWR retention policy. That’s right the SQL Monitor retention policy is controlled by the AWR policy. In fact, each of the SQL Monitor reports stored in the DBA_HIST_REPORTS table is associated with an AWR SNAP_ID. You can change the retention policy using the DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS procedure.


To generate a SQL Monitor report in SQL Developer, go to the tools menu and click on Real-Time SQL Monitor.

You can generate a real-time SQL Monitor report on the command line by calling the PL/SQL function DBMS_SQLTUNE.REPORT_SQL_MONITOR using “active” as the report type as shown below.

SET trimspool ON
SET pages 0
SET linesize 32767
SET LONG 1000000
SET longchunksize 1000000

spool sqlmon_active.html

SELECT dbms_sqltune.Report_sql_monitor(SQL_ID=>’&sql_id’ TYPE=>'active')
FROM dual;

spool OFF

Just remember to edit the resulting sqlmon_active.html file to remove the first line and last line in the file (the spool off). The resulting html file can then be viewed in any browser. The browser must have connectivity to OTN to load the active report code.

How do I retrieve a historical SQL Monitor report in Oracle Database 12c?

You can review a historical or archived SQL Monitor report either from Enterprise Manager (EM), EM Database Express, or via the command line.

In EM Database Express, on the Monitored SQL tab of the Performance Hub page, click on the Select Time Period button above the time graph on the upper left hand side of the screen. A popup window will appear; where you can select which time period you want to see SQL Monitor reports from.

To manually generate a persisted SQL Monitor report for a single SQL statement, you will first need to find its REPORT_ID and then use the PL/SQL function DBMS_AUTO_REPORT.REPORT_REPOSITORY_DETAIL to extract the report.

The easiest way to find the REPORT_ID is to query DBA_HIST_REPORTS and supply as much information as you can about the SQL statement and when it was executed.

In DBA_HIST_REPORTS most of the column names are self-explanatory. However, there are two columns KEY1 and KEY2 that warrant some explanation, as you are going to need to use at least one of them in order to find the correct REPORT_ID.

KEY1 is the SQL_ID for the statement
KEY2 is the SQL execution_id for the statement

Here’s an example of the query I used:

SELECT report_id
FROM dba_hist_reports
WHERE dbid = 1954845848
AND component_name = 'sqlmonitor'
AND report_name = 'main'
AND period_start_time BETWEEN
To_date('27/07/2017 11:00:00','DD/MM/YYYY HH:MI:SS')
To_date('27/07/2017 11:15:00','DD/MM/YYYY HH:MI:SS')
AND key1 = 'cvn84bcx7xgp3';


Once you have the REPORT_ID, you can use the PL/SQL function DBMS_AUTO_REPORT.REPORT_REPOSITORY_DETAIL to generate the SQL Monitor report, as shown below.

SET echo ON
SET trimspool ON
SET pages 0
SET linesize 32767
SET LONG 10000000
SET longchunksize 1000000
spool old_sqlmon.html

SELECT dbms_auto_report.Report_repository_detail(rid=>42, TYPE=>'active')
FROM dual;

spool OFF
Just remember to edit the resulting old_sqlmon.html file to remove the first line and last line in the file (the spool off). The resulting html file can then be viewed in any browser. The browser must have connectivity to OTN to load the active report


Is it possible to generate SQL Monitor reports for all of the SQL statements monitored during a given period of time?
Starting in Oracle Database 12c, you can use the perfhubrpt.sql script, in the $ORACLE_HOME/rdbms/admin directory, to generate a PerfHub for a given time period, which will include SQL Monitor reports for all of the monitored SQL statements during that period.

The script will prompt you for the report level (default is typical but I would use all), the database id (default is the database you are on), instance number (default is the instance you are on) and the time period you are interested in.

The output of the perfhubrpt.sql is an html file that is an historical view of the EM performance hub for the specified time period.
oracle  dba  sql  performance  troubleshoot  tips 
21 days ago by some_hren
Особенности Oracle Real-Time SQL Monitoring | Oracle mechanics
В процессе анализа запросов удобно использовать SQL Monitor через процедуру DBMS_SQLTUNE.REPORT_SQL_MONITOR напрямую или через OEM/OCM/SQL Developer

Полезно при этом иметь в виду некоторые «врождённые» особенности отображения статистики выполнения запроса, как я полагаю, неизменные с версии 11g:

1) Если в процессе выполнения запрос, например, через PL/SQL функции вызывает рекурсивные запросы (классический пример — Oracle E-Business Suite), эти рекурсивные в выводе SQL Monitor на отражаются почти никак* (Если же с помощью запроса к ASH попытаться учесть ASH записи рекурсивных запросов, попадающих в ASH между MIN(SAMPLE_TIME) и MAX(SAMPLE_TIME) конкретного выполнения/выполнений «основного» запроса dfcrfxbfac5nx, картинка запроса наполнится новыми красками)

2) В вывод Real-Time SQL Monitoring попадают лишь записи из текущей V$ACTIVE_SESSION_HISTORY, соответственно, Activity / Activity Detail показывают/обсчитывают данные только за период, отражённый в н.м. в ASH, без учёта DBA_HIST_ACTIVE_SESS_HISTORY, что может затруднять оценку длинных запросов
oracle  dba  sysadm  performance  monitoring 
21 days ago by some_hren
microsoft/mssql-scripter: Repository for the new SQL cross-platform command line tools
Python scripts for generating scripts out of a SQL Server database, including data, if needed.
mssql  python  opensource  foss  utility  script  database  data  dba  tool 
25 days ago by taffit
Cleanly Uninstalling Stubborn SQL Server Components
Learn how to uninstall stubborn SQL Server components from your system.
SQLServer  installation  tips  Windows  dba  problem 
4 weeks ago by janneaa
ORACLE-BASE - ALTER SYSTEM CANCEL SQL : Cancel a SQL Statement in a Session in Oracle Database 18c
The ALTER SYSTEM CANCEL SQL command was introduced in Oracle Database 18c to cancel a SQL statement in a session, providing an alternative to killing a rogue session.

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

oracle  dba  18c 
6 weeks ago by some_hren

« earlier    

related tags

%product_hunt  18c  201902  abstraction  admin  administration  alias  alternativeto  annoyance  authorisation  automation  autonomousdb  aws  azure  backup  baseline  benjamindouglasconsulting  best_of  bestpractice  binning  blog  book  browser  bug  career  case  casestudy  cheat.sheets  cli  client  cloud  collation  commandline  comparisons  compsci  computer  configuration  confused  connection  cool  course  cplusplus  crossplatform  cs  data-model  data  dataarchitecture  database  databasedesign  databases  datadog  dataeng  datamunging  dataquality  datascience  datawarehouse  db  dbatools  ddl  debugging  dev  devel  development  devops  diagrams  dns  documentation  driver  duplicate  eclipse  errormessage  explained  explanations  firebird  flloss  foss  free  freemium  funny  genomebrowser  girhub  git  github  gui  guide  guis  hdfs  ide  ides  ingest  installation  introduction  jetbrains  jobs  joins  junior  juypter  k  linux  loadtesting  locking  lowcode  mac_apps  macosx  managementconsole  mariadb-sys  mariadb  maybesolution  metrics  microsoft  migration  monitoring  mssql  mysql-sys  mysql  mysqladmin  mysqlclient  mysqlcppconn  needshelp  network  networking  newbie  nosql  odbc  opensource  ops  optimization  oracle  oraclemagazine  orientation  paid  parallel  parameterizedquery  partitioning  patch  path  pdf  performance  perl  permissions  pipes  pocket  poster  postgres  postgresql  postgress  powershell  preparedstatements  problem  programming  project  protocol  psotgres  python  qt  queries  query  queryengnine  queryplan  queue  r  rds  redis  reference  relational  resources  scalability  schemadesign  schemaless  science  screencast  script  scripting  senior  sequelpro  server  service  settings  show  size  snippets  so  solution  sql  sqlalchemy  sqlite  sqlitebrowser  sqllite  sqlserver  stackoverflow  storedprocedure  subprocess  suggestion  sysadm  sysadmin  t-sql  tables  testing  theory  tigerteam  timeanddate  timestamp  tip  tips  tolearn  tool  tools  toread  totry  tounderstand  trial  troubleshoot  troubleshooting  tuner  tuning  tutorial  tutorials  ucsc  unix  updates  updating  upgrade  useful  utility  venndiagram  versioncontrol  vertica  video  visualization  web-app  windows  windows_apps  withpictures  workaround  wtf 

Copy this bookmark: