navigate

Saturday, February 13, 2016

Online wait expert answers, latch: cache buffers chains event how to solve!




 1 SQL_ID 6s7jtnzrmmuw1, child number 0 
2 -------------------------------------
3 insert / * + append * / into qianyi.WEB_APP_BASE select * from V_WEB_APP_BASE
4
5 Plan hash value: 843235207
6
7 ------------------------------------------------- -------------------------------------------------- ---------
8 | Id | Operation | Name | Rows | Bytes | TempSpc | Cost (% CPU) | Time |
9 ------------------------------------------------- -------------------------------------------------- ---------
10 | 0 | INSERT STATEMENT | | | | | 16714 (100) | |
11 | 1 | LOAD AS SELECT | | | | | | |
12 | 2 | VIEW | V_WEB_APP_BASE | 50001 | 1035M | | 16714 (2) | 00:01:54 |
13 | 3 | UNION-ALL | | | | | | |
14 | 4 | NESTED LOOPS | | 1 | 707 | | 4 (0) | 00:00:01 |
15 | 5 | TABLE ACCESS FULL | T_PLY_BASE_TRANS | 1 | 38 | | 2 (0) | 00:00:01 |
16 | 6 | TABLE ACCESS BY INDEX ROWID | T_PLY_BASE | 1 | 669 | | 2 (0) | 00:00:01 |
17 | * 7 | INDEX UNIQUE SCAN | PK_PLY_BASE | 1 | | | 1 (0) | 00:00:01 |
18 | * 8 | HASH JOIN | | 50000 | 56M | 2056K | 16710 (2) | 00:01:54 |
19 | 9 | TABLE ACCESS FULL | T_EDR_BASE_TRANS | 50000 | 1464K | | 61 (4) | 00:00:01 |
20 | 10 | TABLE ACCESS FULL | T_EDR_BASE | 509K | 562M | | 8213 (3) | 00:00:56 |
twenty one ------------------------------------------------- -------------------------------------------------- ---------
22
23 Predicate Information (identified by operation id):
twenty four ------------------------------------------------- -
25
26 7 - access (.. "A" "C_PLY_APP_NO" = "B" "C_PLY_APP_NO")
27 8 - access (.. "C" "C_EDR_APP_NO" = "D" "C_EDR_APP_NO")
28


T_PLY_BASE_TRANS --0 rows
T_PLY_BASE --3000000 rows
T_EDR_BASE_TRANS --50000 rows
T_EDR_BASE --500000 rows

Caught out from this implementation plan, the implementation plan of the sql no problem, you should soon be able to complete the implementation of the results, but really it run for 40 minutes in the database, has been waiting for: latch: cache buffers chains

