Read about oracle pl sql performance tuning, The latest news, videos, and discussion topics about oracle pl sql performance tuning from alibabacloud.com
rows reserved data index_size unused
TB_WCB 9439661 317208 kb 167168 KB 149872 KB 168 KB
*/ we found that the size before index compression was 329M. And after compression is 149M, the compression ratio is 45%. The effect is also very obvious. Summarize: Compression through tables and indexes. We can reduce the disk space occupied by the table, this is only part of it, and more importantly, reading the same amount of data, just need to read less data pages,
Label:First, the cause of the promptPlsql Dev collects user statistics while the user is running, but because the user you are currently logged on does not have access to the V$session,v$sesstat and V$statname view, the current user's statistics cannot be collected, and the Plsql The automatic statistics is configured in the Dev tool to conflict, so this hint appears and the test verifies that:[[email protected] ~]$ sqlplus/ asSYSDBA SQL*Plus:release
Label:Original: T-SQL performance Tuning-information collectionIO information (starting from server startup)--database IO Analysiswith iofordatabase as (SELECT db_name (vfs.database_id) as DatabaseName, case if Smf.type = 1 Then ' log_file ' ELSE ' data_file ' END As Databasefile_type, sum (vfs.num_of_bytes_written) as Io_write, sum (VFS.N
/1000 N ' Total time spent in MS ',total_worker_time/1000 N ' Total CPU time MS ',Total_physical_reads N ' Total number of physical reads ',Total_logical_reads/execution_count N ' Each logical read ',Total_logical_reads N ' Logical reads total number of times ',Total number of total_logical_writes N ' logical writes ',Execution_count N ' execution times ',Creation_time N ' statement compilation Time ',Last_execution_time N ' Last Execution time ',SUBSTRING (St.text, (QS.STATEMENT_START_OFFSET/2)
Structure
Similar to other languages, the syntax structure is as follows:
if condition thenstatement1elsestatement2end if ;
2) Loop Structure
This structure is not the same as other languages. There are three types of loop structures in PL/SQL programs:
a. loop … end loop;b. while condition loop … end loop;c. for variable in low_bound . . upper_b
compression is 167M, the size of the table after compression is only 40% of the original table, the effect is obvious, and because most of the table's fields are only IDs, the relative repetition value is not too much.However, we see that the size of the index varies substantially, so we continue to compress the index:5. Compression indexAlter index idx_tb_wcb_id on Tb_wcbrebuildwith (Data_compression=row)6, the comparison after the index compressionsp_spaceused ' ms_visit_qst_opt '/*name
The Time Model for oracle performance optimization collects statistics on the time model. The time model counts the time spent in the database according to the operation type. The most important time model is the database time-DB time, which represents the total time of the front-end session in the database, and is an indicator of the overall instance load .. Database time is an important proportion of the
Web applications with more than 300 million data records in a single tableProgramAfter the test, some functional and performance problems were discovered. The following methods were taken to adjust the performance:
Changing the vast majority of SQL queries to stored procedures can undoubtedly improve some performance
1. Log in to PL/SQL DeveloperThis omits the installation steps for the Oracle database and PL/SQL Developer, and note that when you install the PL/SQL Developer software, do not install
First download instantclient can be downloaded to the Oracle website, to register the account, must be under the corresponding version, otherwise unsuccessful,10g,11g version of, I have been sent up, free download: Is crossing, wait a minuteConfiguration file Tnsnames.ora This one place is generally put (network/admin), only in PL/SQL----->tools-->proferences ...
Just note that the output parameter is registered with the Registeroutparameter method inside the CallableStatement.Stored procedures:Import java.sql.CallableStatement;Import java.sql.Connection;Import Java.sql.DriverManager;Import java.sql.PreparedStatement;Import java.sql.SQLException;Import Java.sql.Types;Calling Oracle's stored procedures in Javapublic class Javacallpro {public static void Main (string[] args) {String driver= "Oracle.jdbc.driver.OracleDriver";String url= "JDBC:ORACLE:THIN:@1
Oracle SQL injection is an old security issue. Generally, enterprise applications only focus on Java-level writing specifications, such as preparedStatement, or directly filter out dangerous characters. In fact, when compiling PL/SQL functions or procedure, there are also injection problems. Let's discuss it briefly.
F
1. knowledge point: you can read the following screen recording:
PL/SQL program structure declare description (variable description, cursor declaration, exception description) begin statement sequence (DML statement 〕... Exception Processing statement End;/-------------------------------------------------------------------- first PL/
The Oracle tutorial you are looking at is the Oracle Pl/sql introductory case practice. We have already learned the basics of pl/sql programming, and this article will combine a case to deepen understanding of these knowledge poin
summary data is based is updated frequently, it is easier to use a view than to write SQL every time
2. Create a ViewThis is how the CREATE view is defined in the MySQL documentation: CREATE [OR REPLACE] [algorithm = {UNDEFINED | MERGE | TempTable}] [definer = {User | Current_User}] [SQL SECURITY {definer | INVOKER}]VIEW view_name [(column_list)]As Select_statement[With [cascaded | LOCAL] CHECK
Label:PL/SQL exception handling for Oracle databaseExceptions are abnormal events that occur during the course of a program's operation, usually caused by hardware problems or programming problems. Even the best-written programs may encounter errors or unanticipated events during the PL/SQL programming process. A robus
Log on to PL/SQL Developer, log on to the local database, and check that the Oracle listening service is not started. Right-click to start the listening program and report an error. How can this problem be solved ?,
I. Error description
When you log on to PL/SQL Developer t
Downloads.
Step 2: unzip the installation package1> after downloading, decompress the compressed file to a local path, for example, C:/instantclient.2> Create the network/admin folder in this path and create tnsnames in the C:/instantclient/network/admin folder. ora file, the file content is the TNS information of the database to be connected. For example:
WORCL =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.1.4) (PORT = 1521 )))(CONNECT_DATA =(SERVICE_NAME = orcl))
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.