Introduction
What is Lighty?
Lighty is a supervision and monitoring software for Oracle databases, allowing you to analyze and optimize your Oracle database workload and resources.
The software is compatible with 10.2, 11.1, 11.2 and 12.1 Oracle versions. It runs on Standard or Enterprise versions (with or without Diagnostic and Tuning Pack).
Lighty knows how to exploit the features of RAC databases.
Main features
| View of several databases' performances at the same time |
| View of the activity in real time |
| View of past activity (based on L-ASH or ASH for standard versions) |
| Allows to easily explore the historical data in the AWR or Statspack |
| Allows to view all major statistics related to SQLs and sessions orders |
| Easy navigation among the many bases information through SQLs reports |
See the list of features.
System Requirements
The software runs on Windows and Linux platforms.
An option is provided in the software to allow you to use more or less memory depending on your needs. The minimum memory required is 256 MB with a maximum of 1024 MB. The default and recommended value is 512 MB.
Disk space occupied is about 200 MB.
Installation
Lighty is distributed as a self-contained application in a compressed file (Zip Format) on Windows platforms.
Just unzip the file in the desired location. A Lighty directory is created with a tree structure containing all the files required for its operation. Then start Lighty.exe.
| A version of the Java Runtime Environment (JRE) is included (version 7 update 51) |
| You don't need an Oracle client |
| You don't need to be administrator of your Windows computer |
| The software does not create any object in the database |
Lancement
When the application is launched, the firewall will request the authorization to open the port needed to display local help. Just select Cancel to stop having requests during subsequent starts.
Update
The software update can be done in 2 ways. In both cases, your preferences will be saved.
A window appears, telling you if you are using the latest version. If that's not the case, you can choose to automatically download and install the latest version. (An internet connection is required. For Internet access through a proxy, it may be necessary to set up access through software options.)
Removing the Lighty tree structure then decompressing the zip file.
Evaluation period
The free trial version is fully functional. However, you can not declare more than 5 connections. At the end of the evaluation period, only the monitoring multi bases remains accessible.
To continue using other options, you have to buy a license.
Diagnostic and Tuning Packs
The ASH and AWR packs are chargeable options for Oracle.
If you don't possess the licenses, we suggest you to use Lighty ASH and Statspack.
L-ASH
L-ASH (Lighty ASH) is a small utility which simulate ASH functionalities. This utility takes a snapshot of active sessions every 10 seconds. These images are stored in a table in order to be viewed in the Activity Viewer screen
The scripts are located in the L-ASH subdirectory tree software.
You can also download the file L-ASH.zip which contains all the scripts.
You can also download the file L-ASH.zip which contains all the scripts.
L-ASH Setup
Connect to the database as SysDBA
Run the installation.sql script (ex: sqlplus sys/password@TNS_ALIAS as SysDBA @installation.sql)
The script needs two parameters: the password and the default tablespace for the ASHSTAT account
The script creates the following items:
A user named ASHSTAT
2 tables
L_ASH - Table containing the session statistics
PARAMETERS - Parameters table
1 Package LIGHTY_PKG
LIGHTY_PKG.SET_RETENTION to change the retention statistics (by default, 7 days)
LIGHTY_PKG.SNAP to take a snapshot of active sessions
LIGHTY_PKG.PURGE to remove old data (retention stored in the PARAMETERS table)
2 jobs
ASHSTAT."L-ASH" - Runs every 10 seconds LIGHTY_PKG.SNAP
ASHSTAT."PURGE L-ASH" Executed once a day to purge old data
Installation Output
sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Tue Feb 4 20:15:49 2014 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> @installation installation of L-ASH V1.0 www.orachrome.com ----------------------------------- Choose ASHSTAT user's password ----------------------------------- Enter value for ashstat_pwd: ashstat Choose the Default tablespace for the ASHSTAT user --------------------------------------------------- Enter value for default_tablespace: users ... Creating ASHSTAT user ... Installing required packages SQL> alter session set current_schema=ASHSTAT; Session altered. SQL> set echo off feedback on; ... Create table L_ASH Table created. 1 row created. 1 row created. Table created. Index created. ... Create Package Lighty_pkg l_pkg112 1 row selected. Package created. No errors. Package body created. No errors. ... Create Scheduler jobs "L-ASH" and "PURGE L-ASH" PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. ... View Scheduler jobs "L-ASH" and "PURGE L-ASH" OWNER JOB_NAME STATE START_DATE REPEAT_INTERVAL NEXT_RUN_DATE RUN_COUNT -------------------- -------------------- --------------- -------------------- -------------------------------------------------- -------------------- ---------- ASHSTAT L-ASH SCHEDULED 02/04/2014 20:16:03 FREQ=SECONDLY;INTERVAL=10 02/04/2014 20:16:03 0 ASHSTAT PURGE L-ASH SCHEDULED 02/04/2014 20:16:03 FREQ=DAILY;BYHOUR=22;BYMINUTE=0;BYSECOND=0 02/04/2014 22:00:00 0 2 rows selected.
Statspack
Statspack is a tool allowing you to take snapshots of your base's statistics on a regular basis. Even if it is less complete than the AWR, it remains an interesting solution.
To install Statspack, connect as SysDBA,
then run the script spcreate.sql available in the directory "Oracle_HOME"/rdbms/admin
then run the script spcreate.sql available in the directory "Oracle_HOME"/rdbms/admin
Level Of Capture
There are several possible capture levels with Statspack (the default level is 5). The different levels and their descriptions are visible by querying the table STATS$LEVEL_DESCRIPTION.
To fully benefit from Statspack with Lighty, it is recommended to pass the capture level to 7 (includes among others statistics on segments).
You can directly update the table STATS$STATSPACK_PARAMETER (it requires to have at least one snapshot by instance):
sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Tue Feb 4 20:15:49 2014 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> @installation installation of L-ASH V1.0 www.orachrome.com ----------------------------------- Choose ASHSTAT user's password ----------------------------------- Enter value for ashstat_pwd: ashstat Choose the Default tablespace for the ASHSTAT user --------------------------------------------------- Enter value for default_tablespace: users ... Creating ASHSTAT user ... Installing required packages SQL> alter session set current_schema=ASHSTAT; Session altered. SQL> set echo off feedback on; ... Create table L_ASH Table created. 1 row created. 1 row created. Table created. Index created. ... Create Package Lighty_pkg l_pkg112 1 row selected. Package created. No errors. Package body created. No errors. ... Create Scheduler jobs "L-ASH" and "PURGE L-ASH" PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. ... View Scheduler jobs "L-ASH" and "PURGE L-ASH" OWNER JOB_NAME STATE START_DATE REPEAT_INTERVAL NEXT_RUN_DATE RUN_COUNT -------------------- -------------------- --------------- -------------------- -------------------------------------------------- -------------------- ---------- ASHSTAT L-ASH SCHEDULED 02/04/2014 20:16:03 FREQ=SECONDLY;INTERVAL=10 02/04/2014 20:16:03 0 ASHSTAT PURGE L-ASH SCHEDULED 02/04/2014 20:16:03 FREQ=DAILY;BYHOUR=22;BYMINUTE=0;BYSECOND=0 02/04/2014 22:00:00 0 2 rows selected.
Rem Rem -------------------------------------------------------- Rem Rem Update Statspack Level to 7 Rem and sqls captured to 100 Rem Rem V1.0 Rem 15/09/2014 - Creation Rem Rem www.orachrome.com Rem -------------------------------------------------------- Rem update stats$statspack_parameter set SNAP_LEVEL=7,NUM_SQL=100 ; commit;
Snapshot scheduling
To take Statspack snapshots, you have to edit the statspack.snap from perfstat diagram at regular intervals (usually every hour).
On the RAC environments it is necessary to run the procedure on each instance.
The following PL / SQL program allows you to program Statspack snapshots on standalone or RAC databases.
This PL / SQL code creates a program and a running job for each instance. On databases in RAC mode, to avoid possible contentions, jobs are started sequentially.
Thus, only the instance 1 job is scheduled to run at regular intervals. At the end of his execution, it triggers the release of the job on instance 2, which triggers the job running on instance 3, etc.
Rem
Rem --------------------------------------------------------
Rem
Rem This PL/SQL will create a hourly job
Rem to take a statspack snapshot
Rem
Rem RAC Aware
Rem
Rem V1.0
Rem 15/09/2014 - Creation
Rem
Rem www.orachrome.com
Rem --------------------------------------------------------
Rem
Rem Example of Dropping jobs and programs in a 2 instance Rac Database
Rem exec dbms_scheduler.drop_job('STATSPACK_SNAP_1');
Rem exec dbms_scheduler.drop_job('STATSPACK_SNAP_2');
Rem exec dbms_scheduler.drop_program('PROG_STATSPACK_SNAP_1');
Rem exec dbms_scheduler.drop_program('PROG_STATSPACK_SNAP_2');
Rem --------------------------------------------------------
Rem Installing jobs and programs
Rem --------------------------------------------------------
DECLARE
newjob SYS.job_definition;
newjobarr SYS.job_definition_array;
cpt NUMBER := 0;
name VARCHAR2(50);
command VARCHAR2(80);
jobenable BOOLEAN;
cptinstances NUMBER := 0;
repeatInterval varchar2(400);
startDate date;
BEGIN
newjobarr := sys.job_definition_array();
select count(*) into cptinstances from gv$instance;
newjobarr.extend(cptinstances);
-- Add a job and a program for each instance
FOR rec IN (select inst_id, max(inst_id) over () maxInstId,lead(inst_id,1,0) over (order by inst_id) nextInstId from gv$instance)
LOOP
name := 'STATSPACK_SNAP_' || rec.inst_id;
command :='';
IF (rec.inst_id < rec.maxInstId)
THEN
command := 'dbms_scheduler.enable(''STATSPACK_SNAP_' || rec.nextInstid || ''');';
END IF;
DBMS_SCHEDULER.CREATE_PROGRAM( program_name=> 'PROG_' || name,
program_action=>'BEGIN perfstat.statspack.snap;' || command || ' end;',
program_type=>'PLSQL_BLOCK',
number_of_arguments=>0,
comments=>'',
enabled=>TRUE);
IF (rec.inst_id =1)
THEN
jobenable := true;
repeatInterval := 'FREQ=HOURLY';
startDate := trunc(sysdate,'HH')+1/24;
ELSE
jobenable := false;
repeatInterval := null;
startDate := null;
END IF;
-- Create a JOB_DEFINITION object type
newjob := sys.job_definition(job_name => name,
job_style => 'REGULAR',
program_name => 'PROG_' || name,
repeat_interval => repeatInterval,
-- start_date => trunc(sysdate,'HH')+1/24,
start_date => startDate,
auto_drop => FALSE,
enabled => jobenable,
instance_id => rec.inst_id);
-- Add it to the array
cpt := cpt +1;
newjobarr(cpt) := newjob;
END LOOP;
-- Call CREATE_JOBS
DBMS_SCHEDULER.CREATE_JOBS(newjobarr, 'TRANSACTIONAL');
END;
Removing old data
You can manually delete old snapshots
by running the script sppurge.sql in the directory "ORACLE_HOME"/rdbms/admin
by running the script sppurge.sql in the directory "ORACLE_HOME"/rdbms/admin
You can also schedule a job to delete old snapshots.
The following PL / SQL script creates a daily job (launched every day at 11:00 pm) to delete Statspack snapshots. The snapshot history is defined as for the L-ASH tool by the ASHSTAT.LIGHTY_PKG.SET_RETENTION package (Default retention of7 Days).
Rem
Rem --------------------------------------------------------
Rem
Rem This PL/SQL will create a daily job (Runs every day at 11PM)
Rem to purge the statspack snapshots
Rem RAC Aware
Rem
Rem L-ASH must be installed
Rem The retention is stored in the table ASHSTAT.PARAMETERS
Rem Default : 7 Days
Rem
Rem V1.0
Rem 15/09/2014 - Creation
Rem
Rem www.orachrome.com
Rem --------------------------------------------------------
Rem
grant select on ashstat.parameters to perfstat;
exec dbms_scheduler.drop_job('"PERFSTAT"."PURGE STATSPACK"');
BEGIN
sys.dbms_scheduler.create_job(
job_name => '"PERFSTAT"."PURGE STATSPACK"',
job_type => 'PLSQL_BLOCK',
job_action => 'DECLARE
DAYS NUMBER := 7;
BEGIN
SELECT value INTO days FROM ashstat.parameters WHERE name=''retention'';
FOR instances in (select sp.dbid,INSTANCE_NUMBER
FROM stats$database_instance sp,v$database v
WHERE sp.dbid=v.dbid)
LOOP
FOR snapshot_range IN (SELECT min(snap_id) MIN_SNAP_ID ,max(snap_id) MAX_SNAP_ID
FROM stats$snapshot
WHERE dbid=instances.dbid
AND instance_number=instances.instance_number
AND snap_time < trunc(sysdate)-DAYS)
LOOP
IF (SNAPSHOT_RANGE.MIN_SNAP_ID IS NULL)
THEN
EXIT;
END IF;
statspack.purge( i_begin_snap => snapshot_range.MIN_SNAP_ID
, i_end_snap => snapshot_range.MAX_SNAP_ID
, i_snap_range => true
, i_extended_purge=> false
, i_dbid => instances.dbid
, i_instance_number => instances.instance_number);
END LOOP;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20100,''PERFSTAT.STATSPACK PURGE Error'' || SUBSTR(SQLERRM, 1 , 1000));
END;
',
repeat_interval => 'FREQ=DAILY;BYHOUR=23;BYMINUTE=0;BYSECOND=0',
start_date => systimestamp,
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'Purge statspack repository',
auto_drop => FALSE,
enabled => FALSE);
sys.dbms_scheduler.set_attribute( name => '"PERFSTAT"."PURGE STATSPACK"', attribute => 'job_weight', value => 1);
sys.dbms_scheduler.set_attribute( name => '"PERFSTAT"."PURGE STATSPACK"', attribute => 'auto_drop', value => TRUE);
sys.dbms_scheduler.enable( '"PERFSTAT"."PURGE STATSPACK"' );
END;
/
Rem Rem -------------------------------------------------------- Rem Rem Statspack Jobs Rem Rem V1.0 Rem 15/09/2014 - Creation Rem Rem www.orachrome.com Rem -------------------------------------------------------- Rem set lines 500 pages 40 head on col JOB_NAME for A20 col OWNER for A20 col REPEAT_INTERVAL for A50 col START_DATE for A20 col NEXT_RUN_DATE for A20 select OWNER,job_name,state,to_char(START_DATE,'MM/DD/YYYY HH24:MI:SS') START_DATE, REPEAT_INTERVAL, to_char(NEXT_RUN_DATE,'MM/DD/YYYY HH24:MI:SS') NEXT_RUN_DATE, run_count from dba_scheduler_jobs where regexp_like(job_name,'STATSPACK') /
The scripts are located in the L-ASH subdirectory tree software.
You can also download the file L-ASH.zip which contains all the scripts.
You can also download the file L-ASH.zip which contains all the scripts.
To sum up, the sequence of steps to carry out in order to set up a complete Statspack management (while logged as SysDBA and after installing L-ASH) is as follows:
- Statspack installation + programming snapshots + purge old data
- SQL>@?/rdbms/admin/spcreate (Installation of Statspack)
- SQL>exec perfstat.statspack.snap; -> If the database is in RAC mode, take a snapshot by instance
- SQL>@lighty_sp_update_level.sql (Modification of the capture level and number of SQLs captured.)
- SQL>@lighty_sp_snap_job.sql (Schedule captures)
- SQL>@lighty_sp_purge_job.sql (Schedule deletion of old snapshots)
- SQL>@lighty_sp_view_jobs.sql (View of already-created jobs)
Retention management
By default, 7 days of activity history are kept in memory
To change this retention, run ASHSTAT.LIGHTY_PKG.SET_RETENTION, specifying the number of days you want to keep online.
To change this retention, run ASHSTAT.LIGHTY_PKG.SET_RETENTION, specifying the number of days you want to keep online.
Software Configuration
You must report these two utilities in a connection properties if you want them to be taken into account by the software. To this end, open the properties of a connection, and check the corresponding boxes.
General Operation
How to start with Lighty
Main Toolbar
This toolbar can be hidden. All the options in the toolbar are also available in the menus or via keyboard shortcuts.
Keyboard shortcuts
| Shortcut | Description |
|---|---|
| Ctrl + G | Moving to multiple connections mode |
| Ctrl + I | Moving to single connection mode |
| Ctrl + M | Displays the connection configuration page |
| Ctrl + A | Adding a new connection |
| Ctrl + O | Displays the options window |
| Ctrl + N | Displays or hides the connection floating window |
| Ctrl + R | Displays or hides the report floating window |
| Ctrl + Q | Displays or hides the floating window of a SQL's statistics |
| Ctrl + S | Displays or hides the floating window of a session's statistics |
Floating Windows
Lighty uses floating windows on its GUI. These windows can be grouped, moved to the edges of the main window, resized, hidden. Some can be maximized by double-clicking on the title bar or icon.
| Window | Can be maximized | Can be Moved |
|---|---|---|
| Connection List | ||
| SQL reports List | ||
| SQL Statistics | ||
| Session Statistics |
Dashboard
The dashboard provides an overview of different activity statistics through a single page. It is managed through the 
icons. This single page is organized into tabs. You can add a new tab from the 
button. Each tab can be named. You can also close a tab with the appropriate button. Within each tab, you can organize your table using predefined widgets. By default, the table has 3 columns dedicated to widgets. Decrease / increase the number of columns using the 
button. The widgets available for the dashboard are visible by clicking on the 
button. To insert a widget into the dashboard, simply drag and drop it to the desired location using the mouse. Widgets are resizable (width and height). You can also customize or remove any widget on the dashboard. A single widget can be opened multiple times.
Some have options. In this case, the 
icon appears at the top right of the widget.
Automatic backup of your environment is performed for each modification, ensuring the persistence of your dashboard.
The dashboard principle is used in 2 screens (which are by default pre-filled with some widgets):
- "Activity Viewer" Tab (Bottom)
- "Real Time" Tab
Hyperlinks
Hyperlinks allow you to easily navigate among all the information available. They are blue.
In the reporting screen, the browsing history is displayed at the top of the window, and allows you to return instantly to the desired information.
Filters
Filters are ubiquitous in the different windows of the software. They can easily isolate the desired information
There are 3 types of filters:
- The checkbox
- The text field
The filter is automatically applied as you type in - Value Lists
The filter is automatically applied as you select a value.
You can select multiple values
Timeline Components
This component visually informs you of the identifier's activity in a certain range of time.
The activity is divided by Oracle wait categories
Specific colors for each class
Specific colors for each class
The example below shows you the SQLs consumption in a certain time range (11:50 am to 2:10 pm)
Several information are visible on a single component:
The implementing range
The type of activity
The activity time
This component is present in many screens and widgets of the software.
Automatic refreshes
Many screens offer a refresh of displayed information.
In this case, the 
buttons allow you to manage this refresh.
To refresh automatically, select the checkbox and the frequency on the list (10s, 30s or 60s). A countdown is displayed.
To refresh manually, click on the icon.
Export Tools
Depending on the type of information displayed, the export button 
allows various operations:
- Copy content to the clipboard
- Generate ASH report (global, for a SQL or a session)
- Generate AWR reports in HTML
- Generate ADDM reports in HTML
- Generate a report for a supervised SQL in HTML format
Connections
Ajout
The addition is done through the 
button, the menu or the shortcut Ctrl + A.
Adding a connection takes 3 steps.
1 - Definition
The connection name must be unique.
By default, a 10-session connection pool is proposed and used. You can increase or decrease this pool.
The type and application details allow you to group your connections depending on their functions.
You can then easily identify the connections later (in the menu or in the Control Panel)
2 - Configuring connection
Set the user and password.
A connection as SysDBA can show some special reports (data cache, hidden settings, log alert content)
The password can be saved (in encrypted format) so you don't need to enter it for every connection.
The connection is configured entering the server name, port, and the instance or service name. You can also enter a complete connection string (TNS Entry for example).
3 - Options
Choose here the packs used by the database. By default, since the 11G version, the parameter control_management_pack_access defines the use of Diagnostic and Tuning packs.
If you don't use these packages, you can install L-ASH (simulation tool for the ASH functionality). Then check the "L-ASH installed" option.
You can also check the Statspack option if it is installed.
Configuring connections
This window is accessible from the "Options" menu, by the 
icon or by the shortcut Ctrl + M
The window lists all defined connections. You can modify their configuration, or delete a connection.
List connections
You can list the connections via the "Connection List" menu or the floating window of the same name.
The menu regroups connections by type and application.
Activity of a database
3 tabs help you to navigate among the statistics.
If you do not have the Diagnostic Pack license,
you can install L-ASH to simulate the ASH functionality
you can install L-ASH to simulate the ASH functionality
The "Activity Viewer" tab allows you to view a database's activity. Based on data from the "Active Session History" feature, this screen helps you in understanding the activity.
The main graph is in the middle of this screen.
This graph represents, over a certain range of time, the consumed activity of your database divided into waiting class and CPU consumption.
To isolate the desired information, several options are available:
- Select a date range
- Filter data
- Focus on a part of the graph
- Select only the data from the SGA (buffer ASH) for even more precision
- Organize the display of your dashboard
1 - Select a date range
Use the buttons:
You can also choose the exact date and time of start and end by clicking the "Custom" button and entering the start / end date / time.
2 - Filter data
The field filter allows you to choose a SQL ID or a Session ID.
You can also show / hide the list of predefined filters using the 
button.
These filters are configurable (
button) from a list of twenty items.
3 - Focus on a part of the graph
Use the mouse to select a part of the graph and display the corresponding statistics.
The buttons in the graph 
(or right-click menu of the mouse) allow you to save, copy to clipboard, zoom in or out...
4 - Select only the data from the SGA (buffer ASH) for even more precision
This choice is made by selecting the check box 
Once selected, all the displayed data will come from the SGA memory only, and more precisely from the ASH buffer.
The 
button allows you to see the retention, the number of lines and the size of the buffer (per instance).
5 - Organize your dashboard
| Widget | Details | Timeline Component | Possible Options |
|---|---|---|---|
| Graph | Displays the activity of a subset as a pie chart or graphical history |
Pie chart or graphical history Choice of Statistics (Server, program, user, customer identifier, module, action, SQL operation, SQL, system event) |
|
| Sessions |
Displays sessions Distils the information by displaying the SQLs for a session. |
||
| SQLs |
Displays SQLs Distils the information by displaying the system events for a SQL. The execution plan is displayed next to the SQL. When there are several plans, only the number of plans is shown. You can view the different plans by clicking on the tree icon. |
||
| PL / SQL |
Displays PL / SQL Distils the information by displaying SQLs for PL / SQL. |
||
|
Service, Module, Action |
Displays activity by service, module, action | Grouping based on one or more criteria | |
| Machine, Program, User | Displays activity by machine, program, user | Grouping based on one or more criteria | |
| Client Identifier | Displays activity by customer ID | ||
| Waiting class, system event | Displays activity by waiting class and/or system event | ||
| Instance, Service | Displays activity by Instance and/or Service | Grouping based on one or more criteria | |
| Segments | Displays usage segments | ||
| PGA, Temporary Space Usage | Displays the PGA or the maximum temporary space used in the time interval | Grouping by SQL or by Session | |
| Blocking sessions | Displays blocking sessions and sessions blocked in the time interval |
Real Time
The control panel shows in real time the database activity and statistics.
It is organized as a dashboard in which you can customize the layout.
| Some widgets show the instantaneous activity |
| The Supervised SQLs |
| The long operations sessions |
| The use of temporary spaces, PGA |
| The outstanding transactions |
| The blocking sessions |
| Some widgets show incremental statistics calculated between 2 snapshots: |
| The system statistics |
| The "Latches" type locks |
| The event histograms |
| The segments used |
| Some widgets show incremental statistics calculated between 2 snapshots: |
The activity is divided by oracle wait categories
( button on the top right of each widget)
(
button on the top right of each widget)
A single widget can be opened multiple times.
Reports
The "Reports" tab allows you to explore your database statistics. For simplicity, these reports are classified by category and subcategory.
| Category | Subcategory |
|---|---|
| Db Information | Db Information, Properties / Usage, Parameters, Memory Components, Alerts, Alert Log, FRA, Backups |
| Physical Design | Tablespaces, Db Files, Redo Logs, Archiving Activity |
| DB Statistics | Current Activity (ASH), Past Activity (AWR), Past Activity (Statspack), Db Statistics, Statistics / Optimizer |
| Sessions | Sessions, Blocking Sessions, Session Long Operations |
| SQLs | Running SQLs, Monitored SQLs, Top 100 SQLs, Not Shared SQLs |
| Segments / Tables | Objects by Schema, Tables / Indexes... |
| Locks / Transactions | Active Transactions, Transactions Recovering, Dictionary Cache Activity |
Navigation hyperlinks and navigation history displays make the passage from one information to another easier.
Some reports require Diagnostic and Tuning packs licenses
Lighty prevents them from running if you select the right options at the connection level
These reports concern the operation of the AWR repository, the supervised SQLs and the view of ASH activity
Lighty prevents them from running if you select the right options at the connection level
These reports concern the operation of the AWR repository, the supervised SQLs and the view of ASH activity
Some reports are only accessible when connected as SysDBA
(data buffer cache, hidden parameters, alert log viewer)
(data buffer cache, hidden parameters, alert log viewer)
SQL Statistics
A Floating Window is dedicated to a SQL's statistics. It is automatically displayed when you click the SQL ID on the screens.
You can also enter the SQL ID (and if possible the "child" number) to search manually.
This window can be maximized by clicking on the title bar
| Find all the information relating to a SQL |
| The different execution plans (including those stored in the AWR repository or Statspack) |
| The execution statistics from the AWR or Statspack |
| The SQL usage displayed in the form of a timeline component |
| Runtime statistics in the cache (SQL Area) |
| The profiles, patches, and present baselines |
The statistics can be shown per execution, fetch, or row
(using the
buttons)
(using the
buttons)
You can also view the past bind variables and the optimizer environment
for each execution plan and for each SQL cursor present in the cache.
for each execution plan and for each SQL cursor present in the cache.
Compare Tab
The purpose of this new tab is to improve the comparison of two data sources
• The current database with a snapshot (export) of a database saved beforehand.
• You can also compare two previous states (exports).
• Or a same base at two different points in time.
Once in the Compare tab you can select "Actual Source" or the Import button.
The values in the table on the right are not clickable like the Activity Viewer tab.
Filters or buttons to vary the period remain operational.
Reports
In the "Reports" section, we find various reports highlighting the most relevant information for the DBAs' work. Since the 1.6.0 version, you have the possibility to create your own custom reports.
There are three new reports that have emerged:
- Jobs Visualization and Management
- Comparison of two AWR Reports
- Statspack Reports
Jobs Visualization and Management
In this view, you can visualize the different jobs. You can also Enable or Disable a job.
Comparison of two AWR Reports
You can now compare two AWR reports and generate a comparative report in HTML format. In the view below, select the first of the two reports you want to compare (just click on the "Compare To..." link next to the selected report).
Now choose the second report you want to compare (you must click on the "Compare" link next to the desired report).
This generates a comparative report in HTML format.
Statspack Reports
In the "Pack Activity (Statspack)" section a new feature has been added. It can generate a report in text format.
To generate a report, you must first define an SSH connection
Select the menu 'Current connection / Information'
Then log into an account allowing to run sqlplus (the spreport.sql script is used)
Click on "Text Report" to generate the report in text format.
Sessions Statistics
A floating window is dedicated to session statistics. It is automatically displayed when you click the SID of a session present on the different screens.
You can also enter the SID to search manually.
This window can be maximized by double-clicking on the title bar
(or on the corresponding icon)
(or on the corresponding icon)
| Find all the information related to a session |
| Login information |
| The open cursors |
| The optimizer's environment |
| The statistics of the session(incremental and global) |
| The current transaction |
| SQLs execution history, in the form of a Timeline |
Import / Export Databases
You can import and export databases connections between the different Lighty softwares
Kill a session
You can kill a session from the Lighty interface
Export reports
You have the possibility to export reports in CSV format
Snap Statspack / AWR and L-ASH / ASH
You can launch a snap Statspack / AWR and L-ASH / ASH (depending on the configuration of your bases) to perform analysis on the appropriate period
Enable / disable a session track
You can enable and disable a session track from Lighty (the path of the generated log file is displayed in the software)
The SQL Report Wizard Duplicate Function
SQL Report Wizard also allows you to duplicate or modify already existing reports. The "Duplicate" function was designed to create custom reports based on existing reports. Thus, an ordinary user will be able to define its own reports without modifying the existing ones. This will save him considerable time, because if there is already a report similar to the one he wants, he won't have to recreate everything. The "Duplicate" button is located on the Report tab, at the bottom of the screen.
To duplicate a report, simply open an existing report and click on the "Duplicate" button. This will open the report editor, in which the various fields will be pre-filled with the old report's values. Only the default name is changed so that the reports don't have two identical names.
Once the report is duplicated, you can modify it, delete it or duplicate it again at your convenience.
Warning: you can only modify or delete a previously added report.
The default reports contained in Lighty cannot therefore be edited nor deleted.
Monitoring a database
You can enable automatic monitoring of certain metrics in a database.
Introduction
The goal is to be able to send alert emails when the value of a metric for an active connection exceeds a certain threshold.The values come from the "Charts" of the "Real Time" tab.
For now, only the following types of values can be monitored:
- Chart I/O: write/s and read/s values
- Chart response Time: CPU value
- Chart Avg Active session: Avg value Active session
Principle of triggering an alert
For each active connection, the chart refresh function is used to test whether the displayed value type is to be monitored.- If this is the case, it has to be ensured that the interval of time elapsed since the last check (check interval) is greater than or equals to the duration defined for this type of value
- If the elapsed time is sufficient, the value is checked.
- If the value is greater than or equals to the threshold defined for this type, the mode is changed to "warning" and the retry interval is redefined.
- If the "warning" status is reached consecutively the number of times defined for this type (max check attempts), it triggers the sending of an alert
For example for Chart response Time: CPU value
We have a check interval of 5 minutes, a retry interval of 2 minutes, a max check at time equal to 3 and a % CPU of 10%
The verification will take place every 5 minutes.
- If the value is greater than or equals to 10% we go into warning mode and then check every 2 minutes.
- If there are 3 consecutive warnings, an alert is sent. Then the warning is deleted and the check interval goes back to 5 minutes.
- If you go below the 10% threshold before reaching the 3 warning, the check interval goes back to 5 minutes.
Software Configuration
This is done from the 'Options: Menu tools / Options' dialog box, 'Monitoring' tab
In the 'Monitoring Configuration' frame, the default parameters of all monitorings are defined.
These parameters will be used if the 'Default' box of a monitoring is checked.
The 'Enable the monitoring' box allows:
- to disable all monitoring (when unchecked)
- or to make them accessible to the modification (when checked).
- You can activate or deactivate a monitoring (box at the top left of the frame).
- You can use the default global values for 'Check Interval', 'Max Check Attemps' and 'Retry Interval' by checking 'Default'.
- If the box 'Default' is not checked, you can customize these values.
Configuring connections
From the 'Connection Manager' screen (accessible via the 'Tools' menu), select a connection on the list and click on the right button of the mouse to bring up the context menu.
The 'Configure Monitoring' option opens the configuration window for the selected connection.
- For this connection, we can then choose to activate the monitoring: case "Enable" the monitoring.
- Then for each type of value to be monitored you can choose to use the default values; in this case the global values will be used and the frame will be grayed out.
- Otherwise, you can redefine these values for this connection.
Note: If global monitoring is disabled, this screen will remain fully grayed out.
Email sending
Depending on the type of license, you may or may not enter the recipient(s) in the Email field.
- For a simple license, the mail used is not modifiable, it is the one that was informed during the license application.
- For a multi-site license, you can modify the global mail and redefine it for each of the connections. If for a connection the field is left empty, then the global mail will be used.
Note: You can enter several recipients by separating addresses by a ";".
Example email:
The SMTP server sending mails is configured from the 'Option' window ('Tools / Options' menu), 'Email' tab.
Impact on performance
The monitoring uses the data assembled by the Charts of the Real Time part.No additional query is performed, so the impact on the DB server is void.
The Lighty application is moderately impacted.
An active monitoring list is maintained for easy searching. It contains for each active connection the list of values types to be monitored.
The chart display loop performs a search for each new value on this list, to see if a monitoring is in progress for this value type and connection.
If an alert is to be sent, it is queued. The sending of an e-mail is processed in a separate thread.
Note: Since the charts are refreshed every minute, you cannot go below this value for the check interval.
SQL Report Wizard
The SQL Report Wizard is a configuration tool which allows the user to create its own reports. It can be accessed via the "Tools" menu, clicking on "SQL Reports Wizard". The generated reports can be found in the menu on the left of the screen with the existing reports.
 |
 |
 |
||
| Menu | Bottom of a window | Reports Window |
To add a report, you must go through the SQL Report Wizard menu.
To edit or delete a report, you must display it on the screen by selecting it in the Reports Window (accessible from the Windows menu) and click on the links at the bottom of the report.
You can only edit or delete a report that you have previously added.
The default reports contained in Lighty cannot therefore be edited nor deleted.
Screen details
1 - SQL Properties
A report executes an SQL query whose properties are defined in this first screen.
First of all, you have to specify the type of result you want:
| Default Table |
|
| Single Line Table Properties |
|
| Tree Table Properties | ... |
| Text Area |
|
| Html Area | The query returns an HTML output that is displayed in the default browser. |
| Clob Text | ... |
| Refresh |
The report will be refreshed periodically, the period can be configurable. 
|
| Diag Pack Needed | Uses Diag Pack |
| AWR Exportable |
The export menu is supplemented by AWR exports 
|
| Statspack | Uses Statspack |
| Only For SysDBA User | Will appear only for SysDBA user |
| Tuning Pack Needed | Uses the tuning pack |
| ASH Exportable |
The export menu is completed by ASH exports 
|
A parent query will be attached to a category selected from the list:
The report will then appear in the category tree as well as in the "My Report" category.
Warning: if one day you choose to delete a whole category, you will delete all related queries.
A child query is not attached to any category, it will be floating in the tree structure. Unless it is linked to an existing group, in this case it will be associated with the category of the latter.
A query can create a group (a group can be seen as a subcategory) or join a group by selecting it on the list.
Creation:
Affiliation with a group:
If there is no group, the report will be only visible in the "My Report" category.
The Keyword field will be used to search for reports, in addition to the name.
Graphical example:
2 - SQL Versions
Several version ranges can be configured on this screen. For each of them, a compatible request can then be defined.
By default, a row exists for the databases from version 10.2.
To modify the Min and Max versions, you must select the new values in the dedicated lists and click on "Modify".
To add a row, click on "Add"; the values selected on the lists are then used to create the new row.
To delete a line, select it and click on "Delete".
The different rows' ranges must not overlap.
The value Max 99999 must appear in a row.
The "Valid" column indicates whether a query is defined for this version range.
Before moving to the next screen, you must select the line you want to define the request for.
3 - SQL Text
This screen allows you to enter the query that will retrieve data from the report.The query will be used for databases whose version will match the range selected on the previous screen.
The value of any parameters of the query is represented by the character "?".
The "Parse" button is used to test the query using the database selected in the "Connection to test" list.
If successful, "Parse succedeed" is displayed.
4 - Columns Definitions
This is where you can set each column of the query.
| Displayed | Indicates whether the column will be displayed in the report |
| Filtered | Indicates whether the column can be used to filter results (see Filter Values) |
| Incremental | Indicates whether the column displays the difference with the previous result of the query (in the case of a query that is periodically refreshed) |
| Virtual | Indicates whether the column's value will be calculated based on 2 other columns' value (see Virtual Columns screen) |
| Type |
Indicates the type of value returned. The type can be redefined through a list; depending on the type, the column will display default links to another windows. Example: For the SID (Session ID) type, clicking on the value opens the Session statistics window 
|
| ProgressBar |
Indicates whether the column displays values in the form of a progress bar (seeProgress Bar properties screen). Example:
|
| Linked |
Indicates whether the column displays a link to another report (see Linked Column) Example: 
|
| Min. Width | Specifies the column's minimum size in pixel |
5 - Virtual Columns
This screen allows you to define the method of calculating the values of the columns whose "Virtual" parameter has been checked.
After selecting on the list the column to be processed, you must choose the 2 columns whose values will be used, as well as the operation to obtain the final value.
 |
 |
 |
| Operand 1 | Operator | Operand 2 |
6 - Parameters
This screen allows you to define the type of each parameter identified by a "?" in the query.
Once you have selected the parameter on the list, you can define its name and type.
The parameter's input area will be displayed depending on the selected type.
Example: Selecting a Tablespace List type displays the Tablespaces drop-down list:
If you check the "PDB Parameter" box, the PDB List type will be automatically selected.
The "Select Result" type allows you to use the result of a query to fill a list of choices.
The query is verified via the "Parse SQL" button.
7 - Progress Bar Columns Properties
This screen allows you to configure the columns whose values are displayed in the form of a progress bar.
After selecting the column, choose the display color and the type among:
8 - Linked Columns
This screen allows you to configure the links of the columns whose "Linked" attribute has been checked.
If the query has only one parameter, the report can be defined as the one displayed by default when you click on a column whose name is defined in the field "Column Linked".
This means that if another report includes a column of the same name, clicking on a value in that column will display the default report.
For each column "linked" the report to be called can be defined.
If the report needs a parameter from the calling report, it should be added to the list Used Column Names.
Two types of configuration allow the calling of a report:
1. Simple Link to SQL: Display the report selected on the list Linked to SQL.
2. Conditional Link depending on column Values: Displays reports based on the value of a column.
• Depending on value of: select column
• When allows you to enter the value
• Then: allows you to select the corresponding report
Use the Add and Remove buttons to add or remove conditions.
9 - Filter Values
This screen allows you to set the filters that will be presented in the report header
For each column whose "Filtered" attribute has been checked, you can choose the display mode among:
| CheckBox Filter | Used for Yes / No columns, it displays a checkbox and a label defined in the Filter Displayed Text field. The filter value used when the box is checked is defined in the Filter value field. Example: for the IsDefault column, the display will be as follows:   |
| Field Filter | Displays a value input field for the filter For multitenant databases, the filter on the PDB is automatically added  |
| Panel Filter | Displays a panel containing the values of all columns defined as filtered Example: here Backup type and Status are columns used for filtering  |