Introduction
What is Lighty ?
Lighty is a supervision and monitoring software for oracle database performance.
The software is compatible with 10.2, 11.1, 11.2 and 12.1 oracle versions. It works with the Standard or Enterprise versions (with or without Diagnostic and Tuning Pack).
Lighty knows how to exploit the features of RAC databases.
Main functionnalities
| View of the performance of multiple databases simultaneous |
| Vision of the activity in real time |
| Vision of past activity (based on L-ASH ASH or for standard versions) |
| Allows to easily explore the historical data in the AWR or Statspack |
| Allows viewing all major statistics related to SQLs and sessions orders |
| Easy navigation among the many information bases through SQLs reports |
See the list of features.
System Requirements
Lighty works on Windows, Mac OSX and Linux Operating Systems.
An option is provided in the software to allow you to use more or less memory as needed. The minimum memory required is 256 MB with a maximum of 1024 MB. The default and recommended value is 512 MB.
The occupied disk space 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 to the desired location. A Lighty directory is created with a tree containing all the files required for its operation. Then start Lighty.exe
| A version of the JRE (Java Runtime Environment) is included (version 7 update 51) |
| You don't need an oracle client |
| You do'nt need to be administrator on your 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 click Cancel to stop having the message the next time you start
Update
The software update can be done in 2 ways. In 2 cases, your preferences will be preserved.
A window appears telling you if you use the latest version. If it doesn't, 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 then decompressing the zip file.
Evaluation period
The free trial version is fully functional. However, you can not declare more than 5 connections. When the evaluation period is over, only the monitoring multi bases remains accessible.
To continue using other options, you have to buy a licence.
Diagnostic and Tuning Packs
The ASH and AWR packs are paid options for Oracle.c.
If you do not possedez licenses, we offer the alternative use of Light and Statspack ASH.
L-ASH
L-ASH (Lighty ASH) is a small utility to simulate functionalities of ASH. This utility takes a snapshot every 10 seconds of active sessions. These images are stored in a table in order to be viewed in Activity Viewer Screen
The scripts are present in the L-ASH subdirectory tree software.
You can also download the file L-ASH.zip that contains all the scripts.
You can also download the file L-ASH.zip that contains all the scripts.
L-ASH Setup
Connect to the database as sysdba
Run script installation.sql (ex : sqlplus sys/password@TNS_ALIAS as sysdba @installation.sql)
The script asks 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 sessions 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 perform the deletion of 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 on a regular basis the statistics of your base. Less complete than the AWR, it remains an interesting solution.
To install Statspack, connect as sysdba
then, execute the script spcreate.sql available in the directory "Oracle_HOME"/rdbms/admin
then, execute the script spcreate.sql available in the directory "Oracle_HOME"/rdbms/admin
Level Of Capture
There are several possible catch levels with statspack (the default is 5). The various levels and descriptions are visible by querying the table STATS$LEVEL_DESCRIPTION.
To fully benefit from Statspack with Lighty, it is recommended to pass the catch level 7 (includes among other statistics on segments).
You can directly update the table STATS$STATSPACK_PARAMETER. (Requires to have at least a 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 snaposhots, you have to edit the statspack.snap from perfstat diagram at regular intervals (usually every hour).
On the RAC environments it is necessary to perform the procedure on each instance.
The PL / SQL following program allows to set up the programming snapshots statspack on standalone or RAC databases.
This PL/SQL code create a program and an execution job for 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 performance, it triggers the release of the job on instance 2, which triggers the job is running on the 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 for deleting old snapshots.
The following PL/SQL script create a daily job (launched every day at 11:00pm) to delete statspack snapshots. The snapshots history is defined like the L-ASH tool by the ASHSTAT.LIGHTY_PKG.SET_RETENTION package. (Default retention 7 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 present in the L-ASH subdirectory tree.
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
In summary, the sequence of actions to perform to set up a complete management statspack (while logged sysdba and having first installed L-ASH):
- Setup Statspack + programming snapshots + purge old data
- SQL>@?/rdbms/admin/spcreate (Statspack Installation)
- SQL>exec perfstat.statspack.snap; -> if database is in RAC, Take a snap by instance
- SQL>@lighty_sp_update_level.sql (Modification of the capture level and number of SQL's 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 (visualisation 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 by specifying the number of days you want to keep online.
To change this retention, run ASHSTAT.LIGHTY_PKG.SET_RETENTION by specifying the number of days you want to keep online.
Software Configuration
To recognize these two utilities into the software, you must report them to the properties of a connection. To do this, open the properties of a connection, and select the check boxes.
General Operation
How to Start with Lighty
Main Toolbar
This toolbar can be hidden. All options in the toolbar are also available in the menus or via keyboard shortcuts.
Keyboard shortcuts
| Shortcut | Description |
|---|---|
| Ctrl + G | Passage in multiple connections mode |
| Ctrl + I | Moving to single mode connection |
| Ctrl + M | Displays the connection configuration page |
| Ctrl + A | Adding a new connection |
| Ctrl + O | Displays the options window |
| Ctrl + N | Shows or hides the floating window connections |
| Ctrl + R | Shows or hides the floating window reports |
| Ctrl + Q | Shows or hides the floating window of the statistics of a SQL |
| Ctrl + S | Shows or hides the floating window statistical registration |
Floating Windows
Lighty uses floating windows in its GUI. These windows can be grouped, displaced on one edge 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 | ||
| List of SQL reports | ||
| SQL Statistics | ||
| Session Statistics |
Dashboard
The dashboard allows through a single page to provide an overview of different activity statistics. Its management is done with 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 by the appropriate button. Within each tab, 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 gadgets available for the dashboard are visible by clicking on the 
. To integrate a widget to the dashboard, simply drag and drop the widget to the desired location using the mouse. Widgets are resizable (width and height). You can also customize or remove any gadget on the dashboard. A single gadget 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 principle of the dashboard is used in 2 screens (by default, pre-filled with some widgets) :
- "Activity Viewer" Tab (Bottom)
- Tab "Real Time" "); ?>
Hyperlinks
Hyperlinks allow you to easily navigate among the many dispobiles information. They are blue.
In the reporting screen, the history of your browsing is displayed at the top of the window allow you to return instantly to the desired information.
Filters
Filters are ubiquitous in different Windows on the software. They can isolate the desired information easily
There are 3 types of filters :
- The check box
- The text field
The filter is applied automatically as you type in - Value Lists
The filter is applied automatically as you select a value.
You can select multiple values
Timeline Components
This component visually informs you of the activity of an identifier in a 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 sql consumption in a time range (11:50 a.m. to 2:10 p.m.)
More information on a single component:
The implementing beaches
The type of activity
The activity time
This component is present in many screens and widgets of the software.
Automatic refresh
Many screens offer the refreshing feature of displayed information.
In this case, the buttons 
allow you to manage this refresh.
For automatic refresh, select the check box and the frequency in the list (10s, 30s or 60s). A countdown is displayed.
To manually refresh, click the icon.
Export Tools
According to the type of information displayed, the export button 
allows various operations :
- Copy content to the clipboard
- ASH report generation (global, for a SQL or a session)
- AWR report generation in HTML
- ADDM report generation in HTML
- Generating a report of a supervised sql in HTML format
Connections
Ajout
The addition is done through the 
, by the menu or the shortcut Ctrl + A.
Add Connection is done in 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 allow you to group your connections according to their functions.
You can 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 view some special reports (data cache, hidden settings, log alert content ...)
The password can be saved (in encrypted format) so you do an entry for each connection.
The connection configuration can be done by entering the server name, port, and the name of the instance or service name. You can also enter a complete chain of connection (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 do not 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, with the icon 
or by the shortcut Ctrl + M
The window lists all defined connections. You can modify their configuration, or delete a connection.
The connections list
You can list the connections via the "Connection List" menu or the Floating window of the same name.
The menu includes connections by type and application.
Activity of a database
3 tabs helps to navigate among the statistics.
If you do not have the Diagnostic Pack license,
you can install L-ASH to simulate the functionality of ASH
you can install L-ASH to simulate the functionality of ASH
The tab "Activity Viewer" allows you to view the activity of a database. Based on data from the feature "Active Session History", this screen helps you in understanding the activity.
In the middle of this screen is the main graph.
This graph represents, over a range of time consumed, the activity of your database divided by class waiting and CPU consumption.
To help isolate the desired information, several options are available:
- Selecting a date range
- Filter data
- Zoom 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
This is done using the buttons
You can also choose the exact date and time of start and end by clicking the "Custom" button and entering the debin/end date/time
2 - Filter data
The text 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 part of the graphic
This is done using the mouse. Select a portion of the graph to display the corresponding statistics.
The buttons in the graphic 
(or right-click menu of the mouse) allow you to save, copy to clipboard, select the zoom, delete zoom, ...
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 data will be displayed only from the SGA and more precisely the ASH buffer.
The button 
allows you to see the retention, the number of lines and the size of that buffer (for instance).
5 - Organize your dashboard
| Widget | Details | Timeline Component | Possible Options |
|---|---|---|---|
| Graph | View the activity of a subset as a type of graph or pie chart history |
Pie chart or historical Choice of Statistics (Server, program, user, customer identifier, module, action sql operation, sql, system event) |
|
| Sessions |
Show sessions. Fine-tune the information by posting SQLs for a session. |
||
| SQLs |
Displays SQLs Fine-tune the information by posting the events for a SQL system. The execution plan is displayed next to the SQL. When there are several plans, the count of plans is shown. Possibility then to view the plans by clicking on the tree icon. |
||
| PL/SQLs |
Displays PL/SQL Fine-tune the information by posting SQLs for PL/SQL. |
||
|
Service, Module, Action |
Show activity by service, module, action | Grouping on one or more criteria | |
| Machine, Program, User | Show activity per machine, program, user | Grouping on one or more criteria | |
| Client Identifier | Show activity by customer ID | ||
| Waiting class, system event | Show activity per class waiting and/or system event | ||
| Instance, Service | Show activity by Instance and/or Service | Grouping 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 | Show blocking sessions and the sessions blocked in the time interval |
Real time
The control panel shows the activity of the database in real time .
It is organized as a dashboard allowing you to customize the layout.
| Some widgets show the instantaneous activity |
| The supervised SQLs |
| Long operations sessions |
| The use of temporary spaces, PGA |
| Outstanding transactions |
| The blocking sessions |
| Some widgets show incremental statistics computed between 2 snapshots. |
| The system statistics |
| The "Latches" type locks |
| The event histograms |
| The segments used |
| Some widgets show incremental statistics computed 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 gadget can be opened multiple times.
Reports
The tab "Reports" allows you to explore the statistics of your database. These reports are distributed by category and subcategory for simplicity.
| Category | Subcategory |
|---|---|
| Db Informations | 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 display make easy the passage of information to another.
Some reports require licenses have diagnostic and tuning packs
Lighty prevents them from running if you select the right options at the connection level
It reports on the operation of the AWR repository, supervised sqls and visualization ASH activity
Lighty prevents them from running if you select the right options at the connection level
It reports on the operation of the AWR repository, supervised sqls and visualization 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 type is dedicated to statistics from SQL. It is automatically displayed when you click the SQL ID present on the screens.
You can also enter the SQL ID (Possibly the "child" number) to search manually.
This window can be maximized by clicking on the title bar
| Find all the informations related to a SQL |
| The various execution plans (including those stored in the AWR repository or statspack) |
| The execution statistics from the AWR or statspack |
| The sql usage displayed in 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.
(buttons
)
(buttons
)
You can also view the bind variables and the sql optimizer environment
for each execution plan or for each child cursor present in the cache.
for each execution plan or for each child 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.
• We can also compare two previous states (exports).
• Or the same base but at two different times.
Once on the Compare tab you can choose "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 have various reports highlighting the most relevant information for the work of the DBAs. Since version 1.6.0 you have the possibility to create your own custom reports.
There are three new reports that have emerged:
- Jobs Visualization/Management
- Comparison AWR Reports
- Statspack Reports
Jobs Visualization/Management
In this view we can view the different jobs. We also have the option of Enabling or Disabling a job.
Comparison AWR Reports
We can now compare two AWR reports and generate a comparative report to 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 select report).
Now it remains to choose the second report that we want to compare (it is necessary to click on the link "Compare" 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 is used to generate a report in text format.
To generate a report, you must first define an SSH connection
Select the menu 'Current connection / Information'
Then enter an account to execute sqlplus (the spreport.sql script is used)
click "Text Report" and the report in text format is generated.
Sessions Statistics
A Floating Window is dedicated to statistics for a session. It is automatically displayed when you click the SID of this session on the various screens.
You can also enter the SID to search manually.
This window can be maximized by double clicking on the title bar (or the corresponding icon)
| Find all the informations related to a session |
| Login information |
| Open cursors |
| The optimizer's environment |
| The statistics of the session(incremental and global) |
| The current transaction |
| The execution history of the Timeline form |
Import/Export Databases
You can import and export databases connections between Lighty softwares
Kill a session
You can kill a session from the Lighty interface
Export reports
You have the ability 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 trace of a session
You can enable and disable a trace on a session 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 existing reports. The "Duplicate" function has been designed to create custom reports based on existing reports. Thus a user lambda will be able to define these own reports without modifying the existing one. This will save him considerable time because if there is already a report similar to the one he wants, he will not 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 the Duplicate button. This will open the report editor with the various pre-filled fields with the values of the old report. Only the default name is changed so that it does not have two identical names.
After the duplicate report 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 are therefore neither editable nor deletable.
Monitoring a database
You can enable automatic monitoring of certain metrics in a database.
Introduction
The goal is to be able to send alert mails when the value of a metric for an active connection exceeds a certain threshold.The values are derived from the "Chart" 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 is ensured that the interval of time elapsed since the last check (check interval) is greater than or equal 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 equal to the threshold defined for this type, it 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 attemps), one 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 equal to ten percent we go into warning and then check every 2 minutes.
- If there are 3 consecutive warnings, an alert is sent. Then we delete the warning and we start to check every 5 minutes.
- If you go below the 10% threshold before reaching the 3 warning, you start to check every 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 the 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 (unchecked)
- or to make them accessible to the modification (box 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 from 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 one 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 multisite 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 configuration of the SMTP server for sending mail is done 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 nil.
The Lighty application is moderately impacted.
An active monitoring list is maintained for easy searching. It contains for each active connection the list of types of values to be monitored
The Charts display loop performs a search for each new value in this list to see if a monitoring is in progress for this type of value and this 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 can not go below this value for the check interval.
SQL Report Wizzard
SQL Report Wizard is a tool for creating these reports. This tool for configuring reports can be accessed via the "Tools" menu and then "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 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 by selecting it in the Reports window (accessible from the Windows menu) and clicking on the links at the bottom of the report.
You can only modify or delete a previously added report.
The default reports contained in Lighty are therefore neither editable nor deletable.
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 | ... |
| Refreshable |
The report will be refreshed periodically, the period can be configurable 
|
| Diag Pack Needed | Use Diag Pack |
| AWR Exportable |
The export menu is supplemented by AWR exports 
|
| Statspack | Use Statspack |
| Only For Sysdba User | Will appear only for user Sysdba |
| Tuning Pack Needed | Uses the tuning pack |
| Ash Exportable |
The export menu is completed by exports Ash 
|
A parent query will be attached to a category to be 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 delete the selected category you delete all related queries.
A child query is not attached to any category, it will be floating in the tree. 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 sub category) or join a group by selecting from the list.
Creation :
Membership in a group:
If there is no group, the report will be visible only in the My Report category.
The Keyword field will be used for the report search, in addition to the name.
Graphical example:
2 - Sql Versions
Several version ranges can be configured in this screen. For each one can then define a compatible request.
By default, a row exists for the minimum version 10.2 databases.
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 Add, the values selected in the lists are then used to create the new row.
To delete a line, select it and click Delete.
The ranges of the different lines 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 for which you want to define the request.
3 - Sql Text
This screen allows you to enter the query that will retrieve the 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 are 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 Définitions
This is where you can set each of the columns 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 (case of a query that is refreshed periodically) |
| Virtual | Indique si la valeur de la colonne sera calculée en fonction de 2 colonnes (voir écran Virtuals Columns) |
| Type |
Indicates the type of value returned. The type can be redefined by a list depending on the type, the column will display default links to another window. Example : For the Sid (Session Id) type, clicking on the value opens the Session statistics 
|
| ProgressBar |
Indicates whether the column displays values as a progress bar (see Progress Bar properties screen). Example :
|
| Linked |
Indicates whether the column displays a link to another report (see Linked Column ) Example : 
|
| Min. Width | Specifies the minimum pixel size of the column |
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 the column to be processed in the list, you must choose the 2 columns whose values will be used, as well as the operation to achieve 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 application.
After selecting the parameter in the list, you can define its name and type.
The display of the parameter input area will be adapted to the selected type.
Example: A Tablespace List type lists 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 selection list
The verification of the request is done via the Parse Sql button.
6 - Progress Bar Columns Properties
This screen allows you to set the columns whose values are displayed as a progress bar.
After selecting the column, choose the display color and the type from :
7 - Linked Columns
This screen allows you to set 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 press a column whose name is defined in the field Column Linked.
This means that if another report to a column of that name, clicking 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 parameterization allow the calling of a report :
1. Simple Link to Sql : will display the selected report in 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.
8 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 one can choose the display mode from:
| CheckBox Filter | Used for Yes No columns, it displays a checkbox and a label defined in the Filter Displayed Text field. The value used to filter when the box is checked is defined in the Filter value Example: for the column IsDefault will have the following display.   |
| Field Filter | Displays an input field of a value 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  |