SQL & gt; select * from (select latch #, gets, misses from v $ latch_children where name = 'cache buffers chains' order by misses desc) where rownum & lt; = 10;

LATCH # GETS MISSES
---------- ---------- ----------
122 796749524 33238090
122 474008309 4544130
122 26100007 3874355
122 393011341 3605687
122 393779469 3022598
122 394561570 2386402
122 261811589 2217600
122 6536775 1750723
122 6531901 1734751
122 6536392 1703470

10 rows selected

Reply:
1. Optimize the logic read large sql
2. Check the io
3. awr report on or sp
Reply:
Back to the table the best statistical analysis,
Reply:
Mysterious black box, entangled two days, the results of the index get, I too dishes!

Etc. Great God help




            


Table Structure:

id level segm_name parent_id
011 China null
022 Guangdong 01
033 Shenzhen 02
044 Futian District 03
054 Luohu District 03
065 People's Court 04
075 Convention Center 04
086 * F * No. 06
officeExhibition hall * 096 * 07
.
.
.

Wherein the level of field grade, parent_id association on an id.
Like the two existing structure of the table, due to the Id is generated in a different way, you need to find the same level of the fields, the associated field on grade level is also the same two tables ID table!

The big idea of God can provide an idea or a script, beg to force! !
Reply:
I did not understand specifically what kind of results you want
Reply:
what do you mean?
Your ID is automatically generated. . So you now associated with the ID no
Is not this mean?
If this means that you can find out where your current ID is a beginning. Then, based on your previous add a few to be able to associate correct.
Reply:
quote 1 floor yinan9 reply:
did not understand what you want specific results


is to have two structures the same table, the table The segm_name and rank the same, but not the same Id, segm_name now is to identify and rank on the same level and the same level of segm_name and the ID table.

Where from six to one by parent_id association can be assembled into a complete independent address.
Reply:
If the table as long as the ID, Join what can not it do,
 
select a.id, b.id
from a, b
where a.level = b.level
and a.segm_name = b.segm_name
and a.parent_id = b.parent_id

Reply:
quote the second floor ulihss reply:
you mean?
Your ID is automatically generated. . So you now associated with the ID no
Is not this mean?
If this means that you can find out where your current ID is a beginning. Then, based on your previous add a few to be able to associate correct.


In fact, the two structures are the same table, their respective ID generation mechanism is not the same, but the address is the same grade and fields. The aim now is through the field and grade two tables to find out ID control these two tables, which is table 1_id table 2_id. But the fields and on grade level in which they should be associated with the same.
Reply:
quote 4th floor yinan9 reply:
If the table as long as the ID, Join what can not it do,
SQL code? 12345select a.id, b.id from a, b where a.level = b.level and a.segm_name = b.segm_name and a.parent_id = b.parent_id


Sorry, I might description is not clear enough. Because the ID is not the same, so the same parent_id not, can not be directly associated with parent_id. I now want to know how to get on comparative field and grade level through the parent_id, and then get it to compare the ID control.
Reply:
quote, 6th Floor hzwhen1014 reply:
a reference to the fourth floor yinan9 reply: If the table as long as the ID, Join what can not it do,
SQL code? 12345select a.id, b.id from a, b where a.level = b.level and a.segm_name = b.segm_name and a.parent_id = b.parent_id

Sorry, I might describe ......
To be honest, I still do not understand
Reply:
 - with the following statement for the path hierarchy isolated field, and then set the id field by comparing the path 
.select t. *, sys_connect_by_path (segm_name, '/') as path from t
start with parent_id is null connect by prior id = parent_id;

Reply:
quote 8th floor fw0124 reply:
SQL code 123-- with the following statement for the path hierarchy isolated field, and then set the field by comparing the path? . id.select t *, sys_connect_by_path (segm_name, '/') as path from tstart with parent_id is null connect by prior id = parent_id;



Thanks for the great God! ! !

SQL written request


This post was last edited by the ppjava2009 on 2012-12-23 23:27:06
ORACLE demand in wording similar to the following in SQL
DB2
SQL1: Construction Constant temporary table (use very much, do not need to delete tables in functional demo you create a table, insert data and other operations, you can execute SQL replication)
 select 
* / * Where there is other code * /
from (
values
('Joe Smith', 'marketing', 4000),
('Zhao', 'Technology', 2000),
('John Doe', 'marketing', 5000),
('Li Bai', 'Technology', 5000),
('King five', 'marketing', NULL),
('Wang Lan', 'Technology', 4000)
) As employ (name, dept, salary);

Use WITH rewrite too much trouble
 WITH t (name, dept, salary) AS (
SELECT 'Joe Smith' name, 'marketing' dept, 4000 salary FROM dual UNION ALL
SELECT 'Zhao' name, 'Technology' dept, 2000 salary FROM dual UNION ALL
SELECT 'John Doe' name, 'marketing' dept, 3000 salary FROM dual UNION ALL
SELECT 'Li Bai' name, 'Technology' dept, 5000 salary FROM dual UNION ALL
SELECT 'Wang Wu' name, 'marketing' dept, NULL salary FROM dual UNION ALL
SELECT 'Wang Lan' name, 'Technology' dept, 4000 salary FROM dual
)
SELECT * FROM t;


SQL2: insert more than one row of data
 insert into t_trans (cid, seq, input, amount) values 
('Zs001', 1, '2000-1-1', 350.00),
('Zs001', 2, '2000-1-2', 140.00),
('Ls002', 3, '2000-1-3', 220.00),
('Zs001', 4, '2000-1-4', 270.00),
('Ls002', 5, '2000-1-5', 480.00),
('Ls002', 6, '2000-1-6', 660.00);

Of course, you can also use UNION ALL, but too much trouble
 INSERT INTO t_trans (cid, seq, input, amount) 
SELECT 'zs001' cid, 1 seq, '2000-1-1' input, 350.00 amount FROM dual UNION ALL
SELECT 'zs001' cid, 2 seq, '2000-1-2' input, 140.00 amount FROM dual UNION ALL
SELECT 'ls002' cid, 3 seq, '2000-1-3' input, 220.00 amount FROM dual UNION ALL
SELECT 'zs001' cid, 4 seq, '2000-1-4' input, 270.00 amount FROM dual UNION ALL
SELECT 'ls002' cid, 5 seq, '2000-1-5' input, 480.00 amount FROM dual UNION ALL
SELECT 'ls002' cid, 6 seq, '2000-1-6' input, 660.00 amount FROM dual;

Reply:
SQL1: seemingly only with
SQL2: you can insert all, but it is not easy
INSERT ALL
INTO S (S #, SNAME) VALUES (1, 'Joe Smith')
INTO S (S #, SNAME) VALUES (2, 'John Doe')
INTO S (S #, SNAME) VALUES (3, 'Wang Wu')
SELECT * FROM DUAL;
Reply:
quote 1 floor fw0124 reply:
SQL1: seemingly only with
SQL2: you can insert all, but it is not easy
INSERT ALL
INTO S (S #, SNAME) VALUES (1, 'Joe Smith')
INTO S (S #, SNAME) VALUES (2, 'John Doe')
INTO S (S #, SNAME) VALUES (3, 'Wang Wu')
SELECT * FROM DUAL;
......

Thank you!
Reply:
Do not sink ah, I always think there are some strange but very easy to use wording
Reply:
1 floor talking about a more comprehensive
This is indeed no oracle sql server DB2 convenient

Questions on the stored procedure




            


Write stored procedures: Input parameters is a collection of types, return type is Table, and then in the process of internal storage, the collection cycle,
The value of the collection as a condition query after query cycle to meet the conditions of records returned by Table.

How stored procedures not previously written, a problem, and wanted to write this stored procedure, big cow help line waiting
Reply:
Not that blur paging you.

Such storage process basic nor can you write, you do not think of.

10G because the syntax is limited, was more difficult to achieve, 11G and now most of them are useless projects such new database.
Reply:
Results similar to paging thought, but I want is: a collection of all the parameters passed in, to the storage process, the values of these parameters to meet all records are returned to. Because so many of these number of parameters, there may be greater than 1000, and I want these processes are processed in the database, and then returned to me a result. If your code processing, will fight so long to SQL, so I think the stored procedure, but does not know how to write stored procedures, upstairs worship heroes seek help!
Reply:
We project this database using a 11g
Reply:
quote the second floor crazyliyang reply:
thought paged similar, but the results I want is: a collection of all the parameters passed in, to the storage process, These parameter values meet all records are returned to. Because so many of these number of parameters, there may be greater than 1000, and I want these processes are processed in the database, and then returned to me a result. If your code processing, will fight so long to SQL, so I think the stored procedure, but does not know how to write stored procedures, upstairs worship heroes seek help!

Is select ... from table where cola in (more than one thousand parameters)
Or select ... from table where col1 = parameters a and col2 like parameter two and ... and col1000 = parameter one thousand

Why not board the oracle




            


Novices new to the database. . What SQL plus to enter a user name and password Yes - there will be prompted protocol adapter error - seek under the guidance of the great God, I was WIN7, the version is Oracle11g ~ Guiqiu
Reply:
To see if there configure the listener, or have not played up listening service
Reply:
quote 1 floor snoopy93 reply:
to see if there configure the listener, or the monitoring service there since it

How to configure?
Reply:
Both look similar service from up there:
OracleOraDb10g_home2TNSListener
OracleServiceXXX
If not, open look
Reply:
Give you a URL you look at: http: //soft.chinabyte.com/database/384/12244384.shtml
Reply:
quote 3rd floor snoopy93 reply:
look similar to these two services have not played together:
OracleOraDb10g_home2TNSListener
OracleServiceXXX
If not, open look

Tried, I could not find the SID, very depressed, I think reloading
Reply:
quote 5th floor eviljordan reply:
quote 3rd floor snoopy93 reply: look similar to these two services have not played together:
OracleOraDb10g_home2TNSListener
OracleServiceXXX
If not, open look
Tried, I could not find the SID, very depressed, I want to reinstall the

At the command line: set oracle_sid = xxx on the line.
Reply:
quote, 6th Floor snoopy93 reply:
quote 5th floor eviljordan reply:
3rd floor snoopy93 quote reply: look similar to these two services since there is no up:
OracleOraDb10g_home2TNSListener
OracleServiceXXX
If not, open look
Tried, I could not find the SID, very depressed, I want to reinstall the
At the command line: set oracle_sid = xxx on the line.

He says the environment variable is not defined oracle_sid
Reply:
Note that the host monitor host name is best written, do not write the localhost